Smarticen Notes

索引实践

28 min read

普通索引与唯一索引

比如现在有一个身份证号的字段id_card，业务经常在这个字段上查询数据，因此现在需要建立索引进行加速。 因为身份证号字段比较大，所以是不推荐作为主键的，这个在索引的维护中已经说明过了。

每个人的身份证都是唯一的，如果业务代码已经保证了不会写入两个重复的身份证号，那么对于这个字段建立唯一索引和普通索引在逻辑上都是正确的。

那么现在就要从性能的角度上来考虑，是选择唯一索引还是普通索引？接下来从这两种索引对查询语句和更新语句的性能影响来进行分析。

注意，下面普通索引与唯一索引的选择只建立在业务保证了字段的唯一性，普通索引与唯一索引逻辑上一致的情况下。

查询过程

对于一条形如select id from T where k=5的查询语句在索引树上查询时，先通过B+树的树根开始，按层搜索到叶子节点，然后在业内通过二分法定位到记录。

  • 普通索引：找到满足第一个记录后，需要查找下一个记录，直到碰到第一个不满足k=5条件的记录。
  • 唯一索引：索引定义了唯一性，所以查找到第一个满足条件的记录后，就会立即停止索引。

从上面来看，好像唯一索引对与查询来说性能是有所提升的，但是实际上，两者之间的性能差距是微乎其微的。因为InnoDB的数据是按照数据页为单位来读写的，也就是说读取一个记录的时候，是把这个记录所在的数据页一同读取出来的，普通索引中k=5的记录也都组织在一起，所以无非就是多做一次查找和判断下一条记录（一次是因为这里的场景中，字段业务中就是唯一的所以才会在普通索引和唯一索引中进行选择）。

唯一比较麻烦的就是，k=5这条记录刚好就是数据页的最后一条记录，这样要读取下一条记录就必须读取下一个数据页，这样会复杂一些，但是这种情况出现的概率本身就很低，将成本平摊到所有的查询中可以忽略不计。

所以结论就是：==唯一索引和普通索引在查询过程中的性能几乎没有差别==。

更新过程

change buffer

这里需要先介绍一下change buffer。

change buffer 当更新一个数据页的时候，如果数据页在内容当中就直接更新，如果不在那么在不影响数据一致性的前提下，InnoDB会将这些更新操作缓存在change buffer中，这样不需要从磁盘中读取这个数据页就能够进行更新，下次查询需要访问这个数据页的时候，将数据页读入内容，然后执行change buffer中与这个页相关的操作，就可以保证数据逻辑的正确性

通过change buffer减少了磁盘的IO，大大提高了更新的效率。不过需要注意一点：

Note

虽然叫做change buffer，但是实际上它是可以持久化的数据，也就是说，change buffer在内存当中有拷贝，也会被写入到磁盘上。

实际上，change buffer是针对二级普通索引的，将随机IO变为顺序IO。主键索引和唯一索引要更新就需要读取数据页，判断数据的唯一性。要理解这一点，先要明确概念：

  1. mysql的数据存在在主键索引树的叶子节点。
  2. 普通索引和唯一索引也有自己的索引树。
  3. 更新操作会同时更新所有的索引树结构。特别是insert操作：主键索引（也是唯一的）和唯一索引树都要更新，是无法使用change buffer的。但是普通索引的更新，是可以使用change buffer的。

将change buffer中的修改应用到原数据页得到最新结果的过程称为merge

  1. 访问对应的数据页会触发merge。
  2. 系统后台线程定期merge。
  3. 数据库正常关闭的过程中也会merge。

对于二级普通索引，其更新操作先记录在change buffer可以减少读取磁盘，并且只记录了更新的操作，相比于读取一个数据页的16k，占用的内存也是很小的。

那么到了这里就可以明白，二级普通索引在更新的时候能够使用change buffer来减少读取磁盘，提高语句的执行速度并且提高内存利用率，而唯一索引无法利用change buffer。

更新一条记录的时候，如果记录的数据页在内存中：

  • 普通索引：找到数据页的位置，更新值，结束。
  • 唯一索引：找到在数据页中对应的位置，判断没有冲突，更新值，结束。 这种情况下两者之间的性能差距可以忽略不计。

