案例：SQL无法走索引,字符集以及编码不一致如何处理

1.执行计划：

SELECT * FROM `table1` WHERE  `uuid`  in( SELECT uuid FROM table2 WHERE project_create_at != "0000-00-00 00:00:00" )

+--------------------+----------------+-----------------------+-------------------+----------------+-------------------------------------------------------------------+
| table              | type           | key                   | key_len           | rows           | Extra                                                             |
+--------------------+----------------+-----------------------+-------------------+----------------+-------------------------------------------------------------------+
| table2             | range          | idx-project-create-at | 6                 | 51             | Using index condition; Start temporary                            |
| table1             | ALL            |                       |                   | 1934595        | Using where; End temporary; Using join buffer (Block Nested Loop) |
+--------------------+----------------+-----------------------+-------------------+----------------+-------------------------------------------------------------------+

执行计划上查看是type 全表扫描。
那么来查看下结构：

CREATE TABLE `table2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uuid` varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'UUID',
   xxxxxx
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid_idx` (`uuid`),
) ENGINE=InnoDB AUTO_INCREMENT=2343994 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `table2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uuid` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '项目uuid',
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=5408 DEFAULT CHARSET=utf8mb4

从表结构发现，字符集以及字符编码不一致？那么应该如何转换呢
explain extended看下是否有提示：

select `id` AS `id`,
        xxxxxxxx
       ``.`b`.`updated_at` AS `updated_at`
  from table2 `a` join `table1` `b`
 where((`a`.`project_create_at`<> '0000-00-00 00:00:00')
   and(`a`.`uuid`= convert(`b`.`uuid` using utf8mb4)))

从执行计划中显示还是，全表扫描那么使用explain extended show warnings 查看 and(swift_nuochou_com.a.uuid= convert(swift_nuochou_com.b.uuid using utf8mb4)))  进行了类型转换

那我们我们使用convert是否可行：

explain extended
select b.*
from (select convert(uuid using utf8) COLLATE utf8_unicode_ci as uuid
from table2 where project_create_at != "0000-00-00 00:00:00") a, table1 b
where a.uuid = b.uuid
+--------------+-----------------------+--------------------+----------------+-------------------------+-----------------------+-------------------+---------------+----------------+--------------------+-----------------------+
| id           | select_type           | table              | type           | possible_keys           | key                   | key_len           | ref           | rows           | filtered           | Extra                 |
+--------------+-----------------------+--------------------+----------------+-------------------------+-----------------------+-------------------+---------------+----------------+--------------------+-----------------------+
| 1            | PRIMARY               | <derived2>         | ALL            |                         |                       |                   |               | 117            |                100 |                       |
| 1            | PRIMARY               | b                  | eq_ref         | uuid_idx                | uuid_idx              | 386               | a.uuid        | 1              |                100 |                       |
| 2            | DERIVED               | table2             | range          | idx-project-create-at   | idx-project-create-at | 6                 |               | 117            |                100 | Using index condition |
+--------------+-----------------------+--------------------+----------------+-------------------------+-----------------------+-------------------+---------------+----------------+--------------------+-----------------------+
共返回 3 行记录,花费 3 ms.

注意这里面字符集以及字符编码都不一致，如果只改字符集的话，还是会全表扫描，需要先转字符集然后字符编码才可以