需求

做過開發的同學，對分頁肯定不會陌生，因為很多前臺頁面展示，為了更好的展示數據，就會用到分頁，所以如何寫一個高性能的分頁SQL語句，是每一個開發人員需要掌握的技能。

分頁SQL

這里給大家寫一個分頁SQL語句。

mysql> show create table sbtest1\G;*************************** 1. row *************************** Table: sbtest1Create 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=10000001 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;

DBA看一條SQL語句是否有性能問題，首先會看看SQL語句的執行計劃，這里我們也一起先看看執行計劃

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)

從執行計劃上可以看到，possible_keys有k_1,idx_sbtest1_k_pad 兩個，可是SQL語句真正執行的時候，并沒有使用到索引，從key為NULL就可以知道，不走索引，性能基本會有問題，怎么辦。

有的朋友會說，讓SQL走索引啊，可是MySQL數據庫的優化器，為什么不讓SQL走索引呢。原來優化器會在索引存在的情況下，通過符合RANGE范圍的條數和總數的比例來選擇是使用索引還是進行全表遍歷，當需要讀取的數據超過一個臨界值時，優化器會放棄從索引中讀取而改為進行全表掃描。

有一定技術儲備的朋友就會開始支招，強制走索引，那么來看看強制走索引的效果。

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)

看執行計劃，已經走了索引(k_1)，那真實的執行時間來一起看看。

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)

看到實際執行時間(1 min 36.66 sec)，是不是不敢相信，走索引了，怎么還會要1分36秒啊，簡直不能接受。我來用一副圖，來展示一下SQL語句執行過程，你就會明白，為什么執行時間長了。

從上圖，可以很明顯的看出，性能瓶頸在哪里了吧，是回表查詢操作耗時，因為要回表查詢大約500W次，所以怎么減少回表操作，就是優化的重點。

那可不可以只查詢要返回的10條記錄的ID，最后只做10次回表操作呢，答案，當然是可以的，優化之后的SQL如下所示。

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)

看執行計劃，效果非常好，而且用的是覆蓋索引，怎么看出是用了覆蓋索引，因為Extra列同時出現了Using where和Using index。

來看看實際執行時間

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)

效果也是非常棒的，310毫秒就查詢出了結果。

進階

在這里優化之后的SQL語句如下所示

select?a.*?from?sbtest1?a?inner?join?(select?id?from?sbtest1?where?k>=10?order?by?k?limit?1000000,10)?b?using?(id);

如果order by后面需要加上pad列進行排序呢，變成如下所示

select?a.*?from?sbtest1?a?inner?join?(select?id?from?sbtest1?where?k>=10?order?by?k,pad?limit?1000000,10)?b?using?(id);

上述分頁優化核心思想就是覆蓋索引，很顯然加pad列之后，就不能用覆蓋索引解決問題了，因為不滿足使用覆蓋索引的條件。

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)

這個時候要如何優化呢，需要在k和pad列上創建復合索引，就可以解決問題。

給大家留一個SQL，大家看看怎么優化。

select?a.id?from?sbtest1?a?where?k>=10?order?by?k?desc,pad?asc?limit?1000000,10;

最后介紹一下在GtiChat平臺開啟的一個新Chat，

Chat名稱：二進制包方式安裝 MySQL 8.0.21 和 Docker 方式搭建 MySQL 8.0.21 服務

本 Chat 你將會獲取以下知識：

1. 怎么從 MySQL 官網獲取最新的 MySQL 數據庫安裝包，以及獲取歷史歸檔版本的 MySQL 數據庫安裝包

2. 提供標準化的 MySQL 8.0.21 參數配置(稍作微調，即可上生產)，并講解核心參數

3. MySQL 8.0.21 數據多實例創建和多實例啟停維護

4. MySQL 8.0.21 安全配置

5. Docker 方式搭建 MySQL 8.0.21 服務

總結

以上是生活随笔為你收集整理的mysql sql优化_MySQL数据库SQL语句优化原理专题（三）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。