MySQL 高效查询的实践指南：SQL语句优化篇

千羽

发布于 2024-07-31 14:43:11

1400

发布于 2024-07-31 14:43:11

文章被收录于专栏：程序员千羽

在 MySQL 数据库中，编写高效的 SQL 语句不仅能提升查询速度，还能优化资源使用，避免潜在的性能问题。本文将详细介绍 MySQL 查询中的一些强制性和推荐性规范，并提供具体的示例来帮助你更好地理解和应用这些最佳实践。

**避免使用 `count(列名)` 替代 `count(*)`**

【强制】 不要使用 count(列名) 或 count(常量) 来替代 count(*)。count(*) 是 SQL92 定义的标准语法，用于统计行数，跟数据库系统无关，且能够统计包括 NULL 值在内的所有行。

说明：count(*) 计算所有行，包括 NULL 值，而 count(列名) 不统计 NULL 值的行。

正例：

-- 正确：统计所有行
SELECT COUNT(*) FROM orders;

-- 错误：只统计 `customer_id` 非 NULL 的行
SELECT COUNT(customer_id) FROM orders;

示例：

在一个订单表 orders 中，如果你需要统计所有的订单数，不论 customer_id 是否为 NULL，应该使用 count(*)，以确保统计结果准确无误。

理解 `count(distinct col)` 的行为

【强制】 count(distinct col) 计算该列除 NULL 之外的不重复行数。需要注意的是，如果 count(distinct col1, col2) 中的任一列全为 NULL，则即使另一列有不同的值，也会返回 0。

说明：count(distinct col1, col2) 在处理 NULL 值时，要求所有参与的列都不为 NULL 才能返回有效的结果。

正例：

-- 统计 `customer_id` 的不重复值（排除 NULL）
SELECT COUNT(DISTINCT customer_id) FROM orders;

示例：

如果你需要计算唯一客户的数量，并且 customer_id 列可能包含 NULL 值，count(distinct customer_id) 会忽略这些 NULL 值，确保计算结果仅包括实际的客户数。

处理 `sum()` 函数的 NPE 问题

【强制】 当某一列的值全是 NULL 时，count(col) 返回 0，而 sum(col) 返回 NULL。因此，使用 sum() 时需注意 NPE（空指针异常）问题。

正例：

-- 避免 NPE 问题
SELECT IF(ISNULL(SUM(amount)), 0, SUM(amount)) FROM orders;

示例：

在计算订单总金额时，如果所有订单的金额列都是 NULL，SUM(amount) 会返回 NULL。通过使用 IF(ISNULL(SUM(amount)), 0, SUM(amount))，可以确保返回 0 而不是 NULL。

使用 `ISNULL()` 判断 NULL 值

【强制】 使用 ISNULL() 来判断值是否为 NULL。

说明：NULL 与任何值的比较结果都是 NULL。例如，NULL <> NULL 和 NULL = NULL 的结果都是 NULL，而不是 false 或 true。

正例：

-- 检查是否为 NULL
SELECT * FROM orders WHERE ISNULL(customer_id);

示例：

如果你需要筛选出 customer_id 为 NULL 的订单，使用 ISNULL() 进行判断，而不是直接使用 NULL 比较操作符。

分页查询中的优化

【强制】 编写分页查询逻辑时，如果 count 为 0，应直接返回，避免执行后续的分页语句。

说明：当查询结果为空时，进行分页操作是没有意义的，可以通过早期退出来提高性能。

正例：

-- 查询总数
SELECT COUNT(*) INTO @total_count FROM orders WHERE status = 'shipped';

-- 如果总数为0，直接返回
IF @total_count = 0 THEN
    SELECT 'No records found';
ELSE
    -- 执行分页查询
    SELECT * FROM orders WHERE status = 'shipped' LIMIT 0, 10;
END IF;

示例：

在实现分页查询时，首先查询总记录数。如果记录数为 0，则避免执行分页查询，从而节省资源和时间。

外键和级联的使用

【强制】 不得使用外键和级联，所有外键概念必须在应用层解决。

说明：外键和级联操作适用于低并发环境，但在分布式、高并发场景下，不推荐使用，因为它们可能引发数据库更新风暴，并影响插入速度。

正例：

在应用层处理数据一致性，而不是依赖数据库的级联更新。例如，在更新学生表中的 student_id 时，手动更新成绩表中的相关记录，而不是依赖数据库自动更新。

示例：

-- 更新学生记录时，同时更新成绩记录
UPDATE students SET student_id = 'new_id' WHERE student_id = 'old_id';
UPDATE grades SET student_id = 'new_id' WHERE student_id = 'old_id';

存储过程的使用

【强制】 禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。

说明：存储过程的调试和维护比较困难，且在不同数据库系统之间移植性差。

正例：

尽量使用标准 SQL 语句和应用层逻辑来实现业务需求，而不是依赖存储过程。

示例：

-- 使用标准 SQL 实现业务逻辑，而非存储过程
SELECT * FROM orders WHERE status = 'shipped';

