《MySQL是怎么样运行的》读书笔记三--单表查询和两表连接

单表访问方法

查询的执行方式大致分为下边两种：

使用全表扫描进行查询

使用索引进行查询。

唯一二级索引就是作为索引的那列值都是唯一的，没有重复的。

const

意思是常数级别的，代价是可以忽略不计的。不过这种const访问方法只能在主键列或者唯一二级索引列和一个常数进行等值比较时才有效

ref

用普通二级索引找到对应记录的id值，然后再回表到聚簇索引中查找完整的用户记录。

ref_or_null

有时候我们不仅想找出某个二级索引列的值等于某个常数的记录，还想把该列的值为NULL的记录也找出来

range

SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);

index

没懂，建议回去看。。。

all

使用全表扫描执行查询的方式称之为：all。

索引合并

Intersection合并（交集合并）

SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';

• 二级索引的记录都是由索引列 + 主键构成的，所以我们可以计算出这两个结果集中id值的交集。

• 按照上一步生成的id值列表进行回表操作，也就是从聚簇索引中把指定id值的完整用户记录取出来，返回给用户。

Union合并

SELECT * FROM single_table WHERE key1 = 'a' OR key3 = 'b'

Sort-Union合并

连接的原理

连接查询的结果集中包含一个表中的每一条记录与另一个表中的每一条记录相互匹配的组合，像这样的结果集就可以称之为笛卡尔积

在MySQL中，连接查询的语法也很随意，只要在FROM语句后边跟多个表名就好了

mysql> SELECT * FROM t1, t2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    1 | a    |    2 | b    |
|    2 | b    |    2 | b    |
|    3 | c    |    2 | b    |
|    1 | a    |    3 | c    |
|    2 | b    |    3 | c    |
|    3 | c    |    3 | c    |
|    1 | a    |    4 | d    |
|    2 | b    |    4 | d    |
|    3 | c    |    4 | d    |
+------+------+------+------+
9 rows in set (0.00 sec)

设置过滤条件删除过大的笛卡尔积：

涉及单表的条件：如t1.m1 > 1是只针对t1表的过滤条件

涉及两表的条件：如t1.m1 = t2.m2、t1.n1 > t2.n2。

1.首先确定第一个需要查询的表，这个表称之为驱动表。

2.根据t1表中的记录去找t2表中的记录，所以t2表也可以被称之为被驱动表。

在两表连接查询中，驱动表只需要访问一次，被驱动表可能被访问多次。

内连接和外连接

概念：

• 对于内连接的两个表，驱动表中的记录在被驱动表中找不到匹配的记录，该记录不会加入到最后的结果集，我们上边提到的连接都是所谓的内连接。

• 对于外连接的两个表，驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集。

外连接：

• 左外连接

  选取左侧的表为驱动表。

• 右外连接

  选取右侧的表为驱动表。

where和on的区别：

• WHERE子句中的过滤条件

  WHERE子句中的过滤条件就是我们平时见的那种，不论是内连接还是外连接，凡是不符合WHERE子句中的过滤条件的记录都不会被加入最后的结果集。

• ON子句中的过滤条件

  对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL值填充。

内连接中的WHERE子句和ON子句是等价的。 

左外连接：

SELECT * FROM t1 LEFT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件];

 内连接：INNER JOIN

嵌套循环连接

这种驱动表只访问一次，但被驱动表却可能被多次访问，访问次数取决于对驱动表执行单表查询后的结果集中的记录条数的连接执行方式称之为嵌套循环连接（Nested-Loop Join） 

使用索引加快连接速度

在嵌套循环连接的步骤2中采用索引查询

基于块的嵌套循环连接（Block Nested-Loop Join）

扫描一个表的过程其实是先把这个表从磁盘上加载到内存中，然后从内存中比较匹配条件是否满足。 内存里可能并不能完全存放的下表中所有的记录，所以在扫描表前边记录的时候后边的记录可能还在磁盘上，等扫描到后边记录的时候可能内存不足，所以需要把前边的记录从内存中释放掉。

oin buffer就是执行连接查询前申请的一块固定大小的内存，先把若干条驱动表结果集中的记录装在这个join buffer中，然后开始扫描被驱动表，每一条被驱动表的记录一次性和join buffer中的多条驱动表记录做匹配，因为匹配的过程都是在内存中完成的，所以这样可以显著减少被驱动表的I/O代价