MySQL 学习笔记【索引篇】

本文为极客时间《Mysql实战45讲》的学习笔记，并结合《高性能Mysql》，梳理了索引相关的知识点，总结了一些常见问题，并记录了一些比较实用的方法。

hpgvrbco7x.png

基础问题

索引是一种数据结构。官方描述为：索引（Index）是帮助MySQL高效获取数据的数据结构。因此我们针对索引的使用和优化，本质上也是基于一种特殊的数据结构进行的优化。总结下innodb的索引特点：

首先，从类型上可以分为主键索引与普通索引。普通索引也叫二级索引，类型上包括唯一索引，单列索引，联合索引，全文索引等。对于主键索引而言，他在叶子节点上存储的是索引+全部数据，而普通索引的叶子节点存储的是数据的主键索引的值。

主键索引这种索引+数据的组织方式也叫做聚簇索引，下一节会详细描述。 知识点补充： R-Tree 索引(空间索引)：空间索引是MyISAM的一种特殊索引类型，主要用于地理空间数据类型。Full-text (全文索引)：全文索引是MyISAM的一种特殊索引类型，主要用于全文索引，InnoDB从MYSQL5.6版本提供对全文索引的支持。

当我们执行一条SQL命令通过主键ID 查询一条数据时，直接将查到的数据返回。但是当执行一条语句查到的是普通索引时，就需要拿到这条数据的主键索引的值，然后再去主键索引上查一遍，拿到数据。这个过程叫做回表。比如下面这条SQL：

SELECT * FROM TABLE WHERE k > 16; 

如果是范围查询，一次查n行数据出来，正常情况下要回n次表，这样的开销是难以忍受的。因此，MySQL会有一些优化：索引下推，MRR（Multi-Range Read）。

然后，当我们使用联合索引时，假设我们的索引为 （UID,NAME,AGE），而我们要查的数据点为（UID，NAME）时，就不会触发回表，因为当前索引的数据已经包含我们要查询的数据点，不需要再去主键索引查找，这种方式被称为覆盖索引。

覆盖索引使我们常用的优化方案之一，但是覆盖索引也是有代价的：需要占用更多的数据存储空间，会增加维护成本。

同样的，当我们在使用联合索引时，通常要遵循最左前缀原则。简而言之：最左字段和最左字符串都会用到最左原则。比如，一个字段为STRING，查询时，使用LIKE 'vp_%',依然可以用到索引。

最后，索引的叶子节点维护的存储单元是数据页，也就是说，每次从磁盘读取数据到内存是以页（page）为单位，一页数据根据其大小，可以存储多行数据。在数据页中，数据行以有序数组的方式存储，查找方式类似二分法。当一个数据页的空间不足时，就会触发页分裂，导致插入效率降低，存储空间使用率降低。相反，当一个数据页被删除掉很多数据时，也会触发页合并，过程相当于分裂的逆流程。

此处会引出一个问题：为什么innodb引擎的表一定要有一个自增的主键ID？

索引确实可以加快查询的效率，但是他也有两个较大的开销：第一，维护起来成本很高；第二，需要占用很多存储空间。

数据页

数据页是InnoDB管理存储空间的基本单位，一个页的大小一般是16KB(该数值会影响N叉树的N)。

叶子节点数据.jpg

这是叶子节点的具体构成，他们的指针指向的是一个数据页。数据页的结构如下

15085536-492f495ba293c8e3.png

数据页的字段说明（https://blog.csdn.net/itguangit/article/details/100932004）

我们在此只说一下innodb数据页的一些结论：

• 数据页的基础结构：
  1. File Header，表示页的一些通用信息，占固定的38字节。
  2. Page Header，表示数据页专有的一些信息，占固定的56个字节。
  3. Infimum + Supremum，两个虚拟的伪记录，分别表示页中的最小和最大记录，占固定的26个字节。
  4. User Records：真实存储我们插入的记录的部分，大小不固定。
  5. Free Space：页中尚未使用的部分，大小不确定。
  6. Page Directory：页中的某些记录相对位置，也就是各个槽在页面中的地址偏移量，大小不固定，插入的记录越多，这个部分占用的空间越多。
  7. File Trailer：用于检验页是否完整的部分，占用固定的8个字节。
