记一次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=字符串，再次执行

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

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的执行计划并对比，结果显示

看到结果我表示

翻阅 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优化案例的资料请关注我们其它相关文章！

赞 (0)

相关推荐

  • MySQL的id关联和索引使用的实际优化案例

    MySQL的id关联和索引使用的实际优化案例

    昨晚收到客服MM电话,一用户反馈数据库响应非常慢,手机收到load异常报警,登上主机后发现大量sql执行非常慢,有的执行时间超过了10s 优化点一: SELECT * FROM `sitevipdb`.`game_shares_buy_list` WHERE price>='2.00′ ORDER BY tran_id DESC LIMIT 10; 表结构为: CREATE TABLE `game_shares_buy_list` ( `tran_id` int(10) unsigned NOT

  • MySQL下的RAND()优化案例分析

    众所周知,在MySQL中,如果直接 ORDER BY RAND() 的话,效率非常差,因为会多次执行.事实上,如果等值查询也是用 RAND() 的话也如此,我们先来看看下面这几个SQL的不同执行计划和执行耗时. 首先,看下建表DDL,这是一个没有显式自增主键的InnoDB表: [yejr@imysql]> show create table t_innodb_random\G *************************** 1. row *************************

  • MySQL优化案例系列-mysql分页优化

    通常,我们会采用ORDER BY LIMIT start, offset 的方式来进行分页查询.例如下面这个SQL: SELECT * FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 100, 10; 或者像下面这个不带任何条件的分页SQL: SELECT * FROM `t1` ORDER BY id DESC LIMIT 100, 10; 一般而言,分页SQL的耗时随着 start 值的增加而急剧增加,我们来看下面这2个不同起始值的分页SQL执行

  • Mysql性能优化案例研究-覆盖索引和SQL_NO_CACHE

    Mysql性能优化案例研究-覆盖索引和SQL_NO_CACHE

    场景 产品中有一张图片表pics,数据量将近100万条,有一条相关的查询语句,由于执行频次较高,想针对此语句进行优化 表结构很简单,主要字段: 复制代码 代码如下: user_id 用户ID picname 图片名称 smallimg 小图名称 一个用户会有多条图片记录,现在有一个根据user_id建立的索引:uid,查询语句也很简单:取得某用户的图片集合: 复制代码 代码如下: select picname, smallimg from pics where user_id = xxx; 优化

  • MySQL索引优化的实际案例分析

    MySQL索引优化的实际案例分析

    Order by desc/asc limit M是我在mysql sql优化中经常遇到的一种场景,其优化原理也非常的简单,就是利用索引的有序性,优化器沿着索引的顺序扫描,在扫描到符合条件的M行数据后,停止扫描:看起来非常的简单,但是我经常看到很多性能较差的sql没有利用这个优化规律,下面将结合一些实际的案例来分析说明: 案例一: 一条sql执行非常的慢,执行时间为: root@test 02:00:44 SELECT * FROM test_order_desc WHERE END_TIME>

  • 一个优化MySQL查询操作的具体案例分析

    问题描述 一个用户反映先线一个SQL语句执行时间慢得无法接受.SQL语句看上去很简单(本文描述中修改了表名和字段名): SELECT count(*) FROM a JOIN b ON a.`S` = b.`S` WHERE a.`L` > '2014-03-30 00:55:00' AND a.`L` < '2014-03-30 01:00:00' ; 且查询需要的字段都建了索引,表结构如下: CREATE TABLE `a` ( `L` timestamp NOT NULL DEFAULT

  • Mysql性能优化案例 - 覆盖索引分享

    Mysql性能优化案例 - 覆盖索引分享

    场景 产品中有一张图片表,数据量将近100万条,有一条相关的查询语句,由于执行频次较高,想针对此语句进行优化 表结构很简单,主要字段: 复制代码 代码如下: user_id 用户ID picname 图片名称 smallimg 小图名称 一个用户会有多条图片记录 现在有一个根据user_id建立的索引:uid 查询语句也很简单:取得某用户的图片集合 复制代码 代码如下: select picname, smallimg from pics where user_id = xxx; 优化前 执行查

  • 记一次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

  • MySQL 索引优化案例

    MySQL 索引优化案例

    目录 数据准备 联合索引的首字段用范围查询 强制走索引 覆盖索引优化 in和or什么时候会走索引 like xx% 一般都会走索引,和数据量无关 索引下推 为什么范围查找没有用索引下推优化? 如何选择索引 Trace 工具 深入优化 order by 和 group by 优化总结 Using filesort文件排序原理详解 单路排序模式: 双路排序(又叫回表排序模式) 分页优化 常规的limit分页 优化 根据主键排序 非主键排序的优化 表关联优化 常见的表关联算法 内嵌循环连接算法 基于块

  • MYSQL 的10大经典优化案例场景实战

    MYSQL 的10大经典优化案例场景实战

    目录 一.SQL优化一般步骤 1.explain 分析SQL的执行计划 2.show profile 分析 3.trace 4.确定问题并采用相应的措施 二.场景分析(案例) 1. 最左匹配 2.隐式转换 3.大分页 4.in + order by 5.范围查询阻断,后续字段不能走索引 6.不等于.不包含不能用到索引的快速搜索 7.优化器选择不使用索引的情况 8.复杂查询 9.asc和desc混用 10.大数据 一.SQL优化一般步骤 通过慢查日志等定位那些执行效率较低的SQL语句 1.expl

  • MySQL组合索引(多列索引)使用与优化案例详解

    MySQL组合索引(多列索引)使用与优化案例详解

    目录 1.多列索引 2.测试案例及过程 2.1 创建一个测试数据库和数据表 2.2 添加两个单列索引 2.3 查询一条数据利用到两个列的索引 2.4 查看执行计划 2.5 然后删除以上索引,添加多列索引 2.6 再次查询 3.多列索引的使用顺序 3.1 怎么选择建立组合索引时,列的顺序 3.2 组合索引的使用规则 1.多列索引 我们经常听到一些人说"把WHERE条件里的列都加上索引",其实这个建议非常错误. 在多个列上建立单独的索引大部分情况下并不能提高MySQL的查询性能.MySQL

  • MySQL优化案例之隐式字符编码转换

    MySQL优化案例之隐式字符编码转换

    目录 索性失效前提 一个真实的案例 优化前原始sql分析 优化初步处理 初步优化无效分析 第二次优化处理 第三次优化 结论 索性失效前提 MySQL中我们知道有: 1.如果对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能. 2.隐式类型转换也会导致同样的放弃走树搜索. 因为类型转换等价于在条件字段上使用了函数比如: /*假设tradeid字段有索引,且为varchar类型*/ mysql> select * from tradelog where tradeid=

  • MySQL数据库优化之分表分库操作实例详解

    本文实例讲述了MySQL数据库优化之分表分库操作.分享给大家供大家参考,具体如下: 分表分库 垂直拆分 垂直拆分就是要把表按模块划分到不同数据库表中(当然原则还是不破坏第三范式),这种拆分在大型网站的演变过程中是很常见的.当一个网站还在很小的时候,只有小量的人来开发和维护,各模块和表都在一起,当网站不断丰富和壮大的时候,也会变成多个子系统来支撑,这时就有按模块和功能把表划分出来的需求.其实,相对于垂直切分更进一步的是服务化改造,说得简单就是要把原来强耦合的系统拆分成多个弱耦合的服务,通过服务间的

  • MySQL如何优化查询速度

    前面章节我们介绍了如何选择优化的数据类型.如何高效的使用索引,这些对于高性能的MySQL来说是必不可少的. 但这些还完全不够,还需要合理的设计查询. 如果查询写的很糟糕,即使表结构再合理.索引再合适,也是无法实现高性能的. 谈到MySQL性能优化,查询优化作为优化的源头,它也是最能体现一个系统是否更快. 本章以及接下来的几章将会着重讲解关于查询性能优化的内容,从中会介绍一些查询优化的技巧,帮助大家更深刻地理解MySQL如何真正地执行查询.究竟慢在哪里.如何让其快起来,并明白高效和低效的原因何在,

随机推荐