sql两个字段的值求和_⼀次SQL查询优化原理分析（900W+数
据，从17s到300ms）
专注于Java领域优质技术，欢迎关注
作者：Muscleape
有⼀张财务流⽔表，未分库分表，⽬前的数据量为9555695，分页查询使⽤到了limit，优化之前的查询耗时16 s 938 ms (execution: 16 s 831 ms, fetching: 107 ms)，按照下⽂的⽅式调整SQL后，耗时347 ms (execution: 163 ms, fetching: 184 ms)；
操作： 查询条件放到⼦查询中，⼦查询只查主键ID，然后使⽤⼦查询中确定的主键关联查询其他的属性字段；
原理： 减少回表操作；
⼀：前⾔
⾸先说明⼀下MySQL的版本：
表结构：
id为⾃增主键，val为⾮唯⼀索引。
灌⼊⼤量数据，共500万：
我们知道，当limit offset rows中的offset很⼤时，会出现效率问题：
为了达到相同的⽬的，我们⼀般会改写成如下语句：
时间相差很明显。
为什么会出现上⾯的结果？我们看⼀下select * from test where val=4 limit 300000,5；的查询过程：查询到索引叶⼦节点数据。
根据叶⼦节点上的主键值去聚簇索引上查询需要的全部字段值。
类似于下⾯这张图：
像上⾯这样，需要查询300005次索引节点，查询300005次聚簇索引的数据，最后再将结果过滤掉前300000条，取出最后5条。MySQL耗费了⼤量随机I/O在查询聚簇索引的数据上，⽽有300000次随机I/O查询到的数据是不会出现在结果集当中的。
肯定会有⼈问：既然⼀开始是利⽤索引的，为什么不先沿着索引叶⼦节点查询到最后需要的5个节点，然后再去聚簇索引中查询实际数据。这样只需要5次随机I/O，类似于下⾯图⽚的过程
其实我也想问这个问题。
⼆：证实
下⾯我们实际操作⼀下来证实上述的推论：
为了证实select * from test where val=4 limit 300000,5是扫描300005个索引节点和300005个聚簇索引上的数据节点，我们需要知道MySQL有没有办法统计在⼀个sql中通过索引节点查询数据节点的次数。我先试了Handler_read_*系列，很遗憾没有⼀个变量能满⾜条件。
我只能通过间接的⽅式来证实：
InnoDB中有buffer pool。⾥⾯存有最近访问过的数据页，包括数据页和索引页。所以我们需要运⾏两个sql，来⽐较buffer pool中的数据页的数量。预测结果是运⾏select * from test a inner join (select id from test where val=4 limit 300000,5); 之后，buffer pool中
的数据页的数量远远少于select * from test where val=4 limit 300000,5；对应的数量，因为前⼀个sql只访问5次数据页，⽽后⼀个sql
访问300005次数据页。
sql优化的几种方式select * from test where val=4 limit 300000,5
mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('val','primary') and TABLE_NAME like '%可以看出，⽬前buffer pool中没有关于test表的数据页。
可以看出，此时buffer pool中关于test表有4098个数据页，208个索引页。
select * from test a inner join (select id from test where val=4 limit 300000,5) ；为了防⽌上次试验的影响，我们需要清空buffer
pool，重启mysql。
mysqladmin shutdown/usr/local/bin/mysqld_safe &
mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('val','primary') and TABLE_NAME like '%test%' g 运⾏sql：
我们可以看明显的看出两者的差别：第⼀个sql加载了4098个数据页到buffer pool，⽽第⼆个sql只加载了5个数据页到buffer pool。符
合我们的预测。也证实了为什么第⼀个sql会慢：读取⼤量的⽆⽤数据⾏(300000)，最后却抛弃掉。⽽且这会造成⼀个问题：加载了很多
热点不是很⾼的数据页到buffer pool，会造成buffer pool的污染，占⽤buffer pool的空间。遇到的问题
为了在每次重启时确保清空buffer pool，我们需要关闭innodb_buffer_pool_dump_at_shutdown和
innodb_buffer_pool_load_at_startup，这两个选项能够控制数据库关闭时dump出buffer pool中的数据和在数据库开启时载⼊在磁盘上
备份buffer pool的数据。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论