MySQL索引命中与失效代码实现

目录
  • 创建表
  • MySQL执行优化器
    • 第一种情况:针对联合索引,是否遵循最左匹配原则;
    • 第二种情况:去掉大哥,看看索引是否命中;
    • 第三种情况:在索引列上做了函数操作,会导致索引失效而导致全表扫描
    • 第四种情况:模糊查询前缀是以%开头的,索引失效
    • 第五种情况:模糊查询中后缀是以%,可以命中索引
    • 第六种情况:使用is not null 会导致索引失效
    • 第六种情况:使用and时,其中有一个条件查询带有索引而另一个不带索引,不会导致索引失效。而使用or时,如果条件查询中其中一个不带索引,导致索引失效,必须全部带有索引。
    • 第七种情况:使用不等于(!= 或者<>)的时候,会导致索引失效
    • 第八种情况:使用范围查询之后索引失效
    • 第九种情况:隐式转换可能会导致我们的索引失效
  • 总结
    • MySQL中索引失效的情况
    • 查看MySQL中索引是否命中可以使用explainh执行优化器来查看。

创建表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(5) NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名',
  `password` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户密码',
  `create_essay` int(5) NOT NULL COMMENT '原创文章',
  `user_visited` int(10) NOT NULL COMMENT '被访问量',
  `user_rank` int(5) NOT NULL COMMENT '用户排名',
  `perms` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `nickname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户昵称',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 116856 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

// 创建组合索引
ALTER TABLE `user` ADD INDEX idx_username_password_user_rank (`username`,`password`,`user_rank`)

这里有一个组合索引的最左匹配原则:MySQL最左匹配原则

查看MySQL中索引是否命中可以使用explainh执行优化器来查看

MySQL执行优化器

执行优化器,顾名思义,优化语句的,准确来说是优化查询语句。其实就是在我们写的select语句前加一个Explain关键字。

索引的命中与失效情况

第一种情况:针对联合索引,是否遵循最左匹配原则;

建立一个组合索引

idx_username_password_user_rank(`username`,`password`,`user_rank`)
// 命中索引跟顺序无关
explain SELECT * from `user` where username =  "liuxiangcheng" and password = "515239" and user_rank = 1
explain SELECT * from `user` where user_rank = 1 and username =  "liuxiangcheng" and password = "515239"
explain SELECT * from `user` where user_rank = 1 and password = "515239" and username =  "liuxiangcheng"

结果:

第二种情况:去掉大哥,看看索引是否命中;

// 去掉大哥
explain SELECT * from `user` where password = "515239" and user_rank = 1

去掉大哥之后,索引失效,全表扫描。

第三种情况:在索引列上做了函数操作,会导致索引失效而导致全表扫描

我们先把那个联合索引删除掉,然后在username这一列上建立一个唯一索引:

删除组合索引

drop index idx_username_password_user_rank on `user`

创建唯一索引

alter table `user` ADD UNIQUE key (`username`)

查看索引

explain SELECT * from `user` where username= 'user110819'

explain SELECT * from `user` where concat(username,'')= 'user110819'

第四种情况:模糊查询前缀是以%开头的,索引失效

explain SELECT * from `user` where username like '%user11081'

第五种情况:模糊查询中后缀是以%,可以命中索引

explain SELECT * from `user` where username like 'user11081%'

第六种情况:使用is not null 会导致索引失效

explain SELECT * from `user` where username is not null

第六种情况:使用and时,其中有一个条件查询带有索引而另一个不带索引,不会导致索引失效。而使用or时,如果条件查询中其中一个不带索引,导致索引失效,必须全部带有索引。

and情况:

explain SELECT * from `user` where username = "liuxiangcheng" and password = "515239"

or情况:

explain SELECT * from `user` where username = "liuxiangcheng" or user_rank = 1

我们给user_rank加上索引

alter table `user` ADD index (`user_rank`)

再次查询:

explain SELECT * from `user` where username = "liuxiangcheng" or user_rank = 1

第七种情况:使用不等于(!= 或者<>)的时候,会导致索引失效

explain SELECT * from `user` where   user_rank != 1

or 

explain SELECT * from `user` where   user_rank <> 1

第八种情况:使用范围查询之后索引失效

explain SELECT * from `user` where user_rank >(<,>=,<=) 1

第九种情况:隐式转换可能会导致我们的索引失效

varchar类型,如果用int类型来查询,索引失效

数据库user表中我们的password是varchar类型

如果我们在条件查询中使用整形来替代,那么这个时候索引就会失效,where varchar = int 索引失效

// password is varchar type

explain SELECT * from `user` where password = 515239

explain SELECT * from `user` where password = '515239'

explain SELECT * from `user` where password = "515239"

如果是int类型,我们使用varchar来替代,索引命中

// user_rank is int type

explain SELECT * from `user` where user_rank = "1"

explain SELECT * from `user` where user_rank = '1'

explain SELECT * from `user` where user_rank = 1

总结隐式转换

  1. 当操作符左右两边的数据类型不一致时,会发生隐式转换
  2. 当 where 查询操作符左边为数值类型时发生了隐式转换,但是索引会命中,对查询效率影响不大,但还是不推荐这么做
  3. 当 where 查询操作符左边为字符类型时发生了隐式转换,这样会导致索引失效,造成全表扫描。
  4. 字符串转换为数值类型时,非数字开头的字符串会转化为0,以数字开头的字符串会截取从第一个字符到第一个非数字内容为止的值为转化结果。

总结

MySQL中索引失效的情况

1、组合索引中不遵循最左匹配原则,带头大哥不在,导致索引失效,全表扫描。
2、在索引列上做了函数操作,导致索引失效,全表扫描。
3、模糊查询前缀是以%开头的,导致索引失效,全表扫描。
4、使用is not null 会导致索引失效。
5、使用or时,如果条件查询中其中一个不带索引,导致索引失效,全表扫描。
6、使用不等于(!= 或者<>)的时候,会导致索引失效。
7、使用范围查询(>、<、>=、<=)之后索引失效。
8、隐式转换可能会导致我们的索引失效。

查看MySQL中索引是否命中可以使用explainh执行优化器来查看。

到此这篇关于MySQL索引命中与失效代码实现的文章就介绍到这了,更多相关MySQL索引命中与失效内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • 从MySQL的源码剖析Innodb buffer的命中率计算

    按官方手册推荐Innodb buffer Hit Ratios的计算是: 100-((iReads / iReadRequests)*100) iReads : mysql->status->Innodb_buffer_pool_reads iReadRequests: mysql->status->Innodb_buffer_pool_read_requests 出处: http://dev.mysql.com/doc/mysql-monitor/2.0/en/mem_graph

  • MySQL之主键索引排序失效问题

    目录 主键索引排序失效 现在初始化几行数据 查一下所有记录 查看一下执行计划 总结 主键索引排序失效 环境:MySQL8 有一张用户信息表user_info,建表DDL如下: CREATE TABLE `user_info` ( `id` int(11) NOT NULL COMMENT '用户编号', `age` int(11) NOT NULL COMMENT '用户年龄', PRIMARY KEY (`id`), KEY `idx_age` (`age`) USING BTREE COMM

  • 关于Mysql5.7及8.0版本索引失效情况汇总

    目录 一个独立索引 多个独立索引 总结 TIPS: 没有特殊说明,测试环境均为MySQL8.0,早期版本可能会有更多情况导致索引失效.8.0失效的情况,早期版本也失效:8.0不失效的情况,早期版本可能失效. 所有测试默认不考虑表为空的情况,特殊情况文中会有说明. 本文只介绍Innodb引擎下的索引失效情况. -- 创建测试表 DROP TABLE IF EXISTS `test_idx`; CREATE TABLE `test_idx` ( `id` int(11) NOT NULL AUTO_

  • MySQL中因字段字符集不同导致索引不能命中的解决方法

    什么是索引?为什么要建立索引? 索引用于快速找出在某个列中有一特定值的行,不使用索引,MySQL必须从第一条记录开始读完整个表,直到找出相关的行,表越大,查询数据所花费的时间就越多,如果表中查询的列有一个索引,MySQL能够快速到达一个位置去搜索数据文件,而不必查看所有数据,那么将会节省很大一部分时间. 例如:有一张person表,其中有2W条记录,记录着2W个人的信息.有一个Phone的字段记录每个人的电话号码,现在想要查询出电话号码为xxxx的人的信息. 如果没有索引,那么将从表中第一条记录

  • MySQL索引失效场景及解决方案

    目录 一.前言 二.最左前缀匹配原则 三.MySQL逻辑架构和优化器 四.索引失效场景以及为何会失效 五.总结 一.前言 在对SQL语句进行索引查询时会遇到索引失效的时候,对于该语句的可行性以及性能效率方面有至关重要的影响,本篇剖析索引为何失效,有哪些情况会导致索引失效以及对于索引失效时的优化解决方案,其中着重介绍最左前缀匹配原则.MySQL逻辑架构和优化器.索引失效场景以及为何会失效. 二.最左前缀匹配原则 之前有写了一篇关于MySQL添加索引特点及优化问题方面的文章,下面将介绍索引失效的相关

  • MySQL细数发生索引失效的情况

    目录 索引的存储结构 不合理的模糊查询条件 对索引使用函数 对索引进行表达式计算 对索引使用隐式转换 联合索引非最左匹配 where子句中的or 总结 索引的存储结构 首先了解一下索引的存储结构,知道了索引的存储结构,才方便我们更好地理解索引失效的问题. 索引的存储结构跟MySQL的存储引擎有关,存储引擎的不同采用的结构也会不同. MySQL默认的存储引擎InnoDB采用B+Tree作为索引的数据结构,在创建表时,InnoDB会默认创建一个主键索引,这是一个聚簇索引,其他索引都属于二级索引. M

  • MySQL索引优化之不适合构建索引及索引失效的几种情况详解

    目录 结论 不建议建立索引的场景 索引失效的场景 小结 结论 具体案例下文有详尽描述 不适合建立索引的场景: 数据量比较小的表不建议建立索引 有大量重复数据的字段上不建议建立索引(类似:性别字段) 需要进行频繁更新的表不建议建立索引 where.group by.order by后面的没有使用到的字段不建立索引 不要定义冗余索引 索引失效的场景: 过滤条件使用不等于(!=.<>) 过滤条件使用is not null 在索引字段上使用函数或进行计算 在使用联合索引的时候,需要满足“最佳左前缀法则

  • mysql字段为NULL索引是否会失效实例详解

    项目场景: 很多博客说mysql在字段中创建普通索引,如果该索引中的数据存在null值是不走索引这个结论是错误的,不过尽量还是设置默认值.(版本8.0低于这个版本可能结果不一致) 1.创建表sc_base_color,其中普通索引为 “name,group_num”,这里暂时不测组合索引,下面再测试. CREATE TABLE `sc_base_color` ( `id` bigint NOT NULL AUTO_INCREMENT, `group_num` bigint DEFAULT NUL

  • mysql索引最左原则实例代码

    前言 最近在看MySQL索引的知识,看到组合索引的时候,有一个最左侧原则,通过查找相关资料深入学习了下,下面话不多说了,来一起看看详细的介绍吧 建表 CREATE TABLE `user` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, `sex` tinyint(1) DEFAULT NULL, `age` tinyint(2) DEFAULT NULL, PRIMARY KEY

  • 导致MySQL索引失效的一些常见写法总结

    前言 最近一直忙着处理原来老项目遗留的一些SQL优化问题,由于当初表的设计以及字段设计的问题,随着业务的增长,出现了大量的慢SQL,导致MySQL的CPU资源飙升,基于此,给大家简单分享下这些比较使用的易于学习和使用的经验. 这次的话简单说下如何防止你的索引失效. 再说之前我先根据我最近的经验说下我对索引的看法,我觉得并不是所以的表都需要去建立索引,对于一些业务数据,可能量比较大了,查询数据已经有了一点压力,那么最简单.快速的办法就是建立合适的索引,但是有些业务可能表里就没多少数据,或者表的使用

  • MySQL索引失效的几种情况汇总

    一.索引不存储null值 更准确的说,单列索引不存储null值,复合索引不存储全为null的值.索引不能存储Null,所以对这列采用is null条件时,因为索引上根本 没Null值,不能利用到索引,只能全表扫描. 为什么索引列不能存Null值? 将索引列值进行建树,其中必然涉及到诸多的比较操作.Null值的特殊性就在于参与的运算大多取值为null. 这样的话,null值实际上是不能参与进建索引的过程.也就是说,null值不会像其他取值一样出现在索引树的叶子节点上. 二.不适合键值较少的列(重复

  • MySQL索引失效的几种情况详析

    1.前导模糊查询不能利用索引(like '%XX'或者like '%XX%') 假如有这样一列code的值为'AAA','AAB','BAA','BAB' ,如果where code like '%AB'条件,由于前面是 模糊的,所以不能利用索引的顺序,必须一个个去找,看是否满足条件.这样会导致全索引扫描或者全表扫 描.如果是这样的条件where code like 'A % ',就可以查找CODE中A开头的CODE的位置,当碰到B开头的 数据时,就可以停止查找了,因为后面的数据一定不满足要求.

  • mysql索引失效的几种情况分析

    1.最佳左前缀原则--如果索引了多列,要遵守最左前缀原则.指的是查询要从索引的最左前列开始并且不跳过索引中的列. 前提条件:表中已添加复合索引(username,password,age) 分析:该查询缺少username,查询条件复合索引最左侧username缺少,违反了最佳左前缀原则,导致索引失效,变为ALL,全表扫描 分析:查询条件缺少username,password,查询条件复合索引最左侧username,password缺少,违反了最佳左前缀原则,导致索引失效,变为ALL,全表扫描

  • MySQL索引失效的典型案例

    典型案例 有两张表,表结构如下: CREATE TABLE `student_info` (   `id` int(11) NOT NULL,   `name` varchar(10) DEFAULT NULL,   PRIMARY KEY (`id`),   KEY `idx_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 CREATE TABLE `student_score` (   `id` int(11) NOT NULL,

  • mysql索引失效的十大问题小结

    目录 背景 一.查询条件包含or,可能导致索引失效 二.如何字段类型是字符串,where时一定用引号括起来,否则索引失效 三.like通配符可能导致索引失效. 四.联合索引,查询时的条件列不是联合索引中的第一个列,索引失效. 五.在索引列上使用mysql的内置函数,索引失效. 六.对索引列运算(如,+.-.*./),索引失效. 七.索引字段上使用(!= 或者 < >,not in)时,可能会导致索引失效. 八.索引字段上使用is null, is not null,可能导致索引失效. 九.左连

  • MySQL索引失效原理

    目录 1.索引失效原因 2.再来看看哪些情况会破坏索引的有序性. - 对索引字段做函数操作 - 隐式类型转换 - 隐式字符编码转换 3.总结 1.索引失效原因 首先看看哪些情况下,将会导致查找不能利用索引的有序性. 假设一个表test中有a,b,c,d四个字段,c是主键. 在a,b字段上建立联合索引(a,b):CREATE index idx_a_b on test(a,b); B+树联合索引.JPG 可以得到的规律是:优先按a字段从小到大排序,a字段相等的按b字段从小到大排序: 分析以下情况,

随机推荐

其他