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

俊达2周前技术文章16

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


一个例子

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字段上的索引。解决了查询的性能问题。


总结

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

相关文章

大数据高可用系列--kudu高可用应急方案

大数据高可用系列--kudu高可用应急方案

1 设置机架感知1.1 前置说明    1.9版本后的kudu已经支持机架感知(cdh6之后的版本中的kudu已支持),由于kudu的每个Tablet一般是三副...

CDH实操--kudumaster迁移

CDH实操--kudumaster迁移

1 概述本次kudumaster迁移,中间不需要停kudu集群(会涉及滚动重启kudu角色); 注:若因为任务持续运行导致kudu停止超时可手动一台台停止-启动2 master迁移将cdh2中的ma...

kubebuilder 开发operator初探

1、使用kubebuilder初始化$ mkdir project$ cd project$ kubebuilder init --domain tutorial.kubebuilder.io --r...

Clickhouse MergeTree 原理(一)

Clickhouse MergeTree 原理(一)

MergeTree是Clickhouse里最核心的存储引擎。Clickhouse里有一系列以MergeTree为基础的引擎(见下图),理解了基础MergeTree,就能理解整个系列的MergeTree...

CDP实操--集群扩容

CDP实操--集群扩容

一、前提准备工作1.确保OS的yum源可以正常使用,通过yum repolist命令可以查看到匹配的OS的所有包2.确保Cloudera Manager的yum源运行正常3.hosts文件配置,需要将...

harbor数据迁移-SOP

harbor数据迁移-SOP

背景线下自建harbor需要迁移至云上自建harbor迁移方案harbor私有仓库的主从复制实现数据迁移前置条件harbor目标仓库已部署好,并且版本和源仓库版本最好保持一致迁移步骤1、配置slave...

发表评论    

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