MySQL性能优化(五)字符集不一致导致的隐式类型转换

俊达2年前技术文章986

上一篇文章中,我们介绍了隐式类型转换。这里我们介绍另一种形式的隐式类型转换,由于字符集不一致,导致关联查询无法使用索引。


一个例子

SELECT * 
FROM funds
WHERE  uuid  in ( SELECT uuid FROM patients WHERE create_at != "0000-00-00 00:00:00" )


执行计划



首先优化器对查询做了一个转换,将in查询转换成关联查询

被驱动表funds的uuid上有唯一索引,但是执行计划中显示没有使用索引。


查看执行计划,

explain extended 
    select b.*
    from patients a, funds b
    where a.create_at != "0000-00-00 00:00:00" 
    and a.uuid=b.uuid
    
    
show warnings

select *
from patients a
     join funds b
where((a.create_at <> '0000-00-00 00:00:00')
and(a.uuid= convert(b.uuid using utf8mb4)))


发现对funds.uuid字段进行了类型转换。


我们查看一下表结构的定义,发现uuid的字符集不一致:

 CREATE TABLE `funds` ( 
     `id` int(11) NOT NULL AUTO_INCREMENT,  
     `uuid` varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'UUID',
      …,
 PRIMARY KEY (`id`),  
UNIQUE KEY `uuid_idx` (`uuid`),) 
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE `patients` ( 
     `id` int(11) NOT NULL AUTO_INCREMENT,  
     `uuid` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '项目uuid',  
PRIMARY KEY (`id`),) 
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4


funds.uuid 是utf8,而patients.uuid是utf8mb4。



我们在SQL中做一个处理,将驱动表的uuid字段字符集转换成utf8,匹配被驱动表的uuid字符集:

explain extended
 SELECT b.*
FROM (select convert(uuid using utf8) COLLATE utf8_unicode_ci as uuid
    from patients 
    where project_create_at != "0000-00-00 00:00:00") a, funds b
WHERE a.uuid = b.uuid


5-2.jpg


对SQL进行改写之后,可以看到,可以使用uuid字段上的索引。解决了查询的性能问题。


总结

同一个业务字段,在不同的表中数据类型和字符集要保持一致。这样可以避免表关联的时候发生隐式类型转换。

相关文章

MongoDB的索引(二)

四、Case Insesitive索引1、语法db.collection.createIndex(  { "key" : 1 }, { collation: {locale : <local...

CDP实操--配置Ranger对hive行过滤(七)

CDP实操--配置Ranger对hive行过滤(七)

1.使用sam_sec查询person表2.用sam_sec认证.3.查询person表:4. 配置行过滤策略。5. 新增策略:6. 过滤anna:7. 查询测试。...

MySQL性能优化(六)优化or条件

MySQL性能优化(六)优化or条件

优化器是数据库中非常核心,又非常复杂的一个组件。有的SQL,优化器选择的执行计划并不是最优的,通过改写SQL,可以帮助优化器找到最优的执行计划。where条件中的or子句,是比较容易出问题的一个场景。...

EasyMR之Yarn资源队列管理

EasyMR之Yarn资源队列管理

设想一下,你现在所在的公司有一套线上的hadoop集群。A部门经常做一些定时的BI报表,B部门则经常使用一些软件做一些临时需求。那么他们肯定会遇到同时提交任务的场景,这个时候到底如何分配资源满足这两个...

hiveserver2高可用

hiveserver2高可用

一、安装hiveserver2服务步骤1. 将正常使用的hive目录复制到安装hiveserver2的节点(hd3节点)scp -r /opt/hive hd3:/opt/二、配置hive-site....

MySQL优化器特性(一)IN和Exists(semijoin)子查询优化策略

这篇文章中的SQL和执行计划在mysql 8.0.31环境下进行测试。测试的表结构和数据:表结构mysql> show create table tp\G...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。