站长家园(原代码之家)(www.adminjie.com)网站源码,微信源码,游戏源码,商业源码分享平台。
当前位置:网站首页 技术文章 网络编程 正文

mysql分页查询怎么优化

时间:2022-06-21 [网络编程]作者:fabuyuan 浏览:1 次

分页查询的优化方式:1、子查询优化,可通过把分页的SQL语句改写成子查询的方法获得性能上的提升。2、id限定优化,可以根据查询的页数和查询的记录数计算出查询的id的范围,然后根据“id between and”语句来查询。3、基于索引再排序进行优化,通过索引去找相关的数据地址,避免全表扫描。4、延迟关联优化,可以使用JOIN,先在索引列上完成分页操作,然后再回表获取所需要的列。

本教程操作环境:windows7系统、mysql8版本、Dell G3电脑。

分页查询的效率在数据量大的时候尤为重要,影响到前端响应和用户体验。

分页查询的优化方式

1、使用子查询优化

这种方式先定位偏移位置的 id,然后往后查询,这种方式适用于 id 递增的情况。

子查询优化原理:https://www.jianshu.com/p/0768ebc4e28d

select * from sbtest1 where k=504878 limit 100000,5;的查询过程:

首先会查询到索引叶子节点数据,然后根据叶子节点上的主键值去聚簇索引上查询需要的全部字段值。像下图左边这样,需要查询100005次索引节点,查询100005次聚簇索引的数据,最后再将结果过滤掉前100000条,取出最后5条。MySQL耗费了大量随机I/O在查询聚簇索引的数据上,而有100000次随机I/O查询到的数据是不会出现在结果集当中的。

既然一开始是利用索引的,为什么不先沿着索引叶子节点查询到最后需要的5个节点,然后再去聚簇索引中查询实际数据。这样只需要5次随机I/O,类似于上图右边的过程。这就是子查询优化,这种方式先定位偏移位置的id,然后往后查询,这种方式适用于id递增的情况。如下所示:

mysql> select *  from sbtest1 where k=5020952 limit 50,1;
mysql> select id  from sbtest1 where k=5020952 limit 50,1;
mysql> select * from sbtest1 where k=5020952 and id>=( select id  from sbtest1 where k=5020952 limit 50,1) limit 10;
mysql> select * from sbtest1 where k=5020952 limit 50,10;

在子查询优化中,谓词中k是否有索引,对查询效率有很大影响,上述语句没有使用索引走全表扫描需要24.2s,走了索引后只需要0.67s。

mysql> explain  select * from sbtest1 where k=5020952 and id>=( select id  from sbtest1 where k=5020952 limit 50,1) limit 10;
+----+-------------+---------+------------+-------------+---------------+------------+---------+-------+------+----------+------------------------------------------+
| id | select_type | table   | partitions | type        | possible_keys | key        | key_len | ref   | rows | filtered | Extra                                    |
+----+-------------+---------+------------+-------------+---------------+------------+---------+-------+------+----------+------------------------------------------+
|  1 | PRIMARY     | sbtest1 | NULL       | index_merge | PRIMARY,c1    | c1,PRIMARY | 8,4     | NULL  |   19 |   100.00 | Using intersect(c1,PRIMARY); Using where |
|  2 | SUBQUERY    | sbtest1 | NULL       | ref         | c1            | c1         | 4       | const |   88 |   100.00 | Using index                              |
+----+-------------+---------+------------+-------------+---------------+------------+---------+-------+------+----------+------------------------------------------+
2 rows in set, 1 warning (0.11 sec)

但是这种优化方法也有局限性:

  • 这种写法,要求主键ID必须是连续的

  • Where子句不允许再添加其他条件

2、使用id限定优化

这种方式假设数据表的id是连续递增的,则我们根据查询的页数和查询的记录数可以算出查询的id的范围,可以使用 id between and 来查询。

假设数据库中表的id是连续递增的,则可以根据查询的页数和查询的记录数计算出查询的id的范围,然后根据id between and语句来查询。id的范围可以通过分页公式计算得到,比如说当前页面大小为m,当前页数为no1,则页面最大值为max=(no1+1)m-1,最小值为min=no1m,SQL语句可以表示为id between min and max。

select * from sbtest1 where id between 1000000 and 1000100 limit 100;

这种查询方式能够极大地优化查询速度,基本能够在几十毫秒之内完成。限制是需要明确知道id的情况,不过一般在分页查询的业务表中,都会添加基本的id字段,这为分页查询带来很多便利。上述SQL还有另一种写法:

select * from sbtest1 where id >= 1000001 limit 100;

可以看到执行时间上的差异:

mysql> show profiles;
+----------+------------+--------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                        |
+----------+------------+--------------------------------------------------------------------------------------------------------------+
|        6 | 0.00085500 | select * from sbtest1 where id between 1000000 and 1000100 limit 100                                         |
|        7 | 0.12927975 | select * from sbtest1 where id >= 1000001 limit 100                                                          |
+----------+------------+--------------------------------------------------------------------------------------------------------------+

还可以使用in的方式来进行查询,这种方式经常用在多表关联的时候进行查询,使用其他表查询的id集合,来进行查询:

select * from sbtest1 where id in (select id from sbtest2 where k=504878) limit 100;

