mysql优化方案最新(记一次MySQL的优化案例)
mysql优化方案最新
记一次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优化案例的资料请关注开心学习网其它相关文章!
原文链接:https://mp.weixin.qq.com/s?__biz=MzI4NjExMDA4NQ==&mid=2648450549&idx=1&sn=475067207fc111af7244570b9014f87a&chksm=f3c97d1fc4bef409924c983edc7010b8c9c5427090ef2e6bc964fc6d118fd830635eeac42493&scene=21#wechat_redirect
- mysql 主从配置详解(MySQL 8.0.15配置MGR单主多从的方法)
- mysql配置多实例
- mysql索引知识点总结(MySQL 8.0 之索引跳跃扫描Index Skip Scan)
- centos7怎么安装mysql(Centos7 mysql数据库安装及配置实现教程)
- 将SQL Server数据迁移到MySQL的常见工具
- mysql双向同步原理(详解MySQL的半同步)
- mysql查询killed状态的进程(MySQL kill指令使用指南)
- mysql定时任务
- docker怎么创建mysql服务(Docker部署MySQL8集群一主二从的实现步骤)
- mysqlgroupby用法(MySQL Group by的优化详解)
- mysql恢复数据库(MySQL 两种恢复数据的方法)
- MySQL中对varchar类型的排序
- mysql访问被拒绝(nacos无法连接mysql的解决方法)
- mysql 慢查询日志
- mysql常用的sql语句大全(mysql建表常用的sql语句汇总)
- mysql死锁是什么原因(MySQL的MaxIdleConns不合理,会变成短连接的原因)
- 白龙马的改编学生版,快来看看(白龙马的改编学生版)
- 萌娃唱《白龙马》走红,那生动的小表情,网友直呼 简直是戏精(萌娃唱白龙马走红)
- 朱鹤松被不断认可,凤凰传奇玲花喊话岳云鹏,索要老朱演出门票(朱鹤松被不断认可)
- 元宵晚会槽点多,芒果台上来就假唱,岳云鹏不说相声改评书了(元宵晚会槽点多)
- 岳云鹏跟凤凰传奇谈心,说出了人生中最重要的三个人,这才成功(岳云鹏跟凤凰传奇谈心)
- 爱情可以当饭吃吗(爱情能当饭吃吗)
热门推荐
- python代码添加微信号(python 获取微信好友列表的方法微信web)
- laravel模型详解(laravel config文件配置全局变量的例子)
- php 模拟登录验证码demo(PHP 模拟登陆功能实例详解)
- mysql效率查询(MySQL 如何分析查询性能)
- docker镜像配置的挂载路径(docker挂载本地目录和数据卷容器操作)
- 数据库mysql基本知识(深入理解r2dbc在mysql中的使用)
- 前端用图片隐藏请求接口(前端实现打印图像功能)
- python生成密码字典教程(Python利用字典破解WIFI密码的方法)
- python获取网络数据tcp(Python选择网卡发包及接收数据包)
- sql注入防范有哪些方法(如何有效防止sql注入的方法)