MySQL-字符集导致隐式转换

关键字

隐式转换、字符集

问题描述

SQL:

 SELECT
 u.id,u.aa_user_id,u.name,u.email,u.mobile,u.user_type
 FROM
 bb_schema.user u
  LEFT JOIN aa_schema.bd_staff staff  ON u.aa_user_id = staff.user_id
 AND staff.ENABLE = 1
 AND u.aa_tenant_id = staff.tenantid
 WHERE  u.tenant_id=1359756059382016 AND u.stopstatus=0 LIMIT 10000000;

有同学反馈该 SQL 无法查询出结果

解决问题思路

我们查看执行计划

+----+-------------+-------+------------+------+--------------------------------------+-------------------------+---------+-------+--------+----------+-------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys                        | key                     | key_len | ref   | rows   | filtered | Extra                                           |
+----+-------------+-------+------------+------+--------------------------------------+-------------------------+---------+-------+--------+----------+-------------------------------------------------+
|  1 | SIMPLE      | u     | NULL       | ref  | i_tenant_id_aa_user_id              | i_tenant_id_aa_user_id | 9       | const |   1176 |    10.00 | Using where                                     |
|  1 | SIMPLE      | staff | NULL       | ALL  | i_user_id_tenantid_enable,i_tenantid | NULL                    | NULL    | NULL  | 214632 |   100.00 | Range checked for each record (index map: 0x30) |
+----+-------------+-------+------------+------+--------------------------------------+-------------------------+---------+-------+--------+----------+-------------------------------------------------+

从执行计划 Range checked for each record (index map: 0x30) 。通过执行计划可知，这条语句肯定应该走 i_user_id_tenantid_enable 索引的。是不是又因为字段类型不一致呢。

Range checked for each record (index map: 0x30)
即MySQL没有发现好的索引，但是发现有的索引可能有用(比起没有索引可用的情况)
 0x30  二进制：11 0000‬，即MySQL考虑使用了第5个和第6个索引（i_user_id_tenantid_enable/i_tenantid）
[aa_schema]> show index from bd_staff;
+----------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name                  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| bd_staff |          0 | PRIMARY                   |            1 | id          | A         |      214632 |     NULL | NULL   |      | BTREE      |         |               |
| bd_staff |          1 | staff_index_code          |            1 | code        | A         |       85303 |     NULL | NULL   | YES  | BTREE      |         |               |
| bd_staff |          1 | idx_test1                 |            1 | dr          | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
| bd_staff |          1 | idx_mobile                |            1 | mobile      | A         |       69396 |     NULL | NULL   | YES  | BTREE      |         |               |
| bd_staff |          1 | idx_mobile                |            2 | sysid       | A         |       53664 |     NULL | NULL   | YES  | BTREE      |         |               |
| bd_staff |          1 | idx_mobile                |            3 | tenantid    | A         |      195931 |     NULL | NULL   | YES  | BTREE      |         |               |
| bd_staff |          1 | i_user_id_tenantid_enable |            1 | user_id     | A         |       70297 |     NULL | NULL   | YES  | BTREE      |         |               |
| bd_staff |          1 | i_user_id_tenantid_enable |            2 | tenantid    | A         |      207437 |     NULL | NULL   | YES  | BTREE      |         |               |
| bd_staff |          1 | i_user_id_tenantid_enable |            3 | enable      | A         |      208992 |     NULL | NULL   | YES  | BTREE      |         |               |
| bd_staff |          1 | i_tenantid                |            1 | tenantid    | A         |        6348 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

对比 JOIN 列

user表：
  `aa_user_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `aa_tenant_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
bd_staff表：
  `tenantid` varchar(64) DEFAULT NULL COMMENT '租户标识',
  `user_id` varchar(64) DEFAULT NULL COMMENT '关联用户',

JOIN 列类型相同，但是 user 表显示指定了字符集。

我们修改一下 user 表列

[bb_schema]> ALTER TABLE `user` MODIFY  aa_user_id VARCHAR(64) DEFAULT NULL,MODIFY aa_tenant_id VARCHAR(64) DEFAULT NULL;

再次查看执行计划

[bb_schema]> EXPLAIN SELECT  u.id,u.aa_user_id,u.name,u.email,u.mobile,u.user_type FROM bb_schema.user u  LEFT JOIN aa_schema.bd_staff staff  ON u.aa_user_id = staff.user_id  AND staff.ENABLE = 1  AND u.aa_tenant_id = staff.tenantid WHERE  u.tenant_id=1359756059382016 AND u.stopstatus=0 LIMIT 10000000;
+----+-------------+-------+------------+------+--------------------------------------+---------------------------+---------+---------------------------------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys                        | key                       | key_len | ref                                               | rows | filtered | Extra       |
+----+-------------+-------+------------+------+--------------------------------------+---------------------------+---------+---------------------------------------------------+------+----------+-------------+
|  1 | SIMPLE      | u     | NULL       | ref  | i_tenant_id_aa_user_id              | i_tenant_id_aa_user_id   | 9       | const                                             | 1176 |    10.00 | Using where |
|  1 | SIMPLE      | staff | NULL       | ref  | i_user_id_tenantid_enable,i_tenantid | i_user_id_tenantid_enable | 395     | bb_schema.u.aa_user_id,bb_schema.u.aa_tenant_id,const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+--------------------------------------+---------------------------+---------+---------------------------------------------------+------+----------+-------------+

查询秒出

问题总结

JOIN 关联字段类型不仅一定要相同，字符集也必须相同。禁止在列上单独指定字符集。

MySQL 优先选用 utf8mb4，并且只在库表级别指定即可。