尼恩说在前面

问题1：MySQL中“where 1=1” 条件 影响性能么，为什么？

问题2：听说 MySQL中“where 1=1” 条件，部分场景会严重 影响性能，是哪些场景呢， 该怎么解决？

《尼恩 架构笔记》《尼恩高并发三部曲》《尼恩Java面试宝典》的PDF，请到文末公号【技术自由圈】获取

本文目录

- 尼恩说在前面

- MySQL中“where 1=1”的性能影响深究

- “where 1=1”在动态 SQL 中的应用

- “where 1=1”会导致严重的性能问题

- 性能影响测试

- 场景1：后面不带真实查询条件的where 1=1的执行成本

- read_cost、 eval_cost、prefix_cost 三个成本的关系

- 场景2：后面 带有真实查询条件 的where 1=1的执行成本

- 两条执行计划的对比

- 1=1 本质上被mysql 优化掉了

 - 回顾一下：查询优化器、执行器的工作原理

 - MySQL 优化器对于1=1条件的优化工作

    -MySQL 优化器的语义解析阶段

    -MySQL 优化器的成本估算和计划选择

 - where 1=1的被优化后的结果

 - where 1=1 与索引使用的关系

- 如何避免 where 1=1 导致的性能严重退化?

 - 方法一：通过其他的查询条件命中索引

 - 方法二：如果没有通过其他的查询条件命中索引

 - 方法三：使用`<where>`标签，去掉不必要的1=1

- 尼恩架构团队的塔尖 sql 面试题

- 说在最后：有问题找老架构取经‍

MySQL中“where 1=1”的性能影响深究

“where 1=1”在动态 SQL 中的应用

String sql = "SELECT * FROM table_name WHERE 1 = 1";
if (condition1) {
    sql += " AND column1 = value1";
}
if (condition2) {
    sql += " AND column2 > value2";
}

“where 1=1”会导致严重的性能问题

性能影响测试

SELECT VERSION();

CREATE TABLE `test_user` (
`id` int NOT NULL AUTO_INCREMENT, 
`id_card` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL, 
`age` int DEFAULT '0', 
`user_name` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
`height` int DEFAULT '0',  
`address` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,  
PRIMARY KEY (`id`), 
KEY `idx_id_card_age_user_name` (`id_card`,`age`,`user_name`), 
KEY `idx_height` (`height`))
ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

explain format=json SELECT * FROM test_user WHERE 1 = 1   LIMIT 10,10

explain format=json   SELECT * FROM test_user WHERE 1 = 1  and  height=120  LIMIT 10,10

场景1：后面不带真实查询条件的where 1=1的执行成本

explain format=json SELECT * FROM test_user WHERE 1 = 1   LIMIT 10,10

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "100134.80"
    },
    "table": {
      "table_name": "test_user",
      "access_type": "ALL",
      "rows_examined_per_scan": 489294,
      "rows_produced_per_join": 489294,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "2276.00",
        "eval_cost": "97858.80",
        "prefix_cost": "100134.80",
        "data_read_per_join": "160M"
      },
      "used_columns": [
        "id",
        "id_card",
        "age",
        "user_name",
        "height",
        "address"
      ]
    }
  }
}

1. "query_block": 代表整个查询的执行计划块，包含了关于这个查询的各种信息。

1. "select_id": 1：查询的唯一标识符为 1。
2. "cost_info"：

1. "table_name": "test_user": 查询涉及的表名为 test_user。
2. "access_type": "ALL":

3. "rows_examined_per_scan": 489294:

4. "rows_produced_per_join": 489294: 每次连接产生的行数为 489294。

5. "filtered": "100.00": 过滤后的行数比例为 100%。

6. "cost_info" :

• "read_cost": "2276.00": 读取数据的成本为 2276.00。
• "eval_cost": "97858.80": 评估条件的成本为 97858.80。在这个例子中，由于 WHERE 1 = 1 没有实际的评估工作，这个成本可能主要来自其他方面，如读取数据后的处理。
• "prefix_cost": "100134.80": 前缀成本为 100134.80，通常是指查询的总成本减去某些特定操作的成本。
• "data_read_per_join": "160M": 每次连接读取的数据量为 160M（可能是 160 兆字节）。

read_cost、 eval_cost、prefix_cost 三个成本的关系

1. read_cost（读取数据成本）
   定义：read_cost 主要是指从存储介质（如磁盘或内存）中读取数据所产生的成本。在数据库操作中，数据存储在磁盘上的文件或者内存中的缓存区域。当执行查询时，需要将相关的数据块读取到内存中进行处理，这个过程就会产生 read_cost。
   影响因素：

