技术分享MySQLchangebuffer何时生效

　　作者：胡呈清
　　爱可生DBA团队成员，擅长故障分析、性能优化，个人博客：https：www。jianshu。comua95ec11f67a8，欢迎讨论。
　　本文来源：原创投稿
　　爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。
　　已知changebuffer的原理
　　参考资料：https：juejin。impost6844903875271475213
　　对于普通二级索引，当插入、修改、删除二级索引记录时，即使数据不在innodbbufferpool中，也不需要先把数据从磁盘读取到内存。只需要在changebuffer中完成DML操作，下次读取时才会从磁盘读取数据页到内存，并与changebuffer进行merge，从而得到正确的数据。这减少了DML时的随机IO。疑问
　　按照上述原理，使用changebuffer二级索引不需要读取磁盘，那delete、update是如何得到affectedrows的？答
　　不妨先作出假设：如果delete、update是以主键、唯一索引做为筛选条件，则读取磁盘或者innodbbufferpool中的主键、唯一索引来确定affectedrows。对于普通索引页上记录的删除或者修改，还是直接使用changebuffer，不需要单独将普通索引页从磁盘上读取到内存。如果delete、update是以普通二级索引做为筛选条件，以delete为例（update内部实现是先delete再insert）：deletefromtwherea100；如果索引页不在内存中，则需要先从磁盘读取a索引，找到a100的记录对应的id（主键值），再从磁盘扫描主键索引（回表）将id满足条件的记录读取到内存。然后在innodbbufferpool中把对应的主键索引页、二级索引页中的记录删除。这里不使用changebuffer。验证
　　接下来设计两个实验来验证上述假设。实验1以主键为筛选条件做delete
　　用sysbench造一张100万行的表，表中有一个主键和一个普通索引：CREATETABLEsbtest1（idintNOTNULLAUTOINCREMENT，kintNOTNULLDEFAULT0，cchar（120）COLLATEutf8mb4binNOTNULLDEFAULT，padchar（60）COLLATEutf8mb4binNOTNULLDEFAULT，PRIMARYKEY（id），KEYk2（k））；
　　重启mysqld，清空innodbbufferpool，注意参数：innodbbufferpoolsize64Minnodbbufferpoolloadatstartup0innodbbufferpooldumpatshutdown0innodbbufferpooldumppct0
　　执行delete，并使用showengineinnodbstatusG观察INSERTBUFFERANDADAPTIVEHASHINDEX部分信息，判断是否使用changebuffer：mysqldeletefromsbtest1whereid1；QueryOK，1rowaffected（0。00sec）mysqlshowengineinnodbstatusGINSERTBUFFERANDADAPTIVEHASHINDEXIbuf：size1，freelistlen29，segsize31，1mergesmergedoperations：insert0，deletemark1，delete0discardedoperations：insert0，deletemark0，delete0mysqldeletefromsbtest1whereid2；QueryOK，1rowaffected（0。00sec）INSERTBUFFERANDADAPTIVEHASHINDEXIbuf：size1，freelistlen29，segsize31，2mergesmergedoperations：insert0，deletemark2，delete0discardedoperations：insert0，deletemark0，delete0mysqldeletefromsbtest1whereid3；QueryOK，1rowaffected（0。00sec）INSERTBUFFERANDADAPTIVEHASHINDEXIbuf：size1，freelistlen29，segsize31，3mergesmergedoperations：insert0，deletemark3，delete0discardedoperations：insert0，deletemark0，delete0mysqlselectfromsbtest1whereid4；mysqldeletefromsbtest1whereid4；INSERTBUFFERANDADAPTIVEHASHINDEXIbuf：size1，freelistlen29，segsize31，4mergesmergedoperations：insert0，deletemark4，delete0discardedoperations：insert0，deletemark0，delete0
　　上述实验说明：如果delete是以主键做为筛选条件，对于普通索引k，如果索引页不在内存中（selectfromsbtest1whereid4读取的只是主键索引页，不会读取k索引页），会使用changebuffer（每次delete后，deletemark都增加1）。实验2以普通索引为筛选条件做delete
　　重新造数据，重启mysqld清空bufferpool。下面实验结果说明：如果delete以普通索引做为筛选条件，对于普通索引k，如果索引页不在内存中，不会使用changebuffer。言外之意就是需要读取磁盘了。deletewhereid1，deletemark1，说明使用了changebuffermysqldeletefromsbtest1whereid1；QueryOK，1rowaffected（0。01sec）INSERTBUFFERANDADAPTIVEHASHINDEXIbuf：size1，freelistlen29，segsize31，1mergesmergedoperations：insert0，deletemark1，delete0discardedoperations：insert0，deletemark0，delete0deletewherek367246，deletemark不变，说明没有使用changebuffermysqlselectfromsbtest1whereid2；idkcpad23672464290970034070078987867623571240963549516919385675377266146437193473041702018680900182681503823744446626061119674781290517411214029815060467792434464478849891023499591rowinset（0。00sec）mysqldeletefromsbtest1wherek367246；QueryOK，1rowaffected（0。01sec）INSERTBUFFERANDADAPTIVEHASHINDEXIbuf：size1，freelistlen29，segsize31，1mergesmergedoperations：insert0，deletemark1，delete0discardedoperations：insert0，deletemark0，delete0