MySql实战学习笔记——索引补充篇

发表于 2020-03-03 更新于 2022-06-25 阅读次数：

上一篇博客记录了MySql实战基础篇的一些总结，这一篇在对其中索引进行补充。

普通索引和唯一索引

普通索引和唯一索引主要的区别是，如果如果某字段是唯一索引，那么这个字段不可能出现相同值。接下来我们以这个不同点介绍下在查询和插入时候两个索引的不同。（这里只针对非主键索引）

查询过程

我们还是以这张图为例，假设k值都不重复：

假设执行的查询语句为select id from T where k=5;，会搜索到右下角数据页，然后二分定位到记录。

如果是普通索引，则在找到5这条记录之后，会往后判断一次k是否还是5，如果是继续返回，不是则结束循环
如果是唯一索引，则在找到5这条记录之后直接返回

这两种性能差距是微乎其微的，因为InnoDB是以页为单位读取，所以判断下一个是否相等不在内存的几率很低（只有一种情况，就是下一个值在下一个数据页）。普通索引的性能开销主要在CPU用于判断下一个值是否相等情况，而这种情况的消耗是微乎其微的。

更新过程

change buffer

在讲更新操作的时候，需要提到InnoDB在更新时候做的一个优化：InnoDB在更新某个数据页（非主键索引）的时候发现数据页不在内存，则会把更新操作缓存在change buffer中，这样就省了从硬盘加载数据页的过程。之后如果某次查询操作需要用到该数据页，则在读取数据页之后，应用change buffer中相关操作。

change buffer也是会持久化到硬盘。
change buffer除了访问的时候会进行merge，后台也会有线程定期merge。同时数据库关闭的时候也会执行merge
减少磁盘的随机读（读取数据页），提升更新语句的性能。同时减少占用的内存，提高内存使用率

change buffer应用场景：写多读少的情况，例如账单类，日志类系统。反之，如果写完之后马上会查询，则应该避免使用change buffer，因为写入change buffer之后马上查询会立即触发，随机读入及merge操作，白白多了一个merge的性能损耗。

不同索引类型更新过程

如果要在表T中插入(4,400)这条记录，针对k的索引数中会有两种情况：
第一种情况是该数据页在内存：

如果k是普通索引，找到3和5之间插入这个值（如果此时数据页满了，会怎么做？）
如果k是唯一索引，找到3和5之间，判断到没有冲突，插入这个值

第二种情况是该数据页不在内存：

如果k是普通索引，记录到change buffer中（同样如果数据页原本就满了，会怎么做？）
如果k是唯一索引，需要将数据页读入，判断到没有冲突，插入这个值

可以看到唯一索引在数据页不在内存的情况下会有一次随机读操作。这是两种索引差别比较大的地方

如何选择唯一索引和普通索引

如果逻辑层能满足列唯一性的情况下，尽量选择普通索引。
如果逻辑层不能满足为唯一性的话，则业务需求优先，选择唯一索引

change log 和 redo log区别

最主要的区别就是：change log优化了数据页随机磁盘读，redo log优化了数据页随机磁盘写。可以看下面对于sql：insert into t(id,k) values(id1,k1),(id2,k2);的示例：

假设page1当前在内存，page2不在内存，下面两条虚线箭头对应的是相应数据的落地，暂时不管。

更新page1的时候，直接更新内存，然后在redo log中插入更新page1的操作
更新page2的时候，发现没有在内存，插入到change buffer中，并且同样在redo log中插入操作

关于这一块宕机如何恢复，这一讲的置顶留言部分有很好的分析。
之后的话更新语句就返回了。
接下去我们执行select * from t where k in (k1, k2)操作：

page1当前还在内存，可以直接返回结果
page2当前不在内存，读取相应的数据页，并merge change buffer的操作，把操作写入到redo log中，并直接返回结果。写入redo log之后，就按照redo log的脏页流程来做了。

MySql选错索引

MySql优化器可能在某些情况下会错误的选择索引。可以通过explain来查看索引的使用情况。

由于索引统计信息不准确导致的问题，可以用analyze table来解决
对于其他优化器误判情况，可以使用force index强行指定索引，也可以通过修改语句来引导优化器，还可以通过增删索引来绕过这个问题

这一节讲解使用例子讲解了为何选错索引，过程比较复杂不太好总结，以后如果遇到了回去再查看下

如何给字符串加索引

MySql实战基础篇介绍了索引的存储结构，对于字符串列建索引有两种方式，例如：

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

第一种是使用整个email字段作为索引
第二种是使用email前6位作为索引

使用全字段作为索引

优点：查询效果好，并且可以使用覆盖索引
缺点：占用空间大

使用部分字段作为索引

优点：定义好长度的情况下，节省空间的同时，不会增加过多查询消耗
缺点：长度比较难确定，不能使用覆盖索引

如何定义长度

mysql> select 
  count(distinct left(email,4)）as L4,
  count(distinct left(email,5)）as L5,
  count(distinct left(email,6)）as L6,
  count(distinct left(email,7)）as L7,
from SUser;

这条语句打印了email在不同长度值的个数，可以与行数比较，选择个合适的比例进行

左前缀大部分相同情况

对于左前缀大部分相同情况的字符串（例如身份证），可以通过下面两种方式：

倒序存储，例如因为身份证后几位相同概率不大，通过倒序之后存储就可以满足取较短长度的索引了
增加hash字段，例如单独增加一列int值来存储身份证hash过后的值，对新增的这列加索引来解决查询问题

这两种方式只能支持等值查找，不支持范围查找。这两种方式区别：

占用空间来看的话，需要根据索引前缀长度来判断
CPU消耗的话，hash字段会略高点，因为会使用hash函数
查询性能，使用hash字段会更稳定点