记一次MySQL的优化案例

数据库 2024/12/23 佚名

3 1 2

一背景

有赞的每个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

感兴趣的朋友可以多测试，有其他案例的欢迎讨论。

以上就是记一次MySQL的优化案例的详细内容，更多关于MySQL优化案例的资料请关注其它相关文章！

MySQL,优化,MySQL,优化案例

华山资源网 Design By www.eoogi.com

广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除！

华山资源网 Design By www.eoogi.com

评论“记一次MySQL的优化案例”

暂无评论...

记一次MySQL的优化案例

MySQL 8.0.19安装详细教程(windows 64位)

MySQL group by和order by如何一起使用

评论“记一次MySQL的优化案例”

更新日志

友情链接