学习mysql5-连接原理详情

单表访问与索引

访问查询方式

通过全局扫描查询：通过扫描该表的所有数据页，也就是所有的叶子节点，查询某一些符合条件的叶子节点记录，按自己对innodb的理解可能就是沿着b+树的叶子结点前后链表暴力扫
通过索引扫描查询：
1.走的是主键索引查询，之前在b+树讲过聚簇索引查找记录的方式，先通过非叶子节点比较出来是在那个数据页，在对该页的数据做一个遍历
2.走的是二级索引查询，通过二级索引的非叶子节点找到叶子节点上的主键记录，按照主键回表查聚簇索引的记录
3.当然也可能走二级索引也不需要回表，查询内容就是该索引和主键
4.范围查询，返回的是一段数据记录

查询方式关键字

先建一个例表：
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;

const：走的是聚簇索引或者是唯一的二级索引
ref：走的是非唯一的二级索引
ref_or_null：和ref差别就是多了个null元素的检索
range：上面的三种都是等值匹配，range走的是范围匹配的索引
index：只需要走二级索引查询，而不需要回表的查询方式
all：全表扫描

注意事项：很显然我们定义了多个二级索引，具体可能会选择性的使用其中一个，如何选择走哪一个最多的情况下，比较走了二级索引之后结果，选择还需要走回表的次数比较少的

索引合并

索引合并的场景是比较少的：
MySQL在⼀般情况下执⾏⼀个查询时最多只会⽤到单个⼆级索引，但不是还有特殊情况么，在这些特殊情况下也可能在⼀个查询中使⽤到多个⼆级索引，设计MySQL的⼤叔把这种使⽤到多个 索引来完成⼀次查询的执⾏⽅法称之为：index merge，具体的索引合并算法有下边三种
Intersection合并：一个sql语句条件and的时候，分成两个二级索引执行，之后取这两个索引返回值的交集，再走回表
Union合并：一个sql条件为or的时候，两个索引单独查结果，最后把结果合并
Sort-Union合并：一个sql的条件为or，同时是一个范围查找，能用到两个二级索引的结果，取到结果之后根据主键排序，回表返回结果

连接原理

连接过程简介

表连接的本质，是把表数据连接的拼凑返回的过程，实际过程中也常常需要表连接。如果单纯的连接，没有条件限制，那么表连接的过程实际就是两表的笛卡尔积，这个数量就是N*M的返回值
两表连接的整个过程，也是先取表一作为驱动表，按照sql查询表一的内容，之后把表二作为驱动表，有与表一的关联时，该列就成为了与常数对比的过程，所以驱动表一的查询结果，决定了需要对表二进行多少次的查询

内连接和外连接

内连接：inner join
假设表1有一个字段A，与表2的字段B业务含义是一致的。
那么select * from 表1 inner join 表2 on 表1.A = 表二.B，这个过程就是一个内连接的过程。其实我们常常在使用的过程中，会把这个sql写成select * from 表1，表2 where 表1.A = 表2.B，当然实际执行的效果是一样的，但是容易和其他的where条件混在一起，所以能用inner join 明确的部分，可以用第一种写法。

思考一下inner join在那些场景下可能是达不到业务需求的？
inner join其实是一种相对公平的join，并没有分主表和副表的一种join方式，那么与之对应的问题是，如果你想在A列和B列对应不上的情况下，也把A列表所在的内容返回回来，好像就没有办法完成。

另外内连接该如何选择哪一张作为驱动表？
我们很容易的想到小表作为驱动表，当然这里的小表并不是表数据的大小，而是说在这个sql中，比较驱动表A和驱动表B，哪一个得到的需要请求被驱动表的数量作为判断依据。
左外连接：left join
select * from 表1 left join 表2 on 表1.A = 表二.B，想象一下left join和上面说的内连接最大的本质是什么，无非就是有一张表作为主表，以主表的内容为核心，副表的内容作为附属部门，字段上没有关联的部分填充NULL
左外连接的驱动表固定为主表
右外连接：right join
select * from 表1 right join 表2 on 表1.A = 表二.B，右连接和左连接好像效果一模一样，能互相替换。
右外连接的驱动表固定为主表