但是数据页不在内容的时候：

  • 普通索引：直接将更新记录在change buffer中就结束
  • 唯一索引：将数据页读取内容，判断没有冲突，更新值，结束。 普通索引在这种场景下的性能明显优于唯一索引，将数据从磁盘读入内存涉及随机IO的访问，是数据库中成本最高的操作之一。

change buffer的使用场景

在上面我们了解到了change buffer可以在普通索引的场景下，对更新进行加速。 但是需要注意

不是普通索引的所有场景，使用change buffer都可以进行加速。

change buffer暂存更新操作，然后merge的时候进行真正的更新，所以在merge之前，change buffer暂存的记录越多那么change buffer的收益就越大。

因为对于写多读少的业务，例如账单类、日志类的系统，写完之后立马被访问到的概率比较小，change buffer的使用效果最好。

对于写后立马查询的业务，那么更新记录在change buffer之后就立马读取数据页进行merge，这样不仅随机IO的次数不仅不会减少，反而记录change buffer还成为了一个多余的操作，对于这种业务，change buffer会发生副作用。

索引选择和实践

通过上面的学习，可以知道，当需要在唯一索引和普通索引之间进行选择的时候，尽量选择普通索引，两者在查询能力上几乎没有差别，而在更新上普通索引由于change buffer在绝大多数情况下能够进行加速要明显优于唯一索引。

但是如果在所有的更新后面都伴随着对这个记录的查询，那么应该关闭change buffer。

change buffer和redo log

理解了change buffer的原理，会非常容易联想到2.日志系统：更新语句如何执行中的redo log和WAL。

这两者之间也是非常容易混淆的。

现在要在表上执行下面的插入语句：

mysql> insert into t(id,k) values(id1,k1),(id2,k2);

当前有一个k索引树，找到位置之后，k1所在的数据页在内存当中，k2所在的数据页不在内存中。下图是当前带change buffer的更新状态图： 这条更新语句做了如下的操作：

  1. Page1在内存中，直接更新内存。
  2. Page2没有在内存中，就在内存的change buffer区域，记录下“我要往Page2插入一行”这个信息
  3. 将上述两个动作记入redo log中。 所以这条更新语句的成本本地，写了两处内存和一次顺序写磁盘。同时图中的虚线是后台操作，不影响更新的响应时间。

如果更新后不久发生了读，内存中的数据都还在，那么都的图如下： 这个读过程：

  1. 读Page1的时候，直接从内存中返回即可。
  2. 读Page2的时候，需要把Page2从磁盘读入内存，然后应用change buffer里的操作日志，生成一个正确的版本并返回结果。

redo log记录对数据页的修改，通过将更新操作写入redo log然后更新内存，直接使用内存的值，等待后续同步到磁盘来加速更新。但是如果数据页都不在内存当中，怎么直接更新数据页？先读取数据页，然后再走redo log工作流程？这时候就是change buffer发挥作用了，不去读取数据页到内存，而是直接写入change buffer记录更新，使得只有需要读取数据页的时候才会去加载数据页。

所以redo log主要是节省随机写磁盘的IO操作（转为顺序写），而change buffer主要节省的是更新的时候随机读磁盘的IO消耗（将数据页加载延迟到需要读取这个数据页的时候），而唯一索引因为必须要读取数据页判断是否合法就无法使用change buffer。

选错索引的原因

一条本来可以执行很快的语句，却由于mysql选错了索引，导致执行的速度很慢。 先查看一个例子，建立一个简单的表，如下：

CREATE TABLE `t` (
	`id` int(11) NOT NUL AUTO_INCREMENT,
	`a` int(11) DEFAULT NULL,
	`b` int(11) DEFAULT NULL,
	PRIMARY KEY (`id`),
	KEY `a` (`a`)
	KEY `b` (`b`)
) ENGINE=InnoDB;

然后利用存储过程插入10万行记录，为了方便，这里使用存储过程

delimiter ;;
create procedure idata()
begin
	declare i int;
	set i=1;
	while(i<=100000) do
		insert into t values(i,i,i);
		set i=i+1;
	end while;
end;;
delimiter ;
call idata();

分析下面的语句：

select * from t where a between 10000 and 20000;

按照预期，这个语句将会使用索引a。使用explain关键字分析也确实如此。但是这个案例是不会这么简单的。

在这个10万行数据的表上，做如下操作 也就是开启了一个事务a，然后事务b将数据都删除，又重新插入了10万行数据。这个时候事务b的查询语句select * from t where a between 10000 and 20000;就不会再选择索引a了。

