场景
- 维护一个市民系统,每个人都有一个唯一的身份证号,而且业务代码已经保证了不会写入两个重复的身份证号。如果市民系统需要按照身份证号查姓名,就会执行类似这样的 SQL 语句:
select name from CUser where id_card = 'xxxxxxxyyyyyyzzzzz';
那么怎么建立索引呢?
- 由于身份证号字段比较大,不建议把身份证号当做主键
例子
- 如下,ID是主键,K是索引,从这两种索引对查询语句和更新语句的性能影响来进行分析
分析
- 假设,执行查询的语句是 select id from T where k=5
查找过程
- 对于普通索引来说,查找到满足条件的第一个记录 (5,500) 后,需要查找下一个记录,直到碰到第一个不满足 k=5 条件的记录。
- 对于唯一索引来说,由于索引定义了唯一性,查找到第一个满足条件的记录后,就会停止继续检索。
- 那么,这个不同带来的性能差距会有多少呢?答案是,微乎其微。
- 对于普通索引来说,要多做的那一次“查找和判断下一条记录”的操作,就只需要一次指针寻找和一次计算。
- 当然,如果 k=5 这个记录刚好是这个数据页的最后一个记录,那么要取下一个记录,必须读取下一个数据页,这个操作会稍微复杂一些。
- 但是,我们之前计算过,对于整型字段,一个数据页可以放近千个 key,因此出现这种情况的概率会很低。所以,我们计算平均性能差异时,仍可以认为这个操作成本对于现在的 CPU 来说可以忽略不计。
更新过程
- 唯一索引的更新就不能使用 change buffer,实际上也只有普通索引可以使用
如果要在这张表中插入一个新记录 (4,400) 的话,InnoDB 的处理流程是怎样的,分为记录要更新的目标页在不在内存中:
目标页在内存中
- 对于唯一索引来说,找到 3 和 5 之间的位置,判断到没有冲突,插入这个值,语句执行结束;
- 对于普通索引来说,找到 3 和 5 之间的位置,插入这个值,语句执行结束。
- 这样看来,普通索引和唯一索引对更新语句性能影响的差别,只是一个判断,只会耗费微小的 CPU 时间,二者耗时相差不大。
目标页不在内存中
- 对于唯一索引来说,需要将数据页读入内存,判断到没有冲突,插入这个值,语句执行结束;
- 对于普通索引来说,则是将更新记录在 change buffer,语句执行就结束了。
- 将数据从磁盘读入内存涉及随机 IO 的访问,是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问,所以对更新性能的提升是会很明显的,所以普通索引更快。
实例
之前我就碰到过一件事儿,有个 DBA 的同学跟我反馈说,他负责的某个业务的库内存命中率突然从 99% 降低到了 75%,整个系统处于阻塞状态,更新语句全部堵住。而探究其原因后,我发现这个业务有大量插入数据的操作,而他在前一天把其中的某个普通索引改成了唯一索引。
总结
- 对于写多读少的业务来说,页面在写完以后马上被访问到的概率比较小,此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。
- 反过来,假设一个业务的更新模式是写入之后马上会做查询,那么即使满足了条件,将更新先记录在 change buffer,但之后由于马上要访问这个数据页,会立即触发 merge 过程。这样随机访问 IO 的次数不会减少,反而增加了 change buffer 的维护代价。
- 普通索引和 change buffer 的配合使用,对于数据量大的表的更新优化还是很明显的。
- 唯一索引用不上 change buffer 的优化机制,因此如果业务可以接受,从性能角度出发我建议你优先考虑非唯一索引。