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

MySQL实验 子查询优化双参数limit 没想到双参数limit还有优化的余地,为了亲眼见到,今天来亲自实验一下。 实验准备 使用MySQL官方的大数据库employees进行实验,导入该示例库见此 准备使...

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:

  • 第二行subquery指的是子查询,那么我们先从子查询看起

    type为index,说明我们使用了索引树加速查询

    key为primary key,说明我们使用了主键索引,子查询直接在主键索引树上进行了查询,避免了回表,减少了磁盘I/O

  • 第一行则是外部的查询

    type为range说明是范围查询,然后也使用了主键索引树

    而Using index是指,仅使用索引树中的信息从表中检索列信息,而无需执行其他查找即可读取实际行。

小结

对于不需要order by的直接的双参数limit,我们可以借助覆盖索引的方式优化查询效率。

优化order by+limit见此处

最后的补充

仅对于双参数limit的优化,除了子查询外还有以下方法:

  • 倒排表优化法

    倒排表法类似建立索引,用一张表来维护页数,然后通过高效的连接得到数据。缺点:只适合数据数固定的情况,数据不能删除,维护页表困难

  • 反向查找优化法

    当偏移超过一半记录数的时候,先用排序,这样偏移就反转了

    缺点:order by优化比较麻烦,要增加索引,索引影响数据的修改效率,并且要知道总记录数

    偏移大于数据的一半

    limit偏移算法:

    正向查找: (当前页 – 1) * 页长度

    反向查找: 总记录 – 当前页 * 页长度

有时间在进行实验

  • 发表于 2020-07-07 13:20
  • 阅读 ( 77 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除