MySQL索引未生效问题-字段编码不一致

一、问题描述

页面一个列表查询接口读取经常超时。对应的数据来源于2张表的join。

SELECT 
    *
FROM
    user_comment c
        JOIN
    user u ON u.id = c.user_id
    where c.id = 1

对应表结构

CREATE TABLE `user` (
  `id` char(32) NOT NULL,
  `name` varchar(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE = utf8_general_ci;

CREATE TABLE `user_comment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` char(32) NOT NULL,
  `comment` text,
  PRIMARY KEY (`id`),
  KEY `idx_userId` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

user_comment里user_id有索引，user里id是主键。但是使用explain却发现没有走索引：

图1.explain

二、问题原因

user_comment表为了能保存emoj表情而使用了utf8mb4编码格式，但是user表默认为utf8。所以join条件中的字段的因在两张表中编码不同而不会走索引。

三、问题修复

修改user_comment表默认编码为utf8，并单独修改comment字段编码格式为utf8mb4。

ALTER TABLE `user_comment` 
CHARACTER SET = utf8 , COLLATE = utf8_general_ci ,
CHANGE COLUMN `comment` `comment` TEXT CHARACTER SET 'utf8mb4' NULL DEFAULT NULL ;

四、改进方案

数据库使用utf8mb4需谨慎，只针对需要的字段进行修改。

五、扩展

explain之type

type表示访问类型：

• const：主键或唯一索引等值扫描。
• eq_ref：跨表join时，主键索引(primary key)或者非空唯一索引(unique not null)等值扫描。
• ref：非主键并且非唯一索引等值扫描。
• range：范围扫描。
• index：索引树扫描，如count(*)全表。
• ALL：全表扫描(full table scan)。

扫描效率由快到慢：

const > eq_ref > ref > range > index > ALL