MySQL优化案例之隐式字符编码转换
因为类型转换等价于在条件字段上使用了函数比如:
/*假设tradeid字段有索引,且为varchar类型*/mysql select * from tradelog where tradeid=110717;
/*等价于*/
mysql select * from tradelog where CAST(tradid AS signed int) = 110717; 一个真实的案例
下面来看看隐式字符编码转换导致的一个慢sql
优化前原始sql分析业务上有个sql执行需要1.31秒
看看执行计划:
从执行计划分析看出问题出在r表也就是 h_merge_result_new_indicator 表全表扫描,查看该表的表结构有联合索引。但是联合索引范围后会失效,于是打算新建一个联合索引。
优化初步处理查看预新建联合索引的字段选择性:
结合选择性来看;
create index idx_hmrni on h_merge_result_new_indicator(keyName,module,BATCH_NO);
初步优化无效分析创建后,再次查看执行计划依然无效;
查看表结构:
另外3个表结构其中有2个utf8mb4,1个utf8
字符集 utf8mb4 是 utf8 的超集,所以当这两个类型的字符串在做比较的时候,MySQL 内部的操作是,先把 utf8 字符串转成 utf8mb4 字符集,再做比较。
因此:
这部分会转换后再与h_merge_result_new_indicator关联
第二次优化处理优化就只需要将字符集编码转为utf8再和h_merge_result_new_indicator关联就能用上索引
再看查询只需要0.02秒了
但是还有个问题,如上执行计划key_len是606 =(100*3+3)+(100*3+3)
也就是说,没有用上BATCH_NO字段上的索引,我们知道索引少一个字段,占用会减少,不会太臃肿,因此,联合索引只需要包含r(keyName,module)
drop index idx_hmrni on h_merge_result_new_indicator; create index idx_hmrni on h_merge_result_new_indicator(keyName,module);对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能。该例子是隐式字符编码转换,它们都跟其他条件索引上使用函数一样,因为要求在索引字段上做函数操作而导致了全索引扫描。
MySQL 的优化器确实有 偷懒 的嫌疑,即使简单地把 where id+1=1000 改写成 where id=1000-1 就能够用上索引快速查找,也不会主动做这个语句重写。
保证在条件索引上不做破坏索引值的有序性,是优化索引的利器。
到此这篇关于MySQL优化案例之隐式字符编码转换的文章就介绍到这了,更多相关MySQL隐式字符编码转换内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持!
我想要获取技术服务或软件
服务范围:MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式:远程服务、电话支持、现场服务,沟通指定方式服务
技术标签:数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题
本站部分文章参考或来源于网络,如有侵权请联系站长。
数据库远程运维 MySQL优化案例之隐式字符编码转换
相关文章
- 探索MySQL中的递归树形结构(mysql递归树形结构)
- MySQL循环遍历 —— 实现无缝执行(mysql循环遍历)
- MySQL合并列值 – 一种有效解决方案(mysql列值合并)
- 实现MySQL快速数据库导入(数据库导入mysql)
- 完美开启MySQL开发之旅:安装MySQL开发包(安装mysql开发包)
- 转换MySQL命令行实现编码转换(mysql命令行编码)
- MySQL主从不一致问题解决之道(mysql主从不一致)
- MySQL中的时间转换技术(mysql时间转换)
- MySQL回滚操作的失败案例(mysql回滚失败)
- 解决MySQL日志乱码问题(mysql日志乱码)
- 如何在MySQL中建立数据库?(mysql建立数据库)
- MySQL分区查询优化技术研究(mysql分区查询语句)
- MySQL锁表解锁:避免数据修改时出现冲突(mysql锁表解锁)
- MySQL如何删除表中的主键?(mysql删除表的主键)
- Mysql中计算两个值的差值(mysql 两个值相减)
- MySQL怎样将字符转换为大写(mysql转换大写)
- MySQL从二进制转变,让我们轻松转换!(mysql转换二进制)
- 深入研究C语言与MySQL交互(c mysql 转议)
- MySQL进制转换方法解析(mysql不同进制转换)
- 解析MySQL非可重复读的案例(mysql不可重复读实例)
- 详解MySQL下载安装后启动步骤(mysql下载安装怎么打开)
- MYSQL离线使用方法大揭秘不联网也能愉快地使用MySQL(mysql 不联网吗)