MySQL阅读笔记——8.表访问
-
全表扫描
-
使用索引查询
-
针对主键或唯一二级索引等值查询
-
针对普通二级索引等值查询
-
针对索引列范围查询（in子句 也可以划分为范围查询）
-
扫描整个索引列
-
即使访问通过索引列查询数据，也不一定会使用索引，当MySQL优化器判断
查询二级索引+回表的代价比全表扫描小的时候才会使用索引，如果有大量回表操作，执行 随机I/O 的成本太大，则会倾向于使用 全表扫描
8.1.1 访问方法
-
const：通过 主键 或者 唯一二级索引 与
常数等值比较定位一条数据记录 -
ref：普通索引 与
常数等值比较定位到一条或者多条记录 -
ref_or_null：普通索引列 与
常数等值比较并且还把该列值为null也查出来（col is null） -
range：索引列 进行
范围查询（即：in、between、不等关系等） -
index：查询结果集覆盖索引，并且查询条件没有符合 最左原则 没有用到索引（在 联合索引 中）
该种访问方式因为查询结果覆盖 联合索引 因此只是扫描 二级索引 取出符合条件的记录，没有 回表 操作
-
all：直接从 聚集索引 最小记录开始依次扫描
8.1.2 合并
所谓 合并 就是将在 一定条件下 将查询条件中用到的 所有索引 进行一次查询得到结果集（主要是主键值）在 回表 前对得到的 主键值 进行和并（主要是 Intersection（求交集） 和 union（求并集））操作
因为读取索引是 顺序I/O ，而回表是 随机I/O，读取多个 二级索引 后对结果（主键值）进行合并（交集或者并集），可能减少 回表 的记录数（交集），也可能将离散的记录主键填充为连续的主键，从而在 聚集索引 上进行一些 顺序I/O
-
Intersection合并：
查询中
where用到多个索引条件用and连接，则在一定条件下可能会用到 Intersection合并 （条件是：如果是普通索引则必须是 等值匹配，如果是联合索引则 每个列必须等值匹配；条件中的 主键 可以范围查询 ） -
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设置大小
