mysql 2个表字符集不同join时不能正确走索引
程序员文章站
2022-05-23 14:54:12
...
昨天一个同事做数据迁移引起一个故障,原因是2张表字符集一个为gbk,一个为utf8,并且join key为vachar类型,导致不能正确索引,导致数据库超时,修改字符集编码后正常。本地重现了一下:
一、搞测试数据,文章最后又脚本
二、建索引
--删除多余索引
--建索引,建标语句中有,可忽略
三、测试
1、
可以走到索引
2、修改表编码:仍然可以走到索引,说明join key 都是数值仍然可以走到索引
3、修改关联字段类型为不同类型,不能正确走到索引
ALTER TABLE score MODIFY c_id VARCHAR(32);
--ALTER TABLE student MODIFY s_no VARCHAR(32);
结论,因为字段类型,编码不同都回造成不能正确走到索引,如果都是数值类型的应该就没问题:
1、2表编码不同, join字段数值类型不同, 不能正常走索引,即使编码相同???
2、2表编码相同,join key都是数值类型,正确走到索引
3、2表编码相同,join key数值类型不同,不能正确走到索引
相关数据:
一、搞测试数据,文章最后又脚本
二、建索引
--删除多余索引
drop INDEX index_student_s_age on student ; drop INDEX index_student_s_no on student ; drop INDEX index_score_point on score ; drop INDEX index_score_c_id on score ;
--建索引,建标语句中有,可忽略
CREATE INDEX index_student_s_age on student (s_age); CREATE INDEX index_student_s_no on student (s_no); CREATE INDEX index_score_point on score (point); CREATE INDEX index_score_c_id on score (c_id);
三、测试
1、
引用explain select a.*,b.* from student a , score b where a.s_no=b.c_id and a.s_no=11 1 SIMPLE b const PRIMARY PRIMARY 4 const 1 1 SIMPLE a ref index_student_s_no index_student_s_no 5 const 1 Using where
可以走到索引
2、修改表编码:仍然可以走到索引,说明join key 都是数值仍然可以走到索引
ALTER TABLE student CONVERT TO CHARACTER SET utf8; explain select a.*,b.* from student a , score b where a.s_no=b.c_id and a.s_no=14 1 SIMPLE b const PRIMARY PRIMARY 4 const 1 1 SIMPLE a ref index_student_s_no index_student_s_no 5 const 1 Using where
3、修改关联字段类型为不同类型,不能正确走到索引
ALTER TABLE score MODIFY c_id VARCHAR(32);
--ALTER TABLE student MODIFY s_no VARCHAR(32);
结论,因为字段类型,编码不同都回造成不能正确走到索引,如果都是数值类型的应该就没问题:
1、2表编码不同, join字段数值类型不同, 不能正常走索引,即使编码相同???
2、2表编码相同,join key都是数值类型,正确走到索引
3、2表编码相同,join key数值类型不同,不能正确走到索引
相关数据:
CREATE TABLE `student` ( `s_no` int(11) DEFAULT NULL, `s_name` varchar(500) DEFAULT NULL, `s_age` int(11) DEFAULT NULL, `s_sex` varchar(10) DEFAULT NULL, KEY `index_student_s_no` (`s_no`), KEY `index_student_s_age` (`s_age`) ) ENGINE=InnoDB DEFAULT CHARSET=gbk; CREATE TABLE `score` ( `c_id` int(11) NOT NULL, `c_name` varchar(255) DEFAULT NULL, `point` int(11) DEFAULT NULL, KEY `index_course_point` (`point`), KEY `index_course_c_id` (`point`), KEY `index_score_point` (`point`), KEY `index_score_c_id` (`c_id`) ) ENGINE=InnoDB DEFAULT CHARSET=gbk; INSERT INTO `student` VALUES (1, '张无忌', 18, '男'); INSERT INTO `student` VALUES (2, '周芷若', 19, '女'); INSERT INTO `student` VALUES (3, '杨过', 19, '男'); INSERT INTO `student` VALUES (4, '赵敏', 18, '女'); INSERT INTO `student` VALUES (5, '小龙女', 17, '女'); INSERT INTO `student` VALUES (6, '张三丰', 18, '男'); INSERT INTO `student` VALUES (7, '令狐冲', 19, '男'); INSERT INTO `student` VALUES (8, '任盈盈', 20, '女'); INSERT INTO `student` VALUES (9, '岳灵珊', 19, '女'); INSERT INTO `student` VALUES (10, '韦小宝', 18, '男'); INSERT INTO `student` VALUES (11, '康敏', 17, '女'); INSERT INTO `student` VALUES (12, '萧峰', 19, '男'); INSERT INTO `student` VALUES (13, '黄蓉', 18, '女'); INSERT INTO `student` VALUES (14, '郭靖', 19, '男'); INSERT INTO `student` VALUES (15, '周伯通', 19, '男'); INSERT INTO `student` VALUES (16, '瑛姑', 20, '女'); INSERT INTO `student` VALUES (17, '李秋水', 21, '女'); INSERT INTO `student` VALUES (18, '黄药师', 18, '男'); INSERT INTO `student` VALUES (19, '李莫愁', 18, '女'); INSERT INTO `student` VALUES (20, '冯默风', 17, '男'); INSERT INTO `student` VALUES (21, '王重阳', 17, '男'); INSERT INTO `student` VALUES (22, '郭襄', 18, '女'); INSERT INTO `score` VALUES (1, '企业管理', 2); INSERT INTO `score` VALUES (10, '线性代数', 17); INSERT INTO `score` VALUES (11, '计算机基础', 13); INSERT INTO `score` VALUES (12, 'AUTO CAD制图', 15); INSERT INTO `score` VALUES (13, '平面设计', 11); INSERT INTO `score` VALUES (14, 'Flash动漫', 1); INSERT INTO `score` VALUES (15, 'Java开发', 9); INSERT INTO `score` VALUES (16, 'C#基础', 2); INSERT INTO `score` VALUES (17, 'Oracl数据库原理', 10); INSERT INTO `score` VALUES (2, 'max, 8); INSERT INTO `score` VALUES (3, 'UML', 6); INSERT INTO `score` VALUES (4, '数据库', 7); INSERT INTO `score` VALUES (5, '逻辑电路', 6); INSERT INTO `score` VALUES (6, '英语', 3); INSERT INTO `score` VALUES (7, '电子电路', 5); INSERT INTO `score` VALUES (8, 'mao*思想概论', 4); INSERT INTO `score` VALUES (9, '西方哲学史', 12);