基于块的嵌套循环连接（Block Nested-Loop Join）

这种驱动表只访问⼀次，但被驱动表却可能被多次访问，访问次数取决于对驱动表执⾏单表查询后的结果集中的记录条数的连接执⾏⽅式称之为嵌套循环连 接（Nested-Loop Join），这是最简单，也是最笨拙的⼀种连接查询算法。

使用索引加快连接速度：被驱动表的查询字段建立索引
建议join buff的方式加快速度：每次驱动表的一次返回结果，不是直接去被驱动表查，而是存到join buff里。统一的去做一次被驱动表的查询

优化器计算连接成本

查询成本

io成本：加载一个页到内存的成本
cpu成本：比较一个数据的成本

对于InnoDB存储引擎来说，页是磁盘和内存之间交互的基本单位，设计MySQL的⼤叔规定读取⼀个页⾯花费的成本默认是1.0，读取以及检测⼀条记录是否符合搜索条件的成本默认是0.2。1.0、0.2这些数 字称之为成本常数

单表查询成本

对于一个表的查询，如何计算单表的查询成本？
在⼀条单表查询语句真正执⾏之前，MySQL的查询优化器会找出执⾏该语句所有可能使⽤的⽅案，对⽐之后找出成本最低的⽅案，这个成本最低的⽅案就是所谓的执⾏计划，之后才会调⽤存储引擎提供的 接⼜真正的执⾏查询。

1. 根据搜索条件，找出所有可能使⽤的索引
2. 计算全表扫描的代价
3. 计算使⽤不同索引执⾏查询的代价
4. 对⽐各种执⾏⽅案的代价，找出成本最低的那⼀个

全表扫描的成本计算：
用到了两个关键的参数，一个是数据表的行数量Rows，当然不是走sql查询出来的数量，而是数据统计出来的一个有误差的值
另一个是Data_length，这个参数是该表占用数据的字节数，当然我们知道一个页默认是16k的大小，那么具体这个表占用了多少页Pages也就是DL/16/1024，所以我们很容易的计算出来成本大概是Pages1.0 + Rows0.2 +微调值
二级索引的成本计算：
查询一个范围区间的成本 ：n个区间范围1.0
需要回表的记录数量：x
读取这x的数量x0.2
回表的cpu成本行1.0
回表比较其他查询项的成本x0.2
那么整个的成本就是：n1.0 +x0.2 + x1.0 + x0.2

基于索引的成本计算

由上面的例子，看全表扫描的成本和走二级索引的成本可以看出来。n的值和x的值决定了二级索引的成本，首先如果说二级索引的区间数过多，那么二级索引本身需要的时候比较久，其次区间数多了之后，导致需要回表的数据量变大。

那么上面的x我们是怎么计算出来的呢，如果数据量不大的情况下我们能精准的计算出来数据量的大小，如果数据量大的情况下，只能以区间所在的最大和最小值中取数据页中的参考数据来估算，x值的大小。设计MySQL的⼤叔把这种通过直接访 问索引对应的B+树来计算某个范围区间对应的索引记录条数的⽅式称之为index dive
当然出现范围区间很多的情况下，提供了⼀个系统变量eq_range_index_dive_limit，当数量<eq_range_index_dive_limit的情况下，使用index dive来计算长度，再就是如果超出了这个长度，那么系统提供了基于索引表的统计数据，给出一个有误差的Cardinality 索引列中不重复值的数量，其实也就是一个用来在总数的环境下衡量平均出现的重复次数。

连接查询成本

MySQL中连接查询采⽤的是嵌套循环连接算法，驱动表会被访问⼀次，被驱动表可能会被访问多次，所以对于两表连接查询来说，它的查询成本由下边两个部分构成：
单次查询驱动表的成本
多次查询被驱动表的成本（具体查询多少次取决于对驱动表查询的结果集中有多少条记录）
我们把对驱动表进⾏查询后得到的记录条数称之为驱动表的扇出（英⽂名：fanout）。很显然驱动表的扇出值越⼩，对被驱动表的查询次数也就越少，连接查询的总成本也就越低。当查询优化器想计算整 个连接查询所使⽤的成本时，就需要计算出驱动表的扇出值