使用in查询的方式要注意某些mysql版本不支持在in子句中使用limit。

3、基于索引再排序来优化

基于索引再排序是利用索引查询中有优化算法,通过索引再去找相关的数据地址,避免全表扫描,这样节省了很多时间。另外Mysql中也有相关的索引缓存,在并发高的时候利用缓存效果会更好。在MySQL中可以使用如下语句:

SELECT * FROM 表名称 WHERE id_pk > (pageNum*10) ORDER BY id_pk ASC LIMIT M

这种方法适用于数据量多的情况(元组数上万),最好ORDER BY后的列对象是主键或唯一索引,使得ORDER BY操作能利用索引被消除但结果集是稳定的。比如下面两个语句:

mysql> show profiles;
+----------+------------+--------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                        |
+----------+------------+--------------------------------------------------------------------------------------------------------------+
|        8 | 3.30585150 | select * from sbtest1 limit 1000000,10                                                                       |
|        9 | 1.03224725 | select * from sbtest1 order by id limit 1000000,10                                                           |
+----------+------------+--------------------------------------------------------------------------------------------------------------+

对索引字段id使用order by语句后,性能有了明显的提升。

4、使用延迟关联来优化

和上述的子查询做法类似,我们可以使用JOIN,先在索引列上完成分页操作,然后再回表获取所需要的列。

select a.* from t5 a inner join (select id from t5 order by text limit 1000000, 10) b on a.id=b.id;

从实验中可以得出,在采用JOIN改写后,上面的两个局限性都已经解除了,而且SQL的执行效率也没有损失。

5、记录上次查询结束的位置

和上面使用的方法都不同,记录上次结束位置优化思路是使用某种变量记录上一次数据的位置,下次分页时直接从这个变量的位置开始扫描,从而避免MySQL扫描大量的数据再抛弃的操作。

select * from t5 where id>=1000000 limit 10;

6、使用临时表优化

使用临时存储的表来记录分页的id然后进行in查询

这种方式已经不属于查询优化,这儿附带提一下。

对于使用 id 限定优化中的问题,需要 id 是连续递增的,但是在一些场景下,比如使用历史表的时候,或者出现过数据缺失问题时,可以考虑使用临时存储的表来记录分页的id,使用分页的id来进行 in 查询。这样能够极大的提高传统的分页查询速度,尤其是数据量上千万的时候。

【相关推荐:mysql视频教程

以上就是mysql分页查询怎么优化的详细内容,更多请关注站长家园其它相关文章!

本文标签:  mysql

转载请注明来源:mysql分页查询怎么优化

本文永久链接地址:https://www.adminjie.com/post/13736.html

免责声明:
本站所发布的一切资源仅限用于学习和研究目的;不得将上述内容用于商业或者非法用途,否则,一切后果请用户自负。本站信息来自网络,版权争议与本站无关。您必须在下载后的24个小时之内,从您的电脑中彻底删除上述内容。如果您喜欢该程序,请支持正版软件,购买注册,得到更好的正版服务。

附:
二○○二年一月一日《计算机软件保护条例》第十七条规定:为了学习和研究软件内含的设计思想和原理,通过安装、显示、传输或者存储软件等方式使用软件的,可以不经软件著作权人许可,不向其支付报酬!鉴于此,也希望大家按此说明研究软件!

版权声明:
一、本站致力于为软件爱好者提供国内外软件开发技术和软件共享,着力为用户提供优资资源。
二、本站提供的部分源码下载文件为网络共享资源,请于下载后的24小时内删除。如需体验更多乐趣,还请支持正版。
三、我站提供用户下载的所有内容均转自互联网。如有内容侵犯您的版权或其他利益的,若有侵犯你的权益请:提交版权证明文件到邮箱 2225329873#qq.com(#换为@) 站长会进行审查之后,情况属实的会在三个工作日内为您删除。

  • 站长家园(原代码之家)会员升级
  • 最新文章
    • mysql分页查询怎么优化

      mysql分页查询怎么优化

      分页查询的优化方式:1、子查询优化,可通过把分页的SQL语句改写成子查询的方法获得性能上的提升。2、id限定优化,可以根据查询的页数和查询的记录数计算出查询的i...

    • sql与mysql有什么区别

      sql与mysql有什么区别

      区别:1、SQL是一种结构化查询语言,而MySQL是一种数据库软件,使用SQL语言来查询数据库;2、SQL不提供连接工具,而MySQL提供工具,可用一个名为“M...

    • 怎么让mysql不区分大小写

      怎么让mysql不区分大小写

      让mysql不区分大小写的方法:1、进入mysql的安装目录,找到并打开配置文件“my.ini”;2、在配置文件的最后一行加上“lower_case_table...

    • mysql的安装路径怎么查看

      mysql的安装路径怎么查看

      查看方法:1、鼠标右击“计算机”图标,在打开的菜单中点击“管理”;2、依次点击“服务和应用程序”-“服务”;3、在右侧服务列表中,找到mysql服务;4、选中m...

    • css3的新增属性有没有设置文本溢出的样式

      css3的新增属性有没有设置文本溢出的样式

      css3新增的属性有设置文本溢出样式的,可以利用“text-overflow”属性设置文本溢出的样式;该属性用于指定当文本溢出包含文本的元素时应该如何显示,可以...

    热门文章