主页 > 知识库 > 记一次MySQL的优化案例

记一次MySQL的优化案例

热门标签:百度竞价排名 呼叫中心市场需求 铁路电话系统 AI电销 地方门户网站 网站排名优化 服务外包 Linux服务器

一  背景

      有赞的每个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 group by语句如何优化
  • 解决mysql 组合AND和OR带来的问题

标签:铜川 兰州 衡水 湘潭 仙桃 崇左 黄山 湖南

巨人网络通讯声明:本文标题《记一次MySQL的优化案例》,本文关键词  ;如发现本文内容存在版权问题,烦请提供相关信息告之我们,我们将及时沟通与处理。本站内容系统采集于网络,涉及言论、版权与本站无关。
  • 相关文章
  • 收缩
    • 微信客服
    • 微信二维码
    • 电话咨询

    • 400-1100-266