性能提升 10 倍？SQL 优化核心技巧，附带真实案例解析

本文链接：https://blog.csdn.net/qq_56158663/article/details/145646799

这篇文章详细讲解了 SQL 优化方式，包括每个优化的好处和具体的例子。

一、数据库设计层面优化

规范化 (Normalization)

好处:
- 减少数据冗余: 节省存储空间。
- 提高数据一致性: 避免数据更新时出现不一致的情况。
- 简化数据维护: 修改数据时只需要修改一个地方。

例子:

未规范化: 一个 Products 表包含产品信息和供应商信息。

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(255),
    ProductPrice DECIMAL(10, 2),
    SupplierID INT,
    SupplierName VARCHAR(255),
    SupplierAddress VARCHAR(255)
);

规范化: 将 Products 表分解成 Products 和 Suppliers 两个表。

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(255),
    ProductPrice DECIMAL(10, 2),
    SupplierID INT,
    FOREIGN KEY (SupplierID) REFERENCES Suppliers(SupplierID)
);

CREATE TABLE Suppliers (
    SupplierID INT PRIMARY KEY,
    SupplierName VARCHAR(255),
    SupplierAddress VARCHAR(255)
);

反规范化 (Denormalization)

好处:
- 提高查询性能: 减少 JOIN 操作，加快查询速度。
- 简化查询语句: 减少查询的复杂度。

例子:

规范化: Orders 表和 Customers 表分开存储。

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10, 2),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(255),
    CustomerAddress VARCHAR(255)
);

反规范化: 在 Orders 表中添加 CustomerName 字段。

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    CustomerName VARCHAR(255), -- 添加 CustomerName 字段
    OrderDate DATE,
    TotalAmount DECIMAL(10, 2),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

有很多人不理解什么是规范化和反规范化，接下来由我来讲解！

规范化 (Normalization)

概念： 规范化是一种数据库设计技术，旨在减少数据冗余，提高数据一致性。它通过将数据分解成多个表，并通过外键建立关联来实现。规范化遵循一系列范式 (Normal Forms)，例如 1NF、2NF、3NF、BCNF 等。
目的：
- 消除数据冗余： 减少存储空间。
- 提高数据一致性： 避免数据更新时出现不一致的情况。
- 简化数据维护： 修改数据时只需要修改一个地方。
优点：
- 减少存储空间。
- 提高数据一致性。
- 方便数据更新和维护。
缺点：
- 可能需要更多的 JOIN 操作，增加查询复杂度。
- 查询性能可能下降，特别是对于复杂的查询。

反规范化 (Denormalization)

概念： 反规范化是一种数据库设计技术，旨在提高查询性能，减少 JOIN 操作。它通过在表中添加冗余数据，或者将多个表合并成一个表来实现。
目的：
- 提高查询性能： 减少 JOIN 操作，加快查询速度。
- 简化查询语句： 减少查询的复杂度。
优点：
- 提高查询性能。
- 简化查询语句。
缺点：
- 增加数据冗余。
- 降低数据一致性。
- 增加数据更新和维护的复杂度。

规范化 vs 反规范化：区别总结

特性	规范化 (Normalization)	反规范化 (Denormalization)
目的	减少冗余，提高一致性	提高查询性能
数据冗余	减少	增加
数据一致性	提高	降低
查询性能	可能下降	提高
更新维护	简化	复杂
JOIN 操作	增加	减少

什么情况下使用规范化？

数据一致性要求高： 如果数据一致性是首要考虑因素，那么应该使用规范化。
频繁更新数据： 如果数据频繁更新，那么规范化可以简化更新操作，避免数据不一致。
存储空间有限： 如果存储空间有限，那么规范化可以减少数据冗余，节省存储空间。
OLTP (Online Transaction Processing) 系统： OLTP 系统通常需要频繁地进行事务处理，对数据一致性要求高，因此适合使用规范化。

什么情况下使用反规范化？

查询性能要求高： 如果查询性能是首要考虑因素，那么可以使用反规范化。
读多写少： 如果系统是读多写少的，那么反规范化可以提高查询性能，而数据一致性的问题可以通过其他方式来解决。
数据仓库 (Data Warehouse) 系统： 数据仓库系统通常需要进行大量的分析查询，对查询性能要求高，因此适合使用反规范化。
报表系统： 报表系统通常需要从多个表中提取数据，如果使用规范化的数据库，需要进行大量的 JOIN 操作，影响查询性能，因此可以使用反规范化。
数据量巨大： 当数据量非常大时，JOIN 操作的开销会非常高，此时可以考虑反规范化来减少 JOIN 操作。

