sql中index有什么好處 索引INDEX提升查詢性能的4個(gè)原理

索引通過(guò)減少數(shù)據(jù)掃描量、利用排序結(jié)果集、覆蓋索引和優(yōu)化連接操作提升查詢性能。1. 減少數(shù)據(jù)掃描量是核心原理，通過(guò)b樹(shù)或哈希表結(jié)構(gòu)快速定位目標(biāo)行；2. 排序結(jié)果集允許數(shù)據(jù)庫(kù)直接使用已排序的索引避免額外排序；3. 覆蓋索引可直接從索引獲取所有所需字段，省去回表查詢；4. 連接優(yōu)化通過(guò)索引加速多表連接中的匹配過(guò)程。選擇索引列應(yīng)考慮where子句常用列、高區(qū)分度列、常用于排序的列，并避免小表和過(guò)度索引。索引失效常見(jiàn)原因包括對(duì)列使用函數(shù)、隱式類型轉(zhuǎn)換、not/操作符、like以%開(kāi)頭、未遵循最左前綴原則以及優(yōu)化器選擇全表掃描。可通過(guò)explain命令查看sql是否使用索引及執(zhí)行計(jì)劃詳情。

索引，簡(jiǎn)單來(lái)說(shuō)，就像一本書(shū)的目錄。沒(méi)有目錄，你要找某個(gè)章節(jié)，只能一頁(yè)一頁(yè)翻；有了目錄，直接定位到頁(yè)碼，效率自然大大提高。

索引在SQL中主要通過(guò)減少數(shù)據(jù)庫(kù)需要掃描的數(shù)據(jù)量來(lái)提高查詢速度，但同時(shí)也會(huì)增加寫(xiě)入操作的成本。

索引INDEX提升查詢性能的4個(gè)原理：

1. 減少數(shù)據(jù)掃描量： 這是最核心的。索引通過(guò)創(chuàng)建數(shù)據(jù)結(jié)構(gòu)的副本（通常是B樹(shù)或哈希表），允許數(shù)據(jù)庫(kù)引擎快速定位到包含目標(biāo)值的行，而無(wú)需掃描整個(gè)表。想象一下，在一本500頁(yè)的書(shū)里找一個(gè)詞，沒(méi)索引的話，你得從頭翻到尾。有了索引，直接根據(jù)詞在索引里的位置，找到對(duì)應(yīng)的頁(yè)碼。

2. 排序結(jié)果集： 有些查詢需要排序，比如 ORDER BY。如果索引已經(jīng)按照排序字段排好序，數(shù)據(jù)庫(kù)可以直接使用索引的排序結(jié)果，避免額外的排序操作。這就像你的書(shū)架，如果你按照作者姓名排好序，找特定作者的書(shū)就非常快。

3. 覆蓋索引： 如果一個(gè)索引包含了查詢所需的所有字段（包括 WHERE 子句和 select 子句中的字段），那么數(shù)據(jù)庫(kù)可以直接從索引中獲取數(shù)據(jù)，而不需要回表查詢。回表查詢是指，先通過(guò)索引找到行的位置，然后再去表中讀取完整的行數(shù)據(jù)。覆蓋索引避免了這一步，性能提升非常明顯。

4. 連接優(yōu)化： 在多表連接查詢中，索引可以加速連接操作。數(shù)據(jù)庫(kù)可以使用索引快速找到匹配的行，避免全表掃描。這有點(diǎn)像用電話號(hào)碼簿找人，如果電話號(hào)碼簿按照姓名排序，找到特定姓名的人的電話號(hào)碼就很快。

如何選擇合適的索引列？

選擇合適的索引列是個(gè)技術(shù)活，沒(méi)有絕對(duì)的公式，需要根據(jù)實(shí)際情況權(quán)衡。一般來(lái)說(shuō)，以下幾點(diǎn)可以作為參考：

