【MySQL】之索引与索引优化

目录

• 一、MySQL 索引介绍
• 二、MySQL 索引结构
• 三、索引相关知识点
• 四、索引优化建议

一、MySQL 索引介绍

---

1、索引的定义

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。

说白了，索引的本质就是一种特殊的数据结构。索引的目的在于提高查询效率，就好像我们平常使用的字典一样。我们可以简单理解为索引是排好序的快速查找的数据结构：

2、索引与数据表的联系

我们平常所说的 MySQL 索引，如果没有特别指明，都是指B+树结构组织的索引。当然，除了B+树这种类型的索引之外，还有哈稀索引(hash index)等。

索引的节点存在一个指针，记录对应的数据行所在的物理地址，如图所示：

左边是数据表，Col1 是数据记录的物理地址，Col2 是记录的数据，为了加快 Col2 的查找，右边索引（B+树）的每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以通过索引快速获取到相应数据，从而实现快速检索的目的。

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

3、索引的优缺点

优点

• 极大地提高数据检索的效率，降低数据库的 IO 成本；
• 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗；

缺点

• 由于索引实际上也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的；
• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE、DELETE 操作。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息；
• 索引只是提高效率的一个因素，如果你的 MySQL 有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句；

3、MySQL索引分类

MySQL 的索引种类主要有以下几种：

• 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引；
• 唯一索引：索引列的值必须唯一，但允许有空值；
• 复合索引：即一个索包含多个列；

更多关于索引分类的信息可以参考我的另一篇博客：【MySQL】之字段约束类型

二、MySQL 索引结构

---

1、MySQL 的索引结构种类

• B/B+Tree 索引
• Hash 索引
• full-text 全文索引
• R-Tree 索引

2、B+Tree 索引的检索原理

B+Tree索引的检索原理图如下：

这是一颗典型的 B+ 树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），比如磁盘块 1 包含数据项 17 和 35，包含指针 P1、P2、P3。P1 表示小于 17 的磁盘块，P2 表示在 17 和 35 之间的磁盘块，P3 表示大于 35 的磁盘块。

真实的数据存在于叶子节点，即最下面的数据块：3、5、9、10、13、15 等。非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如 17、35 并不真实存在于数据表中。

B+ 树的查找过程如下：

1. 如果要查找数据项为 29，那么首先会把磁盘块 1 由磁盘加载到内存，此时发生一次 IO；
2. 在内存中用二分查找确定 29 在 17 和 35 之间，锁定磁盘块 1 的 P2 指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块 1 的 P2 指针的磁盘地址把磁盘块 3 由磁盘加载到内存，发生第二次 IO；
3. 29 在 26 和 30 之间，锁定磁盘块 3 的 P2 指针，通过指针加载磁盘块 8 到内存，发生第三次IO，同时内存中做二分查找找到 29，结束查询，总计三次 IO。

真实的情况是，3 层的 B+ 树可以表示上百万的数据，如果上百万的数据查找只需要三次 IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次 IO，那么总共需要百万次的 IO，显然成本非常非常高。

三、索引相关知识点

---

1、聚集索引和非聚集索引

MySQL 底层使用 B+ 树来存储索引，所以数据均存在叶子节点上。对于 InnoDB 而言，主键索引和行记录是存储在一起的，因此叫做聚集索引（clustered index）。除了聚集索引，其他所有都叫做非聚集索引（secondary index），包括普通索引、唯一索引等。

在 InnoDB 中，只存在一个聚集索引：

• 若表存在主键，则主键索引就是聚集索引；
• 若表不存在主键，则会把第一个非空的唯一索引作为聚集索引；
• 否则，会隐式定义一个 RowId 作为聚集索引。

我们以下图为例：

假设现在有一个表，存在 id、name、age 三个字段，其中 id 为主键，因此 id 为聚集索引，name建立索引为非聚集索引。关于 id 和 name 的索引，如图中的 B+ 树，可以看到，聚集索引的叶子节点存储的是主键和行记录，非聚集索引的叶子节点存储的是主键。

2、回表查询

从上面的索引存储结构我们可以看到，在主键索引树上，通过主键就可以一次性查出我们所需要的数据，速度很快。这很直观，因为主键就和行记录存储在一起，定位到了主键就定位到了所要找的包含所有字段的记录。

但是对于非聚集索引，如上面的右图，我们可以看到，需要先根据name所在的索引树找到对应主键，然后通过主键索引树查询到所要的记录，这个过程叫做回表查询。

3、索引覆盖

上面的回表查询无疑会降低查询的效率，那么有没有办法让它不回表呢？有，那这就是索引覆盖。