• 每个记录的头信息中都有一个next_record属性，从而使页中的行数据串联成一个单向有序链表。
• 每个数据页的File Header部分都有上一个和下一个页的编号，所以所有的数据页会组成一个双向链表
• 数据页内默认使用二分法查询。
• 数据从内存同步到磁盘时，都会校验首部和尾部的LSN值。

聚簇索引

聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。

聚簇索引与非聚簇索引.jpg

聚簇索引是一种数据的存储方式，它的数据行只存放在索引（B+树）的叶子中，内部节点不存放数据。聚簇索引是数据的一种组织方式，并不是逻辑上能用上的索引。在(INNODB)中，索引的组织形式都是聚簇索引。简单的说，就是将同一类的数据集中在一块存储中(物理地址连续)，在排序或范围查找时，依靠顺序IO可以很快获得数据。在非聚簇索引下，往往因为物理地址不连续，需要多次I0才能拿到数据。

聚簇索引在查询时可以加快查询效率，但维护数据时需要进行额外的操作。当我们随机向聚簇索引中写入新的数据时，此时每次都要先找到他的头部数据点，然后插入数据，再把尾部的数据一次向后移动，开销极大。

无序插入.png

当顺序写入数据，即每次写入的数据索引都比之前的大（换句话说，就是自增主键）。这时，事情就变得简单多了，只需要在之前的数据后边追加新的数据，不需要移动旧数据。这样一来会大大加快写入的效率。

顺序插入.png

这便是为什么innodb一定要有一个自增的主键ID的原因之一。即使我们没有为数据表设置一个自增主键，innodb引擎也会默认为该表创建一个8位的ROWID，来作为索引的主键。

聚簇索引会占用非常的存储。尤其是当主键的长度很长的时候，会占用较多的存储空间。此外，聚簇索引在全表扫描，count的时候，会比较慢。

change_buffer和索引下推

MySQL有一层叫做优化器，我们写得SQL语句每次都要经过优化器的调整，由它决定用不用索引，用哪个索引。很多时候我们会发现一条看似很平常的语句，查询效率非常慢，排查下来，发现他要么没用索引，要么用错了索引。出现这种情况，通常跟优化器选择逻辑有关。

而优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。 扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断

优化器可以通过索引的基数（cardinality）来判断的需要扫描的行数。简单来说，一个索引上的不同值越多，区分度也越大。即基数越大，区分度越好。基数，则是通过取样统计，获得的。这个值不太准，有些时候并不是很可靠，而且会有滞后性，因此会出现选错索引的问题。

InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。

这个配置可以通过设置参数 innodb_stats_persistent 的值来选择：

• 设置为 on 的时候，表示统计信息会持久化存储。这时，默认的 N 是 20，M 是 10。
• 设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的 N 是 8，M 是 16。

总体来讲，优化器在选择索引的时候会主要考虑的是扫描行数，也会考虑回表的开销等。需要说明的是，对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器会放弃走树搜索功能。同时，字段类型的隐式转化，字符集不同都会导致优化器不走索引。

对字段进行函数操作，不会走索引，但是对数据操作会走索引。

#不会走索引
SELECT * From all_info WHERE round(uid)=100
#会走索引
SELECT * From all_info WHERE uid=100+100

change_buffer

我们前面说过，InnoDB 的数据是按数据页为单位来读写的。

对于一个唯一索引（包括主键）而言，不能违反数据一致性，因此一定会找到这个值，确认其是否存在，这是这页数据已经在内存中，直接执行写入操作。

对于一个普通索引而言，如果数据页刚好在内存里，那么直接更新。如果不在内存，这时，引擎不会再多读一次磁盘，而是把这个操作记录在change buffer 中。如果后续有查询到本页数据，则先执行merge,将change buffer中的操作写入到数据页。

需要说明的是，虽然名字叫作 change buffer，实际上它是可以持久化的数据。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作

