sql调优之:字符集不一致导致的索引失效案例

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: 生产上有一段sql,大概几百行,做的是inser into …select ;的操作,但是执行了一个多小时都出不来,正常情况下不会这么久,通过一步步定位,最后发现是其中某一段的join 特别慢导致的,后面对这段sql重点分析发现,这里的表竟然没有走索引,对于几十万以上的表之间的连接,若是没有索引那就是灾难性的,加不加索引就好比一个人力三轮与超跑的区别。

一、问题复盘与解决



1.问题描述


生产上有一段sql,大概几百行,做的是inser into …select ;的操作,但是执行了一个多小时都出不来,正常情况下不会这么久,通过一步步定位,最后发现是其中某一段的join 特别慢导致的,后面对这段sql重点分析发现,这里的表竟然没有走索引,对于几十万以上的表之间的连接,若是没有索引那就是灾难性的,加不加索引就好比一个人力三轮与超跑的区别。至于为什么没走索引最后定位到了是因为字符集与排序规则与其他表不一致,所以导致了索引失效。下面是犯罪现场:

..........
left join 
 (
  select
   tmvrr.project_code,
   COUNT(1) as project_abnormal_sum, 
   COUNT(DISTINCT tmvrr.customer_id, tmvrr.room_relation_id) as customer_room_car_sum, 
   sum(case when tmvrr.rule_type = '1' then 1 else 0 end) as project_accuracy, 
   sum(case when tmvrr.rule_type = '2' then 1 else 0 end) as project_integrity, 
   sum(case when tmvrr.rule_type = '3' then 1 else 0 end) as project_timeliness, 
   case when tmvrr.estate_type in (1, 3) then '住宅' else '非住宅' end as estate_type  
  FROM
   sunacwy_mdm.tb_mdm_verification_rules_result tmvrr
  left join sunacwy_mdm.tb_unqualify_sc_pool tusp on 
   tmvrr.project_code = tusp.project_code
   and tmvrr.customer_id = tusp.mdm_data_id
   and tmvrr.rule_id = tusp.rule_codes
   and tmvrr.room_relation_id = tusp.mdm_data_id2
  where
   tmvrr.group_code <> ''
   and (tusp.project_code is null or tmvrr.commit_content <> tusp.commit_content)  
  group by
   tmvrr.project_code,
   case when tmvrr.estate_type in (1, 3) then '住宅' else '非住宅' end 
 ) tmvrr on 
  base_org.project_code  = tmvrr.project_code and base_org.estate_type   = tmvrr.estate_type
 .......


2.问题解决


问题解决其实很简单,就是将所有字段的字符集和排序规则更改成了mysql5.7的默认值(笔者生产库是mysql5.7),字符集是utf8mb4,排序规则是utf8_general_ci,然后问题就解决了,更改完以后只能说是天差地别。之前几个小时出不来,更改后几分钟就ok了。

-- 更改全表的字符集与排序规则,除了字符串其他字段其实无需指定排序规则,指定了也不生效
ALTER TABLE tb_mdm_verification_rules_result CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ;
-- 单个字段的更改排序规则
alter table tb_mdm_verification_rules_result change group_name  group_name varchar(50) character set utf8mb4 collate utf8mb4_general_ci COMMENT '集团名称';


3.总结


其实产生原因很简单,但是找起来确实费了一番劲,这与explain使用不是这么熟练也是有关系的(当然最坑的是建表的人干得事)下面一节笔者会复现这个问题,并分析怎么排查这种问题,方便下次碰到一眼就可以解决。


二、mysql5.7与mysql8.0.3中问题复现



无论mysql5.7还是mysql8.0.3都具有相同的以下环境,我们就使用两个表的连接来复现该问题。

### 环境 预设###
表:tb_mdm_unit ,数据量:54280,索引字段:id
表:tb_mdm_floor,数据量:441849,索引字段:unit_id


1.mysql5.7环境问题复现


①相同字符集和排序规则时count


这里字符集都是utf8mb4,排序规则都是utf8mb4_general_ci

select count(1) from tb_mdm_unit un 
left join tb_mdm_floor fl on un.id = fl.unit_id ;


bf0cc80e73934da8ab618a8468562bf2.png


可以看到此时非常的快,只需要0.5s就出来结果了。


②看下相同字符集和排序规则的执行计划


07c0e8b64bbb41e3a6c11185a4168514.png


执行计划key列里是关联条件使用的索引,rows是索引关联到的行数(这个是效率高低的直观体现),Extra是非常重要的一列,sql真正底层在怎么运行是需要看这列的,Using index表示关联完全走了索引,是很高效的行为了。


③不相同字符集和排序规则count


alter table tb_mdm_floor change unit_id unit_id char(20) character set utf8 collate utf8_bin DEFAULT NULL COMMENT '单元id';
select count(1) from tb_mdm_unit un 
left join tb_mdm_floor fl on un.id = fl.unit_id ;


2315462b489e4ef5931de16021053421.png


可以看到此时是巨慢的,到达92s仍是没有出来结果,只能终止这个进程了。由此可见在更改了字符集和排序规则以后对sql的影响是灾难性的。


