mysql> explain select t1.* from t2 ,t1 where t2.id=t1.id and t2.id<3;\
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------------+
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 134 | Using where |
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 392625 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------------+
2 rows in set (0.00 sec)
mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
| 134 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from t2 where id<3;
+----------+
| count(*) |
+----------+
| 128 |
+----------+
1 row in set (0.00 sec)
驱动表 一般返回较少的记录:
mysql> explain select t1.* from (select * from t2 where id<3) t2 ,t1 where t2.id=t1.id;
+----+-------------+------------+------+---------------+-------------+---------+--------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+-------------+---------+--------------+--------+-------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 392625 | Using where |
| 1 | PRIMARY | <derived2> | ref | <auto_key0> | <auto_key0> | 5 | DEVOPS.t1.id | 10 | NULL |
| 2 | DERIVED | t2 | ALL | NULL | NULL | NULL | NULL | 134 | Using where |
+----+-------------+------------+------+---------------+-------------+---------+--------------+--------+-------------+
3 rows in set (0.00 sec)
是预估的需要扫描的行数
不是返回的行数
这就是两者的区别
Oracle 是预估返回的行数
MySQL 是预估扫描的行数,显示需要扫描的行数
mysql> analyze table t2;
+-----------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------+---------+----------+----------+
| DEVOPS.t2 | analyze | status | OK |
+-----------+---------+----------+----------+
1 row in set (0.17 sec)
mysql> explain select t1.* from t2 ,t1 where t2.id=t1.id and t2.id=3;
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------------+
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 16192 | Using where |
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 392625 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------------+
2 rows in set (0.00 sec)
mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
| 16390 |
+----------+
1 row in set (0.01 sec)
mysql> select count(*) from t2 where id=3;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.01 sec)