MySQL隐式字符编码转换导致索引失效案例
收到Zabbix告警，发现一个MySQL服务的CPU突然出现飙涨的情况，分析定位下来，发现是一个查询语句引起的，此SQL关联了好几个表（不合理的设计实现，实际情况就是如此，非常难以改变），而且执行时间非常长。
分析SQL的执行计划如下(下面简单了做了一下脱敏处理)
mysql> explain
....
+----+-------------+-------+-------+----------------------+----------------------+---------+------+--------+-------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+----------------------+----------------------+---------+------+--------+-------------------------------------------------------------------+
| 1 | SIMPLE | so | range | ix_xxx_date | ix_xxx_date | 6 | NULL | 7822 | Using index condition; Using MRR; Using temporary; Using filesort |
| 1 | SIMPLE | soi | ALL | NULL | NULL | NULL | NULL | 137792 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | o | ALL | ix_xxx_sn | NULL | NULL | NULL | 31874 | Range checked for each record (index map: 0x4) |
| 1 | SIMPLE | epo | ALL | ix_xxx_sn | NULL | NULL | NULL | 20071 | Range checked for each record (index map: 0x10) |
+----+-------------+-------+-------+----------------------+----------------------+---------+------+--------+-------------------------------------------------------------------+
4 rows in set (0.00 sec)
mysql>
分析SQL语句后，发现部分表缺少索引，新增了两个索引索引后，发现SQL的性能依然非常差。分析SQL的执行计划后，发现即使相关字段已经新增了索引，但是优化器依然没有走索引。Why？
mysql>
mysql> explain
.....
+----+-------------+-------+-------+--------------------------+--------------------------+---------+------------------+-------+-------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+--------------------------+--------------------------+---------+------------------+-------+-------------------------------------------------+
| 1 | SIMPLE | so | index | ix_xxx_date | PRIMARY | 4 | NULL | 48602 | Using where |
| 1 | SIMPLE | soi | ref | ix_xxx_item_n1 | ix_xxx_item_n1 | 63 | so.ship_sn | 1 | NULL |
| 1 | SIMPLE | o | ALL | ix_xxx_sn | NULL | NULL | NULL | 31875 | Range checked for each record (index map: 0x4) |
| 1 | SIMPLE | epo | ALL | ix_xxx_sn | NULL | NULL | NULL | 20072 | Range checked for each record (index map: 0x10) |
+----+-------------+-------+-------+--------------------------+--------------------------+---------+------------------+-------+-------------------------------------------------+
4 rows in set (0.00 sec)
分析执行计划的Extra列，发现其值为"Range checked for each record (index map: 0x4)"和"Range checked for each record (index map: 0x10)"，Extra列显示这个信息意味着什么呢？我们先来看看官方文档的解释：
Range checked for each record (index map: N) (JSON property: message)
MySQL found no good index to use, but found that some of indexes might be used after column values from preceding tables are known. For each row combination in the preceding tables, MySQL checks whether it is possible to use a range or index_merge access method to retrieve rows. This is not very fast, but is faster than performing a join with no index at all. The applicability criteria are as described in Section 8.2.1.2, “Range Optimization”, and Section 8.2.1.3, “Index Merge Optimization”, with the exception that all column values for the preceding table are known and considered to be constants.
Indexes are numbered beginning with 1, in the same order as shown by SHOW INDEX for the table. The index map value N is a bitmask value that indicates which indexes are candidates. For example, a value of 0x19 (binary 11001) means that indexes 1, 4, and 5 are considered.
当MySQL Query Optimizer没有发现理想的索引时候，如果发现来自前面的表的列值已知，可能部分索引可以使用。对前面的表的每个行组合，MySQL检查 是否可以使用range或index_merge访问方法来索取行。这是使用索引的最慢连接之一。
其实出现这种信息，还有可能意味着表中索引失效了，这里分析后发现关联两个表的字段的排序规则不同导致隐式字符编码转化，从而导致优化器不走索引。
可以用下面脚本查看数据库的字符集和排序规则。
mysql> SELECT
-> SCHEMA_NAME AS 'DATABASE_NAME',
-> DEFAULT_CHARACTER_SET_NAME AS 'DEFAULT_CHARACTER_SET_NAME',
-> DEFAULT_COLLATION_NAME AS 'DEFAULT_COLLATION_NAME'
-> FROM
-> information_schema.SCHEMATA
-> WHERE
-> (DEFAULT_CHARACTER_SET_NAME != 'utf8mb4' OR DEFAULT_COLLATION_NAME != 'utf8mb4_unicode_ci')
-> AND SCHEMA_NAME NOT IN ( 'sys', 'mysql', 'performance_schema', 'information_schema' );
+--------------+--------------------------------+-----------------------+
| DATABASE_NAME| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME|
+--------------+--------------------------------+-----------------------+
| xxx | utf8 | utf8_bin |
| xxx | utf8 | utf8_bin |
+--------------+--------------------------------+-----------------------+
2 rows in set (0.00 sec)
然后检查相关表的排序规则信息
mysql> SELECT TABLE_SCHEMA 'database_name'
-> ,TABLE_NAME
-> ,TABLE_COLLATION
-> ,TABLE_ROWS
-> FROM information_schema.TABLES WHERE TABLE_COLLATION != 'utf8_bin' and TABLE_NAME IN('xxx_order', 'xxx_plan_order');
+---------------+---------------+-----------------+------------+
| database_name | TABLE_NAME | TABLE_COLLATION | TABLE_ROWS |
+---------------+---------------+-----------------+------------+
| trade | xxx_order | utf8_general_ci | 31876 |
| trade | xx_plan_order | utf8_general_ci | 20073 |
+---------------+---------------+-----------------+------------+
2 rows in set (0.00 sec)
如上所示，这两个对应表的排序规则为utf8_general_ci，确实跟关联的其它表的字段的排序规则不一致。使用下面SQL修改排序规则后，SQL的执行计划开始走索引，MySQL服务器的CPU资源也显著下降。
ALTER TABLE xxx_order CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE xxx_plan_order CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
当然，如果你想找出当前数据库中跟数据库排序规则不一致的表，可以使用下面SQL语句
--查看当前MySQL实例中中不符合排序规则的表：
SELECT TABLE_SCHEMA 'database_name'
,TABLE_NAME
,TABLE_COLLATION
,TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_COLLATION != 'utf8_bin' --根据实际情况调整
AND TABLE_SCHEMA not in ('sys','mysql','performance_schema','information_schema');
--查看当前MySQL 数据库中不符合排序规则的表：
SELECT TABLE_SCHEMA 'database_name'
,TABLE_NAME
,TABLE_COLLATION
,TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_COLLATION != (SELECT DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME= database())
AND TABLE_SCHEMA =database();