MySQL,表连接的一些问题答疑

蓝色字体是对MySQL表的连接(两表连接， 多表连接 )常见疑问的回答.

create table tt1(id int primary key, a1 INT, c1 INT, key a1(a1));
create table tt2(id int primary key, a2 INT, c2 INT, key a2(a2));
insert into tt1 values(1,1,1),(2,2,2),(3,3,3),(4,4,4);
insert into tt2 values(1,1,2),(2,2,2);

1)顺序扫描（全表扫描/ 局部扫描）：
mysql> explain select c1 from tt1 where c1>0;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tt1   | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set

结论：type=ALL，说明是进行了全表扫描(由于tt1表里数据都满足c1>0,必然会扫描表里的数据)，物理上进行了顺序扫描，
     优化器选择type=ALL是因为c1上没有索引。

结论：type=ALL，说明是进行了全表扫描，物理上进行了顺序扫描，优化器选择type=ALL是因为c1上没有索引。
？？？我的疑问：这个例子，物理上进行了顺序扫描，也就是说，计算机顺序的在磁盘的磁道上按照物理地址进行扫描，
                如果第一个就扫到c1=1的记录，那么就会停下来扫描，将其读到内存里。
---回答：然后继续扫描后续的，直到所有的都扫描完成。注意不会扫到一个就停下来。
    如果没有扫到就继续扫直到扫到为止，如果运气不好的话就会把整表都扫了一遍才找到c1=1的记录。
    虽然type=ALL，只能说明是物理上进行了顺序扫描，有可能全扫描了这个表的记录，也有可以部分的扫描了表的记录。
    

2)索引扫描(物理上随机扫描)（范围扫描）：
mysql> explain select c1 from tt1 where id>0;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | tt1   | range | PRIMARY       | PRIMARY | 4       | NULL |    4 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set

结论：type=range,key=PRIMARY,说明是进行了基于索引的范围扫描。物理上通过索引进行了随机磁盘扫描。
？？？我的疑问：这个sql中，tt1表里的数据都满足了where条件，这种情况下，我觉得全表扫描比走索引是不是更好一些？还请师多多指正，非常感谢。
---回答：
---1 这里使用了索引扫描,执行器确实将进行索引扫描
---2 这是因为,优化器得到的结论(代价估算后)确实是索引扫描比全表扫描好
---3 但是,如果你增加记录(如上万),优化器经代价估算后,这种情况下通常会得出全表扫描是最优的.
---4 有的数据库的优化器,在你这个例子的情况下,会对小表(小数据量)的表进行一个"启发式"优化,认为小表做全表扫描优于索引扫描.

        
3 MySQL 的两表连接
mysql> explain select * from tt1,tt2 where tt1.id=tt2.c2;
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref         | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+-------------+
|  1 | SIMPLE      | tt2   | ALL    | NULL          | NULL    | NULL    | NULL        |    2 | Using where |
|  1 | SIMPLE      | tt1   | eq_ref | PRIMARY       | PRIMARY | 4       | test.tt2.c2 |    1 | NULL        |
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+-------------+
2 rows in set

结论：这例子使用的是嵌套循环连接算法，tt2是外表即驱动表，逐条的和内表（被驱动表）tt1里数据，按照连接条件进行匹配。

？？？我的非常大的疑惑：表连接的时候都是在内存里进行的，那么在本案例中数据是如何从外存中读到内存里的呢？
---回答: 先到数据缓存区找,找不到,发出IO申请从外存中读入,然后再进行连接.
      我的理解是首先将tt2表的数据（物理上是顺序的读取该的数据文件）全部读到内存里即把tt2表当作外表驱动表。
---外表的数据存于一个缓存区,不会全部读入的,缓存区大小是由极限的.     
   （为什么选tt2表当驱动表，因为本案例中tt2表的索引列没有使用上，并且tt2表的数据量少。不知道理解的对不对，请老师指正，谢谢。）
---优化器会比较两种连接方式，tt1-tt2和tt2-tt1，得出代价最小的。比较的方式就是对每个连接方式求总代价最小的（总代价 =  I/O 代价  + CPU代价  + Memory代价  + Remote代价 ）.     
  
   然后遍历内存里的tt2表的每一条数据，根据连接条件即tt1.id=tt2.c2，从tt1表里取出能匹配上的数据。
   那么具体怎么取tt1表的数据呢？这个案例里tt1.id上有索引，应该是通过索引把tt1表的数据文件里的数据逐条的读到内存里，
   最后和内存里的tt2表进行联合，就是最终的结果集。
---对。你可以假设这些数据都在内存，连接操作就是2层循环驱动下把满足连接条件的记录合并在一起生成一个新记录输出。     
   这些都是我的理解，不知道理解的对不对，这块是我非常困惑的地方，还请老师多多指导，非常感谢。

 

4 MySQL 的多表连接
-- --------------------------------------------------------------------------------------------------------------------------
指定连接方式： STRAIGHT_JOIN
mysql> explain select * from tt1,tt2;
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                 |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------------+
| 1  | SIMPLE      | tt2   | ALL  | NULL          | NULL | NULL    | NULL | 2    | NULL                                  |
| 1  | SIMPLE      | tt1   | ALL  | NULL          | NULL | NULL    | NULL | 4    | Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------------+
2 rows in set

结论：优化器选择tt2作为外表，驱动tt1表。
？？？ 关于多表连接我的想法：
  多个表连接中，表和表的组合数目是非常庞大的，如何快速的找到一个最优的组合，这又是一个难题。
  所以我想，控制在5个表以内进行连接，mysql的优化器的计算还比较准确地，
  如果超过5张表进行连接，mysql优化器的失误就会很大，这都是我的猜想呀，不知道对不对，希望老师多多指正，谢谢。
---MySQL最多限制63个表进行连接。MySQL多表连接算法=贪婪式算法+穷举算法+剪枝优化。
---MySQL默认支持：7个表的连接通常能得到最优的连接方式（常规说法，这是因为MySQL的多表连接算法中包括穷举的思想，且剪枝优化可以被关闭）。
---精确地说：MySQL所使用的贪婪算法不能保证每一种连接方式都被计算，总有一些连接方式会被这个算法无视而得不到参与计算的机会，所以难以总是得到最优的连接方式，但好处是加快了多报表连接的求解速度。