一次SQL查询优化原理分析

[AdrianHu99]

https://mp.weixin.qq.com/s/-Zl9iXVeT0cvQcZHcMLecQ

[AdrianHu99]

SELECT 各种字段 FROM table_name WHERE 各种条件 LIMIT 0,10;

[AdrianHu99]

After optimization:

SELECT 各种字段 FROM table_name main_tale RIGHT JOIN ( SELECT 子查询只查主键 FROM table_name WHERE 各种条件 LIMIT 0,10; ) temp_table ON temp_table.主键 = main_table.主键

[AdrianHu99]

[AdrianHu99]

需要查询300005次索引节点，即查询300005次聚簇索引的数据，最后再将结果过滤掉前300000条，取出最后5条。MySQL耗费了大量随机I/O在查询聚簇索引的数据上，而有300000次随机I/O查询到的数据是不会出现在结果集当中的。

[AdrianHu99]

InnoDB中有buffer pool。里面存有最近访问过的数据页，包括数据页和索引页。所以我们需要运行两个sql，来比较buffer pool中的数据页的数量:

select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('val','primary') and TABLE_NAME like '%test%' group by index_name;Empty set (0.04 sec)

[AdrianHu99]

the first query will also cause:

而且这会造成一个问题：加载了很多热点不是很高的数据页到buffer pool，会造成buffer pool的污染，占用buffer pool的空间。

[AdrianHu99]

为了在每次重启时确保清空buffer pool，我们需要关闭innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_load_at_startup，这两个选项能够控制数据库关闭时dump出buffer pool中的数据和在数据库开启时载入在磁盘上备份buffer pool的数据。