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有索引
实验过程
老版本explain推荐参考博客（即新版本默认explain extended）
使用未优化双参数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行。
（0813修正）limit N,M : 相当于 limit M offset N, 从第 N 条记录开始, 返回 M 条记录
说明双参数limit就是在排序后一直扫描到偏移量的所指的地方（这里是第200010行），然后读取10行再扔掉前200000行。
子查询优化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：
-
第二行subquery指的是子查询，那么我们先从子查询看起
type为index，说明我们使用了索引树加速查询
key为primary key，说明我们使用了主键索引，子查询直接在主键索引树上进行了查询，避免了回表，减少了磁盘I/O
-
第一行则是外部的查询
type为range说明是范围查询，然后也使用了主键索引树
而Using index是指，仅使用索引树中的信息从表中检索列信息，而无需执行其他查找即可读取实际行。
小结
对于不需要order by的直接的双参数limit，我们可以借助覆盖索引的方式优化查询效率。
最后的补充
仅对于双参数limit的优化，除了子查询外还有以下方法：
-
倒排表优化法
倒排表法类似建立索引，用一张表来维护页数，然后通过高效的连接得到数据。缺点：只适合数据数固定的情况，数据不能删除，维护页表困难
-
反向查找优化法
当偏移超过一半记录数的时候，先用排序，这样偏移就反转了
缺点：order by优化比较麻烦，要增加索引，索引影响数据的修改效率，并且要知道总记录数
偏移大于数据的一半
limit偏移算法：
正向查找： (当前页 – 1) * 页长度
反向查找： 总记录 – 当前页 * 页长度
有时间在进行实验
