在 MySQL 中，数据清洗、复杂查询和动态计算是数据库操作中非常重要的部分。它们分别用于处理不规范的数据、实现复杂的业务逻辑以及动态生成结果。

---

一、知识体系包含的主要部分

1. 数据清洗

• 定义：
  • 数据清洗是指对原始数据进行处理，使其符合规范或业务需求。
• 常见场景：
  • 去除重复数据。
  • 处理缺失值（如 NULL 或空字符串）。
  • 格式化数据（如日期格式、大小写转换）。
  • 删除无效数据（如不符合规则的记录）。
• 常用工具与函数：
  • 去重：DISTINCT, GROUP BY。
  • 处理缺失值：COALESCE, IFNULL, CASE WHEN。
  • 格式化数据：
    • 字符串函数：TRIM, LOWER, UPPER, REPLACE。
    • 日期时间函数：DATE_FORMAT, STR_TO_DATE。
  • 删除无效数据：DELETE 或 WHERE 条件过滤。
• 示例：

  -- 将 NULL 替换为默认值
  SELECT COALESCE(column_name, 'Default') FROM table_name;
  
  -- 删除重复记录
  DELETE FROM table_name
  WHERE id NOT IN (
      SELECT MIN(id) FROM table_name GROUP BY column_name
  );
  

2. 复杂查询

• 定义：
  • 复杂查询是指通过多表连接、子查询、聚合函数等手段实现复杂的业务逻辑。
• 常见场景：
  • 多表连接（如内连接、外连接）。
  • 子查询（如标量子查询、行子查询）。
  • 聚合与分组（如 GROUP BY, HAVING）。
  • 排序与分页（如 ORDER BY, LIMIT）。
• 常用工具与函数：
  • 连接查询：JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN。
  • 子查询：IN, EXISTS, NOT EXISTS。
  • 聚合函数：COUNT, SUM, AVG, MIN, MAX。
  • 窗口函数：ROW_NUMBER, RANK, NTILE。
• 示例：

  -- 多表连接查询
  SELECT a.id, b.name
  FROM table_a a
  LEFT JOIN table_b b ON a.fk_id = b.id;
  
  -- 使用子查询过滤数据
  SELECT * FROM employees
  WHERE department_id IN (SELECT id FROM departments WHERE location = 'New York');
  

3. 动态计算

• 定义：
  • 动态计算是指在查询过程中根据输入数据实时生成结果。
• 常见场景：
  • 数值计算（如折扣、比例）。
  • 日期时间计算（如时间差、未来日期）。
  • 条件判断（如分类、评分）。
• 常用工具与函数：
  • 数值函数：ABS, ROUND, CEIL, FLOOR。
  • 日期时间函数：NOW, DATE_ADD, DATEDIFF。
  • 条件函数：CASE WHEN, IF, COALESCE。
• 示例：

  -- 计算折扣后的价格
  SELECT price, discount_rate, price * (1 - discount_rate) AS discounted_price
  FROM products;
  
  -- 计算订单的预计交付日期
  SELECT order_date, DATE_ADD(order_date, INTERVAL 7 DAY) AS delivery_date
  FROM orders;
  

---

二、底层原理

1. 数据清洗的底层原理

• 存储引擎的处理：
  • 数据清洗通常涉及对字段值的读取和修改。
  • 存储引擎从表中读取数据，并将其传递给 SQL 函数或表达式。
• 位图标记的影响：
  • 如果字段允许 NULL，存储引擎通过位图标记字段是否为 NULL。
  • 数据清洗函数（如 COALESCE）会在运行时检查该标记，决定是否替换为默认值。
• 执行计划优化：
  • 查询优化器会尽量利用索引加速数据清洗操作。
  • 示例：

    CREATE INDEX idx_column ON table_name(column_name);
    SELECT TRIM(column_name) FROM table_name;
    

2. 复杂查询的底层原理

• 连接算法：
  • 常见的连接算法包括嵌套循环连接（Nested Loop Join）、哈希连接（Hash Join）和排序合并连接（Sort Merge Join）。
  • 优化器会选择最高效的连接算法，基于统计信息和索引分布。
• 子查询的执行：
  • 子查询可能以两种方式执行：
    • 独立执行：子查询先执行，结果缓存后用于主查询。
    • 相关执行：子查询依赖主查询的每一行重新执行。
• 聚合与分组：
  • 聚合函数通过临时表或内存结构存储中间结果。
  • 分组操作通常需要对数据进行排序或哈希分区。

3. 动态计算的底层原理

• 表达式求值：
  • 动态计算的核心是表达式求值，SQL 引擎在运行时逐行计算表达式的值。
  • 示例：

    SELECT ROUND(price * 0.9, 2) AS discounted_price FROM products;
    

• 类型推断：
  • SQL 引擎会根据表达式的参数推断返回值的数据类型。
  • 示例：

    SELECT CONCAT('Order ID: ', id) FROM orders; -- 返回字符串类型
    

• 短路求值：
  • 某些函数（如 CASE WHEN 或 COALESCE）支持短路求值，避免不必要的计算。

---

三、常见问题与解决方案

1. 如何提高数据清洗的效率？

• 使用虚拟列：
  • 对于频繁使用的清洗逻辑，可以通过创建虚拟列并建立索引来优化性能。

    ALTER TABLE table_name ADD COLUMN new_column VARCHAR(255) AS (TRIM(old_column)) STORED;
    CREATE INDEX idx_new_column ON table_name(new_column);
    

• 批量处理：
  • 避免逐行更新数据，使用批量操作提高效率。

    UPDATE table_name SET column_name = TRIM(column_name) WHERE column_name LIKE ' %';
    

2. 如何优化复杂查询的性能？

• 索引优化：
  • 确保连接字段和过滤条件上有索引。
  • 示例：

    CREATE INDEX idx_fk ON table_a(fk_id);
    

• 分解查询：
  • 将复杂查询拆分为多个简单查询，减少单次查询的复杂度。
• 覆盖索引：
  • 确保索引覆盖查询所需的字段，减少回表操作。

3. 如何设计高效的动态计算逻辑？

• 预计算：
  • 对于频繁使用的计算结果，可以预先存储在表中，避免实时计算。

    ALTER TABLE products ADD COLUMN discounted_price DECIMAL(10, 2) AS (price * 0.9) STORED;
    

• 简化表达式：
  • 避免嵌套过多的函数调用，简化计算逻辑。
• 使用用户定义函数（UDF）：
  • 对于复杂的业务逻辑，可以创建自定义函数提高复用性。

---

四、总结

MySQL 数据清洗、复杂查询和动态计算的知识体系涵盖了以下几个方面：

1. 数据清洗：去除重复数据、处理缺失值、格式化数据、删除无效数据。
2. 复杂查询：多表连接、子查询、聚合与分组、排序与分页。
3. 动态计算：数值计算、日期时间计算、条件判断。

底层原理的核心在于：

• 数据清洗：通过存储引擎和 SQL 函数处理字段值，优化执行计划。
• 复杂查询：通过连接算法、子查询执行和聚合分组实现复杂逻辑。
• 动态计算：通过表达式求值、类型推断和短路求值动态生成结果。