MySQL 查询优化

标签：WHERE 表中物化 MySQL 查询优化连接 SELECT

我们在写一些 mysql 语句时。可能会写一些耗费性能的语句。这时候我们可以利用查询重写将糟糕的语句转化成某种可以高效执行的形式。

条件化简

将我们写的复杂表达式进行简化。

1. 移除不必要的括号

举例：

可将SELECT * FROM (t1, (t2, t3)) WHERE ...化简为SELECT * FROM t1, t2, t3 WHERE ...

2. 常量替代

举例：

a = 5 AND b > a 可被转化为 a = 5 AND b > 5 （注：or 操作符不可以）

3. 移除没用的条件

比如说移除一些永真或永假的表达式。

4. 表达式计算

如果表达式中只包含常量的话，它的值会被先计算出来

在索引条件中。只有索引列和常数使用某些运算符连接起来，才可能形成合适的范围区间来减少需要扫描的记录数量。

5. HAVING 和 WHERE 子句的合并

如果查询语句中没有出现聚合函数，那么having子句和where子句可以合并。

6. 常量表

使用主键等值匹配或者唯一二级索引列等值匹配作为搜索条件来查询某个表。

外连接消除

因为内连接可能通过优化表的连接顺序来降低整体的查询成本，所以考虑将外连接尽量转化成内连接。

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

优化器会将右连接查询转化成左连接查询。

在外连接查询中，如果指定where子句中被驱动表中的列不为NULL值的条件称为空值拒绝。符合空值拒绝后，外连接和内连接可以相互转换。

子查询优化

子查询基础：
IN：IN关键词用于筛选某个列中的数据，如果它们与给定值列表中的任何一个匹配，就会被选中。

示例：
sqlCopy code
SELECT * FROM Customers WHERE Country IN ('USA', 'Canada', 'UK');
上面的查询将选择那些位于"USA"、"Canada"或"UK"国家的客户。
ANY 和 ALL：这两个关键词通常与子查询一起使用，用于比较子查询的结果与外部查询的条件。
ANY：如果子查询中的任何一个结果满足外部查询条件，就返回结果。

示例：
SELECT ProductName FROM Products WHERE Price > ANY (SELECT Price FROM SpecialOffers);
上面的查询将返回价格高于特别优惠产品中任何一个产品的产品名称。
ALL：只有当子查询中的所有结果都满足外部查询条件时，才返回结果。

示例：
SELECT ProductName FROM Products WHERE Price > ALL (SELECT Price FROM SpecialOffers);
上面的查询将返回价格高于特别优惠产品中所有产品的产品名称。
EXISTS：EXISTS关键词用于检查子查询是否返回结果，如果子查询返回了任何结果，那么外部查询将返回数据。

示例：
SELECT OrderID FROM Orders WHERE EXISTS (SELECT * FROM OrderDetails WHERE OrderDetails.OrderID = Orders.OrderID);
上面的查询将返回那些至少有一个订单详情的订单ID。

1. 标量子查询优化

单独执行子查询并将子查询得到的结果作为外层查询的参数，再执行外层查询

2. 行子查询优化

从外层查询中获取一条记录。从这条记录中找出子查询中涉及的值
执行子查询, 得到子查询的查询结果。
根据子查询的查询结果来检测外层查询where子句的条件是否成立。

3. IN 子查询优化

不相关IN子查询

物化表转连接

不直接将不相关子查询的结果集当做外层查询的参数，而是将子查询的列去重写入临时表中。如何对记录进行去重呢？答：为表中的列建立主键或者唯一索引。

如果子查询结果集不会特别大，会为它建立基于内存的使用memory存储引擎的临时表，并且会为此建立hash索引。

如果子查询结果非常大，超过了系统变量tmp_table_size或者max_heap_table_size的值，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也相应的转变为了B加树索引。

物化表：将此查询结果集中的记录保存到临时表的过程称为物化。这个临时表称为物化表。

得到物化表之后。可以从子查询物化表和表的角度来看待。也就是说，他们两个表其实建立了一个内连接。查询优化器就可以评估不同连接顺序需要的成本来决定选择成本更低的方案执行查询。

