资讯专栏INFORMATION COLUMN

Mysql优化--Limit查询的优化考虑

bang590 / 2388人阅读

摘要:对于这种情况,最简单的查询就是使用覆盖索引,查询某些需要的列。这种叫延时关联,先通过使用覆盖索引查询返回需要的主键再根据主键关联原表获得需要的数据,尽可能的减少了需要扫描的行数。在某些特定的场合,其实有另外一种优化方案的。

在实际业务中对于分页来说是一个比较常见的业务需求。那么就会使用到limit查询,当我们在使用Limit查询的时候,在数据比较小、或者只查询前面一部分数据的时候效率是很高的。但是当数据量大的时候,或者查询offset数量比较大的时候,如:limit 100000,20效率往往就不尽人意了。通常的一个办法就是Limit配合order by,如果order by有对用户的索引的话,效率通常是比较不错的。
对于这种情况,最简单的查询就是 使用覆盖索引,查询某些需要的列。这样的效果是很好的
如下面这个

mysql>   SELECT * FROM student    LIMIT 1000000,1;
+---------+------------+------------+------------+-------+---------------------+
| id      | first_name | last_name  | created_at | score | updated_at          |
+---------+------------+------------+------------+-------+---------------------+
| 1000001 | kF9DxBgnUi | yLXnPSHJpH | 2019-07-11 |    97 | 2019-07-11 14:29:59 | |
+---------+------------+------------+------------+-------+---------------------+
1 rows in set (0.31 sec)

可以看到时间

mysql>  EXPLAIN SELECT score,first_name FROM student  ORDER BY created_at   LIMIT 1000000,20 G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: index
possible_keys: NULL
          key: time_sorce_name
      key_len: 69
          ref: NULL
         rows: 1000001
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

mysql>

这样的话查询的列使用到的了覆盖索引,扫描行数会减少很多,但是这样的效果也不是很尽人意,但是如果有其他的查询的话,这样的查询也会变的很慢。
比如我们加上last_name列。
如下

mysql>  SELECT score,first_name,last_name FROM student  ORDER BY created_at   LIMIT 1000000,1;
+-------+------------+------------+
| score | first_name | last_name  |
+-------+------------+------------+
|    86 | knKsV2g2fY | WB5qJeLZuk |
+-------+------------+------------+
1 row in set (4.81 sec)

mysql>

这个查询需要执行 4秒多的时间。通过分析可以看到这个查询是没有办法使用索引的

mysql> explain SELECT score,first_name,last_name FROM student  ORDER BY created_at   LIMIT 1000000,1G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6489221
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)

mysql>

那么我们现在把查询修改如下

mysql> SELECT student.score,student.first_name FROM student INNER JOIN (SELECT id FROM student  ORDER BY created_at   LIMIT 1000000,1 ) AS temp USING(id);
+-------+------------+
| score | first_name |
+-------+------------+
|    15 | 2QWZ       |
+-------+------------+
1 row in set (0.18 sec)
mysql> EXPLAIN SELECT student.score,student.first_name,last_name FROM student INNER JOIN (SELECT id FROM student  ORDER BY created_at   LIMIT 1000000,1 ) AS temp USING(id);
+----+-------------+------------+------------+--------+---------------+-----------------+---------+---------+---------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key             | key_len | ref     | rows    | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+-----------------+---------+---------+---------+----------+-------------+
|  1 | PRIMARY     |  | NULL       | ALL    | NULL          | NULL            | NULL    | NULL    | 1000001 |   100.00 | NULL        |
|  1 | PRIMARY     | student    | NULL       | eq_ref | PRIMARY       | PRIMARY         | 4       | temp.id |       1 |   100.00 | NULL        |
|  2 | DERIVED     | student    | NULL       | index  | NULL          | time_sorce_name | 69      | NULL    | 1000001 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+-----------------+---------+---------+---------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

分析结果,可以看到这个时候只查询了1000001一条数据记录。为什么会有这样的变化呢。这种叫延时关联先通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据,尽可能的减少了需要扫描的行数。

在某些特定的场合,其实有另外一种优化方案的。比如要获取最新的几条插入记录。那么在上一次查询的时候我们可以记录下最后一条记录的主键ID(last_id)。
那么查询就可以改为

SELECT score,first_name,last_name,id FROM student  WHERE id>=last_id ORDER BY id ASC   LIMIT 1

比如last_id=1000000那么这个查询就会从1000000开始。这样的场景不管数据到多大的offset性能都会很好。

文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。

转载请注明本文地址:https://www.ucloud.cn/yun/49414.html

相关文章

  • MySql查询性能优化

    摘要:如果缓存的是关联查询的结果,那么其中的一个表发生变化,整个缓存就失效了。例如上面用代替关联查询比随机的关联更加高效。优化关联查询,要确保或者子句中的列上有索引,并且在建立索引时需要考虑到关联的顺序。 避免向数据库请求不需要的数据 在访问数据库时,应该只请求需要的行和列。请求多余的行和列会消耗MySql服务器的CPU和内存资源,并增加网络开销。例如在处理分页时,应该使用LIMIT限制My...

    魏宪会 评论0 收藏0
  • Mysql分页&关联查询优化

    摘要:当升级的时候需要注意关联语法运算符优先级等其他可能会发生变化的地方。优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描,而不是查询所有的列。这个技术也可以用于优化关联查询中的子句。 以下内容参考《高性能Mysql》 优化关联查询 这个话题基本上整本书都在讨论,这里需要特别提到的是: 确保ON或者USING子句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。 当表A...

    RiverLi 评论0 收藏0
  • MySQL分页查询优化

    摘要:如果素所的页面被访问的频率都相同,那么这样的查询平均需要访问半个表的数据,要优化这种查询,要么在页面中限制分页的数量,要么是优化大偏移量的性能。优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖查询,而不是查询所有的列。 之前搬砖的时候遇到对行数大的表进行分页的操作,性能好差。最近在读《高性能MySQL》,正好讲到这个方面的,记录一下(基本上都是原文)。 优化LIMIT分页 在...

    wh469012917 评论0 收藏0
  • 单机数据库优化一些实践

    摘要:数据库连接池优化数据库连接池本质上是一种缓存,它是一种抗高并发的手段。数据库连接池优化主要是对参数进行优化,一般我们使用连接池,它的具体参数如下初始连接数,这里的初始指的是第一次的时候,而不是应用启动的时候。 0、前言 数据库优化有很多可以讲,按照支撑的数据量来分可以分为两个阶段:单机数据库和分库分表,前者一般可以支撑500W或者10G以内的数据,超过这个值则需要考虑分库分表。另外,一...

    ashe 评论0 收藏0
  • MySQL Optimization 优化原理

    摘要:有非常多的原因会导致选择错误的执行计划,比如统计信息不准确不会考虑不受其控制的操作成本用户自定义函数存储过程认为的最优跟我们想的不一样我们希望执行时间尽可能短,但值选择它认为成本小的,但成本小并不意味着执行时间短等等。 MySQL Optimization 优化原理 MySQL逻辑架构 如果能在头脑中构建一幅MySQL各组件之间如何协同工作的架构图,有助于深入理解MySQL服务器。下图...

    Terry_Tai 评论0 收藏0

发表评论

0条评论

最新活动
阅读需要支付1元查看
<