作者：sunpy
連結：https://www.jianshu.com/p/c5fb2dadbfe4
準備工作
# 總記錄數為500000
mysql> selectcount(id) from edu_test;
+-----------+
| count(id) |
+-----------+
| 500000 |
+-----------+
1row inset (0.05 sec)
分析過程
從0開始查詢10條：
mysql> select * from edu_test limit 0, 10;
10 rows inset (0.05 sec)
從20萬開始查詢10條：
mysql> select * from edu_test limit 200000, 10;
10 rows inset (0.14 sec)
從50萬開始查詢10條：
mysql> select * from edu_test limit 499000, 10;
10 rows inset (0.21 sec)
現象：隨著分頁越深入，查詢的時間也越來越長。
mysql> explain select * from edu_test limit 200000, 10;
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------+
|1| SIMPLE | edu_test | NULL | ALL | NULL | NULL | NULL | NULL | 499483 |100.00| NULL |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------+
1 row in set (0.09 sec)
思考：limit分頁做了一個全表掃描，掃描後將從200000開始往後取10條記錄返回。
最佳化
思路：
快速定位到要存取的數據行，縮小掃描範圍。
方案1
延遲查詢（先定位再查詢）.
方案2、方案3
方案1：透過有序唯一索引縮小掃描範圍
前提必須要id有序，要不然結果會漏掉一部份數據的。
mysql> select * from edu_test where id > 499000orderby id asc limit 10;
10 rows inset (0.14 sec)
mysql> explain select * from edu_test where id > 499000orderby id asc limit 10;
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | edu_test | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 1000 | 100.00 | Usingwhere |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row inset (0.16 sec)
# 再縮小掃描範圍
mysql> select * from edu_test where id between 499000and499020orderby id asc limit 10;
10 rows inset (0.09 sec)
mysql> explain select * from edu_test where id between 499000and499020orderby id asc limit 10;
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | edu_test | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 21 | 100.00 | Usingwhere |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row inset (0.08 sec)
方案2：子查詢
mysql> SELECT * FROM edu_test WHERE id >= (SELECT id FROM edu_test ORDERBY id LIMIT 499000, 1) LIMIT 10;
10 rows inset (0.16 sec)
mysql> explain SELECT * FROM edu_test WHERE id >= (SELECT id FROM edu_test ORDERBY id LIMIT 499000, 1) LIMIT 10;
+----+-------------+----------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| 1 | PRIMARY | edu_test | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 1000 | 100.00 | Usingwhere |
| 2 | SUBQUERY | edu_test | NULL | index | NULL | PRIMARY | 4 | NULL | 499001 | 100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
2 rows inset (0.14 sec)
方案3：join查詢
mysql> select * from edu_test s, (select id from edu_test order by id limit 499000, 10) t where s.id = t.id;
10 rows in set (0.16 sec)
mysql> explain select * from edu_test s, (select id from edu_test order by id limit 499000, 10) t where s.id = t.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 | 499010 |100.00| NULL |
| 1 | PRIMARY | s | NULL | eq_ref | PRIMARY | PRIMARY |4| t.id |1| 100.00 | NULL |
|2| DERIVED | edu_test | NULL | index | NULL | PRIMARY | 4 | NULL | 499010 |100.00| Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+
3 rows in set (0.10 sec)
實際業務場景
場景：
在我們設計資料庫id的時候，可能采用字串格式、有順序的id，帶有一定的業務邏輯這樣的分布式id。
解決：
如果我們分頁想要最佳化時候，根據減少掃描思路，可以透過where id like '10289%' 方式，先縮小範圍再分頁。
啟示：
在設計資料庫id主鍵的時候，盡量
保持主鍵唯一且有序
，
最好能解決熱點業務問題
（如果依賴很多的非主鍵值，那麽我們可能還需要回表操作），而且主鍵本身就是一種唯一索引，這種唯一有序特性可以便於幫助我們後期最佳化，減少掃描記錄範圍。
