mysql count 查询优化方案

转载

技术博客领航者 2024-11-21 15:13:38

文章标签 mysql count 查询优化方案子查询半连接内连接 文章分类 MySQL 数据库

当我们往黑窗口输入一条SQL给Mysql执行，Mysql 查询优化器会按照一定的规则帮我们对SQL进行重写，优化，本文将会讲解：

Mysql基于规则的优化

条件化简：
常量表检测
外连接消除
子查询

不相关标量子查询
标量相关子查询
IN子查询优化

临时表
半连接的实现方法
半连接的使用条件：

小结

条件化简：

条件化简就是我们平时很常见的消除一些冗余的条件，如：

移除不必要的括号
常量传递
比如：a = 5 AND b > a可以转换为a = 5 AND b > 5
等值传递
比如：a = b and b = c and c = 5 可以转换为 a = 5 and b = 5 and c = 5
移除没用的条件
比如：(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

看完上面的条件化简是不是挺简单的，我们继续往下看：

常量表检测

常量表指的是对该表的查询成本是常量级别的，非常快的。比如表里没有数据或只有一条数据（针对MyIsam存储引擎），或者在Innodb中按照主键等值匹配、按照唯一二级索引等值匹配。在这种情况下，mysql会先把涉及到该表的条件全部替换为常数，然后再分析其余表的查询成本。

举个例子：

SELECT * FROM table1 INNER JOIN table2 ON table1.column1 = table2.column2 WHERE table1.primary_key = 1;

因为这里涉及到对table1的常量查询，所以可以把table1匹配的结果直接查出来并替换为常量。

SELECT table1表记录的各个字段的常量值, table2.* FROM table1 INNER JOIN table2 
ON table1表column1列的常量值 = table2.column2;

这个地方是要提醒的，因为我之前的理解：这种查询会先采用两表做笛卡尔积之后再匹配查询条件，显然那样的成本是更高的。

外连接消除

首先内连接（inner join）与外连接（left join、right join）的区别在于：

外连接的驱动表，如果无法在被驱动表找到匹配ON子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表的各字段用NULL填充。

而内连接如果无法在被驱动表找到匹配ON子句中的过滤条件的记录，那么该记录不会加入到结果集中。

如果我们在查询中指定被驱动表的相关列的值不为NULL，那么在这种情况下，内连接与外连接就没有什么区别了。此时mysql查询优化器就可以自由评估不同连接顺序的成本，来选择成本最低的连接顺序。

如：

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

这里指定了被驱动表t2的n2列不能为null，所以这里的左连接与内连接是等价的，等同于：

SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2;

子查询

在讲解子查询之前，我们新建两张表，表的结构一样，并插入一定量的数据：

create table single_table1
(
    id           INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    key1         VARCHAR(100),
    key2         INT,
    key3         VARCHAR(100),
    key_part1    VARCHAR(100),
    key_part2    VARCHAR(100),
    key_part3    VARCHAR(100),
    common_field VARCHAR(100),
    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 = utf8mb4;

create table single_table2
(
    id           INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    key1         VARCHAR(100),
    key2         INT,
    key3         VARCHAR(100),
    key_part1    VARCHAR(100),
    key_part2    VARCHAR(100),
    key_part3    VARCHAR(100),
    common_field VARCHAR(100),
    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 = utf8mb4;

不相关标量子查询

比如：

SELECT * FROM s1 WHERE key1 = (SELECT common_field FROM s2 WHERE key3 = 'a' LIMIT 1);

我们看到子查询的查询条件是key3，外查询的查询条件是key1，是不相关的条件。

它的执行计划就和我们正常想的一样，先执行子查询的结果，然后代入到外层查询执行。

标量相关子查询

SELECT * FROM s1 WHERE key1 = (SELECT common_field FROM s2 WHERE s1.key3 = s2.key3 LIMIT 1);

先从外层s1表获取一条记录，然后将该条记录的值代入到子查询中，如果满足子查询，则加入到结果集中，否则丢弃。然后遍历下一条记录。

以上两种都是正常的执行方式，没有很特殊的优化。

IN子查询优化

比如：

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

可能我们的想法是先执行子查询，然后将子查询的结果代入外层查询，嗯，我之前也是这么想的。

实际上，mysql在in子查询上做了很多优化。原因是如果in子查询的结果比较少，其实还好，查询效率还是很高的，一旦in子查询的结果比较多，那效率就低了。

所以mysql对此有哪些优化呢？

临时表

临时表：将in子查询的结果写入到一张临时表中，子查询要查询的列可以作为表的主键或唯一索引。

如果in子查询结果集不是很大，那么临时表是基于Memory存储引擎的，如果很大，就会采用基于磁盘的表（采用B+索引），这个边界值由变量tmp_table_size或者max_heap_table_size控制。

show variables like '%tmp_table_size%';
show variables like '%max_heap_table_size%';

mysql count 查询优化方案_内连接

mysql count 查询优化方案_mysql count 查询优化方案_02

我看了一下，默认值是32MB。

虽然建立临时表也是一个成本，但我们来看一下现在情况，

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

最后我们要查询的结果集，可以看成是：

1、扫描s1表，取出s1表中key1列的值，看是否存在于临时表中。

2、扫描临时表，把子查询的结果分别去s1表中找出key1=xxx的记录

也就是说上边的查询相当于s1表与临时表的内连接。

SELECT s1.* FROM s1 INNER JOIN materialized_table ON key1 = m_val;

其中materialized_table 就是我们创建的临时表，这个过程在mysql中叫做物化，m_val就是物化表中的存储common_field值的列名。

当上述in查询优化为内连接后，mysql就可以评估不同的连接方式的成本从而选择成本更低的连接查询方式啦。

不过你发现没有，如果单纯地认为，in子查询可以转换为内连接，是有问题的。

问题1：如果临时表中的值不是唯一的（就是说同一个common_field值存在多个），那么在in子查询中不会有问题，而在内连接中，就有可能出现相同的两条数据。这是不对的。所以我们需要对临时表进行去重。在Mysql中，为了去除重复列导致结果不对的问题还提出了一个半连接的概念（semi-join，当然这是一种优化手段，我们写sql的时候是用不到的），

半连接就是用来解决重复列的问题，它只考虑驱动表中的数据是否存在于被驱动表中，而不关注有多少条匹配。

半连接的实现方法

为了落地半连接这个概念，会有几种不同的实现方法：

1、Table pullout （表上拉），这种主要针对子查询的列是主键或唯一索引列，就是我们前面说的最好的情况，就是子查询的列是不重复的，那么直接改为内连接就可以了。

SELECT s1.* FROM s1 INNER JOIN materialized_table ON key1 = m_val;

2、DuplicateWeedout execution strategy（重复值消除），这也是我们前面说过的，既然子查询有重复值，那我们就消除重复值呗。如果子查询只有一列，那我们可以将这一列作为临时表的主键，就可以消除重复值了，消除后就同第一种方案了。
3、LooseScan execution strategy （松散索引扫描）
这种情况主要针对in子查询的查询条件是索引列的情况。如：

SELECT * FROM s1 WHERE key3 IN (SELECT key1 FROM s2 WHERE key1 > 'a' AND key1 < 'b');

子查询中的key1列是索引列，可以把s2作为驱动表，通过扫描key1列的索引，取key1列不同的值到s1表中匹配数据记录。

4、FirstMatch execution strategy （首次匹配）
这个就是半连接在关联表时的语义实现，扫描驱动表，取出一条驱动表中的数据，到被驱动表中匹配，当匹配到第一条之后就将该条数据加入到结果集中。

半连接的使用条件：

SELECT ... FROM outer_tables WHERE expr IN (SELECT ... FROM inner_tables
...) AND ...
或
SELECT ... FROM outer_tables WHERE (oe1, oe2, ...) IN (SELECT ie1, ie2,
... FROM inner_tables ...) AND ...

如果子查询后有or条件，或者是not in,子查询中有union，有聚集函数等情况就无法使用半连接进行优化了，这个要记住。

另外，如果in子查询不符合半连接的条件，mysql会在以下两次策略中选择成本更低的方案：
1、将子查询物化之后再执行查询
2、执行IN to EXISTS转换（因为任何in子查询都可以转换为exsits查询）