mysql 基于规则的执行计划

oracle 里有基于规则的执行计划，不过现在很少用了。mysql里也有这种，不过看起来很弱智，还是我的错觉，明天继续：

 

mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM teacher_class A, student_class B where A.classId = B.classId AND B.classId = 2;
+----+-----------+---------+-------------+----+---------+-----------+-------------+
| id | teacherId | classId | description | id | classId | studentId | description |
+----+-----------+---------+-------------+----+---------+-----------+-------------+
|  2 | 900002    |       2 | NULL        |  2 |       2 | 100002    | NULL        |
|  2 | 900002    |       2 | NULL        |  3 |       2 | 100003    | NULL        |
+----+-----------+---------+-------------+----+---------+-----------+-------------+
2 rows in set (0.01 sec)

mysql> SELECT * FROM student_class B,teacher_class A where A.classId = B.classId AND B.classId = 2;
+----+---------+-----------+-------------+----+-----------+---------+-------------+
| id | classId | studentId | description | id | teacherId | classId | description |
+----+---------+-----------+-------------+----+-----------+---------+-------------+
|  2 |       2 | 100002    | NULL        |  2 | 900002    |       2 | NULL        |
|  3 |       2 | 100003    | NULL        |  2 | 900002    |       2 | NULL        |
+----+---------+-----------+-------------+----+-----------+---------+-------------+
2 rows in set (0.00 sec)

mysql> show profiles;
+----------+------------+----------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                        |
+----------+------------+----------------------------------------------------------------------------------------------+
|        1 | 0.00133400 | SELECT * FROM teacher_class A, student_class B where A.classId = B.classId AND B.classId = 2 |
|        2 | 0.00111300 | SELECT * FROM student_class B,teacher_class A where A.classId = B.classId AND B.classId = 2  |
+----------+------------+----------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM teacher_class A, student_class B where A.classId = B.classId AND B.classId = 2;
+----+-----------+---------+-------------+----+---------+-----------+-------------+
| id | teacherId | classId | description | id | classId | studentId | description |
+----+-----------+---------+-------------+----+---------+-----------+-------------+
|  2 | 900002    |       2 | NULL        |  2 |       2 | 100002    | NULL        |
|  2 | 900002    |       2 | NULL        |  3 |       2 | 100003    | NULL        |
+----+-----------+---------+-------------+----+---------+-----------+-------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM student_class B,teacher_class A where A.classId = B.classId AND B.classId = 2;
+----+---------+-----------+-------------+----+-----------+---------+-------------+
| id | classId | studentId | description | id | teacherId | classId | description |
+----+---------+-----------+-------------+----+-----------+---------+-------------+
|  2 |       2 | 100002    | NULL        |  2 | 900002    |       2 | NULL        |
|  3 |       2 | 100003    | NULL        |  2 | 900002    |       2 | NULL        |
+----+---------+-----------+-------------+----+-----------+---------+-------------+
2 rows in set (0.00 sec)

mysql> show profiles;
+----------+------------+----------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                        |
+----------+------------+----------------------------------------------------------------------------------------------+
|        1 | 0.00133400 | SELECT * FROM teacher_class A, student_class B where A.classId = B.classId AND B.classId = 2 |
|        2 | 0.00111300 | SELECT * FROM student_class B,teacher_class A where A.classId = B.classId AND B.classId = 2  |
|        3 | 0.00087100 | SELECT * FROM teacher_class A, student_class B where A.classId = B.classId AND B.classId = 2 |
|        4 | 0.00065500 | SELECT * FROM student_class B,teacher_class A where A.classId = B.classId AND B.classId = 2  |
+----------+------------+----------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/758322/viewspace-680904/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/758322/viewspace-680904/