【MySQL基础】如何选择存储引擎

引入

• 在介绍存储引擎之前，先来回顾一下索引的相关知识。

• 索引是对数据库表中一个或多个列的值进行排序的结构，建立索引有助于更快地获取信息。

• mysql 有四种不同的索引类型：

  • 主键索引 ( PRIMARY )
  • 唯一索引 ( UNIQUE )
  • 普通索引 ( INDEX )
  • 全文索引（FULLTEXT , MYISAM 及 mysql 5.6 以上的 Innodb ）

• 建立索引的目的是加快对表中记录的查找或排序，索引也并非越多越好，因为创建索引是要付出代价的：增加了数据库的存储空间，在插入和修改数据时要花费较多的时间维护索引。

• 在设计表或索引时，常出现以下几个问题：

  • 少建索引或不建索引。这个问题最突出，建议建表时 DBA 可以一起协助把关。
  • 索引滥用。滥用索引将导致写请求变慢，拖慢整体数据库的响应速度（5.5 以下的 mysql 只能用到一个索引)。
  • 从不考虑联合索引。实际上联合索引的效率往往要比单列索引的效率更高。
  • 非最优列选择。低选择性的字段不适合建单列索引，如 status 类型的字段。

什么是存储引擎？

• MySQL数据库表是用于存储和组织信息的数据结构，可以将表理解为由行和列组成的表格，类似于Excel的电子表格的形式。有的表简单，有的表复杂，有的表根本不用来存储任何长期的数据，有的表读取时非常快，但是插入数据时去很差；

• 而我们在实际开发过程中，就可能需要各种各样的表，不同的表，就意味着存储不同类型的数据，数据的处理上也会存在着差异，

• 对于MySQL来说，它提供了很多种类型的存储引擎，我们可以根据对数据处理的需求，选择不同的存储引擎，从而最大限度的利用MySQL强大的功能。

• 数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以 获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎

存储引擎的介绍

1.查看存储引擎

• mysql给开发者提供了查询存储引擎的功能，我使用的是MySQL5.5，使用命令SHOW ENGINES;可以查看存储引擎。如下图：

分析：从图中可以看出，MySQL支持多种存储引擎，而默认的存储引擎是InnoDB。

• 我还可以查看当前表的存储引擎，使用命令show create table student;查看，如下图

分析：从上图可以看出student表的存储引擎是InnoDB

2.MyISAM

（1）定义

• MyISAM表是独立于操作系统的，这说明可以轻松地将其从Windows服务器移植到Linux服务器；每当我们建立一个MyISAM引擎的表时，就会在本地磁盘上建立三个文件，文件名就是表明。

• MyISAM表无法处理事务，这就意味着有事务处理需求的表，不能使用MyISAM存储引擎。

（2）MyISAM的表支持3中不同的存储格式，分别是静态（固定长度）表、动态表、压缩表，其中，静态表是默认的存储格式。

• 静态表中的字段都是非变长字段，这样每个记录都是固定长度的，

  • 优点是存储非常迅速，容易缓存，出现故障容易修复；
  • 缺点是占用的空间通常比动态多。静态表的数据在存储时会按列的宽度定义补足空格，但是在应用访问的时候并不会得到这些空格，这些空格在返回给应用之前已经去掉。

• 动态表中包含变长字段，记录不是固定长度的，

  • 优点是占用的空间相对较少，
  • 缺点是频繁的更新和删除记录会产生碎片，需要定期执行 OPTIMIZE TABLE语句或myisamchk -r命令来改善性能，在出现故障时恢复相对比较困难

• 压缩表由myisam工具创建，占据非常小的磁盘空间。因为每个记录是被单独压缩的，所有只有非常小的访问开支

（3）特点

• 大文件（达到63位文件长度）在支持大文件的文件系统和操作系统上被支持

• 当把删除和更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一块自动完成

• 每个MyISAM表最大索引数是64，这可以通过重新编译来改变。每个索引最大的列数是16

• 最大的键长度是1000字节，这也可以通过编译来改变，对于键长度超过250字节的情况，一个超过1024字节的键将被用上

• BLOB和TEXT列可以被索引

• NULL被允许在索引的列中，这个值占每个键的0~1个字节

• 所有数字键值以高字节优先被存储以允许一个更高的索引压缩

