Mysql如何判断是否走索引

Mysql判断sql是否走索引是开发中需要掌握的技能，也是面试可能问到的知识点之一。

数据准备

在说明这些原因前，需要数据表结构和测试数据：

CREATE TABLE `user` (
  `id` int(10) NOT NULL,
  `name` varchar(20) CHARACTER SET utf8 NOT NULL COMMENT '名字',
  `password` varchar(40) CHARACTER SET utf8 NOT NULL COMMENT '密码',
  `status` char(1) CHARACTER SET utf8 DEFAULT NULL COMMENT '状态',
  `mobile` char(11) CHARACTER SET utf8 DEFAULT NULL COMMENT '手机号',
  `add_time` int(10) DEFAULT NULL COMMENT '新增日期',
  `gender` char(1) CHARACTER SET utf8 DEFAULT NULL COMMENT '1男2女3未知',
  `age` int(10) DEFAULT NULL COMMENT '年龄',
  `uuid` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT 'uuid测试外键',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_mobile` (`mobile`) USING BTREE COMMENT '手机号',
  UNIQUE KEY `idx_uuid` (`uuid`) USING BTREE COMMENT 'uuid',
  KEY `idx_status` (`status`) USING BTREE COMMENT '状态',
  KEY `idx_add_time` (`add_time`) USING BTREE COMMENT '新增日期',
  KEY `idx_gender_age` (`gender`,`age`) USING BTREE COMMENT '性别和年龄联合索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
INSERT INTO `yxjc`.`user`(`id`, `name`, `password`, `status`, `mobile`, `add_time`, `gender`, `age`, `uuid`) VALUES (1, '张三', '123456', '1', '13122222222', 1579142334, '1', 22, '111');
INSERT INTO `yxjc`.`user`(`id`, `name`, `password`, `status`, `mobile`, `add_time`, `gender`, `age`, `uuid`) VALUES (2, '李四', '123456', '1', '13133333333', 1673836735, '2', 23, '222');
INSERT INTO `yxjc`.`user`(`id`, `name`, `password`, `status`, `mobile`, `add_time`, `gender`, `age`, `uuid`) VALUES (3, '王五', '123456', '0', '13155555555', 1736995134, '2', 24, '333');
INSERT INTO `yxjc`.`user`(`id`, `name`, `password`, `status`, `mobile`, `add_time`, `gender`, `age`, `uuid`) VALUES (4, '赵六', '123456', '0', '13156666666', 1736995136, '1', 25, '444');

这里，创建了一张用户表user，并给mobile，uuid添加唯一索引，status，add_time添加普通索引，gender,age添加联合索引。

后面在作关联查询时还有一个user_ext表，后面再说。

explain判断是否走索引

我们可以用explain sql 查看sql的执行计划，用于判断sql语句是否使用了索引，使用了什么索引。

explain select * from user where id = 1;

输出

其中列type是判断索引的关键字段，explain 中 type相关解释如下：

序号	explain type 值	解释
1	system	系统表，表里只有一行数据
2	const	使用主键索引或唯一索引等值查询
3	eq_ref	使用主键索引或唯一索引等值表连接
4	ref	非唯一索引等值查询
5	ref_or_null	非唯一索引，可以包含null值
6	index_merge	使用多个索引
7	range	索引范围查询
8	index	索引全表扫描
9	all	全表扫描

在上面的表格中，索引由上至下从好到差，除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引。

有了这些为基础，接下来介绍Mysql为什么不走索引，我们都以上面的准备数据为基础。

失效原因

下面通过例子介绍几种不走索引的原因，让你对它有更深刻的印象。

字段类型和查询类型不一致

explain select * from user where status = '1';

 explain select * from user where status = 1;

在上面的例子中，user表的status字段是char类型，

• 当status='1'时，type的返回值是ref，使用了非唯一索引等值查询；
• 当status=1时，type的返回值是all，它表示全表扫描。

所以，当查询字段的类型和定义不同的时候，是不走索引的。

使用函数导致不走索引

explain select * from user where from_unixtime(add_time, '%Y-%m-%d')>'2022-1-16';

explain select * from user where add_time>1673836735;

在上面的例子中我们使用了函数时间戳转字符串from_unixtime，

• 当条件为from_unixtime(add_time, '%Y-%m-%d')>'2022-1-16'时，type的返回值是all，它扫描了全表，没有走索引；
• 当条件为add_time>1673836735时，type的返回值是range，它使用了索引范围查询。

所以，使用函数导致不走索引，在这个例子中，我们可以先将日期格式转为时间戳。

or 前后使用不同的字段

explain select * from user where id=1 or mobile = '13122222222';

explain select * from user where id = 1 or id = 3;

在上面的例子中，我们使用了or查询，

• 当查询条件是id=1 or mobile = '13122222222';时，type的返回值是all，它扫描了全表，没有走索引；
• 当查询条件是id=1 or id=3;时，type的返回值是range，它使用了索引范围查询。

所以，or查询条件前后两个字段需要一致，否则会扫描全表。

like查询导致不走索引

在like查询中，当%放在匹配字段左边是不走索引的。

explain select * from user where mobile like  '%1312%';

explain select * from user where mobile like  '1312%'; 

在上面的例子中，我们使用了like查询，

• 当查询条件是 mobile like  '%1312%';时，type的返回值是all，它扫描了全表，没有走索引；
• 当查询条件是mobile like  '1312%'时，type的返回值是range，它使用了索引范围查询。

所以，在like查询中，当%放在匹配字段左边是不走索引的。

联合索引缺少条件

在user表中有联合索引 KEY `idx_gender_age` (`gender`,`age`) USING BTREE COMMENT '性别和年龄联合索引'

explain select * from user where age=23;

explain select * from user where age=23 and gender = '1' 

在上面的例子中，我们使用了联合索引，

• 当查询条件是 age=23 时，type的返回值是all，它扫描了全表，没有走索引；
• 当查询条件是age=23 and gender = '1' 时，type的返回值是ref，它使用了非唯一索引等值查询。

所以，当使用联合索引时，where条件需要全部带上。

使用不等式或取反索引失效

使用 != 或者 <> NOT IN、NOT EXISTS导致索引失效。

例如

-- 走索引
explain select * from user where mobile = '13122222222';
-- 不走索引
explain select * from user where mobile != '13122222222';

关联查询不走索引

这里我们新增一个表user_ext和user表作关联

CREATE TABLE `user_ext` (
  `user_id` int(10) NOT NULL COMMENT 'uid',
  `address` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '地址',
  `intro` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '介绍',
  `resume` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '履历',
  `uuid` varchar(40) CHARACTER SET utf8 DEFAULT NULL,
  UNIQUE KEY `idx_id` (`user_id`),
  UNIQUE KEY `idx_uuid` (`uuid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='用户扩展表';
INSERT INTO `yxjc`.`user_ext`(`user_id`, `address`, `intro`, `resume`, `uuid`) VALUES (1, '123', '123', '123', '111');
INSERT INTO `yxjc`.`user_ext`(`user_id`, `address`, `intro`, `resume`, `uuid`) VALUES (2, '222', '222', '222', '222');
INSERT INTO `yxjc`.`user_ext`(`user_id`, `address`, `intro`, `resume`, `uuid`) VALUES (3, '333', '333', '333', '333');
INSERT INTO `yxjc`.`user_ext`(`user_id`, `address`, `intro`, `resume`, `uuid`) VALUES (5, '555', '555', '555', '555');

其中我们将b表user_ext, uuid的字符编码设置为utf8而不是utf8mb4，索引失效的sql如下：

explain 
select * from user as a 
left join user_ext as b on a.uuid = b.uuid
where a.uuid = '111'

其中

• a表返回const，表示主键索引或者唯一索引；
• b表返回all，表示扫描全表。

 说明在作left join关联时，utf8不能向utf8mb4转换，但是utf8mb4可以向utf8转换，也就是说我们将a表的uuid字段改为utf8，b表的uuid改为utf8mb4是成立的。

修改字段字符编码后再次运行

关于连表查询不止字符编码这一个原因，还有其它的可能原因，如下：

• 关联条件字段类型是否保持一致
• 关联条件字段字符集是否保持一致
• 关联条件是否使用函数
• 模糊匹配是否使用前模糊 '%xxx'
• 是否使用正则表达式

总结

掌握mysql不走索引的原因是我们开发中不可或缺的一项技能，但是在线上运行的程序，我们并不知道什么时候需要优化，于是Mysql提供了开启慢查询的功能，记录一些 查询过慢的sql语句（比如查询时间超过2秒），我们可以通过这些sql语句再进行优化。

开启慢查询配置。

[mysqlld]  
long_query_time=2  
  
#5.0、5.1等版本配置如下选项  
log-slow-queries="mysql_slow_query.log"  
#5.5及以上版本配置如下选项  
slow-query-log=On  
slow_query_log_file="mysql_slow_query.log"  
  
log-query-not-using-indexes