文章目录
一、前言
如题所示,博主这边在两表关键更新的时候,明明关联字段都有索引,但是竟然没用上?百思不得其解,最后发现原来是编码格式的问题,此处记录一下。
1、问题sql:
update test_a as a left join test_b as b on a.uuid=b.uuid set a.log_time = b.log_time;
2、explain一下
mysql> explain update test_a as a left join test_b as b on a.uuid=b.uuid set a.log_time = b.log_time;
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| 1 | UPDATE | a | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | SIMPLE | b | NULL | ALL | NULL | NULL | NULL | NULL | 36873167 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
可以看到这里扫描b表3600W
条数据,导致这条sql
十分缓慢,我滴天,不能忍的。
3、量级
a表:10W级别
b表:3600W级别
二、排查过程
1、难道是字段类型的问题,表结构如下
CREATE TEMPORARY TABLE if not exists test_a (
uuid varchar(32) NOT NULL PRIMARY KEY,
`log_time` datetime NOT NULL ,
`daily_nums` VARCHAR(100) NOT NULL DEFAULT '0',
UNIQUE KEY `uuid` (`uuid`)
) ;
CREATE TABLE `test_b` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`uuid` varchar(32) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
UNIQUE KEY `uuid` (`uuid`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8
这里可以看到,两个表的关联字段都是有索引的,类似都是varchar
类型
2、查看字段编码
这是在百度的时候发现的,有人提出字段编码的问题,博主一想还真是有可能,不查不知道,一查吓一跳。参考:
https://www.hxstrive.com/article/829.htm
show full columns from test_b;
+-------------------+--------------+-----------------+------+-----+---------+----------------+------------+-------------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------------------+--------------+-----------------+------+-----+---------+----------------+------------+-------------+
| id | bigint(20) | NULL | NO | PRI | NULL | auto_increment | select | |
| uuid | varchar(32) | utf8_general_ci | NO | UNI | | | select | |
show full columns from test_a;
+--------------------+-------------+--------------------+------+-----+---------+-------+------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+--------------------+-------------+--------------------+------+-----+---------+-------+------------+---------+
| uuid | varchar(32) | utf8mb4_general_ci | NO | PRI | NULL | | select | |
| user_id | bigint(20) | NULL | NO | | NULL | | select | |
通过对比我们可以发现,两个表的uuid
字段编码是不一样的,一个是普通的utf8
类型,一个是utf8mb4
类型,难道确实是编码的原因吗?
3、修改编码一致看看
博主这里是临时表,所以只需要在建表的时候指定一下即可。
CREATE TEMPORARY TABLE if not exists test_a (
uuid varchar(32) NOT NULL PRIMARY KEY,
`log_time` datetime NOT NULL ,
`daily_nums` VARCHAR(100) NOT NULL DEFAULT '0',
UNIQUE KEY `uuid` (`uuid`)
) DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
(1)解释:
1)DEFAULT CHARACTER SET utf8:数据库字符集。设置数据库的默认编码为utf8,这里utf8中间不要"-";
2)COLLATE utf8_general_ci:数据库校对规则。
(2)查看效果
mysql> explain update test_a as a,test_b as b set a.log_time = b.log_time where a.uuid=b.uuid;
+----+-------------+-------+------------+--------+----------------------------+------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+----------------------------+------+---------+-------------------+------+----------+-------+
| 1 | UPDATE | a | NULL | ALL | PRIMARY,uuid,uuid_2,uuid_3 | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | SIMPLE | b | NULL | eq_ref | uuid | uuid | 98 | analyze_em.a.uuid | 1 | 100.00 | NULL |
这里可以看到,确实是用到索引了,经过博主测试,用到索引之后,此sql
只需要1-2s
左右就跑完了,性能提升将近百倍。
4、为何会出现这种原因
(1)查看数据库编码
mysql> show variables like '%char%';
+--------------------------+-----------------------------------------------+
| Variable_name | Value |
+--------------------------+-----------------------------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | xxx\charsets\ |
+--------------------------+-----------------------------------------------+
注意这里的character_set_database
,它的含义是我们创建表的时候,默认会使用此种编码格式。而我们创建临时表的时候,大多数情况下是不主动设置编码格式的,因此就会默认使用utf8mb4
格式。 但是呢,我们创建普通表的时候,又习惯于设置utf8
类型,所以就导致了这种问题的出现。
(2)解决方案
1)创建表的时候,指定好编码格式
2)设置表的默认编码格式,重启数据库
三、最后
不得不说已经好久没写博客了,一方面是值得写的内容不多,一方面也是忙吧。还是希望能坚持写博客的习惯,奥利给!
end