• 每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列，当INSERT和UPDATE操作的时候该列被更新，同时AUTO_INCREMENT列将被刷新。所以说，MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快

• 可以把数据文件和索引文件放在不同目录

• 每个字符列可以有不同的字符集

• 有VARCHAR的表可以固定或动态记录长度

• VARCHAR和CHAR列可以多达64KB

（4）适用场景

• 选择密集型的表。MyISAM存储引擎在筛选大量数据时非常迅速，这是它最突出的优点。

• 插入密集型的表。MyISAM的并发插入特性允许同时选择和插入数据。例如：MyISAM存储引擎很适合管理邮件或Web服务器日志数据。

3.InnoDB

（1）定义

InnoDB是一个健壮的事务型存储引擎，这种存储引擎已经被很多互联网公司使用，为用户操作非常大的数据存储提供了一个强大的解决方案。

（2）特点

• InnoDB是默认的存储引擎。

• InnoDB还引入了行级锁定和外键约束，

（3）适用场合

• 更新密集的表。InnoDB存储引擎特别适合处理多重并发的更新请求。

• 事务：InnoDB存储引擎是支持事务的标准MySQL存储引擎。

• 自动灾难恢复。与其它存储引擎不同，InnoDB表能够自动从灾难中恢复。

• 外键约束。MySQL支持外键的存储引擎只有InnoDB。

• 支持自动增加列AUTO_INCREMENT属性。

4.MEMORY

（1）定义

使用MySQL Memory存储引擎的出发点是速度。为得到最快的响应时间，采用的逻辑存储介质是系统内存。

（2）特点

• 在内存中存储表数据会提供很高的性能，但当mysqld守护进程崩溃时，所有的Memory数据都会丢失。获得速度的同时也带来了一些缺陷。

• 它要求存储在Memory数据表里的数据使用的是长度不变的格式，这意味着不能使用BLOB和TEXT这样的长度可变的数据类型，VARCHAR是一种长度可变的类型，但因为它在MySQL内部当做长度固定不变的CHAR类型，所以可以使用。

• Memory同时支持散列索引和B树索引。B树索引的优于散列索引的是，可以使用部分查询和通配查询，也可以使用<、>和>=等操作符方便数据挖掘。散列索引进行“相等比较”非常快，但是对“范围比较”的速度就慢多了，因此散列索引值适合使用在=和<>的操作符中，不适合在<或>操作符中，也同样不适合用在order by子句中。

（3）使用场景

• 目标数据较小，而且被非常频繁地访问。在内存中存放数据，所以会造成内存的使用，可以通过参数max_heap_table_size控制Memory表的大小，设置此参数，就可以限制Memory表的最大大小。

• 如果数据是临时的，而且要求必须立即可用，那么就可以存放在内存表中。

• 存储在Memory表中的数据如果突然丢失，不会对应用服务产生实质的负面影响。

5.MERGE

（1）定义

• MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表结构必须完全相同，尽管其使用不如其它引擎突出，但是在某些情况下非常有用。

• Merge表就是几个相同MyISAM表的聚合器；

• Merge表中并没有数据，对Merge类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行操作。

（2）特点

• MEMORY表的每个表可以有多达32个索引，每个索引16列，以及500字节的最大键长度

• MEMORY存储引擎执行HASH和BTREE缩影

• 可以在一个MEMORY表中有非唯一键值

• MEMORY表使用一个固定的记录长度格式

• MEMORY不支持BLOB或TEXT列

• MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引

• MEMORY表在所由客户端之间共享（就像其他任何非TEMPORARY表）

• MEMORY表内存被存储在内存中，内存是MEMORY表和服务器在查询处理时的空闲中，创建的内部表共享

• 当不再需要MEMORY表的内容时，要释放被MEMORY表使用的内存，应该执行DELETE FROM或TRUNCATE TABLE，或者删除整个表（使用DROP TABLE）

（3）适用场景

• 对于服务器日志信息，一般常用的存储策略是将数据分成很多表，每个名称与特定的时间端相关。

