欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

mysql 2个表字符集不同join时不能正确走索引

程序员文章站 2022-05-23 14:54:12
...
昨天一个同事做数据迁移引起一个故障,原因是2张表字符集一个为gbk,一个为utf8,并且join key为vachar类型,导致不能正确索引,导致数据库超时,修改字符集编码后正常。本地重现了一下:

一、搞测试数据,文章最后又脚本
二、建索引
--删除多余索引
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);



相关标签: sql mysql 索引