我们现在让优化器自己选择索引，和强制使用a索引，设置慢查询日志的阈值为0来记录下语句的执行情况:

set long_query_time=0;
select * from t where a between 10000 and 20000; /*Q1*/
select * from t force index(a) where a between 10000 and 20000;/*Q2*/

分析慢查询日志的可以得到结果：

  • Q1扫描了10万行， 进行了全表扫描。
  • Q2只扫描了10001行。 也就是说，mysql错选了索引，导致执行了更长的时间。

要了解这个原因，首先就需要了解优化器的原理。

优化器的逻辑

选择索引是优化器的工作。优化器之所以选择索引，是为了找到一个最优的方案，用最小的代价去执行语句。 在数据库中，扫描行数是影响执行代价的因素之一，扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的CPU资源越少。

当然，扫描行数不是唯一的判断标准，优化器还会结合是否使用临时、是否排序等因素进行综合判断。

不过针对这个例子，显然没有什么临时表这些项，所以选错索引肯定是在判断扫描行数的时候出现了问题。

现在问题的关键就成了，扫描行数如何判断？

扫描行数的判断？

mysql在真正开始执行语句之气那，并不能精确地知道满足这个条件的记录有多少条，只能根据统计信息来估算记录数。这个统计信息就是“区分度”。一个索引上不同的值越多，这个索引的区分度就越好。

使用show index方法，可以看到一个索引的基数，但是mysql得到基数并不是准确值。

mysql采用采用统计的方法来获取基数，InnoDB会默认选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，得到了这个索引的基数。每当变更的数据行数超过1/M的时候，就会自动触发重新做一次索引统计。通过设置参数innodb_states_persistent的值可以设置存储索引统计的方式：

  • on：统计信息将会被持久化。默认的N是20，M是10.
  • off：统计信息只在内存中。默认的N是8，M是16.

统计的基数虽然不精确，但是大体上差别不会很大， 选错所以还有一个原因，那就是对于一个具体的语句，优化器还要判断，执行这个语句本身要扫描多少行。查看预估的行数，即rows字段。

Q1的语句倒是没有什么问题，不过Q2的语句差别很大，但是即使如此优化器还是选择了10万行扫描而不是3万行扫描计划的Q1，这是因为：使用普通索引要把回表的代价算进去，这个代价优化器也要算进去的。而直接在主键索引上扫描10万行，没有额外的代价。优化器估算了两个代价，最终认为Q1要更优。

优化器的选择索引的策略本身并没有问题，关键还是在于没能够准确地判断出扫描的行数。可以使用analyze tabel t命令来重新统计索引信息。在实践中发现explain的结果预估的rows值跟实际情况差距比较大时，可以采用这个方法来处理。

除了扫描行数，还有临时表，排序等影响。例如下面的查询于江湖：

select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;

两个都是普通索引，a需要扫描1000行，b需要扫描50001行，但是最终还是选择了b，对于这个例子来说，选择b无疑是选择错了索引，但是mysql选择错的原因是什么：

  1. 这条语句里有order by b，优化器认为走索引b可以避免排序
  2. 有limit 1，优化器认为只要找到了1条满足条件的记录，所以b的遍历就可以提前终止，值得冒险。

索引选择异常和处理

大多数情况下，优化器都能够找到正确的索引，但偶尔也会碰到上面的两种情况。 一种解决方法就是，采用force index强行选择一个索引。

第二种方法就是，修改语句，引导mysql使用我们期望的索引。例如将order by b limit 1变为order by b,a limit 1，两者逻辑相同。之前优化器选择索引b，是认为b可以避免排序，所以即使扫描的行数多，代价也更小。而order by b,a这种写法，要求按照b,a排序，就意味着使用这两个索引都要排序，因此扫描行数成为了影响决策的主要条件，优化器就会选择只需要扫描1000行的索引a 了。 不过这并不是通用的优化手段，只是刚好这个语句中有limit 1，连个语句逻辑一致才可以这样做。

第三种方法是，新建一个更加合适的索引，来提供给优化器进行选择，或者删掉误用的索引。

字符串字段加索引

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

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

下图就是两者的不同： 可以看到，由于email(6)这个索引结构中每个邮箱字段都只取前6个字节，所以占用的空间会更小，这就是前缀索引的优势。但是，这样可能会增加额外的记录扫描次数。

