工作中,经常遇到这样的问题,我明明在MySQL表上面加了索引,为什么执行SQL查询的时候却没有用到索引?

同一条SQL有时候查询用到了索引,有时候却没用到索引,这是咋回事?

原因可能是索引失效了,失效的原因有以下几种,看你有没有踩过类似的坑?

1. 数据准备:

有这么一张用户表,在name字段上建个索引:

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(255) DEFAULT NULL COMMENT '姓名',
  `age` int DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB COMMENT='用户表';

2. Explain详解:

想要查看一条SQL是否用到索引?用到了哪种类型的索引?

可以使用 explain 关键字,查看SQL执行计划。例如:

explain select * from user where id=1;

可以看到type=const,表示使用了主键索引。

explain的所有type类型如下:

3. 失效原因

1. 数据类型隐式转换

name字段是varchar类型,如果我们使用数据类型查询,就会产生数据类型转换,虽然不会报错,但是无法用到索引。

explain select * from user where name='一灯';
explain select * from user where name=18;

2. 模糊查询 like 以%开头

explain select * from user where name like '张%';
explain select * from user where name like '%张';

3. or前后没有同时使用索引

虽然name字段上加了索引,但是age字段没有索引,使用or的时候会全表扫描。

# or前后没有同时使用索引,导致全表扫描
explain select * from user where name='一灯' or age=18;

4. 联合索引,没有使用第一列索引

如果我们在(name,age)上,建立联合索引,但是查询条件中只用到了age字段,也是无法用到索引的。

使用联合索引,必须遵循最左匹配原则,首先使用第一列字段,然后使用第二列字段。

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(255) DEFAULT NULL COMMENT '姓名',
  `age` int DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`),
  KEY `idx_name_age` (`name`,`age`)
) ENGINE=InnoDB COMMENT='用户表';

5. 在索引字段进行计算操作

如果我们在索引列进行了计算操作,也是无法用到索引的。

# 在主键索引上进行计算操作,导致全表扫描
explain select * from user where id+1=2;

6. 在索引字段字段上使用函数

如果我们在索引列使用函数,也是无法用到索引的。

7. 优化器选错索引

同一条SQL有时候查询用到了索引,有时候却没用到索引,这是咋回事?

这可能是优化器选择的结果,会根据表中数据量选择是否使用索引。

当表中大部分name都是一灯,这时候用name='一灯'做查询,还会不会用到索引呢?

索引优化器会认为,用索引还不如全表扫描来得快,干脆不用索引了。

当然我们认为优化器优化的不对,也可以使用 force index 强制使用索引。

知识点总结:

目录一、 mysql 中如果 没有 索引 如何查找数据1、方式一:在一个页中查找2、方式二:在多个页中查找二、 mysql 索引 的概念1、前提条件2、目录项记录3、如何区分普通用户记录和目录项记录4、普通用户记录和目录项的区别有哪些?三、 mysql 中B+树1、如何快速定位目录项2、上图中B+树的解释 一、 mysql 中如果 没有 索引 如何查找数据 1、方式一:在一个页中查找 比如数据较少,都存储在一个页中。在查找记录时,是要根据搜索条件的不同分为两种情况的。 第一种情况:以主键列作为搜索条件 (1)、在页目录(Page 1.摘要:DBA经常会有新接手的业务数据库,之前大多事RD自己维护, 没有 专职DBA来维护 MySQL ,建 索引 随心所欲,导致好多表上有大 索引 ,有的表 索引 大小比表数据都要大很多,作为一名DBA看到这种情况是不是会头大,很想把那些 用的index全部干掉,但也不能随便就干掉,怎么办,表示很需要耗时间。还有一种情况,DBA建了 索引 ,怎么查看这些已经建完的 索引 就一定 使用 到了呢?有些业务可能过了一段时间就... 如果参数是1个会用到 索引 , 如果参数是2个及以上不会用到 索引 。 或者in里是连续的参数,比如where fee in (1,2)、where fee in (1,2,3),fee是int类型并且有单列 索引 ,依然不会 使用 索引 .(包括int和varchar类型的字段) 2.like ‘%abc’ 或者 like‘%abc%’ 3.条件中 有表达式,比如where num/2=100 或者 substring(a,1,3)='ab’ 一、单表 查询 索引 列和 查询 索引 列+其他非 索引 查询 数据速度为什么有差?为什么 查询 索引 列会不 索引 ?现象: 单个表,结构如下:CREATE TABLE `t_attach_new` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT,`fname` varchar(100) NOT NULL DEFAULT '',`ftype` varchar(50) ... 有时候在 MySQL 上执行 查询 时,即使WHERE条件中涉及的字段已经建立了 索引 ,但 MySQL 仍然 没有 使用 到该 索引 ,下面针对各种可能的原因进行总结。 下文中说到的原因均已在 MySQL 5.7.21中进行验证测试。 可能的原因 1)where中的字段涉及隐式数据类型转换 users表的a字段建有 索引 ,数据类型为varchar SELECT * FROM users WHERE a = 1 上述语句不会 使用 到a字段的 索引 ,因为a字段的数据类型是varchar,而SQL中的WHERE条件是a=1,涉及到 隐式转换导致 索引 失效.这一点应当引起重视.也是开发中经常会犯的错误. 由于表的字段tu_mdn定义为varchar2(20),但在 查询 时把该字段作为number类型以where条件传给 mysql ,这样会导致 索引 失效. 错误的例子:select * from test where tu_mdn=13333333333; 正确的例子:select * from test where tu_mdn='1... 今天查看宝塔面板后台的 MySQL 设置状态时,发现“ 没有 使用 索引 ”非常多,达到10W+,刚开始不知道这是什么意思。去网上查找也 没有 找到关于宝塔面板的这个设置到底是什么意思?有什么影响。宝塔面板 MySQL 没有 使用 索引 过高有什么影响?如上图, 没有 使用 索引 达到10W+,经过查找资料总结了一下 MySQL 没有 使用 索引 查询 ,相当于全表 查询 ,效率底下,数据库会疯狂的消耗内存,并且会影响前端程序的执行。... select * from tableName where a=1 and (b=1 or c=1) 其中建立了ab和ac的联合 索引 ,但是看db监控慢 查询 的扫描行数和a=1的行数相同。通过explain看出执行计划是 ab的联合 索引 ,但是为啥扫描行数却是a=1的行数呢?猜测是因为要查a=1 and b=1的数据时候,可以 ab 索引 ,但是查出 a=1和c=1的数据,就需要扫描a=1的所有数据。可以通过联合 索引 的数据结构看出,当a相同时候,会按照b来排序。而且 mysql 一般不会选择 多个 索引 ,除非要 索引 合并,