一次SQL查询优化原理分析900W数据，从17s到300ms

　　来源：jianshu。comp0768ebc4e28d
　　有一张财务流水表，未分库分表，目前的数据量为9555695，分页查询使用到了limit，优化之前的查询耗时16s938ms（execution：16s831ms，fetching：107ms），按照下文的方式调整SQL后，耗时347ms（execution：163ms，fetching：184ms）；
　　操作：查询条件放到子查询中，子查询只查主键ID，然后使用子查询中确定的主键关联查询其他的属性字段；
　　原理：1、减少回表操作；2、可参考《阿里巴巴Java开发手册（泰山版）》第五章MySQL数据库、（二）索引规约、第7条：【推荐】利用延迟关联或者子查询优化超多分页场景。说明：MySQL并不是挑过offeset行，而是取offsetN行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的底下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。正例：先快速定位需要获取的id段，然后再关联：SELECTa。FROM表1a，（selectidfrom表1where条件LIMIT100000，20）bwherea。idb。id；优化前SQLSELECT各种字段FROMtablenameWHERE各种条件LIMIT0，10；优化后SQLSELECT各种字段FROMtablenamemaintaleRIGHTJOIN（SELECT子查询只查主键FROMtablenameWHERE各种条件LIMIT0，10；）temptableONtemptable。主键maintable。主键
　　一，前言
　　首先说明一下MySQL的版本：mysqlselectversion（）；version（）5。7。171rowinset（0。00sec）
　　表结构：mysqldesctest；FieldTypeNullKeyDefaultExtraidbigint（20）unsignedNOPRINULLautoincrementvalint（10）unsignedNOMUL0sourceint（10）unsignedNO03rowsinset（0。00sec）
　　id为自增主键，val为非唯一索引。
　　灌入大量数据，共500万：mysqlselectcount（）fromtest；count（）52428821rowinset（4。25sec）
　　我们知道，当limitoffsetrows中的offset很大时，会出现效率问题：mysqlselectfromtestwhereval4limit300000，5；idvalsource3327622443327632443327642443327652443327662445rowsinset（15。98sec）
　　为了达到相同的目的，我们一般会改写成如下语句：mysqlselectfromtestainnerjoin（selectidfromtestwhereval4limit300000，5）bona。idb。id；idvalsourceid332762244332762233276324433276323327642443327642332765244332765233276624433276625rowsinset（0。38sec）
　　时间相差很明显。
　　为什么会出现上面的结果？我们看一下selectfromtestwhereval4limit300000，5；的查询过程：
　　查询到索引叶子节点数据。根据叶子节点上的主键值去聚簇索引上查询需要的全部字段值。
　　类似于下面这张图：
　　像上面这样，需要查询300005次索引节点，查询300005次聚簇索引的数据，最后再将结果过滤掉300000条，取出最后5条。MySQL耗费了大量随机IO在查询聚簇索引的数据上，而有300000次随机IO查询到的数据是不会出现在结果集当中的。
　　肯定会有人问：既然一开始是利用索引的，为什么不先沿着索引的节点查询到最后需要的5个节点，然后再去聚簇索引中查询实际数据。这样只需要5次随机IO，类似于下面图片的过程：
　　其实我也想问这个问题。
　　我们创建了一个高质量的技术交流群，与优秀的人在一起，自己也会优秀起来，赶紧点击加群，享受一起成长的快乐。另外，如果你最近想跳槽的话，年前我花了2周时间收集了一波大厂面经，节后准备跳槽的可以点击这里领取！证实
　　下面我们实际操作一下来证实上述的推论：
　　为了证实selectfromtestwhereval4limit300000，5是扫描300005个索引节点和300005个聚簇索引上的数据节点，我们需要知道MySQL有没有办法统计在一个sql中通过索引节点查询数据节点的次数。我先试了Handlerread系列，很遗憾没有一个变量能满足条件。
　　我只能通过间接的方式来证实：
　　InnoDB中有bufferpool。里面存有最近访问过的数据页，包括数据页和索引页。所以我们需要运行两个sql，来比较bufferpool中的数据页的数量。预测结果是运行selectfromtestainnerjoin（selectidfromtestwhereval4limit300000，5）；之后，bufferpool中的数据页的数量远远少于selectfromtestwhereval4limit300000，5；对应的数量，因为前一个sql只访问5次数据页，而后一个sql访问300005次数据页。selectfromtestwhereval4limit300000，5mysqlselectindexname，count（）frominformationschema。INNODBBUFFERPAGEwhereINDEXNAMEin（val，primary）andTABLENAMEliketestgroupbyindexname；Emptyset（0。04sec）
　　可以看出，目前bufferpool中没有关于test表的数据页。mysqlselectfromtestwhereval4limit300000，5；idvalsource3327622443327632443327642443327652443327662445rowsinset（26。19sec）mysqlselectindexname，count（）frominformationschema。INNODBBUFFERPAGEwhereINDEXNAMEin（val，primary）andTABLENAMEliketestgroupbyindexname；indexnamecount（）PRIMARY4098val2082rowsinset（0。04sec）
　　可以看出，此时bufferpool中关于test表有4098个数据页，208个索引页。
　　selectfromtestainnerjoin（selectidfromtestwhereval4limit300000，5）；为了防止上次试验的影响，我们需要清空bufferpool，重启mysql。mysqladminshutdownusrlocalbinmysqldsafemysqlselectindexname，count（）frominformationschema。INNODBBUFFERPAGEwhereINDEXNAMEin（val，primary）andTABLENAMEliketestgroupbyindexname；Emptyset（0。03sec）
　　运行sql：mysqlselectfromtestainnerjoin（selectidfromtestwhereval4limit300000，5）bona。idb。id；idvalsourceid332762244332762233276324433276323327642443327642332765244332765233276624433276625rowsinset（0。09sec）mysqlselectindexname，count（）frominformationschema。INNODBBUFFERPAGEwhereINDEXNAMEin（val，primary）andTABLENAMEliketestgroupbyindexname；indexnamecount（）PRIMARY5val3902rowsinset（0。03sec）
　　我们可以看明显地看出两者的差别：第一个sql加载了4098个数据页到bufferpool，而第二个sql只加载了5个数据页到bufferpool。符合我们的预测。也证实了为什么第一个sql会慢：读取大量的无用数据行（300000），最后却抛弃掉。而且这会造成一个问题：加载了很多热点不是很高的数据页到bufferpool，会造成bufferpool的污染，占用bufferpool的空间。遇到的问题
　　为了在每次重启时确保清空bufferpool，我们需要关闭innodbbufferpooldumpatshutdown和innodbbufferpoolloadatstartup，这两个选项能够控制数据库关闭时dump出bufferpool中的数据和在数据库开启时载入在磁盘上备份bufferpool的数据。
　　参考资料：
　　1。https：explainextended。com20091023mysqlorderbylimitperformancelaterowlookups
　　2。https：dev。mysql。comdocrefman5。7eninnodbinformationschemabufferpooltables。html