通过下面的一句查询语句来看一下这两个索引分别是怎么执行的

select id, name, email from SUser where email='[email protected]'

对于index1：

  1. 从index1索引树中找到满足索引值是[email protected]的记录，取得ID2的值
  2. 到主键上查询主键值为ID2的行，判断email的值是正确的，将这行记录加入结果。
  3. 取index1索引树上的下一条记录，发现已经不满足条件了，循环结束。 对于index2：
  4. 从index2索引树上找到满足索引是’zhangs’的记录，找到第一个ID1
  5. 到主键上查找主键值是ID1的行，判断email不是’[email protected]’，这行记录丢弃。
  6. 取index2上下一行，重复上上面的步骤，直到在index2上遇到不符合要求的记录。 在这个例子中，前缀索引导致读取数据的次数变多。

只要我们能够定义好长度，就可以做到既节省空间又不用额外增加太多的查询成本。

建立前缀索引的时候，需要关注区分度，区分度越高，意味着重复的键值越少，从小打大判断不同长度的前缀的区分度，选择一个比较合适的建立前缀索引。 先查看这个列上有多少不同的值：

select count(distinct email) as L from SUser;

计算不同长度前缀的的值：

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;

在一个合适的比例中，选择长度最短的。

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

将前面的查询语句变为：

select id, email from SUser where email='[email protected]'

在这种情况下反而不建立前缀索引更好，这样就可以利用覆盖索引直接返回查询到的值，而不需要回表查询。

即使你将 index2 的定义修改为 email(18) 的前缀索引，这时候虽然 index2 已经包含了所有的信息，但 InnoDB 还是要回到 id 索引再查一下，因为系统并不确定前缀索引的定义是否截断了完整信息。

==所以前缀索引无法使用覆盖索引==。

前缀区分度不够好如何处理

对于前缀区分度不高的情况，例如身份证号，前6为是地址码，同一个县的人身份证号前6位是相同的，如果维护的数据库是一个市的公民信息系统的话，对前6位做前缀索引区分度是非常低的，需要加长前缀。

但是索引选取的越长，占用的磁盘空间就越大，相同的也能够放下的索引值也就越少，搜索的效率也就越低。

如果业务中能够确定只会按照身份证进行等值查询的时候，还有别的处理方式，既可以占用更小的空间，也能够给达到相同的查询效率。

第一种方式就是倒序存储。存储身份证号的时候倒序存储，因为倒序的时候6六位是没有地址码这样的重复的，当然具体应该用几位需要根据前面介绍的区分度的方法进行验证。 在进行查询的时候这样写：

mysql> select field_list from t where id_card = reverse('input_id_card_string');

第二种方式就是使用hash字段。在表上再创建一个整数字段，用来保存身份证的校验码，同时在这个字段上创建索引

mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);

每次插入新记录的时候，都同时用crc32()这个函数得到校验码填到这个新字段。由于校验码可能冲突，所以还需要对crc32()结果相同的再进行精确匹配，不过校验码已经足够过滤掉绝大部分记录了。

mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

索引的长度变成了4个字节，比原来小了很多。

这两种方式的相同点就是它们都不支持范围查询，无论是倒序还是hash，都不具有顺序性，所以只有确定只需要等值查询的时候再使用这两种方法。

至于不同点：

  • 倒序存储不需要额外的存储空间，hash字段方法需要额外增加一个字段。不过如果倒序的前缀长了一些的话，消耗跟hash方法也就差不多了。
  • CPU消耗方面，倒序每次写和读都需要额外调用一次reverse函数，hash字段的方式需要额外调用一次crc32函数。如果只从函数的复杂度来看，reverse函数消耗的CPU资源要更小些。
  • 查询效率来看，hash字段的查询效率要更稳定一下，因为crc32虽然计算的结果会有冲突，但是概率非常小，每次查询的平均扫描行数接近1。

小结

对于字符串字段创建索引：

  1. 直接创建完整索引，这样可能比较占用空间。
  2. 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引。
  3. 倒序存储再创建前缀索引，用于绕过字符串本身前缀区分度不够的情况。
  4. 创建hahs字段索引，查询性能稳定，不过有额外的存储和计算消耗，与第三种方式都不支持范围扫描。

Graph View