深入MySQL的索引实践及优化

一、什么是索引

索引是帮助MySQL高效获取数据的数据结构。简单来讲，数据库索引就像是书前面的目录，能加快数据库的查询速度。
对于海量数据来说，它的目录也是很大的，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中。

InnoDB提供的一种分类方式，InnoDB的每张表都会有一个聚集索引，有且仅有一个，这也是该表的物理存储方式；非聚集索引可以有多个；InnoDB和MyISAM会自动为主键或者声明为UNIQUE的列去自动建立B+树索引。

- 聚簇索引（主键索引）：
根据主键构建的索引叫做聚簇索引。将数据存储与索引放到了一块，找到索引也就找到了数据。
优点：检索速度很快，排序查找、范围查找都很不赖；没有回表查询现象。
缺点：主键最好是自增的，因为连续的占用存储更少的主键索引性能更好；主键最好设置为不可变，改变主键会导致聚集索引的维护代价很高。

- 非聚簇索引（辅助索引）：
不是根据主键构建的索引叫做非聚集索引或者二级索引或者辅助索引。
将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置。
缺点：存在回表查询现象

- 联合索引：
能够针对多个列进行排序，先按第一个列进行索引排序，在上一个列相同的情况下，按照下一列再继续排序。
叶子节点记录索引列+主键。

• 聚簇索引叶子节点存放完整的记录：

• 非聚簇索引叶子节点存放索引项列+主键：
  需要回表查询一次

二、数据结构——为什么是B+树

在探讨这个问题前我们先明确一件事： 对于索引的树结构，每个节点称为页，页就是我们上面说的磁盘块，是MySQL数据读取的基本单位。因此，一个节点的读取对应于一次IO操作，底层数据结构的设计应该往减少IO次数的方向进行优化。

如果不知道这几种数据结构的，建议先面向百度学习一下，这不是本文的重点。

平衡二叉查找树

- 缺点：
路数太少，树太深，检索性能有限，而且会导致查找过程中IO次数很多。

红黑树

- 缺点：
1 同样的，路数太少，树太深，检索性能有限；
2 另外，红黑树不是绝对平衡，IO次数会不稳定。
其实所有的二叉树都有类似的局限性。

B树（多叉）

- 优点：
1 路数比较多，成功地将二叉树的瘦长结构优化成了矮胖结构，极大地减少了IO查询次数。
2 另外，叶节点都在同一层上，IO次数是比较稳定的。
其实B树已经比较理想了，那为啥MySQL索引没用B树呢？
- 不足：
1 每个非叶节点存储的是索引+数据，其中数据是一条记录，
试想一下，如果一条记录属性非常多，那么B树的每个节点能存储的数据就会变少，
面对海量的数据，最后B树就会从矮胖型变成瘦长型，IO次数势必无法得到优化
2 注意看叶节点层，不同节点不是连续的，当我们需要进行快速范围检索的时候，B树恐怕无法满足我们的要求。

B+树（多叉）

- 优点：
1 非叶节点仅存储索引，不存储数据，每个节点可存储较多索引值，因此可保证树是矮胖型的，IO次数得到优化
2 叶节点全部在同一层，IO次数十分稳定
3 叶节点保存索引+数据，并且增加了双循环链表的支持，可支持快速范围检索

为什么底层是B+树，现在知道原因了吧，这种设计是不是很巧妙？

三、MySQL索引实战

• 每个索引都对应一棵B+树，B+树分为好多层，最下边一层是叶子节点，其余的是内节点。所有用户记录都存储在B+树的叶子节点，所有目录项记录都存储在内节点。

• InnoDB存储引擎会自动为主键（如果没有它会自动帮我们添加）建立聚簇索引，聚簇索引的叶子节点包含完整的用户记录。

• 我们可以为自己感兴趣的列建立二级索引，二级索引的叶子节点包含的用户记录由索引列 + 主键组成，所以如果想通过二级索引来查找完整的用户记录的话，需要通过回表操作，也就是在通过二级索引找到主键值之后再到聚簇索引中查找完整的用户记录。

• B+树中每层节点都是按照索引列值从小到大的顺序排序而组成了双向链表，而且每个页内的记录（不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单链表。如果是联合索引的话，则页面和记录先按照联合索引前边的列排序，如果该列值相同，再按照联合索引后边的列排序。

• 通过索引查找记录是从B+树的根节点开始，一层一层向下搜索。由于每个页面都按照索引列的值建立了Page Directory（页目录），所以在这些页面中的查找非常快。

1.索引创建

create table student(
    `id` INT NOT NULL auto_increment PRIMARY KEY,
    `name` VARCHAR(10) NOT NULL,
    `gender` char(1) NOT NULL,
    `address` VARCHAR(100) NOT NULL,
    KEY id_name_gender(id,name,gender)
);

(1)自动创建索引

• 聚簇索引：

如果我们在创建表的时候定义了主键，InnoDB会自动为每张表创建一个聚簇索引，索引目录项为主键，底层B+树非叶子节点只存储目录项，而叶子结点存放完整记录。（不需要回表）

在MySQL中，如果没有显式定义主键或唯一索引，MySQL会自动为表创建一个隐藏的聚簇索引。这个隐藏的聚簇索引被称为聚簇索引（Clustered Index）或主键索引（Primary Key Index），它是按照表的插入顺序来组织数据的。

建议：创建表的时候指定自增主键。一来是自增主键非常适合用于B+树索引排序；二来是自增主键占用空间较小，每个B+树非叶子节点可以存储更多的目录项，使得对应的B+树更加的矮胖，每一次查询IO次数就会越少。

• 非聚簇索引：

此外，对于指定为UNIQUE的列也会被自动创建B+树索引。

