codeigniter - 与多个where条件批量更新_Mysql 优化之 条件过滤

点击上方「Java有货」关注我们

技术交流群添加方式

+

添加小编微信:372787553，备注:进群带您进入Java技术交流群

条件过滤 

    在连接处理中，前缀行是从连接中的一个表传递到下一个表的那些行。通常，优化程序会尝试在连接顺序的早期放置前缀计数较低的表，以防止行组合的数量迅速增加。在某种程度上，优化器可以使用有关从一个表中选择并传递到下一个表的行的条件的信息，它可以更准确地计算行估计并选择最佳执行计划。

如果不使用条件过滤，则表的前缀行数将WHERE根据优化器选择的访问方法，根据子句选择的估计行数进行计算 。条件过滤使优化器可以WHERE在访问方法未考虑的子句中使用其他相关条件 ，从而改善其前缀行数估计。例如，即使可能存在基于索引的访问方法，该方法可用于在连接中从当前表中选择行，但是在表中可能还存在其他条件。WHERE 子句可以过滤(进一步限制)传递给下一张表的合格行的估计值。

仅在以下情况下，条件才有助于过滤估计：

• 它引用当前表。

• 它取决于连接序列中一个或多个常量值。

• 访问方法尚未考虑它。

在EXPLAIN输出中，该 rows列指示所选访问方法的行估计，并且该filtered 列反映条件过滤的效果。 filtered值以百分比表示。最大值为100，这表示未过滤行。值从100减小表示过滤量增加。

前缀行计数(估计从当前表通过连接传递到下一个表的行数)是rows和 filtered值的乘积。即，前缀行数是估计的行数，该估计的行数由于估计的滤波效果而减少。例如，如果rows为1000且filtered为20％，那么条件过滤会将估算的行数1000减少为前缀行数1000×20％= 1000×.2 = 200。

考虑以下查询：

SELECT *
  FROM employee JOIN department ON employee.dept_no = department.dept_no
  WHERE employee.first_name = 'John'
  AND employee.hire_date BETWEEN '2018-01-01' AND '2018-06-01';

假设数据集具有以下特征：

• 该employee表有1024行。

• 该department表有12行。

• 两个表在上都有一个索引dept_no。

• 该employee表的索引为 first_name。

• 8行满足以下条件 employee.first_name：

  employee.first_name = 'John'
  

• 150行满足以下条件 employee.hire_date：

  employee.hire_date BETWEEN '2018-01-01' AND '2018-06-01'
  

• 1行满足以下两个条件：

  employee.first_name = 'John'
  AND employee.hire_date BETWEEN '2018-01-01' AND '2018-06-01'
  

没有条件过滤，将 EXPLAIN产生如下输出：

+----+------------+--------+------------------+---------+---------+------+----------+
| id | table      | type   | possible_keys    | key     | ref     | rows | filtered |
+----+------------+--------+------------------+---------+---------+------+----------+
| 1  | employee   | ref    | name,h_date,dept | name    | const   | 8    | 100.00   |
| 1  | department | eq_ref | PRIMARY          | PRIMARY | dept_no | 1    | 100.00   |
+----+------------+--------+------------------+---------+---------+------+----------+

为此employee，name索引上的访问方法将 拾取与名称匹配的8行'John'。没有进行任何过滤(filtered为100％)，因此所有行都是下一张表的前缀行：前缀行计数为 rows× filtered= 8×100％= 8。

通过条件过滤，优化器还可以考虑WHERE 访问方法未考虑的子句中的条件。在这种情况下，优化器使用启发式方法估计BETWEEN 条件为的16.31％的过滤效果employee.hire_date。结果，EXPLAIN产生如下输出：

+----+------------+--------+------------------+---------+---------+------+----------+
| id | table      | type   | possible_keys    | key     | ref     | rows | filtered |
+----+------------+--------+------------------+---------+---------+------+----------+
| 1  | employee   | ref    | name,h_date,dept | name    | const   | 8    | 16.31    |
| 1  | department | eq_ref | PRIMARY          | PRIMARY | dept_no | 1    | 100.00   |
+----+------------+--------+------------------+---------+---------+------+----------+

现在，前缀行计数为rows× filtered= 8×16.31％= 1.3，它更紧密地反映了实际数据集。

通常，优化器不会为最后一个连接表计算条件过滤效果(减少前缀行数)，因为没有下一个表可以将行传递给该表。发生以下情况的例外 EXPLAIN：为了提供更多信息，将为所有连接的表(包括最后一个表)计算过滤效果。

要控制优化器是否考虑其他过滤条件，请使用系统变量的 condition_fanout_filter标志 optimizer_switch。默认情况下，此标志是启用的，但可以禁用该标志以抑制条件过滤(例如，如果发现某个特定查询没有它，则会产生更好的性能)。

如果优化器高估了条件过滤的效果，则性能可能会比不使用条件过滤的情况差。在这种情况下，这些技术可能会帮助：

• 如果未对列进行索引，请对其进行索引，以便优化程序获得有关列值分布的一些信息，并可以改善其行估计。

• 更改加入顺序。完成此操作的方法包括紧接在和 之后的连接顺序优化器提示。 STRAIGHT_JOIN``SELECT``STRAIGHT_JOIN

• 禁用会话的条件过滤：

  SET optimizer_switch = 'condition_fanout_filter=off';

今天的分享就到这里！ 往期好文

1.JVM 调优实战

2.Spring Aop实战案例二

3.程序员都在读的书，你也不能错过哦！

4.Java设计模式之策略者模式

5.Redis之缓存穿透与缓存击穿

6.Redis之缓存雪崩

7.Docker三剑客Compose命令介绍与使用

8.JDK14的重大更新

9.IDEA 2020重大更新，快来抢先get一下

10.异常处理之全局异常捕获

11.Java 8系列之重新认识HashMap

12.聊聊MyBatis二级缓存机制

13.Map merge 你不知道的一波骚操作

14.Mysql执行计划全解，SQL优化必备技能

---

小编寄语

小编创建了一个关于Java学习讨论的微信群！想进去的可以联系小编！同时也欢迎大家点赞与转发！

小编微信：372787553

备注为进群，通过后小编会邀请您进群！

Ja va有货因您而美