mysql索引失效的常见9种原因详解

123 篇文章 2 订阅
118 篇文章 0 订阅

目录

前言:

1.最佳左前缀法则

2.主键插入顺序

3.计算、函数、类型转换(自动或手动)导致索引失效

4.范围条件右边的列索引失效

5.不等于(!= 或者<>)导致索引失效

6.is null可以使用索引,is not null无法使用索引

7.like以通配符%开头索引失效

8.OR 前后只要存在非索引的列,都会导致索引失效?

9.数据库和表的字符集统一使用utf8mb4

特别鸣谢:


前言:

MySQL中提高性能的一个最有效的方式是对数据表设计合理的索引。索引提供了高效访问数据的方法,并且加快查询的速度, 因此索引对查询的速度有着至关重要的影响。

  • 使用索引可以快速地定位表中的某条记录,从而提高数据库查询的速度,提高数据库的性能。
  • 如果查询时没有使用索引,查询语句就会扫描表中的所有记录。在数据量大的情况下,这样查询的速度会很慢。

大多数情况下都(默认)采用B+ 树来构建索引。只是空间列类型的索引使R- 树,并且MEMORY 表还支持hash 索引。其实,用不用索引最终都是优化器说了算

优化器是基于什么的优化器? 基于cost开销(CostBaseOptimizer) ,它不是基于规则( Rule-BasedOptimizer),也不是基于语义。怎么样开销小就怎么来。另外, SQL 语句是否使用索引,跟数据库版本、数据量、数据选择度都有关系

**1.**最佳左前缀法则

拓展: Alibaba

Java 开发手册》

索引文件具有 B-Tree 的最左前缀匹配特性,如果左边的值未确定,那么无法使用此索引。

**2.**主键插入顺序

如果此时再插入一条主键值为 9 的记录,那它插入的位置就如下图:

可这个数据页已经满了,再插进来咋办呢?我们需要把当前 页面分裂 成两个页面,把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着什么?意味着: 性能损耗 !所以如果我们想尽量 避免这样无谓的性能损耗,最好让插入的记录的 主键值依次递增 ,这样就不会发生这样的性能损耗了。

所以我们建议:让主键具有 AUTO_INCREMENT ,让存储引擎自己为表生成主键,

在插入记录时存储引擎会自动为我们填入自增的主键值。这样的主键占用空间小,顺序写入,减少页分裂。

**3.计算、函数、类型转换(自动或手动)**导致索引失效

**4.**范围条件右边的列索引失效

例子:

#创建一个联合索引, 注意字段的顺序

create index idx_age_classid_name on student(age,classid,name);

#执行计划

EXPLAIN SELECT SQL_NO_CACHE * FROM student

WHERE student .age = 30 AND student .classId > 20 AND student .name = ‘abc’ ;

#再创建一个联合索引,与上面的索引对比字段顺序变了

create index idx_age_name_classid on student(age,name,classid);

#再执行一模一样的执行计划

EXPLAIN SELECT SQL_NO_CACHE * FROM student

WHERE student .age = 30 AND student .classId > 20 AND student .name = ‘abc’ ;

看到两个执行计划虽然都用到了索引,但是:

  • 第一个没用全,只用到了联合索引“idx_age_classid_name” 的age和classid。
  • 第二个把联合索引“idx_age_name_classid”的age,name和classid都用上了。

5.不等于(!= 或者**<>)**导致索引失效

6.is null****可以使用索引,is not null无法使用索引

**7.like以通配符%**开头索引失效

拓展: Alibaba 《Java 开发手册》

【强制】页面搜索严禁左模糊或者全模糊,如果需要请走搜索引擎来解决。

8.OR 前后只要存在非索引的列,都会导致索引失效

**9.**数据库和表的字符集统一使用utf8mb4

统一使用utf8mb4( 5.5.3 版本以上支持 ) 兼容性更好,统一字符集可以避免由于字符集转换产生的乱码。不同的 字符集 进行比较前需要进行 转换 会造成索引失效。

特别鸣谢:

以上知识来源于“尚硅谷”,宋红康老师的《MySQL数据库教程天花板,mysql安装到mysql高级,强!硬!》系列课程。

所以本文章看不懂的卷王们可以去搜索宋红康老师的mysql系列课程。

最后

深知大多数初中级Java工程师,想要提升技能,往往是自己摸索成长或者是报班学习,但对于培训机构动则近万的学费,着实压力不小。自己不成体系的自学效果低效又漫长,而且极易碰到天花板技术停滞不前!

因此收集整理了一份《Java开发全套学习资料》送给大家,初衷也很简单,就是希望能够帮助到想自学提升又不知道该从何学起的朋友,同时减轻大家的负担。

小编已加密:aHR0cHM6Ly9kb2NzLnFxLmNvbS9kb2MvRFVrVm9aSGxQZUVsTlkwUnc==出于安全原因,我们把网站通过base64编码了,大家可以通过base64解码把网址获取下来。

评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值