专业

深圳网站优化客服热线:SEO

揭秘百度SEO快排7天上首页秘籍详情

深圳网站优化

当前位置:首页 > 技术教程

导致MySQL索引失效的几种常见写法 - 一个程序员的成长的个人空间 - OSCHINA

来源: 作者: 访问次数:19

导致MySQL索引失效的几种常见写法

一个程序员的成长
 一个程序员的成长 发布于 09/06 15:15
字数 2157
阅读 2.7K
收藏 34
点赞 1
评论 1
mysql

最近一直忙着处理原来老项目遗留的一些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字段建立了索引,但是如果!= 或者 <> 这种都会导致索引失效,进行全表扫描,所以如果数据量大的话,谨慎使用

可以通过分析SQL看到,type类型是ALL,扫描了10行数据,进行了全表扫描。<>也是同样的结果。

2、类型不一致导致的索引失效

在说这个之前,一定要说一下设计表字段的时候,千万、一定、必须要保持字段类型的一致性,啥意思?比如user表的id是int自增,到了用户的账户表user_id这个字段,一定、必须也是int类型,千万不要写成varchar、char什么的骚操作。

SELECT * FROM `user` WHERE height= 175;

这个SQL诸位一定要看清楚,height表字段类型是varchar,但是我查询的时候使用了数字类型,因为这个中间存在一个隐式的类型转换,所以就会导致索引失效,进行全表扫描。

现在明白我为啥说设计字段的时候一定要保持类型的一致性了不,如果你不保证一致性,一个int一个varchar,在进行多表联合查询(eg: 1 = '1')必然走不了索引。

遇到这样的表,里面有几千万数据,改又不能改,那种痛可能你们暂时还体会。

少年们,切记,切记。

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

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

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

如果你对列进行了(+,-,*,/,!), 那么都将不会走索引。

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

OR导致索引是在特定情况下的,并不是所有的OR都是使索引失效,如果OR连接的是同一个字段,那么索引不会失效,反之索引失效。

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

这个我相信大家都明白,模糊搜索如果你前缀也进行模糊搜索,那么不会走索引。

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 全部是不走索引的。我之前就有误解(丢人了...)。

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

不走索引。

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

走索引。

根据这个情况,建议大家这设计字段的时候,如果没有必要的要求必须为NULL,那么最好给个默认值空字符串,这可以解决很多后续的麻烦(有深刻的体验<体验=教训>)。

符合索引

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

测试之前,删除其他的单列索引。

啥叫最左匹配原则,就是对于符合索引来说,它的一个索引的顺序是从左往右依次进行比较的,像第二个查询语句,name走索引,接下来回去找age,结果条件中没有age那么后面的sex也将不走索引。

注意:

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不走索引,后面的字段也将不走索引。

关于符合索引导致索引失效的情况能说的目前就这两种,其实我觉得对于符合索引来说,重要的是如何建立高效的索引,千万不能说我用到那个字段我就去建立一个单独的索引,不是就可以全局用了嘛。这样是可以,但是这样并没有符合索引高效,所以为了成为高级的搬砖工,我们还是要继续学习,如何创建高效的索引。

日拱一卒,功不唐捐

今日推荐

为什么建议你使用枚举?
用户输入的虎狼之词,怎么校验之后不见了?



本文分享自微信公众号 - 一个程序员的成长(xiaozaibuluo)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。

  • 全面实用的SEO竞争对手研究
  • seo优化逻辑和品牌如何进行口碑建设
  • 「飞优seo」手把手教你如何安装宝塔面板
  • 移动端SEO优化应该注意的几点
  • 你不知道的SEO常见术语,万词霸屏带你深入了解!
  • seo如何获取长尾流量
  • 企业网站建设seo优化如何写好软文
  • SEO优化案例中常犯的7个错误
  • SEO需要抛弃老旧观点才能跟上节奏
  • SEO优化对一个企业的影响力有多大
  • 让您的网站提升排名的SEO四步策略
  • 单页网站怎么做SEO优化与推广
  • 评价分析、FB兴趣发现、客户管理、SEO相关,我们推荐这50个工具
  • 想让网站流量翻倍,那你会做seo优化吗
  • SEO搜索优化,你可以不做外链吗?
  • seo软文写作必备知识
  • 杭州优化公司教你怎样通过SEO做产品宣传推广?
  • 网站SEO标题规划操作,网页标题修改后应该怎么办?
  • 2020年10个步骤让你从SEO小白到专家
  • 企业网站建设seo优化排名不稳定是什么原因?
  • SEO优化|如何判断是否过度优化 爱帮网络总结五点
  • 当心SEO优化行业中的伪科学
  • SEO指南:学习如何对网站进行SEO
  • SEO常用的关键词扩展分析工具有哪些?
  • 新手SEO需要学习十大要点我们相信许多企业都知道SEO优化是非常重要的,当他们第一次进入互联网。他们希望通过搜索引擎的免费频道获得大量流量。那么如何学习搜索引擎优化呢?这里我们给你一些建议。 1、准备空间和域名 SEO有很多概念术语,当然,这些都是要记住的,但不建议死记硬背,而是在实践中(操作网站优化时)要记住,因为死记硬背很容易忘记,而且你也不会真正理解它的含义。因此,我们主张我们应该从学习SEO的第一天开始操作。学习SEO一定要实践,即使你学了2年SEO课程,如果你没有实践过,你只会成为SEO的理
  • 有效SEO优化攻略已备好,咨询、转化不再“愁”
  • SEO网站诊断怎么做?几点基本因素和大家分享
  • SEO指南:学习如何对网站进行SEO
  • 如何做好seo优化内链工作
  • 如何做好seo优化内链工作
  • 网站改版的SEO注意事项!
  • 网站SEO课程笔记整理版!
  • 外贸网站优化所需采用的SEO策略
  • 百度SEO优化,什么时间增加内容与反向链接?
  • SEO网站数据分析调整
  • 雇佣SEO公司前请尝试15个SEO技巧
  • 关于网站seo的几点小常识,你知道多少?
  • SEO内容策划和文案是根基
  • 什么是SEO文章?seo文章的作用方法
  • SEO优化外包公司该怎么选?