如何优化MySQL

发布时间：2020-10-28 21:31:47 作者：Leah
来源：亿速云阅读：140

如何优化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索引，

如何优化MySQL

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

如何优化MySQL

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

a 那么为啥当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的执行计划并对比，结果显示

如何优化MySQL

看到结果我表示

如何优化MySQL

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

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

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

如何优化MySQL

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

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

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

where name = 123

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

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

a 表 order_no 是utf8mb4 ,b 表order_no 是 utf8

看完上述内容，你们掌握如何优化MySQL的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注亿速云行业资讯频道，感谢各位的阅读！

如何优化MySQL

相关阅读