作者|bingfeng
来源 |一个程序员的成长(ID:xiaozaibuluo)
头图 | CSDN下载自视觉中国
最近一直忙着处理原来老项目遗留的一些 SQL 优化问题,由于当初表的设计以及字段设计的问题,随着业务的增长,出现了大量的慢 SQL ,导致 MySQL 的 CPU 资源飙升,基于此,给大家简单分享下这些比较使用的易于学习和使用的经验。
这次的话简单说下如何防止你的索引失效。
再说之前我先根据我最近的经验说下我对索引的看法,我觉得并不是所以的表都需要去建立索引,对于一些业务数据,可能量比较大了,查询数据已经有了一点压力,那么最简单、快速的办法就是建立合适的索引,但是有些业务可能表里就没多少数据,或者表的使用频率非常不高的情况下是没必要必须要去做索引的。就像我们有些表,2年了可能就10来条数据,有索引和没索引性能方面差不多多少。
索引只是我们优化业务的一种方式,千万为了为了建索引而去建索引。
下面是我此次测试使用的一张表结构以及一些测试数据
CREATETABLE`user`(`id`int(5)unsignedNOTNULLAUTO_INCREMENT,`create_time`datetimeNOTNULL,`name`varchar(5)NOTNULL,`age`tinyint(2)unsignedzerofillNOTNULL,`sex`char(1)NOTNULL,`mobile`char(12)NOTNULLDEFAULT'',`address`char(120)DEFAULTNULL,`height`varchar(10)DEFAULTNULL,PRIMARYKEY(`id`),KEY`idx_createtime`(`create_time`)USINGBTREE,KEY`idx_name_age_sex`(`name`,`sex`,`age`)USINGBTREE,KEY`idx_height`(`height`)USINGBTREE,KEY`idx_address`(`address`)USINGBTREE,KEY`idx_age`(`age`)USINGBTREE)ENGINE=InnoDBAUTO_INCREMENT=261DEFAULTCHARSET=utf8;
INSERTINTO`bingfeng`.`user`(`id`,`create_time`,`name`,`age`,`sex`,`mobile`,`address`,`height`)VALUES(1,'-09-0210:17:47','冰峰',22,'男','1','陕西省咸阳市彬县','175');INSERTINTO`bingfeng`.`user`(`id`,`create_time`,`name`,`age`,`sex`,`mobile`,`address`,`height`)VALUES(2,'-09-0210:17:47','松子',13,'女','1',NULL,'180');INSERTINTO`bingfeng`.`user`(`id`,`create_time`,`name`,`age`,`sex`,`mobile`,`address`,`height`)VALUES(3,'-09-0210:17:48','蚕豆',20,'女','1',NULL,'180');INSERTINTO`bingfeng`.`user`(`id`,`create_time`,`name`,`age`,`sex`,`mobile`,`address`,`height`)VALUES(4,'-09-0210:17:47','冰峰',20,'男','17765010977','陕西省西安市','155');INSERTINTO`bingfeng`.`user`(`id`,`create_time`,`name`,`age`,`sex`,`mobile`,`address`,`height`)VALUES(255,'-09-0210:17:47','竹笋',22,'男','我测试下可以储存几个中文',NULL,'180');INSERTINTO`bingfeng`.`user`(`id`,`create_time`,`name`,`age`,`sex`,`mobile`,`address`,`height`)VALUES(256,'-09-0310:17:47','冰峰',21,'女','',NULL,'167');INSERTINTO`bingfeng`.`user`(`id`,`create_time`,`name`,`age`,`sex`,`mobile`,`address`,`height`)VALUES(257,'-09-0210:17:47','小红',20,'','',NULL,'180');INSERTINTO`bingfeng`.`user`(`id`,`create_time`,`name`,`age`,`sex`,`mobile`,`address`,`height`)VALUES(258,'-09-0210:17:47','小鹏',20,'','',NULL,'188');INSERTINTO`bingfeng`.`user`(`id`,`create_time`,`name`,`age`,`sex`,`mobile`,`address`,`height`)VALUES(259,'-09-0210:17:47','张三',20,'','',NULL,'180');INSERTINTO`bingfeng`.`user`(`id`,`create_time`,`name`,`age`,`sex`,`mobile`,`address`,`height`)VALUES(260,'-09-0210:17:47','李四',22,'','',NULL,'165');
单个索引
1、使用!= 或者 <> 导致索引失效
SELECT*FROM`user`WHERE`name`!='冰峰';
我们给name字段建立了索引,但是如果!= 或者 <> 这种都会导致索引失效,进行全表扫描,所以如果数据量大的话,谨慎使用
可以通过分析 SQL 看到,type 类型是 ALL,扫描了10行数据,进行了全表扫描。<>也是同样的结果。
2、类型不一致导致的索引失效
在说这个之前,一定要说一下设计表字段的时候,千万、一定、必须要保持字段类型的一致性,啥意思?比如 user 表的 id 是 int 自增,到了用户的账户表 user_id 这个字段,一定、必须也是 int 类型,千万不要写成 varchar 、char 什么的骚操作。
SELECT*FROM`user`WHEREheight=175;
这个 SQL 诸位一定要看清楚,height 表字段类型是 varchar,但是我查询的时候使用了数字类型,因为这个中间存在一个隐式的类型转换,所以就会导致索引失效,进行全表扫描。
现在明白我为啥说设计字段的时候一定要保持类型的一致性了不,如果你不保证一致性,一个 int 一个 varchar ,在进行多表联合查询( eg : 1 = '1')必然走不了索引。
遇到这样的表,里面有几千万数据,改又不能改,那种痛可能你们暂时还体会。
少年们,切记,切记。
3、函数导致的索引失效
SELECT*FROM`user`WHEREDATE(create_time)='-09-03';
如果你的索引字段使用了索引,对不起,他是真的不走索引的。
4、运算符导致的索引失效
SELECT*FROM`user`WHEREage-1=20;
如果你对列进行了(+,-,*,/,!), 那么都将不会走索引。
5、OR引起的索引失效
SELECT*FROM`user`WHERE`name`='张三'ORheight='175';
OR 导致索引是在特定情况下的,并不是所有的 OR 都是使索引失效,如果 OR 连接的是同一个字段,那么索引不会失效,反之索引失效。
6、模糊搜索导致的索引失效
SELECT*FROM`user`WHERE`name`LIKE'%冰';
这个我相信大家都明白,模糊搜索如果你前缀也进行模糊搜索,那么不会走索引。
7、NOT IN、NOT EXISTS导致索引失效
SELECTs.*FROM`user`sWHERENOTEXISTS(SELECT*FROM`user`uWHEREu.name=s.`name`ANDu.`name`='冰峰')
SELECT*FROM`user`WHERE`name`NOTIN('冰峰');
这两种用法,也将使索引失效。但是 NOT IN 还是走索引的,千万不要误解为 IN 全部是不走索引的。我之前就有误解(丢人了...)。
8、IS NULL不走索引,IS NOT NULL走索引
SELECT*FROM`user`WHEREaddressISNULL
不走索引。
SELECT*FROM`user`WHEREaddressISNOTNULL;
走索引。
根据这个情况,建议大家这设计字段的时候,如果没有必要的要求必须为 NULL ,那么最好给个默认值空字符串,这可以解决很多后续的麻烦(有深刻的体验<体验=教训>)。
符合索引
1、最左匹配原则
EXPLAINSELECT*FROM`user`WHEREsex='男';
EXPLAINSELECT*FROM`user`WHEREname='冰峰'ANDsex='男';
测试之前,删除其他的单列索引。
啥叫最左匹配原则,就是对于符合索引来说,它的一个索引的顺序是从左往右依次进行比较的,像第二个查询语句,name 走索引,接下来回去找 age ,结果条件中没有 age 那么后面的 sex 也将不走索引。
注意:
SELECT*FROM`user`WHEREsex='男'ANDage=22AND`name`='冰峰';
可能有些搬砖工可能跟我最开始有个误解,我们的索引顺序明明是 name 、 sex 、age ,你现在的查询顺序是 sex 、age 、name ,这肯定不走索引啊,你要是自己没测试过,也有这种不成熟的想法,那跟我一样还是太年轻了,它其实跟顺序是没有任何关系的,因为 mysql 的底层会帮我们做一个优化,它会把你的 SQL 优化为它认为一个效率最高的样子进行执行。所以千万不要有这种误解。
2、如果使用了!=会导致后面的索引全部失效
SELECT*FROM`user`WHEREsex='男'AND`name`!='冰峰'ANDage=22;
我们在 name 字段使用了 != ,由于 name 字段是最左边的一个字段,根据最左匹配原则,如果 name 不走索引,后面的字段也将不走索引。
关于符合索引导致索引失效的情况能说的目前就这两种,其实我觉得对于符合索引来说,重要的是如何建立高效的索引,千万不能说我用到那个字段我就去建立一个单独的索引,不是就可以全局用了嘛。这样是可以,但是这样并没有符合索引高效,所以为了成为高级的搬砖工,我们还是要继续学习,如何创建高效的索引。
更多精彩推荐☞Linus Torvalds 回应,Debian 项目曾讨论永久禁止他出席会议!☞对话阿里云李飞飞:云原生数据库的时代来了☞IT 往事录:苹果 Mac 之父,却在 Mac 问世前黯然退场☞B 站神曲damedane:精髓在于换脸,五分钟就能学会☞可怕!公司部署了一个东西,悄悄盯着你……☞极简椭圆曲线密码学入门
点分享点点赞点在看