準(zhǔn)備工作
# 總記錄數(shù)為500000
mysql> select count(id) from edu_test;
+-----------+
| count(id) |
+-----------+
| 500000 |
+-----------+
1 row in set (0.05 sec)
分析過程
從0開始查詢10條:
mysql> select * from edu_test limit 0, 10;
10 rows in set (0.05 sec)
從20萬開始查詢10條:
mysql> select * from edu_test limit 200000, 10;
10 rows in set (0.14 sec)
從50萬開始查詢10條:
mysql> select * from edu_test limit 499000, 10;
10 rows in set (0.21 sec)
- 現(xiàn)象:隨著分頁越深入盈简,查詢的時(shí)間也越來越長救氯。
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分頁做了一個(gè)全表掃描驾诈,掃描后將從200000開始往后取10條記錄返回凤瘦。
優(yōu)化
思路:
- 快速定位到要訪問的數(shù)據(jù)行,縮小掃描范圍拂封。
方案1 - 延遲查詢(先定位再查詢).
方案2茬射、方案3
方案1:通過有序唯一索引縮小掃描范圍
前提必須要id有序,要不然結(jié)果會漏掉一部分?jǐn)?shù)據(jù)的烘苹。
mysql> select * from edu_test where id > 499000 order by id asc limit 10;
10 rows in set (0.14 sec)
mysql> explain select * from edu_test where id > 499000 order by 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 | Using where |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set (0.16 sec)
# 再縮小掃描范圍
mysql> select * from edu_test where id between 499000 and 499020 order by id asc limit 10;
10 rows in set (0.09 sec)
mysql> explain select * from edu_test where id between 499000 and 499020 order by 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 | Using where |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set (0.08 sec)
方案2:子查詢
mysql> SELECT * FROM edu_test WHERE id >= (SELECT id FROM edu_test ORDER BY id LIMIT 499000, 1) LIMIT 10;
10 rows in set (0.16 sec)
mysql> explain SELECT * FROM edu_test WHERE id >= (SELECT id FROM edu_test ORDER BY 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 | Using where |
| 2 | SUBQUERY | edu_test | NULL | index | NULL | PRIMARY | 4 | NULL | 499001 | 100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
2 rows in set (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)
實(shí)際業(yè)務(wù)場景
- 場景:
在我們設(shè)計(jì)數(shù)據(jù)庫id的時(shí)候躲株,可能采用字符串格式、有順序的id镣衡,帶有一定的業(yè)務(wù)邏輯這樣的分布式id霜定。 - 解決:
如果我們分頁想要優(yōu)化時(shí)候,根據(jù)減少掃描思路廊鸥,可以通過where id like '10289%' 方式望浩,先縮小范圍再分頁。 - 啟示:
在設(shè)計(jì)數(shù)據(jù)庫id主鍵的時(shí)候惰说,盡量保持主鍵唯一且有序磨德,最好能解決熱點(diǎn)業(yè)務(wù)問題(如果依賴很多的非主鍵值,那么我們可能還需要回表操作),而且主鍵本身就是一種唯一索引典挑,這種唯一有序特性可以便于幫助我們后期優(yōu)化酥宴,減少掃描記錄范圍。