mysql是怎样运行的_MySQL 是怎样运行的

时隔10多天，才开始第五部分的总结，这部分主要跟着大佬的脚步“MySQL 是怎样运行的：从根儿上理解 MySQL”，学习MySQL单表查询，多表查询，以及访问方式的选择， 这一部分主要是Mysql内部的查询选择

单表访问方法

对于单表查询， MySQL 把查询执行方式大致分为两种使用全表扫描

使用索引查询

根据实际情况有可以细分很多种类针对主键或唯一二级索引的等值查询

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

针对索引列的范围查询

直接扫描整个索引

MySQL执行查询语句的方式称之为访问方法或者访问类型。同一个查询语句可能可以使用多种不同的访问方法来执行，虽然最后的查询结果都是一样的，但是执行的时间可能差很多

访问方式

const

针对主键或唯一二级索引的等值查询(mysql 利用主键值直接在聚簇索引中定位对应的用户记录)

ref

对某个普通的二级索引列与常数进行等值比较 (由于二级索引不唯一导致可能先找到多条对应记录， 然后需要在回表到聚簇索引中查找完整的用户记录， 回表代价不确定)

唯一二级索引不限制NULL的数量， 所以 SELECT * FROM single_table WHERE key2 IS NULL; 使用的ref 访问方式。可以这样简单理解，通过二级索引先查找在进行回表操作定位完整数据

ref_or_null

想找出某个二级索引列的值等于某个常数的记录，还想把该列的值为NULL的记录也找出来

range

索引列需要匹配某个或某些范围的值

index

需要的数据可以直接通过二级索引记录直接得到， 不需要进行回表操作

all

全表扫描 把聚簇索引中的记录都依次和给定的搜索条件做一下比较

注意事项

明确range访问方法使用的范围区间

对于B+树索引来说，只要索引列和常数使用=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、=、<=、BETWEEN、!=(不等于也可以写成<>)或者LIKE操作符连接起来，就可以产生一个所谓的区间。

索引合并

MySQL在一般情况下执行一个查询时最多只会用到单个二级索引，但在游一些特殊情况下也可能在一个查询中使用到多个二级索引。简单点理解就是通过多个索引先把满足条件的数据在索引页中筛选出来后， 在根据主键进行回表操作Intersection合并 (交集合并)

一般分为2种情况二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况。

主键列可以是范围匹配(二级索引的用户记录是由索引列 + 主键构成的)

二级索引等值匹配， 主键索引范围

以搜索条件中出现2个索引的等值匹配为例， 如果2个搜索条件都是二级索引

只读取一个二级索引的成本：

- 按照某个搜索条件读取一个二级索引

- 根据从该二级索引得到的主键值进行回表操作，然后再过滤其他的搜索条件

读取多个二级索引之后取交集成本：

- 按照不同的搜索条件分别读取不同的二级索引

- 将从多个二级索引得到的主键值取交集，然后进行回表操作

由于读取二级索引的操作是顺序I/O, 但是回表操作时随机I/O， 所以如果只读取一个二级索引时需要回表的记录数特别多，而读取多个二级索引之后取交集的记录数非常少，当节省的因为回表而造成的性能损耗比访问多个二级索引带来的性能损耗更高时，读取多个二级索引后取交集比只读取一个二级索引的成本更低。

- Union合并(并集合并)

- 二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只出现匹配部分列的情况。

- 主键列可以是范围匹配

- 使用Intersection索引合并的搜索条件

- Sort-Union合并

先按照二级索引记录的主键值进行排序，之后按照Union索引合并方式执行的方式称之为Sort-Union索引合并

多表查询

连接原理

连接 的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用户, 像这样的结果集就可以称之为笛卡尔积

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

步骤1：选取驱动表，使用与驱动表相关的过滤条件，选取代价最低的单表访问方法来执行对驱动表的单表查询。

步骤2：对上一步骤中查询驱动表得到的结果集中每一条记录，都分别到被驱动表中查找匹配的记录。

内连接和外连接驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集的是外连接， 反之为内连接。

左连接又可以根据选取的驱动表不同，分为左外连接(选取左侧的表为驱动表)， 右外连接(选取右侧的表为驱动表)。

对于内连接来说，驱动表和被驱动表是可以互换的，并不会影响最后的查询结果

对于外连接来说，左外连接和右外连接的驱动表和被驱动表不能轻易互换。

连接的查询算法嵌套循环连接

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

使用索引加快连接速度

扫描被驱动表时可以通过索引加快速度

基于块的嵌套循环连接

查询驱动表的得到的结果集存放在一个名为 join buffer(连接查询前申请的一块固定大小的内存) 的地方, 开始扫描被驱动表时，每一条被驱动表的记录一次性和join buffer中的多条驱动表记录做匹配，避免一次访问的驱动表的数据只和1条驱动表的得到的结果比对, 所以这样可以显著减少被驱动表的I/O代价。