服务器之家:专注于服务器技术及软件下载分享
分类导航

Mysql|

MySQL分页Limit的优化过程实战

2019-06-27 15:10旧梦发癫 Mysql

在mysql中进行分页查询时,一般会使用limit查询,下面这篇文章主要给大家介绍了关于MySQL分页Limit优化的相关资料,文中通过示例代码介绍的非常详细,需要的朋友可以参考借鉴,下面随着小编来一起学习学习吧

前言

在我们使用查询语句的时候,经常要返回前几条或者中间某几行数据,这个时候怎么办呢?不用担心,mysql已经为我们提供了这样一个功能。

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset 

LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1): 为了与 PostgreSQL 兼容,MySQL 也支持句法: LIMIT # OFFSET #。

所以通常在查询数据的时候,我们都会用到limit分页,因为这样避免了全表查询,会提高查询效率。但是在一个表的数据量多了之后,分页查询会明细的变慢,下面来一起看看详细的介绍吧

MySQL分页Limit优化

创建测试表card 2000万数据

mysql> select count(*) from card;
+----------+
| count(*) |
+----------+
| 20000000 |
+----------+
1 row in set (0.00 sec)

-首先测试前1000行查询速度

mysql> select * from card limit 1000,10;
+---------+--------------------------------------+
| card_id | card_number       |
+---------+--------------------------------------+
| 1001 | 13fc90a6-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1002 | 13fc923e-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1003 | 13fc93d5-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1004 | 13fc956a-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1005 | 13fc9702-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1006 | 13fc9899-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1007 | 13fc9a31-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1008 | 13fc9bc6-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1009 | 13fc9d5e-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1010 | 13fc9ef5-2e3b-11e8-ae62-9c5c8e6e37cf |
+---------+--------------------------------------+
10 rows in set (0.00 sec)

-测试100万之后的查询

mysql> select * from card limit 1000000,10;
+---------+--------------------------------------+
| card_id | card_number       |
+---------+--------------------------------------+
| 1000001 | 2d87021a-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000002 | 2d8703ac-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000003 | 2d87053b-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000004 | 2d8706cd-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000005 | 2d87085f-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000006 | 2d8709f1-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000007 | 2d870b83-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000008 | 2d870d18-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000009 | 2d870eaa-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000010 | 2d871039-2e3b-11e8-ae62-9c5c8e6e37cf |
+---------+--------------------------------------+
10 rows in set (0.18 sec)

-测试1000万之后的查询

mysql> select * from card limit 10000000,10;
+----------+--------------------------------------+
| card_id | card_number       |
+----------+--------------------------------------+
| 10000001 | b11ad76c-2e49-11e8-ae62-9c5c8e6e37cf |
| 10000002 | b11aefd5-2e49-11e8-ae62-9c5c8e6e37cf |
| 10000003 | b11af868-2e49-11e8-ae62-9c5c8e6e37cf |
| 10000004 | b11b0031-2e49-11e8-ae62-9c5c8e6e37cf |
| 10000005 | b11b07ad-2e49-11e8-ae62-9c5c8e6e37cf |
| 10000006 | b11b0f0f-2e49-11e8-ae62-9c5c8e6e37cf |
| 10000007 | b11b1669-2e49-11e8-ae62-9c5c8e6e37cf |
| 10000008 | b11b1db2-2e49-11e8-ae62-9c5c8e6e37cf |
| 10000009 | b11b24fa-2e49-11e8-ae62-9c5c8e6e37cf |
| 10000010 | b11b2c37-2e49-11e8-ae62-9c5c8e6e37cf |
+----------+--------------------------------------+
10 rows in set (1.29 sec)

可以看到越到后面查询效率会越低。因为在查询100万之后的数据的时候,mysql会首先查询100万零10条数据,然后截取后面的十条数据。这些就造成的性能的降低。

那么怎么去避免这个扫描100万条数据呢。我们可以明确的知道,100万之后的主键是大于100万的。所以我们可以将sql改写,让其用到索引,降低扫描的行数

mysql> select * from card where card_id>=1000000 limit 10;
+---------+--------------------------------------+
| card_id | card_number       |
+---------+--------------------------------------+
| 1000000 | 2d870088-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000001 | 2d87021a-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000002 | 2d8703ac-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000003 | 2d87053b-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000004 | 2d8706cd-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000005 | 2d87085f-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000006 | 2d8709f1-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000007 | 2d870b83-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000008 | 2d870d18-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000009 | 2d870eaa-2e3b-11e8-ae62-9c5c8e6e37cf |
+---------+--------------------------------------+
10 rows in set (0.00 sec)

这样就可以很大的提高查询效率

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对服务器之家的支持。

延伸 · 阅读

精彩推荐
  • MysqlUbuntu18.04安装mysql5.7.23的教程

    Ubuntu18.04安装mysql5.7.23的教程

    这篇文章主要为大家详细介绍了Ubuntu18.04安装mysql5.7.23的教程,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...

    登峰小蚁2902019-06-05
  • MysqlMySQL通过show processlist命令检视性能的讲解

    MySQL通过show processlist命令检视性能的讲解

    今天小编就为大家分享一篇关于MySQL通过show processlist命令检视性能的讲解,小编觉得内容挺不错的,现在分享给大家,具有很好的参考价值,需要的朋友一起跟随小编来看看吧...

    CODETC5022019-06-24
  • Mysqlmysql如何利用binlog进行数据恢复详解

    mysql如何利用binlog进行数据恢复详解

    MySQL的binlog日志是MySQL日志中非常重要的一种日志,下面这篇文章主要给大家介绍了关于mysql如何利用binlog进行数据恢复的相关资料,文中通过示例代码介绍的非常详细,需要的朋友可以参考下...

    陈芳志2692019-06-20
  • MysqlRR与RC隔离级别下索引和锁的测试脚本示例代码

    RR与RC隔离级别下索引和锁的测试脚本示例代码

    这篇文章主要给大家介绍了关于RR与RC隔离级别下索引和锁的测试脚本的相关资料,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧...

    stuartlau3892019-06-14
  • Mysqlmysql导出表的字段和相关属性的步骤方法

    mysql导出表的字段和相关属性的步骤方法

    在本篇文章里小编给大家分享了关于mysql导出表的字段和相关属性的步骤方法,有需要的朋友们跟着学习下。...

    laozhang3052019-06-09
  • MysqlMysql主从数据库(Master/Slave)同步配置与常见错误

    Mysql主从数据库(Master/Slave)同步配置与常见错误

    今天小编就为大家分享一篇关于Mysql主从数据库(Master/Slave)同步配置与常见错误,小编觉得内容挺不错的,现在分享给大家,具有很好的参考价值,需要的朋友一起跟随小编来看看吧...

    CODETC3802019-06-24
  • Mysql优化InnoDB表BLOB,TEXT列的存储效率

    优化InnoDB表BLOB,TEXT列的存储效率

    今天小编就为大家分享一篇关于优化InnoDB表BLOB,TEXT列的存储效率,小编觉得内容挺不错的,现在分享给大家,具有很好的参考价值,需要的朋友一起跟随小编来看看吧...

    CODETC3472019-06-01
  • Mysql基于更新SQL语句理解MySQL锁定详解

    基于更新SQL语句理解MySQL锁定详解

    这篇文章主要给大家介绍了关于MySQL数据库SQL更新锁定的相关资料,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧...

    Leshami2632019-06-15