MySQL中提高性能的一個(gè)最有效的方式是對(duì)數(shù)據(jù)表設(shè)計(jì)合理的索引。索引提供了高效訪問數(shù)據(jù)的方法，并且加快查詢的速度， 因此索引對(duì)查詢的速度有著至關(guān)重要的影響。

使用索引可以快速地定位表中的某條記錄，從而提高數(shù)據(jù)庫查詢的速度，提高數(shù)據(jù)庫的性能。如果查詢時(shí)沒有使用索引，查詢語句就會(huì)掃描表中的所有記錄。在數(shù)據(jù)量大的情況下，這樣查詢的速度會(huì)很慢。

我們一般創(chuàng)建的索引類型都是B+Tree結(jié)構(gòu)，其實(shí)，用不用索引最終都是優(yōu)化器說了算。

那么MySQL優(yōu)化器是什么呢？

MySQL內(nèi)部優(yōu)化器是MySQL中很重要的一個(gè)部分，它主要用于在執(zhí)行查詢時(shí)獲取最合適的執(zhí)行計(jì)劃，以使得查詢能夠以最短的時(shí)間內(nèi)得到結(jié)果。

MySQL內(nèi)部優(yōu)化器的工作原理是在接收到一條查詢語句之后，它會(huì)根據(jù)一系列的算法和規(guī)oSi則來確定哪個(gè)執(zhí)行計(jì)劃是最優(yōu)的。

通常情況下，MySQL優(yōu)化器會(huì)依賴于表的統(tǒng)計(jì)信息和索引信息來進(jìn)行優(yōu)化決策。例如，在執(zhí)行select語句時(shí)，優(yōu)化器會(huì)嘗試使用索引來避免全表掃描。同時(shí)，優(yōu)化器還會(huì)對(duì)各種查詢操作的代價(jià)進(jìn)行估算，以便找到最優(yōu)的執(zhí)行計(jì)劃。

那么我們?nèi)绾稳ゲ榭催@條sql的一個(gè)執(zhí)行計(jì)劃呢？很簡單只需要在我們執(zhí)行的sql前面加上explain關(guān)鍵字即可。

-- 創(chuàng)建數(shù)據(jù)庫表格
CREATE TABLE `account` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `phone` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `pwd` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `province` varchar(80) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `city` varchar(60) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `status` int NOT NULL,
  `gmt_create` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- 執(zhí)行sql查看執(zhí)行計(jì)劃
explain SELECT * from account where id = '1'

EXPLAIN 是用于分析并優(yōu)化查詢語句性能的工具，執(zhí)行計(jì)劃會(huì)解析查詢語句并生成執(zhí)行計(jì)劃，包括訪問表和索引所需的策略、查詢優(yōu)化器的選擇以及每個(gè)階段的估計(jì)記錄數(shù)等信息，深入SQL語句在執(zhí)行過程中的各個(gè)細(xì)節(jié)。

OK，接下來我們來看一下各個(gè)字段的含義。

id：對(duì)于 SELECT 語句，每個(gè)查詢都會(huì)被分配一個(gè)唯一的ID。表示查詢的標(biāo)識(shí)符，數(shù)字越大越先執(zhí)行。

select_type：表示查詢類型或者子查詢類型，使用不同的 select_type 來幫助評(píng)估查詢性能，并確定可以采取哪些優(yōu)化方法。需要根據(jù)具體情況來進(jìn)行相應(yīng)的優(yōu)化，例如盡量減少子查詢的數(shù)量，避免使用不必要的 UNION 操作等等。

table：表示查詢涉及到哪些表，對(duì)于子查詢等復(fù)雜查詢可能涉及多張表。

partitions：表示查詢操作涉及到的分區(qū)表的分區(qū)情況。

type：表示 MySQL 在表中找到所需行的方式，常見的類型包括 ALL, index，range, ref, eq_ref, const, system, NULL。

possible_key：表示 MySQL 可以使用哪些索引來優(yōu)化查詢

key：表示 MySQL 實(shí)際使用的索引，如果沒有使用任何索引，則該值為 NULL

key_len：表示 MySQL 實(shí)際使用的索引的長度，該值與索引定義的長度有關(guān)

ref：表示 MySQL 使用哪個(gè)列或常量與索引列進(jìn)行比較。

rows：表示 MySQL 估計(jì)要掃描多少行才能找到所需記錄，是一個(gè)估算值而不是確切值。

filtered：查詢條件過濾的效率，百分比形式表示， Filtered 越高，表示查詢結(jié)果集中過濾數(shù)據(jù)所需要的開銷越小，查詢性能就越好。

Extra：該字段包括一些額外的查詢信息，包括使用何種排序方式、使用哪種 Join 操作等。

OK，介紹了這么多，下面我們就開始進(jìn)入正題，來說一說索引失效的場景都有哪些。

