关联字段字符集相同,但是校验规则不一样,导致表关联时做隐式转换,无法用到关联字段上的索引
来源:互联网 发布:linux改变用户所属组 编辑:程序博客网 时间:2024/05/21 19:45
同类型的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;
可以发现,也达到了同样的效果
0 0
- 关联字段字符集相同,但是校验规则不一样,导致表关联时做隐式转换,无法用到关联字段上的索引
- mysql两表关联但是关联字段类型不一致
- 2张表关联字段的关联更新,代码如下:
- 相同字段合并--两个表,并却关联关系表
- VARCHAR2字段关联
- 根据字段关联批量修改关联数据
- gridview关联个数不定的字段
- Sharepoint lookup字段的关联列表查询
- 更改主外键关联的字段值
- 查找某表的所有与之关联的表以及关联字段
- sql关联表修改相关字段
- oracle关联表更新表多个字段
- 一张表中两个字段互相关联
- 关联2个表的字段,以及别名的用法
- 根据字段不同值关联查询不同表的问题
- jpa Sort 使用关联表的字段排序
- Yii grid view 关联其他表的字段
- mybatis查询有关联关系并且有相同字段的两张表问题解决
- win10 ctrl V 热键失效
- python 判断输入的是不是合法(正确)的ip
- Linux之vim常用操作
- Android studio 应用访问本地服务器
- MyBatis和IBatis的区别
- 关联字段字符集相同,但是校验规则不一样,导致表关联时做隐式转换,无法用到关联字段上的索引
- JavaScript中String(a)和new String(a)两种生成字符串的方法的使用区别
- CFgym:Ex Machina(交互 & 线段树区间最值)
- Hive 使用RegexSerDe来处理标准格式Apache Web日志
- java自定义注解实现对象到xml配置的转换
- 相机的功能丰富,加入前后摄像头的翻转,闪光灯,对焦功能。
- 最小生成树
- Java8新特性浅析
- Alsa框架录音程序