Mysql 学习（十一）mysql基于规则的优化

最新推荐文章于 2024-05-11 11:00:05 发布

程序胖

最新推荐文章于 2024-05-11 11:00:05 发布

阅读量394

点赞数

文章标签： mysql 学习数据库

本文链接：https://blog.csdn.net/qq_41816516/article/details/130621666

版权

mysql 是一个软件，也就是我们查询的工具，所以底层会有一个功能，就是查询重写，根据一些规则来优化我们查询语句，优化主要从下面几个方面：
- 查询条件
- 连接
- 子查询

条件化简

我们先来看看如何优化查询语句

移除不必要的括号

例子：((a = 3 and b = c) or ((a>c) and (c < 5)))
因为有些括号是可以不必要存在，所以可以去掉，优化之后
(a = 5 and b = c) or (a > c and c < 5)

常量传递

例子： a = 5 and b > a
查询条件分别为 a = 5 和 b > a，因为他们两个查询条件是且的关系，所以是取并集，b > a 的等式就可以换成 b > 5

等值传递

例子：a = b and b = c and c = 5
查询条件分别为 a = b 和 b = c 和 c = 5，因为这三个条件都是且的关系，所以可以优化成 a = 5 and b = 5 and c =5

移除没用的条件

例子：( a < 1 and 1 = 1) or (a = 6 or 1 != 1)
查询条件 1 = 1 永远是 true 而 1 != 1 永远是 false 所以我们可以简化一下 (a < 1 and true) or (a = 6 or false) 继续简化就可以得到 a < 1 or a = 6

优化表达式计算

例子：a = 2 + 3
在查询之前，如果表达式只包含常量的话，就会给计算出来，但是某列不是以单独的形式作为表达式的操作数时，优化器是不会进行化简得。

HAVING子句和WHERE子句的合并

如果查询语句没有出现聚合函数，优化器就会把having的查询条件和where的查询条件放在一起查询

外连接消除

我们想来说说内连接和外连接的区别：
- 内连接的驱动表和被驱动表是可以相互变换的，外连接的驱动表和被驱动表是固定的
- 而内连接的驱动表的记录如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录会被舍弃。对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL值填充。
知道区别之后，我们思考一个问题，有没有一种情况，会让内连接和外连接没什么区别？
答案是有的，比如SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2 WHERE t2.n2 IS NOT NULL;
正常来说，t1 左外连接 t2 ，假设 t1.m1 有值没有匹配上 t2.m2 的时候，这个值也会被记录，并且 t2 的值都会变成 null，但是现在where条件让 t2.n2 不能为 null ，所以就会导致这个值不能被记录，所以我们完全可以把这个语句升级为
SELECT * FROM t1 innerJOIN t2 ON t1.m1 = t2.m2
再举个例子：SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2 WHERE t2.m2 = 2;
在这个例子中，我们在WHERE子句中指定了被驱动表t2的m2列等于2，也就相当于间接的指定了m2列不为NULL值，所以上面的这个左（外）连接查询其实和下面这个内连接查询是等价的：SELECT * FROM t1 INNER JOIN t2 ON t1.m1 = t2.m2 WHERE t2.m2 = 2;
我们把这种在外连接查询中，指定的WHERE子句中包含被驱动表中的列不为NULL值的条件称之为空值拒绝，在被驱动表的WHERE子句符合空值拒绝的条件后，外连接和内连接可以相互转换。这种转换带来的好处就是查询优化器可以通过评估表的不同连接顺序的成本，选出成本最低的那种连接顺序来执行查询。

子查询优化

子查询是怎么运行的？

为了了解子查询在mysql中是怎么运行的，所以我们首先要创建一个表，然后s1 和 s2 两张表结构跟下面一样：

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;

子查询主要分为两种：
- 一种是不相关子查询
- 一种是相关子查询
两种类型子查询的方式是不同的
不相关子查询：
- 例子：SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2);
- 执行过程：
  - 先运行子查询 SELECT common_field FROM s2
  - 然后将子查询的结果当作参数执行外层查询SELECT * FROM s1 WHERE key1 IN (..)
- 总结：对于包含不相关的标量子查询或者行子查询的查询语句来说，MySQL会分别独立的执行外层查询和子查询，就当作两个单表查询就好了。
相关子查询：
- 例子：SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE s1.key2 = s2.key2);
- 执行过程：
  * 先从外层查询获取一条记录，也就是从 s1 中获取一条记录
  * 然后根据这条记录跑到子查询SELECT common_field FROM s2 WHERE s1.key2 = s2.key2中查询
  * 最后根据子查询的查询结果来检测外层查询WHERE子句的条件是否成立，如果成立，就把外层查询的那条记录加入到结果集，否则就丢弃。
  * 然后循环遍历以上三个条件