oracle 里有基于规则的执行计划，不过现在很少用了。mysql里也有这种，不过看起来很弱智，还是我的错觉，明天继续：
mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)
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)
+----+-----------+---------+-------------+----+---------+-----------+-------------+
| 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)
+----+---------+-----------+-------------+----+-----------+---------+-------------+
| 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)
+----------+------------+----------------------------------------------------------------------------------------------+
| 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)
+----+-----------+---------+-------------+----+---------+-----------+-------------+
| 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)
+----+---------+-----------+-------------+----+-----------+---------+-------------+
| 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)
+----------+------------+----------------------------------------------------------------------------------------------+
| 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博客 ” ，链接：https://blog.itpub.net/758322/viewspace-680904/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
mysql的Covering Index
请登录后发表评论
登录
全部评论