• 数据量：要读取的数据量越大，read_cost 就越高。例如，对于一个全表扫描的查询，需要读取整个表的数据，read_cost 会比只读取少量满足条件的数据行高很多。
• 存储介质性能：磁盘的 I/O 速度会直接影响 read_cost。如果是机械硬盘，其 I/O 速度相对较慢，read_cost 会比较高；而固态硬盘（SSD）的 I/O 速度快，read_cost 会相应降低。另外，内存读取速度比磁盘快很多，所以如果数据已经在内存缓存中，read_cost 会比从磁盘读取低。
• 数据分布和存储格式：数据在磁盘上的分布情况以及存储格式也会影响 read_cost。例如，数据是否按照某种顺序存储（如按照索引顺序），如果是，可能会减少磁盘寻道时间，从而降低 read_cost。
• 示例：在一个查询中，假设需要读取一个包含 100 万行数据的表，每行数据大小为 1KB，从磁盘读取数据块的单位成本为 1（这是一个抽象的成本单位），如果是全表扫描，read_cost 可能就会很高，约为 100 万（行数）* 1KB（每行大小）* 1（单位成本）。

• 条件复杂度：如果查询条件很复杂，包含多个逻辑运算符（如 AND、OR）、函数（如 SUM、AVG）或者嵌套的子查询，eval_cost 会增加。例如，一个 WHERE 子句中有多个复杂的函数调用和嵌套子查询，数据库需要执行更多的计算和比较操作来评估条件，从而增加 eval_cost。
• 数据类型和索引使用：数据类型也会影响 eval_cost。例如，比较整数类型的条件可能比比较文本类型的条件更快。如果能够利用索引来帮助评估条件，eval_cost 可能会降低。例如，对于一个有索引的列，数据库可以通过索引快速定位满足条件的行，减少了对每一行数据的详细评估，从而降低 eval_cost。
• 示例：对于一个查询条件为 WHERE age > 30 AND salary < 5000 的查询，数据库需要对每一行数据的 age 和 salary 列进行比较操作。如果表中有 10 万行数据，每次比较操作的成本假设为 0.01（抽象成本单位），那么 eval_cost 大约为 10 万（行数）* 2（两个条件）* 0.01（单位成本）。

prefix_cost（前缀成本）

定义：prefix_cost 是查询的一种累积成本估算，它通常是查询总成本减去某些特定操作的成本。具体来说，它考虑了在执行查询过程中的多个步骤的成本累积，包括读取数据、评估条件以及其他可能的操作（如排序、分组等），但可能会排除一些后续的特定操作成本，以便于在执行计划比较等场景中进行更灵活的成本分析。

与其他成本的关系：prefix_cost 是 read_cost 和 eval_cost 等成本的综合体现，并且还可能包含其他相关操作的成本。它的计算方式和具体包含的内容可能因数据库系统的优化器实现而有所不同。一般来说，它可以帮助用户和优化器快速判断一个查询执行计划在主要操作阶段的成本情况。

示例：假设一个查询的总成本为 100，其中排序操作成本为 10，那么 prefix_cost 可能就是 90，它反映了在不考虑排序操作的情况下，读取数据和评估条件等主要操作的成本。在执行计划选择过程中，数据库优化器可能会比较不同执行计划的 prefix_cost，选择成本较低的计划作为主要的候选方案，然后再考虑其他操作（如排序）的成本来最终确定执行计划。

场景2：后面 带有真实查询条件 的where 1=1的执行成本

explain format=json   SELECT * FROM test_user WHERE 1 = 1  and  height=120  LIMIT 10,10

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "7252.80"
    },
    "table": {
      "table_name": "test_user",
      "access_type": "ref",
      "possible_keys": [
        "idx_height"
      ],
      "key": "idx_height",
      "used_key_parts": [
        "height"
      ],
      "key_length": "5",
      "ref": [
        "const"
      ],
      "rows_examined_per_scan": 6044,
      "rows_produced_per_join": 6044,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "6044.00",
        "eval_cost": "1208.80",
        "prefix_cost": "7252.80",
        "data_read_per_join": "1M"
      },
      "used_columns": [
        "id",
        "id_card",
        "age",
        "user_name",
        "height",
        "address"
      ]
    }
  }
}

1. "query_block": 代表整个查询的执行计划块，包含了关于这个查询的各种关键信息。

1. "select_id": 1：查询的唯一标识符为 1。
2. "cost_info" :