1.隱式轉(zhuǎn)換導(dǎo)致不走索引，索引失效

當(dāng)采用索引查詢時(shí)列的類型不一樣，就會(huì)導(dǎo)致索引失效。我們當(dāng)前account表中id是varchar類型，我們現(xiàn)在查詢用數(shù)字類型查，這會(huì)就會(huì)導(dǎo)致索引失效。

explain SELECT * from account where id = 1

改用字符串查詢

explain SELECT * from account where id = '1'

2.當(dāng)索引列配合不是索引列進(jìn)行or查詢時(shí)，索引失效

當(dāng)我們查詢時(shí)索引列配合不是索引列進(jìn)行查詢的時(shí)候，會(huì)導(dǎo)致索引失效，比如說id是索引，gmt_create不是索引，當(dāng)**id = ‘1’ or gmt_create = ‘2024-01-01’**時(shí)，這就會(huì)導(dǎo)致索引失效。

explain SELECT * from account where id='1' or  gmt_create = '2024-01-01'

要想讓其走索引查詢，可以給gmt_create加上索引，or兩邊字段都是索引字段才會(huì)走索引。

CREATE INDEX gmt_create on account(gmt_create)

這里也可以我們規(guī)定強(qiáng)制走哪一個(gè)索引，不過一般不建議，因?yàn)閟ql優(yōu)化器已經(jīng)幫我們計(jì)算好最優(yōu)的查詢方式。

explain SELECT * from account force index(PRIMARY) where id='1' or  gmt_create = '2024-01-01'

雖然強(qiáng)制地使用了索引，但是經(jīng)過分析，這次查詢還是沒有使用索引，所以強(qiáng)制使用索引并不一定是生效的。

3.業(yè)務(wù)表的數(shù)據(jù)量太少，索引失效

MySQL索引是為了加速查詢而存在的，如果數(shù)據(jù)量太小，MySQL查詢速度本來就很快，這時(shí)候使用索引反而會(huì)拖慢查詢速度。因此，當(dāng)數(shù)據(jù)量很小的時(shí)候，MySQL索引可能會(huì)失效。

explain SELECT * from account where id = '1'

4.當(dāng)索引字段采用函數(shù)查詢時(shí)，索引失效

當(dāng)索引字段采用函數(shù)查詢時(shí)，會(huì)導(dǎo)致索引失效，比如gmt_create本身是一個(gè)索引字段，我們采用YEAR函數(shù)進(jìn)行查詢，就會(huì)導(dǎo)致索引的失效。

explain SELECT * from account where YEAR(gmt_create)  =  '2023'

5.like查詢索引字段左邊模糊查詢，索引失效

當(dāng)索引列使用LIKE操作符時(shí)，左邊模糊查詢會(huì)導(dǎo)致索引失效。比如我們給province加上索引，我們用province like “%天津%” 或者 province like “%天津” 都會(huì)導(dǎo)致索引的失效，只有province like "天津%"索引才不會(huì)失效。

添加索引：CREATE INDEX province on account(province)

explain SELECT * from account where province like '%天津'

explain SELECT * from account where province like '天津%'

6.字段重復(fù)性高導(dǎo)致索引失效

比如有一些字段他的重復(fù)性的值確實(shí)特別的高，那么這種字段就不適合加索引。

explain SELECT * from account where province = '寶地區(qū)'

7.IS NULL操作時(shí)，索引失效

IS NULL不走索引，IS NOT NULL走索引，設(shè)計(jì)字段的時(shí)候，如果沒有要求必須為NULL，那最好給個(gè)默認(rèn)值空字符串。

explain SELECT id from account where province is  NULL

explain SELECT id from account where province is not NULL

還有一種情況，單鍵值的B樹索引列上存在null值，導(dǎo)致COUNT(*)不能走索引。

-- status狀態(tài)加上索引
CREATE INDEX status on account(status)

explain SELECT count(status) from account

我們來看一下status加上索引，沒有為空的數(shù)據(jù)時(shí)，執(zhí)行計(jì)劃是啥樣的。

8.聯(lián)合索引沒有遵循最左匹配原則，索引失效

如果使用了聯(lián)合索引，但查詢時(shí)未使用索引的第一列，索引也會(huì)失效。

原因：比如我們根據(jù)字段（t1，t2，t3）建立了聯(lián)合索引，則排序規(guī)則是先按t1字段進(jìn)行排序，t1字段相同再按t2字段排序，當(dāng)t1、t2字段都相同時(shí)再按t3字段進(jìn)行排序。如果我們的查詢條件中沒有使用到第一列，那么該索引也就沒有辦法使用。

--  創(chuàng)建聯(lián)合索引
CREATE INDEX idx_phone_provice_status on account(phone,province,status)

我們刪除之前加的province和status的單獨(dú)的索引。

