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行，
（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，我們可以借助覆寫索引的方式優化查詢效率，
優化order by+limit見此處
最后的補充
僅對于雙引數limit的優化，除了子查詢外還有以下方法：
-
倒排表優化法
倒排表法類似建立索引，用一張表來維護頁數，然后通過高效的連接得到資料，缺點：只適合資料數固定的情況，資料不能洗掉，維護頁表困難
-
反向查找優化法
當偏移超過一半記錄數的時候，先用排序，這樣偏移就反轉了
缺點：order by優化比較麻煩，要增加索引，索引影響資料的修改效率，并且要知道總記錄數
偏移大于資料的一半
limit偏移演算法：
正向查找： (當前頁 – 1) * 頁長度
反向查找： 總記錄 – 當前頁 * 頁長度
有時間在進行實驗
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/16412.html
標籤：MySQL
上一篇：Mariadb之復制過濾器