深入理解mysql索引

MYSQL索引

---

前言

目前MySQL主要流行的存储引擎为MyISAM和InnoDB两种，而InnoDB相对来说用的更多，本文主要针对这两种存储引擎来进行描述。

---

一、索引是什么？

索引是帮助MySQL高效获取数据的排好序的数据结构。MySQL中索引默认的数据结构为B+Tree。

二、MyISAM和InnoDB的区别

1.数据存储文件不同

新建两张表Test_InnoDB（存储引擎为InnoDB），Test_MyISAM（存储引擎为MyISAM），当进入到MySQL安装目录下的data文件夹可以看到test_innodb.frm，test_innodb.ibd；test_myisam.frm，test_myisam.MYD，test_myisam.MYI文件。
（一）针对MyISAM有test_myisam.frm，test_myisam.MYD，test_myisam.MYI三个文件。
*.frm–表定义，是描述表结构的文件。
*.MYD–"D"数据信息文件，是表的数据文件。
*.MYI–"I"索引信息文件，是表数据文件中任何索引的数据树
（二）针对InnoDB有test_innodb.frm，test_innodb.ibd两个文件。
*.frm–表定义，是描述表结构的文件。
*.ibd–表数据文件，其实就是索引和数据存储在一个文件内。
由此可以看出InnoDB和MyISAM的索引存的区别，这样就能更好的理解下午中MyISAM和InnoDB索引的区别。

三、B+Tree

非叶子节点不存储data，只存储索引（冗余），可以放更多的索引。叶子节点包含所有索引字段。叶子节点用指针连接，提高区间访问的性能（增加范围查找的效率）。

补充知识：

系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。
InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB，可通过参数innodb_page_size将页的大小设置为4K、8K、16K，在MySQL中可通过如下命令查看页的大小：
show variables like ‘innodb_page_size’;
而系统一个磁盘块的存储空间往往没有这么大，因此InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16KB。InnoDB在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。
B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块。

2.MyISAM索引结构

由上文提到MyISAM存储引擎的数据和索引是分开存储的，索引中存储是该记录在磁盘中所处的位置（这也叫做非聚簇索引：数据和索引分开存储的）；MyISAM的辅助索引结构与主键索引结构类似，索引树上存储的为该记录所在磁盘位置。

2.InnoDB索引结构

（1）主键索引（InnoDB中主键索引就是聚簇索引：叶子节点存储的是索引和数据）

当构建一张存储引擎为InnoDB的表时，数据就是以B+Tree的树形结构存储的（用主键构建，当表中没有主键时，MySQL会自动从你所有列中选择一列没有重复数据的列构建，如过没有不会重复的列，MySQL则会帮你建立一个隐藏列来维护B+Tree。）我们可以看到B+Tree的叶子节点存储的为索引和数据，当查询数据时只需要扫描这颗B+Tree就能拿到所有数据。

（二）辅助索引（非主键索引）

辅助索引的叶子节点存储的则是索引和主键，很显然辅助索引会比主键索引小，扫描会更快。列如上图中是用name列维护的一颗B+Tree，当需要查询name时可以直接扫描这颗B+Tree；如果还需要查询其他列的信息，则会根据得到的主键去主键索引中查询（这就称为回表操作）。

（3）联合索引

如图，以name，age，position三列建立一个联合索引，他们是如何排序呢？其实可以看成依次比较，当用name可以比较出大小时直接根据name排序，当name不能比较出大小时则根据age比较，当age不能比较出大小时则根据position比较。