mysql索引总结 mysql

数据结构

innodb使用了b+树作为索引
主键索引的叶子节点存的是整行数据，也被称为聚簇索引
非主键索引的叶子节点存的是主键的值，也被称为二级索引
基于非主键索引的查询，先搜索树得到主键的值，再到主键的索引树搜索一次，这个过程称为回表，要多扫描一棵索引树，在应用中应该尽量使用主键查询
索引维护
B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护
当插入一个中间值，可能会涉及到数据页的挪动，包括页分裂、页合并
自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的： NOT NULL PRIMARY KEY AUTO_INCREMENT，它每次插入都是追加操作，不涉及到挪动其他记录，不会触发叶子节点分裂
有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高（性能）
主键使用自增主键，长度比使用字符串更小，普通索引的叶子节点更小，占用的空间也更小（存储空间）
当业务场景是只有一个索引、且必须是唯一索引，可以使用业务字段做主键

覆盖索引

在查询中，索引已经覆盖了查询所需的所有字段，称为覆盖索引
覆盖索引可以减少树的搜索次数，显著提升查询性能，使用覆盖索引是一个常见的优化手段
最左前缀原则
- 最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符
- 索引下推优化，如果一个查询条件包含覆盖索引的最左字段与其余字段，查询时会通过覆盖索引去比对其余字段是否满足要求，若不满足则不会去回表
唯一索引和普通索引
对于普通索引来说，查找到满足条件的第一个记录后，需要查找下一个记录，直到碰到第一个不满足普通索引条件的记录
对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索
InnoDB 的数据是按数据页为单位来读写的，对于整型字段，一个数据页可以放近千个 key，因此出现这种情况的概率会很低。所以，我们计算平均性能差异时，仍可以认为这个操作成本对于现在的 CPU 来说可以忽略不计

change buffer

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了
在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性
将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作
显然，如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率

Change Buffer 的相关设置上面就是写缓存（Change Buffer）的相关知识，写缓存（Change Buffer）我们也是可以使用命令参数来控制，MySQL 数据库提供了两个对写缓存（Change Buffer）的参数。
Change Buffer 参数

innodb_change_buffer_max_size

innodb_change_buffer_max_size 表示 Change Buffer 最大大小占 Buffer Pool 的百分比，默认为 25%。最大可以设置为 50%。

innodb_change_buffering

innodb_change_buffering 参数用来控制对哪些操作启用 Change Buffer 功能，默认是：all
change buffer 适用场景 change buffer 并不是适用于所有场景，以下两种情况不适合开启change buffer ：

数据库都是唯一索引
如果数据库都是唯一索引，那么在每次操作的时候都需要判断索引是否有冲突，势必要将数据加载到缓存中对比，因此也用不到 Change Buffer
写入一个数据后，会立刻读取它
写入一个数据后，会立刻读取它，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用

以下几种情况开启 Change Buffer，会使得 MySQL 数据库明显提升：

数据库大部分是非唯一索引
业务是写多读少
写入数据之后并不会立即读取它

change buffer和redo log

写请求
如果要插入的行所对应的页在内存中，直接更新内存
如果要插入的行所对应的页不在内存中，就在内存的change buffer区域，记录下要往该页插入该行的信息
将上述操作记录在redolog中
以上操作写了两次内存（直接更细内存、更新change buffer），写了一次磁盘（两次内存操作合起来写一次redolog到磁盘，并且是顺序写）
读请求
读内存中存在的页所对应数据的时候，直接从内存返回。
读内存中不存在的页所对应数据，存在于change buffer的数据的时候，需要把该页从磁盘读入内存中，然后应用 change buffer 里面的操作日志，生成一个正确的版本并返回结果。可以看到，直到需要读内存不存在的页的时候，这个数据页才会被读入内存。因为有了change buffer ，写之前，需要更新的行不在数据页，并不需要将硬盘里的数据读到数据页，只要写change buffer 。等下次读数据时，从硬盘读出数据页，然后利用change buffer 去变更数据页

redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。

索引优化器

MySQL 在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数
这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好
我们可以使用 show index 方法，看到一个索引的基数
- MySQL通过采样统计得到索引的基数
- 采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数
- 而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计
explain的结果中，rows 这个字段表示的是预计扫描行数
如果使用索引 a，每次从索引 a 上拿到一个值，都要回到主键索引上查出整行数据，这个代价优化器也要算进去的
analyze table t 命令，可以用来重新统计索引信息，如果发现 explain 的结果预估的 rows 值跟实际情况差距比较大，可以采用这个方法来处理
索引选择异常和处理
采用 force index 强行选择一个索引
考虑修改语句，引导 MySQL 使用我们期望的索引
字符串索引
MySQL 是支持前缀索引的，可以定义字符串的一部分作为索引。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串
比如，这两个在 email 字段上创建索引的语句：

mysql> alter table SUser add index index1(email); mysql> alter table SUser add index index2(email(6));

第一个语句创建的 index1 索引里面，包含了每个记录的整个字符串；而第二个语句创建的 index2 索引里面，对于每个记录都是只取前 6 个字节
由于 email(6) 这个索引结构中每个邮箱字段都只取前 6 个字节（即：zhangs），所以占用的空间会更小，这就是使用前缀索引的优势。但，这同时带来的损失是，可能会增加额外的记录扫描次数
如果使用的是 index1（即 email 整个字符串的索引结构），执行顺序是这样的：
1. 从 index1 索引树找到满足索引值是’zhangssxyz@xxx.com’的这条记录，取得 ID2 的值；
2. 到主键上查到主键值是 ID2 的行，判断 email 的值是正确的，将这行记录加入结果集；
3. 取 index1 索引树上刚刚查到的位置的下一条记录，发现已经不满足 email='zhangssxyz@xxx.com’的条件了，循环结束。
4. 这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。
如果使用的是 index2（即 email(6) 索引结构），执行顺序是这样的：
1. 从 index2 索引树找到满足索引值是’zhangs’的记录，找到的第一个是 ID1；
2. 到主键上查到主键值是 ID1 的行，判断出 email 的值不是’zhangssxyz@xxx.com’，这行记录丢弃；
3. 取 index2 上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出 ID2，再到 ID 索引上取整行然后判断，这次值对了，将这行记录加入结果集；
4. 重复上一步，直到在 idxe2 上取到的值不是’zhangs’时，循环结束。
5. 在这个过程中，要回主键索引取 4 次数据，也就是扫描了 4 行。通过这个对比，你很容易就可以发现，使用前缀索引后，可能会导致查询语句读数据的次数变多
对于这个查询语句来说，如果你定义的 index2 不是 email(6) 而是 email(7），也就是说取 email 字段的前 7 个字节来构建索引的话，即满足前缀’zhangss’的记录只有一个，也能够直接查到 ID2，只扫描一行就结束了
使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本
在建立索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少
如果使用 index1（即 email 整个字符串的索引结构）的话，可以利用覆盖索引，从 index1 查到结果后直接就返回了，不需要回到 ID 索引再去查一次。而如果使用 index2（即 email(6) 索引结构）的话，就不得不回到 ID 索引再去判断 email 字段的值。即使你将 index2 的定义修改为 email(18) 的前缀索引，这时候虽然 index2 已经包含了所有的信息，但 InnoDB 还是要回到 id 索引再查一下，因为系统并不确定前缀索引的定义是否截断了完整信息
参考资料【mysql索引总结】极客时间，mysql实战45讲