子查询转半连接

考虑不使用物化表直接将子查询转换为连接。

包含IN子查询的查询和两表连接查询之间并不完全等价。主要是因为对于S1表中的某条记录来说，如果S2表中要有大于等于两条的记录满足条件，那么记录就会多次加入最终的结果集中。因此提出了半连接的概念：将S1表和S2表进行半连接的意思就是对于S1表当中的一条记录来说，我们只关心在S2表中是否存在与之匹配的记录，而不关心具体有多少条记录匹配最终的结果集。结果集中只保留S1表的记录。

半连接的实现：

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

当子查询的查询列表中只有主键或者唯一索引列时，可以把子查询的表上拉到外层查询的from子句中。（原因是因为主键或者唯一索引列的数据本身就不重复）
Duplicate Weedout （重复值消除）

目的就是将重复值消除。我们可以建一个只含有主键的临时表。然后每次当S1表中的记录要加入结果集中，就把这条记录的ID加到这个临时表中。如果添加成功。那么就把该记录添加到最终的结果集。如果添加失败，说明已经存在，就丢弃它。这种利用临时表消除半连接结果集中的重复值的方式称为重复值消除。
Loose Scan（松散扫描）

对于下列查询：
```
SELECT * FROM s1 WHERE key3 IN (SELECT key1 FROM s2 WHERE key1 > 'a' AND key1 < 'b')
```
这个语句的子查询中，S2表可以使用到Key1列的索引，而子查询的查询列表恰好就是Key1列。这样将S2作为驱动表时，在扫描S2表的index_key1索引时，相同的记录只要扫描一次即可。这种虽然是扫描索引，但只取键值相同的第一条记录去执行匹配操作的方式称为松散扫描。
Semi-join Materialization 半连接物化

使用物化表进行半连接的过程（前面有介绍）
FirstMatch 首次匹配

前文中行子查询优化中使用的方法。

IN子查询转化为EXISTS子查询

例子：

原始查询（使用 IN）：

SELECT ProductName FROM Products WHERE CategoryID IN (SELECT CategoryID FROM Categories WHERE CategoryName = 'Electronics');

将其转换为使用 EXISTS 的查询：

SELECT ProductName FROM Products AS P
WHERE EXISTS (SELECT 1 FROM Categories AS C WHERE C.CategoryID = P.CategoryID AN

目的是：有时子查询在执行时不能使用索引，但是将它转化为EXISTS子查询后却可以使用索引。

4. ANY/ALL 子查询优化

比如将 <ANY (SELECT inner_expr ...) 转换为 < (SELECT MAX(inner_expr) ...)

5. [NOT] EXISTS 子查询

如果exists子查询是不相关子查询，可以先执行子查询得到结果是true还是false，然后重写原先的查询语句进行化简。

如果exists子查询是相关子查询，那么就需要用原始的查询方式来执行。如果有索引，速度也会加快不少。

6. 对于派生表的优化

1. 将派生表物化

也就是将派生表的结果集写到一个内部的日志表中，然后把这个物化表当做普通表一样参与查询。不过， MYSQL 使用了一种称为延迟物化的策略，也就是在查询中真正使用到派生表时才会尝试物化派生表，而不是在执行查询之前就先把派生表物化。（延迟物化的举例：先到外层表中执行WHERE语句查询，如果外层表中没用满足的记录，也就不需要将派生表物化了）

2. 将查询重写为没用派生表的形式

可以先将派生表与外层查询合并，然后将派生表中的搜索条件放到外层查询的搜索条件中。就会消除掉派生表。但当派生表中含有聚集函数，DISTINCT、GROUP BY、HAVING、LIMIT。这种情况就不能与外层查询合并。

总结： MYSQL在执行带有派生表的查询时，优先尝试把派生表和外层查询进行合并，如果不行，再把派生表物化掉，然后执行查询。

标签：WHERE,表中,物化,MySQL,查询,优化,连接,SELECT
From： https://www.cnblogs.com/vLiion/p/17788972.html