change buffer 的优点很明显，可以减少磁盘IO，将多次写操作合并为一次磁盘IO。因此适用于写多读少的业务场景中。但是如果更新后立即跟查询操作，不仅不会降低磁盘IO还要增加维护，显然不合适。同时change buffer 用的是 buffer pool 里的内存，因此不能无限增大。

change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

回表，索引下推，MRR

我们前面讲过回表的问题，当我们通过普通索引k 来查找数据时，需要先在k上找到数据的主键ID，然后再回到主键索引上找到这行数据。如果只是找一行数据，还好，如果很多行呢？

这个时候innodb会通过MRR（Multi-Range Read），将查询到的主键ID进行一次排序，然后在主键索引上顺序读取多行数据，这样效率会快很多。

在MySQL 5.6 中，引入了索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

select * from tuser where name like '张%' and age=10 and ismale=1;

如下图：

无索引下推.jpg

索引下推.jpg

索引下推会先通过现有的索引过滤掉不符合要求的数据，以减小回表次数。通过和MRR的配合，查询效率会快很多。

索引排序

SELECT a,b,c,d From x WHERE a='s' order by b limit 10; 
#对于类似场景的最佳索引为：ab

全字段排序的流程

1. 初始化 sort_buffer，放入 a,b,c,d 这几个字段；
2. 从索引 a 找到第一个满足 a=‘s’的主键 id；
3. 到主键 id 索引取出整行，取字段的值，存入 sort_buffer 中；(回表)
4. 从索引 a 取下一个记录的主键 id；
5. 重复步骤 3、4 直到 leagud_id 的值不满足查询条件为止；
6. 对 sort_buffer 中的数据按照字段 b 做快速排序；
7. 按照排序结果取前 10 行返回给客户端。

rowid排序的流程

当字段数据特别长，超过了max_length_for_sort_data的值，这时候会采用rowid排序。

1. 初始化 sort_buffer，确定放入两个字段，即 b 和 id；
2. 从索引 a 找到第一个满足 a=‘s’的主键 id；
3. 到主键 id 索引取出整行，取 b、id 这两个字段，存入 sort_buffer 中；
4. 从索引 a 取下一个记录的主键 id；
5. 重复步骤 3、4 直到 a 的值不满足查询条件为止；
6. 对 sort_buffer 中的数据按照字段 b 进行排序；
7. 遍历排序结果，取前 10 行，并按照 id 的值回到原表中取出字段返回给客户端。

sort_buffer

MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。

sort_buffer的大小可以通过sort_buffer_size的值来配置。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

可以通过以下代码来看以下是否使用到文件辅助排序（代码来自极客时间）。外部排序采用的是归并算法。将数据分为N块，对于每块数据排序，最后再合并。

/* 打开optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 

/* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000; 

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

/* @b保存Innodb_rows_read的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 计算Innodb_rows_read差值 */
select @b-@a;

总结

一句话总结

索引是一种特殊的数据结构，可以加快数据库的查询和排序等操作。数据库对索引的优化思路主要是：以空间换时间，尽可能的减少随机IO操作。能用内存操作，尽量用内存，尽可能少的读磁盘。

优化总结

从开发使用的角度来看，总结如下：

全值匹配我最爱，最左前缀要遵守。带头大哥不能死，中间兄弟不能断。索引列上少计算，范围之后全失效。Like百分写最右，覆盖索引不写*。不等空值还有OR，索引影响要注意。VAR引号不能丢，SQL优化有诀窍。

在MySQL选错索引的时候可以采用 force index 指定一个索引，也可以略微修改语法，引导优化器选择索引。

索引的排序成本是非常高的，因为数据本身是无序的，每次排序都需要建一张临时表，并在其上进行排序。尽量通过覆盖索引来进行排序，这样就不需要进行额外的排序操作。

在设计表的时候尽量贴合业务，创建索引。尽可能使常用的SQL能够用到索引。

一些【有趣】的问题

change buffer 和 redo log 有什么区别？简而言之：redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。

在change buffer中有此行记录的情况下，再次更改，是增加一条还是原地修改? 增加一条数据。

“N叉树”的N值在MySQL中是可以被人工调整的么？ 5.6以后可以通过page大小来间接控制

count()的字段怎么取？count(字段)<count(主键id)<count(1)≈count(*)