因为唯一索引 数据更新, 必须把数据读入内存 ,检查是不是唯一, 随机IO很贵
按照身份证号查姓名
select name from CUser where id_card = 'xxxxxxxyyyyyyzzzzz';
由于身份证号字段比较大，不能做主键，身份证在代码里面已经保证了唯一
那么有两个选择，给 id_card 字段创建唯一索引，还是普通索引,
查询差不多
select id from T where k=5为例
先是通过 B+ 树从树根开始，按层搜索到叶子节点，就是图中右下角的这个数据页，数据页内部通过二分法来定位记录k=5
- 普通索引:查找到满足条件的第一个记录 (5,500) 后，需要查找下一个记录，直到碰到第一个不满足 k=5 条件的记录。
- 唯一索引: 查找到第一个满足条件的记录后，就会停止继续检索。
但是,没差多少
InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，当找到 k=5 的记录的时候，它所在的数据页就都在内存里了。那么，对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。
如果 k=5 这个记录刚好是这个数据页的最后一个记录，那么要取下一个记录，必须读取下一个数据页，这个操作会稍微复杂一些
但是
页的大小默认是 16KB。.对于整型字段，一个数据页可以放近千个 key，因此出现这种情况的概率会很低
更新
第一种情况是，这个记录要更新的目标页在内存中。这时，InnoDB 的处理流程如下：
- 唯一索引: 找到 3 和 5 之间的位置，判断到没有冲突，插入这个值，语句执行结束；
- 普通索引: 不用判断到没有冲突
差别几乎没有
第二种情况是，这个记录要更新的目标页不在内存中。
- 唯一索引: 需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
- 普通索引: 将更新记录在 change buffer，语句执行就结束了。
将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。
之前我就碰到过一件事儿，有个 DBA 的同学跟我反馈说，他负责的某个业务的库内存命中率突然从 99% 降低到了 75%，整个系统处于阻塞状态，更新语句全部堵住。而探究其原因后，我发现这个业务有大量插入数据的操作，而他在前一天把其中的某个普通索引改成了唯一索引。
实践
在查询能力上是没差别的，
主要考虑 更新性能, 尽量选择普通索引。
在实际使用中，普通索引和 change buffer ( 尽量开大)的配合使用，对于数据量大的表的更新优化还是很明显的。
除非, 所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭 change buffer。
网友评论