重读经典MySQL45讲-普通索引和唯一索引

select name from CUser where id_card = 'xxxxxxxyyyyyyzzzzz';
对于这个sql你可能会想到把id_card添加为主键索引或者普通索引，唯一索引，但是id_card是身份证字段比较大不建议添加为主键索引
为什么字段大就不建议为主键索引？

• 1、如果以身份证作为主键id的话，由于不是递增的在添加的时候可能会带来页分裂的性能消耗（发生页分裂就会带来数据聚的拷贝问题）；
• 2、同时在形成B+tree的时候每个非叶子节点存储的数据量就会变小；
• 3、二级索引里存的是主键信息，主键过长会增加二级索引的空间。
  那么现在有两个选择，要么给 id_card 字段创建唯一索引，要么创建一个普通索引。如果业务代码已经保证了不会写入重复的身份证号，那么这两个选择逻辑上都是正确的。
  如果从性能的角度考虑，你选择唯一索引还是普通索引呢？选择的依据是什么呢？

查询过程

执行查询的语句是 select id from T where k=5。这个查询语句在索引树上查找的过程先是通过 B+ 树从树根开始，按层搜索到叶子节点，也就是图中右下角的这个数据页，然后可以认为数据页内部通过二分法来定位记录。

• 对于普通索引来说，查找到满足条件的第一个记录 (5,500) 后，需要查找下一个记录，直到碰到第一个不满足 k=5 条件的记录。
• 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。
  两个方法不同带来的性能影响微乎其微
  因为innodb是按页为单位进行读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。
  因为引擎是按页读写的，所以说，当找到 k=5 的记录的时候，它所在的数据页就都在内存里了。那么，对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。
  所以这个操作消耗的CPU可以忽略不计

更新过程

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

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

将change buffer中的内容更新到原始数据页这个过程叫merge，除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。
显然，如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。
什么条件下可以使用 change buffer 呢？
对于唯一索引所有操作都需要先检查是否违反数据一致性，所以必须把数据页加载到内存中，也就用不上change buffer，实际上也只有普通索引能用上。

change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。