[TOC] # 单表访问方法 先建一张表，用作后面举例： ```sql CREATE TABLE single_table ( id INT NOT NULL AUTO_INCREMENT, key1 VARCHAR(100), key2 INT, key3 VARCHAR(100), key_part1 VARCHAR(100), key_part2 VARCHAR(100), key_part3 VARCHAR(100), common_field VARCHAR(100), PRIMARY KEY (id), KEY idx_key1 (key1), UNIQUE KEY idx_key2 (key2), KEY idx_key3 (key3), KEY idx_key_part(key_part1, key_part2, key_part3) ) Engine=InnoDB CHARSET=utf8; ``` 这张表除了聚簇索引外还有四个二级索引： - 以`key1`建立`idx_key1`索引。 - 以`key2`建立`idx_key2`唯一索引（没有重复）。 - 以`key3`建立`idx_key3`索引。 - 以`key_part1`、`key_part2`、`key_part3`建立`idx_key_part`联合索引。 ## 访问方法（access method）的概念 单个表的查询方法有以下两种： - 全表扫描 - 使用索引，细分为： - 针对主键或唯一二级索引等值查询 - 针对普通二级索引的等值查询 - 针对索引列的范围查询 - 直接扫描整个索引 `MySQL`执行查询语句的方式被称为`访问方法`或`访问类型`。 ## const 通过主键来定位记录： ``` SELECT * FROM single_table WHERE id = 1438; ```  通过二级索引来定位记录： ``` SELECT * FROM single_table WHERE key2 = 3841; ```  通过主键或者唯一二级索引列与常数的等值比较来定位一条记录的方法被称为`const`。 有一个例外就是 NULL，唯一索引不限制 NULL 的数量，因此下面这条语句可以查到多条内容： ``` SELECT * FROM single_table WHERE key2 IS NULL; ``` 这样的语句不能够用`const`访问方法来执行 ## ref 对于非唯一的普通二级索引： ``` SELECT * FROM single_table WHERE key1 = 'abc'; ``` 一次查询能够查到多条结果，如果结果不多，回表的代价还并不算太高，因此可以使用索引来进行查询。  这种访问方法被称为`ref`。 无论是唯一索引还是普通索引，对于 NULL 值的情况只能用`ref`。 对于联合索引，字段排列和索引一致的话也可以采用`ref`访问。 ``` SELECT * FROM single_table WHERE key_part1 = 'god like'; SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary'; SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary' AND key_part3 = 'penta kill'; ``` 但如果不连续或者有范围查询的话就不能称为`ref`了。 ``` SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 > 'legendary'; ``` ## ref_or_null 有时我们不仅想找出等值的记录，还想找出 NULL 的记录。 ``` SELECT * FROM single_demo WHERE key1 = 'abc' OR key1 IS NULL; ```  这种访问方法称为`ref_or_null`。 ## range 范围查询： ``` SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79); ```  这种访问方法被称为`range`。 ## index 像这种查询语句： ``` SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc'; ``` 筛选的列都包含在一个索引中，且条件也包含在这个索引中，就可以通过遍历索引的叶子节点来查询。这样的操作也不用回表。因为二级索引比聚簇索引小得多，所以成本小的多。 这种访问方法称为`index`。 ## all 全表扫描的访问方法就是`all`。 ## 注意事项 ### 复杂搜索条件下找出范围匹配的区间 ``` SELECT * FROM single_table WHERE (key1 > 'xyz' AND key2 = 748 ) OR (key1 < 'abc' AND key1 > 'lmn') OR (key1 LIKE '%suf' AND key1 > 'zzz' AND (key2 < 8000 OR common_field = 'abc')) ; ``` 分析过程： 看 WHERE 中用了哪些列，哪些是可以用索引的。`key1`、`key2`、`common_field`三个列里，`key1`和`key2`有唯一二级索引。 如果用`key1`的索引，把其他列都移除掉，直接写成 TRUE： ``` (key1 > 'xyz' AND TRUE ) OR (key1 < 'abc' AND key1 > 'lmn') OR (TRUE AND key1 > 'zzz' AND (TRUE OR TRUE)) ``` 简化： ``` (key1 > 'xyz') OR (key1 < 'abc' AND key1 > 'lmn') OR (key1 > 'zzz') ``` 符合`key1 < 'abc' AND key1 > 'lmn'`的永远是 FALSE，移除掉： ``` (key1 > 'xyz') OR (key1 > 'zzz') ``` 继续简化为 ``` key1 > xyz ``` 所以只要拿着上面的条件在`key1`的索引里查询以后再拿去用别的搜索条件过滤即可。 如果使用`key2`的索引，把其他列都移除掉，直接写成 TRUE： ``` (TRUE AND key2 = 748 ) OR (TRUE AND TRUE) OR (TRUE AND TRUE AND (key2 < 8000 OR TRUE)) ``` 继续简化，最后剩下： ``` TRUE ``` 也就是需要`key2`索引的所有列再回表，是得不偿失的，所以这种情况不会用`key2`的索引。 ### 索引合并 前面说查询时最多只会用到一个二级索引，但是也有特殊情况，可以使用多个索引来完成一次查询，称为`index merge`。 #### Intersection 合并 ``` SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b'; ``` 这种查询可以同时使用两个索引： - 从`idx_key1`索引取出`key1 = 'a'`的相关记录。 - 从`idx_key3`索引取出`key3 = 'b'`的相关记录。 - 二级索引最后存的都是`主键+索引列`的形式，所以只要将主键取交集，再回表即可。 因为读取索引是`顺序I/O`，而回表是`随机I/O`，成本会更低，所以这种情况使用两个索引更快。 只有语句满足以下情况时才会使用交集合并： - 二级索引必须等值匹配，对于联合索引必须每个列都等职匹配。 - 主键可以是范围匹配。 #### Union 合并 上面说的是交集关系，即查询的两个条件是`AND`的关系，所以最后取交集。如果查询的两个条件是`OR`的关系，那么最后就是取并集。 使用条件： - 二级索引必须等值匹配，对于联合索引必须每个列都等职匹配。 - 主键可以是范围匹配。 - 搜索条件是交集合并的结果。 但最终使用哪种方式，取决于优化器，在以下两种情况时偏向使用并集合并： - 单独根据搜索条件从某个二级索引中获取的记录数比较少 - 通过交集索引合并后需要回表的记录数大大减少 #### Sort-Union 合并 ``` SELECT * FROM single_table WHERE key1 < 'a' OR key3 > 'z' ``` 在以上的查询中，两个条件查出来的结果都不是按照主键排序的，因此不能直接交集。所以可以做这样的操作： - 先根据`key1 < 'a'`条件从`idx_key1`获取记录，并按照记录的主键值进行排序 - 先根据`key3 < 'z'`条件从`idx_key3`获取记录，并按照记录的主键值进行排序 - 两个排序好的主键序列进行交集 这种方法和`Union`相比多了一步排序，所以代价稍大。 另外，没有`Sort-Intersection`这种形式。因为`Sort-Union`的场景是根据索引获得的结果较少，这样排序的代价也不会太大，但`Intersection`的使用场景是结果太多，回表代价太大。在结果太多的情况下排序的成本也会上升，因此就没有`Sort-Intersection`了。 #### 使用联合索引取代合并 其实最佳的做法是直接给`key1`和`key3`一起建个联合索引，就没有上面这些事了。 # Join 原理 ## 连接的过程 ``` SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd'; ``` 查询中有三个过滤条件： - t1.m1 > 1 - t1.m1 = t2.m2 - t2.n2 < 'd' 大概的执行过程如下： - 第一个需要查询的表定义为`驱动表`，查询即从`const`、`ref`、`ref_or_null`、`range`、`index`、`all`这些方法中选择一个代价最小的去执行即可。  - 针对上一步的结果，去`t2`匹配记录，此时`t2`就是`被驱动表`。接下来就是两次单表查询。  在上述两个步骤中可以看出，驱动表只访问了一次，被驱动表被访问了多次。 ## 内连接和外连接 为了方便理解，建立两张有意义的表： ```sql CREATE TABLE student ( number INT NOT NULL AUTO_INCREMENT COMMENT '学号', name VARCHAR(5) COMMENT '姓名', major VARCHAR(30) COMMENT '专业', PRIMARY KEY (number) ) Engine=InnoDB CHARSET=utf8 COMMENT '学生信息表'; CREATE TABLE score ( number INT COMMENT '学号', subject VARCHAR(30) COMMENT '科目', score TINYINT COMMENT '成绩', PRIMARY KEY (number, score) ) Engine=InnoDB CHARSET=utf8 COMMENT '学生成绩表'; ``` 插入一些内容： ``` mysql> SELECT s1.number, s1.name, s2.subject, s2.score FROM student AS s1, score AS s2 WHERE s1.number = s2.number; +----------+-----------+-----------------------------+-------+ | number | name | subject | score | +----------+-----------+-----------------------------+-------+ | 20180101 | 杜子腾 | 母猪的产后护理 | 78 | | 20180101 | 杜子腾 | 论萨达姆的战争准备 | 88 | | 20180102 | 范统 | 论萨达姆的战争准备 | 98 | | 20180102 | 范统 | 母猪的产后护理 | 100 | +----------+-----------+-----------------------------+-------+ 4 rows in set (0.00 sec) ``` - 内连接：驱动表中的记录在被驱动表中`ON`不到匹配记录的情况下不会加入到结果集中 - 外连接：`ON`不到的情况下也加到结果集中 - 左外连接：选左侧表为驱动表 - 右外连接：选右侧表为驱动表 如果驱动表中的信息也不想全部加入结果集，可以使用： - 加 WHERE 子句进行过滤 - 加 ON 子句进行过滤。内连接时，这个子句效果和 WHERE一直，外链接时，匹配不到的记录，会用 NULL填充。 `ON`子句也成为`连接条件`。 ### 左（外）连接 ``` SELECT * FROM t1 LEFT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件]; ``` 在左连接里，左边的`t1`是驱动表，右边的`t2`是被驱动表。必须用`ON`子句来指出连接条件。 ``` mysql> SELECT s1.number, s1.name, s2.subject, s2.score FROM student AS s1 LEFT JOIN score AS s2 ON s1.number = s2.number; +----------+-----------+-----------------------------+-------+ | number | name | subject | score | +----------+-----------+-----------------------------+-------+ | 20180101 | 杜子腾 | 母猪的产后护理 | 78 | | 20180101 | 杜子腾 | 论萨达姆的战争准备 | 88 | | 20180102 | 范统 | 论萨达姆的战争准备 | 98 | | 20180102 | 范统 | 母猪的产后护理 | 100 | | 20180103 | 史珍香 | NULL | NULL | +----------+-----------+-----------------------------+-------+ 5 rows in set (0.04 sec) ``` ### 右（外）连接 ``` SELECT * FROM t1 [INNER | CROSS] JOIN t2 [ON 连接条件] [WHERE 普通过滤条件]; ``` ### 内连接 内外连接本质的的区别就是驱动表中不符合`ON`条件的记录会不会被加到最后的结果集里。 ``` SELECT * FROM t1 [INNER | CROSS] JOIN t2 [ON 连接条件] [WHERE 普通过滤条件]; ``` 也就是说，下面几种内连接的写法是等价的： - SELECT * FROM t1 JOIN t2; - SELECT * FROM t1 INNER JOIN t2; - SELECT * FROM t1 CROSS JOIN t2; 而且和以下这样的写法也是等效的。 - SELECT * FROM t1, t2; 以上介绍了很多内连接的方法，记住一种就好，`INNER JOIN`语义明确，建议使用这个。另外，内连接里`ON`和`WHERE`等价，所以不强制写。 ### 小结 ``` mysql> SELECT * FROM t1 INNER JOIN t2 ON t1.m1 = t2.m2; +------+------+------+------+ | m1 | n1 | m2 | n2 | +------+------+------+------+ | 2 | b | 2 | b | | 3 | c | 3 | c | +------+------+------+------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2; +------+------+------+------+ | m1 | n1 | m2 | n2 | +------+------+------+------+ | 2 | b | 2 | b | | 3 | c | 3 | c | | 1 | a | NULL | NULL | +------+------+------+------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM t1 RIGHT JOIN t2 ON t1.m1 = t2.m2; +------+------+------+------+ | m1 | n1 | m2 | n2 | +------+------+------+------+ | 2 | b | 2 | b | | 3 | c | 3 | c | | NULL | NULL | 4 | d | +------+------+------+------+ 3 rows in set (0.00 sec) ``` ## 连接的原理 ### 嵌套循环连接（Nested-Loop Join）  伪代码表示： ``` for each row in t1 { #此处表示遍历满足对t1单表查询结果集中的每一条记录 for each row in t2 { #此处表示对于某条t1表的记录来说，遍历满足对t2单表查询结果集中的每一条记录 for each row in t3 { #此处表示对于某条t1和t2表的记录组合来说，对t3表进行单表查询 if row satisfies join conditions, send to client } } } ``` 这是最简单的一种连接查询算法。 ### 借助索引 如题。 ### 基于块的嵌套循环连接（Block Nested-Loop Join） 由于实际情况的表经常难以全部装进内存，被驱动表又会被多次访问，导致会重复读取磁盘，非常消耗I/O，因此设计了`join buffer`的概念。  把驱动表的结果的一部分放进内存，与被驱动表多条记录匹配来减少I/O代价。 系统变量`join_buffer_size`就是这个 buffer 的大小，默认 256kb。 为了节省 join buffer 的使用，最好减少查询的列，不要用`*`去匹配，这样能多装一些记录。