-- Table "class" DDLCREATE TABLE `class` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(50) default NULL,
PRIMARY KEY (`id`),
KEY `i_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Table "student" DDLCREATE TABLE `student` (
`studentId` varchar(11) NOT NULL,
`name` varchar(50) default NULL,
PRIMARY KEY (`studentId`),
KEY `i_studentId` (`studentId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Table "student_class" DDLCREATE TABLE `student_class` (
`id` int(11) NOT NULL auto_increment,
`classId` int(11) default NULL,
`studentId` varchar(11) default NULL,
`description` varchar(255) default NULL,
PRIMARY KEY (`id`),
KEY `fk_studentId` (`studentId`),
KEY `fk_student_classId` (`classId`),
CONSTRAINT `fk_student_classId` FOREIGN KEY (`classId`) REFERENCES `class` (`id`),
CONSTRAINT `fk_studentId` FOREIGN KEY (`studentId`) REFERENCES `student` (`studentId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Table "teacher" DDLCREATE TABLE `teacher` (
`teacherId` varchar(11) NOT NULL default '',
`name` varchar(50) default NULL,
PRIMARY KEY (`teacherId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Table "teacher_class" DDLCREATE TABLE `teacher_class` (
`id` int(11) NOT NULL auto_increment,
`teacherId` varchar(11) default NULL,
`classId` int(11) default NULL,
`description` varchar(255) default NULL,
PRIMARY KEY (`id`),
KEY `fk_teacherId` (`teacherId`),
KEY `fk_teacher_classId` (`classId`),
CONSTRAINT `fk_teacher_classId` FOREIGN KEY (`classId`) REFERENCES `class` (`id`),
CONSTRAINT `fk_teacherId` FOREIGN KEY (`teacherId`) REFERENCES `teacher` (`teacherId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO class(name) values("04");
INSERT INTO class(name) values("05");INSERT INTO student(studentId, name) values("100001", "張三");
INSERT INTO student(studentId, name) values("100002", "李四");
INSERT INTO student(studentId, name) values("100003", "wangwu");INSERT INTO student_class(studentId, classId) values("100001", 1);
INSERT INTO student_class(studentId, classId) values("100002", 2);
INSERT INTO student_class(studentId, classId) values("100003", 2);INSERT INTO teacher(teacherId, name) values("900001", "李老師");
INSERT INTO teacher(teacherId, name) values("900002", "王老師");INSERT INTO teacher_class(teacherId, classId) values("900001", 1);
INSERT INTO teacher_class(teacherId, classId) values("900002", 2);
MySQL explain的使用說明explain顯示了MySQL如何使用索引來處理select語句以及連接表。可以幫助選擇更好的索引和寫出更優化的查詢語句。
使用方法，在select語句前加上explain就可以了：
如：SELECT A.name, B.name FROM student A, class B, student_class C WHERE B.id = 2 AND B.Id = C.classId AND A.studentId = C.studentId;
分析結果形式如下：引用MySQL> EXPLAIN SELECT A.name, B.name FROM student A, class B, student_class C WHERE B.id = 2 AND B.Id = C.classId AND A.studentId = C.studentId;
+----+-------------+-------+-------+---------------------------------+--------------------+---------+------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------------------------+--------------------+---------+------------------+------+-------------+
| 1 | SIMPLE | B | const | PRIMARY,i_id | PRIMARY | 4 | const | 1 | |
| 1 | SIMPLE | C | ref | fk_studentId,fk_student_classId | fk_student_classId | 5 | const | 1 | Using where |
| 1 | SIMPLE | A | ref | PRIMARY,i_studentId | PRIMARY | 35 | user.C.studentId | 1 | |
+----+-------------+-------+-------+---------------------------------+--------------------+---------+------------------+------+-------------+
3 rows in set (0.00 sec)
EXPLAIN列的解釋：
MySQL 5.1 用戶手冊 7.2.1. EXPLAIN語法（獲取SELECT相關信息）
有詳細的诠釋id: SELECT識別符。這是SELECT的查詢序列號。
select_type: 可以為以下任何一種
SIMPLE: 簡單SELECT(不使用UNION或子查詢)
PRIMARY: 最外面的SELECT
UNION: UNION中的第二個或後面的SELECT語句
DEPENDENT UNION: 子查詢中的第一個SELECT，取決於外面的查詢
DERIVED: 導出表的SELECT(FROM子句的子查詢)
table 顯示這一行的數據是關於哪張表的
type 這是重要的列，顯示連接使用了何種聯接類型。從最好到最差的連接類型為system, cons,eq_ref, ref, ref_or_null, index_merge, unique_subquery, index_subquery, range、index和ALL
possible_keys 顯示可能應用在這張表中的索引。如果為NULL，沒有可能的索引。可以為相關的域從WHERE語句中選擇一個合適的語句
key 實際使用的索引。如果為NULL，則沒有使用索引。很少的情況下，MYSQL會選擇優化不足的索引。這種情況下，可以在SELECT語句中使用USE INDEX（indexname）來強制使用一個索引或者用IGNORE INDEX（indexname）來強制MySQL忽略索引
key_len 使用的索引的長度。在不損失精確性的情況下，長度越短越好
ref 顯示索引的哪一列被使用了，如果可能的話，是一個常數
rows MySQL認為必須檢查的用來返回請求數據的行數
Extra 關於MYSQL如何解析查詢的額外信息。將在表4.3中討論，但這裡可以看到的壞的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，結果是檢索會很慢extra 該列包含MySQL解決查詢的詳細信息。
Distinct: 一旦MySQL找到了與行相聯合匹配的行，就不再搜索了
Not exists: MySQL優化了LEFT JOIN，一旦它找到了匹配LEFT JOIN標准的行，就不再搜索了
Range checked for each: Record（index map:#）沒有找到理想的索引，因此對於從前面表中來的每一 個行組合，MySQL檢查使用哪個索引，並用它來從表中返回行。這是使用索引的最慢的連接之一
Using filesort: 看到這個的時候，查詢就需要優化了。MySQL需要進行額外的步驟來發現如何對返回的行 排序。它根據連接類型以及存儲排序鍵值和匹配條件的全部行的行指針來排序全部行
Using index: 列數據是從僅僅使用了索引中的信息而沒有讀取實際的行動的表返回的，這發生在對表的全 部的請求列都是同一個索引的部分的時候
Using temporary: 看到這個的時候，查詢需要優化了。這裡，MySQL需要創建一個臨時表來存儲結果，這通常發生在對不同的列集進行ORDER BY上，而不是GROUP BY上
Using where: WHERE子句用於限制哪一個行匹配下一個表或發送到客戶。除非你專門從表中索取或檢查 所有行，如果Extra值不為Using where並且表聯接類型為ALL或index，查詢可能會有一些錯誤如果想要使查詢盡可能快，應找出Using filesort 和Using temporary的Extra值
Using sort_union(...), Using union(...), Using intersect(...): 這些函數說明如何為index_merge聯接類型合並索引掃描。
Using index for group-by: 類似於訪問表的Using index方式，Using index for group-by表示MySQL發現了一個索引，可以用來查詢GROUP BY或DISTINCT查詢的所有列，而不要額外搜索硬盤訪問實際的表。並且，按最有效的方式使用索引，以便對於每個組，只讀取少量索引條目不同連接類型的诠釋（按照效率高低的順序排序）
system: 表只有一行：system表。這是const連接類型的特殊情況
const: 表中的一個記錄的最大值能夠匹配這個查詢（索引可以是主鍵或惟一索引）。因為只有一行，這個值實際就是常數，因為MySQL先讀這個值然後把它當做常數來對待第一條SQL查詢語句
eq_ref: 對於每個來自於前面的表的行組合，從該表中讀取一行。這可能是最好的聯接類型，除了const類型。它用在一個索引的所有部分被聯接使用並且索引是UNIQUE或PRIMARY KEY。
ref 這個連接類型只有在查詢使用了不是惟一或主鍵的鍵或者是這些類型的部分（比如，利用最左邊前綴）時發生。對於之前的表的每一個行聯合，全部記錄都將從表中讀出。這個類型嚴重依賴於根據索引匹配的記錄多少―越少越好
ref: 對於每個來自於前面的表的行組合，所有有匹配索引值的行將從這張表中讀取。如果聯接只使用鍵的最左邊的前綴，或如果鍵不是UNIQUE或PRIMARY KEY（換句話說，如果聯接不能基於關鍵字選擇單個行的話），則使用ref。如果使用的鍵僅僅匹配少量行，該聯接類型是不錯的。ref可以用於使用=或<=>操作符的帶索引的列。MySQL> EXPLAIN SELECT * FROM student A WHERE studentId = "100001";
+----+-------------+-------+------+---------------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------------+---------+---------+-------+------+-------------+
| 1 | SIMPLE | A | ref | PRIMARY,i_studentId | PRIMARY | 35 | const | 1 | Using where |
+----+-------------+-------+------+---------------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)MySQL> EXPLAIN SELECT * FROM teacher_class A, student_class B where A.classId = B.classId AND B.classId = 2;
+----+-------------+-------+------+--------------------+--------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+--------------------+--------------------+---------+-------+------+-------------+
| 1 | SIMPLE | A | ref | fk_teacher_classId | fk_teacher_classId | 5 | const | 1 | Using where |
| 1 | SIMPLE | B | ref | fk_student_classId | fk_student_classId | 5 | const | 1 | Using where |
+----+-------------+-------+------+--------------------+--------------------+---------+-------+------+-------------+
2 rows in set (0.00 sec)
unique_subquery: 該類型替換了下面形式的IN子查詢的ref：MySQL> EXPLAIN SELECT * FROM student_class A WHERE A.classId IN (SELECT B.id FROM class B WHERE B.name = "04");
+----+--------------------+-------+-----------------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+-----------------+---------------+---------+---------+------+------+--------------------------+
| 1 | PRIMARY | A | ALL | | | | | 3 | Using where |
| 2 | DEPENDENT SUBQUERY | B | unique_subquery | PRIMARY,i_id | PRIMARY | 4 | func | 1 | Using index; Using where |
+----+--------------------+-------+-----------------+---------------+---------+---------+------+------+--------------------------+
2 rows in set (0.00 sec)
index_subquery: 該聯接類型類似於unique_subquery。可以替換IN子查詢，但只適合下列形式的子查詢中的非唯一索引：MySQL> EXPLAIN SELECT * FROM student A WHERE A.studentId IN (SELECT B.studentId FROM student_class B WHERE B.classId = 1);
+----+--------------------+-------+----------------+---------------------------------+--------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+----------------+---------------------------------+--------------+---------+------+------+--------------------------+
| 1 | PRIMARY | A | ALL | | | | | 3 | Using where |
| 2 | DEPENDENT SUBQUERY | B | index_subquery | fk_studentId,fk_student_classId | fk_studentId | 36 | func | 1 | Using index; Using where |
+----+--------------------+-------+----------------+---------------------------------+--------------+---------+------+------+--------------------------+
2 rows in set (0.00 sec)index_subquery 與 unique_subquery 不同之處是子查詢中的條件形式。一個是以索引為條件的。
range: 這個連接類型使用索引返回一個范圍中的行，比如使用>或<查找東西時發生的情況MySQL> EXPLAIN SELECT * FROM student_class A WHERE A.id BETWEEN 1 AND 2;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | A | range | PRIMARY | PRIMARY | 4 | | 1 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
index: 該聯接類型與ALL相同，除了只有索引樹被掃描。這通常比ALL快，因為索引文件通常比數據文件小。當查詢只使用作為單索引一部分的列時，MySQL可以使用該聯接類型。
MySQL> EXPLAIN SELECT count(*) FROM student_class;
+----+-------------+---------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | student_class | index | | PRIMARY | 4 | | 3 | Using index |
+----+-------------+---------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
MySQL> EXPLAIN SELECT A.* FROM teacher_class A, class B WHERE A.classId = B.id;
+----+-------------+-------+-------+--------------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+--------------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | B | index | PRIMARY,i_id | PRIMARY | 4 | | 2 | Using index |
| 1 | SIMPLE | A | ALL | fk_teacher_classId | | | | 2 | Using where |
+----+-------------+-------+-------+--------------------+---------+---------+------+------+-------------+
2 rows in set (0.00 sec)
ALL: 對於每個來自於先前的表的行組合，進行完整的表掃描。如果表是第一個沒標記const的表，這通常不好，並且通常在它情況下
很差。通常可以增加更多的索引而不要使用ALL，使得行能基於前面的表中的常數值或列值被檢索出。MySQL> EXPLAIN SELECT * FROM student A WHERE name = "wangwu";
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | A | ALL | | | | | 3 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