• "query_cost": "7252.80": 查询的预估总成本为 7252.80。这个成本是数据库优化器根据多种因素估算出来的，包括读取数据的成本、评估条件的成本等。相对较低的成本通常意味着执行计划较为高效。

1. "table_name": "test_user": 查询涉及的表名为 test_user。
2. "access_type": "ref": 访问表的方式为 ref，表示通过非唯一索引进行查找。

3. "possible_keys": ["idx_height"]:

4. "key": "idx_height":

5. "used_key_parts": ["height"]: 使用的索引部分是 height 列，表明索引 idx_height 是基于 height 列创建的，并且在查询中只使用了这个索引的 height 部分。
6. "key_length": "5": 索引的长度为 5，可能与存储 height 列的值所需的空间有关。
7. "ref": ["const"]: 索引引用的值是常量，这里可能是查询条件中的某个具体值。
8. "rows_examined_per_scan": 6044: 每次扫描检查的行数为 6044。这表示在执行查询过程中，需要检查的行数估计为 6044 行。相比全表扫描，这个行数较少，说明索引的使用有效地减少了需要检查的数据量。
9. "rows_produced_per_join": 6044: 每次连接产生的行数为 6044。如果查询涉及多个表的连接，这个值表示从这个表中产生的行数，用于连接操作。在这个例子中，可能没有连接操作，所以这个值与 rows_examined_per_scan 相同。
10. "filtered": "100.00": 过滤后的行数比例为 100%。这意味着经过筛选条件（可能与 height 列相关）过滤后，所有满足条件的行数占总检查行数的比例为 100%。
11. "cost_info":

• "read_cost": "6044.00": 读取数据的成本为 6044.00。
• "eval_cost": "1208.80": 评估条件的成本为 1208.80。
• "prefix_cost": "7252.80": 前缀成本为 7252.80，通常是指查询的总成本减去某些特定操作的成本。
• "data_read_per_join": "1M": 每次连接读取的数据量为 1M（可能是 1 兆字节）。

12. "used_columns": ["id", "id_card", "age", "user_name", "height", "address"]: 查询使用的列包括 id、id_card、age、user_name、height 和 address。这意味着查询将从表中读取这些列的数据。

两条执行计划的对比

对比两条 SQL执行的结果，可以发现它们消耗的时间几乎相同，因此，看起来where 1=1 后面有没有带真实条件，对整体的性能 影响非常大 。

1=1 本质上被mysql 优化掉了

回顾一下：查询优化器、执行器的工作原理

• 上图中 优化器 + 执行器 是哼哈二将：
• 根据sql的结构生成不同的执行计划，然后选择一个最优的 plan 计划 ， 是MySQL优化器 的主要任务。
• 执行这个最优的执行计划 plan ，是 MySQL执行器 的主要任务。

• 语法和语义解析
  当接收到一个 SQL 查询语句时，优化器首先会对语句进行语法和语义解析。
  它会识别出查询中的关键字（如 SELECT、FROM、WHERE、JOIN 等）、表名、列名、函数调用、操作符以及各种条件表达式等元素。这是理解查询意图的基础步骤。
  例如，对于查询语句 “SELECT * FROM users WHERE age> 18 AND gender = 'male'”，优化器会解析出这是一个从 “users” 表中选择所有列，并且筛选出年龄大于 18 岁且性别为男性的记录的查询。
• 查询重写
  优化器会尝试对查询进行重写，以使其更高效或符合优化规则。
  这可能包括简化复杂的表达式、消除冗余的子句等操作。
  例如，对于一些包含嵌套子查询的复杂查询，优化器可能会将其转换为等价的连接操作，以提高查询性能。
  另外，如果查询中存在重复的条件或者可以通过逻辑推导简化的条件，优化器也会进行相应的处理。
• 生成执行计划
  这是优化器的核心职责之一。
  它会根据解析后的查询和数据库的元数据（如表结构、索引信息、统计信息等）生成多个可能的执行计划。
  执行计划描述了数据库如何执行查询，包括访问哪些表、以何种顺序访问、是否使用索引、如何进行连接操作等细节。
  例如，对于一个涉及多表连接的查询，优化器可能会生成多种连接顺序不同的执行计划，如先连接表 A 和表 B，再连接表 C，或者先连接表 B 和表 C，再连接表 A 等不同的方案。