• 例如：可以用12个相同的表来存储服务器日志数据，每个表用对应各个月份的名字来命名。当有必要基于所有12个日志表的数据来生成报表，这意味着需要编写并更新多表查询，以反映这些表中的信息。与其编写这些可能出现错误的查询，不如将这些表合并起来使用一条查询，之后再删除Merge表，而不影响原来的数据，删除Merge表只是删除Merge表的定义，对内部的表没有任何影响。

6.ARCHIVE

• Archive是归档的意思，

• 在归档之后很多的高级功能就不再支持了，仅仅支持最基本的插入和查询两种功能。

• Archive在MySQL 5.5之后的版本支持索引。

• Archive拥有很好的压缩机制，它使用zlib压缩库，在记录被请求时会实时压缩，

• Archive经常被用来当做仓库使用。

四种存储引擎的异同

功 能 MYISAM Memory InnoDB Archive 存储限制 256TB RAM 64TB None 支持事物 No No Yes No 支持B树索引 Yes Yes Yes Yes 支持全文索引 Yes No No No 支持数索引 Yes Yes Yes No 支持哈希索引 No Yes No No 支持数据缓存 No N/A Yes No 支持外键 No No Yes No 锁机制 表锁 表锁 行锁 表锁

存储引擎的适用场景

• 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性，并发性要求不是很高，那么选择这个存储引擎是非常适合的。MyISAM是在Web、数据仓库和其他应用环境下最常使用的存储引擎之一。

• 如果应用对事物的完整性有比较高的要求，在并发条件下要求数据的一致性， 数据操作除了插入和查询以外，还能包括很多的更新、删除操作，那么InnoDB存储引擎应该是比较合适的选择。InnoDB存储引擎除了有效地降低由类似计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB都是合适的选择。

• 如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果

• 如果是数据仓储等VLDB环境，适合使用MERGE

• 如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive

存储引擎的实现方式

1.MyISAM

• MyISAM引擎使用B+ Tree作为索引结构，叶节点存放的是数据记录的地址。

• MyISAM引擎的辅助索引（二级索引）和主索引在结构上没有区别，只是辅助索引的key可以重复，叶节点上存放的也是数据记录的地址。

• MyISAM引擎的索引结构为B+Tree，其中B+Tree的数据域存储的内容为实际数据的地址，也就是说它的索引和实际的数据是分开的，只不过是用索引指向了实际的数据，这种索引就是所谓的非聚集索引。

2.InnoDB

• InnoDB中表数据本身就是按B+ Tree组织的一个索引结构，叶节点存放的就不是数据记录的地址，而是完整的数据记录。所以InnoDB这种存储方式，又称为聚集索引，使得按主键的搜索十分高效，但二级索引搜索需要检索两遍索引：首先二级索引获得主键，然后用主键到主索引中检索到数据记录。

• 因为主键是InnoDB表记录的”逻辑地址“，所以InnoDB要求表必须有主键，MyISAM可以没有。

3.MySQL为什么使用B+树作为索引？

（1）为什么不用hash表存储？

• B树适合在磁盘等直接存储设备上组织动态查找表，文件的组织方式便是B树或B+树。他在查询和动态操作方面效率很高。

• 但是hash表的效率更高，可是hash表第一存在散列冲突问题，这个必须解决；第二hash表的一般利用率仅为50%，这就会占用大量的存储空间而实际应用的空间却不多。

• B树和hash表都很灵活适用于多表查找和存储，可是当存储比例达到一定程度时hash表必须进行扩容才能维持之后的操作，而B树不用。

（2）如何选择数据结构？

• 一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。

• 索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，

• 评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

4.聚集索引

• 聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。

• 例如，如果应用程序执行 的一个查询经常检索某一日期范围内的记录，则使用聚集索引可以迅速找到包含开始日期的行，然后检索表中所有相邻的行，直到到达结束日期。这样有助于提高此 类查询的性能。同样，如果对从表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上聚集（物理排序），避免每次查询该列时都进行排序，从而节 省成本。

本人才疏学浅，若有错，请指出，谢谢！
如果你有更好的建议，可以留言我们一起讨论，共同进步！
衷心的感谢您能耐心的读完本篇博文！

参考链接：
1.Innodb中的事务隔离级别和锁的关系—美团点评技术博客
2.MySQL索引原理及慢查询优化——美团点评技术博客
3.MySQL索引背后的数据结构及算法原理
4.B+树与MySQL存储引擎