摘要:結合多列索引遵循最左前綴原則,推測上面的查詢語句只使用了最左邊的的索引。如果不指定名,則只能使用索引的第一列。如果查詢中有某個列的范圍查詢,則其右邊所有列都無法使用索引優化查找。
問題背景MySQL 5.6.30
由于爬蟲抓取的數據不斷增多,這兩天在不斷對數據庫以及查詢語句進行優化,其中一個表結構如下:
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 通常可以支持“只訪問索引的查詢”,即查詢只需要訪問索引,而無須訪問數據行。
如果不是按照索引的最左列開始查找,則無法使用索引。例如上表的索引無法用于查找名字為 Bill 的人,也無法查找某個特定生日的人,因為這兩列都不是最左數據列。類似地,也無法查找姓氏以某個字母結尾的人。
不能跳過索引中列。也就是說,上表的索引無法用于查找姓氏為 Smith 并且在某個特定日期出生的人。如果不指定名(first_name),則 MySQL 只能使用索引的第一列。
如果查詢中有某個列的范圍查詢,則其右邊所有列都無法使用索引優化查找。例如有查詢 WHERE last_name = "Smith" AND first_name LIKE "J%" AND dob = "1976-12-23" ,這個查詢只能使用索引的前兩列,因為這里 LIKE 是一個范圍條件(但是服務器可以把其余列用于其他目的)。如果范圍查詢列值的數量有限,那么可以通過使用多個等于條件來代替范圍條件。
原文鏈接:http://xueliang.org/article/detail/20170326235631083
文章版權歸作者所有,未經允許請勿轉載,若此文章存在違規行為,您可以聯系管理員刪除。
轉載請注明本文地址:http://specialneedsforspecialkids.com/yun/17598.html
閱讀 1428·2021-11-22 15:24
閱讀 2519·2021-10-11 11:06
閱讀 2323·2021-10-09 09:45
閱讀 2525·2021-09-09 09:33
閱讀 634·2019-08-30 15:53
閱讀 1438·2019-08-30 12:48
閱讀 656·2019-08-29 13:47
閱讀 499·2019-08-26 18:27