MySQL組合索引(多列索引)使用與優(yōu)化案例詳解
1、多列索引
我們經(jīng)常聽(tīng)到一些人說(shuō)"把WHERE條件里的列都加上索引"，其實(shí)這個(gè)建議非常錯(cuò)誤。
在多個(gè)列上建立單獨(dú)的索引大部分情況下并不能提高M(jìn)ySQL的查詢(xún)性能。MySQL 在5.0之后引入了一種叫“索引合并”（index merge）的策略，一定程度上可以使用表上的多個(gè)單列索引來(lái)定位指定的行。但是當(dāng)服務(wù)器對(duì)多個(gè)索引做聯(lián)合操作時(shí)，通常需要耗費(fèi)大量CPU和內(nèi)存資源在算法的緩存、排序和合并操作上，特別是當(dāng)其中有些索引的選擇性不高，需要合并掃描大量的數(shù)據(jù)的時(shí)候。這個(gè)時(shí)候，我們需要一個(gè)多列索引。
2、測(cè)試案例及過(guò)程
2.1 創(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;利用存儲(chǔ)過(guò)程插入1000w 行隨機(jī)數(shù)據(jù)(表引擎可以先設(shè)置為 MyISAM，然后改為 InnoDB)
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 ;2.2 添加兩個(gè)單列索引
(執(zhí)行過(guò)程要花點(diǎn)時(shí)間,建議分開(kāi)一句一句執(zhí)行):
ALTER TABLE `payment` ADD INDEX idx_customer_id(`customer_id`); ALTER TABLE `payment` ADD INDEX idx_staff_id(`staff_id`);
2.3 查詢(xún)一條數(shù)據(jù)利用到兩個(gè)列的索引
select count(*) from payment where staff_id = 2205 AND customer_id = 93112;
2.4 查看執(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é)果(取交集或者并集或者兩者都有)
查詢(xún)結(jié)果：
mysql> select count(*) from payment where staff_id = 2205 AND customer_id = 93112 ;
+----------+
| count(*) |
+----------+
| 178770 |
+----------+
1 row in set (0.12 sec)
2.5 然后刪除以上索引,添加多列索引
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)?customer_id 的選擇性更大,所以把它放前面）。
2.6 再次查詢(xún)
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)多列索引加快的查詢(xún)（這里數(shù)據(jù)量還是較小,更大的時(shí)候比較更明顯）。
3、多列索引的使用順序
3.1 怎么選擇建立組合索引時(shí)，列的順序
多列索引的列順序至關(guān)重要，如何選擇索引的列順序有一個(gè)經(jīng)驗(yàn)法則：將選擇性最高的列放到索引最前列（但是不是絕對(duì)的）。經(jīng)驗(yàn)法則考慮全局的基數(shù)和選擇性，而不是某個(gè)具體的查詢(xún)：
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 的選擇性更高，所以將它作為索引列的第一位。
3.2 組合索引的使用規(guī)則
索引可以理解成排好序的數(shù)據(jù)結(jié)構(gòu)。組合索引可以這樣理解，比如（a,b,c），abc 都是排好序的，在任意一段 a 的下面 b 都是排好序的，任何一段 b 下面 c都是排好序的；
生效的規(guī)則是：從前往后依次使用生效，如果中間某個(gè)索引沒(méi)有使用，那么斷點(diǎn)前面的索引部分起作用，斷點(diǎn)后面的索引沒(méi)有起作用；
比如：
where a=3 and b=45 and c=5 .... 這種三個(gè)索引順序使用中間沒(méi)有斷點(diǎn)，全部發(fā)揮作用； where a=3 and c=5... 這種情況下b就是斷點(diǎn)，a發(fā)揮了效果，c沒(méi)有效果 where b=3 and c=4... 這種情況下a就是斷點(diǎn)，在a后面的索引都沒(méi)有發(fā)揮作用，這種寫(xiě)法聯(lián)合索引沒(méi)有發(fā)揮任何效果； where b=45 and a=3 and c=5 .... 這個(gè)跟第一個(gè)一樣，全部發(fā)揮作用，abc只要用上了就行，跟寫(xiě)的順序無(wú)關(guān)
（a,b,c）多列索引使用的示例，說(shuō)明：（a,b,c）組合索引和(a,c,b）是不一樣的
(0) select * from mytable where a=3 and b=5 and c=4; abc三個(gè)索引都在where條件里面用到了，而且都發(fā)揮了作用 (1) select * from mytable where c=4 and b=6 and a=3; 這條語(yǔ)句列出來(lái)只想說(shuō)明 mysql沒(méi)有那么笨，where里面的條件順序在查詢(xún)之前會(huì)被mysql自動(dòng)優(yōu)化，效果跟上一句一樣 (2) select * from mytable where a=3 and c=7; a用到索引，b沒(méi)有用，所以c是沒(méi)有用到索引效果的 (3) select * from mytable where a=3 and b>7 and c=3; a用到了，b也用到了，c沒(méi)有用到，這個(gè)地方b是范圍值，也算斷點(diǎn)，只不過(guò)自身用到了索引 (4) select * from mytable where b=3 and c=4; 因?yàn)閍索引沒(méi)有使用，所以這里 bc都沒(méi)有用上索引效果 (5) select * from mytable where a>4 and b=7 and c=9; a用到了 b沒(méi)有使用，c沒(méi)有使用 (6) select * from mytable where a=3 order by b; a用到了索引，b在結(jié)果排序中也用到了索引的效果，前面說(shuō)了，a下面任意一段的b是排好序的 (7) select * from mytable where a=3 order by c; a用到了索引，但是這個(gè)地方c沒(méi)有發(fā)揮排序效果，因?yàn)橹虚g斷點(diǎn)了，使用 explain 可以看到 filesort (8) select * from mytable where b=3 order by a; b沒(méi)有用到索引，排序中a也沒(méi)有發(fā)揮索引效果
到此這篇關(guān)于MySQL組合索引(多列索引)使用與優(yōu)化的文章就介紹到這了,更多相關(guān)mysql組合索引使用內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
MySQL查詢(xún)優(yōu)化必備知識(shí)點(diǎn)總結(jié)
這篇文章主要給大家介紹了關(guān)于MySQL查詢(xún)優(yōu)化必備知識(shí)點(diǎn)的相關(guān)資料，文中介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2021-03-03
子查詢(xún)是將一個(gè)查詢(xún)語(yǔ)句嵌套在另一個(gè)查詢(xún)語(yǔ)句中,內(nèi)層查詢(xún)語(yǔ)句的查詢(xún)結(jié)果，可以為外層查詢(xún)語(yǔ)句提供查詢(xún)條件2013-05-05
使用MySQL獲取系統(tǒng)性能和狀態(tài)的代碼片段，由此需求的朋友可以參考下，希望對(duì)大家有所幫助2013-07-07
MySQL?DDL執(zhí)行方式Online?DDL詳解
這篇文章主要介紹了MySQL?DDL執(zhí)行方式Online?DDL詳解，文章圍繞主題展開(kāi)詳細(xì)的內(nèi)容介紹，具有一定的參考價(jià)值，感興趣的小伙伴可以參考一下2022-09-09
mysql數(shù)據(jù)庫(kù)視圖和執(zhí)行計(jì)劃實(shí)戰(zhàn)案例
這篇文章主要給大家介紹了關(guān)于mysql數(shù)據(jù)庫(kù)視圖和執(zhí)行計(jì)劃的相關(guān)資料,在使用MySQL過(guò)程中視圖和執(zhí)行計(jì)劃是一個(gè)很好的工具,文中通過(guò)圖文以及代碼介紹的非常詳細(xì),需要的朋友可以參考下2024-02-02
在MySQL中如果不為NOT NULL字段賦值（等同于賦NULL值）也就是說(shuō),MySQL中NOT NULL并不是一個(gè)約束條件了2012-03-03
MySQL修改安全策略時(shí)報(bào)錯(cuò):ERROR?1193?(HY000)的解決辦法
這篇文章主要給大家介紹了關(guān)于MySQL修改安全策略時(shí)報(bào)錯(cuò)：ERROR?1193?(HY000):?Unknown?system?variable?‘validate_password_policy‘的解決方法,文中通過(guò)圖文介紹的非常詳細(xì),需要的朋友可以參考下2023-02-02
這篇文章給大家簡(jiǎn)單介紹了MySQL臨時(shí)表的簡(jiǎn)單用法，對(duì)此有需要的朋友可以跟著學(xué)習(xí)操作下。2018-06-06
本文主要介紹了mysql8.0使用PXC實(shí)現(xiàn)高可用,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2025-02-02
簡(jiǎn)單講解sql語(yǔ)句中的group by的使用方法
掌握sql語(yǔ)句中g(shù)roup by的使用方法會(huì)對(duì)我們的工作效率有很大的提升，下面小編來(lái)和大家一起簡(jiǎn)單學(xué)習(xí)一下2019-05-05