一次Mysql查询踩坑经历（查询索引失效问题）-优快云博客

本文链接：https://blog.youkuaiyun.com/lejustdoit/article/details/144486380

1、之前的sql建表脚本

CREATE TABLE `crm_driver` (
  `id` bigint(22) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `clue_id` bigint(20) NOT NULL COMMENT '线索表id',
  `driver_name` varchar(128) NOT NULL COMMENT '试驾人姓名',
  `driver_phone` varchar(32) NOT NULL COMMENT '试驾人手机号',
  `driver_state` tinyint(1) NOT NULL DEFAULT '6' COMMENT '试驾状态 ： 1-已确认、2-试驾中、3-已完成、4-已取消',
  `reserva_shop_id` bigint(22) NOT NULL COMMENT '预约门店id',
  `shop_name` varchar(64) NOT NULL DEFAULT '' COMMENT '预约门店名称',
  `driver_time` datetime DEFAULT NULL COMMENT '试驾时间',
  `drive_remarks` varchar(512) NOT NULL DEFAULT '' COMMENT '试驾备注',
  `affirm_is` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否确认',
  `route_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '路线id',
  `test_car_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '试驾车id',
  `remarks` varchar(512) NOT NULL DEFAULT '' COMMENT '备注',
  `is_deleted` int(11) DEFAULT '0' COMMENT '逻辑删除',
  `created_by` varchar(64) DEFAULT NULL COMMENT '创建人',
  `created_time` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_by` varchar(64) DEFAULT NULL COMMENT '更新人',
  `updated_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_clue_id` (`clue_id`) USING BTREE COMMENT '线索ID',
  KEY `idx_driver_phone` (`driver_phone`) USING BTREE COMMENT '试驾人手机号',
  KEY `idx_shop_id` (`reserva_shop_id`) USING BTREE COMMENT '预约门店',
  KEY `idx_driver_state` (`driver_state`) USING BTREE COMMENT '试驾状态'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='试驾';

2、我接到的需求很简单，就是查询试驾表每页10条数据包含的手机号，在每个门店的试驾完成数，所以我的查询语句如下：

SELECT
	cd.driver_phone AS custPhone,
	cd.reserva_shop_id AS shopId,
	count( * ) AS driveCount 
FROM
	crm_driver cd 
WHERE
	cd.driver_phone IN ( 19300000002, 19300000004, 19300000014, 19300000016, 19300000017, 19300000018, 19300000019, 19300000022, 19300000034 ) 
	AND cd.driver_state = 3 
GROUP BY
	cd.driver_phone,
	cd.reserva_shop_id;

3、执行结果，结果花了8.7秒+

4、然后看一下数据库表的总量，只有153w+数据量

5、还有索引情况，group by 和查询的字段都落在索引上了

6、这时候再来执行一下explain，看看什么情况：

扫描了80w+行，然后真正使用到的索引只有 driver_state状态字段：

7、难道是in的条数多了，会导致索引查询失效吗，所以我把in 改成单个等于=试一下解析情况，发现这里还是没有用到索引：

8、难道我加的是假索引，我再换一个字都呢，用门店字段reserva_shop_id试试，居然一下就用到了索引：

9、然后看看这两个字段有什么区别，才发现了手机号是varchar类型，门店id是bigint类型：

10、那我换成字符串类型去查询呢，试试：

这里单个字段尝试生效了：

然后再改原始的业务sql:

查询时间缩几十短，0.37秒就执行完成：

11、这里就找到问题原因了，原来是在执行查询的时候，按照常规理解，以为手机号也应该是int类型，但是由于之前这个项目在早期的时候，别人设计的表结构字段，弄成了varchar，查询的时候导致mysql进行了隐式转换，然后索引失效，所以建表语句也要规范才行呀，不然会误导后面参与进来的其他开发同事，埋下隐形的坑。

12、总结小结：

（1）如果你的字段类型是varchar，你传参的类型是数值，会导致隐式转换索引失效；
（2）如果你的字段类型是varchar，你传参的类型是字符串，走索引；
（3）如果你的字段类型是int，你传参的类型是数值，走索引；
（4）如果你的字段类型是int，你传参的字符串，不会导致隐式转换索引失效，也会走索引（答但是不建议这么操作）；

参考链接：MySQL隐式转换造成索引失效 | JavaGuide