MySQL組合索引(多列索引)使用與優化案例詳解

2022-07-04 14:03:17

1、多列索引

我們經常聽到一些人說"把WHERE條件裡的列都加上索引"，其實這個建議非常錯誤。

在多個列上建立單獨的索引大部分情況下並不能提高MySQL的查詢效能。MySQL 在5.0之後引入了一種叫“索引合併”（index merge）的策略，一定程度上可以使用表上的多個單列索引來定位指定的行。但是當伺服器對多個索引做聯合操作時，通常需要耗費大量CPU和記憶體資源在演演算法的快取、排序和合並操作上，特別是當其中有些索引的選擇性不高，需要合併掃描大量的資料的時候。這個時候，我們需要一個多列索引。

2、測試案例及過程

2.1 建立一個測試資料庫和資料表

 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 行亂資料(表引擎可以先設定為 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 新增兩個單列索引

(執行過程要花點時間,建議分開一句一句執行):

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

2.3 查詢一條資料利用到兩個列的索引

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

2.4 檢視執行計劃

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)；這便是索引合併，利用兩個索引,然後合併兩個結果(取交集或者並集或者兩者都有)

查詢結果：

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`);

注意，多列索引很關注索引列的順序（因為 customer_id 的選擇性更大,所以把它放前面）。

2.6 再次查詢

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

發現多列索引加快的查詢（這裡資料量還是較小,更大的時候比較更明顯）。

3、多列索引的使用順序

3.1 怎麼選擇建立組合索引時，列的順序

多列索引的列順序至關重要，如何選擇索引的列順序有一個經驗法則：將選擇性最高的列放到索引最前列（但是不是絕對的）。經驗法則考慮全域性的基數和選擇性，而不是某個具體的查詢：

mysql> select count(DISTINCT staff_id) / count(*) AS staff_id_selectivity, count(DISTINCT customer_id) / count(*) AS customer_id_selectivity, count(*) from paymentG;
*************************** 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 組合索引的使用規則

索引可以理解成排好序的資料結構。組合索引可以這樣理解，比如（a,b,c），abc 都是排好序的，在任意一段 a 的下面 b 都是排好序的，任何一段 b 下面 c都是排好序的；

生效的規則是：從前往後依次使用生效，如果中間某個索引沒有使用，那麼斷點前面的索引部分起作用，斷點後面的索引沒有起作用；

比如：

where a=3 and b=45 and c=5 .... 這種三個索引順序使用中間沒有斷點，全部發揮作用；
where a=3 and c=5... 這種情況下b就是斷點，a發揮了效果，c沒有效果
where b=3 and c=4... 這種情況下a就是斷點，在a後面的索引都沒有發揮作用，這種寫法聯合索引沒有發揮任何效果；
where b=45 and a=3 and c=5 .... 這個跟第一個一樣，全部發揮作用，abc只要用上了就行，跟寫的順序無關

（a,b,c）多列索引使用的範例，說明：（a,b,c）組合索引和(a,c,b）是不一樣的

(0)    select * from mytable where a=3 and b=5 and c=4;
abc三個索引都在where條件裡面用到了，而且都發揮了作用
(1)    select * from mytable where  c=4 and b=6 and a=3;
這條語句列出來只想說明 mysql沒有那麼笨，where裡面的條件順序在查詢之前會被mysql自動優化，效果跟上一句一樣
(2)    select * from mytable where a=3 and c=7;
a用到索引，b沒有用，所以c是沒有用到索引效果的
(3)    select * from mytable where a=3 and b>7 and c=3;
a用到了，b也用到了，c沒有用到，這個地方b是範圍值，也算斷點，只不過自身用到了索引
(4)    select * from mytable where b=3 and c=4;
因為a索引沒有使用，所以這裡 bc都沒有用上索引效果
(5)    select * from mytable where a>4 and b=7 and c=9;
a用到了  b沒有使用，c沒有使用
(6)    select * from mytable where a=3 order by b;
a用到了索引，b在結果排序中也用到了索引的效果，前面說了，a下面任意一段的b是排好序的
(7)    select * from mytable where a=3 order by c;
a用到了索引，但是這個地方c沒有發揮排序效果，因為中間斷點了，使用 explain 可以看到 filesort
(8)    select * from mytable where b=3 order by a;
b沒有用到索引，排序中a也沒有發揮索引效果

到此這篇關於MySQL組合索引(多列索引)使用與優化的文章就介紹到這了,更多相關mysql組合索引使用內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！