【Mysql】索引选择

朱雀 2022-03-14 12:08 224阅读 0赞

选择普通索引还是唯一索引？  
对于查询过程来说：  
a、普通索引，查到满足条件的第一个记录后，继续查找下一个记录，直到第一个不满足条件的记录  
b、唯一索引，由于索引唯一性，查到第一个满足条件的记录后，停止检索  
但是，两者的性能差距微乎其微。因为InnoDB根据数据页来读写的。  
对于更新过程来说：  
概念：change buffer  
当需要更新一个数据页，如果数据页在内存中就直接更新，如果不在内存中，在不影响数据一致性的前提下，InnoDB会将这些更新操作缓存在change buffer中。下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中的与这个页有关的操作。  
  
change buffer是可以持久化的数据。在内存中有拷贝，也会被写入到磁盘上  
  
purge:将change buffer中的操作应用到原数据页上，得到最新结果的过程，成为purge  
访问这个数据页会触发purge，系统有后台线程定期purge，在数据库正常关闭的过程中，也会执行purge  
  
**唯一索引的更新不能使用change buffer**  
  
change buffer用的是buffer pool里的内存，change buffer的大小，可以通过参数innodb\_change\_buffer\_max\_size来动态设置。这个参数设置为50的时候，表示change buffer的大小最多只能占用buffer pool的50%。  
  
将数据从磁盘读入内存涉及随机IO的访问，是数据库里面成本最高的操作之一。  
change buffer 因为减少了随机磁盘访问，所以对更新性能的提升很明显。  
  
change buffer使用场景  
在一个数据页做purge之前，change buffer记录的变更越多，收益就越大。  
对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。  
  
反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在change buffer,但之后由于马上要访问这个数据页，会立即触发purge过程。  
这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价。所以，对于这种业务模式来说，change buffer反而起到了副作用。  
  
索引的选择和实践：  
尽可能使用普通索引。  
redo log主要节省的是随机写磁盘的IO消耗(转成顺序写)，而change buffer主要节省的则是随机读磁盘的IO消耗。

如果某次写入使用了change buffer机制，之后主机异常重启，是否会丢失change buffer和数据？

不会丢失，虽然只更新内存，但事务提交的时候，把change buffer的操作记录到redo log里了，所以崩溃回复的时候，change buffer能找回来。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3czNzI0MjYwOTY_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3czNzI0MjYwOTY_size_16_color_FFFFFF_t_70]: /images/20220313/9b42475b80b54d36bd440b17b1c067e6.png