MySQL 普通索引和唯一索引该如何选择？

MySQL 普通索引和唯一索引该如何选择？

普通索引和唯一索引在查询能力上没啥差别，主要考虑对更新性能的影响，要尽量选择普通索引。接下来分析两种索引在查询语句和更新语句对性能的影响。

查询过程

MySQL InnoDB 是采用 B+ 树实现其索引结构。

image

B+ 树的查找过程

如上图所示：现在需要查找 29 这个值。

1. 将磁盘块1从磁盘加载到内存，发生一次IO ,在内存中使用二分查找方式找到 29在17和35 之间，锁定磁盘块1的P2 指针。
2. 通过磁盘块1 的 P2 指针地址把磁盘块3 加载到内存，发生第二次IO ,锁定磁盘块3 的 P2 指针
3. 通过磁盘块3 的P2指针加载磁盘块8到内存，发生第三次 IO.同时根据二分查找找到29 查询结束。

image

假设用执行这么一个查询语句：

select id from T where k=5

现在索引树上查找，然后通过 B+ 树从树根开始，按层搜索到叶子节点，然后从数据页内部通过二分查找中定位记录。

image

• 对应普通索引来说，查找到满足条件的第一个记录 (5,500) 之后，需要查找下一个记录，直到找到第一个不满足 k =5 的记录终止检索。
• 对于唯一索引来说，定义了唯一性，找到了第一个满足条件的记录后，停止检索。InnoDB 是按数据页为单位读写的，以页为单位，读入整个内存，每个数据页默认大小 16 K ,由于内存的查找是很快的，即使普通索引会继续查找下一条k=5 的记录，只需要进行一次指针+一次计算，整个性能也是可以忽略不计的，也就是说普通索引和唯一索引在查询性能上差别不是很大。

更新过程

change buffer

change buffer 是一个特殊的数据结构，当二级索引的页面不在缓冲池中，change buffer 会缓存对二级索引的数据操作（update, insert, delete）.主要减少磁盘的随机 I/O。

唯一索引不会使用 Change buffer ,如果索引设置了唯一属性，在进行插入或者修改操作时，InnoDB 必须进行唯一性检查，如果不读取索引页到缓冲池，无法校验索引是否唯一，但是可以进行缓冲删除操作。

• https://dev.mysql.com/doc/refman/5.7/en/innodb-change-buffer.html

当需要更新一个数据页时，如果数据页在内存中就直接更新，如果这个数据页还没有内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些操作缓存在 change buffer 中，这样就不需要从磁盘读入数据页，下次查询需要访问这个数据页时，将数据页读入内存，然后执行 change buffer 中与这个页的操作。通过这种方式保证这个数据逻辑的正确性。

change buffer 实际上是持久化的数据，change buffer 在内存中有拷贝，也会被写入到磁盘上。将 change buffer 中的操作应用到原数据页，得到最新结果过程称为 merge，除了访问页这个数据页会触发 merge 操作，系统后台线程会定期 merge ,在数据库正常关闭过程中，也会执行 merge。

change buffer 配置

• innodb_change_buffer_max_size% 配置写缓冲的大小，占整个缓冲池的比例，默认值是25%，最大值是50%。

show variables like '%innodb_change_buffer_max_size%';

image

• innodb_change_buffering配置是否缓存辅助索引页的修改，默认为 all，即缓存 insert/delete-mark/purge

show variables like '%innodb_change_buffering%';

image

如果能够将更新操作先记录 change buffer ,减少读磁盘，语句的执行速度会得到明显提升，而且数据读入内存了需要 buffer pool ，所以使用这种方式能够避免占用内存，提高内存利用率。普通索引能够使用 change buffer ,但是唯一索引不行，因此 普通索引比唯一索引更新操作快。

change buffer 使用场景

• 适合的场景

1. 非唯一索引
2. 业务写多读少，或者不是写后立即读场景。

• 不合适场景

1. 数据库都是唯一索引
2. 写入一个数据后，会立即读取它。

change buffer 和 redo log 区别

redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的是随机读磁盘的IO消耗。

快速回忆一遍 redo log

redo log +WAL 技术

redo Log 是 InnoDB 引擎特有的日志。如果每一次更新操作都需要写进磁盘，然后磁盘要找到那条记录，然后再更新，整个过程 IO 成本很高，查找成本很高。MySQL 采用了什么方式提高更新效率呢？

MySQL 采用 WAL 技术，Write Ahead Loging,关键点是先写日志再写磁盘，具体执行如下：当有一条记录需要更新的时候，InnoDB 引擎会先把记录写到 redo log里，并更新内存，这个时候更新就算完事了。当 InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，这个更新一般是在空闲的时候做。