同类型的case还有 关联字段字符集不一样,导致表关联时做隐式转换,无法用到关联字段上的索引
案例分析如下:
有2个表act_hi_comment,bpm_task_result,建表语句如下
CREATE TABLE `act_hi_comment` (
`ID_` varchar(64) COLLATE utf8_bin NOT NULL,
`TYPE_` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`TIME_` datetime(3) NOT NULL,
`USER_ID_` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`TASK_ID_` varchar(64) COLLATE utf8_bin DEFAULT NULL,
`PROC_INST_ID_` varchar(64) COLLATE utf8_bin DEFAULT NULL,
`ACTION_` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`MESSAGE_` varchar(4000) COLLATE utf8_bin DEFAULT NULL,
`FULL_MSG_` longblob,
PRIMARY KEY (`ID_`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
CREATE TABLE `bpm_task_result` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`taskId` varchar(50) DEFAULT NULL COMMENT '任务ID',
`processId` varchar(50) DEFAULT NULL COMMENT '流程ID',
`createTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`state` int(11) NOT NULL COMMENT '1-审批通过 2-驳回修改 3-审批不通过 4-审批作废',
PRIMARY KEY (`id`),
UNIQUE KEY `bpm_task_result_taskId_uindex` (`taskId`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=15916 DEFAULT CHARSET=utf8;
有如下SQL,分析执行计划
EXPLAIN SELECT * from act_hi_comment LEFT JOIN bpm_task_result ON act_hi_comment.TASK_ID_ = bpm_task_result.taskId;
使用desc extended,查看附加信息:
mysql> show warnings\G
*************************** 1. row ***************************
Level: Warning
Code: 1739
Message:
Cannot use ref access on index 'bpm_task_result_taskId_uindex' due to type or collation conversion on field 'taskId'
*************************** 2. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `personnel`.`act_hi_comment`.`ID_` AS `ID_`,`personnel`.`act_hi_comment`.`TYPE_` AS `TYPE_`,`personnel`.`act_hi_comment`.`TIME_` AS `TIME_`,`personnel`.`act_hi_comment`.`USER_ID_` AS `USER_ID_`,`personnel`.`act_hi_comment`.`TASK_ID_` AS `TASK_ID_`,`personnel`.`act_hi_comment`.`PROC_INST_ID_` AS `PROC_INST_ID_`,`personnel`.`act_hi_comment`.`ACTION_` AS `ACTION_`,`personnel`.`act_hi_comment`.`MESSAGE_` AS `MESSAGE_`,`personnel`.`act_hi_comment`.`FULL_MSG_` AS `FULL_MSG_`,`personnel`.`bpm_task_result`.`id` AS `id`,`personnel`.`bpm_task_result`.`taskId` AS `taskId`,`personnel`.`bpm_task_result`.`processId` AS `processId`,`personnel`.`bpm_task_result`.`createTime` AS `createTime`,`personnel`.`bpm_task_result`.`state` AS `state` from `personnel`.`act_hi_comment` left join `personnel`.`bpm_task_result` on((`personnel`.`act_hi_comment`.`TASK_ID_` = `personnel`.`bpm_task_result`.`taskId`)) where 1
明确了用不上关联字段taskid上的索引bpm_task_result_taskId_uindex的原因是
collation conversion on field 'taskId'
找到原因了,那么就想解决办法:
方法一:将字符集校验规则修改成一致
在测试环境拷贝一张`activiti`.act_hi_comment,修改表的字符集校验规则,并且将已存在的数据做转换,SQL如下
ALTER TABLE `activiti`.`act_hi_comment` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
修改后的表结构如下:
CREATE TABLE `act_hi_comment` (
`ID_` varchar(64) NOT NULL,
`TYPE_` varchar(255) DEFAULT NULL,
`TIME_` datetime NOT NULL,
`USER_ID_` varchar(255) DEFAULT NULL,
`TASK_ID_` varchar(64) DEFAULT NULL,
`PROC_INST_ID_` varchar(64) DEFAULT NULL,
`ACTION_` varchar(255) DEFAULT NULL,
`MESSAGE_` varchar(4000) DEFAULT NULL,
`FULL_MSG_` longblob,
PRIMARY KEY (`ID_`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
可以看到字符集校验规则已经变得和bpm_task_result一致了,再次查看执行计划:
EXPLAIN SELECT * from `activiti`.act_hi_comment a LEFT JOIN bpm_task_result b ON a.TASK_ID_ = b.taskId;
可以看到已经使用上了bpm_task_result_taskId_uindex这个索引
方法二:显示转换字符集校验规则:
很遗憾,目前convert和cast函数中不支持指定字符集校验规则,详见官方文档
https://dev.mysql.com/doc/refman/5.7/en/cast-functions.html,但是可以直接在字段后面跟COLLATE utf8_general_ci,显示指定校验规则,所以SQL可以改成如下形式:
EXPLAIN SELECT * from act_hi_comment a LEFT JOIN bpm_task_result b ON a.TASK_ID_ COLLATE utf8_general_ci = b.taskId;
可以发现,也达到了同样的效果