前言
最近一直忙著處理原來(lái)老項(xiàng)目遺留的一些SQL優(yōu)化問(wèn)題，由于當(dāng)初表的設(shè)計(jì)以及字段設(shè)計(jì)的問(wèn)題，隨著業(yè)務(wù)的增長(zhǎng)，出現(xiàn)了大量的慢SQL，導(dǎo)致MySQL的CPU資源飆升，基于此，給大家簡(jiǎn)單分享下這些比較使用的易于學(xué)習(xí)和使用的經(jīng)驗(yàn)。
這次的話簡(jiǎn)單說(shuō)下如何防止你的索引失效。
再說(shuō)之前我先根據(jù)我最近的經(jīng)驗(yàn)說(shuō)下我對(duì)索引的看法，我覺(jué)得并不是所以的表都需要去建立索引，對(duì)于一些業(yè)務(wù)數(shù)據(jù)，可能量比較大了，查詢數(shù)據(jù)已經(jīng)有了一點(diǎn)壓力，那么最簡(jiǎn)單、快速的辦法就是建立合適的索引，但是有些業(yè)務(wù)可能表里就沒(méi)多少數(shù)據(jù)，或者表的使用頻率非常不高的情況下是沒(méi)必要必須要去做索引的。就像我們有些表，2年了可能就10來(lái)?xiàng)l數(shù)據(jù)，有索引和沒(méi)索引性能方面差不多多少。
索引只是我們優(yōu)化業(yè)務(wù)的一種方式，千萬(wàn)為了為了建索引而去建索引。
下面是我此次測(cè)試使用的一張表結(jié)構(gòu)以及一些測(cè)試數(shù)據(jù)
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', '陜西省咸陽(yáng)市彬縣', '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, '男', '我測(cè)試下可以儲(chǔ)存幾個(gè)中文', 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');
單個(gè)索引
1、使用!= 或者 > 導(dǎo)致索引失效
SELECT * FROM `user` WHERE `name` != '冰峰';
我們給name字段建立了索引，但是如果!= 或者 > 這種都會(huì)導(dǎo)致索引失效，進(jìn)行全表掃描，所以如果數(shù)據(jù)量大的話，謹(jǐn)慎使用
可以通過(guò)分析SQL看到，type類型是ALL，掃描了10行數(shù)據(jù)，進(jìn)行了全表掃描。>也是同樣的結(jié)果。
2、類型不一致導(dǎo)致的索引失效
在說(shuō)這個(gè)之前，一定要說(shuō)一下設(shè)計(jì)表字段的時(shí)候，千萬(wàn)、一定、必須要保持字段類型的一致性，啥意思？比如user表的id是int自增，到了用戶的賬戶表user_id這個(gè)字段，一定、必須也是int類型，千萬(wàn)不要寫(xiě)成varchar、char什么的騷操作。
SELECT * FROM `user` WHERE height= 175;
這個(gè)SQL諸位一定要看清楚，height表字段類型是varchar，但是我查詢的時(shí)候使用了數(shù)字類型，因?yàn)檫@個(gè)中間存在一個(gè)隱式的類型轉(zhuǎn)換，所以就會(huì)導(dǎo)致索引失效，進(jìn)行全表掃描。
現(xiàn)在明白我為啥說(shuō)設(shè)計(jì)字段的時(shí)候一定要保持類型的一致性了不，如果你不保證一致性，一個(gè)int一個(gè)varchar，在進(jìn)行多表聯(lián)合查詢（eg: 1 = '1'）必然走不了索引。
遇到這樣的表，里面有幾千萬(wàn)數(shù)據(jù)，改又不能改，那種痛可能你們暫時(shí)還體會(huì)。
少年們，切記，切記。
3、函數(shù)導(dǎo)致的索引失效
SELECT * FROM `user` WHERE DATE(create_time) = '2020-09-03';
如果你的索引字段使用了索引，對(duì)不起，他是真的不走索引的。
4、運(yùn)算符導(dǎo)致的索引失效
SELECT * FROM `user` WHERE age - 1 = 20;
如果你對(duì)列進(jìn)行了（+，-，*，/，!）, 那么都將不會(huì)走索引。
5、OR引起的索引失效
SELECT * FROM `user` WHERE `name` = '張三' OR height = '175';
OR導(dǎo)致索引是在特定情況下的，并不是所有的OR都是使索引失效，如果OR連接的是同一個(gè)字段，那么索引不會(huì)失效，反之索引失效。
6、模糊搜索導(dǎo)致的索引失效
SELECT * FROM `user` WHERE `name` LIKE '%冰';
這個(gè)我相信大家都明白，模糊搜索如果你前綴也進(jìn)行模糊搜索，那么不會(huì)走索引。
7、NOT IN、NOT EXISTS導(dǎo)致索引失效
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 還是走索引的，千萬(wàn)不要誤解為 IN 全部是不走索引的。我之前就有誤解（丟人了...）。
8、IS NULL不走索引，IS NOT NULL走索引
SELECT * FROM `user` WHERE address IS NULL
不走索引。
SELECT * FROM `user` WHERE address IS NOT NULL;
走索引。
根據(jù)這個(gè)情況，建議大家這設(shè)計(jì)字段的時(shí)候，如果沒(méi)有必要的要求必須為NULL，那么最好給個(gè)默認(rèn)值空字符串，這可以解決很多后續(xù)的麻煩（有深刻的體驗(yàn)體驗(yàn)=教訓(xùn)>）。
符合索引
1、最左匹配原則
EXPLAIN SELECT * FROM `user` WHERE sex = '男';
EXPLAIN SELECT * FROM `user` WHERE name = '冰峰' AND sex = '男';
測(cè)試之前，刪除其他的單列索引。
啥叫最左匹配原則，就是對(duì)于符合索引來(lái)說(shuō)，它的一個(gè)索引的順序是從左往右依次進(jìn)行比較的，像第二個(gè)查詢語(yǔ)句，name走索引，接下來(lái)回去找age，結(jié)果條件中沒(méi)有age那么后面的sex也將不走索引。
注意：
SELECT * FROM `user` WHERE sex = '男' AND age = 22 AND `name` = '冰峰';
可能有些搬磚工可能跟我最開(kāi)始有個(gè)誤解，我們的索引順序明明是name、sex、age，你現(xiàn)在的查詢順序是sex、age、name，這肯定不走索引啊，你要是自己沒(méi)測(cè)試過(guò)，也有這種不成熟的想法，那跟我一樣還是太年輕了，它其實(shí)跟順序是沒(méi)有任何關(guān)系的，因?yàn)閙ysql的底層會(huì)幫我們做一個(gè)優(yōu)化，它會(huì)把你的SQL優(yōu)化為它認(rèn)為一個(gè)效率最高的樣子進(jìn)行執(zhí)行。所以千萬(wàn)不要有這種誤解。
2、如果使用了!=會(huì)導(dǎo)致后面的索引全部失效
SELECT * FROM `user` WHERE sex = '男' AND `name` != '冰峰' AND age = 22;
我們?cè)趎ame字段使用了 != ，由于name字段是最左邊的一個(gè)字段，根據(jù)最左匹配原則，如果name不走索引，后面的字段也將不走索引。
關(guān)于符合索引導(dǎo)致索引失效的情況能說(shuō)的目前就這兩種，其實(shí)我覺(jué)得對(duì)于符合索引來(lái)說(shuō)，重要的是如何建立高效的索引，千萬(wàn)不能說(shuō)我用到那個(gè)字段我就去建立一個(gè)單獨(dú)的索引，不是就可以全局用了嘛。這樣是可以，但是這樣并沒(méi)有符合索引高效，所以為了成為高級(jí)的搬磚工，我們還是要繼續(xù)學(xué)習(xí)，如何創(chuàng)建高效的索引。
總結(jié)
到此這篇關(guān)于導(dǎo)致MySQL索引失效的一些常見(jiàn)寫(xiě)法的文章就介紹到這了,更多相關(guān)MySQL索引失效的常見(jiàn)寫(xiě)法內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:- MySQL隱式類型轉(zhuǎn)換導(dǎo)致索引失效的解決
- 解決mysql模糊查詢索引失效問(wèn)題的幾種方法
- MySQL索引失效的典型案例
- mysql索引失效的幾種情況分析
- Mysql 5.6 "隱式轉(zhuǎn)換"導(dǎo)致的索引失效和數(shù)據(jù)不準(zhǔn)確的問(wèn)題
- MySQL索引失效的幾種情況詳析
- MySQL索引失效的幾種情況匯總
- mysql回表致索引失效案例講解
