需求
做過開發的同學，對分頁肯定不會陌生，因為很多前臺頁面展示，為了更好的展示數據，就會用到分頁，所以如何在MySQL數據庫中寫一個高性能的分頁SQL語句，是每一個開發人員需要掌握的技能。
分頁SQL優化原理
這裡給大家寫一個分頁SQL語句。
<code>mysql> show create table sbtest1\G;
*************************** 1. row ***************************
Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`),
KEY `idx_sbtest1_k_pad` (`k`,`pad`)
) ENGINE=InnoDB AUTO_INCREMENT=5000001 DEFAULT CHARSET=utf8 MAX_ROWS=1000000
1 row in set (0.00 sec)
這裡準備了一張500W記錄的測試表
mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 5000000 |
+----------+
1 row in set (36.23 sec)
測試分頁SQL語句
select a.* from sbtest1 a where k>=10 order by k limit 1000000,10;/<code>DBA看一條SQL語句是否有性能問題，首先會看看SQL語句的執行計劃，這裡我們也一起先看看執行計劃
<code>mysql> explain select a.* from sbtest1 a where k>=10 order by k limit 1000000,10; +----+-------------+-------+------------+------+-----------------------+------+---------+------+---------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+-----------------------+------+---------+------+---------+----------+-----------------------------+ | 1 | SIMPLE | a | NULL | ALL | k_1,idx_sbtest1_k_pad | NULL | NULL | NULL | 4804854 | 50.00 | Using where; Using filesort | +----+-------------+-------+------------+------+-----------------------+------+---------+------+---------+----------+-----------------------------+ 1 row in set, 1 warning (0.36 sec)/<code>
從執行計劃上可以看到，possible_keys有k_1,idx_sbtest1_k_pad 兩個，可是SQL語句真正執行的時候，並沒有使用到索引，從key為NULL就可以知道，不走索引，性能基本會有問題，怎麼辦。
有的朋友會說，讓SQL走索引啊，可是MySQL數據庫的優化器，為什麼不讓SQL走索引呢。原來優化器會在索引存在的情況下，通過符合RANGE範圍的條數和總數的比例來選擇是使用索引還是進行全表遍歷，當需要讀取的數據超過一個臨界值時，優化器會放棄從索引中讀取而改為進行全表掃描。
有一定技術儲備的朋友就會開始支招，強制走索引，那麼來看看強制走索引的效果。
<code>mysql> explain select a.* from sbtest1 a FORCE INDEX(k_1) where k>=10 order by k limit 1000000,10; +----+-------------+-------+------------+-------+---------------+------+---------+------+---------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+---------+----------+-----------------------+ | 1 | SIMPLE | a | NULL | range | k_1 | k_1 | 4 | NULL | 2402427 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+------+---------+------+---------+----------+-----------------------+ 1 row in set, 1 warning (0.35 sec)/<code>
看執行計劃，已經走了索引（k_1），那真實的執行時間來一起看看。
<code>mysql> select a.* from sbtest1 a FORCE INDEX(k_1) where k>=10 order by k limit 1000000,10; +---------+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | id | k | c | pad | +---------+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | 625795 | 2491354 | 97204650565-29444916083-97115589676-40957844519-94449833558-65804998462-60532777657-14143069112-49633654712-45111409020 | 62512103246-48607606334-30155359433-33452555729-53895828100 | | 699397 | 2491354 | 32339675223-04668542998-80231026645-48395677535-02765586559-55831506063-42378875097-54580937047-37599981559-17618207647 | 29107597440-02396388801-60803416516-20183514546-47217028215 | | 710717 | 2491354 | 90067622196-22944775363-10477154003-65718440755-24111522288-05149673185-81902474191-32337446442-26659698531-19766723590 | 86804024266-77223258212-31659403967-01772860244-99013209389 | | 743592 | 2491354 | 93848584940-19743659878-81447583142-56888444937-65254387827-69794611882-41783362491-17920606195-80837332876-61005171709 | 18905422789-64525703285-24594762685-66972415320-18032736842 | | 857338 | 2491354 | 65447608668-53055131627-49484988592-56750024449-62428854104-09419771096-35386440425-85674387129-76043542898-38871555063 | 00988589677-01502672421-94556913009-28600249646-25815466940 | | 865647 | 2491354 | 40627346200-31156486805-14979452789-43576795710-31471455633-50576965996-46658943363-33235956457-29071759844-01216446475 | 77768402076-66310138953-89244891269-40818622962-29528856272 | | 867735 | 2491354 | 60189133283-00412480472-85765718780-99930014238-27634346893-05196495869-38494141485-78725336333-97665769760-66018168229 | 51330855469-21834479110-70983834788-18568325279-18729638102 | | 911459 | 2491354 | 45484218970-22406621124-16181607460-87980700413-18848748487-38186811906-83866179899-48052521037-07022164880-32639610985 | 66781406596-73096831064-93546863231-94196105489-63929163328 | | 936676 | 2491354 | 94497954531-98116538211-42837637393-90318550621-31404186605-85978752098-54751837452-40263829756-21584452095-09911571729 | 57946842290-70811751667-96407003836-80311371855-21528783577 | | 1117598 | 2491354 | 01895171691-22358407775-28637603773-13518829168-90455880119-28672508798-65059848567-86474578422-79051749776-25498873626 | 19312889757-23713866145-22328278494-54535848675-44626652413 | +---------+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ 10 rows in set (1 min 36.66 sec)/<code>
看到實際執行時間（1 min 36.66 sec)，是不是不敢相信，走索引了，怎麼還會要1分36秒啊，簡直不能接受。我來用一副圖，來展示一下SQL語句執行過程，你就會明白，為什麼執行時間長了。
從上圖，可以很明顯的看出，性能瓶頸在哪裡了吧，是回表查詢操作耗時，因為要回表查詢大約500W次，所以怎麼減少回表操作，就是優化的重點。
那可不可以只查詢要返回的10條記錄的ID，最後只做10次回表操作呢，答案，當然是可以的，優化之後的SQL如下所示。
<code>mysql> explain select a.* from sbtest1 a inner join (select id from sbtest1 where k>=10 order by k limit 1000000,10) b using (id); +----+-------------+------------+------------+--------+-----------------------+---------+---------+------+---------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+--------+-----------------------+---------+---------+------+---------+----------+--------------------------+ | 1 | PRIMARY | | NULL | ALL | NULL | NULL | NULL | NULL | 1000010 | 100.00 | NULL | | 1 | PRIMARY | a | NULL | eq_ref | PRIMARY | PRIMARY | 4 | b.id | 1 | 100.00 | NULL | | 2 | DERIVED | sbtest1 | NULL | range | k_1,idx_sbtest1_k_pad | k_1 | 4 | NULL | 2402427 | 100.00 | Using where; Using index | +----+-------------+------------+------------+--------+-----------------------+---------+---------+------+---------+----------+--------------------------+ 3 rows in set, 1 warning (0.02 sec)/<code>
看執行計劃，效果非常好，而且用的是覆蓋索引，怎麼看出是用了覆蓋索引，因為Extra列同時出現了Using where和Using index。
來看看實際執行時間
<code>mysql> select a.* from sbtest1 a inner join (select id from sbtest1 where k>=10 order by k limit 1000000,10) b using (id); +---------+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | id | k | c | pad | +---------+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | 625795 | 2491354 | 97204650565-29444916083-97115589676-40957844519-94449833558-65804998462-60532777657-14143069112-49633654712-45111409020 | 62512103246-48607606334-30155359433-33452555729-53895828100 | | 699397 | 2491354 | 32339675223-04668542998-80231026645-48395677535-02765586559-55831506063-42378875097-54580937047-37599981559-17618207647 | 29107597440-02396388801-60803416516-20183514546-47217028215 | | 710717 | 2491354 | 90067622196-22944775363-10477154003-65718440755-24111522288-05149673185-81902474191-32337446442-26659698531-19766723590 | 86804024266-77223258212-31659403967-01772860244-99013209389 | | 743592 | 2491354 | 93848584940-19743659878-81447583142-56888444937-65254387827-69794611882-41783362491-17920606195-80837332876-61005171709 | 18905422789-64525703285-24594762685-66972415320-18032736842 | | 857338 | 2491354 | 65447608668-53055131627-49484988592-56750024449-62428854104-09419771096-35386440425-85674387129-76043542898-38871555063 | 00988589677-01502672421-94556913009-28600249646-25815466940 | | 865647 | 2491354 | 40627346200-31156486805-14979452789-43576795710-31471455633-50576965996-46658943363-33235956457-29071759844-01216446475 | 77768402076-66310138953-89244891269-40818622962-29528856272 | | 867735 | 2491354 | 60189133283-00412480472-85765718780-99930014238-27634346893-05196495869-38494141485-78725336333-97665769760-66018168229 | 51330855469-21834479110-70983834788-18568325279-18729638102 | | 911459 | 2491354 | 45484218970-22406621124-16181607460-87980700413-18848748487-38186811906-83866179899-48052521037-07022164880-32639610985 | 66781406596-73096831064-93546863231-94196105489-63929163328 | | 936676 | 2491354 | 94497954531-98116538211-42837637393-90318550621-31404186605-85978752098-54751837452-40263829756-21584452095-09911571729 | 57946842290-70811751667-96407003836-80311371855-21528783577 | | 1117598 | 2491354 | 01895171691-22358407775-28637603773-13518829168-90455880119-28672508798-65059848567-86474578422-79051749776-25498873626 | 19312889757-23713866145-22328278494-54535848675-44626652413 | +---------+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ 10 rows in set (0.31 sec)/<code>
效果也是非常棒的，310毫秒就查詢出了結果。
進階
在這裡優化之後的SQL語句如下所示
<code>select a.* from sbtest1 a inner join (select id from sbtest1 where k>=10 order by k limit 1000000,10) b using (id);/<code>
如果order by後面需要加上pad列進行排序呢，變成如下所示
<code>select a.* from sbtest1 a inner join (select id from sbtest1 where k>=10 order by k,pad limit 1000000,10) b using (id);/<code>
上述分頁優化核心思想就是覆蓋索引，很顯然加pad列之後，就不能用覆蓋索引解決問題了，因為不滿足使用覆蓋索引的條件。
<code>mysql> explain select id from sbtest1 where k>=10 order by k,pad limit 1000000,10; +----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+ | 1 | SIMPLE | sbtest1 | NULL | ALL | k_1 | NULL | NULL | NULL | 4804854 | 50.00 | Using where; Using filesort | +----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+ 1 row in set, 1 warning (0.01 sec)/<code>
這個時候要如何優化呢，需要在k和pad列上創建複合索引，就可以解決問題。
最後給大家留一個SQL，大家看看怎麼優化。
<code>select a.id from sbtest1 a where k>=10 order by k desc,pad asc limit 1000000,10;/<code>
SQL優化系列文章
關注
1.如果您喜歡這篇文章，請點贊+轉發。
2.如果您特別喜歡，請加關注。
分享到:
