圖文詳解Mysql索引的最左字首原則

前言

之所以有這個最左字首索引

歸根結底是mysql的資料庫結構 B+樹

在實際問題中 比如

索引index (a,b,c)有三個欄位，

使用查詢語句select * from table where c = '1' ，sql語句不會走index索引的

select * from table where b =‘1’ and c ='2' 這個語句也不會走index索引

1. 定義

最左字首匹配原則：在MySQL建立聯合索引時會遵守最左字首匹配原則，即最左優先，在檢索資料時從聯合索引的最左邊開始匹配

為了更好辨別這種情況，通過建立表格以及索引的情況進行分析

2. 全索引順序

建立一張表，建立一個聯合索引，如果順序顛倒，其實還是可以識別的，但是一定要有它的全部部分

建立表

CREATE TABLE staffs(
	id INT PRIMARY KEY AUTO_INCREMENT,
	`name` VARCHAR(24) NOT NULL DEFAULT'' COMMENT'姓名',
	`age` INT NOT NULL DEFAULT 0 COMMENT'年齡',
	`pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'職位',
	`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入職時間'
)CHARSET utf8 COMMENT'員工記錄表';

INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());

建立索引ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(name,age,pos);

索引的順序位name-age-pos

顯示其索引有沒有show index from staffs;

通過顛倒其左右順序，其執行都是一樣的

主要的語句是這三句

1. explain select *from staffs where name='z3'and age=22 and pos='manager';
2. explain select *from staffs where pos='manager' and name='z3'and age=22;
3. explain select *from staffs where age=22 and pos='manager' and name='z3';

以上三者的順序顛倒，都使用到了聯合索引

最主要是因為MySQL中有查詢優化器explain，所以sql語句中欄位的順序不需要和聯合索引定義的欄位順序相同，查詢優化器會判斷糾正這條SQL語句以什麼樣的順序執行效率高，最後才能生成真正的執行計劃

不論以何種順序都可使用到聯合索引

3. 部分索引順序

3.1 正序

如果是按照順序（缺胳膊斷腿的），都是一樣的

1. explain select *from staffs where name=‘z3’;
2. explain select *from staffs where name='z3’and age=22;
3. explain select *from staffs where name='z3’and age=22;

其type都是ref型別，但是其欄位長度會有微小變化，也就是它定義的字長長度變化而已

3.2 亂序

如果部分索引的順序打亂

1. 只查第一個索引explain select *from staffs where name='z3';
2. 跳過中間的索引 explain select *from staffs where name='z3' and pos='manager';
3. 只查最後的索引 explain select *from staffs where pos='manager';

可以發現正序的時候

如果缺胳膊少腿，也是按照正常的索引

即使跳過了中間的索引，也是可以使用到索引去查詢

但是如果只查最後的索引

type就是all型別，直接整個表的查詢了（這是因為沒有從name一開始匹配，直接匹配pos的話，會顯示無序，）

有些時候type就是index型別，這是因為還是可以通過索引進行查詢

index是對所有索引樹進行掃描，而all是對整個磁碟的資料進行全表掃描

4. 模糊索引

類似模糊索引就會使用到like的語句

所以下面的三條語句

如果複合最左字首的話，會使用到range或者是index的型別進行索引

1. explain select *from staffs where name like '3%'; 最左字首索引，型別為index或者range
2. explain select *from staffs where name like '%3%'; 型別為all，全表查詢
3. explain select *from staffs where name like '%3%';，型別為all，全表查詢

5. 範圍索引

如果查詢多個欄位的時候，出現了中間是範圍的話，建議刪除該索引，剔除中間索引即可

具體思路如下

建立一張單表

CREATE TABLE IF NOT EXISTS article(
	id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	author_id INT(10) UNSIGNED NOT NULL,
	category_id INT(10) UNSIGNED NOT NULL,
	views INT(10) UNSIGNED NOT NULL,
	comments INT(10) UNSIGNED NOT NULL,
	title VARCHAR(255) NOT NULL,
	content TEXT NOT NULL
);

INSERT INTO article(author_id,category_id,views,comments,title,content)
VALUES
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(1,1,3,3,'3','3');

經過如下查詢：

explain SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

發現其上面的單表查詢，不是索引的話，他是進行了全表查詢，而且在extra還出現了Using filesort等問題

所以思路可以有建立其複合索引

具體建立複合索引有兩種方式：

1. create index idx_article_ccv on article(category_id,comments,views);
2. ALTER TABLE 'article' ADD INDEX idx_article_ccv ( 'category_id , 'comments', 'views' );

但這只是去除了它的範圍，如果要去除Using filesort問題的話，還要將其中間的條件範圍改為等於號才可滿足

發現其思路不行，所以刪除其索引 DROP INDEX idx_article_ccv ON article;

主要的原因是：

這是因為按照BTree索引的工作原理，先排序category_id，如果遇到相同的category_id則再排序comments,如果遇到相同的comments 則再排序views。

當comments欄位在聯合索引裡處於中間位置時，因comments > 1條件是一個範圍值(所謂range)，MySQL無法利用索引再對後面的views部分進行檢索，即range型別查詢欄位後面的索引無效。

所以建立複合索引是對的

但是其思路要避開中間那個範圍的索引進去

只加入另外兩個索引即可create index idx_article_cv on article(category_id, views);

總結

到此這篇關於通過Mysql索引的最左字首原則的文章就介紹到這了,更多相關Mysql索引最左字首原則內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！