MySQL实验子查询优化双参数limitG

Z时代
2024-01-10
分类：综合

database

mysql实验-子查询优化双参数limit">MySQL实验子查询优化双参数limit

没想到双参数limit还有优化的余地，为了亲眼见到，今天来亲自实验一下。

实验准备

使用MySQL官方的大数据库employees进行实验，导入该示例库见此

准备使用其中的employees表，先查看一下表结构和表内的记录数量

mysql> desc employees;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int(11)       | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum("M","F") | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> select count(*) from employeed;
ERROR 1146 (42S02): Table "employees.employeed" doesn"t exist
mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
|   300024 |
+----------+
1 row in set (0.05 sec)

我们可以看到，只有主键emp_no有索引

实验过程

MySQL5.7官网对Explain各项参数的解释

explain参数5.7版本推荐参考博客

老版本explain推荐参考博客（即新版本默认explain extended）

关于explain参数的拓展链接

MySQL explain key值的解释

使用未优化双参数limit

运行一般情况下的双参数limit并explain：

mysql> select * from employees limit 200000,10;
+--------+------------+------------+------------+--------+------------+
| emp_no | birth_date | first_name | last_name  | gender | hire_date  |
+--------+------------+------------+------------+--------+------------+
| 299976 | 1952-12-08 | Kristian   | Kampfer    | M      | 1994-12-28 |
| 299977 | 1956-09-30 | Zsolt      | Benveniste | M      | 1994-08-15 |
| 299978 | 1956-08-08 | Anneli     | Kitai      | F      | 1994-08-09 |
| 299979 | 1953-03-18 | Satoru     | Kornyak    | F      | 1991-06-16 |
| 299980 | 1953-05-26 | Marsal     | Lovengreen | M      | 1988-05-09 |
| 299981 | 1960-06-22 | Claudi     | Mamelak    | M      | 1986-07-13 |
| 299982 | 1955-06-21 | Juichirou  | Hiraishi   | M      | 1989-12-17 |
| 299983 | 1964-11-19 | Bezalel    | Iacovou    | M      | 1998-02-22 |
| 299984 | 1961-11-03 | Frazer     | Birch      | M      | 1986-12-31 |
| 299985 | 1961-01-04 | Miomir     | Nergos     | F      | 1996-07-07 |
+--------+------------+------------+------------+--------+------------+
10 rows in set (0.06 sec)

mysql> explain select * from employees limit 200000,10;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 299468 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
1 row in set, 1 warning (0.00 sec)

我们对explain进行分析：

type为ALL，全表扫描，也就是说没有用索引，因此key和key_len都为NULL

表之间没有引用因此ref为NULL，这里是单表查询

partition为null说明没有使用/访问分区表

扫描了299468行（limit不会过滤null和空值，为什么是这个数据还望看官解答）

这里的filtered指，从存储引擎经过server层过滤后剩下有N%的数据满足查询条件，100%表示未对行进行筛选。

EXTRA额外解释：Using filesort，排序时无法使用到索引时

说明双参数limit就是在排序后一直扫描到偏移量的所指的地方（这里是第100001行），然后读取10行再扔掉前100000行。

子查询优化limit

优化思路：先在子查询中利用“覆盖索引”的方式先找出要选取的第一行数据的主键值，然后再从这里根据主键值选取10条数据

mysql> select * from employees where emp_no >= (select emp_no from employees limit 200000,1) limit 10;
+--------+------------+------------+------------+--------+------------+
| emp_no | birth_date | first_name | last_name  | gender | hire_date  |
+--------+------------+------------+------------+--------+------------+
| 299976 | 1952-12-08 | Kristian   | Kampfer    | M      | 1994-12-28 |
| 299977 | 1956-09-30 | Zsolt      | Benveniste | M      | 1994-08-15 |
| 299978 | 1956-08-08 | Anneli     | Kitai      | F      | 1994-08-09 |
| 299979 | 1953-03-18 | Satoru     | Kornyak    | F      | 1991-06-16 |
| 299980 | 1953-05-26 | Marsal     | Lovengreen | M      | 1988-05-09 |
| 299981 | 1960-06-22 | Claudi     | Mamelak    | M      | 1986-07-13 |
| 299982 | 1955-06-21 | Juichirou  | Hiraishi   | M      | 1989-12-17 |
| 299983 | 1964-11-19 | Bezalel    | Iacovou    | M      | 1998-02-22 |
| 299984 | 1961-11-03 | Frazer     | Birch      | M      | 1986-12-31 |
| 299985 | 1961-01-04 | Miomir     | Nergos     | F      | 1996-07-07 |
+--------+------------+------------+------------+--------+------------+
10 rows in set (0.03 sec)

可以看到，查询速度提高了一倍

mysql> explain select * from employees where emp_no >= (select emp_no from employees limit 200000,1) limit 10;
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | PRIMARY     | employees | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 149734 |   100.00 | Using where |
|  2 | SUBQUERY    | employees | NULL       | index | NULL          | PRIMARY | 4       | NULL | 299468 |   100.00 | Using index |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
2 rows in set, 1 warning (0.03 sec)

分析explain：