百倍性能提升SQL优化纪实

现象

在一次数据统计中，有以下5张表，

CREATE TABLE
    statistic_1
    (
        ...
        method_md5 CHAR(32) COLLATE utf8_bin NOT NULL,
        DAY DATE NOT NULL,
		...
        total_invoke_count bigint NOT NULL
    )
    ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

数据10441条。

CREATE TABLE `method` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `service_md5` char(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `method` varchar(511) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `method_md5` char(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `method_md5_UNIQUE` (`method_md5`) USING BTREE,
  KEY `idx_service_md5` (`service_md5`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

数据123879条。

CREATE TABLE `service` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `service_md5` char(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `application_code` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `service_md5_UNIQUE` (`service_md5`) USING BTREE,
  KEY `idx_application_code_name` (`application_code`(10),`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

数据46148条。

CREATE TABLE `deploy_unit` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `du_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `du_name` varchar(64) NOT NULL,
  `du_package` varchar(64) NOT NULL,
  `system_id` char(15) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `du_id_UNIQUE` (`du_id`) USING BTREE,
  KEY `idx_system_id` (`system_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

数据12296条。

CREATE TABLE `system_info` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `system_uid` char(15) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `system_ch_name` varchar(32) NOT NULL,
  `system_eh_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `system_uid_UNIQUE` (`system_uid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

数据5546条。

select csi.system_uid, csi.snd_system_eh_name, sum(a.total_invoke_count)
from statistic_1 a 
left join method c on a.method_md5 = c.method_md5 
left join service b on c.service_md5 = b.service_md5
left join deploy_unit cdi on b.app_code = cdi.du_id 
left join system_info csi on cdi.du_package = csi.system_uid
group by csi.system_uid
order by csi.system_uid desc;

在DBeaver中执行以上语句，默认查询200行，用了23.472s。时间不可接受！

从上图看，left join system_info csi on cdi.du_package = csi.system_uid时，没有用上索引。
明明system_uid是唯一索引啊，为什么没用上呢？

仔细查看left join时用到的2列，
deploy_unit表中的du_package varchar(64) NOT NULL
system_info表中的system_uid char(15) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL

是不是跟SET utf8 COLLATE utf8_bin有关系呢？

优化措施

把utf8编码去掉试试

ALTER TABLE system_info CHANGE COLUMN `system_uid` `system_uid` char(15) NOT NULL;

执行以上语句后，再去执行之前同样的查询语句，之前没有用到索引的关联，现在也用上了索引，优化有效果，如下图：

可以看到system_uid_UNIQUE索引可以用上了。
查询200行，只用166ms，简直是质的飞跃。

优化效果

同样查询200条记录，
耗时：23.472s —》 166ms，性能提升100倍+。

总结

MySQL 字符集不一致导致索引失效，需要修改字符集编码解决。