技術分享 | MySQL 覆蓋索引最佳化案例一則

作者：劉晨
網名 bisal ，具有十年以上的應用運維工作經驗，目前主要從事資料庫應用研發能力提升和技術管理相關的工作，Oracle ACE（Alumni），騰訊雲TVP，擁有 Oracle OCM & OCP 、EXIN DevOps Master 、SCJP 等國際認證，國內首批 Oracle YEP 成員，OCMU 成員，《DevOps 最佳實踐》中文譯者之一，CSDN & ITPub 專家博主，公眾號"bisal的個人雜貨鋪"，長期堅持分享技術文章，多次線上上和線下分享技術主題。
本文來源：原創投稿
*愛可生開源社群出品，原創內容未經授權不得隨意使用，轉載請聯絡小編並註明來源。

最近有個MySQL的SQL效能問題，原理可能很基礎，但考察的就是能不能將"顯而易見"的知識應用到實踐中。

經過脫敏的SQL如下所示，對test表中的c1列進行聚類，再透過SUM ... CASE WHEN...等函式進行統計，test表資料量500萬，當前檢索用時55秒，需求是將執行降到秒級，

SELECT c1,·
       SUM(CASE WHEN c2=0 THEN 1 ELSE 0 END) as folders,
       SUM(CASE WHEN c2=1 THEN 1 ELSE 0 END) as files,
       SUM(c3)
FROM  test
GROUP BY c1;

為了更好地說明，建立一張測試表，主鍵欄位是id，除了c1、c2、c3欄位外，還有其他欄位，有很多索引，但和c1、c2、c3相關的，只是idx_test_01，c1作為前導列的複合索引，且c2和c3不在索引中，

CREATE TABLE test (
  id bigint(20) not null,
  c1 varchar(64) collate utf8_bin not null,
  c2 tinyint(4) not null,
  c3 bigint(20) default null,
  ...
  primary key(id),
  key idx_test_01(c1, ...)
  key ...
  ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

顯而易見，如上SQL執行時，能用到的索引就只有idx_test_01，Extra是NULL，

+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | index | idx_test_01   | idx_test_01 | 206     | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

我們知道，MySQL的索引預設是聚簇索引（可以理解為Oracle的IOT索引組織表），針對當前僅有(c1, ...)這個複合索引，當執行檢索時，即便能使用這個複合索引，他都需要執行兩個操作，（1）訪問(c1, ...)複合索引；（2）從該複合索引中得到主鍵id，再進行回表，根據主鍵id，得到相應資料。這個過程中，最需要消耗的，就是磁碟IO的資源。不僅需要訪問(c1, ...)複合索引的資料，還需要回表，訪問資料行。

設計索引應該考慮到整個查詢，不單只是WHERE條件。索引是能高效找到資料的方式，但是如果使用索引可以直接得到列的資料，即索引的葉子節點中已經包含要查詢的資料，就無需回表，讀資料行了。如果一個索引包含（或者叫做覆蓋）所有要查詢的欄位的值，就可以稱之為"覆蓋索引"，但是要注意，只有B-tree索引可以用於覆蓋索引。

覆蓋索引能顯著提高檢索的效能，原因就是查詢只需要掃描索引而無需回表，

1.索引條目通常遠小於資料行大小，因此如果只需要掃描索引，就會極大地減少資料訪問量。資料訪問響應時間大部分花費在資料複製上，索引比資料更小，更容易全部放入記憶體中。

2.因為索引是按照列值的順序儲存的，所以範圍查詢會比隨機從磁碟讀取每一行資料消耗的IO少得多。

3.由於InnoDB的聚簇索引的特點，覆蓋索引對InnoDB表特別有用，因為InnoDB的二級索引在葉子節點中儲存了記錄的主鍵值，所以如果二級索引能夠覆蓋查詢，則可以避免對主鍵索引的二次查詢。

在索引中滿足查詢的成本一般比查詢記錄本身要小得多。

因此，針對這條SQL，建立包含了(c1, c2, c3)的複合索引，

create index idx_test_02(c1, c2, c3) on test;

此時執行SQL，Extra顯示Using index，說明用到了覆蓋索引的特性，


+----+-------------+-------+------------+-------+-------------------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys           | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+-------------------------+-------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | index | idx_test_01,idx_test_02 | idx_test_02 | 204     | NULL |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+-------------------------+-------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

從執行效率上，原來跑55秒的語句，現在只需要2秒。

根據2-8原則，可能我們平時碰到的SQL最佳化，很多都可以用基礎的知識解決，只有一小部分，需要一些技巧，或者更深層次的知識，但這些所謂的基礎知識，"瞭解"和"理解"，存在著區別，單從知識來講，可能都知道是怎麼個原理，但當碰到實際的場景，能不能將知識運用到實踐中，就取決於對知識的理解程度了，這個不僅僅指資料庫領域，其它任何領域，都是相通的，學習知識，重要的是能應用到實踐中，能做到舉一反三，這個的前提就是對知識是不是真正理解了，而不是停留在表面上。

因此，我們學習任何知識的時候，一定要強調理論和實踐的結合，多積累經驗，畢竟解決問題，才是我們大多數職場人學習的目標。

技術分享 | MySQL 覆蓋索引最佳化案例一則

相關文章