总结：权衡与选择

规范化和反规范化是两种不同的数据库设计策略，需要在数据一致性和查询性能之间进行权衡。

没有绝对的“好”或“坏”： 最佳选择取决于具体的业务需求和数据特点。
可以混合使用： 在实际应用中，可以根据不同的表和不同的查询需求，混合使用规范化和反规范化。
持续评估和调整： 随着业务的发展和数据的变化，需要定期评估数据库设计，并进行必要的调整。

例子：电商平台的订单系统

规范化： 可以将订单信息、客户信息、商品信息分别存储在不同的表中，通过外键建立关联。
反规范化： 可以在订单表中添加客户姓名、商品名称等冗余信息，以减少查询订单信息时需要进行的 JOIN 操作。

最终的选择取决于：

订单信息的查询频率。
客户信息和商品信息的更新频率。
系统对数据一致性的要求。

选择合适的数据类型
- 好处:
  - 减少存储空间: 节省磁盘空间。
  - 提高查询效率: 更小的数据类型可以更快地进行比较和排序。
- 例子:
  - 不合适的类型: 使用 VARCHAR(255) 存储布尔值 (True/False)。
  - 合适的类型: 使用 BOOLEAN 或 TINYINT 存储布尔值。
索引 (Index)
- 好处:
  - 加速数据检索: 显著提高查询速度，特别是对于大型表。
- 例子:
  - 没有索引: 查询 Customers 表中 City 为 ‘London’ 的客户。
```
SELECT * FROM Customers WHERE City = 'London';
```
  - 创建索引: 在 City 列上创建索引。
```
CREATE INDEX idx_city ON Customers (City);
```
  - 查询速度提升: 创建索引后，查询速度会大大提高。

分区 (Partitioning)

好处:
- 提高查询效率: 只需扫描相关的分区，减少数据扫描量。
- 方便数据管理: 可以对不同的分区进行单独的管理和维护。

例子:

没有分区: Orders 表包含所有年份的订单数据。

范围分区: 按年份对 Orders 表进行分区。

CREATE TABLE Orders (
    OrderID INT,
    OrderDate DATE,
    CustomerID INT,
    TotalAmount DECIMAL(10, 2)
)
PARTITION BY RANGE (YEAR(OrderDate)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023)
);

二、SQL 语句层面优化

避免使用 SELECT *
- 好处:
  - 减少资源消耗: 只传输需要的列，减少网络带宽和内存消耗。
  - 提高查询效率: 避免读取不必要的列，加快查询速度。
  - 避免索引失效: 有些情况下，使用 SELECT * 会导致索引失效。
- 例子:
  - 优化前: SELECT * FROM Customers WHERE City = 'London';
  - 优化后: SELECT CustomerID, CustomerName FROM Customers WHERE City = 'London';
使用 WHERE 子句限制结果集
- 好处:
  - 减少数据扫描量: 尽早过滤掉不需要的数据，减少后续操作的数据量。
  - 提高查询效率: 加快查询速度。
- 例子:
  - 优化前: SELECT * FROM Orders WHERE CustomerID IN (SELECT CustomerID FROM Customers WHERE City = 'London');
  - 优化后: SELECT * FROM Orders WHERE CustomerID IN (SELECT CustomerID FROM Customers WHERE City = 'London') AND OrderDate > '2023-01-01';
避免在 WHERE 子句中使用函数或表达式
- 好处:
  - 避免索引失效: 索引只能用于简单的列比较，不能用于函数或表达式。
  - 提高查询效率: 使用索引可以加快查询速度。
- 例子:
  - 优化前: SELECT * FROM Orders WHERE YEAR(OrderDate) = 2023;
  - 优化后: SELECT * FROM Orders WHERE OrderDate >= '2023-01-01' AND OrderDate < '2024-01-01';
使用 JOIN 代替子查询
- 好处:
  - 提高查询效率: JOIN 通常比子查询更有效率，特别是对于复杂的查询。
