常见的大表查询优化

测试表user, user_detail各100w数据

下面是一个常见的连表查询分页sql

SELECT * FROM user u LEFT JOIN user_detail ud ON u.id = ud.user_id LIMIT 800000, 10

执行时间3.323s

优化下可以写成这样

SELECT * FROM (SELECT * FROM user LIMIT 800000, 10) u LEFT JOIN user_detail ud ON u.id = ud.user_id 

执行时间0.020s

这是因为SQL语句里第一个被执行的总是FROM子句, 所以在两个表连接之前先查询主表的数据, 再去连接其他表

1000000 : 10 的效率区别

还能再优化

SELECT * FROM (SELECT * FROM user WHERE id > 800000 LIMIT 10) u LEFT JOIN user_detail ud ON u.id = ud.user_id

执行时间0.015s

当limit 800000 时, 代表mysql要先依次过滤掉80w行数据, 再取10条

而用where id 时, 就走主键索引, 直接取10条符合条件的数据