mysql中LEFT JOIN分页时的优化方法

引言

查询的时候使用LEFT JOIN，配合分页要怎么优化？

正文

常见的
分页SQL：

SELECT *
FROM table1 t1
LEFT JOIN table2 t2 ON t2.id = t1.id
LEFT JOIN table3 t3 ON t3.id = t1.id
LEFT JOIN table4 t4 ON t4.id = t1.id
WHERE t1.id = 1 AND t2. ...
LIMIT 0,30

总数SQL：

SELECT COUNT(0)
FROM table1 t1
LEFT JOIN table2 t2 ON t2.id = t1.id
LEFT JOIN table3 t3 ON t3.id = t1.id
LEFT JOIN table4 t4 ON t4.id = t1.id
WHERE t1.id = 1 AND t2. ...

问题：

LEFT JOIN消耗性能
全部LEFT JOIN后再取分页
取总数时候不必要的LEFT JOIN影响性能

解决办法

WHERE 条件语句用不到的LEFT JOIN的表，放到取分页后面
取总数时候不必要的LEFT JOIN不要

取分页数据：

SELECT *
FROM (
    SELECT *
    FROM table1 t1
    LEFT JOIN table2 t2 ON t2.id = t1.id
    LEFT JOIN table3 t3 ON t3.id = t1.id
    WHERE t1.id = 1 AND t2. ...
    LIMIT 0,30
    )t
LEFT JOIN table4 t4 ON t4.id = t1.id

取总数

SELECT COUNT(0)
FROM table1 t1
LEFT JOIN table2 t2 ON t2.id = t1.id
LEFT JOIN table2 t3 ON t3.id = t1.id
WHERE t1.id = 1 AND t2. ...

结果

优化前：

优化后：