④看下不相同字符集和排序规则的执行计划


0e200161bf2240a88e4da068476b6e6e.png


对比这个执行计划与上面那个,可以发现 rows列和Extra列有很大区别,rows列的第二行,由6直接变成了435420,那这个数字意味着什么呢?这个问题先放下,可以先看下Extra列,这一列由Using index变成了Using where; Using index; Using join buffer (Block Nested Loop)。出现Using where 其实是因为mysql执行优化器对于这条sql进行了优化,优化后的sql 会在末尾拼接 “where 1”,在不走索引的查询中好像都会在末尾拼接这个。所以这里有个Using where(有兴趣的话,使用show warnings,即可看到这个执行优化器优化后的sql)。出现Using index是因为,确实走到了索引,但是因为字符集不一致即使走了也相当于没走,其实此时索引是不生效的。最后一个要说的也是最重要的是:Using join buffer (Block Nested Loop);当sql中出现外连时若是连接条件不是索引,Extra中就会提示这个提示(想要详细了解的建议看下这个博主的这篇文章写得还是不错的:MySQL Join原理),其实mysql5.5之前若是没有索引就是会出现笛卡尔积,mysql5.5之后8.0之前就是使用的Block Nested Loop来解决join时没有索引的问题,这种算法虽然比笛卡尔积效率高很多,但是对于大数据量的表来说还是无法承受的,所以上面的查询结果还是出不来,mysql8.0.19之后则采用了hash join 来解决连表时不走索引的问题。这种方式也很高效,效率其实是接近索引的。


2.mysql8.0环境问题复现


①相同字符集和排序规则时count


这里字符集都是utf8mb4,排序规则都是utf8mb4_general_ci(mysql8.0以后默认排序规则不是他了,这里为了验证保持统一)

select count(1) from tb_mdm_unit un 
left join tb_mdm_floor fl on un.id = fl.unit_id ;


下面是执行结果,可以看到0.49s很快就出来了


fc6b255be77c40958699338cc5c7727a.png


②看下相同字符集和排序规则的执行计划


7143c1ada8974e128c4da63b6709d001.png


Extra列都是Using index,说明连接完全走的索引,key列也没有区别,有点区别的是rows,可以发现第二行只有1,在5.7里这个数是6,不过这个区别和今天验证的问题没有关系,这个区别能说明,mysql8的索引比mysql5.7的索引更高效(所以建议能将mysql5.7升级到mysql8的就升级吧)。


③不相同字符集和排序规则count


alter table tb_mdm_floor change unit_id unit_id char(20) character set utf8 collate utf8_bin DEFAULT NULL COMMENT '单元id';
select count(1) from tb_mdm_unit un 
left join tb_mdm_floor fl on un.id = fl.unit_id ;

73076fdf0919411e928af5b4c72d37a5.png


在mysql8.0中将字符集更改过以后好像还是很快,只是比以前慢了一点,并没有像5.7那样根本出不来结果,这是为什么呢?其实mysql8以后(准确说是8.0.19以后)对不走索引的join进行了优化,如果么有索引那么就使用hash算法将等值连接的两个值都使用hash算法进行计算,然后使用计算后的结果进行连接(有点像是给你建了两个hash索引一样),这样也会提升很多的速度。具体看下执行计划。


④看下不相同字符集和排序规则的执行计划


c3b1e4d6223a41b582bdd9c478ae00ad.png


对比这个执行计划与mysql5.7同场景的执行计划可以发现,唯一的不同点就是 Using join buffer (Block Nested Loop) 变成了 Using join buffer (hash join)。上面也简单说了其实是mysql8对不走索引的等值连接进行的优化,等值连接的优化算法由Block Nested Loop变成了hash join。hash join 相当于是额外建立了两个hash索引,所以速度还是很快的,但是这个速度肯定不如走原来的索引快。


3.总结


left join 和right join 在 mysql5.7中若是连接条件不是索引,或者索引失效了,则会导致数据量的剧增,此时使用的连接算法BNL相当于是进行局部的笛卡尔积,效率也是很低的,到了mysql8.0以后,若是索引实现会使用hash算法对连接条件进行关联,这个效率是很高的,基本接近走索引的效率了。所以说升级到mysql8.0好处还是显而易见的(什么?msyql8.0还有什么优化?可以看看这一篇:关联子查询的前世今生),实在升级不了,那么我们就需要根据执行计划,来看连接表是否出现了BNL算法出现的情况,一旦出现请关注索引的建立和失效情况,不然sql的性能会非常低下。


4.延展问题:相同字符集不同排序规则会影响索引吗?


只调整排序规则,sql如下:

alter table tb_mdm_floor change unit_id unit_id char(20) character set utf8mb4 collate utf8mb4_polish_ci DEFAULT NULL COMMENT '单元id';
select count(1) from tb_mdm_unit un 
left join tb_mdm_floor fl on un.id = fl.unit_id ;


执行结果如下,直接报错,事实上笔者换了多个排序规则,都是会报这个错误,所以相同字符集不同排序规则会不会导致索引失效,直接就不用看了,因为sql执行不了。7f598810c838483fb532ab67ce0e038f.png


