求大神给解释一下执行计划

Question

本帖最后由 yuanxulong198010 于 2014-03-31 17:14:52 编辑

Answer 1

不带/G的方式贴出你的 explain 以方便他人分析另外贴一下 show index from ..

Answer 2

+----+-------------+-------+-------+----------------------------------------------------------------+-------------------------+---------+--------------------+------+-----------------------------------------------------------+
| id | select_type | table | type  | possible_keys                                                  | key                     | key_len | ref                | rows | Extra                                                     |
+----+-------------+-------+-------+----------------------------------------------------------------+-------------------------+---------+--------------------+------+-----------------------------------------------------------+
|  1 | SIMPLE      | a     | ref   | idx_course_course_id                                        | idx_course_course_id | 93      | const              |    1 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | b     | ref   | idx_series,idx_series_course_id,idx_series_series_id | idx__series          | 93      | const              |    4 | Using where                                               |
|  1 | SIMPLE      | c     | ref   | series_id                                                      | series_id               | 93      | course.b.series_id |   18 | Using where                                               |
|  1 | SIMPLE      | d     | range | PRIMARY,idx_student_id,idx_video_id,idx_course_id_student_id   | PRIMARY                 | 152     | NULL               | 4636 | Using where                                               |
+----+-------------+-------+-------+----------------------------------------------------------------+-------------------------+---------+--------------------+------+--
这个感觉看不太清啊
mysql> show index from course;
+------------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name                | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tcourse |          0 | PRIMARY                 |            1 | id          | A         |         171 |     NULL | NULL   |      | BTREE      |         |               |
| course |          1 | idx_course_course_id |            1 | course_id   | A         |         171 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------
这样可以吗？大神

Answer 3

+----+-------------+-------+-------+----------------------------------------------------------------+-------------------------+---------+--------------------+------+-----------------------------------------------------------+
| id | select_type | table | type  | possible_keys                                                  | key                     | key_len | ref                | rows | Extra                                                     |
+----+-------------+-------+-------+----------------------------------------------------------------+-------------------------+---------+--------------------+------+-----------------------------------------------------------+
|  1 | SIMPLE      | a     | ref   | idx_course_course_id                                           | idx_course_course_id    | 93      | const              |    1 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | b     | ref   | idx_series,idx_series_course_id,idx_series_series_id           | idx__series             | 93      | const              |    4 | Using where                                               |
|  1 | SIMPLE      | c     | ref   | series_id                                                      | series_id               | 93      | course.b.series_id |   18 | Using where                                               |
|  1 | SIMPLE      | d     | range | PRIMARY,idx_student_id,idx_video_id,idx_course_id_student_id   | PRIMARY                 | 152     | NULL               | 4636 | Using where                                               |
+----+-------------+-------+-------+----------------------------------------------------------------+-------------------------+---------+--------------------+------+--
注意 ID 列都是1

Answer 4

对呀，ID列确实都是1，这不是因为没有子查询什么之类吗？我觉得这个正常啊，我现在不能理解的就是为什么只查了一行（rows＝1），但是后面还用到了Using temporary; Using filesort，这个我特别不理解，只有一行记录啊，怎么还需要用到临时表，用到文件排序？

Answer 5

由于在排序字段没有索引，所以必须用到use filesort
由于你的排序用到了两个表的字段而且没有索引，所以必须用到use temporary
跟你前面的子查询没关系

Answer 6

请问能给解释一下执行顺序吗？不应该是从上往下执行吗？那就应该是先查a表，可是查a表的话应该是用不到那些排序的，除非是从下往上执行，但我查资料说的是ID相同的是从上往下执行，请大侠给解释下好吗。

Answer 7

d你的ID都是1，表示查询处理器会将你的查询合并为一个查询，所以排序操作可能会写在第一列

Answer 8

感觉感谢benluobobo的耐心解答，那您的意思是ID相同的没有先后执行顺序了？还有，合并为一个查询是什么意思？谢谢 。

Answer 9

请benluobobo确认一下，是不是ID相同的执行顺序是从上到下的？你的意思我大概明白，就是ID相同的是一个查询，因此useing filesort只代表整个查询最终会使用，而不是在第一行会使用，是吧？