- 例子:
  - 优化前: SELECT * FROM Orders WHERE CustomerID IN (SELECT CustomerID FROM Customers WHERE City = 'London');
  - 优化后: SELECT Orders.* FROM Orders JOIN Customers ON Orders.CustomerID = Customers.CustomerID WHERE Customers.City = 'London';
选择合适的 JOIN 类型
- 好处:
  - 提高查询效率: 根据实际需求选择最合适的 JOIN 类型，避免不必要的数据扫描。
- 例子:
  - 如果只需要返回两个表中匹配的行，使用 INNER JOIN。
  - 如果需要返回左表的所有行，以及右表中匹配的行，使用 LEFT JOIN。
优化 GROUP BY 和 ORDER BY 子句
- 好处:
  - 提高查询效率: 确保 GROUP BY 和 ORDER BY 的列上有索引，可以加快排序和分组的速度。
- 例子:
  - SELECT City, COUNT(*) FROM Customers GROUP BY City ORDER BY City; (确保 City 列上有索引)
使用 EXISTS 代替 COUNT(*)
- 好处:
  - 提高查询效率: EXISTS 只要找到匹配的行就返回，而 COUNT(*) 需要扫描整个表。
- 例子:
  - 优化前: SELECT c.* FROM Customers c WHERE ( SELECT COUNT(*) FROM Orders o WHERE o.CustomerID = c.CustomerID ) > 0;
  - 优化后: SELECT c.* FROM Customers c WHERE EXISTS ( SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID ); (效果相同，但 EXISTS 通常更快)
使用 UNION ALL 代替 UNION
- 好处:
  - 提高查询效率: UNION 会去除重复行，而 UNION ALL 不会。如果确定结果集中没有重复行，可以使用 UNION ALL 提高效率。
- 例子:
  - SELECT City FROM Customers WHERE Country = 'USA' UNION ALL SELECT City FROM Suppliers WHERE Country = 'USA';
利用查询缓存 (Query Cache)
- 好处:
  - 提高查询效率: 直接返回缓存结果，避免重复执行查询。
- 例子:
  - 如果数据库服务器启用了查询缓存，并且查询语句和数据没有发生变化，那么下次执行相同的查询时，可以直接从缓存中获取结果。
使用 LIMIT 分页
- 好处:
  - 减少资源消耗: 限制返回的行数，减少网络传输量和内存消耗。
  - 提高查询效率: 避免读取不必要的数据，加快查询速度。
- 例子:
  - SELECT * FROM Products LIMIT 10 OFFSET 20; (获取第 21-30 条记录)

三、数据库服务器层面优化

硬件升级
- 好处:
  - 提高整体性能: 更快的 CPU、更大的内存、更快的磁盘和更快的网络都可以提高数据库服务器的整体性能。
- 例子:
  - 升级 CPU 可以提高查询处理速度。
  - 增加内存可以减少磁盘 I/O。
  - 使用 SSD 磁盘可以提高数据读取速度。
数据库配置优化
- 好处:
  - 提高数据库服务器的性能: 合理的配置可以充分利用硬件资源，提高数据库服务器的性能。
- 例子:
  - 调整缓冲区大小：增加缓冲区大小可以减少磁盘 I/O。
  - 调整连接数：增加连接数可以提高并发处理能力。
  - 调整日志大小：调整日志大小可以提高写入性能。
定期维护
- 好处:
  - 保持数据库服务器的良好状态: 定期维护可以清理垃圾数据，整理索引碎片，提高查询效率。
- 例子:
  - 分析表：更新表的统计信息，帮助优化器选择更优的执行计划。
  - 重建索引：整理索引碎片，提高查询效率。
  - 清理日志：删除旧的日志文件，释放磁盘空间。
使用数据库连接池
- 好处:
  - 减少连接创建和销毁的开销: 提高性能。
- 例子:
  - 在应用程序中使用数据库连接池，避免每次查询都创建新的连接。
监控和诊断
- 好处:
  - 及时发现和解决性能问题: 监控数据库服务器的性能指标，可以及时发现性能瓶颈并进行优化。
- 例子:
  - 监控 CPU 使用率、内存使用率、磁盘 I/O、网络流量等。
  - 使用数据库自带的诊断工具分析慢查询日志，找出性能瓶颈。