记一次MySQL的优化案例

原作者: [db:作者] 来自: [db:来源] 收藏邀请

一背景

有赞的每个OLTP数据库实例上会设置一个sql-killer进程用于kill 掉执行时间超过一定阈值的sql。下午开发接收到sql被kill的报错，一起帮助开发排查，本文介绍该案例。

二场景分析

表结构:

CREATE TABLE `xxx_info` (

  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',

  `user_id` bigint(20) unsigned NOT NULL DEFAULT '0' ,

  `group_id` bigint(20) unsigned NOT NULL DEFAULT '0',

  `nick_name` varchar(30) NOT NULL DEFAULT '' COMMENT '昵称',

  `is_del` tinyint(5) NOT NULL DEFAULT '0' COMMENT '0：数据有效、1：数据逻辑删除',

  `created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',

  `updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',

  PRIMARY KEY (`id`),

  KEY `idx_userid_groupid` (`user_id`,`group_id`)

) ENGINE=InnoDB AUTO_INCREMENT=1382032 DEFAULT CHARSET=utf8mb4 ;

问题sql如下

 SELECT id, name,status FROM xxx_info WHERE user_id IN (670039223,'373149878') AND group_id = 1 AND is_del = 0;

第一眼看到sql ，先检查了表结构和索引 user_id 是数值类型的，且索引ok 然后手工执行计划竟然没有走idx_userid_groupid索引，

怀疑 user_id in 两种不同类型的字段导致"隐式转换",将其中参数值都换为数值类型或者字符串或者使用 user_id=数值类型 or user_id=字符串，再次执行

执行计划都是正确。对此我们要解决两个问题

那么为啥当user_id in (X,Y,Z) 是不同类型时，就不走索引了呢？

我们使用optimizer_trace 来跟踪执行计划。

set session optimizer_trace='enabled=on';

SELECT id, nick_name,is_del  FROM xxx_info WHERE user_id IN (670039223,'373149878') AND group_id = 1 AND is_del = 0;

select * from information_schema.optimizer_trace;

SELECT id, nick_name,is_del FROM xxx_info WHERE user_id IN (670039223,'373149878') AND group_id = 1 AND is_del = 0;

select * from information_schema.optimizer_trace;

set session optimizer_trace='enabled=off';

获取两个sql的执行计划并对比，结果显示

看到结果我表示

翻阅 https://bugs.mysql.com 还没找到相关结果。

代码里面如何产生不同类型的值？

以下是开发(阿杜)自己的测试

目前的解决方式是和开发同学沟通让他们在程序做参数类型一致性校验，都转换为 int/long 类型。

特别提醒常见发生隐式转换导致索引失效的场景

1 where 判断符号左边是字符串，右边是数值比如

where name = 123

2 多表join关联条件的字段类型不一致，类似于 1

3 多表join关联条件字符集类型不一样。比如

a 表 order_no 是utf8mb4 ,b 表order_no 是 utf8