分享一個MySQL 多列索引優化實例程式碼

由於爬蟲抓取的資料不斷增多，這兩天在不斷對資料庫以及查詢語句進行優化，其中一個表結構如下：

CREATE TABLE `newspaper_article` (
  `id` varchar(50) NOT NULL COMMENT '编号',
  `title` varchar(190) NOT NULL COMMENT '标题',
  `author` varchar(255) DEFAULT NULL COMMENT '作者',
  `date` date NULL DEFAULT NULL COMMENT '发表时间',
  `content` longtext COMMENT '正文',
  `status` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_status_date` (`status`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章表';

根據業務需要，增加了idx_status_date 索引，在執行下面這個SQL 時特別耗時：

SELECT id, title, status, date FROM article WHERE status > -2 AND date = '2016-01-07';

根據觀察，每天新增的資料大概在2500條以內，本以為這裡指定了具體某天的日期'2016-01-07' ，實際需要掃描的資料量應該在2500條以內才對，但實際上並非如此：

#實際共掃描了185589條數據，遠高於預估的2500條，實際執行時間都將近3秒鐘：

這是為什麼呢？

解決方案

將idx_status_date (status, date) 改為idx_status (status) 後，檢視MySQL 執行計畫：

可以看到將多列索引改為單列索引後，執行計劃要掃描的資料總量沒有任何變化。結合多列索引遵循最左前綴原則，推測上面的查詢語句只使用了 idx_status_date 最左邊的 status 的索引。

翻了下《高效能MySQL》找到了下面這段話，證實了我的想法：

如果查詢中有某個列的範圍查詢，則其右邊所有欄位都無法使用索引優化查找。例如有查詢WHERE last_name = 'Smith' AND first_name LIKE 'J%' AND dob = '1976-12-23' ，這個查詢只能使用索引的前兩列，因為這裡LIKE 是一個範圍條件（但是伺服器可以把其餘欄位用於其他目的）。如果範圍查詢列值的數量有限，那麼可以透過使用多個等於條件來取代範圍條件。

因此，這裡解決想法有兩種：

• 可以透過使用多個等於條件來取代範圍條件

• 修改idx_status_date (status, date) 為索引idx_date_status (date, status) ，並新建一個idx_status 索引，即可達到相同的效果。

優化後的執行計劃：

實際執行結果：

#總結

當人們談論索引的時候，如果沒有特別指明類型，那麼多半說的是B-Tree 索引，它使用B-Tree 資料結構來儲存資料。我們使用術語“B-Tree”，是因為 MySQL 在 CREATE TABLE 和其他語句中也使用該關鍵字。不過，底層的儲存引擎也可能使用不同的儲存結構。 InnoDB使用的是B+Tree。
假如有以下資料表：

CREATE TABLE People (
  last_name  varchar(50)    not null,
  first_name varchar(50)    not null,
  dob        date           not null,
  gender     enum('m', 'f') not null,
  key(last_name, first_name, dob)
);

B-Tree 索引對以下資料表的查詢有效

• ##全值符合

   全值符合指的是和索引中的所有欄位進行匹配，例如上表的索引可用於查找姓名為Cuba Allen 、出生於1960-01-01 的人。
  

• 符合最左字首

   上表中的索引可用來找出所有姓為 Allen 的人，也就是只使用索引的第一列。
  

• 符合列前綴

   只符合某一列的值的開頭部分。例如上表的索引可用來尋找所有以 J 開頭的姓的人。這裡也只使用了索引的第一列。
  

• 符合範圍值

   例如上表中的索引可用來尋找姓在 Allen 和 Barrymore 之間的人。這裡也只使用了索引的第一列。
  

• 精確地符合某一列並且範圍符合另一列

   上表的索引也可用於尋找所有姓為Allen ，且名字是字母K 開頭（如Kim 、 Karl 等）的人。即第一列 last_name 全匹配，第二列 first_name 範圍匹配。
  

• 只存取索引的查詢

   B-Tree 通常可以支援“只存取索引的查詢”，即查詢只需要存取索引，而無須存取資料行。
  

B-Tree 索引的一些限制

• #如果不是按照索引的最左列開始查找，則無法使用索引。例如上表的索引無法用來尋找名字為 Bill 的人，也無法尋找某個特定生日的人，因為這兩個欄位都不是最左邊資料列。類似地，也無法找出姓氏以某個字母結尾的人。

• 不能跳過索引中列。也就是說，上表的索引無法用來尋找姓氏為 Smith 並且在某個特定日期出生的人。如果不指定名稱（first_name），則 MySQL 只能使用索引的第一列。

• 如果查詢中有某個欄位的範圍查詢，則其右邊所有欄位都無法使用索引最佳化尋找。例如有查詢WHERE last_name = 'Smith' AND first_name LIKE 'J%' AND dob = '1976-12-23' ，這個查詢只能使用索引的前兩列，因為這裡LIKE 是一個範圍條件（但是伺服器可以把其餘欄位用於其他目的）。如果範圍查詢列值的數量有限，那麼可以透過使用多個等於條件來取代範圍條件。

以上是分享一個MySQL 多列索引優化實例程式碼的詳細內容。更多資訊請關注PHP中文網其他相關文章！