MySQL · 最佳实践 · 一个“异常”的索引选择

简介:
+关注继续查看

背景

在处理一个用户性能问题的时候, 发现有一个全表扫描语句, 上下文如下: 这是很奇怪的,

11


Tips: MySQL在执行条件判断时,若参数类型与字段类型不匹配, 则会作类型转换, 符合转换规则的, 转换完成后可以利用索引

而当参数为字符串,字段类型为整型时, 这个转换是成立的, 比如这个case.

12


因此我们有必要查一下是什么原因.

分析

在优化器执行流程中, 要选择索引, 需要先判断where的部分的条件操作能否使用索引.经典的有,

Tips: 在字段上作函数操作,是无法使用索引的. 比如 where c+1 = 10

因此怀疑是在判断过程出了什么岔子.

调用栈

mysql_select->mysql_execute_select->JOIN::optimize->get_quick_record_count->SQL_SELECT::test_quick_select->get_mm_tree->get_full_func_mm_tree->get_func_mm_tree

一路下来都是预期内调用,直到 get_func_mm_tree返回了 NULL, 表示这个语句无法使用索引.

然后我们发现了这样一段代码,

13


简单说,就是在IN的入口有一个判断, 如果in中的字段类型不兼容, 则认为不可使用索引. 而这个arg_types_compatible 的赋值逻辑是: if (type_cnt == 1) arg_types_compatible = TRUE; 也就是说,当IN列表中出现超过一个字段类型时, 就认为类型不兼容.

这个本来是一个优化操作, 提前判断避免额外的后续判断.

对照实验

至此,我们就清楚, 问题来自于IN和列表中的类型问题. 以下几个对照试验可以作为验证:

where id in ('1', '1') 可以使用索引

where id = '1' or id = 1 可以使用索引

是不是bug?

如果把他当作bug处理,只需要在get_func_mm_tree函数中去掉上述最后一个图中的判断逻辑即可, 有兴趣的读者可以自己注释后验证.

前面的用户场景使用的是5.6,作为验证,我们测试了5.7版本,结果发现,5.7中可以相同的语句序列使用此索引!

翻了代码发现果然是去掉了这个判断.

但有趣的是, 官方并不是为了解决这个问题而作的修改, 实际上是在支持(a,b) in (X, Y)这样的语法时,由于这个语句就不能要求XY必须相同类型,因此去掉了这个判断, “阴差阳错”把这个问题改过来了.

翻了下commit log, 发现在5.7.3修复的, 因5.7.3之后,文章开头的这个例子的优化器结果就变了.

更多

所以我们看到, 实际上即使是小版本, 也是可能改变程序行为的.

而版本更新,尤其是要使用官方最新版本的特性, 有时候是必须作程序升级的.

而即使是小版本号的不同, 也可能对业务造成影响.

上述这个需求和现状,其实是有矛盾的, 运维的同学们可以考虑下, 如何处理这种潜在风险呢?

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2月前
|
关系型数据库 MySQL Java
家人们,谁懂啊!mysql 版本5.7.99连接异常
在项目工作中需要部署nacos,数据库使用的是别的公司提供的mysql,本来挺好部署的一个服务却被一个报错打破,异常如下:
37 0
家人们,谁懂啊!mysql 版本5.7.99连接异常
|
3月前
|
SQL 关系型数据库 MySQL
Mysql 异常:Lock wait timeout exceeded; try restarting transaction的解决办法
Mysql 异常:Lock wait timeout exceeded; try restarting transaction的解决办法
|
5月前
|
关系型数据库 MySQL 数据库
一文剖析MySQL主从复制异常错误代码13114
一文剖析MySQL主从复制异常错误代码13114
124 0
|
5月前
|
SQL 关系型数据库 MySQL
MySQL主主SQL线程异常修复大作战,一失足成千古恨啊!
MySQL主主SQL线程异常修复大作战,一失足成千古恨啊!
182 0
|
5月前
|
SQL 监控 关系型数据库
MySQL主从复制“死掉”!引发Slave库SQL线程异常的一次“血案”追踪
MySQL主从复制“死掉”!引发Slave库SQL线程异常的一次“血案”追踪
316 0
|
10月前
|
SQL JavaScript 前端开发
两个相同的负载user在一起启动的时候,造成相关接口调用第一次报异常 调用第二次正常 如此反反复复 解决方法;mysql复习、JavaScript HTML BOM和DOM触发监听机制事件
两个相同的负载user在一起启动的时候 造成相关接口调用第一次报异常 调用第二次正常 如此反反复复 解决方法 放掉一个实例个数
80 0
两个相同的负载user在一起启动的时候,造成相关接口调用第一次报异常 调用第二次正常 如此反反复复 解决方法;mysql复习、JavaScript HTML BOM和DOM触发监听机制事件
|
11月前
|
IDE 关系型数据库 MySQL
MySQL 异常:这一篇就够了,MySQL 抛出异常的几种常见解决方式小结
MySQL 异常:这一篇就够了,MySQL 抛出异常的几种常见解决方式小结
310 0
MySQL 异常:这一篇就够了,MySQL 抛出异常的几种常见解决方式小结
|
12月前
|
关系型数据库 MySQL Linux
MySQL压测时Linux中断异常飚高,原来是因为...
MySQL压测时Linux中断异常飚高,原来是因为...
|
关系型数据库 MySQL
MySQL主从异常Coordinator stopped because there were error(s) in the worker(s). The most recent failur
MySQL主从异常Coordinator stopped because there were error(s) in the worker(s). The most recent failur
2664 0
|
SQL 安全 关系型数据库
关于MYSQL不允许修改和删除的异常报错解决方法
关于MYSQL不允许修改和删除的异常报错解决方法
221 0
推荐文章
更多