加入收藏 | 设为首页 | 会员中心 | 我要投稿 PHP编程网 - 钦州站长网 （https://www.0777zz.com/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 站长学院 > MySql教程 > 正文

mysql 分页优化 探究

发布时间：2022-11-25 12:36:02 所属栏目：MySql教程 来源：
导读：　　问题：

　　mysql 使用limit 进行分页，当页码不断增加的时候，查询效率不断降低（实际扫描行数增加）。

　　场景：

　　数据库版本 ：mysql 8

　　3个表关联，查出结果集，按 某字段排序，然后
　　问题：
 
　　mysql 使用limit 进行分页，当页码不断增加的时候，查询效率不断降低（实际扫描行数增加）。
 
　　场景：
 
　　数据库版本 ：mysql 8
 
　　3个表关联，查出结果集，按 某字段排序，然后进行分页（一般情况下MySQL分页查询，可以按主键或者时间字段排序）
 
　　构造数据
 
　　create table t1 (
 
　　id int auto_increment primary key,
 
　　name1 varchar(20),
 
　　name2 varchar(20),
 
　　name3 varchar(20)
 
　　);
 
　　create table t2 (
 
　　id int auto_increment primary key,
 
　　name1 varchar(20),
 
　　name2 varchar(20),
 
　　name3 varchar(20)
 
　　);
 
　　create table t3 (
 
　　id int auto_increment primary key,
 
　　name1 varchar(20),
 
　　name2 varchar(20),
 
　　name3 varchar(20)
 
　　);
 
　　drop procedure testdb.p_create_data;
 
　　delimiter $$
 
　　create procedure testdb.p_create_data(cnt int)
 
　　begin
 
　　declare s int default 1;
 
　　while s'b534'
 
　　and t2.name2>'bb111'
 
　　and t3.name2>'bbb222'
 
　　limit 3000000,10
 
　　分页吗 20万
 
　　--0.27
 
　　50 万 0.69
 
　　100 万 1.35s
 
　　200 2.67s
 
　　300万 4.s
 
　　+----+-------------+-------+------------+-------+---------------+--------+---------+-----------------+---------+----------+-----------------------+
 
　　| id | select_type | table | partitions | type | possible_keys | key | key_len | ref| rows | filtered | Extra|
 
　　+----+-------------+-------+------------+-------+---------------+--------+---------+-----------------+---------+----------+-----------------------+
 
　　| 1 | SIMPLE| t1 | NULL| range | idx_t1| idx_t1 | 63| NULL| 1594921 | 100.00 | Using index condition |
 
　　| 1 | SIMPLE| t3 | NULL| ref | idx_t3| idx_t3 | 63| testdb.t1.name1 |32 | 33.33 | Using index condition |
 
　　| 1 | SIMPLE| t2 | NULL| ref | idx_t2| idx_t2 | 63| testdb.t1.name1 |32 | 33.33 | Using index condition |
 
　　+----+-------------+-------+------------+-------+---------------+--------+---------+-----------------+---------+----------+-----------------------+
 
　　换一种写法来分页：
 
　　select t1.*,t2.* ,t3.*
 
　　from t1 join t2 on t1.name1=t2.name1
 
　　join t3 on t2.name1=t3.name1
 
　　where
 
　　t1.id >= (
 
　　select t1.id
 
　　from t1 join t2 on t1.name1=t2.name1
 
　　join t3 on t2.name1=t3.name1
 
　　where
 
　　t1.name2 >'b534'
 
　　and t2.name2>'bb111'
 
　　and t3.name2>'bbb222'
 
　　limit 3000000,1)
 
　　limit 10;
 
　　+----+-------------+-------+------------+-------+---------------+---------+---------+-----------------+---------+----------+--------------------------+
 
　　| id | select_type | table | partitions | type | possible_keys | key| key_len | ref| rows | filtered | Extra|
 
　　+----+-------------+-------+------------+-------+---------------+---------+---------+-----------------+---------+----------+--------------------------+
 
　　| 1 | PRIMARY| t1 | NULL| range | PRIMARY| PRIMARY | 4| NULL| 1594921 | 100.00 | Using where|
 
　　| 1 | PRIMARY| t3 | NULL| ref | idx_t3| idx_t3 | 63| testdb.t1.name1 |32 | 100.00 | NULL|
 
　　| 1 | PRIMARY| t2 | NULL| ref | idx_t2| idx_t2 | 63| testdb.t1.name1 |32 | 100.00 | NULL|
 
　　| 2 | SUBQUERY | t1 | NULL| range | idx_t1| idx_t1 | 63| NULL| 1594921 | 100.00 | Using where; Using index |
 
　　| 2 | SUBQUERY | t3 | NULL| ref | idx_t3| idx_t3 | 63| testdb.t1.name1 |32 | 33.33 | Using where; Using index |
 
