MySQL 查询之分页优化

最新推荐文章于 2021-07-23 12:34:37 发布

hidespace

最新推荐文章于 2021-07-23 12:34:37 发布

阅读量444

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/navioo/article/details/51873220

版权

MySQL 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

<?php
/*
SELECT * FROM `tbl_user` limit 10,20; -- 20 rows in set (0.00 sec)
SELECT * FROM `tbl_user` limit 100,20; -- 20 rows in set (0.00 sec)
SELECT * FROM `tbl_user` limit 1000,20; -- 20 rows in set (0.00 sec)
SELECT * FROM `tbl_user` limit 10000,20; -- 20 rows in set (0.01 sec)
SELECT * FROM `tbl_user` limit 100000,20; -- 20 rows in set (0.06 sec)
SELECT * FROM `tbl_user` limit 1000000,20; -- 20 rows in set (0.37 sec)
SELECT * FROM `tbl_user` limit 1900000,20; -- 20 rows in set (0.69 sec)

优化：利用覆盖索引

（1）条件查询：id>=
SELECT id FROM tbl_user limit 1900000,1; -- 20 rows in set (0.40 sec) => 1900001
SELECT * FROM `tbl_user` WHERE id>= (SELECT id FROM tbl_user limit 1900000,1) limit 20; --20 rows in set (0.41 sec)

（2）联接：join
SELECT * FROM tbl_user a JOIN (SELECT id FROM tbl_user limit 1900000,20) b ON a.id=b.id; ----20 rows in set (0.38 sec)