MySQL避免索引失效的方法示例
避免索引失效
在MySQL中,索引是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。它就像一本書的目錄,通過索引可以快速定位到數(shù)據(jù)的具體位置,從而減少對數(shù)據(jù)庫的掃描量,提高查詢速度。索引可以存儲在表中的一個或多個列上,創(chuàng)建索引后可以大大加快數(shù)據(jù)的檢索速度,但也會占用額外的磁盤空間,并且在數(shù)據(jù)插入、刪除和更新時需要對索引進(jìn)行維護(hù),這可能會降低這些操作的性能
盡管索引能顯著提升查詢性能,但在某些情況下,索引可能會失效,導(dǎo)致查詢性能并未達(dá)到預(yù)期。以下是一些常見的導(dǎo)致索引失效的情況:
全值匹配:
通常,當(dāng)你對索引中的所有列都指定了具體值時,索引會生效。但是,這里提到的“避免索引失效”更多是指在其他情況下,因?yàn)槿灯ヅ浔旧砭褪菫榱死盟饕摹?/p>
假設(shè)有一個表employees
,上面有一個索引(first_name, last_name)
。
SELECT * FROM employees WHERE first_name = 'John' AND last_name = 'Doe';
這個查詢將全值匹配索引中的所有列,因此索引會生效。
最左前綴法則:
對于復(fù)合索引(即索引包含多個列),MySQL會遵循最左前綴法則。這意味著,在查詢條件中,如果索引列不是以索引中的第一個列開始,則索引可能不會被使用。例如,如果有一個索引是(A, B, C),那么查詢條件中只有A、A和B、A和B和C的組合才能有效利用索引,而只有B或B和C的組合則不會。
繼續(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';
- 有效利用索引的查詢:
- 不會利用索引的查詢(僅針對
last_name
):
使用函數(shù)或計(jì)算:
如果在索引列上使用了函數(shù)或進(jìn)行了計(jì)算,那么索引可能不會被使用。例如,如果有一個索引在列date_col
上,但查詢條件為YEAR(date_col) = 2023
,那么索引可能不會被利用。
假設(shè)employees
表有一個日期列hire_date
,并且該列上有索引。
-- 索引可能不會被利用 SELECT * FROM employees WHERE YEAR(hire_date) = 2020;
相比之下,如果查詢條件沒有使用函數(shù):
-- 索引會被利用 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)換來匹配它們,那么索引可能不會被使用。
-- 隱式類型轉(zhuǎn)換,索引可能不會被利用 SELECT * FROM employees WHERE employee_id = 123; -- 假設(shè)employee_id是字符串類型,但查詢中使用了數(shù)字
如果employee_id
是數(shù)字類型,則索引會被利用。
例子:
-- 索引可能不會被有效利用(取決于MySQL優(yōu)化器的決定) SELECT * FROM employees WHERE first_name != 'John';
使用不等于(!= 或 <>)操作符:
使用不等于操作符時,MySQL可能會選擇全表掃描而不是使用索引,特別是當(dāng)查詢條件中的列是索引列時。
-- 索引可能不會被有效利用(取決于MySQL優(yōu)化器的決定) SELECT * FROM employees WHERE first_name != 'John';
使用IS NULL 或 IS NOT NULL:
對于索引列,使用IS NULL
或IS NOT NULL
條件可能會導(dǎo)致索引失效,盡管這取決于MySQL的版本和具體的查詢優(yōu)化器行為。
-- 對于索引列,IS NULL 或 IS NOT NULL 可能導(dǎo)致索引失效(取決于MySQL版本和查詢優(yōu)化器) SELECT * FROM employees WHERE email IS NULL; -- 假設(shè)email列上有索引
LIKE以通配符開始:
當(dāng)使用LIKE
操作符并且模式以通配符(如%
)開始時,MySQL可能無法使用索引。例如,name LIKE '%abc'
不會利用name
列上的索引。
如果僅僅是尾部模糊匹配,索引不會失效。如果是頭部模糊匹配,索引失效。
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
時,如果OR連接的兩個條件分別指向不同的索引列,MySQL可能無法有效地使用索引。
-- 如果OR連接的兩個條件分別指向不同的索引列,MySQL可能無法有效地使用索引 SELECT * FROM employees WHERE first_name = 'John' OR last_name = 'Doe';
索引列參與計(jì)算或函數(shù):
如果索引列參與了計(jì)算或函數(shù)操作,則可能導(dǎo)致索引失效。
-- 索引列參與了計(jì)算,索引可能不會被利用 SELECT * FROM employees WHERE first_name = CONCAT('J', 'ohn');
到此這篇關(guān)于MySQL避免索引失效的方法示例的文章就介紹到這了,更多相關(guān)MySQL避免索引失效內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
關(guān)于MySql數(shù)據(jù)庫Update批量更新不同值的實(shí)現(xiàn)方法
這篇文章主要介紹了關(guān)于MySql數(shù)據(jù)庫Update批量更新不同值的實(shí)現(xiàn)方法,數(shù)據(jù)庫管理系統(tǒng)可以通過SQL管理數(shù)據(jù)庫,定義和操作數(shù)據(jù),維護(hù)數(shù)據(jù)的完整性和安全性,需要的朋友可以參考下2023-05-05MySQL索引底層數(shù)據(jù)結(jié)構(gòu)詳情
這篇文章主要介紹了MySQL索引底層數(shù)據(jù)結(jié)構(gòu)詳情,下面文章圍繞MySQL索引底層數(shù)據(jù)結(jié)構(gòu)的相關(guān)資料展開全篇文章,具有一定的參考價值,需要的小伙伴可以參考一下2021-12-12詳解MySQL數(shù)據(jù)庫--多表查詢--內(nèi)連接,外連接,子查詢,相關(guān)子查詢
這篇文章主要介紹了MySQL多表查詢,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2019-04-04一文詳解MySQL數(shù)據(jù)庫索引優(yōu)化的過程
在MySQL數(shù)據(jù)庫中,索引是一種關(guān)鍵的組件,它可以大大提高查詢的效率,但是,當(dāng)數(shù)據(jù)量增大或者查詢復(fù)雜度增加時,索引的選擇和優(yōu)化變得至關(guān)重要,本文將記錄MySQL數(shù)據(jù)庫索引優(yōu)化的過程,以幫助開發(fā)人員更好地理解和應(yīng)用索引優(yōu)化技巧2023-06-06MySQL利用profile分析慢sql詳解(group left join效率高于子查詢)
最近因?yàn)橐粋€用了子查詢的sql語句查詢很慢,嚴(yán)重影響了性能,所以需要進(jìn)行優(yōu)化,下面這篇文章主要跟大家介紹了關(guān)于MySQL利用profile分析慢sql的相關(guān)資料,文中介紹的非常詳細(xì),需要的朋友們可以參考借鑒,下面來一起看看吧。2017-03-03Mysql修改datadir導(dǎo)致無法啟動問題解決方法
這篇文章主要介紹了Mysql修改datadir導(dǎo)致無法啟動問題解決方法,本文原因是SELINUX導(dǎo)致,用關(guān)閉SELINUX的方法解決了這個問題,需要的朋友可以參考下2015-02-02rpm -ivh方式安裝mysql并修改數(shù)據(jù)存儲位置的實(shí)現(xiàn)
在Linux環(huán)境下進(jìn)行MySQL的安裝可以使用不同的方式,但在本文中我們將關(guān)注一種特定的方式,即通過RPM包的方式進(jìn)行安裝,本文主要介紹了rpm -ivh方式安裝mysql并修改數(shù)據(jù)存儲位置的實(shí)現(xiàn),感興趣的可以了解一下2023-09-09