查询
单表查询
访问方法
- MySQL执行查询语句的方式称为访问方法或者访问类型。同一个查询语句可以有多个访问方法来执行，不同访问方法进行查询的结果保持一致，但是所需的成本不同。
- const 通过主键或者唯一二级索引列与常数的等值比较的访问方法。如果主键或者二级索引是由多个列来组成，则必须每一列都进行等值比较。
- 该访问方法被认为是常数级别的
- 利用二级索引进行查询，并且查询条件为NULL时，不采用const访问方法。
- ref 通过二级索引列与常数进行等值比较，并且形成的扫描区间为单点扫描区间的访问方法。
- 采用二级索引来进行查询时，每找到一条符合条件的二级索引，都会立即回表查询，而不是得到所有的主键id再统一进行回表。
- 当查询的值为NULL时，最多只能使用ref查询方法。
- 对于索引列中包含多个列的索引来说，只要左边的列与常数进行等值比较，都可以用ref，如果是范围比较，就不能用ref。
- ref_or_null 比ref方法多了搜索条件为NULL。
- 值为NULL的记录会被放在索引树的最左边
- range 使用索引查询时，对应的扫描区间为若干个单点扫描区间或者范围扫描区间的单表访问方法。
- index 扫描全部二级索引记录的访问方法。
- index访问方法的应用场景可以如下：查询结果是覆盖索引中对应的列，但是搜索条件不是按索引列的从左到右来的，可能是中间某个列或者某几个列。但是由于所要的结果在索引中已经存在，不需要再次回表查询了，仅需要扫描该索引即可。
- all 执行全表扫描的访问方法。
索引合并
- 索引合并 使用多个索引来完成一次查询的方法
- Intersection索引合并 利用不同的索引分别进行扫描，得到的结果取交集，再利用得到的结果来进行回表。
要求使用二级索引的必须是等值查询，并且如果是联合索引，要求每一个索引列都要覆盖。原因：当二级索引的值相同时，叶子节点是根据主键排列的，便于取交集，并且在后续回表的过程中更有利于读取信息，而不是随机I/O。
select * from table where key1 = 10 and key2 = 20; - Union索引合并 利用不同的索引分别进行扫描，得到的结果取并集，再利用得到的结果来进行回表。
同样要求用二级索引的必须是等值查询，并且如果是联合索引，要求每一个索引列都要覆盖。原因：便于取并集，回表时的效率也高。
select * from table where key1 = 10 or key2 = 20; - sort-union索引合并 利用不同索引分别进行扫描，得到结果先排序再取并集。
不要求二级索引必须等值查询，也不要求联合索引的每一个列都得是等值查询
select * from table where key1 > 10 and index_part1 = 1;
连接查询
- 连接的本质就是把各个表中的记录都取出来进行一次匹配，并把匹配后的组合发送给客户端
- 驱动表即第一个需要查询的表，再根据驱动表到被驱动表中找匹配的记录。也就是说，驱动表只需要访问一次，被驱动表可能需要访问多次。每获得一条驱动表记录，都需要到被驱动表中寻找匹配的记录。
内连接
- 对于进行内连接查询的表，如果驱动表中的记录在被驱动表中没找到对应的，那就不会加入结果集中。
- 内连接的查询基本步骤如下
- 选取驱动表，适用与驱动表相关的过滤条件，选取代价最低的单表访问方法来执行对驱动表的单表查询
- 对步骤1中查询驱动表中得到的结果集中的每一条记录，都分别到被驱动表中查找匹配的记录。
外连接
- 对于进行外连接查询的表(包括左外连接和右外连接)，即使驱动表中的记录在被驱动表中没找到对应的，还是会加入到结果集。
- 左外连接 - 选择左侧的表为驱动表
- 右外连接 - 选择右侧的表为驱动表
where子句中不符合条件的记录，不管是外连接还是内连接，都不会加入结果集。
on子句的处理方式就很符合内外连接的区别。如果不符合条件，在内连接中就不会加入结果集，但是在外连接中会加入结果集。
计算成本
- 一条查询语句的执行成本包括I/O成本和CPU成本。前者指把记录从磁盘加载到内存中的时间，后者指读取记录、判断记录是否满足搜索条件、对结果进行排序等操作所损耗的时间。
- 成本常数
- 读取一个页面的成本默认为1.0
- 检测一个记录是否符合搜索条件的成本默认是0.2
示例：假设某查询语句需要用到索引uk_key2，且其对应的搜索条件是key2 > 10 AND key2 < 1000，即对应的扫描区间是(10, 1000);
根据 查询成本 = I/O成本 + CPU成本 = （索引I/O成本 + 记录I/O成本）+（索引CPU成本 + 记录CPU成本）分别计算对应的值
- 索引I/O成本 所谓I/O成本即是将数据页从磁盘读取到内存的成本，MySQL认为有多少个区间就相当于有多少个页面，示例中只有一个页面，因此
索引I/O成本 = 1 × 1.0- 索引CPU成本 所谓CPU成本即是对记录读取、判断等操作的成本，因此需要先计算有多少条记录。计算过程为：先找到区间最左记录和区间最右记录（由于B+树中定位一条记录是常数级别的速度，因此该消耗忽略不计），然后判断两条记录的距离。如果两条记录相隔不太远，就直接读取中间数据页中的Page Header中的PAGE_N_RECS属性（代表当前页面有多少条记录），直接相加即可；如果两条记录相隔很远，则沿着区间最左记录所在页面向右读取10个页面，取每个页面记录的平均值，再乘以页面数就是总记录条数(记为N)。因此
索引CPU成本 = N × 0.2- 记录I/O成本 上一步已经算出总记录条数，此时需要进行回表来判断每条记录是否都符合除索引条件以外的其他条件，即需要将所在页面读取到内存中。因此
记录I/O成本 = N × 1- 记录CPU成本 判断每条记录的具体情况。因此
记录CPU成本 = N × 0.2
单表查询成本
- 执行一条查询语句的基本步骤
- 根据搜索条件，查找所有能用的索引
- 计算全表扫描的成本
- 计算使用各种索引的成本
- 对比各种执行方案的代价，成本最低的方案就是所谓的执行计划
连接查询成本
- 当多张表连接查询时，驱动表访问一次，被驱动表访问多次，因此连接查询的成本是由单次查询驱动表的成本和多次查询被驱动表的成本组成。
- 连接查询成本 = 单次访问驱动表的成本 + 驱动表扇出值 × 单次访问被驱动表的成本
- 扇出 - 查询驱动表后得到的记录条数
- 对于左外或者右外连接来说，驱动表是固定的。因此只需要分别为驱动表和被驱动表选择成本最低的访问方法
- 对于内连接来说，驱动表和被驱动表可以互换，因此既需要考虑不同的表作为驱动表，又需要考虑当前驱动表下，驱动表和被驱动表的成本最低访问方法