mysql中index與Multiple-Column Indexes區別與聯絡

標籤：table   pre   code   mysq   --   xxx   需求   exp   and   

索引對提升SELECT/UPDATE語句查詢速度有著立竿見影的效果，有索引和無索引，查詢速度往往差幾個數量級。

本次討論一下index（每列作為一個索引，單列索引）和Multiple-Column Indexes（多列作為一個索引，最多16列，複合索引）使用情境。

常見建立或添加索引的方式：

方式一，建表時建立

CREATE TABLE test (    id         INT NOT NULL,    last_name  CHAR(30) NOT NULL,    first_name CHAR(30) NOT NULL,    PRIMARY KEY (id),--  單列索引    INDEX name (last_name)-- 複合索引-- INDEX name (last_name,first_name));

方式二，給即存表添加索引：

/*單列索引*/ALTER TABLE test ADD INDEX name (last_name);/*複合索引*/ALTER TABLE test ADD INDEX name (last_name,first_name);

這裡要注意索引的順序很重要，關係到能否命中索引，後面詳細討論。

mysql官方文檔是這麼描述複合索引的：mysql能使用複合索引來查詢索引中的所有列，或者查詢複合索引中的第一列、前兩列、前三列等等。

 如果指定列順序正確相對於索引中的定義順序，那麼一個簡單的複合作引可以加快一個表中幾種類型的查詢。官網地址

以上這段話包可解讀如下：

• 複合索引的優點：一個複合索引解決多種查詢的效率問題
• 複合索引的命中規則：前半句話描述的是命中複合索引的最左首碼(leftmost prefix)規則，也就是上面提到的索引順序問題。這個規則是對於WHERE語句中的索引列的順的。

簡單的說加入複合索引的順序是(col1, col2, col3)那麼WHERE語句中的(col1)、(ol1, col2)、(col1, col2, col3)都能命中索引，但是(col2)、(col3)、(ol2, col3)是不能命中索引規則的，因為不滿足最左首碼規則。

舉個栗子，假設建立複合索引如下：

CREATE TABLE test (    id         INT NOT NULL,    last_name  CHAR(30) NOT NULL,    first_name CHAR(30) NOT NULL,    PRIMARY KEY (id),    INDEX name (last_name,first_name));

根據最左首碼原則，以下查詢語句能命中索引：

SELECT * FROM test WHERE last_name=‘Widenius‘; SELECT * FROM test  WHERE last_name=‘Widenius‘ AND first_name=‘Michael‘; SELECT * FROM test  WHERE last_name=‘Widenius‘  AND (first_name=‘Michael‘ OR first_name=‘Monty‘); SELECT * FROM test  WHERE last_name=‘Widenius‘  AND first_name >=‘M‘ AND first_name < ‘N‘;

以下查詢語句不能命中複合索引：

SELECT * FROM test WHERE first_name=‘Michael‘; SELECT * FROM test  WHERE last_name=‘Widenius‘ OR first_name=‘Michael‘;

如果不清楚是否命中索引，EXPLAIN是個好工具，要充分利用起來。比如下語句,查看結果中的key就能知道是否命中，為空白就是木有命中：

EXPLAIN SELECT * FROM test WHERE first_name=‘Michael‘;

使用情境：

在做報表集計一般會使用ETL工具抽取業務資料插入到事實表中，就比如使用kettle update/insert，其內部原理都是先查詢不存在就如插入資料，存在資料就更新。

因此如果資料很多的話就會有很多的查詢動作，並且是多條件查詢。這種情況複合索引效率是最高的，但是往往實際的業務中還會以單列作為查詢的場合這是就需要普通索引來滿足這個需求。

這種場合就會出現同一列同時出現在了複合索引和單列索引中，這也是很正常的。給一個實際的例子如下：

CREATE TABLE `st_stock` (  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘自增ID‘,  `id_company` int(11) NOT NULL COMMENT ‘組織維度‘,  `id_date` int(11) NOT NULL COMMENT ‘日期維度‘,  `id_part` int(11) NOT NULL COMMENT ‘材料維度‘,  `number` decimal(18,2) DEFAULT NULL COMMENT ‘出庫數量‘,  `subtotal` decimal(21,6) DEFAULT NULL COMMENT ‘出庫金額‘,  PRIMARY KEY (`id`),  KEY `IDX_ORG` (`id_company`),  KEY `IDX_PART` (`id_part`),  KEY `IDX_DATE_ORG_PART` (`id_date`,`id_company`,`id_part`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT=‘xxxxxx‘;

實際療效：

 

#2498和#2499資料量大於等於#2496、#2497，但集計時間將近縮短了一半，效果還是很明顯。

最佳化效果沒有達到一個數量或更進階是因為最佳化的只是JOB中一個轉換，只是最佳化的眾多因素之一。

即便如此也達到了預期的效果。

 

最後還要注意一點，索引的維護是有代價的，並不是越多越好。其他參考資料如下：

https://www.percona.com/blog/2009/09/19/multi-column-indexes-vs-index-merge/

https://www.percona.com/blog/2014/01/03/multiple-column-index-vs-multiple-indexes-with-mysql-56/

https://dev.mysql.com/doc/refman/5.7/en/drop-index.html

https://dev.mysql.com/doc/refman/5.6/en/multiple-column-indexes.html

https://dev.mysql.com/doc/refman/5.7/en/create-index.html

mysql中index與Multiple-Column Indexes區別與聯絡