数据订正的最佳实践

【强制】 在执行数据订正（如删除或修改记录）之前，要先使用 SELECT 语句确认数据，以避免误操作。

说明：在执行 UPDATE 或 DELETE 操作前，通过 SELECT 语句检查数据，确保操作的准确性。

正例：

-- 先检查数据
SELECT * FROM orders WHERE order_id = '1234';

-- 确认无误后执行删除
DELETE FROM orders WHERE order_id = '1234';

示例：

在删除订单记录之前，先查询该订单的详细信息，确保删除操作不会影响其他数据。

合理使用 `in` 操作符

【推荐】 避免使用 IN 操作符，如果无法避免，确保 IN 后的集合元素数量控制在 1000 个之内。

说明：IN 操作符用于检查某个值是否在一个集合中。集合过大可能导致性能问题。

正例：

-- 控制集合大小
SELECT * FROM products WHERE product_id IN (1, 2, 3, ..., 1000);

示例：

如果你需要查询多个产品的详细信息，将 IN 子句中的元素数量控制在合理范围内，避免性能下降。

全球化字符存储与表示

【参考】 如果涉及全球化需求，所有字符存储和表示均以 UTF-8 编码为宜。对于表情等特殊字符，使用 utf8mb4 编码。

说明：LENGTH() 和 CHARACTER_LENGTH() 函数在处理字符时会有不同的结果。utf8mb4 能够存储更多字符。

正例：

-- 计算字符长度
SELECT LENGTH('轻松工作');          -- 返回 12
SELECT CHARACTER_LENGTH('轻松工作'); -- 返回 4

-- 存储表情
CREATE TABLE messages (
    id INT PRIMARY KEY,


    text VARCHAR(255) CHARACTER SET utf8mb4
);

示例：

在全球化应用中，选择 utf8mb4 编码以支持更全面的字符集，包括表情符号等特殊字符。

使用 `TRUNCATE TABLE` 的注意事项

【参考】 TRUNCATE TABLE 比 DELETE 更快，但没有事务支持，也不触发触发器，因此不建议在开发代码中使用此语句。

说明：TRUNCATE TABLE 会快速删除所有记录，但可能会引发意外问题，建议在需要删除大量数据时谨慎使用。

正例：

-- 不推荐使用 TRUNCATE TABLE
TRUNCATE TABLE orders;

-- 推荐使用 DELETE 语句，并添加事务支持
START TRANSACTION;
DELETE FROM orders;
COMMIT;

示例：

在开发过程中，使用 DELETE 语句并结合事务，能够更安全地进行数据删除操作，并确保数据一致性。

MySQL 查询优化及一些规范

MySQL 语句的规范

1. 使用合适的数据类型

【强制】 为每一列选择最合适的数据类型，避免使用过大的数据类型，这样可以减少存储空间并提高查询性能。

正例：

-- 使用合适的数据类型
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),          -- 合适长度的 VARCHAR 类型
    created_at DATETIME       -- 使用 DATETIME 类型记录时间
);

反例：

-- 不推荐：使用过大的数据类型
CREATE TABLE logs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,   -- 不必要的大类型
    message TEXT,                          -- 使用 TEXT 存储小字符串
    created_at TIMESTAMP                   -- TIMESTAMP 适合存储时间戳
);

说明：使用合适的数据类型可以节省存储空间并提高查询效率。选择合适的字段长度和类型对于性能至关重要。

2. 避免在 `WHERE` 子句中使用函数

【强制】 尽量避免在 WHERE 子句中对列使用函数，这样会导致索引失效，影响查询性能。

正例：

-- 避免函数使用，直接比较列值
SELECT * FROM orders WHERE order_date = '2024-07-29';

-- 使用函数对列进行操作时需要慎重
-- SELECT * FROM orders WHERE DAY(order_date) = 29;

说明：函数调用会阻止 MySQL 使用索引，从而降低查询性能。尽量将查询条件设置为列的原始值。

3. 合理使用事务

【强制】 在数据操作时使用事务来确保数据一致性和完整性。确保在事务中包含 COMMIT 或 ROLLBACK。

正例：

-- 使用事务处理数据
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

说明：事务确保了操作的原子性，即所有操作要么全部成功，要么全部失败，避免数据不一致的问题。

**4. 避免使用 `SELECT *`**

【强制】 避免在 SELECT 语句中使用 *，应该明确列出所需的列。这有助于减少数据传输量和提高查询性能。

正例：

-- 明确选择所需的列
SELECT id, name, email FROM users;

说明：选择具体列可以减少数据量和内存使用，优化查询性能。

5. 定期进行表和索引的优化

【推荐】 定期使用 OPTIMIZE TABLE 进行表和索引的优化，以保持数据库性能的稳定性。

正例：

-- 优化表和索引
OPTIMIZE TABLE users;

说明：优化表和索引有助于减少碎片，提高查询性能。

