MySQL中如何使用多列索引，相信很多沒有經(jīng)驗(yàn)的人對(duì)此束手無策，為此本文總結(jié)了問題出現(xiàn)的原因和解決方法，通過這篇文章希望你能解決這個(gè)問題。

為灌陽(yáng)等地區(qū)用戶提供了全套網(wǎng)頁(yè)設(shè)計(jì)制作服務(wù)，及灌陽(yáng)網(wǎng)站建設(shè)行業(yè)解決方案。主營(yíng)業(yè)務(wù)為網(wǎng)站建設(shè)、成都網(wǎng)站制作、灌陽(yáng)網(wǎng)站設(shè)計(jì)，以傳統(tǒng)方式定制建設(shè)網(wǎng)站，并提供域名空間備案等一條龍服務(wù)，秉承以專業(yè)、用心的態(tài)度為用戶提供真誠(chéng)的服務(wù)。我們深信只要達(dá)到每一位用戶的要求，就會(huì)得到認(rèn)可，從而選擇與我們長(zhǎng)期合作。這樣，我們也可以走得更遠(yuǎn)！

多列索引

我們經(jīng)常聽到一些人說"把WHERE條件里的列都加上索引"，其實(shí)這個(gè)建議非常錯(cuò)誤。在多個(gè)列上建立單獨(dú)的索引大部分情況下并不能提高M(jìn)ySQL的查詢性能。MySQL在5.0之后引入了一種叫“索引合并”(index  merge)的策略，一定程度上可以使用表上的多個(gè)單列索引來定位指定的行。但是當(dāng)服務(wù)器對(duì)多個(gè)索引做聯(lián)合操作時(shí)，通常需要耗費(fèi)大量CPU和內(nèi)存資源在算法的緩存、排序和合并操作上，特別是當(dāng)其中有些索引的選擇性不高，需要合并掃描大量的數(shù)據(jù)的時(shí)候。

這個(gè)時(shí)候，我們需要一個(gè)多列索引。

案例

創(chuàng)建一個(gè)測(cè)試數(shù)據(jù)庫(kù)和數(shù)據(jù)表：

CREATE DATABASE IF NOT EXISTS db_test default charset utf8 COLLATE utf8_general_ci;  use db_test; CREATE TABLE payment (       id         INT UNSIGNED NOT NULL AUTO_INCREMENT,       staff_id  INT UNSIGNED NOT NULL,       customer_id INT UNSIGNED NOT NULL,       PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入1000w行隨機(jī)數(shù)據(jù)(利用存儲(chǔ)過程):

DROP PROCEDURE IF EXISTS add_payment;   DELIMITER //     create PROCEDURE add_payment(in num INT)     BEGIN         DECLARE rowid INT DEFAULT 0;         SET @exesql = 'INSERT INTO payment(staff_id, customer_id) values (?, ?)';         WHILE rowid < num DO             SET @staff_id = (1 + FLOOR(5000*RAND()) );              SET @customer_id = (1 + FLOOR(500000*RAND()));             SET rowid = rowid + 1;             prepare stmt FROM @exesql;             EXECUTE stmt USING @staff_id, @customer_id;                     END WHILE;     END // DELIMITER ;

或者你可以直接下載使用我的測(cè)試數(shù)據(jù)(也是利用上面的存儲(chǔ)過程,但是我之后調(diào)整了數(shù)據(jù)):

測(cè)試數(shù)據(jù)

添加兩個(gè)單列索引(執(zhí)行過程要花點(diǎn)時(shí)間,建議分開一句一句執(zhí)行):

ALTER TABLE `payment` ADD INDEX idx_customer_id(`customer_id`);  ALTER TABLE `payment` ADD INDEX idx_staff_id(`staff_id`);

查詢一條數(shù)據(jù)利用到兩個(gè)列的索引:

select count(*) from payment where staff_id = 2205 AND customer_id = 93112;

查看執(zhí)行計(jì)劃:

mysql> explain select count(*)  from payment  where staff_id =  2205  AND customer_id =  93112; +----+-------------+---------+-------------+------------------------------+------------------------------+---------+------+-------+-------------------------------------------------------------------------+ | id | select_type | table   | type        | possible_keys                | key                          | key_len | ref  | rows  | Extra                                                                   | +----+-------------+---------+-------------+------------------------------+------------------------------+---------+------+-------+-------------------------------------------------------------------------+ |  1 | SIMPLE      | payment | index_merge | idx_customer_id,idx_staff_id | idx_staff_id,idx_customer_id | 4,4     | NULL | 11711 | Using intersect(idx_staff_id,idx_customer_id); Using where; Using index | +----+-------------+---------+-------------+------------------------------+------------------------------+---------+------+-------+-------------------------------------------------------------------------+ 1 row in set (0.00 sec)

可以看到type是index_merge,Extra中提示Using  intersect(idx_staff_id,idx_customer_id);

這便是索引合并,利用兩個(gè)索引,然后合并兩個(gè)結(jié)果(取交集或者并集或者兩者都有)

查詢結(jié)果:

mysql> select count(*)  from payment  where staff_id =  2205  AND customer_id =  93112 ; +----------+ | count(*) | +----------+ |   178770 | +----------+ 1 row in set (0.12 sec)

然后刪除以上索引,添加多列索引:

ALTER TABLE payment DROP INDEX idx_customer_id;  ALTER TABLE payment DROP INDEX idx_staff_id;  ALTER TABLE `payment` ADD INDEX idx_customer_id_staff_id(`customer_id`, `staff_id`);

注意,多列索引很關(guān)注索引列的順序(因?yàn)閏ustomer_id的選擇性更大,所以把它放前面)

查詢:

mysql> select count(*)  from payment  where staff_id =  2205  AND customer_id =  93112; +----------+ | count(*) | +----------+ |   178770 | +----------+ 1 row in set (0.05 sec)

發(fā)現(xiàn)多列索引加快的查詢(這里數(shù)據(jù)量還是較小,更大的時(shí)候比較更明顯)

注意

多列索引的列順序至關(guān)重要，如何選擇索引的列順序有一個(gè)經(jīng)驗(yàn)法則:將選擇性***的列放到索引最前列(但是不是絕對(duì)的)。經(jīng)驗(yàn)法則考慮全局的基數(shù)和選擇性，而不是某個(gè)具體的查詢：

mysql> select count(DISTINCT staff_id) / count(*) AS staff_id_selectivity, count(DISTINCT customer_id) / count(*) AS customer_id_selectivity, count(*) from payment\G; *************************** 1. row ***************************    staff_id_selectivity: 0.0005 customer_id_selectivity: 0.0500                count(*): 10000000 1 row in set (6.29 sec)

customer_id的選擇性更高，所以將它作為索引列的***位。

多列索引只能匹配最左前綴，也就是說：

select * from payment  where staff_id =  2205  AND customer_id =  93112 ; select count(*)  from payment  where  customer_id =  93112 ;

可以利用索引，但是

select * from payment where staff_id = 2205 ;

看完上述內(nèi)容，你們掌握MySQL中如何使用多列索引的方法了嗎？如果還想學(xué)到更多技能或想了解更多相關(guān)內(nèi)容，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，感謝各位的閱讀！