• 創(chuàng)建數(shù)據(jù)庫、表,插入數(shù)據(jù)

create database idx_optimize character set 'utf8';
CREATE TABLE users(
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_name VARCHAR(20) NOT NULL COMMENT '姓名',
  user_age INT NOT NULL DEFAULT 0 COMMENT '年齡',
  user_level VARCHAR(20) NOT NULL COMMENT '用戶等級',
  reg_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注冊時間'
);
INSERT INTO users(user_name,user_age,user_level,reg_time)
VALUES('tom',17,'A',NOW()),('jack',18,'B',NOW()),('lucy',18,'C',NOW());

• 創(chuàng)建聯(lián)合索引

ALTER TABLE users ADD INDEX idx_nal (user_name,user_age,user_level) USING BTREE;

2.優(yōu)化原則詳解

1)最佳左前綴法則

最佳左前綴法則: 如果創(chuàng)建的是聯(lián)合索引，就必須遵守這個法則，當(dāng)使用聯(lián)合索引時，where后面條件需要從索引的最左前開始使用。

• 場景1: 按照索引字段順序使用，三個字段都使用了索引,沒有問題

EXPLAIN SELECT * FROM users WHERE user_name = 'tom' 
AND user_age = 17 AND user_level = 'A';

• 場景2: 直接跳過user_name使用索引字段，索引無效，未使用到索引。

EXPLAIN SELECT * FROM users WHERE user_age = 17 AND user_level = 'A';

• 場景3: 不按照創(chuàng)建聯(lián)合索引的順序,使用索引

EXPLAIN SELECT * FROM users WHERE 
user_age = 17 AND user_name = 'tom' AND user_level = 'A';

where后面查詢條件順序是 user_age、user_level、user_name與我們創(chuàng)建的索引順序user_name、user_age、user_level不一致，為什么還是使用了索引，原因是因?yàn)镸ySql底層優(yōu)化器對其進(jìn)行了優(yōu)化。

• 最佳左前綴底層原理

1. MySQL創(chuàng)建聯(lián)合索引的時候要遵守一個規(guī)則： 首先會對聯(lián)合索引最左邊的字段進(jìn)行排序，再在第一個字段的基礎(chǔ)之上對第二個字段進(jìn)行排序。

所以: 最佳左前綴原則其實(shí)是和B+樹的結(jié)構(gòu)有關(guān)系, 最左字段肯定是有序的, 第二個字段則是無序的(聯(lián)合索引的排序方式是: 先按照第一個字段進(jìn)行排序,如果第一個字段相等再根據(jù)第二個字段排序). 所以如果直接使用第二個字段 user_age 通常是使用不到索引的.

2) 不要在索引列上做任何計(jì)算

不要在索引列上做任何操作，比如計(jì)算、使用函數(shù)、自動或手動進(jìn)行類型轉(zhuǎn)換,會導(dǎo)致索引失效，從而使查詢轉(zhuǎn)向全表掃描。

• 插入數(shù)據(jù)

INSERT INTO users(user_name,user_age,user_level,reg_time) VALUES('11223344',22,'D',NOW());

• 場景1: 使用系統(tǒng)函數(shù) left()函數(shù),對user_name進(jìn)行操作

EXPLAIN SELECT * FROM users WHERE LEFT(user_name, 6) = '112233';

場景2: 字符串不加單引號 (隱式類型轉(zhuǎn)換)

varchar類型的字段，在查詢的時候不加單引號,就需要進(jìn)行隱式轉(zhuǎn)換, 導(dǎo)致索引失效，轉(zhuǎn)向全表掃描。

EXPLAIN SELECT * FROM users WHERE 

3) 范圍之后全失效

范圍之后全失效: where條件中如果有范圍條件，并且范圍條件之后還有其他條件.

• 場景1: 條件單獨(dú)使用user_name時, type=ref, key_len=62

-- 條件只有一個 user_name
EXPLAIN SELECT * FROM users WHERE user_name = 'tom';

場景2: 條件增加一個 user_age ( 使用常量等值) ,type= ref , key_len = 66

EXPLAIN SELECT * FROM users WHERE user_name = 'tom' AND user_age = 17;

場景3: 使用全值匹配, type = ref , key_len = 128 , 索引都利用上了.

EXPLAIN SELECT * FROM users WHERE user_name = 'tom' 
AND user_age = 17 AND user_level = 'A';

場景4: 使用范圍條件時, avg > 17 , type = range , key_len = 66 , 與場景3 比較,可以發(fā)現(xiàn) user_level 索引沒有用上.

-----使用范圍條件  user_age>17  ,user_level索引就失效了
EXPLAIN SELECT * FROM users WHERE user_name = 'tom' 
AND user_age > 17 AND user_level = 'A';

4) 避免使用 is null 、 is not null、!= 、or

• 使用 is null 會使索引失效

EXPLAIN SELECT * FROM users WHERE user_name IS NULL;
---Impossible where： 表示where條件不成立，不能返回任何的行

• 使用 is not null 會使索引失效

EXPLAIN SELECT * FROM users WHERE user_name IS NOT NULL;  ---全表掃描

• 使用 != 和 or 會使索引失效

EXPLAIN SELECT * FROM users WHERE user_name != 'tom';
EXPLAIN SELECT * FROM users WHERE user_name = 'tom' or user_name = 'jack';

5) like以%開頭會使索引失效

like查詢?yōu)榉秶樵儯?出現(xiàn)在左邊，則索引失效。%出現(xiàn)在右邊索引未失效.

• 場景1: 兩邊都有% 或者 字段右邊有%,索引都會失效

EXPLAIN SELECT * FROM users WHERE user_name LIKE '%tom%';
EXPLAIN SELECT * FROM users WHERE user_name LIKE '%tom';

對比場景1可以知道, 通過使用覆蓋索引 type = index,并且 extra = Using index,從全表掃描變成了全索引掃描.

場景2: 字段左邊有%,索引生效

EXPLAIN SELECT * FROM users WHERE user_name LIKE 'tom%';

解決%出現(xiàn)在左邊索引失效的方法

• 使用覆蓋索引

EXPLAIN SELECT user_name FROM users WHERE user_name LIKE '%jack%';
EXPLAIN SELECT user_name,user_age,user_level FROM users WHERE user_name LIKE '%jack%';

like 失效的原理

1. %號在右: 由于B+樹的索引順序，是按照首字母的大小進(jìn)行排序，%號在右的匹配又是匹配首字母。所以可以在B+樹上進(jìn)行有序的查找，查找首字母符合要求的數(shù)據(jù)。所以有些時候可以用到索引.
2. %號在左: 是匹配字符串尾部的數(shù)據(jù)，我們上面說了排序規(guī)則，尾部的字母是沒有順序的，所以不能按照索引順序查詢，就用不到索引.
3. 兩個%%號: 這個是查詢?nèi)我馕恢玫淖帜笣M足條件即可，只有首字母是進(jìn)行索引排序的，其他位置的字母都是相對無序的，所以查找任意位置的字母是用不上索引的.

索引優(yōu)化原則總結(jié)

• 最左前綴法則要遵守
• 索引列上不計(jì)算
• 范圍之后全失效
• 覆蓋索引記住用。
• 不等于、is null、is not null、or導(dǎo)致索引失效。
• like百分號加右邊，加左邊導(dǎo)致索引失效，解決方法：使用覆蓋索引。

到此這篇關(guān)于Mysql的索引優(yōu)化原則的文章就介紹到這了,更多相關(guān)mysql索引優(yōu)化內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論