多表连接的成本分析

连接查询的成本计算公式是这样的：
连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本
对于左（外）连接和右（外）连接查询来说，它们的驱动表是固定的，所以想要得到最优的查询⽅案只需要：
分别为驱动表和被驱动表选择成本最低的访问⽅法。 可是对于内连接来说，驱动表和被驱动表的位置是可以互换的，所以需要考虑两个⽅⾯的问题：
不同的表作为驱动表最终的查询成本可能是不同的，也就是需要考虑最优的表连接顺序。
然后分别为驱动表和被驱动表选择成本最低的访问⽅法。 很显然，计算内连接查询成本的⽅式更⿇烦⼀些，下边我们就以内连接为例来看看如何计算出最优的连接查询⽅案。

mysql统计数据

永久性的统计数据

这种统计数据存储在磁盘上，也就是服务器重启之后这些统计数据还在
innodb_table_stats存储了关于表的统计数据，每⼀条记录对应着⼀个表的统计数据。
innodb_index_stats存储了关于索引的统计数据，每⼀条记录对应着⼀个索引的⼀个统计项的统计数据。

innodb_table_stats 直接看⼀下这个innodb_table_stats表中的各个列都是⼲嘛的：
字段名 描述
database_name 数据库名
table_name 表名
last_update 本条记录最后更新时间
n_rows 表中记录的条数
clustered_index_size 表的聚簇索引占⽤的页⾯数量
sum_of_other_index_sizes 表的其他索引占⽤的页⾯数量

n_rows统计项的收集 ：
采样收集，也就是采标本页中的数据量做平均值，最后计算估算值
clustered_index_size和sum_of_other_index_sizes统计项的收集：
从数据字典⾥找到表的各个索引对应的根页⾯位置。 系统表SYS_INDEXES⾥存储了各个索引对应的根页⾯信息。
从根页⾯的Page Header⾥找到叶⼦节点段和⾮叶⼦节点段对应的Segment Header。 在每个索引的根页⾯的Page Header部分都有两个字段：
PAGE_BTR_SEG_LEAF：表⽰B+树叶⼦段的Segment Header信息。
PAGE_BTR_SEG_TOP：表⽰B+树⾮叶⼦段的Segment Header信息。
从叶⼦节点段和⾮叶⼦节点段的Segment Header中找到这两个段对应的INODE Entry结构。 找到INODE Entry之后就能很快的知道三个链接结构下占用多少页+零散页

innodb_index_stats 直接看⼀下这个innodb_index_stats表中的各个列都是⼲嘛的：
字段名 描述
database_name 数据库名
table_name 表名
index_name 索引名
last_update 本条记录最后更新时间
stat_name 统计项的名称
stat_value 对应的统计项的值
sample_size 为⽣成统计数据⽽采样的页⾯数量
stat_description 对应的统计项的描述

目前我只关注某个索引中数据的重复数，通过sample_size设置采样数量

⾮永久性的统计数据

这种统计数据存储在内存中，当服务器关闭时这些这些统计数据就都被清除掉了，等到服务器重启之后，在某些适当的场景下才会重新收集这些统计数据。

与永久性的统计数据不同，⾮永久性的统计数据采样的页⾯数量是由innodb_stats_transient_sample_pages控制的，这个系统变量的默认值是8。
另外，由于⾮永久性的统计数据经常更新，所以导致MySQL查询优化器计算查询成本的时候依赖的是经常变化的统计数据，也就会⽣成经常变化的执⾏计划，这个可能让⼤家有些懵逼。不过最近 的MySQL版本都不咋⽤这种基于内存的⾮永久性统计数据了，所以我们也就不深⼊唠叨它了。

innodb_stats_method的使⽤
1.查询区间过多in查询，做估算
2.因为连接导致的不确定性需要查多少次被驱动表做估算

null值是否作为重复值？
可以系统自定义判断这个NULL值的估算逻辑