• 成本估算和计划选择
  对于每个生成的执行计划，优化器会估算其执行成本。
  成本估算考虑多种因素，主要包括 I/O 成本（从磁盘读取数据的成本）和 CPU 成本（对数据进行处理的成本，如比较、排序、函数计算等）。
  优化器会根据表和索引的统计信息（如表的行数、索引的键值分布、列的基数等）来计算这些成本。
  然后，它会选择成本最低的执行计划作为最终的执行计划。
  例如，如果一个执行计划需要进行大量的全表扫描，I/O 成本较高，而另一个执行计划可以通过使用索引有效减少数据读取量，优化器会倾向于选择后者。

• 执行计划的执行
  一旦优化器确定了最终的执行计划，执行器就负责按照这个计划执行查询。
  它会根据执行计划中规定的步骤，逐个操作地处理查询。
  例如，如果执行计划要求先从某个表中读取数据，执行器就会向存储引擎发出相应的读取请求，获取数据行。
  如果执行计划包括连接操作，执行器会按照指定的连接算法（如嵌套循环连接、哈希连接等）对数据进行连接处理。
• 数据读取和操作
  执行器负责从存储引擎读取数据，并根据查询的要求进行相应的操作。
  这包括读取表中的行数据、获取索引中的键值信息等。在读取数据的过程中，执行器会遵循存储引擎的接口和规则。
  例如，对于 InnoDB 存储引擎，执行器会通过 InnoDB 提供的接口来读取表空间文件（.ibd 文件）中的数据。同时，执行器会对读取的数据进行操作，如根据 WHERE 子句中的条件进行筛选，对选定的列进行投影操作（即选择查询中指定的列）等。
• 事务管理（如果涉及）
  如果查询是在事务环境中进行的，执行器需要参与事务的管理。
  它会负责开启事务、提交事务或者回滚事务等操作，以确保数据的一致性和完整性。
  例如，在一个包含多个更新操作的事务查询中，执行器会按照事务的要求，先将更新操作记录到事务日志（如 InnoDB 的 redo 日志）中，在事务提交时确保所有的更新操作都持久化到磁盘，或者在事务回滚时撤销已经执行的部分更新操作。
• 结果集生成和返回
  执行器会根据查询的要求生成最终的结果集。
  这可能包括对数据进行排序、分组、聚合等操作。
  例如，对于一个带有 ORDER BY 子句的查询，执行器会对读取的数据进行排序，然后将排序后的结果返回给客户端。
  在生成结果集的过程中，执行器会遵循 SQL 标准和数据库的特定规则，确保结果的准确性和完整性。
  最后，执行器将结果集返回给客户端应用程序，完成查询的执行过程。

MySQL 优化器对于1=1条件的优化工作

MySQL 优化器的语义解析阶段

MySQL 优化器的成本估算和计划选择

where 1=1的被优化后的结果

 SELECT * FROM test_user WHERE 1 = 1  order by  height   LIMIT 10,10

where 1=1 与索引使用的关系

如何避免 where 1=1 导致的性能严重退化?

方法一：通过其他的查询条件命中索引

SELECT * FROM test_user WHERE 1 = 1  and  height=120

方法二：如果没有通过其他的查询条件命中索引

explain   SELECT * FROM test_user WHERE 1 = 1  ORDER BY id  LIMIT 10,10;

方法三：使用<where>标签，去掉不必要的1=1

<select id="" parameterType = "">
    SELECT * FROM user 
    <where>
        <if test="name != null and name != ''">
           AND name = #{name}
        </if>
        <if test="age != null">
           AND age = #{age}
        </if>
    </where>
</select>

尼恩提示：方式三 只是去掉 不必要的 1=1 条件而已， 并没有解决根本问题。

根本问题是要通过 sql 的其他子句，比如 select 子句、order by 子句 命中索引， 从而避免全表扫描。

如果遇到 mysql 这块的很难的面试题，可以找尼恩 来交流。

尼恩架构团队的塔尖 sql 面试题

• sql查询语句的执行流程：

• 索引

• 索引下推 ?

• 索引失效

• MVCC

• binlog、redolog、undo log

• mysql 事务

• 分布式事务

• mysql 调优

说在最后：有问题找老架构取经‍

• MySQL中“where 1=1” 条件 影响性能么，为什么？
• 听说 MySQL中“where 1=1” 条件，部分场景会严重 影响性能，是哪些场景呢， 该怎么解决？

空窗8月：中厂大龄34岁，被裁8月收一大厂offer， 年薪65W，转架构后逆天改命!

空窗2年：42岁被裁2年，天快塌了，急救1个月，拿到开发经理offer，起死回生

空窗半年：35岁被裁6个月， 职业绝望，转架构急救上岸，DDD和3高项目太重要了

空窗1.5年：失业15个月，学习40天拿offer， 绝境翻盘，如何实现？