MySQL字符集不同导致的问题

jiezi

5 年前

开发反映有个请求 sql 很慢，需要 3s 以上。sql 如下：

SELECT
  tc.customer_name,
  cb.service_record_id~~~~,
  tc.customer_code,
  tc.customer_desc,
  tc.customer_account_id,
  tc.phone
FROM
  tur_customer tc
INNER JOIN tur_customer_bind cb ON tc.customer_account_id = cb.customer_account_id
WHERE
  cb.employee_account_id = '181'
AND tc.is_delete = 0
ORDER BY
  cb.service_record_id DESC
LIMIT 0,10

表结构：

CREATE TABLE `tur_customer` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `customer_code` varchar(64) NOT NULL COMMENT '客户编码',
  `customer_name` varchar(255) NOT NULL COMMENT '客户姓名',
  `customer_name_hash` varchar(128) DEFAULT NULL COMMENT '客户姓名 Hash',
  `customer_account_id` varchar(32) NOT NULL COMMENT '帐号系统对应的唯一标识',
  `real_name_status` int(11) DEFAULT '10' COMMENT '实名制认证状态: 10 未认证, 20 已实名, 30 已认证',
  `sex` int(11) DEFAULT '1' COMMENT '性别;1 男；0 女',
  `phone` varchar(128) NOT NULL COMMENT '联系电话，可能时手机号，也可能是座机号码',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_customercode` (`customer_code`) USING BTREE,
  UNIQUE KEY `uniq_customeraccountid` (`customer_account_id`) USING BTREE,
  KEY `idx_customernamehash` (`customer_name_hash`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=267031 DEFAULT CHARSET=utf8 COMMENT='客户基本信息表';
 
CREATE TABLE `tur_customer_bind` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `customer_account_id` varchar(32) NOT NULL COMMENT '客户账号',
  `employee_account_id` varchar(32) NOT NULL COMMENT '顾问账号',
  `employee_account_name` varchar(32) NOT NULL COMMENT '顾问名称',
  `service_record_id` int(11) NOT NULL COMMENT '最新服务记录 ID',
  `bind_time` datetime NOT NULL COMMENT '绑定时间',
  `store_code` varchar(32) DEFAULT NULL COMMENT '店面编码',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uniq_account` (`customer_account_id`) USING BTREE COMMENT '客户账号唯一索引',
  KEY `idx_employee_account_id` (`employee_account_id`) USING BTREE COMMENT '顾问账号索引'
) ENGINE=InnoDB AUTO_INCREMENT=14583 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='当前客户关系表';

分析表的执行计划如下：

+----+-------------+-------+------+--------------------------------------+-------------------------+---------+-------+-------+--------------------------------------------------------+
| id | select_type | table | type | possible_keys                        | key                     | key_len | ref   | rows  | Extra                                                  |
+----+-------------+-------+------+--------------------------------------+-------------------------+---------+-------+-------+--------------------------------------------------------+
|  1 | SIMPLE      | cb    | ref  | uniq_account,idx_employee_account_id | idx_employee_account_id | 130     | const |     1 | Using index condition; Using temporary; Using filesort |
|  1 | SIMPLE      | tc    | ALL  | NULL                                 | NULL                    | NULL    | NULL  | 58697 | Using where; Using join buffer (Block Nested Loop)     |
+----+-------------+-------+------+--------------------------------------+-------------------------+---------+-------+-------+--------------------------------------------------------+

两表关联字段 tur_customer.customer_account_id 和 tur_customer_bind.customer_account_id 都有索引，且类型都为 varchar，但是索引没有用到，查看字符集发现一个表是 utf8 一个是 utf8mb4。

两表的字符集不同，会造成无法使用索引的情况发生。查看库的字符集为 utf8，排序规则为 utf8_general_ci.

将表 tur_customer 修改为 utf8mb4 格式后，该 sql 的执行时间仅为 0.1s，再次查看执行计划如下：


+----+-------------+-------+--------+--------------------------------------+-------------------------+---------+-----------------------------+------+----------------------------------------------------+
| id | select_type | table | type   | possible_keys                        | key                     | key_len | ref                         | rows | Extra                                              |
+----+-------------+-------+--------+--------------------------------------+-------------------------+---------+-----------------------------+------+----------------------------------------------------+
|  1 | SIMPLE      | cb    | ref    | uniq_account,idx_employee_account_id | idx_employee_account_id | 130     | const                       |    1 | Using index condition; Using where; Using filesort |
|  1 | SIMPLE      | tc    | eq_ref | uniq_customeraccountid               | uniq_customeraccountid  | 130     | test.cb.customer_account_id |    1 | Using where                                        |
+----+-------------+-------+--------+--------------------------------------+-------------------------+---------+-----------------------------+------+----------------------------------------------------+

报错信息如下：

SQL state [HY000]; error code [1267]; Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='; nested exception is java.sql.SQLException: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='"

查了下两表的 DDL，一个是 utf8mb4_unicode_ci，一个是 utf8mb4_general_ci，所以导致了这个问题

解决方法 ：将这两个表统一改为 utf8mb4_unicode_ci 或者 utf8mb4_general_ci。

如果不修改表结构，可以在查询的时候指定等号一边的字符集编码:

格式：WHERE 列名 COLLATE utf8mb4_unicode_ci = 嵌套语句
或者：WHERE 列名 = 嵌套语句 COLLATE utf8mb4_unicode_ci 达到让两边的字符集相等！

MySQL字符集不同导致的问题

1、两表字符集不同导致无法使用索引

2、两表排序规则不同进行 join 连表报错