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

最近一直忙著處理原來老項目遺留的一些SQL優(yōu)化問題携冤,由于當初表的設計以及字段設計的問題,隨著業(yè)務的增長秩铆,出現(xiàn)了大量的慢SQL砚亭,導致MySQL的CPU資源飆升,基于此殴玛,給大家簡單分享下這些比較使用的易于學習和使用的經驗捅膘。

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

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

索引只是我們優(yōu)化業(yè)務的一種方式钢坦,千萬為了為了建索引而去建索引。

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

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

現(xiàn)在明白我為啥說設計字段的時候一定要保持類型的一致性了不前标,如果你不保證一致性,一個int一個varchar距潘,在進行多表聯(lián)合查詢(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

符合索引

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酌呆,你現(xiàn)在的查詢順序是sex、age搔耕、name隙袁,這肯定不走索引啊,你要是自己沒測試過弃榨,也有這種不成熟的想法菩收,那跟我一樣還是太年輕了,它其實跟順序是沒有任何關系的鲸睛,因為mysql的底層會幫我們做一個優(yōu)化娜饵,它會把你的SQL優(yōu)化為它認為一個效率最高的樣子進行執(zhí)行。所以千萬不要有這種誤解官辈。

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

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

image

關于符合索引導致索引失效的情況能說的目前就這兩種电湘,其實我覺得對于符合索引來說隔节,重要的是如何建立高效的索引,千萬不能說我用到那個字段我就去建立一個單獨的索引胡桨,不是就可以全局用了嘛官帘。這樣是可以,但是這樣并沒有符合索引高效昧谊,所以為了成為高級的搬磚工刽虹,我們還是要繼續(xù)學習,如何創(chuàng)建高效的索引呢诬。

更多精彩內容請關注微信公眾號:一個程序員的成長

?著作權歸作者所有,轉載或內容合作請聯(lián)系作者
  • 序言:七十年代末涌哲,一起剝皮案震驚了整個濱河市,隨后出現(xiàn)的幾起案子尚镰,更是在濱河造成了極大的恐慌阀圾,老刑警劉巖,帶你破解...
    沈念sama閱讀 222,807評論 6 518
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件狗唉,死亡現(xiàn)場離奇詭異初烘,居然都是意外死亡,警方通過查閱死者的電腦和手機分俯,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 95,284評論 3 399
  • 文/潘曉璐 我一進店門肾筐,熙熙樓的掌柜王于貴愁眉苦臉地迎上來,“玉大人缸剪,你說我怎么就攤上這事吗铐。” “怎么了杏节?”我有些...
    開封第一講書人閱讀 169,589評論 0 363
  • 文/不壞的土叔 我叫張陵唬渗,是天一觀的道長。 經常有香客問我奋渔,道長镊逝,這世上最難降的妖魔是什么? 我笑而不...
    開封第一講書人閱讀 60,188評論 1 300
  • 正文 為了忘掉前任蹋半,我火速辦了婚禮,結果婚禮上充坑,老公的妹妹穿的比我還像新娘减江。我一直安慰自己染突,他們只是感情好,可當我...
    茶點故事閱讀 69,185評論 6 398
  • 文/花漫 我一把揭開白布辈灼。 她就那樣靜靜地躺著份企,像睡著了一般。 火紅的嫁衣襯著肌膚如雪巡莹。 梳的紋絲不亂的頭發(fā)上司志,一...
    開封第一講書人閱讀 52,785評論 1 314
  • 那天,我揣著相機與錄音降宅,去河邊找鬼骂远。 笑死,一個胖子當著我的面吹牛腰根,可吹牛的內容都是我干的激才。 我是一名探鬼主播,決...
    沈念sama閱讀 41,220評論 3 423
  • 文/蒼蘭香墨 我猛地睜開眼额嘿,長吁一口氣:“原來是場噩夢啊……” “哼瘸恼!你這毒婦竟也來了?” 一聲冷哼從身側響起册养,我...
    開封第一講書人閱讀 40,167評論 0 277
  • 序言:老撾萬榮一對情侶失蹤东帅,失蹤者是張志新(化名)和其女友劉穎,沒想到半個月后球拦,有當地人在樹林里發(fā)現(xiàn)了一具尸體靠闭,經...
    沈念sama閱讀 46,698評論 1 320
  • 正文 獨居荒郊野嶺守林人離奇死亡,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內容為張勛視角 年9月15日...
    茶點故事閱讀 38,767評論 3 343
  • 正文 我和宋清朗相戀三年坎炼,在試婚紗的時候發(fā)現(xiàn)自己被綠了阎毅。 大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點故事閱讀 40,912評論 1 353
  • 序言:一個原本活蹦亂跳的男人離奇死亡点弯,死狀恐怖,靈堂內的尸體忽然破棺而出矿咕,到底是詐尸還是另有隱情抢肛,我是刑警寧澤,帶...
    沈念sama閱讀 36,572評論 5 351
  • 正文 年R本政府宣布碳柱,位于F島的核電站捡絮,受9級特大地震影響,放射性物質發(fā)生泄漏莲镣。R本人自食惡果不足惜福稳,卻給世界環(huán)境...
    茶點故事閱讀 42,254評論 3 336
  • 文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望瑞侮。 院中可真熱鬧的圆,春花似錦鼓拧、人聲如沸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 32,746評論 0 25
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽。三九已至梅掠,卻和暖如春酌住,著一層夾襖步出監(jiān)牢的瞬間,已是汗流浹背阎抒。 一陣腳步聲響...
    開封第一講書人閱讀 33,859評論 1 274
  • 我被黑心中介騙來泰國打工酪我, 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留,地道東北人且叁。 一個月前我還...
    沈念sama閱讀 49,359評論 3 379
  • 正文 我出身青樓都哭,卻偏偏與公主長得像,于是被迫代替她去往敵國和親谴古。 傳聞我的和親對象是個殘疾皇子质涛,可洞房花燭夜當晚...
    茶點故事閱讀 45,922評論 2 361