mysql(4) 笔记

最新推荐文章于 2024-05-21 09:46:31 发布

qq_46146522

最新推荐文章于 2024-05-21 09:46:31 发布

阅读量354

点赞数

分类专栏： mysql 文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/qq_46146522/article/details/126935646

版权

mysql 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

MySQL基于规则的优化

条件化简

常量传递

eg: a=5 AND b>a 会被优化为 a=5 AND b>5

！！！ OR 不能传递；OR只需要一个条件成立即可，b>a 成立不仅仅是a = 5的情况；

等值传递

有时候多个列之间存在等值匹配的关系，比如这样：

a = b and b = c and c = 5

这个表达式可以被简化为：

a = 5 and b = 5 and c = 5

同样的， OR 不能用

移除没用的条件

对于一些明显永远为 TRUE 或者 FALSE 的表达式，优化器会移除掉它们，比如这个表达式：

(a < 1 and b = b) OR (a = 6 OR 5 != 5)

很明显， b = b 这个表达式永远为 TRUE ， 5 != 5 这个表达式永远为 FALSE ，所以简化后的表达式就是这样

的：

(a < 1 and TRUE) OR (a = 6 OR FALSE)

可以继续被简化为

a < 1 OR a = 6

表达式计算

a= 5+1 ==》 a=6;

但是如果某个列并不是以单独的形式作为表达式的操作数时，比如出现在函数中，出现在某个更复杂表达式中，优化器是不会尝试对这些表达式进行化简的，对列进行计算或者加函数都会使用不到索引

HAVING字句和 WHERE 字句的合并

如果查询语句中没有出现诸如 SUM 、 MAX 等等的聚集函数以及 GROUP BY 子句，优化器就把 HAVING 子句和

WHERE 子句合并起来。

外连接消除

外连接和内连接的本质区别就是：对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用 NULL值填充；而内连接的驱动表的记录如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录会被舍弃

凡是不符合WHERE子句中条件的记录都不会参与连接。只要我们在搜索条件中指定关于被驱动表相关列的值不为 NULL ,在这种情况下：外连接和内连接也就没有什么区别了! 注意只要where中指定被驱动表不能为null，都可，例如 SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2 WHERE t2.m2 = 2;

SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2 WHERE t2.n2 IS NOT NULL;

子查询优化

子查询含义

按字查询返回的结果集区分子查询

标量子查询返回一个值
行子查询返回一行数据
列子查询返回一列数据
表子查询返回多行多列数据

相关子查询：

如果子查询的执行需要依赖于外层查询的值，我们就可以把这个子查询称之为相关子查询。比如：

SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2 WHERE n1 = n2); n1 是t1表的列；

子查询的一些注意事项：

在 SELECT 子句中的子查询必须是标量子查询。
对于 [NOT] IN/ANY/SOME/ALL 子查询来说，子查询中不允许有 LIMIT 语句。
对于子查询中的order by，distinct，没有聚集函数以及 HAVING 子句的 GROUP BY 语句都没意义（但不报错），order by，distinct语句对最后查询到的结果集处理，子查询只是中间的一个集合，不需要处理；
不允许在一条语句中增删改某个表的记录时同时还对该表进行子查询。比如： DELETE FROM t1 WHERE m1 < (SELECT MAX(m1) FROM t1);

子查询的具体执行及优化

对于标量子查询和行子查询

不相关子查询：先查询子查询得出结果集，再将结果集带入到外层查询执行

相关子查询：先从外层查询中获取一条记录，带入到子查询中去执行看是否有结果，有结果的话再拿到外层查询中去执行，得出最后结果；

IN 子查询优化

不直接将不相关子查询的结果集当作外层查询的参数，而是将该结果集写入一个临时表里，写入临时表的记录会被去重，一般情况下子查询结果集不会大的离谱，所以会为它建立基于内存的使用 Memory 存储引擎的临时表，而且会为该表建立哈希索引。如果子查询的结果集非常大，超过了系统变量 tmp_table_size 或者 max_heap_table_size ，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为 B+ 树索引。将子查询结果集中的记录保存到临时表的过程称之为物化

物化表转连接（join），评估连接成本，选取连接成本最低的方案执行；

将in子查询直接转换为semi-join（半连接）

SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = ‘a’);

SELECT s1.* FROM s1 INNER JOIN s2 ON s1.key1 = s2.common_field WHERE s2.key3 = ‘a’;

其实可以将子查询直接转化为 join，但有一些小问题，假设我们的子查询查出了多条重复的值（这里的重复是common_field 重复）在做连接时，相同的s1.key1 = s2.common_field 就会被连接多次；但我们本意是 key1 在 in（…）字句中有一个对应的值即可；总的来说就是in（）字句中的一个值在join连接中可能会连接多次，但我们只需要一次结果即可；

由此诞生了半连接，它会把连接中的重复连接去掉；更准确的说：对于 s1 表的某条记录来说，我只关心在 s2 表中是否存在与之匹配的记录是否存在，而不关心具体有多少条记录与之匹配，最终的结果集中只保留 s1 表的记录。

semi—join （半连接）在mysql 语法中是没有的，它只是一个概念上的连接方式；

半连接具体实现：

Table pullout （子查询中的表上拉）

当子查询的查询列表处只有主键或者唯一索引列时，可以直接把子查询中的表上拉到外层查询的 FROM 子句

中，并把子查询中的搜索条件合并到外层查询的搜索条件中，比如这个

SELECT * FROM s1

WHERE key2 IN (SELECT key2 FROM s2 WHERE key3 = ‘a’);

DuplicateWeedout execution strategy （重复值消除）

LooseScan execution strategy （松散索引扫描）

对于 SELECT * FROM s1

WHERE key3 IN (SELECT key1 FROM s2 WHERE key1 > ‘a’ AND key1 < ‘b’);

这个查询，对于 s2 表的访问可以使用到 key1 列的索引，而恰好子查询的查询列表处就是 key1 列，在将该查询转换为半连接查询后，如果将 s2 作为驱动表执行查询的话，将s2中首条满足条件的记录拿出（由于key1 是二级索引，可能有多个相同的key1 记录行），在与s1进行连接；这里只从s2表取出首条满足条件的记录啊，这种就称之为松散索引扫描；

FirstMatch execution strategy （首次匹配）

！！！注意：由于相关子查询并不是一个独立的查询，所以不能转换为物化表来执行查询。

ANY/ALL****子查询优化

原始表达式 转换为

< ANY (SELECT inner_expr …) < (SELECT MAX(inner_expr) …)

> ANY (SELECT inner_expr …) > (SELECT MIN(inner_expr) …)

< ALL (SELECT inner_expr …) < (SELECT MIN(inner_expr) …)

> ALL (SELECT inner_expr …) > (SELECT MAX(inner_expr) …)