避免索引失效

在MySQL中，索引是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。它就像一本書(shū)的目錄，通過(guò)索引可以快速定位到數(shù)據(jù)的具體位置，從而減少對(duì)數(shù)據(jù)庫(kù)的掃描量，提高查詢速度。索引可以存儲(chǔ)在表中的一個(gè)或多個(gè)列上，創(chuàng)建索引后可以大大加快數(shù)據(jù)的檢索速度，但也會(huì)占用額外的磁盤空間，并且在數(shù)據(jù)插入、刪除和更新時(shí)需要對(duì)索引進(jìn)行維護(hù)，這可能會(huì)降低這些操作的性能

盡管索引能顯著提升查詢性能，但在某些情況下，索引可能會(huì)失效，導(dǎo)致查詢性能并未達(dá)到預(yù)期。以下是一些常見(jiàn)的導(dǎo)致索引失效的情況：

全值匹配：

通常，當(dāng)你對(duì)索引中的所有列都指定了具體值時(shí)，索引會(huì)生效。但是，這里提到的“避免索引失效”更多是指在其他情況下，因?yàn)槿灯ヅ浔旧砭褪菫榱死盟饕摹?/p>

假設(shè)有一個(gè)表employees，上面有一個(gè)索引(first_name, last_name)。

SELECT * FROM employees WHERE first_name = 'John' AND last_name = 'Doe';

這個(gè)查詢將全值匹配索引中的所有列，因此索引會(huì)生效。

最左前綴法則：

對(duì)于復(fù)合索引（即索引包含多個(gè)列），MySQL會(huì)遵循最左前綴法則。這意味著，在查詢條件中，如果索引列不是以索引中的第一個(gè)列開(kāi)始，則索引可能不會(huì)被使用。例如，如果有一個(gè)索引是(A, B, C)，那么查詢條件中只有A、A和B、A和B和C的組合才能有效利用索引，而只有B或B和C的組合則不會(huì)。

繼續(xù)使用上面的employees表和索引(first_name, last_name)。

SELECT * FROM employees WHERE first_name = 'John';  
SELECT * FROM employees WHERE first_name = 'John' AND last_name = 'Doe';

SELECT * FROM employees WHERE last_name = 'Doe';

• 有效利用索引的查詢：
• 不會(huì)利用索引的查詢（僅針對(duì)last_name）：

使用函數(shù)或計(jì)算：

如果在索引列上使用了函數(shù)或進(jìn)行了計(jì)算，那么索引可能不會(huì)被使用。例如，如果有一個(gè)索引在列date_col上，但查詢條件為YEAR(date_col) = 2023，那么索引可能不會(huì)被利用。

假設(shè)employees表有一個(gè)日期列hire_date，并且該列上有索引。

-- 索引可能不會(huì)被利用  
SELECT * FROM employees WHERE YEAR(hire_date) = 2020;

相比之下，如果查詢條件沒(méi)有使用函數(shù)：

-- 索引會(huì)被利用  
SELECT * FROM employees WHERE hire_date >= '2020-01-01' AND hire_date < '2021-01-01';

隱式類型轉(zhuǎn)換：

如果索引列的數(shù)據(jù)類型和查詢條件中的數(shù)據(jù)類型不一致，并且MySQL需要進(jìn)行隱式類型轉(zhuǎn)換來(lái)匹配它們，那么索引可能不會(huì)被使用。

-- 隱式類型轉(zhuǎn)換，索引可能不會(huì)被利用  
SELECT * FROM employees WHERE employee_id = 123;  -- 假設(shè)employee_id是字符串類型，但查詢中使用了數(shù)字

如果employee_id是數(shù)字類型，則索引會(huì)被利用。

例子：

-- 索引可能不會(huì)被有效利用（取決于MySQL優(yōu)化器的決定）  
SELECT * FROM employees WHERE first_name != 'John';

使用不等于（!= 或 <>）操作符：

使用不等于操作符時(shí)，MySQL可能會(huì)選擇全表掃描而不是使用索引，特別是當(dāng)查詢條件中的列是索引列時(shí)。

-- 索引可能不會(huì)被有效利用（取決于MySQL優(yōu)化器的決定）  
SELECT * FROM employees WHERE first_name != 'John';

使用IS NULL 或 IS NOT NULL：

對(duì)于索引列，使用IS NULL或IS NOT NULL條件可能會(huì)導(dǎo)致索引失效，盡管這取決于MySQL的版本和具體的查詢優(yōu)化器行為。

-- 對(duì)于索引列，IS NULL 或 IS NOT NULL 可能導(dǎo)致索引失效（取決于MySQL版本和查詢優(yōu)化器）  
SELECT * FROM employees WHERE email IS NULL;  -- 假設(shè)email列上有索引

LIKE以通配符開(kāi)始：

當(dāng)使用LIKE操作符并且模式以通配符（如%）開(kāi)始時(shí)，MySQL可能無(wú)法使用索引。例如，name LIKE '%abc'不會(huì)利用name列上的索引。

如果僅僅是尾部模糊匹配，索引不會(huì)失效。如果是頭部模糊匹配，索引失效。

1.explain select * from tb_seller where name like '腳本之家%';
2.explain select * from tb_seller where name like '%腳本之家';
3.explain select * from tb_seller where name like '%腳本之家%';

OR條件：

當(dāng)查詢條件包含OR時(shí)，如果OR連接的兩個(gè)條件分別指向不同的索引列，MySQL可能無(wú)法有效地使用索引。

-- 如果OR連接的兩個(gè)條件分別指向不同的索引列，MySQL可能無(wú)法有效地使用索引  
SELECT * FROM employees WHERE first_name = 'John' OR last_name = 'Doe';

索引列參與計(jì)算或函數(shù)：

如果索引列參與了計(jì)算或函數(shù)操作，則可能導(dǎo)致索引失效。

-- 索引列參與了計(jì)算，索引可能不會(huì)被利用  
SELECT * FROM employees WHERE first_name = CONCAT('J', 'ohn');

到此這篇關(guān)于MySQL避免索引失效的方法示例的文章就介紹到這了,更多相關(guān)MySQL避免索引失效內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！ 

最新評(píng)論