技術分享 | 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優化，很多都可以用基礎的知識解決，隻有一小部分，需要一些技巧，或者更深層次的知識，但這些所謂的基礎知識，"了解"和"了解"，存在着差別，單從知識來講，可能都知道是怎麼個原理，但當碰到實際的場景，能不能将知識運用到實踐中，就取決于對知識的了解程度了，這個不僅僅指資料庫領域，其它任何領域，都是相通的，學習知識，重要的是能應用到實踐中，能做到舉一反三，這個的前提就是對知識是不是真正了解了，而不是停留在表面上。

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