聯合索引失效情況分析

一.模擬表結構：

背景：

MySQL版本——8.0.37

表結構DDL：

CREATE TABLE `unite_index_table` (`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主鍵',`clomn_first` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '列1',`clomn_second` bigint NOT NULL COMMENT '列2',`clomn_third` bigint NOT NULL COMMENT '列3',`clomn_fourth` int NOT NULL COMMENT '列4',`clomn_fifth` bigint NOT NULL COMMENT '列5',`clomn_sixth` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '列6',`clomn_seventh` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '列7',`clomn_eighth` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '列8',`clomn_ninth` int NOT NULL COMMENT '列9',`clomn_tenth` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '列10',`clomn_eleventh` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '列11',`clomn_twelfth` bigint NOT NULL COMMENT '列12',`clomn_thirteenth` bigint NOT NULL COMMENT '列13',`clomn_fourteenth` bigint NOT NULL COMMENT '列14',`clomn_fifteenth` tinyint NOT NULL DEFAULT '1' COMMENT '列15',PRIMARY KEY (`id`),KEY `idx_clomn_fifth` (`clomn_fifth`),KEY `idx_unite` (`clomn_second`,`clomn_third`,`clomn_twelfth`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='模擬表';

表結構比較奇怪，是為了模擬實際生產的表結構，表結構信息進行脫敏；

?創建mock數據：
?

DROP PROCEDURE
IFEXISTS generate_mock_data;DELIMITER //
CREATE PROCEDURE generate_mock_data ( IN row_count INT ) BEGINDECLAREi INT DEFAULT 0;START TRANSACTION;-- 開始事務WHILEi < row_count DOINSERT INTO unite_index_table (clomn_first,clomn_second,clomn_third,clomn_fourth,clomn_fifth,clomn_sixth,clomn_seventh,clomn_eighth,clomn_ninth,clomn_tenth,clomn_eleventh,clomn_twelfth,clomn_thirteenth,clomn_fourteenth,clomn_fifteenth )VALUES(SUBSTRING( MD5( RAND( ) ), 1, 20 ),FLOOR( RAND( ) * 10000000000 ),FLOOR( RAND( ) * 10000000000 ),FLOOR( RAND( ) * 100000 ),FLOOR( RAND( ) * 10000000000 ),SUBSTRING( MD5( RAND( ) ), 1, 64 ),IF( RAND( ) > 0.5, SUBSTRING( MD5( RAND( ) ), 1, 64 ), NULL ),IF( RAND( ) > 0.5, SUBSTRING( MD5( RAND( ) ), 1, 20 ), NULL ),FLOOR( RAND( ) * 256 ),IF( RAND( ) > 0.5, SUBSTRING( MD5( RAND( ) ), 1, 1024 ), NULL ),IF( RAND( ) > 0.5, SUBSTRING( MD5( RAND( ) ), 1, 64 ), NULL ),FLOOR( RAND( ) * 10000000000 ),UNIX_TIMESTAMP( ),UNIX_TIMESTAMP( ),FLOOR( RAND( ) * 2 ) );SET i = i + 1;END WHILE;COMMIT;-- 提交事務END // 
DELIMITER;
CALL generate_mock_data ( 1000000 );
DROP PROCEDURE
IFEXISTS generate_mock_data;

?二.不同情況下的查詢：

背景補充：

1.做模擬查詢的時候，如果使用區間查詢（>,>=,<,<=,!=,between and），區間查詢的值需要再表中存在，而不是超出區間，不然可能會導致explain分析該SQL會使用索引（實則為查詢值越界）

補充：

摘自MySQL官網
`key_len`?(JSON name:?`key_length`)	The?`key_len`?column indicates the length of the key that MySQL decided to use. The value of?`key_len`?enables you to determine how many parts of a multiple-part key MySQL actually uses. If the?`key`?column says?`NULL`, the?`key_len`?column also says?`NULL`. Due to the key storage format, the key length is one greater for a column that can be?`NULL`?than for a?`NOT NULL`?column.

2.key_len 的計算基于索引中每列的數據類型、字符集以及是否允許 NULL。以下是一些常見數據類型的索引長度：

INT：4 字節。
BIGINT：8 字節。
VARCHAR(n)：根據字符集計算。例如，utf8mb4 字符集下，VARCHAR(20) 可能占用 n * 4 字節（utf8mb4 每個字符最多 4 字節）。
CHAR(n)：與 VARCHAR 類似，但 CHAR 是固定長度。
如果列允許 NULL，則會額外增加 1 字節（用于存儲 NULL 標志）。

在聯合索引中，key_len 是查詢中實際用到的索引列的長度之和。MySQL 會根據查詢條件和最左匹配原則，決定使用索引的前幾列。

key_len展示所使用到的索引key的長度，會根據具體使用到的索引的聯合索引的key個數以及索引列的類型發生變化；

例如：

僅使用首列索引；
?使用所有索引列；

能使用到索引的情況：

1.使用索引的首列等值查詢和第三列區間查詢：

EXPLAIN SELECT* 
FROMunite_index_table 
WHEREclomn_twelfth >= 7827883584 AND clomn_second = 2058342613

SQL Explain
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	unite_index_table		ref	idx_unite	idx_unite	8	const	574	33.33	Using index condition

?分析得出，因為使用了聯合索引的第一列，所以查詢是使用到了索引，具體使用情況為：Using index condition

聯合索引哪怕是僅用到了索引首列，也是可以走索引優化查詢（另外提一嘴，是否走聯合索引和where條件后的列先后順序無關，最左匹配和查詢時列索引順序毫無關系，MySQL對SQL會進行解釋器優化）；

2.使用索引首列等值查詢，第二列區間查詢，第三列區間查詢

EXPLAIN SELECT* 
FROMunite_index_table 
WHEREclomn_twelfth != 3665470530 AND clomn_second = 6132267663 AND clomn_third >= 845697131

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	unite_index_table		range	idx_unite	idx_unite	16		1	90	Using index condition

使用到了索引，key_len為16，表名使用了索引的首列和次列；

補充說明：

//將clomn_second全部設置為1，讓索引列失去特異性，驗證第一種情況是否仍然成立：

EXPLAIN SELECT* 
FROMunite_index_table 
WHEREclomn_twelfth >= 7827883584 AND clomn_second = 1

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	unite_index_table		ref	idx_unite	idx_unite	8	const	304216	33.33	Using index condition

?雖然索引失去了特異性，仍然使用了索引，但是此時的key_len變成了8；

//再將clomn_third全部設置為2，讓索引列失去特異性，驗證第二種情況是否仍然成立：

EXPLAIN SELECT* 
FROMunite_index_table 
WHEREclomn_twelfth != 3665470530 AND clomn_second = 1 AND clomn_third = 2

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	unite_index_table		ALL	idx_unite				606845	100	Using where

?出乎意料的，聯合索引第一列索引和第二列索引列同時失去特異性后，導致查詢不能夠走到索引，造成全表掃描

不能使用到索引的情況：

1.索引第一列為區域查詢（帶有>或者<），不包含其他列

EXPLAIN SELECT* 
FROMunite_index_table 
WHEREclomn_second > 251963017

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	unite_index_table		ALL	idx_unite				610742	50	Using where

查詢僅帶有首列的區間查詢，?查詢不能走聯合索引

2.索引第一列為區間查詢（帶有>或者<）

EXPLAIN SELECT* 
FROMunite_index_table 
WHEREclomn_second > 251963017 AND clomn_third = 5251684771

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	unite_index_table		ALL	idx_unite				610742	5	Using where

因為首行非等值查詢，而是區間查詢，且有其他查詢列，聯合查詢不走索引（上一個示例都不會索引，這個更不會）

3.使用聯合索引的第二列和第三列

EXPLAIN SELECT* 
FROMunite_index_table 
WHEREclomn_third = 5251684771 AND clomn_twelfth > 2058342

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	unite_index_table		ALL					610742	3.33	Using where

由于沒有篩選首列，聯合索引沒能走到聯合索引的優化，即通常講的不滿足最左匹配原則；

三.總結：

總體結論

首列等值查詢是關鍵：
- 只要首列（clomn_second）使用等值查詢（=)，即使后續列使用區間查詢（>、>=）或不等值查詢（!=），聯合索引仍可使用。
首列區間查詢導致索引失效：
- 當首列使用區間查詢（>、<等），無論后續列條件如何，聯合索引無法使用，導致全表掃描。
最左匹配原則：
- 聯合索引必須從首列開始匹配，跳過首列直接查詢后續列無法利用索引。
特異性影響：
- 首列失去特異性時，索引仍可使用，但性能下降（掃描行數增加）。
- 首列和后續列均失去特異性時，索引可能失效，導致全表掃描。
查詢條件順序無關：
- WHERE子句中列的順序不影響索引使用，MySQL優化器會自動調整。