mysql明明有索引却用不到的情况

深碍√TFBOYSˉ_ 2022-12-10 08:42 189阅读 0赞

### 文章目录 ###

*   *  一、前言
     *   *  1、问题sql：
         *  2、explain一下
         *  3、量级
     *  二、排查过程
     *   *  1、难道是字段类型的问题，表结构如下
         *  2、查看字段编码
         *  3、修改编码一致看看
         *   *  （1）解释：
             *  （2）查看效果
         *  4、为何会出现这种原因
         *   *  （1）查看数据库编码
             *  （2）解决方案
     *  三、最后

## 一、前言 ##

如题所示，博主这边在两表关键更新的时候，明明关联字段都有索引，但是竟然没用上？百思不得其解，最后发现原来是编码格式的问题，此处记录一下。

### 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一下 ###

可以看到这里扫描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

通过对比我们可以发现，两个表的`uuid`字段编码是不一样的，一个是普通的`utf8`类型，一个是`utf8mb4` 类型，难道确实是编码的原因吗？

### 3、修改编码一致看看 ###

博主这里是临时表，所以只需要在建表的时候指定一下即可。

#### （1）解释： ####

1）DEFAULT CHARACTER SET utf8：数据库字符集。设置数据库的默认编码为utf8，这里utf8中间不要"-"；
     2）COLLATE utf8_general_ci:数据库校对规则。

#### （2）查看效果 ####

这里可以看到，确实是用到索引了，经过博主测试，用到索引之后，此`sql`只需要`1-2s`左右就跑完了，性能提升将近百倍。

### 4、为何会出现这种原因 ###

#### （1）查看数据库编码 ####

注意这里的`character_set_database` ，它的含义是我们创建表的时候，默认会使用此种编码格式。而我们创建临时表的时候，大多数情况下是不主动设置编码格式的，因此就会默认使用`utf8mb4` 格式。 但是呢，我们创建普通表的时候，又习惯于设置`utf8`类型，所以就导致了这种问题的出现。

#### （2）解决方案 ####

1）创建表的时候，指定好编码格式
    2）设置表的默认编码格式，重启数据库

## 三、最后 ##

不得不说已经好久没写博客了，一方面是值得写的内容不多，一方面也是忙吧。还是希望能坚持写博客的习惯，奥利给！

**end**