有两张表订单表(order 875533条)、订单的错误日志表(order_error_log 41975条),order_error_log中存在order的id,做一个简单的关联查询,但是速度很慢。
表结构
CREATE TABLE `order_error_log` (
`id` varchar(32) NOT NULL,
......省略40个字段
`order_id` varchar(32) DEFAULT NULL COMMENT '订单id',
PRIMARY KEY (`id`),
KEY `idx_order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单日志表';
CREATE TABLE `order` (
`id` varchar(32) NOT NULL,
`create_user` varchar(32) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
......此处省略130个字段,表的字段有点多
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单表';
SQL
SELECT
o.*
FROM
order_error_log oe
INNER JOIN `order` o ON oe.order_id = o.id
WHERE
oe.id = "5f2eb2063cbc40e9a414b77e35156c9f";
排查过程
执行以上SQL花费了5.8秒,这显然不正常,查看一下执行计划发现没有使用order表的id作为索引查询,而是全表扫描的order。
删除主键
这让我百思不得其解,甚至一度认为order表的主键出现了问题,于是我将order表的主键删除了,确实起到了一定的作用,SQL的平均执行时间降低到了4秒,但还是没有达到预期的速度。然后我有将主键重建了,执行时间有变成5.8秒左右了。
explain extended + show warnings
MySQL在执行SQL之前会对SQL进行一定的优化,然后执行优化后的SQL,那就想办法查看一下优化后的SQL,几番百度找到了explain extended
+ show warnings
。
EXPLAIN EXTENDED SELECT
o.*
FROM
order_error_log oe
INNER JOIN `order` o ON oe.order_id = o.id
WHERE
oe.id = "5f2eb2063cbc40e9a414b77e35156c9f";
SHOW WARNINGS;
优化后的SQL
/* select#1 */
SELECT
`test`.`o`.`id` AS `id`,
`test`.`o`.`create_user` AS `create_user`,
`test`.`o`.`create_time` AS `create_time`,
......省略N个字段
FROM
`test`.`order_error_log` `oe`
JOIN `test`.`order` `o`
WHERE
(
(
'5f2eb2063cbc40e9a414b77e35156c9f' = '5f2eb2063cbc40e9a414b77e35156c9f'
)
AND (
'8685e366f84a45289faf965cc317c1a9' = CONVERT (
`test`.`o`.`id` USING utf8mb4
)
)
)
可以看到最后又一句CONVERT (test.o.id USING utf8mb4)
,对order表的id进行了字符集转换,所以导致不使用索引,而是全表扫描。现在回到表结构可以看到order_error_log为utf8mb4,order表为utf8。
解决方案
将order_error_log表的order_id改为utf8,体验飞一般的感觉()
alter table order_error_log change order_id order_id varchar(32) character set utf8;
总结
在执行join关联查询,且关联的字段都是索引列,也会因为两个字段字符集不同导致不使用索引。