由于爬蟲抓取的數據不斷增多，這兩天在不斷對數據庫以及查詢語句進行優化，其中一個表結構如下：
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 是一個范圍條件（但是服務器可以把其余列用于其他目的）。如果范圍查詢列值的數量有限，那么可以通過使用多個等于條件來代替范圍條件。
