mysql 2涓〃瀛楃闆嗕笉鍚宩oin鏃朵笉鑳芥纭蛋绱㈠紩
鏄ㄥぉ涓€涓悓浜嬪仛鏁版嵁杩佺Щ寮曡捣涓€涓晠闅滐紝鍘熷洜鏄?寮犺〃瀛楃闆嗕竴涓负gbk锛屼竴涓负utf8锛屽苟涓攋oin key涓簐achar绫诲瀷锛屽鑷翠笉鑳芥纭储寮曪紝瀵艰嚧鏁版嵁搴撹秴鏃讹紝淇敼瀛楃闆嗙紪鐮佸悗姝ｅ父銆傛湰鍦伴噸鐜颁簡涓€涓嬶細

涓€銆佹悶娴嬭瘯鏁版嵁锛屾枃绔犳渶鍚庡張鑴氭湰
浜屻€佸缓绱㈠紩
--鍒犻櫎澶氫綑绱㈠紩

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銆?琛ㄧ紪鐮佷笉鍚? join瀛楁鏁板€肩被鍨嬩笉鍚? 涓嶈兘姝ｅ父璧扮储寮曪紝鍗充娇缂栫爜鐩稿悓锛燂紵锛?
2銆?琛ㄧ紪鐮佺浉鍚岋紝join key閮芥槸鏁板€肩被鍨嬶紝姝ｇ‘璧板埌绱㈠紩
3銆?琛ㄧ紪鐮佺浉鍚岋紝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