mysql sql的性能优化

• 前置条件：

select version();  -- 》  5.7.17

desc test;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| val | int(10) unsigned | NO | MUL | 0 |                |
| source | int(10) unsigned | NO | | 0       | |
+--------+---------------------+------+-----+---------+----------------+

--id为自增主键，val为非唯一索引。

• 查询SQL：  下面的sql  前面的耗时是后面的几百倍

select * from test where val=4 limit 300000,5;



select * from test a inner join (select id from test where val=4 limit 300000,5) b on a.id=b.id;

• 原因就是第一条语句的 利用val的非聚集索引查询出300005条数据，再去聚集索引查询出这300005条数据的所有字段。最后再去除前面的30000条数据。IO的次数可想而知
• 第二条sql语句，是在非聚集索引查询出 符合分页后的5条。然后根据主键去查询这五条数据的所有字段

疑问： 既然一开始是利用索引的，为什么不先沿着索引叶子节点查询到最后需要的5个节点，然后再去聚簇索引中查询实际数据

• 因为查询索引叶子节点的时候，无法统计到查询数据节点(聚集索引)的次数

• 而第二条是主键，这样不需要查询数据节点（聚集索引）就可以知道查询的当前序号