在 MySQL 数据库中，编写高效的 SQL 语句不仅能提升查询速度，还能优化资源使用，避免潜在的性能问题。本文将详细介绍 MySQL 查询中的一些强制性和推荐性规范，并提供具体的示例来帮助你更好地理解和应用这些最佳实践。
避免使用 count(列名) 替代 count(*)
【强制】 不要使用 count(列名) 或 count(常量) 来替代 count(*)。count(*) 是 SQL92 定义的标准语法，用于统计行数，跟数据库系统无关，且能够统计包括 NULL 值在内的所有行。
说明：count(*) 计算所有行，包括 NULL 值，而 count(列名) 不统计 NULL 值的行。
正例：
代码语言：javascript复制-- 正确：统计所有行
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 才能返回有效的结果。
正例：
代码语言：javascript复制-- 统计 `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（空指针异常）问题。
正例：
代码语言：javascript复制-- 避免 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。
正例：
代码语言：javascript复制-- 检查是否为 NULL
SELECT * FROM orders WHERE ISNULL(customer_id);
示例：
如果你需要筛选出 customer_id 为 NULL 的订单，使用 ISNULL() 进行判断，而不是直接使用 NULL 比较操作符。
分页查询中的优化
【强制】 编写分页查询逻辑时，如果 count 为 0，应直接返回，避免执行后续的分页语句。
说明：当查询结果为空时，进行分页操作是没有意义的，可以通过早期退出来提高性能。
正例：
代码语言：javascript复制-- 查询总数
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 时，手动更新成绩表中的相关记录，而不是依赖数据库自动更新。
示例：
代码语言：javascript复制-- 更新学生记录时，同时更新成绩记录
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 语句和应用层逻辑来实现业务需求，而不是依赖存储过程。
示例：
代码语言：javascript复制-- 使用标准 SQL 实现业务逻辑，而非存储过程
SELECT * FROM orders WHERE status = 'shipped';
数据订正的最佳实践
【强制】 在执行数据订正（如删除或修改记录）之前，要先使用 SELECT 语句确认数据，以避免误操作。
说明：在执行 UPDATE 或 DELETE 操作前，通过 SELECT 语句检查数据，确保操作的准确性。
正例：
代码语言：javascript复制-- 先检查数据
SELECT * FROM orders WHERE order_id = '1234';
-- 确认无误后执行删除
DELETE FROM orders WHERE order_id = '1234';
示例：
在删除订单记录之前，先查询该订单的详细信息，确保删除操作不会影响其他数据。
合理使用 in 操作符
【推荐】 避免使用 IN 操作符，如果无法避免，确保 IN 后的集合元素数量控制在 1000 个之内。
说明：IN 操作符用于检查某个值是否在一个集合中。集合过大可能导致性能问题。
正例：
代码语言：javascript复制-- 控制集合大小
SELECT * FROM products WHERE product_id IN (1, 2, 3, ..., 1000);
示例：
如果你需要查询多个产品的详细信息，将 IN 子句中的元素数量控制在合理范围内，避免性能下降。
全球化字符存储与表示
【参考】 如果涉及全球化需求，所有字符存储和表示均以 UTF-8 编码为宜。对于表情等特殊字符，使用 utf8mb4 编码。
说明：LENGTH() 和 CHARACTER_LENGTH() 函数在处理字符时会有不同的结果。utf8mb4 能够存储更多字符。
正例：
代码语言：javascript复制-- 计算字符长度
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 会快速删除所有记录，但可能会引发意外问题，建议在需要删除大量数据时谨慎使用。
正例：
代码语言：javascript复制-- 不推荐使用 TRUNCATE TABLE
TRUNCATE TABLE orders;
-- 推荐使用 DELETE 语句，并添加事务支持
START TRANSACTION;
DELETE FROM orders;
COMMIT;
示例：
在开发过程中，使用 DELETE 语句并结合事务，能够更安全地进行数据删除操作，并确保数据一致性。
MySQL 查询优化及一些规范
MySQL 语句的规范
1. 使用合适的数据类型
【强制】 为每一列选择最合适的数据类型，避免使用过大的数据类型，这样可以减少存储空间并提高查询性能。
正例：
代码语言：javascript复制-- 使用合适的数据类型
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50), -- 合适长度的 VARCHAR 类型
created_at DATETIME -- 使用 DATETIME 类型记录时间
);
反例：
代码语言：javascript复制-- 不推荐：使用过大的数据类型
CREATE TABLE logs (
id BIGINT PRIMARY KEY AUTO_INCREMENT, -- 不必要的大类型
message TEXT, -- 使用 TEXT 存储小字符串
created_at TIMESTAMP -- TIMESTAMP 适合存储时间戳
);
说明：使用合适的数据类型可以节省存储空间并提高查询效率。选择合适的字段长度和类型对于性能至关重要。
2. 避免在 WHERE 子句中使用函数
【强制】 尽量避免在 WHERE 子句中对列使用函数，这样会导致索引失效，影响查询性能。
正例：
代码语言：javascript复制-- 避免函数使用，直接比较列值
SELECT * FROM orders WHERE order_date = '2024-07-29';
-- 使用函数对列进行操作时需要慎重
-- SELECT * FROM orders WHERE DAY(order_date) = 29;
说明：函数调用会阻止 MySQL 使用索引，从而降低查询性能。尽量将查询条件设置为列的原始值。
3. 合理使用事务
【强制】 在数据操作时使用事务来确保数据一致性和完整性。确保在事务中包含 COMMIT 或 ROLLBACK。
正例：
代码语言：javascript复制-- 使用事务处理数据
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 语句中使用 *，应该明确列出所需的列。这有助于减少数据传输量和提高查询性能。
正例：
代码语言：javascript复制-- 明确选择所需的列
SELECT id, name, email FROM users;
说明：选择具体列可以减少数据量和内存使用，优化查询性能。
5. 定期进行表和索引的优化
【推荐】 定期使用 OPTIMIZE TABLE 进行表和索引的优化，以保持数据库性能的稳定性。
正例：
代码语言：javascript复制-- 优化表和索引
OPTIMIZE TABLE users;
说明：优化表和索引有助于减少碎片，提高查询性能。
EXPLAIN 查询优化的具体案例
EXPLAIN 可以帮助你分析 SQL 查询的执行计划，从而找出潜在的性能瓶颈。以下是一些常见的 EXPLAIN 查询优化案例：
1. 基本使用
示例：
代码语言：javascript复制-- 使用 EXPLAIN 分析查询执行计划
EXPLAIN SELECT * FROM orders WHERE order_id = 1;
解释：
EXPLAIN 显示了 SQL 查询的执行计划，包括使用的索引、扫描的表、以及每个步骤的成本等信息。
2. 查询性能分析
案例：
假设你有以下查询：
代码语言：javascript复制SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';
EXPLAIN 输出：
代码语言：javascript复制 ---- ------------- -------- -------- ------------------------- ---------- --------- ------ --------- ----------
| 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. 联表查询优化
案例：
假设你有以下联表查询：
代码语言：javascript复制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 输出：
代码语言：javascript复制 ---- ------------- -------- -------- -------------------------- ---------- --------- ------ --------- ----------------
| 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. 优化排序操作
案例：
假设你有以下查询：
代码语言：javascript复制SELECT * FROM products ORDER BY price DESC;
EXPLAIN 输出：
代码语言：javascript复制 ---- ------------- ---------- -------- ------------------------- ---------- --------- ------ --------- -----------------
| 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. 避免全表扫描
案例：
假设你有以下查询：
代码语言：javascript复制SELECT * FROM orders WHERE status = 'completed';
EXPLAIN 输出：
代码语言：javascript复制 ---- ------------- -------- -------- -------------------- ------ --------- ------ --------- ----------------
| 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开发手册(黄山版).pdf
