導致MySQL索引失效的幾種常見寫法

發布時間:2021-02-18 14:04:46 作者:本站編輯 來源:本站原創 閱讀次數:

Mysql.jpg

最近一直忙著處理原來老項目遺留的一些SQL優化問題,由于當初表的設計以及字段設計的問題,隨著業務的增長,出現了大量的慢SQL,導致MySQL的CPU資源飆升,基于此,給大家簡單分享下這些比較使用的易于學習和使用的經驗。

這次的話簡單說下如何防止你的索引失效。

再說之前我先根據我最近的經驗說下我對索引的看法,我覺得并不是所以的表都需要去建立索引,對于一些業務數據,可能量比較大了,查詢數據已經有了一點壓力,那么最簡單、快速的辦法就是建立合適的索引,但是有些業務可能表里就沒多少數據,或者表的使用頻率非常不高的情況下是沒必要必須要去做索引的。就像我們有些表,2年了可能就10來條數據,有索引和沒索引性能方面差不多多少。

索引只是我們優化業務的一種方式,千萬為了為了建索引而去建索引。

下面是我此次測試使用的一張表結構以及一些測試數據

CREATE TABLE `user` (  `id` int(5) unsigned NOT NULL AUTO_INCREMENT,  `create_time` datetime NOT NULL,  `name` varchar(5) NOT NULL,  `age` tinyint(2) unsigned zerofill NOT NULL,  `sex` char(1) NOT NULL,  `mobile` char(12) NOT NULL DEFAULT '',  `address` char(120) DEFAULT NULL,  `height` varchar(10) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `idx_createtime` (`create_time`) USING BTREE,  KEY `idx_name_age_sex` (`name`,`sex`,`age`) USING BTREE,  KEY `idx_ height` (`height`) USING BTREE,  KEY `idx_address` (`address`) USING BTREE,  KEY `idx_age` (`age`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=261 DEFAULT CHARSET=utf8;


INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (1, '2019-09-02 10:17:47', '冰峰', 22, '男', '1', '陜西省咸陽市彬縣', '175');INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (2, '2020-09-02 10:17:47', '松子', 13, '女', '1', NULL, '180');INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (3, '2020-09-02 10:17:48', '蠶豆', 20, '女', '1', NULL, '180');INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (4, '2020-09-02 10:17:47', '冰峰', 20, '男', '17765010977', '陜西省西安市', '155');INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (255, '2020-09-02 10:17:47', '竹筍', 22, '男', '我測試下可以儲存幾個中文', NULL, '180');INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (256, '2020-09-03 10:17:47', '冰峰', 21, '女', '', NULL, '167');INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (257, '2020-09-02 10:17:47', '小紅', 20, '', '', NULL, '180');INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (258, '2020-09-02 10:17:47', '小鵬', 20, '', '', NULL, '188');INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (259, '2020-09-02 10:17:47', '張三', 20, '', '', NULL, '180');INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (260, '2020-09-02 10:17:47', '李四', 22, '', '', NULL, '165');


單個索引


1、使用!= 或者 <> 導致索引失效
SELECT * FROM `user` WHERE `name` != '冰峰';

我們給name字段建立了索引,但是如果!= 或者 <> 這種都會導致索引失效,進行全表掃描,所以如果數據量大的話,謹慎使用

image

可以通過分析SQL看到,type類型是ALL,掃描了10行數據,進行了全表掃描。<>也是同樣的結果。


2、類型不一致導致的索引失效

在說這個之前,一定要說一下設計表字段的時候,千萬、一定、必須要保持字段類型的一致性,啥意思?比如user表的id是int自增,到了用戶的賬戶表user_id這個字段,一定、必須也是int類型,千萬不要寫成varchar、char什么的騷操作。

SELECT * FROM `user` WHERE height= 175;

這個SQL諸位一定要看清楚,height表字段類型是varchar,但是我查詢的時候使用了數字類型,因為這個中間存在一個隱式的類型轉換,所以就會導致索引失效,進行全表掃描。

image

現在明白我為啥說設計字段的時候一定要保持類型的一致性了不,如果你不保證一致性,一個int一個varchar,在進行多表聯合查詢(eg: 1 = '1')必然走不了索引。

遇到這樣的表,里面有幾千萬數據,改又不能改,那種痛可能你們暫時還體會。

少年們,切記,切記。


3、函數導致的索引失效
SELECT * FROM `user` WHERE DATE(create_time) = '2020-09-03';

如果你的索引字段使用了索引,對不起,他是真的不走索引的。

image

4、運算符導致的索引失效
SELECT * FROM `user` WHERE age - 1 = 20;

如果你對列進行了(+,-,*,/,!), 那么都將不會走索引。

image

5、OR引起的索引失效
SELECT * FROM `user` WHERE `name` = '張三' OR height = '175';

OR導致索引是在特定情況下的,并不是所有的OR都是使索引失效,如果OR連接的是同一個字段,那么索引不會失效,反之索引失效。

image

6、模糊搜索導致的索引失效
SELECT * FROM `user` WHERE `name` LIKE '%冰';

這個我相信大家都明白,模糊搜索如果你前綴也進行模糊搜索,那么不會走索引。

image

7、NOT IN、NOT EXISTS導致索引失效
SELECT s.* FROM `user` s WHERE NOT EXISTS (SELECT * FROM `user` u WHERE u.name = s.`name` AND u.`name` = '冰峰')
SELECT * FROM `user` WHERE `name` NOT IN ('冰峰');

這兩種用法,也將使索引失效。但是NOT IN 還是走索引的,千萬不要誤解為 IN 全部是不走索引的。我之前就有誤解(丟人了...)。

image

8、IS NULL不走索引,IS NOT NULL走索引
SELECT * FROM `user` WHERE address IS NULL

image

不走索引。

SELECT * FROM `user` WHERE address IS NOT NULL;

image

走索引。

根據這個情況,建議大家這設計字段的時候,如果沒有必要的要求必須為NULL,那么最好給個默認值空字符串,這可以解決很多后續的麻煩(有深刻的體驗<體驗=教訓>)。


復合索引


1、最左匹配原則
EXPLAIN SELECT * FROM `user` WHERE sex = '男';
EXPLAIN SELECT * FROM `user` WHERE name = '冰峰' AND sex = '男';

測試之前,刪除其他的單列索引。

啥叫最左匹配原則,就是對于符合索引來說,它的一個索引的順序是從左往右依次進行比較的,像第二個查詢語句,name走索引,接下來回去找age,結果條件中沒有age那么后面的sex也將不走索引。

image

注意:

SELECT * FROM `user` WHERE sex = '男' AND age = 22 AND `name` = '冰峰';

可能有些搬磚工可能跟我最開始有個誤解,我們的索引順序明明是name、sex、age,你現在的查詢順序是sex、age、name,這肯定不走索引啊,你要是自己沒測試過,也有這種不成熟的想法,那跟我一樣還是太年輕了,它其實跟順序是沒有任何關系的,因為mysql的底層會幫我們做一個優化,它會把你的SQL優化為它認為一個效率最高的樣子進行執行。所以千萬不要有這種誤解。


2、如果使用了!=會導致后面的索引全部失效
SELECT * FROM `user` WHERE sex = '男' AND `name` != '冰峰' AND age = 22;

我們在name字段使用了 != ,由于name字段是最左邊的一個字段,根據最左匹配原則,如果name不走索引,后面的字段也將不走索引。

image

關于符合索引導致索引失效的情況能說的目前就這兩種,其實我覺得對于符合索引來說,重要的是如何建立高效的索引,千萬不能說我用到那個字段我就去建立一個單獨的索引,不是就可以全局用了嘛。這樣是可以,但是這樣并沒有符合索引高效,所以為了成為高級的搬磚工,我們還是要繼續學習,如何創建高效的索引。


我們的價值觀

對得起良心、對得起客戶

聯系電話:15598115999

QQ  咨詢:6645166

電子郵箱:feng@haokuai.cn

公司地址:內蒙古呼和浩特市金橋開發區阿拉坦大街北側商業樓1號B段

为什么总是干到一半就没水了