建设网站费用入会计分录,站长统计 站长统计,行业关键词搜索排名,榆林网站建设电话一、存储引擎概念
MySQL数据库和大多数的数据库不同, MySQL数据库中有一个存储引擎的概念, 针对不同的存储需求可以选择最优的存储引擎。
存储引擎就是存储数据#xff0c;建立索引#xff0c;更新查询数据等等技术的实现方式 。存储引擎是基于表的#xff0c;而不是基…一、存储引擎概念
MySQL数据库和大多数的数据库不同, MySQL数据库中有一个存储引擎的概念, 针对不同的存储需求可以选择最优的存储引擎。
存储引擎就是存储数据建立索引更新查询数据等等技术的实现方式 。存储引擎是基于表的而不是基于库的。所以存储引擎也可被称为表类型。
OracleSqlServer等数据库只有一种存储引擎。MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎可以根据需要使用相应引擎或者编写存储引擎。
1.1存储引擎架构
MySQL的逻辑架构分为四层
第一层连接层最上层是一些客户端和链接服务包含本地sock 通信和大多数基于客户端/服务端工具实现的类似于 TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。它们都是服务于C/S程序或者是这些程序所需要的 连接处理身份验证安全性等等。
第二层服务层值得关注。这是MySQL的核心部分。通常叫做 SQL Layer。在 MySQL数据库系统处理底层数据之前的所有工作都是在这一层完成的包括权限判断 sql解析行计划优化 query cache 的处理以及所有内置的函数(如日期时间数学运算加密)等等。各个存储引擎提供的功能都集中在这一层如存储过程触发器视图等。
第三层引擎层。通常叫做StorageEngine Layer 也就是底层数据存取操作实现部分由多种存储引擎共同组成。它们负责存储和获取所有存储在MySQL中的数据。就像Linux众多的文件系统 一样。每个存储引擎都有自己的优点和缺陷。服务器是通过存储引擎API来与它们交互的。这个接口隐藏 了各个存储引擎不同的地方。对于查询层尽可能的透明。这个API包含了很多底层的操作。如开始一个事物或者取出有特定主键的行。存储引擎不能解析SQL互相之间也不能通信。仅仅是简单的响应服务器 的请求。
第四层存储层。主要是将数据存储在文件系统之上并完成与存储引擎的交互。和其他数据库相比MySQL有点与众不同它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎上插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。
二、MySQL中查看与修改引擎
2.1查看mysql所支持的存储引擎以及从中得到mysql默认的存储引擎。
show engines;
创建新表时如果不指定存储引擎那么系统就会使用默认的存储引擎MySQL5.5之前的默认存储引擎是MyISAM5.5之后就改为了InnoDB。
查看Mysql数据库默认的存储引擎 指令 show variables like %storage_engine% 可以看出默认的数据库引擎是InnoDB。附上各个参数说明
参数名称解释说明Engine存储引擎名称Support是否支持该引擎以及该引擎是否为默认存储引擎YES表示支持NO表示不支持DEFAULTDEFAULT表示为默认存储引擎Comment存储引擎的简单介绍Transactions表示该引擎是否支持事务XA说明该存储引擎是否支持分布事务Savepoints说明该存储引擎是否支持部分事务回滚
2.2如何更改默认存储引擎。
找到MySQL配置文件mysql.ini首先将其备份。
cp mysql.ini mysql.ini.bak
在[mysqld]后面添加default-storage-engine引擎名字 保存重启MySQL服务即可。
2.3如何在建表时设置引擎以及建表后修改引擎。
建表时设置引擎语法
create table 表名...) type引擎名
建表后更改表的引擎语法
alter table 表名 type引擎名
2.4如何查看修改和创建表的存储引擎。
语法一 show create table 表名;
show create table user;
语法二 show table status like 表名 show table status like user
2.5准确查看某个数据库中的某一表所使用的存储引擎。
语法 show table status from 数据库名 where name表名 show table status from xxkk where nameuser;
三、MySQL中存储引擎
下面重点介绍几种常用的存储引擎 并对比各个存储引擎之间的区别 如下表所示 3.1InnoDB引擎
InnoDB是一个事务型存储引擎提供了对数据库ACID事务的支持并实现了SQL标准的四种隔离级别具有行级锁定这一点说明锁的粒度小在写数据时不需要锁住整个表因此适用于高并发情形及外键支持所有数据库引擎中独一份仅有它支持外键
该引擎的设计目标便是处理大容量数据的数据库系统MySQL在运行时InnoDB会在内存中建立缓冲池用于缓存数据及索引。
优点
支持自动增长列支持外键约束
缺点
该引擎不支持FULLTEXT类型的索引没有保存表的行数在执行select count(*) from 表名 查询单行时需要遍历扫描全表
在以下场合下使用InnoDB是最理想的选择
更新密集的表。 InnoDB存储引擎特别适合处理多重并发的更新请求。事务。 InnoDB存储引擎是支持事务的标准MySQL存储引擎。自动灾难恢复。 与其它存储引擎不同InnoDB表能够自动从灾难中恢复。外键约束。 MySQL支持外键的存储引擎只有InnoDB。支持自动增加列AUTO_INCREMENT属性。从5.7开始innodb存储引擎成为默认的存储引擎。
一般来说如果需要事务支持并且有较高的并发读取频率InnoDB是不错的选择。
3.2MyLSAM引擎
MyISAM存储引擎不支持事务、也不支持外键优势是访问速度快对事务完整性没有 要求或者以selectinsert为主的应用基本上可以用这个引擎来创建表
支持3种不同的存储格式分别是静态表动态表压缩表 静态表表中的字段都是非变长字段这样每个记录都是固定长度的优点存储非常迅速容易缓存出现故障容易恢复缺点是占用的空间通常比动态表多。动态表记录不是固定长度的这样存储的优点是占用的空间相对较少缺点频繁的更新、删除数据容易产生碎片需要定期执行OPTIMIZE TABLE或者myisamchk-r命令来改善性能压缩表因为每个记录是被单独压缩的所以只有非常小的访问开支 MyISAM表是独立于操作系统的这说明可以轻松地将其从Windows服务器移植到Linux服务器。
每当我们建立一个MyISAM引擎的表时就会在本地磁盘上建立三个文件文件名就是表名。
MyISAM表无法处理事务这就意味着有事务处理需求的表不能使用MyISAM存储引擎。
MyISAM存储引擎特别适合在以下几种情况下使用 选择密集型的表。 MyISAM存储引擎在筛选大量数据时非常迅速这是它最突出的优点。 插入密集型的表。 MyISAM的并发插入特性允许同时选择和插入数据。
由此看来MyISAM存储引擎很适合管理服务器日志数据。
3.3MemoryHeap引擎
Memory存储引擎将表的数据存放在内存中。每个MEMORY表实际对应一个磁盘文件格式是.frm 该文件中只存储表的结构而其数据文件都是存储在内存中这样有利于数据的快速处理提高整个表的效率。MEMORY 类型的表访问非常地快因为他的数据是存放在内存中的并且默认使用HASH索引 但是服务一旦关闭表中的数据就会丢失。
Memory同时支持散列索引和B树索引B树索引可以使用部分查询和通配查询也可以使用,和等操作符方便数据挖掘散列索引相等的比较快但是对于范围的比较慢很多
可能的缺点
1. 要求存储的数据是数据长度不变的格式Blob和Text类型数据不可用长度不固定
2. 用完表格后表格便被删除
适用场景
那些内容变化不频繁的代码表或者作为统计操作的中间结果表便于高效地堆中间结果进行分析并得到最终的统计结果目标数据比较小而且非常频繁的进行访问在内存中存放数据如果太大的数据会造成内存溢出。可以通过参数max_heap_table_size控制Memory表的大小限制Memory表的最大的大小数据是临时的而且必须立即能取出用到于是可存放在内存中存储在Memory表中的数据如果突然间丢失的话也没有太大的关系
3.4MRG_MyISAM引擎
MRG_MyISAM存储引擎是一组MyISAM表的组合说白了Merge表就是几个相同MyISAM表的聚合器Merge表中并没有数据对Merge类型的表可以进行查询、更新、删除操作这些操作实际上是对内部的MyISAM表进行操作。
比如我们可能会遇到这样的问题同一种类的数据会根据数据的时间分为多个表如果这时候进行查询的话就会比较麻烦Merge可以直接将多个表聚合成一个表统一查询然后再删除Merge表删除的是定义原来的数据不会影响。
MRG_MyISAM引擎缺点如下
使用表级锁虽然内存访问快但如果频繁的读写表级锁会成为瓶颈。只支持固定大小的行。Varchar类型的字段会存储为固定长度的Char类型浪费空间。不支持TEXT、BLOB字段。当有些查询需要使用到临时表使用的也是MEMORY存储引擎时如果表中有TEXT、BLOB字段那么会转换为基于磁盘的MyISAM表严重降低性能。由于内存资源成本昂贵一般不建议设置过大的内存表如果内存表满了可通过清除数据或调整内存表参数来避免报错。
3.5Blackhole引擎
任何写入到此引擎的数据均会被丢弃掉 不做实际存储Select语句的内容永远是空。他会丢弃所有的插入的数据服务器会记录下Blackhole表的日志所以可以用于复制数据到备份数据库。
适用场景
充当日志服务器验证dump file语法正确性以使用blackhole引擎来检测binlog功能所需要的额外负载
四、MyISAM与InnoDB如何选择
两种存储引擎的大致区别表现在
InnoDB支持事务MyISAM不支持。事务是一种高级的处理方式如在增删改中要是出错可以回滚还原数据而MyISAM就不可以了这是非常重要的。MyISAM适合查询以及插入为主的应用InnoDB适合频繁修改以及涉及到安全性较高的应用。InnoDB支持外键MyISAM不支持。InnoDB是默认引擎MylSAM不是。InnoDB不支持FULLTEXT类型的索引。InnoDB中不保存表的行数如在查询数据表时InnoDB需要扫描一遍整个表来计算有多少行但是MyISAM只要简单的读出保存好的行数即可。对于自增长的字段InnoDB中必须包含只有该字段的索引但是在MyISAM表中可以和其他字段一起建立联合索引。清空整个表时InnoDB是一行一行的删除效率非常慢。MyISAM则会重建表。InnoDB支持行锁。
如果数据量比较大这是需要通过升级架构来解决比如分表分库读写分离而不是单纯地依赖存储引擎。一般都是选用InnoDB了主要是MyISAM的全表锁读写串行问题并发效率锁表效率低MyISAM对于读写密集型应用一般是不会去选用的。
