Mysql索引失效問題demo

Mysql索引失效問題demo

# 1. 準備工作
CREATE TABLE `user` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(20) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` INT DEFAULT '0',
  `name` VARCHAR(30) COLLATE utf8mb4_bin DEFAULT NULL,
  `height` INT DEFAULT '0',
  `address` VARCHAR(30) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_code_age_name` (`code`,`age`,`name`),
  KEY `idx_height` (`height`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

INSERT INTO test.user (id, CODE, age, NAME, height,address) VALUES (1, '101', 21, '周星馳', 175,'香港');
INSERT INTO test.user (id, CODE, age, NAME, height,address) VALUES (2, '102', 18, '周杰倫', 173,'臺灣');
INSERT INTO test.user (id, CODE, age, NAME, height,address) VALUES (3, '103', 23, '蘇東坡', 174,'北宋');

# 8.0.28
SELECT VERSION();


EXPLAIN SELECT * FROM USER WHERE id=1;
# 由於id欄位是主鍵，該sql語句用到了主鍵索引。

# 2. 不滿足最左匹配原則
EXPLAIN SELECT * FROM USER WHERE CODE='101';

EXPLAIN SELECT * FROM USER WHERE CODE='101' AND age=21 

EXPLAIN SELECT * FROM USER WHERE CODE='101' AND age=21 AND NAME='周星馳';
# 上面三種情況，sql都能正常走索引。

EXPLAIN SELECT * FROM USER WHERE CODE = '101'  AND NAME='周星馳';
# 查詢條件原本的順序是：code、age、name，但這裡只有code和name中間斷層了，掉了age欄位，這種情況也能走code欄位上的索引。
# 這4條sql中都有code欄位，它是索引欄位中的第一個欄位，也就是最左邊的欄位。只要有這個欄位在，該sql已經就能走索引。
# 最左匹配原則

## 以下未應用索引
EXPLAIN SELECT * FROM USER WHERE age=21;
EXPLAIN SELECT * FROM USER WHERE age=21 AND NAME='周星馳';
EXPLAIN SELECT * FROM USER WHERE NAME='周星馳';
# 說明以上3種情況不滿足最左匹配原則，說白了是因為查詢條件中，沒有包含給定欄位最左邊的索引欄位，即欄位code。

# 3. 使用了select *
EXPLAIN SELECT * FROM USER WHERE NAME='蘇東坡';
# 在該sql中用了select *，從執行結果看，走了全表掃描，沒有用到任何索引，查詢效率是非常低的。

EXPLAIN 
SELECT CODE,NAME FROM USER 
WHERE NAME='蘇東坡';
# 如果select語句中的查詢列，都是索引列，那麼這些列被稱為覆蓋索引。這種情況下，查詢的相關欄位都能走索引，索引查詢效率相對來說更高一些。
# 而使用select *查詢所有列的資料，大機率會查詢非索引列的資料，非索引列不會走索引，查詢效率非常低。

# 4. 索引列上有計算
EXPLAIN SELECT * FROM USER WHERE id=1;
# 由於id欄位是主鍵，該sql語句用到了主鍵索引。

EXPLAIN SELECT * FROM USER WHERE id+1=2;
# 從上圖中的執行結果，能夠非常清楚的看出，該id欄位的主鍵索引，在有計算的情況下失效了。

# 5. 索引列用了函式
EXPLAIN SELECT * FROM USER  WHERE height=17;

EXPLAIN SELECT * FROM USER  WHERE SUBSTR(height,1,2)=17;
# 這時需要用到SUBSTR函式，用它擷取了height欄位的前面兩位字元，從第一個字元開始。
# 在使用該函式之後，該sql語句竟然走了全表掃描，索引失效了。

# 6. 欄位型別不同
EXPLAIN 
SELECT * FROM USER WHERE CODE="101";
# 溫馨提醒一下，查詢字元欄位時，用雙引號“和單引號都可以。

# 但如果你在寫sql時，不小心把引號弄掉了，把sql語句變成了：
EXPLAIN 
SELECT * FROM USER WHERE CODE=101;
# 該sql語句竟然變成了全表掃描。因為少寫了引號，這種小小的失誤，竟然讓code欄位上的索引失效了。
# 因為code欄位的型別是varchar，而傳參的型別是int，兩種型別不同。

# 如果int型別的height欄位，在查詢時加了引號條件，卻還可以走索引：
EXPLAIN SELECT * FROM USER 
WHERE height='175';
# 該sql語句確實走了索引。int型別的引數，不管在查詢時加沒加引號，都能走索引。

# mysql發現如果是int型別欄位作為查詢條件時，它會自動將該欄位的傳參進行隱式轉換，把字串轉換成int型別。

SELECT 1 + '1';
# mysql自動把字串1，轉換成了int型別的1，然後變成了：1+1=2。

SELECT CONCAT(1,'1');
# 如果你確實想拼接字串該怎麼辦？
# 答：可以使用concat關鍵字。

/**
關鍵問題來了：為什麼字串型別的欄位，傳入了int型別的引數時索引會失效呢？

答：根據mysql官網上解釋，字串'1'、' 1 '、'1a'都能轉換成int型別的1，也就是說可能會出現多個字串，對應一個int型別引數的情況。那麼，mysql怎麼知道該把int型別的1轉換成哪種字串，用哪個索引快速查值?

感興趣的小夥伴可以再看看官方文件：https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html
**/

# 7. like左邊包含%
SELECT * FROM USER WHERE NAME LIKE '李%';
/**
目前like查詢主要有三種情況：

like '%a'
like 'a%'
like '%a%'
**/

EXPLAIN SELECT * FROM USER
WHERE CODE LIKE '10%';


EXPLAIN SELECT * FROM USER
WHERE CODE LIKE '%1';
# 這種%在1左邊時，code欄位上索引失效了，該sql變成了全表掃描。

EXPLAIN SELECT * FROM USER
WHERE CODE LIKE '%1%';
# 該sql語句的索引也會失效。

/**
那麼，為什麼會出現這種現象呢？
答：其實很好理解，索引就像字典中的目錄。一般目錄是按字母或者拼音從小到大，從左到右排序，是有順序的。
我們在查目錄時，通常會先從左邊第一個字母進行匹對，如果相同，再匹對左邊第二個字母，如果再相同匹對其他的字母，以此類推。
透過這種方式我們能快速鎖定一個具體的目錄，或者縮小目錄的範圍。
但如果你硬要跟目錄的設計反著來，先從字典目錄右邊匹配第一個字母不現實。
**/

# 8. 列對比
EXPLAIN SELECT * FROM USER 
WHERE id=height
/**
id欄位本身是有主鍵索引的，同時height欄位也建了普通索引的，並且兩個欄位都是int型別，型別是一樣的。
但如果把兩個單獨建了索引的列，用來做列對比時索引會失效。
**/

# 9. 使用or關鍵字
EXPLAIN SELECT * FROM USER 
WHERE id=1 OR height='175';


EXPLAIN SELECT * FROM USER 
WHERE id=1 OR height='175' OR address='北宋';
# 之前的索引都失效了。因為你最後加的address欄位沒有加索引，從而導致其他欄位的索引都失效了。
# 注意：如果使用了or關鍵字，那麼它前面和後面的欄位都要加索引，不然所有的索引都會失效，這是一個大坑。

# 10. not in和not exists
/**
在我們日常工作中用得也比較多的，還有範圍查詢，常見的有：
in
exists
not in
not exists
between and
**/
EXPLAIN SELECT * FROM USER
WHERE height IN (173,174,175,176);
# sql語句中用in關鍵字是走了索引的。

EXPLAIN SELECT * FROM USER  t1
WHERE  EXISTS (SELECT 1 FROM USER t2 WHERE t2.height=173 AND t1.id=t2.id)
# 用exists關鍵字同樣走了索引。

/**
上面演示的兩個例子是正向的範圍，即在某些範圍之內。
那麼反向的範圍，即不在某些範圍之內，能走索引不？
**/
EXPLAIN SELECT * FROM USER
WHERE height NOT IN (173,174,175,176);
# 索引失效了。

EXPLAIN SELECT * FROM USER
WHERE id  NOT IN (173,174,175,176);
# 主鍵欄位中使用not in關鍵字查詢資料範圍，任然可以走索引。而普通索引欄位使用了not in關鍵字查詢資料範圍，索引會失效。

EXPLAIN SELECT * FROM USER  t1
WHERE  NOT EXISTS (SELECT 1 FROM USER t2 WHERE t2.height=173 AND t1.id=t2.id)
# sql語句中使用not exists關鍵後，t1表走了全表掃描，並沒有走索引。

# 11. order by的坑
# order by後面的條件，也要遵循聯合索引的最左匹配原則。
EXPLAIN SELECT * FROM USER
ORDER BY `code` LIMIT 100;

EXPLAIN SELECT * FROM USER
ORDER BY `code`,age LIMIT 100;

EXPLAIN SELECT * FROM USER
ORDER BY `code`,age,NAME LIMIT 100;
# 索引失效了?? 
# 除了遵循最左匹配原則之外，有個非常關鍵的地方是，後面還是加了limit關鍵字，如果不加它索引會失效。

## 查詢的是索引列，會適應索引。
EXPLAIN SELECT CODE,NAME FROM USER
ORDER BY `code`,age,NAME LIMIT 100;

EXPLAIN SELECT * FROM USER
WHERE CODE='101'
ORDER BY age;
# order by還能配合where一起遵循最左匹配原則。

EXPLAIN SELECT * FROM USER
WHERE CODE='101'
ORDER BY NAME;
# 雖說name是聯合索引的第三個欄位，但根據最左匹配原則，該sql語句依然能走索引，因為最左邊的第一個欄位code，在where中使用了。只不過order by的時候，排序效率比較低，需要走一次filesort排序

EXPLAIN SELECT * FROM USER
ORDER BY CODE DESC,age DESC LIMIT 100;
# order by後面的code和age欄位都用了降序，所以依然走了索引  失效了??

## 查詢的是索引列，會適應索引。
EXPLAIN SELECT CODE,NAME FROM USER
ORDER BY CODE DESC,age DESC LIMIT 100;

EXPLAIN SELECT * FROM USER
WHERE CODE='101'
ORDER BY CODE, NAME;
# code欄位在where和order by中都有，對於這種情況，從圖中的結果看出，還是能走了索引的。

EXPLAIN SELECT * FROM USER
ORDER BY CODE, NAME;
# 如果order by語句中沒有加where或limit關鍵字，該sql語句將不會走索引。


EXPLAIN SELECT * FROM USER
ORDER BY CODE, height LIMIT 100;
# 如果對多個索引進行order by，索引也失效了。

EXPLAIN SELECT * FROM USER
ORDER BY NAME LIMIT 100;
# name欄位是聯合索引的第三個欄位，從圖中看出如果order by不滿足最左匹配原則，確實不會走索引。

EXPLAIN SELECT * FROM USER
ORDER BY CODE ASC,age DESC LIMIT 100;
# 儘管order by後面的code和age欄位遵循了最左匹配原則，但由於一個欄位是用的升序，另一個欄位用的降序，最終會導致索引失效。