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.
注意这里面字符集以及字符编码都不一致,如果只改字符集的话,还是会全表扫描,需要先转字符集然后字符编码才可以