MySQL索引(二)

一般索引和唯一索引

我们来比较一下在不同过程一般索引和唯一索引的区别。

查询阶段：

例如：select id from T where k=5并且要在k上面加索引。

结构为：

image-20230416151418723
  • 对于普通索引来说，查找到满足条件的第一个记录(5,500)后，需要查找下一个记录，直到碰到第一个不满足k=5条件的记录。
  • 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

但是因为InnoDB的数据是按数据页为单位来读写的，所以多读几条数据对性能的影响是微乎其微的（数据在下一页的情况很少很少）。

更新过程：

为了说明普通索引和唯一索引对更新语句性能的影响这个问题，需要先了解一下change buffer。

change buffer：

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。

但是change buffer不适用于更新完马上会做查询的业务场景。

普通索引和唯一索引下的change buffer：

  • 唯一索引因为要判断数据的唯一性，必须将数据读到内存中，这样没必要使用change buffer了。
  • 普通索引更新会使用change buffer，直接将更新记录在change buffer中之后查询在进行merge操作。

change buffer也会持久化到系统表空间（ibdata1）中，以及redo log中也会记录这个语句

小结

普通索引和唯一索引应该怎么选择。其实，这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，我建议你尽量选择普通索引。（因为如果唯一索引更新的数据没有在内存中就需要进行磁盘IO远比change buffer慢）

前缀索引

MySQL是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。

比如，这两个在email字段上创建索引的语句：

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

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

实际上，我们在建立索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。因此，我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀。

前缀索引对覆盖索引的影响

你先来看看这个SQL语句：select id,email from SUser where email='zhangssxyz@xxx.com';

如果使用index1（即email整个字符串的索引结构）的话，可以利用覆盖索引，从index1查到结果后直接就返回了，不需要回到ID索引再去查一次。而如果使用index2（即email(6)索引结构）的话，就不得不回到ID索引再去判断email字段的值。

也就是说，使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。

遇到前缀的区分度不够好的情况时，我们要怎么办呢？

  • 第一种方式是使用倒序存储。

    如果你存储身份证号的时候把它倒过来存，每次查询的时候：where id_card = reverse('input_id_card_string');

  • 第二种方式是使用hash字段。

    你可以在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。然后每次插入新记录的时候，都同时用crc32()这个函数得到校验码填到这个新字段。由于校验码可能存在冲突，也就是说两个不同的身份证号通过crc32()函数得到的结果可能是相同的，所以你的查询语句where部分要判断id_card的值是否精确相同。

两者区别：

  1. 从占用的额外空间来看，倒序存储方式在主键索引上，不会消耗额外的存储空间，而hash字段方法需要增加一个字段。当然，倒序存储方式使用4个字节的前缀长度应该是不够的，如果再长一点，这个消耗跟额外这个hash字段也差不多抵消了。
  2. 在CPU消耗方面，倒序方式每次写和读的时候，都需要额外调用一次reverse函数，而hash字段的方式需要额外调用一次crc32()函数。如果只从这两个函数的计算复杂度来看的话，reverse函数额外消耗的CPU资源会更小些。
  3. 从查询效率上看，使用hash字段方式的查询性能相对更稳定一些。因为crc32算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近1。而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。

选错索引问题

MySQL在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。MySQL采样统计：采样统计的时候，InnoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。所以MySQL优化器也会优化错误。

解决：

  • 使用analyze table t 命令进行修正（可以解决大部分问题）。
  • 直接使用force index强行选择一个索引。
  • 我们可以考虑修改语句，引导MySQL使用我们期望的索引
  • 可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。

索引失效

条件字段函数操作

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。到这里我给你说明了，由于加了month()函数操作，MySQL无法再使用索引快速定位功能，而只能使用全索引扫描。

隐式类型转换

那么，现在这里就有两个问题：

  1. 数据类型转换的规则是什么？
  2. 为什么有数据类型转换，就需要走全索引扫描？

就知道对于优化器来说，这个语句相当于：相当于使用了函数。

1
mysql> select * from tradelog where  CAST(tradid AS signed int) = 110717;

所以你就能确认MySQL里的转换规则了：在MySQL中，字符串和数字做比较的话，是将字符串转换成数字。

隐式字符编码转换

例如字符集utf8mb4是utf8的超集，所以当这两个类型的字符串在做比较的时候，MySQL内部的操作是，先把utf8字符串转成utf8mb4字符集，再做比较。所以也相当于使用了函数。