欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

mysql索引失效的問題

 更新時間：2023年08月30日 10:52:56   作者：果子貍呀  
這篇文章主要介紹了mysql索引失效的問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教<BR>

一、查詢條件包含or，可能導致索引失效

新建一個user表，它有一個普通索引userId，

結構如下：

CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userId` int(11) NOT NULL,
`age` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_userId` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

執(zhí)行一條查詢sql，它是會走索引的，

如下圖所示：

把or條件+沒有索引的age加上，并不會走索引，

如圖：

分析&結論：

  • 對于or+沒有索引的age這種情況，假設它走了userId的索引，但是走到age查詢條件時，它還得全表掃描，也就是需要三步過程： 全表掃描+索引掃描+合并
  • 如果它一開始就走全表掃描，直接一遍掃描就完事。
  • mysql是有優(yōu)化器的，處于效率與成本，遇到or條件，索引可能失效，看起來也合情合理。

注意：如果or條件的列都加了索引，索引可能會走的，大家可以自己試一試。

二、如何字段類型是字符串

where時一定用引號括起來，否則索引失效

假設demo表結構如下：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(32) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userId` (`userId`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

userId為字符串類型，是B+樹的普通索引，如果查詢條件傳了一個數字過去，它是不走索引的，

如圖所示：

如果給數字加上'',也就是傳一個字符串呢，當然是走索引，

如下圖：

分析與結論：

為什么第一條語句未加單引號就不走索引了呢？

這是因為不加單引號時，是字符串跟數字的比較，它們類型不匹配，MySQL會做隱式的類型轉換，把它們轉換為浮點數再做比較。

三、like通配符可能導致索引失效

并不是用了like通配符，索引一定失效，而是like查詢是以%開頭，才會導致索引失效。

表結構：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(32) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userId` (`userId`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

like查詢以%開頭，索引失效，

如圖：

把%放后面，發(fā)現索引還是正常走的，

如下：

把%加回來，改為只查索引的字段（覆蓋索引），發(fā)現還是走索引，驚不驚喜，意不意外

結論：

like查詢以%開頭，會導致索引失效?？梢杂袃煞N方式優(yōu)化：

  • 使用覆蓋索引
  • 把%放后面

附: 索引包含所有滿足查詢需要的數據的索引，稱為覆蓋索引(Covering Index)。

四、聯合索引

查詢時的條件列不是聯合索引中的第一個列，索引失效。

表結構：（有一個聯合索引 idx_userid_age ， userId 在前， age 在后）

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userid_age` (`userId`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

在聯合索引中，查詢條件滿足最左匹配原則時，索引是正常生效的。

請看demo：

如果條件列不是聯合索引中的第一個列，索引失效，

如下：

分析與結論：

  • 當我們創(chuàng)建一個聯合索引的時候，如(k1,k2,k3)，相當于創(chuàng)建了（k1）、(k1,k2)和(k1,k2,k3)三個索引，這就是最左匹配原則。
  • 聯合索引不滿足最左原則，索引一般會失效，但是這個還跟Mysql優(yōu)化器有關的。

五、在索引列上使用mysql的內置函數

索引失效。

表結構：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(32) NOT NULL,
  `loginTime` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userId` (`userId`) USING BTREE,
  KEY `idx_login_time` (`loginTime`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

雖然loginTime加了索引，但是因為使用了mysql的內置函數Date_ADD()，索引直接GG，

如圖：

六、對索引列運算（如，+、-、*、/）

索引失效。

表結構：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(32) NOT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

雖然age加了索引，但是因為它進行運算，索引直接迷路了。。。

如圖：

七、索引字段上使用（！= 或者 < >，not in）

可能會導致索引失效。

表結構：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

雖然age加了索引，但是使用了！= 或者 < >，not in這些時，索引如同虛設。

如下：

八、索引字段上使用is null， is not null

可能導致索引失效。

表結構：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `card` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE,
  KEY `idx_card` (`card`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

單個name字段加上索引，并查詢name為非空的語句，其實會走索引的，

如下：

單個card字段加上索引，并查詢name為非空的語句，其實會走索引的，

如下：

但是它兩用or連接起來，索引就失效了，

如下：

九、左連接查詢或者右連接查詢查詢關聯的字段編碼格式不一樣

可能導致索引失效。

新建兩個表，一個user，一個user_job

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
CREATE TABLE user_job (
id int(11) NOT NULL,
userId int(11) NOT NULL,
job varchar(255) DEFAULT NULL,
name varchar(255) DEFAULT NULL,
PRIMARY KEY (id),
KEY idx_name (name) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

user 表的name字段編碼是utf8mb4，而user_job表的name字段編碼為utf8。

執(zhí)行左外連接查詢,user_job表還是走全表掃描，

如下：

如果把它們改為name字段編碼一致，還是會走索引。

十、mysql估計使用全表掃描要比使用索引快

則不使用索引。

當表的索引被查詢，會使用最好的索引，除非優(yōu)化器使用全表掃描更有效。優(yōu)化器優(yōu)化成全表掃描取決與使用最好索引查出來的數據是否超過表的30%的數據。

不要給'性別'等增加索引。如果某個數據列里包含了均是"0/1"或“Y/N”等值，即包含著許多重復的值，就算為它建立了索引，索引效果不會太好，還可能導致全表掃描。

Mysql出于效率與成本考慮，估算全表掃描與使用索引，哪個執(zhí)行快。這跟它的優(yōu)化器有關，來看一下它的邏輯架構圖吧（圖片來源網上）

總結

總結了索引失效的十大雜癥，在這里來個首尾呼應吧，分析一下我們生產的那條慢sql。

模擬的表結構與肇事sql如下：

CREATE TABLE `user_session` (
  `user_id` varchar(32) CHARACTER SET utf8mb4 NOT NULL,
  `device_id` varchar(64) NOT NULL,
  `status` varchar(2) NOT NULL,
  `create_time` datetime NOT NULL,
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`,`device_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
explain 
update user_session set status =1
where  (`user_id` = '1' and `device_id`!='2')
or (`user_id` != '1' and `device_id`='2')

分析：

  • 執(zhí)行的sql，使用了 or 條件，因為組合主鍵( user_id , device_id )，看起來像是每一列都加了索引，索引會生效。
  • 但是出現 != ,可能導致索引失效。也就是 or + != 兩大綜合癥，導致了慢更新sql。

解決方案：

那么，怎么解決呢？我們是把 or 條件拆掉，分成兩條執(zhí)行。同時給 device_id 加一個普通索引。

最后，總結了索引失效的十大雜癥，希望大家在工作學習中，參考這十大雜癥，多點結合執(zhí)行計劃 expain 和場景，具體分析 ，而不是按部就班，墨守成規(guī)，認定哪個情景一定索引失效。

以上為個人經驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

相關文章

  • 關于mysql init_connect的幾個要點總結

    關于mysql init_connect的幾個要點總結

    下面小編就為大家?guī)硪黄P于mysql init_connect的幾個要點總結。小編覺得挺不錯的，現在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧
    2017-03-03
  • 詳解MySQL中的數據類型和schema優(yōu)化

    詳解MySQL中的數據類型和schema優(yōu)化

    這篇文章主要介紹了MySQL中的數據類型和schema優(yōu)化的相關資料，幫助大家更好的理解和學習MySQL的知識，感興趣的朋友可以了解下
    2020-10-10
  • mysql 8.0.12 安裝配置圖文教程

    mysql 8.0.12 安裝配置圖文教程

    這篇文章主要為大家詳細介紹了mysql 8.0.12 安裝配置圖文教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下
    2018-08-08
  • mysql表添加索引的幾種實現方法

    mysql表添加索引的幾種實現方法

    本文介紹了在MySQL中使用ALTER?TABLE語句為表添加索引的幾種常見方式,包括普通索引、唯一索引、主鍵索引和全文索引,感興趣的可以了解一下
    2024-11-11
  • 淺談Mysql時間的存儲?datetime還是時間戳timestamp

    淺談Mysql時間的存儲?datetime還是時間戳timestamp

    本文主要介紹了淺談Mysql時間的存儲?datetime還是時間戳timestamp，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧
    2022-07-07
  • 淺談MySQL觸發(fā)器的原理以及使用

    淺談MySQL觸發(fā)器的原理以及使用

    這篇文章主要介紹了淺談MySQL觸發(fā)器的原理以及使用,觸發(fā)器的執(zhí)行不需要使用 CALL 語句來調用，也不需要手工啟動，只要一個預定義的事件發(fā)生就會被 MySQL自動調用,需要的朋友可以參考下
    2023-05-05
  • mysql 的load data infile

    mysql 的load data infile

    前些日子在開發(fā)一個輿情監(jiān)測系統(tǒng)，需要在一個操作過程中往數據表里插入大量的數據，為了改變以往生硬地逐條數據插入的笨辦法，也為了提高執(zhí)行效率，決定用load data infile來執(zhí)行數據插入。
    2009-05-05
  • MySQL中order by的使用詳情

    MySQL中order by的使用詳情

    在使用select語句時可以結合order by對查詢的數據進行排序。如果不使用order by默認情況下MySQL返回的數據集,與它在底層表中的順序相同,可能與你添加數據到表中的順序一致,也可能不一致，如果希望得到的數據有順序,就該明確排序方式,下面詳細內容需要的朋友可以參考一下
    2021-11-11
  • MySQL中慢SQL優(yōu)化方法小結

    MySQL中慢SQL優(yōu)化方法小結

    這篇文章主要為大家詳細介紹了MySQL中慢SQL優(yōu)化方法的相關知識,文中的示例代碼講解詳細,具有一定的借鑒價值,感興趣的小伙伴可以參考一下
    2024-04-04
  • 20分鐘MySQL基礎入門

    20分鐘MySQL基礎入門

    這篇文章主要為大家分享了20分鐘MySQL基礎入門教程，快速掌握MySQL基礎知識，真正了解MySQL，具有一定的參考價值，感興趣的小伙伴們可以參考一下
    2016-11-11

最新評論