深入剖析MySQL索引机制

• 一、存储引擎MyIsam/InnoDB

MySQL数据库的存储引擎有MyIsam和InnoDB两个，MyIsam索引和数据分离，是天生非聚簇索引，InnoDB数据和索引在一起，数据文件本身就是主键索引文件，这样的索引被称为聚簇索引。直接反应在文件系统中，MyIsam存储引擎中每个数据表有三个文件.frm、.MYD和.MYI，其中.frm是表定义文件，.MYD是数据文件，.MYI则是索引文件。InnoDB只有两个文件.frm和.ibd，.frm是表定义文件，数据和索引则统一存放在.ibd中。

二、索引数据结构B+Tree

索引是为了加速对表中数据行的检索而创建的一种分散存储的数据结构，索引能极大的减少存储引擎需要扫描的数据量，可以帮助我们在进行分组、排序等操作时避免使用临时表。

MySQL数据库的索引数据结构为B+Tree，B+Tree是多路绝对平衡查找树，所有叶子节点高度相同。B+Tree具有如下优点：

1、B+节点关键字个数与子节点个数是1:1关系；

2、B+非叶节点不保存数据相关信息，只保存关键字和子节点的引用；

3、B+关键字对应的数据保存在叶子节点中；

4、B+叶子节点是顺序排列的，并且相邻节点具有顺序引用的关系。

由于B+Tree出色的数据结构，所有采用B+Tree作为索引数据结构，可以减少磁盘IO次数，提高磁盘读写能力，使得查询效率也更加的稳定。

三、MySQL运行机制

四、MySQL中B+Tree索引体现形式—MyIsam

如下图所示，以主键id和字段name分别建立两个索引，B+Tree数据结构中非叶子节点存放索引关键字和子节点引用，叶子节点中存放的是数据指针/磁盘地址。通过关键字查询B+Tree获取数据指针/磁盘地址，然后根据指针/磁盘地址访问目标数据。

五、MySQL中B+Tree索引体现形式—InnoDB

如下图所示，InnoDB中索引和数据存放在一起，以主键为索引组织数据存储，叶子节点包括所有数据。字段name的创建索引后怎么进行数据检索呢，其实只要知道其它索引（辅助索引）B+Tree叶子节点存放的内容就能一目了然，由于其它索引（辅助索引）B+Tree叶子节点存放的是数据主键，所以检索到主键后，还需要再检索一遍主键索引，才能找到目标数据。

六、创建索引几点建议

1）索引列的数据长度能少则少

2）索引一定不是越多越好，越全越好

3）匹配列前缀可用到索引 like 999%，like %999%则用不到索引

4）Where条件中 not in 和<>操作无法使用索引

5）匹配范围值，order by，group by也可用到索引

6）多用指定列查询，只返回自己想到的数据列，少用select *（如果查询可通过索引节点中的关键字直接返回，则该索引称之为覆盖索引，覆盖索引可减少数据库IO，将随机IO变为顺序IO，可提高查询性能）

7）索引列上不能使用表达式和函数

8）对于过长字符串需创建索引可通过创建前缀索引（前缀的选择，要有好的离散性）

9）联合索引中如果不是按照索引最左列开始查找，无法使用索引

10）联合索引中精确匹配最左前列并范围匹配另外一列可以用到索引

11）联合索引中如果查询条件中有某个列的范围查询，则其右边的所有列都无法使用索引