4.延展问题:字符集为什么导致索引失效?


其实很简单,两个字段不是使用字符编码,就没办法判断相等,建立的索引其实也是一个列,这个列的字符集合别的列不同,那就无法比较是否相等,自然就会造成索引失效了。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
21天前
|
SQL 数据库
数据库数据恢复—SQL Server数据库报错“错误823”的数据恢复案例
SQL Server附加数据库出现错误823,附加数据库失败。数据库没有备份,无法通过备份恢复数据库。 SQL Server数据库出现823错误的可能原因有:数据库物理页面损坏、数据库物理页面校验值损坏导致无法识别该页面、断电或者文件系统问题导致页面丢失。
88 12
数据库数据恢复—SQL Server数据库报错“错误823”的数据恢复案例
|
27天前
|
存储 SQL 关系型数据库
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
MySQL调优主要分为三个步骤:监控报警、排查慢SQL、MySQL调优。 排查慢SQL:开启慢查询日志 、找出最慢的几条SQL、分析查询计划 。 MySQL调优: 基础优化:缓存优化、硬件优化、参数优化、定期清理垃圾、使用合适的存储引擎、读写分离、分库分表; 表设计优化:数据类型优化、冷热数据分表等。 索引优化:考虑索引失效的11个场景、遵循索引设计原则、连接查询优化、排序优化、深分页查询优化、覆盖索引、索引下推、用普通索引等。 SQL优化。
214 15
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
|
27天前
|
存储 关系型数据库 MySQL
MySQL高级篇——覆盖索引、前缀索引、索引下推、SQL优化、主键设计
覆盖索引、前缀索引、索引下推、SQL优化、EXISTS 和 IN 的区分、建议COUNT(*)或COUNT(1)、建议SELECT(字段)而不是SELECT(*)、LIMIT 1 对优化的影响、多使用COMMIT、主键设计、自增主键的缺点、淘宝订单号的主键设计、MySQL 8.0改造UUID为有序
MySQL高级篇——覆盖索引、前缀索引、索引下推、SQL优化、主键设计
|
18天前
|
SQL 存储 索引
SQL Server的Descending Indexes降序索引
【9月更文挑战第21天】在SQL Server中,降序索引允许指定列的排序顺序为降序,可显著优化涉及降序排序的查询性能,特别是在复合索引中。通过创建降序索引,可以更高效地满足特定业务需求,如按交易时间降序获取最新记录。然而,使用时需考虑查询频率、数据分布及维护成本,以确保最佳性能。
|
29天前
|
SQL 分布式计算 数据库
SQL调优总结
数据库表的规范化和反规范化设计,设计合适的字段数据类型……
38 8
|
2月前
|
Java XML Maven
跨越时代的飞跃:Struts 2 升级秘籍——从旧版本无缝迁移到最新版,焕发应用新生!
【8月更文挑战第31天】随着软件技术的发展,Struts 2 框架也在不断更新。本文通过具体案例指导开发者如何从旧版平滑升级到 Struts 2.6.x。首先更新 `pom.xml` 中的依赖版本,并执行 `mvn clean install`。接着检查 `struts.xml` 配置,确保符合新版本要求,调整包扫描器等设置。审查 Action 类及其注解,检查配置文件中的弃用项及插件。更新自定义拦截器实现,并验证日志配置。最后,通过一系列测试确保升级后的系统正常运行。通过这些步骤,可以顺利完成 Struts 2 的版本升级,提升应用的安全性和性能。
106 0
|
2月前
|
Java 开发者 前端开发
Struts 2、Spring MVC、Play Framework 上演巅峰之战,Web 开发的未来何去何从?
【8月更文挑战第31天】在Web应用开发中,Struts 2框架因强大功能和灵活配置备受青睐,但开发者常遇配置错误、类型转换失败、标签属性设置不当及异常处理等问题。本文通过实例解析常见难题与解决方案,如配置文件中遗漏`result`元素致页面跳转失败、日期格式不匹配需自定义转换器、`&lt;s:checkbox&gt;`标签缺少`label`属性致显示不全及Action中未捕获异常影响用户体验等,助您有效应对挑战。
73 0
|
2月前
|
SQL 存储 数据库
|
2月前
|
存储 SQL 数据库
深入解析SQL中的聚集索引与非聚集索引
【8月更文挑战第31天】
101 0
|
2月前
|
SQL 监控 关系型数据库
SQL性能监控与调优工具的神奇之处:如何用最佳实践选择最适合你的那一个,让你的数据库飞起来?
【8月更文挑战第31天】在现代软件开发中,数据库性能监控与调优对应用稳定性至关重要。本文对比了数据库内置工具、第三方工具及云服务工具等几种常用SQL性能监控与调优工具,并通过示例代码展示了如何利用MySQL的EXPLAIN功能分析查询性能。选择最适合的工具需综合考虑功能需求、数据库类型及成本预算等因素。遵循了解工具功能、试用工具及定期维护工具等最佳实践,可帮助开发者更高效地管理和优化数据库性能,迎接未来软件开发中的挑战与机遇。
40 0