索引该如何选择
- 如何选择索引
- 查询
- 更新
- change buffer
- change buffer使用场景
- 索引的选择和实践
- change buffer和redo log
如何选择索引
再不同的业务场景下，如何选择唯一索引还是普通索引？
如果需要按照身份证号查姓名，就需要执行以下sql语句：
select name from CUser where id_card = 'xxxxxxxxyyyyyyyzzzz';就需要再id_card上面创建索引，如果业务逻辑中保证了不会存在重复的身份证号，那么两种索引均可逻辑上来说都是正确的；
从两种索引对于查询和更新语句的性能影响来进行分析；
查询
以主键列为ID，表中有字段k为例；
执行的查询语句是：
select id from T where k = 5;从索引树上查找，先从B+树的根开始，按层搜索叶子节点，再数据页内部通过二分法来定位记录；
- 普通索引：查找到满足条件(5,500)以后，需要查找下一记录，直到碰到第一个不满足k=5条件的语句；
- 唯一索引：再查找到第一个 满足条件的语句之后，就停止继续检索；
这两种操作对于性能的影响是微乎其微的，因为InnoDB引擎是以页为单位将数据整体读入内存中，普通索引多做的一次"查询和判断下一次记录",只需要一次指针寻找和一次计算；
更新
change buffer
在了解两种索引对于语句性能影响之前，先了解一下change buffer的概念。
当需要更新一个数据页的时候，如果数据页在内存中就直接更新，如果数据页还没有在内存中的时候，在不影响一致性的前提下，InnoDB会将这些更新操作缓存在change buffer中，在下次查询需要访问这个数据页的时候，再将数据页读入内存，从而保证数据逻辑的正确性；
change buffer是可以持久化的数据，在内存中有拷贝，也会被写入磁盘中；通过merge操作将change buffer这种操作应用到原数据页中，除了访问数据页触发merge,系统后台会定期merge,关闭数据库的时候，也会执行merge操作；
什么条件下可以使用change buffer?
对于唯一索引来说，插入一个数据(4,1000),需要先判断当前的表中是否存在k=4的记录了，必须将数据页读入内存才能进行判断，读入内存，就可以直接更新内存，就不需要change buffer了；
所以，只有普通索引才可以使用change buffer
change buffer使用的是buffer pool的内存，大小通过参数 innodb_change_buffer_max_size进行动态设置；
思考一下插入数据(4,1000)的过程：
- 如果记录更新的目标页在内存中：
对于唯一索引来所，找到3和5之间的位置，判断到没有冲突，插入这个值，语句执行结束；
对于普通索引，找到3和5之间的位置，插入这个值，语句执行结束； - 如果记录更新的目标页不在内存中
对于唯一索引来所，找到3和5之间的位置，判断到没有冲突，插入这个值，语句执行结束；
对于普通索引，将更新记录在change buffer，语句执行结束；
将数据从磁盘读入内存涉及到随机IO的访问，数据库中成本最高的操作之一，change buffer减少了随机磁盘访问，对于更新性能的提升会很明显；
change buffer使用场景
change buffer只限于在普通索引的场景下，change buffer的目的是将记录的变更动作缓存下来，所以在一个数据页merge之前，change buffer记录的越多，收益就越大；
对于写多读少的业务来说，change buffer的使用效果最好，常见的就是账单类和日志类系统；
不适用于在写入之后马上进行查询，因为更新在change buffer之中，马上访问这个数据页，会触发merge过程，随即访问IO的次数不会减少，会增加change buffer的维护代价；
索引的选择和实践
两种索引在查询能力上没有什么区别，主要考虑性能的影响，建议尽量选择普通索引；
如果在数据更新的后面，马上进行查询，应当先关闭change buffer，而其它情况change buffer都可以提升更新性能；
change buffer和redo log
例如现在要对这个表执行插入语句：
insert into t(id,k) values(id1,k1),(id2,k2);
分析这条语句你会发现，涉及了四个部分：内存、redolog（ib_log_fileX）、数据表空间、系统表空间(ibdata1);
- Page1在内存中直接更新；
- Page2不在内存中，就在内存的change buffer区域，记录下"我要在Page2中插入这一行"；
- 将上述两个操作，记录在redo log中；
现在执行一个读请求
select * from t where k in(k1,k2);下图是两个读请求的流程图：
- 读取Page1的时候，直接从内存返回；
- 读取Page2的时候，需要把Page2从磁盘读入内存中，然后应用change buffer里面的操作日志，返回一个正确版本的结果；
redo log主要节省的是随机写磁盘的IO消耗(转成顺序写)，而change buffer主要节省的是随机磁盘的IO消耗
