Mysql优化（出自官方文档） - 第二篇
目录
Mysql优化（出自官方文档） - 第二篇
1 关于Nested Loop Join的相关知识
1.1 相关概念和算法
Mysql在实现join的时候，采用的Nested Loop Join技术，join的方式还有其他两种：Hash Join和SortMergeJoin ,Mysql处于逻辑实现统一的角度，只实现了Nested Loop Join，假设有t1, t2, t3三张表，EXPLAIN优化的结果为：
Table Join Type
t1 range
t2 ref
t3 ALL那么其Join的方式可以用下面的为代表来表示：
for each row in t1 matching range {
for each row in t2 matching reference key {
for each row in t3 {
if row satisfies join conditions, send to client
}
}
}为了减少内表的IO，Mysql又引入了NestedLoopJoin的一个变种，叫做：Block Nested-Loop Join，简称为BNL，简单理解就是NestedLoopJoin With Buffer，为每一个join维护一个Join Buffer，对于外层循环，每次将扫描的行放入到Buffer中，内表直接对Buffer中的行进行匹配操作，对应的伪代码如下：
for each row in t1 matching range {
for each row in t2 matching reference key {
store used columns from t1, t2 in join buffer
if buffer is full {
for each row in t3 {
for each t1, t2 combination in join buffer {
if row satisfies join conditions, send to client
}
}
empty join buffer
}
}
}
if buffer is not empty {
for each row in t3 {
for each t1, t2 combination in join buffer {
if row satisfies join conditions, send to client
}
}
}1.2 一些优化
在Join语句中，除了
on语句后面的条件外，如果还存在where语句，假设有三张表t1, t2, t3，如下面的格式：select * from (t1,t2) on P1(t1,t2) inner join t3 on P2(t2,t3) where C1(t1) and C2(t2) and C3(t3);其
inner join的伪代码如下所示：FOR each row t1 in T1 such that C1(t1) { FOR each row t2 in T2 such that P1(t1,t2) AND C2(t2) { FOR each row t3 in T3 such that P2(t2,t3) AND C3(t3) { IF P(t1,t2,t3) { t:=t1||t2||t3; OUTPUT t; } } } }Mysql会采用条件下推的优化方式，提前将
where的条件限制在inner join的循环里面，这样子，如果C1的条件非常严苛，那么可以避免大量对t2, t3表的IO操作。需要注意的是：这种优化方式可能并不适用于outer join，outer join中where提前会导致不同的结果。outer join优化对于
left join，如果对于generated NULL row, 后面的where条件始终为false, 那么，这个left join可以被安全的转换为inner join，如下面的语句所示，假设t1.column1始终为NULL；SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;那么这条语句可被转换为
inner join：SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;解释：之所以会有这样的转换， 是因为这样做
t2便不需要进行全表扫描，只需要扫描出column2 = 5的行。有时候，在
prepare阶段，一些琐碎的条件可以直接被移除掉（特指Mysql8.0.14+的版本），而不是在优化器里面在进行移除，提前移除这些条件，可以让优化器尽可能的把left join转换为inner join，比如以下语句：SELECT * FROM t1 LEFT JOIN t2 ON condition_1 WHERE condition_2 OR 0 = 10 = 1始终为false，因此可以直接移除掉，移除后的结果：
SELECT * FROM t1 LEFT JOIN t2 ON condition_1 where condition_2此时，优化器便可以根据实际情况将该语句优化为
inner join（假设按照上一个规则优化）：SELECT * FROM t1 JOIN t2 WHERE condition_1 AND condition_2
outer join一些简化措施（重写或者转换）大多数情况下，
right join会在解析阶段直接被转换为left join，如下所示：(T1, ...) RIGHT JOIN (T2, ...) ON P(T1, ..., T2, ...)转换为的结果为：
(T2, ...) LEFT JOIN (T1, ...) ON P(T1, ..., T2, ...)有时候，当
join带有where条件时，并且where条件里面首先对inner table进行访问，那么这个时候，优化器将很难对其进行优化，比如下面的例子：SELECT * T1 LEFT JOIN T2 ON P1(T1,T2) WHERE P(T1,T2) AND R(T2)此时，Mysql会对
where条件进行null-rejected判断，如果判断成功，那么outer join就可以安全的被转换为inner join，需要注意的是，进行null-rejected判断的对象必须在内表上，原理很简单，因为对于outer join，如果内表没有匹配到的行，会进行null-complemented（对应的内表所有行被设置为NULL），所以，如果此时where条件对于内表的判断始终为false，那么null-complemented的行将会被过滤掉，此时``outer join的结果将等价于inner join的结果，对于下面的例子：T1 LEFT JOIN T2 ON T1.A=T2.Anull-rejected的条件包括（t2的列会被设置为NULL）：T2.B IS NOT NULL T2.B > 3 T2.C <= T1.C T2.B < 2 OR T2.C > 1下面的条件不属于
null-rejected，因为下面的条件对于null-complemented行可能为真：T2.B IS NULL T1.B < 3 OR T2.B IS NOT NULL T1.B < 3 OR T2.B > 3举例说明，比如下面的语句：
SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A LEFT JOIN T3 ON T3.B=T1.B WHERE T3.C > 0可以判断出，
T3.C > 0是一个典型的null-rejected条件，因此该语句可以被直接的转换为：SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A INNER JOIN T3 ON T3.B=T1.B WHERE T3.C > 0再来看一个更加复杂的例子：
SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A LEFT JOIN T3 ON T3.B=T2.B WHERE T3.C > 0与第一个例子唯一的趋避额就是
T2和T3 join的条件不同，此时join的对象变成了T3.B = T2.B，类似于第一个例子，上述语句可以直接转换为：SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A INNER JOIN T3 ON T3.B=T2.B WHERE T3.C > 0在Mysql中，
inner join是cross join带on的版本，上面这条语句和下面是等价的：SELECT * FROM (T1 LEFT JOIN T2 ON T2.A=T1.A), T3 WHERE T3.C > 0 AND T3.B=T2.B对于最外层的
T1和T2，可以看到where条件里面的T3.B = T2.B又是一个null-rejected条件，因此，外层的join也可以被直接优化为：SELECT * FROM (T1 INNER JOIN T2 ON T2.A=T1.A), T3 WHERE T3.C > 0 AND T3.B=T2.B经过上面的优化过程，最终的语句已经没有了
outer join，可以大幅度提高join的效率，因此根据该优化规则，我们写join的时候，也可以有效的利用这种优化方式。
1、cmd中安装app的的命令
2、探索 HD50
3、技能
4、office模板
5、如何进行用户行为分析并提高用户粘性