Mysql优化技巧之Limit查询的示例分析

共计 3677 个字符，预计需要花费 10 分钟才能阅读完成。

丸趣 TV 小编给大家分享一下 Mysql 优化技巧之 Limit 查询的示例分析，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！

前言

在实际业务中对于分页来说是一个比较常见的业务需求。那么就会使用到 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,1\G
*************************** 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 |  derived2  | 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 性能都会很好。

看完了这篇文章，相信你对“Mysql 优化技巧之 Limit 查询的示例分析”有了一定的了解，如果想了解更多相关知识，欢迎关注丸趣 TV 行业资讯频道，感谢各位的阅读！