MySQL阅读笔记——8.表访问

8.1 单表访问

1. 全表扫描

2. 使用索引查询

   1. 针对主键或唯一二级索引等值查询

   2. 针对普通二级索引等值查询

   3. 针对索引列范围查询（in子句 也可以划分为范围查询）

   4. 扫描整个索引列

即使访问通过索引列查询数据，也不一定会使用索引，当MySQL优化器判断查询二级索引+回表的代价比全表扫描小的时候才会使用索引，如果有大量回表操作，执行 随机I/O 的成本太大，则会倾向于使用 全表扫描

8.1.1 访问方法

1. const：通过 主键 或者 唯一二级索引 与常数等值比较定位一条数据记录

2. ref：普通索引 与常数等值比较定位到一条或者多条记录

3. ref_or_null：普通索引列 与常数等值比较并且还把该列值为null也查出来（col is null）

4. range：索引列 进行范围查询 （即：in、between、不等关系等）

5. index：查询结果集覆盖索引，并且查询条件没有符合 最左原则 没有用到索引（在 联合索引 中）

   该种访问方式因为查询结果覆盖 联合索引 因此只是扫描 二级索引 取出符合条件的记录，没有 回表 操作

6. all：直接从 聚集索引 最小记录开始依次扫描

8.1.2 合并

所谓 合并 就是将在 一定条件下 将查询条件中用到的 所有索引 进行一次查询得到结果集（主要是主键值）在 回表 前对得到的 主键值 进行和并（主要是 Intersection（求交集） 和 union（求并集））操作

因为读取索引是 顺序I/O ，而回表是 随机I/O，读取多个 二级索引 后对结果（主键值）进行合并（交集或者并集），可能减少 回表 的记录数（交集），也可能将离散的记录主键填充为连续的主键，从而在 聚集索引 上进行一些 顺序I/O

1. Intersection合并：

   查询中where 用到多个索引条件用and连接，则在一定条件下可能会用到 Intersection合并 （条件是：如果是普通索引则必须是 等值匹配，如果是联合索引则 每个列必须等值匹配；条件中的 主键 可以范围查询 ）

2. Union合并：

   查询中where 用到多个索引条件用or连接，则在一定条件下可能会用到 Union合并 （条件是：如果是普通索引则必须是 等值匹配，如果是联合索引则 每个列必须等值匹配；条件中的 主键 可以范围查询 ）

需要满足上述条件是因为，二级索引是以 索引列+主键 存储的，并且排序先参照索引列再参照主键列，通过上述规则查询 二级索引 得到的结果集都是 按照主键值排序 的，对这种结果集求交集、求并集的算法效率会很高O(n) ​ 具体的算法就是逐个取出两个索引 最小的主键值 进行比较，求交集是不相等则丢弃当前小值取下一个值与另一个集合比较，如果相等加入到结果集；求并集是逐步取最小值，不同则加入到结果集，如果相同则丢弃一个。

8.2 连接访问

　　连接的本质就是将各个连接表的记录依次按照条件取出来匹配组合，并将结果返回给用户。其中第一个查询的表称为 驱动表 之后查询的表都称为 被驱动表 。 ​ 两表连接中如果将不匹配的记录（MySQL将不匹配列以列值为null的方式）返回到结果集中的连接方式叫做 外连接；结果集只包含匹配的记录叫做 内连接（内连接where和on语句等价）

一般将单表过滤条件放到where语句中，将连接表过滤条件放到on语句中（也称为连接条件）

　　驱动表 只查询一次，每条查询的结果都需要到 被驱动表 中查询匹配的记录，需要多次访问被驱动表 这个过程是涉及到从磁盘到内存加载数据，可能效率会很低，因此为了 尽可能减少访问被驱动表的次数 MySQL设计者提出了 join buffer 的概念，在连接查询前开辟一块固定大小的内存区域，将若干 驱动表 查询过滤后得到的结果集存储到这块内存区域中（最好全部存下），然后扫描 被驱动表 与内存中的 驱动表 结果集做匹配，整个过程只扫描了一遍 被驱动表，并且整个过程都是在内存中完成的，可以显著减少了 被驱动表 不断的从磁盘加载到内存的 I/O代价，这种方式叫做 基于块的嵌套连接（Block Nested-Loop Join）

join buffer区域可以通过系统变量join_buffer_size设置大小