所谓索引覆盖，就是在使用这个索引查询时，使它的索引树的叶子节点上的数据可以覆盖你查询的所有字段，就可以避免回表了。我们回到一开始的例子，我们建立的 (a, b, c) 的联合索引，因此当我们查询的字段在 b、c、d 当中的时候，就不需要回表查询，只需要查看一次索引树，这就是索引覆盖。

四、索引优化建议

---

1、是否需要创建索引的场景

1-1、需要创建索引的情况：

• 主键自动建立唯一索引；
• 频繁作为查询条件的字段应该创建索引；
• 查询中与其它表关联的字段，外键关系建立索引；
• 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度；
• 查询中统计或者分组字段；
• 单值和复合索引的选择问题：在高并发下倾向创建组合索引。

1-2、不需要创建索引的情况：

• 表记录太少；
• 经常增删改的表：提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件；
• 频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录还会更新索引，加重了IO负担；
• Where 条件里用不到的字段不创建索引；
• 数据重复且分布平均的表字段，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

2、索引优化建议

2-1、尽可能全值匹配

尽量使用 = 来全值匹配，使用不等于(!= 或者<>)的时候无法使用索引进而导致全表扫描。使用 is null、is not null 也无法使用索引。

对于字符串来说，全值匹配时命中索引几率是最高的，如果要对字符串使用 like 模糊查询，不要把通配符 % 放在开头（'%abc...'），这会导致索引失效，进而变成全表扫描。

2-2、不要在索引列上做运算

如果查询条件中含有函数或表达式，将导致索引失效而进行全表扫描。

2-4、遵循最左匹配原则

对于复合索引，应该遵循最左匹配原则，更多知识可以参考：【MySQL】之联合索引与最左匹配原则。

2-5、尽量避免使用 OR 操作符

查询条件如果使用了 OR 操作符，那么 MySQL 在执行查询时，需要分别使用到与 OR 操作符相关的多个子查询语句，这不仅就意味着 MySQL 需要执行多次查询增加数据库的负担，而且，如果 OR 操作符涉及的多个字段没有建立合适的索引，那么 MySQL 就需要对所有数据进行全表扫描，这样就会导致索引失效。

为了避免索引失效，有以下几点建议：

• 尽量避免在查询语句中使用 OR 操作符。
• 为涉及到 OR 操作符的多个字段建立合适的索引。
• 使用 UNION 操作符代替 OR 操作符，这样可以避免 OR 操作带来的性能问题。

2-6、不要使用含有 NULL 值的列作为索引

只要列中包含有 NULL 值都不应该被包含在索引中，复合索引中只要有一列含有 NULL 值，那么这一列对于此复合索引就是无效的。所以在数据库设计时不要让字段的默认值为 NULL。

2-7、尽量选择区分度高的列作为索引

区分度的公式是 count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是 1，而一些状态、性别字段可能在大数据面前区分度就是 0。一般需要 join 的字段都要求区分度 0.1 以上，即平均 1 条扫描 10 条记录。

2-8、尽可能覆盖索引

上面我们也讲解了什么是覆盖索引：如果一个索引包含所有需要的查询的字段的值，我们称之为覆盖索引。覆盖索引是非常有用的工具，能够极大的提高性能。因为只需要读取索引，而无需读表，极大减少数据访问量，这也是不建议使用 Select * 的原因。

3、关键字优化建议

3-1、ORDER BY 关键字优化

ORDER BY子句，尽量使用 Index 方式排序，避免使用 FileSort 方式排序。MySQL 支持二种方式的排序：FileSort（文件排序）和 Index（索引排序）。Index 效率高，因为它指示 MySQL 扫描索引本身完成排序，而 FileSort 方式效率较低。

ORDER BY 满足以下两情况，会使用 Index 方式排序：

• ORDER BY 语句使用索引最左前列；
• 使用 Where 子句与 Order BY 子句条件列组合满足索引最左前列。

尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀，如果不在索引列上，filesort 有两种算法：双路排序和单路排序

• 双路排序：MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据，读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
• 单路排序：从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间，因为它把每一行都保存在内存中了。

优化策略：

• 增大max_length_for_sort_data参数的设置：不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的
• 增大sort_buffer_size参数的设置：提高这个参数， 会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率

3-2、GROUP BY 关键字优化

• group by 实质是先排序后进行分组，遵照索引建的最佳左前缀；
• 当无法使用索引列，增大 max_length_for_sort_data 参数的设置和增大 sort_buffer_size 参数的设置；
• where 高于 having，能写在 where 限定的条件就不要去 having 限定了。