join用不到索引问题分析

发表于 更新于 分类于

问题

今天开发中遇到了一个奇怪的问题， 三个表做join操作的时候，关联字段和查询条件都是有索引的，但是查询花了15秒。

这种时候，一般也不慌，查下执行计划看看哪里出了问题

1
2
3
4
5
EXPLAIN select count(*)
    from pdd_order_refund r
    join pdd_order o on o.id = r.order_id
    left join pdd_order_outer_info i on i.order_id = o.id 
    where r.shop_id in (628990065,618972237);

结果如下：

explain

可以看到pdd_order表使用主键关联只扫描一行数据， 是正常的，而pdd_order_outer_info 表虽然使用到了IDX_DDD索引，但仍然扫描了全表(87422行)数据， 肯定是有问题的。

查看pdd_order_outer_info表的索引
pdd-order-outer-info-index

可以看到IDX_DDD索引基数和主键基数接近，索引效果应该非常好。以此字段关联扫描的行数大多数情况应该是1，最多也就个位数才对。
为什么明明使用了索引，还要扫全表？？？

img

排查过程

几番查找终于在这篇文章中找到了线索。

通过explain EXTENDED 和show warnings语句查看详细的执行计划

1
2
3
4
5
6
7
EXPLAIN EXTENDED select count(*)
    from pdd_order_refund r
    join pdd_order o on o.id = r.order_id
    left join pdd_order_outer_info i on i.order_id = o.id 
    where r.shop_id in (628990065,618972237);
    
show warnings;

show warnings给出了如下信息:

1
/* select#1 */ select count(0) AS `count(*)` from `pdd_order_db`.`pdd_order_refund` `r` join `pdd_order_db`.`pdd_order` `o` left join `pdd_order_db`.`pdd_order_outer_info` `i` on((convert(`pdd_order_db`.`i`.`order_id` using utf8mb4) = `pdd_order_db`.`r`.`order_id`)) where ((`pdd_order_db`.`o`.`id` = `pdd_order_db`.`r`.`order_id`) and (`pdd_order_db`.`r`.`shop_id` in (628990065,618972237)))

可以看到pdd_order_outer_info 的关联条件实际上变成了如下语句

1
on((convert(`pdd_order_db`.`i`.`order_id` using utf8mb4) = `pdd_order_db`.`r`.`order_id`))

终于找到原因：索引字段order_id经过计算才进行比较，导致索引失效。

为什么pdd_order_outer_info表的order_id字段要转字符集再比较呢？ 所以怀疑是两张表的order_id字段字符集不一致导致的，于是查验pdd_order表和pdd_order_outer_info表的建表语句(无关部分已经省略)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE `pdd_order` (
  `id` varchar(100) NOT NULL,
  `shop_id` bigint(20) NOT NULL,
  `created_time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
CREATE TABLE `pdd_order_outer_info` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `shop_id` bigint(20) NOT NULL,
  `order_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `INDEX_SHOP_ID` (`shop_id`,`created`,`outer_user_id`) USING BTREE,
  KEY `idx_order` (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8mb4

果然发现问题，虽然两张表都是utf8mb4 ， 但是 pdd_order_outer_info 表的order_id 被显示的设置成了utf8。

解决

修改一下order_id的字符集

1
2
ALTER TABLE `pdd_order_db`.`pdd_order_outer_info` 
MODIFY COLUMN `order_id` varchar(255) CHARACTER SET utf8mb4 NOT NULL AFTER `shop_id`;

验证一下：

explain

扫描行数已经变成了1，再执行一下最开始的sql，耗时只有25ms，成功解决问题。

结论

两张表关联的时候，如果关联条件字段的字符集不一致，将不能正常使用索引，导致关联的时候扫描全表，引起性能问题