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

俊达1年前技术文章345

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


一个例子

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


总结

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

相关文章

CPU--使用率

CPU--使用率

一、CPU和任务统计信息查询/proc/stat第一行表示所有CPU的累加其他列表示不同场景下CPU的累加节拍数,单位:USER_HZ即10ms➜  ~ cat ...

Golang 垃圾回收

Golang 垃圾回收

1、标记清除算法Golang 使用标记清除算法作为垃圾回收器的一部分。标记清除算法是一种常见的垃圾回收算法,它通过标记和清除未被引用的对象来回收内存空间。Golang 中,垃圾回收器会定期扫描堆空间,...

kubernetes openelb

1、背景在云服务环境中的 Kubernetes 集群里,通常可以用云服务提供商提供的负载均衡服务来暴露 Service,但是在本地没办法这样操作。而 OpenELB 可以让用户在裸金属服务器、边缘以及...

企业级大数据安全架构(八)

企业级大数据安全架构(八)

前面第七章详细介绍了部署FreeIPA来做kerberos认证,这节接着介绍FreeIPA高可用部署1.FreeIPA高可用配置说明:在安装完一台ipa-server之后,在另一个备份节点部署ipa-...

某网络环境下访问业务异常问题排查

某网络环境下访问业务异常问题排查

问题现象在办公网络环境下访问业务:http://xxx服务,无法正常跳转至登陆页面,如下:但是在另外一台机房服务器访问业务:http://xxx 是正常的,会自动跳转到登陆页面,如下:排查步骤1、查找...

Seatunel 集群部署

Seatunel 集群部署

1、基础环境准备java 1.8 并配置java home操作系统:centos7.9下载安装包:https://www.apache.org/dyn/closer.lua/seatunnel/2.3...

发表评论    

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