`EXPLAIN` 查询优化的具体案例

EXPLAIN 可以帮助你分析 SQL 查询的执行计划，从而找出潜在的性能瓶颈。以下是一些常见的 EXPLAIN 查询优化案例：

1. 基本使用

示例：

-- 使用 EXPLAIN 分析查询执行计划
EXPLAIN SELECT * FROM orders WHERE order_id = 1;

解释：

EXPLAIN 显示了 SQL 查询的执行计划，包括使用的索引、扫描的表、以及每个步骤的成本等信息。

2. 查询性能分析

案例：

假设你有以下查询：

SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

EXPLAIN 输出：

+----+-------------+--------+--------+-------------------------+----------+---------+------+---------+----------+
| id | select_type | table  | type   | possible_keys           | key      | key_len | ref  | rows    | Extra    |
+----+-------------+--------+--------+-------------------------+----------+---------+------+---------+----------+
| 1  | SIMPLE      | orders | range  | order_date_index        | order_date_index | 5      | NULL | 1000    | Using where |
+----+-------------+--------+--------+-------------------------+----------+---------+------+---------+----------+

解释：

type 列的值是 range，表示使用了范围索引，这是一个比全表扫描更高效的查询类型。
key 列显示了使用的索引 order_date_index。

优化：

确保 order_date 列有合适的索引以支持范围查询。如果查询速度慢，考虑对查询条件添加合适的索引。

3. 联表查询优化

案例：

假设你有以下联表查询：

SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date > '2024-07-01';

EXPLAIN 输出：

+----+-------------+--------+--------+--------------------------+----------+---------+------+---------+----------------+
| id | select_type | table  | type   | possible_keys            | key      | key_len | ref  | rows    | Extra          |
+----+-------------+--------+--------+--------------------------+----------+---------+------+---------+----------------+
| 1  | SIMPLE      | o      | range  | order_date_index         | order_date_index | 5      | NULL | 5000    | Using where     |
| 1  | SIMPLE      | c      | eq_ref | PRIMARY                  | PRIMARY  | 4       | o.customer_id | 1       | Using where |
+----+-------------+--------+--------+--------------------------+----------+---------+------+---------+----------------+

解释：

o 表示 orders 表，type 列为 range，表示 order_date_index 被使用。
c 表示 customers 表，type 列为 eq_ref，表示使用了主键索引 PRIMARY 进行等值连接。

优化：

确保联表查询中的连接字段 customer_id 上有索引，并且 order_date 上有范围索引，以提高查询效率。

4. 优化排序操作

案例：

假设你有以下查询：

SELECT * FROM products ORDER BY price DESC;

EXPLAIN 输出：

+----+-------------+----------+--------+-------------------------+----------+---------+------+---------+-----------------+
| id | select_type | table    | type   | possible_keys           | key      | key_len | ref  | rows    | Extra           |
+----+-------------+----------+--------+-------------------------+----------+---------+------+---------+-----------------+
| 1  | SIMPLE      | products | index  | price_index             | price_index | 5      | NULL | 10000   | Using index; Using filesort |
+----+-------------+----------+--------+-------------------------+----------+---------+------+---------+-----------------+

解释：

type 列为 index，表示使用了索引扫描，Extra 列显示了 Using index; Using filesort，表示索引排序的结果需要额外的文件排序。

优化：

如果排序操作导致性能问题，确保 price 列上有索引，并且索引是按照降序排列的，这样可以减少文件排序的开销。

5. 避免全表扫描

案例：

假设你有以下查询：

SELECT * FROM orders WHERE status = 'completed';

EXPLAIN 输出：

+----+-------------+--------+--------+--------------------+------+---------+------+---------+----------------+
| id | select_type | table  | type   | possible_keys      | key  | key_len | ref  | rows    | Extra          |
+----+-------------+--------+--------+--------------------+------+---------+------+---------+----------------+
| 1  | SIMPLE      | orders | ALL    | status_index       | NULL | NULL    | NULL | 100000  |                |
+----+-------------+--------+--------+--------------------+------+---------+------+---------+----------------+

解释：

type 列为 ALL，表示进行全表扫描，没有使用索引。

优化：

确保 status 列上有索引，以避免全表扫描，提高查询效率

总结

通过了解和应用 MySQL 查询优化的最佳实践以及 EXPLAIN 的使用，你可以有效地提高查询性能，避免常见的性能瓶颈。规范化 SQL 语句、选择合适的数据类型、避免不必要的函数调用和全表扫描等措施，能够帮助你更高效地管理和优化数据库操作。希望本文的示例和案例能为你的数据库优化提供实用的参考和指导。

参考链接：https://github.com/alibaba/p3c/blob/master/Java%E5%BC%80%E5%8F%91%E6%89%8B%E5%86%8C(%E9%BB%84%E5%B1%B1%E7%89%88).pdf

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2024-07-30，如有侵权请联系 cloudcommunity@tencent.com 删除

索引

本文分享自千羽的编程时光微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度