由于爬蟲抓取的數(shù)據(jù)不斷增多，這兩天在不斷對(duì)數(shù)據(jù)庫(kù)以及查詢語(yǔ)句進(jìn)行優(yōu)化，其中一個(gè)表結(jié)構(gòu)如下：

CREATE TABLE `newspaper_article` (
  `id` varchar(50) NOT NULL COMMENT '編號(hào)',
  `title` varchar(190) NOT NULL COMMENT '標(biāo)題',
  `author` varchar(255) DEFAULT NULL COMMENT '作者',
  `date` date NULL DEFAULT NULL COMMENT '發(fā)表時(shí)間',
  `content` longtext COMMENT '正文',
  `status` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_status_date` (`status`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章表';

根據(jù)業(yè)務(wù)需要，添加了 idx_status_date 索引，在執(zhí)行下面這個(gè) SQL 時(shí)特別耗時(shí)：

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

根據(jù)觀察，每天新增的數(shù)據(jù)大概在2500條以內(nèi)，本以為這里指定了具體某天的日期 '2016-01-07' ，實(shí)際需要掃描的數(shù)據(jù)量應(yīng)該在2500條以內(nèi)才對(duì)，但實(shí)際并非如此：

實(shí)際共掃描了185589條數(shù)據(jù)，遠(yuǎn)遠(yuǎn)高于預(yù)估的2500條，且實(shí)際執(zhí)行時(shí)間都將近3秒鐘：

這是為什么呢？

解決方案

將 idx_status_date (status, date) 改為 idx_status (status) 后，查看 MySQL 執(zhí)行計(jì)劃：

可以看到將多列索引改為單列索引后，執(zhí)行計(jì)劃要掃描的數(shù)據(jù)總量沒有任何變化。結(jié)合多列索引遵循最左前綴原則，推測(cè)上面的查詢語(yǔ)句只使用了 idx_status_date 最左邊的 status 的索引。

翻了下《高性能MySQL》找到了下面這段話，證實(shí)了我的想法：

如果查詢中有某個(gè)列的范圍查詢，則其右邊所有列都無法使用索引優(yōu)化查找。例如有查詢 WHERE last_name = 'Smith' AND first_name LIKE 'J%' AND dob = '1976-12-23' ，這個(gè)查詢只能使用索引的前兩列，因?yàn)檫@里 LIKE 是一個(gè)范圍條件（但是服務(wù)器可以把其余列用于其他目的）。如果范圍查詢列值的數(shù)量有限，那么可以通過使用多個(gè)等于條件來代替范圍條件。

因此，這里解決思路有兩種：

• 可以通過使用多個(gè)等于條件來代替范圍條件

• 修改 idx_status_date (status, date) 為索引 idx_date_status (date, status) ，并新建一個(gè) idx_status 索引，即可達(dá)到同樣的效果。

優(yōu)化后的執(zhí)行計(jì)劃：

實(shí)際執(zhí)行結(jié)果：

總結(jié)

當(dāng)人們談?wù)撍饕�的時(shí)候，如果沒有特別指明類型，那么多半說的是 B-Tree 索引，它使用 B-Tree 數(shù)據(jù)結(jié)構(gòu)來存儲(chǔ)數(shù)據(jù)。我們使用術(shù)語(yǔ)“B-Tree”，是因?yàn)?MySQL 在 CREATE TABLE 和其他語(yǔ)句中也使用該關(guān)鍵字。不過，底層的存儲(chǔ)引擎也可能使用不同的存儲(chǔ)結(jié)構(gòu)。InnoDB使用的是B+Tree。
假如有如下數(shù)據(jù)表：

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 索引對(duì)如下類型的查詢有效

• 全值匹配
  全值匹配指的是和索引中的所有列進(jìn)行匹配，例如上表的索引可用于查找姓名為 Cuba Allen 、出生于 1960-01-01 的人。

• 匹配最左前綴
  上表中的索引可用于查找所有姓為 Allen 的人，即只使用索引的第一列。

• 匹配列前綴
  只匹配某一列的值的開頭部分。例如上表的索引可用于查找所有以 J 開頭的姓的人。這里也只使用了索引的第一列。

• 匹配范圍值
  例如上表中的索引可用于查找姓在 Allen 和 Barrymore 之間的人。這里也只使用了索引的第一列。

• 精確匹配某一列并范圍匹配另外一列
  上表的索引也可用于查找所有姓為 Allen ，并且名字是字母 K 開頭（比如 Kim 、 Karl 等）的人。即第一列 last_name 全匹配，第二列 first_name 范圍匹配。

• 只訪問索引的查詢
  B-Tree 通�？梢灾С帧爸辉L問索引的查詢”，即查詢只需要訪問索引，而無須訪問數(shù)據(jù)行。

B-Tree 索引的一些限制

• 如果不是按照索引的最左列開始查找，則無法使用索引。例如上表的索引無法用于查找名字為 Bill 的人，也無法查找某個(gè)特定生日的人，因?yàn)檫@兩列都不是最左數(shù)據(jù)列。類似地，也無法查找姓氏以某個(gè)字母結(jié)尾的人。

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

• 如果查詢中有某個(gè)列的范圍查詢，則其右邊所有列都無法使用索引優(yōu)化查找。例如有查詢 WHERE last_name = 'Smith' AND first_name LIKE 'J%' AND dob = '1976-12-23' ，這個(gè)查詢只能使用索引的前兩列，因?yàn)檫@里 LIKE 是一個(gè)范圍條件（但是服務(wù)器可以把其余列用于其他目的）。如果范圍查詢列值的數(shù)量有限，那么可以通過使用多個(gè)等于條件來代替范圍條件。

以上就是分享一個(gè)MySQL 多列索引優(yōu)化實(shí)例代碼的詳細(xì)內(nèi)容，更多請(qǐng)關(guān)注php中文網(wǎng)其它相關(guān)文章！