mysql编码不同是否可使用索引_mysql 2个表字符集不同join时不能正确走索引

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

1SIMPLEbconstPRIMARYPRIMARY4const 1

1SIMPLEaref index_student_s_noindex_student_s_no5const1Using 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

1SIMPLEbconstPRIMARYPRIMARY4const 1

1SIMPLEaref index_student_s_noindex_student_s_no5const1Using 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, 'maozedong思想概论', 4);

INSERT INTO `score` VALUES (9, '西方哲学史', 12);

分享到：

2013-12-05 13:59

浏览 4312

分类:数据库

评论