分页查询优化

案例一

优化前：
当where条件后的结果集较大并且页数达到一个量级整个SQL的查询效率就十分低下(哪怕where的条件加上了索引也不行)。

select * from user where age = 10 limit 100000,10;
查询结果4.73s

优化后：
其中需要对where条件增加索引，id因为是主键自带索引。select返回减少回表可以提升查询性能,所以采用查询主键字段后进行关联大幅度提升了查询效率。

SELECT a.* FROM user a
INNER JOIN
    (SELECT id FROM user WHERE age = 10 LIMIT 100000,10) b
ON a.id = b.id;
查询结果0.53s

案例二

优化前sql

SELECT  
    mui.id,  
    mui.merchant_id,  
    mui.member_id,  
    DATE_FORMAT(  
        mui.recently_consume_time,  
        '%Y%m%d%H%i%s'  
    ) recently_consume_time,  
    IFNULL(mui.total_consume_num, 0) total_consume_num,  
    IFNULL(mui.total_consume_amount, 0) total_consume_amount,  
    (  
        CASE  
        WHEN u.nick_name IS NULL THEN  
            '会员'  
        WHEN u.nick_name = '' THEN  
            '会员'  
        ELSE  
            u.nick_name  
        END  
    ) AS 'nickname',  
    u.sex,  
    u.head_image_url,  
    u.province,  
    u.city,  
    u.country  
FROM  
    merchant_member_info mui  
LEFT JOIN member_info u ON mui.member_id = u.id  
WHERE  
    1 = 1  
AND mui.merchant_id = '商户编号'  
ORDER BY  
    mui.recently_consume_time DESC / ASC  
LIMIT 0,  
 10 

优化思路：先走覆盖索引定位到，需要的数据行的主键值，然后 INNER JOIN 回原表，取到其他数据。
优化后sql

SELECT  
    mui.id,  
    mui.merchant_id,  
    mui.member_id,  
    DATE_FORMAT(  
        mui.recently_consume_time,  
        '%Y%m%d%H%i%s'  
    ) recently_consume_time,  
    IFNULL(mui.total_consume_num, 0) total_consume_num,  
    IFNULL(mui.total_consume_amount, 0) total_consume_amount,  
    (  
        CASE  
        WHEN u.nick_name IS NULL THEN  
            '会员'  
        WHEN u.nick_name = '' THEN  
            '会员'  
        ELSE  
            u.nick_name  
        END  
    ) AS 'nickname',  
    u.sex,  
    u.head_image_url,  
    u.province,  
    u.city,  
    u.country  
FROM  
    merchant_member_info mui  
INNER JOIN (  
    SELECT  
        id  
    FROM  
        merchant_member_info  
    WHERE  
        merchant_id = '商户ID'  
    ORDER BY  
        recently_consume_time DESC  
    LIMIT 9000,  
    10  
) AS tmp ON tmp.id = mui.id  
LEFT JOIN member_info u ON mui.member_id = u.id 

案例三

优化后：

先查缩小t1表中的范围，再和t2表进行联查
SELECT
	t1.*,
	t2.LEVEL AS LEVEL,
	t2.content AS content 
FROM t1
	LEFT JOIN t2 ON t1.CODE = t2.CODE 
	JOIN ( SELECT id FROM t1 ORDER BY create_at DESC LIMIT 1065721, 20 ) t 
WHERE t.id = t1.id;

参考文档

mysql sql优化之 limit 分页优化

面试官：一千万的数据，你是怎么查询的？

一次 SQL 查询优化原理分析（900W+ 数据，从 17s 到 300ms）

MySQL 百万级数据分页查询及优化

上亿数据怎么玩深度分页？兼容MySQL + ES + MongoDB

MySQL如何设计索引更高效？

实战！聊聊如何解决MySQL深分页问题