　　| 2 | SUBQUERY | t2 | NULL| ref | idx_t2| idx_t2 | 63| testdb.t1.name1 |32 | 33.33 | Using where; Using index |
 
　　+----+-------------+-------+------------+-------+---------------+---------+---------+-----------------+---------+----------+--------------------------+
 
　　300万 1.14.s
 
　　注意：这种写法的条件是无序，或者按主键排序可用。所以表设计比较重要，采用星型设计的话，limit 和排序操作 可以直接在主表上进行，然后再进行连接。
 
　　再测试按 非主键其他字段排序
 
　　select t1.*,t2.* ,t3.*
 
　　from t1 join t2 on t1.name1=t2.name1
 
　　join t3 on t2.name1=t3.name1
 
　　where
 
　　t1.name2 >'b534'
 
　　and t2.name2>'bb111'
 
　　and t3.name2>'bbb222'
 
　　order by t1.name3 desc
 
　　limit 3000000,10
 
　　新建索引
 
　　create index idx_t1_2 on t1 (name2,name1,name3);
 
　　+----+-------------+-------+------------+-------+-----------------+----------+---------+-----------------+---------+----------+------------------------------------------+
 
　　| id | select_type | table | partitions | type | possible_keys | key| key_len | ref| rows | filtered | Extra|
 
　　+----+-------------+-------+------------+-------+-----------------+----------+---------+-----------------+---------+----------+------------------------------------------+
 
　　| 1 | SIMPLE| t1 | NULL| range | idx_t1,idx_t1_2 | idx_t1_2 | 63| NULL| 1594921 | 100.00 | Using where; Using index; Using filesort |
 
　　| 1 | SIMPLE| t3 | NULL| ref | idx_t3| idx_t3 | 63| testdb.t1.name1 |32 | 33.33 | Using index condition|
 
　　| 1 | SIMPLE| t2 | NULL| ref | idx_t2| idx_t2 | 63| testdb.t1.name1 |32 | 33.33 | Using index condition|
 
　　+----+-------------+-------+------------+-------+-----------------+----------+---------+-----------------+---------+----------+------------------------------------------+
 
　　由于有排序操作目前 300万分页 耗时高达 7.22 s
 
　　改写之后：
 
　　select t1.*,t2.* ,t3.*
 
　　from t1 join t2 on t1.name1=t2.name1
 
　　join t3 on t2.name1=t3.name1
 
　　where
 
　　t1.id >= (
 
　　select t1.id
 
　　from t1 join t2 on t1.name1=t2.name1
 
　　join t3 on t2.name1=t3.name1
 
　　where
 
　　t1.name2 >'b534'
 
　　and t2.name2>'bb111'
 
　　and t3.name2>'bbb222'
 
　　order by t1.name3 desc
 
　　limit 3000000,1)
 
　　limit 10;
 
　　+----+-------------+-------+------------+-------+-----------------+----------+---------+-----------------+---------+----------+------------------------------------------+
 
　　| id | select_type | table | partitions | type | possible_keys | key| key_len | ref| rows | filtered | Extra|
 
　　+----+-------------+-------+------------+-------+-----------------+----------+---------+-----------------+---------+----------+------------------------------------------+
 
　　| 1 | PRIMARY| t1 | NULL| range | PRIMARY| PRIMARY | 4| NULL| 1594921 | 100.00 | Using where|
 
　　| 1 | PRIMARY| t3 | NULL| ref | idx_t3| idx_t3 | 63| testdb.t1.name1 |32 | 100.00 | NULL|
 
　　| 1 | PRIMARY| t2 | NULL| ref | idx_t2| idx_t2 | 63| testdb.t1.name1 |32 | 100.00 | NULL|
 
　　| 2 | SUBQUERY | t1 | NULL| range | idx_t1,idx_t1_2 | idx_t1_2 | 63| NULL| 1594921 | 100.00 | Using where; Using index; Using filesort |
 
　　| 2 | SUBQUERY | t3 | NULL| ref | idx_t3| idx_t3 | 63| testdb.t1.name1 |32 | 33.33 | Using where; Using index|
 
　　| 2 | SUBQUERY | t2 | NULL| ref | idx_t2| idx_t2 | 63| testdb.t1.name1 |32 | 33.33 | Using where; Using index|
 
　　+----+-------------+-------+------------+-------+-----------------+----------+---------+-----------------+---------+----------+------------------------------------------+
 
　　结论：耗时 4.26s 也是有部分提升的(约1倍)，如果分页码继续加大，在极端情况下，百万级，千万级下 常规索引覆盖优化就能满足需求。再大的量级 后面情况只能使用修改设计（修改库表结构，分表等）的方式来处理此场景了。
 

（编辑：PHP编程网 - 钦州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
      站长推荐
      热点阅读

      【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

      建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

      Copygight © 2013-2022 https://www.0777zz.com/ All Rights Reserved. PHP编程网 - 钦州站长网

      ICP备案：浙ICP备07501134号 浙公网安备 33038102330484号