(2)手动创建非聚簇索引

在上面我们手动创建了一个非聚簇索引，也可以说是一个联合索引id_name_gender(id,name,gender)，目录项的顺序是id、name、gender，因此，对应的索引B+树非叶子节点存储这三项目录项，叶子节点存储包含id、name、gender三项的记录。（需要回表）

(3)索引的代价

• 空间上的代价：

每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，一棵很大的B+树由许多数据页组成。

• 时间上的代价：

每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。

所以说，一个表上索引建的越多，就会占用越多的存储空间，在增删改记录的时候性能就越差。我们的目标是建立又好又少的索引。

以下是一些使用索引的场景：

2.B+树索引原则

（1）等值匹配且检索列与索引列一致

所谓的全值匹配，是指搜索条件中的列和索引列完全一致，比如创建了一个包含了col_a、col_b、col_c三列的联合索引，并且查询的时候where子句恰好包含了这三个列，并且都是等值连接，这种情况就是全值匹配。

• 问题：WHERE子句中的几个搜索条件的顺序对查询结果有啥影响么？

SELECT * FROM table_name WHERE col_a= 'a' AND col_b= 'b' AND col_c= 'c';

答案是，没有影响，因为MySQL中有查询优化器，会分析这些搜索条件并且按照可以使用的索引中列的顺序来决定先使用哪个搜索条件，后使用哪个搜索条件。

（2）左前缀原则：匹配左边的列

• 索引的匹配顺序按照的是创建索引时的字段顺序：

# id_name_gender(id,name,gender)完全生效
SELECT * FROM student WHERE id=16 and name='tracy' and gender='M';
# id_name_gender(id,name,gender)仅生效id部分，因为从name这里断掉了
SELECT * FROM student WHERE id=16 and gender='M';

（3）左前缀原则：匹配列前缀

• 字符串的匹配和排序也是从最左边第一个字符开始的：

现在再创建一个非聚簇索引：

CREATE INDEX name_nonclustered ON student(name);

这是生效情况：

# 能用上索引
SELECT * FROM student WHERE name LIKE 'tr%';
# 用不上，将会进行全表扫描
SELECT * FROM student WHERE name LIKE '%ay%';

（4）匹配范围值：单列匹配

如果我们已经创建了一个索引，在其中，name是索引的第一个列，那么以下sql是能命中索引的：

SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';

• 过程：
  • 通过B+树在叶子节点中找到第一条name值大于Asa的二级索引记录，读取该记录的主键值进行回表操作，获得对应的聚簇索引记录后发送给客户端。
  • 根据上一步找到的记录，沿着记录所在的链表向后查找（同一页面中的记录使用单向链表连接起来，数据页之间用双向链表连接起来）下一条二级索引记录，判断该记录是否符合name < 'Barlow’条件，如果符合，则进行回表操作后发送至客户端。
  • 重复上一步骤，直到某条二级索引记录不符合name <'Barlow’条件为止。

（5）匹配范围值：多列匹配

如果对多个列同时进行范围查找的话，只有对索引最左边的那个列进行范围查找的时候才能用到B+树索引：

select * from student where id>12 and id <16 and name > 'tracy' and gender = 'M';

• 对于联合索引id_name_gender(id,name,gender)来说，只能用到id列的部分，而用不到name、gender列的部分，因为只有id值相同的情况下才能用后面列的值进行排序，而这个查询中通过id进行范围查找的记录中可能并不是按照name列进行排序的，所以在搜索条件中继续以name列进行查找时是用不到这个B+树索引的。
• 但是，如果先通过id进行精确匹配，后面的name字段再进行范围匹配是能命中索引的。

（6）order by排序

• order by子句也能命中索引，并且也遵守左前缀原则；

• 顺序：如果order by子句中字段的顺序和创建的联合索引字段顺序一致，则直接能命中索引；order by中字段的顺序会影响索引的命中情况，这是和where不一样的地方。

• where子句和order by子句中字段匹配顺序：
  先where再order by
  where子句字段不讲究顺序
  order by子句中字段将会影响索引是否生效

• 升序降序：order by如果对不同字段的升序降序不一致，也会导致索引失效。

（7）group by分组

索引生效和失效情况完全和order by完全一致。

3.索引失效情况总结

• 不符合左匹配原则：
  sql语句中where子句、order by子句、group by字段的顺序不符合左匹配原则，比如联合索引断掉了，或者字符串匹配时未确定最左边的字符。

• order by中asc和desc混用： 部分字段asc排序，另一些desc排序，会索引失效。MySQL设计人认为混用asc和desc会导致索引的匹配异常麻烦，还不如直接文件排序（在内存中或者磁盘上进行排序的）来的快。

• order by中包含非同一个索引的列：
  MySQL无法同时使用多个索引来满足排序的需求，只能使用一个索引。

• 对字段使用了函数，或者运算：
  where sum(mount) > 100
  where mount + 2 >3 # 写成mount>3-2可以避免

四、索引最佳实践

• 索引的设计要契合左前缀原则，原因上面都说了。

• 为了尽可能避免回表查询，建议把经常select的字段包含到联合索引中去，也就是使用覆盖索引（需要查的字段包含到索引中去）。

• 尽可能地不要使用select *，原因同上。

• 只为区分度大的列创建索引，所谓的区分度大就是可能的值尽可能多，比如像性别这样的字段其实没必要索引，因为它只有两个可能的值，排序了也没什么太大的意义。

• 只为数据类型小的字段创建索引，不然索引的非叶节点能装的目录项很有限，会导致B+树从矮胖型变成瘦高型，IO性能不好。

• 不要滥用索引，毕竟每次写入表之后索引都需要维护。

参考

博客1
博客2
博客3