• WHERE子句中常用的列： 這是最基本的原則。如果某個(gè)列經(jīng)常出現(xiàn)在 WHERE 子句中，那么它很可能是一個(gè)好的索引候選列。
• 區(qū)分度高的列： 區(qū)分度是指列中不同值的數(shù)量。區(qū)分度越高的列，索引效果越好。比如，id 列的區(qū)分度通常很高，適合作為索引。而 性別 列的區(qū)分度很低，索引效果可能不明顯。
• 經(jīng)常用于排序的列： 如果某個(gè)列經(jīng)常用于 ORDER BY 子句，那么它可以考慮作為索引列。
• 避免在小表上創(chuàng)建索引： 小表的數(shù)據(jù)量很小，全表掃描可能比使用索引更快。
• 不要過(guò)度索引： 索引會(huì)增加寫(xiě)入操作的成本。過(guò)多的索引會(huì)降低寫(xiě)入性能。

舉個(gè)例子，假設(shè)你有一個(gè) user 表，包含 id、name、age、city 等字段。如果你的查詢經(jīng)常是 SELECT * FROM user WHERE city = ‘北京’，那么 city 列就是一個(gè)好的索引候選列。如果你的查詢經(jīng)常是 SELECT * FROM user WHERE age > 20 ORDER BY name，那么 age 和 name 列都可以考慮作為索引列。

索引失效的常見(jiàn)原因有哪些？

即使創(chuàng)建了索引，也可能因?yàn)楦鞣N原因?qū)е滤饕В瑥亩绊懖樵冃阅堋３Ｒ?jiàn)的索引失效原因包括：

• 使用了函數(shù)或表達(dá)式： 在 WHERE 子句中對(duì)索引列使用了函數(shù)或表達(dá)式，會(huì)導(dǎo)致索引失效。比如 WHERE YEAR(date) = 2023。因?yàn)閿?shù)據(jù)庫(kù)無(wú)法直接使用索引來(lái)計(jì)算函數(shù)或表達(dá)式的結(jié)果。
• 隱式類型轉(zhuǎn)換： 如果查詢條件中的數(shù)據(jù)類型與索引列的數(shù)據(jù)類型不匹配，可能會(huì)發(fā)生隱式類型轉(zhuǎn)換，導(dǎo)致索引失效。比如，索引列是字符串類型，而查詢條件是數(shù)字類型，WHERE id = 123，如果id是字符串類型，可能會(huì)導(dǎo)致索引失效。
• 使用了 NOT、、!=： 這些操作符會(huì)導(dǎo)致數(shù)據(jù)庫(kù)無(wú)法使用索引。
• LIKE 查詢以 % 開(kāi)頭： LIKE ‘%keyword’ 會(huì)導(dǎo)致索引失效，因?yàn)閿?shù)據(jù)庫(kù)無(wú)法使用索引來(lái)定位以任意字符開(kāi)頭的字符串。LIKE ‘keyword%’ 則可以使用索引。
• 組合索引未遵循最左前綴原則： 如果創(chuàng)建了組合索引 (a, b, c)，那么只有在查詢條件中包含 a 或 (a, b) 或 (a, b, c) 時(shí)，才能使用索引。如果查詢條件只包含 b 或 c，則索引失效。
• 優(yōu)化器認(rèn)為全表掃描更快： 在某些情況下，數(shù)據(jù)庫(kù)優(yōu)化器可能會(huì)認(rèn)為全表掃描比使用索引更快，從而放棄使用索引。這通常發(fā)生在數(shù)據(jù)量很小或者索引區(qū)分度很低的情況下。

如何查看sql語(yǔ)句是否使用了索引？

要判斷SQL語(yǔ)句是否使用了索引，可以使用 EXPLaiN 命令。EXPLAIN 命令可以顯示SQL語(yǔ)句的執(zhí)行計(jì)劃，包括是否使用了索引、使用了哪個(gè)索引、掃描了多少行等信息。

例如，對(duì)于以下SQL語(yǔ)句：

SELECT * FROM user WHERE city = '北京';

可以使用以下命令查看執(zhí)行計(jì)劃：

EXPLAIN SELECT * FROM user WHERE city = '北京';

執(zhí)行結(jié)果會(huì)顯示一個(gè)表格，其中 possible_keys 列顯示可能使用的索引，key 列顯示實(shí)際使用的索引，rows 列顯示掃描的行數(shù)。如果 key 列為 NULL，則表示沒(méi)有使用索引。如果 rows 列的值很小，則表示使用了索引，并且效果很好。

通過(guò)分析 EXPLAIN 命令的結(jié)果，可以了解SQL語(yǔ)句的執(zhí)行情況，并根據(jù)需要優(yōu)化索引或SQL語(yǔ)句，以提高查詢性能。