explain select * from account where province = '寶地區(qū)' and status = 1

只要我們把聯(lián)合索引的第一列放在前面，就可以生效。

explain select * from account where phone = '12384374374' and province = '寶地區(qū)' and status = 1

explain select * from account where phone = '12384374374' and status = 1

9.不等于操作符（<>、!=）會(huì)導(dǎo)致索引失效

這種查詢語句無法使用索引，因?yàn)樾枰獟呙枵麄€(gè)表來查找不等于’value’的記錄。

explain select * from account where phone != '12384374374'

10.IN語句引起的索引失效

使用IN語句進(jìn)行查詢時(shí)，如果查詢的值列表比較大或者是一個(gè)子查詢，則會(huì)引起索引失效。

我們可以通過以下sql來模擬這種情況，這塊就不做真實(shí)的演示啦，大家在工作中遇到IN查詢的時(shí)候可以看一下執(zhí)行計(jì)劃，然后做出對(duì)應(yīng)的調(diào)整。

SELECT * FROM table_name WHERE column_name IN (SELECT column_name FROM another_table);

這樣的查詢語句會(huì)導(dǎo)致數(shù)據(jù)庫無法使用索引來查找匹配的記錄，因?yàn)樗饕荒懿檎覇蝹€(gè)值，而不能匹配多個(gè)值。

為了避免IN語句導(dǎo)致的索引失效，我們可以使用以下替代方案：

使用EXISTS語句來代替IN語句，例如：

SELECT * FROM table_name1 t1 WHERE EXISTS (SELECT * FROM table_name2 t2 WHERE t2.column_name = t1.column_name);

或者是使用JOIN來代替IN語句，例如：

SELECT * FROM table_name1 t1 JOIN table_name2 t2 ON(t1.column_name = t2.column_name);

11.數(shù)據(jù)庫與表還有表與表的編碼不兼容，索引失效

在sql中做表關(guān)聯(lián)時(shí)，需要注意兩邊字段的編碼要保持一致。

Ok，以上就是我們?cè)诠ぷ髦谐Ｒ姷囊恍┧饕У陌咐?/p>

接下來我們來說一下，索引的一些設(shè)計(jì)規(guī)則。

• 高頻次查詢且數(shù)據(jù)量大的表建立索引
• 經(jīng)常需要排序、分組和聯(lián)合操作的字段建立索引
• 短索引可以提升訪問的IO效率，對(duì)于BLOB、TEXT或很長的varchar列使用前綴索引
• 刪除無用索引，同列上創(chuàng)建多個(gè)索引，越多索引維護(hù)成本越高，優(yōu)化器在優(yōu)化查詢時(shí)也需要逐個(gè)考慮，會(huì)影響性能
• 根據(jù)業(yè)務(wù)需求，設(shè)計(jì)好聯(lián)合索引，業(yè)務(wù)使用的時(shí)候盡量用到聯(lián)合索引，避免回表查詢
• 盡量選擇區(qū)分度高的列作為索引，區(qū)分度越高性能越好，比如唯一索引
• 索引列不參與計(jì)算，帶函數(shù)的查詢不建議做為索引列
• 盡量擴(kuò)展利用現(xiàn)有索引，聯(lián)合索引的查詢效率比多個(gè)獨(dú)立索引高
• 盡量避免NULL，應(yīng)該指定列為NOT NULL，含有空值的列很難進(jìn)行查詢優(yōu)化，可以用0或一個(gè)空串代替NULL
• 唯一索引與普通索引
  • 唯一索引和普通索引在性能上沒有本質(zhì)的區(qū)別，但在數(shù)據(jù)的唯一性方面
  • 唯一索引在數(shù)據(jù)插入和更新時(shí)需要更多計(jì)算，因此略微慢一些。
• 聚簇索引與非聚簇索引
  • 聚簇索引在性能上優(yōu)于非聚簇索引，因?yàn)榫鄞厮饕菍?shù)據(jù)存儲(chǔ)在一起的
  • 這樣檢索數(shù)據(jù)時(shí)可以最大程度地減少磁盤 IO 操作。但如果經(jīng)常更新表中的數(shù)據(jù)，則聚簇索引的維護(hù)成本相對(duì)較高。
• 覆蓋索引與非覆蓋索引
  • 覆蓋索引可以直接從索引中獲取數(shù)據(jù)，無需回表查詢，因此執(zhí)行速度更快
  • 但是如果查詢需要取出的數(shù)據(jù)列不在索引中，則無法使用覆蓋索引，需要進(jìn)行回表查詢，效率較低。

好啦，至此本文就到這啦，記得三連?關(guān)注哦！

文章來源地址http://www.zghlxwxcb.cn/news/detail-540118.html

到了這里，關(guān)于【MySQL】SQL索引失效的幾種場景及優(yōu)化的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！