Mysql limit 性能分析

1、mysql 中 limit 的使用：

       使用说明： limit 支持两个参数，offset 和 limit。offset 表示偏移量，limit 表示要取的数量。

        示例：返回第11-15条数据。

SELECT * FROM t_user WHERE LIMIT 10, 5;

2、性能分析：

        当 offset 较小是，查询很快，查询速率基本不受 offset 影响，但是当 offset 很大，一般offset 达到几百万或者几千万，查询性能明细降低。

        2、1 创建测试数据表，填入5000000条测试数据

                快速插入大量数据参考：https://www.jb51.net/article/161712.htm

        2、2 执行sql：

SELECT * FROM t_user WHERE `c_user_id` != '' LIMIT 3000000, 5;

          2、3 原因分析：我们发现，此时执行时间22.28秒，我们可以查看一下此时的 buffer pool

select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('idx_user_id','primary') and TABLE_NAME like '%t_user%' group by index_name;

可以看到 buffer pool 关于user表有3955个数据页，2171个索引页，加载太多的数据页导致sql变得很慢。

        2、4 优化：

                2、4、1 使用id优化，我们可以记录上次查询的最后一个id，通过添加id的判断条件来优化查询。

SELECT * FROM t_user WHERE id > 3000000 AND `c_user_id` != '' LIMIT 5;

                

                 2、4、2  子查询方式优化

SELECT * FROM (SELECT id FROM t_user limit 3000000,5) a LEFT JOIN t_user b on a.id = b.id;

                 我们可以看到此时性能都有较大提升，此时再次分析buffer pool，就能看到加载的数据页较之前减少了很多。