记一次MySQL的优化案例

数据库发布日期：2024/11/12 浏览次数：1

正在浏览：记一次MySQL的优化案例

一背景

有赞的每个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 8.0.19安装详细教程(windows 64位)

下一篇：MySQL group by和order by如何一起使用

一句话新闻

一文看懂荣耀MagicBook Pro 16

荣耀猎人回归！七大亮点看懂不只是轻薄本，更是游戏本的MagicBook Pro 16.
人们对于笔记本电脑有一个固有印象：要么轻薄但性能一般，要么性能强劲但笨重臃肿。然而，今年荣耀新推出的MagicBook Pro 16刷新了人们的认知——发布会上，荣耀宣布猎人游戏本正式回归，称其继承了荣耀 HUNTER 基因，并自信地为其打出“轻薄本，更是游戏本”的口号。
众所周知，寻求轻薄本的用户普遍更看重便携性、外观造型、静谧性和打字办公等用机体验，而寻求游戏本的用户则普遍更看重硬件配置、性能释放等硬核指标。把两个看似难以相干的产品融合到一起，我们不禁对它产生了强烈的好奇：作为代表荣耀猎人游戏本的跨界新物种，它究竟做了哪些平衡以兼顾不同人群的各类需求呢？