优化你的MySQL查询：12个必备的SQL书写习惯！

非科班大厂码农（同名公众号）

于 2024-09-12 07:11:06 发布

阅读量618

点赞数 9

分类专栏：数据库文章标签： mysql

本文链接：https://blog.csdn.net/weixin_42627385/article/details/142155800

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

良好的书写SQL习惯非常重要，原因如下：

结构良好的 SQL 语句提高了可读性，更容易理解、维护和调试。
优化后的SQL查询可以更快地执行，减少服务器的工作负担，从而提高数据库性能。
遵循最佳实践可以避免SQL注入等安全问题，保护敏感数据不被泄露或恶意攻击。
统一的SQL编写风格有利于团队成员之间的协作，保证代码的一致性，使项目更容易扩展和维护。

总之，良好的 SQL 书写习惯可以提高数据库系统的效率、安全性和可维护性，使开发人员和最终用户都能从中受益。

1. 使用 EXPLAIN 语句

在编写SQL查询语句时，使用EXPLAIN是一个很好的习惯，它可以帮助我们了解数据库是如何执行查询的，尤其是看查询是否充分利用了索引。例如：

EXPLAIN SELECT * FROM employees WHERE department = 'Engineering';

EXPLAIN它是一个工具，它会告诉你 MySQL 计划如何执行你的查询。它提供了一些重要信息，比如查询使用了哪些表、扫描了多少行、是否使用了索引，以及这些操作的顺序等等。

EXPLAIN的输出可能如下所示：

+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| 1  | SIMPLE      | employees | ref  | department    | department| 14      | const| 100  | Using index |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+

在这个示例中，MySQL 显示它正在使用索引department从employees表中检索数据，这通常是好兆头，数据库意味着通过索引快速定位数据，而不是一行一行地查找。

2. 执行 DELETE/UPDATE 时包含 LIMIT

在执行 DELETE 或 UPDATE 语句时，建议使用LIMIT子句来限制操作的范围。原因如下：

防止意外删除或更新大量数据：如果你在命令行中执行SQL命令时，不小心忘记了条件或写错了条件，可能会导致删除或更新整个表的数据。加入LIMIT子句，比如LIMIT 200，可以限制每个次操作最多影响200条记录，这样即使出错，损失基因有限。你还可以通过日志（如binlog）恢复这些记录。
提高查询效率：例如在查询中使用LIMIT 1可以在找到第一个符合条件的记录后立即停止搜索，避免扫描整个表的数据。这大大节省了时间和资源，提升了数据库的效率。
此外，包含 LIMIT 子句还有助于避免在 DELETE 等操作过程中发生冗长事务，因为在这种情况下，带有索引列的行可能会导致 MySQL 应用写锁和间隙锁，从而阻塞相关行并影响业务操作。
此外，在处理大型数据集时，如果不限制处理的记录数，CPU 就会超载，导致删除过程中性能下降。

以下面的 SQL 语句为例：

-- Delete only one row from the 'employees' table with a specific condition
DELETE FROM employees WHERE department = 'Engineering' LIMIT 100;

3. 为表和字段适当添加注释

在设计数据库表时，必须养成在表和字段中适当添加注释的习惯。这种做法可以为数据库结构提供清晰的上下文，有助于今后的维护工作。例如

CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
  `name` varchar(255) DEFAULT NULL COMMENT 'Account Name',
  `balance` int(11) DEFAULT NULL COMMENT 'Balance',
  `create_time` datetime NOT NULL COMMENT 'Creation Time',
  `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update Time',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='Table for Accounts';

4. 关键词大写并使用缩进

例如：

# 好的做法
SELECT stu.name, SUM(stu.score)
FROM Student AS stu
WHERE stu.classNo = '10'
GROUP BY stu.name;

#不好的做法
SELECT stu.name, sum(stu.score) from Student stu WHERE stu.classNo = '10' group by stu.name.

SELECT stu.name, SUM(stu.score)
FROM Student AS stu
WHERE stu.classNo = ‘10’
GROUP BY stu.name;

5. 在 INSERT 语句中指定字段名称

# 好的做法
INSERT INTO Student (id, name, score) VALUES ('123', 'user1', '100');

#不好的做法
insert into Student values ('123','user1','100');

6. 始终在测试环境中测试 SQL

在执行任何 SQL 修改之前，应该先在测试环境中运行，这样可以避免将有错误的语法或逻辑直接部署到生产环境中，避免损害实际业务。

为 SQL 更改准备详细的操作步骤非常重要，特别是在有依赖关系的情况下。例如，如果你需要修改表结构，而这个表还涉及其他数据操作，先后顺序就非常关键。

此外，在将 SQL 修改部署到生产环境之前，需要对所有更改进行审查。这可以由团队中的其他成员或数据库管理员来进行，确保没有遗漏或潜在的问题。

7. 表中一般需要包含主键、创建时间和更新时间字段

在设计数据库表时，添加主键、创建时间和更新时间这三个字段有以下好处：

主键：确保每条记录都有唯一的标识符，便于高效的数据检索和表之间的引用。
创建时间：你可以查看记录的创建时间，了解数据何时被添加到系统中。这有助于审计和分析数据的历史。
更新时间：更新时间字段帮助你跟踪数据的修改历史，知道最近一次数据被更新的时间。这对于版本控制和变更管理很重要。

例如：

CREATE TABLE `example_table` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255),
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

8. 在更新/删除前使用 WHERE 子句进行确认

在进行 SQL 数据更新或删除操作时，使用 WHERE 子句确认受影响的记录是一个重要的步骤。这种做法在处理生产数据时尤为重要，可避免意外更改。

SELECT * FROM employees WHERE id = 123;

UPDATE employees SET salary = 60000 WHERE id = 123;

9. 使用 InnoDB 存储引擎

InnoDB 支持事务、行级锁定和崩溃恢复，因此高并发环境的首选

因此，除非存在 InnoDB 无法满足的特殊需求，比如需要列存储（适用于分析型查询）或空间数据存储（用于地理信息），否则建议使用 InnoDB。

使用统一的InnoDB存储引擎可以让你的数据库管理和维护更加简单一致，并在大多数情况下利用 InnoDB 的性能优势。

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
) ENGINE=InnoDB;

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10, 2),
    created_at TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

10. 尽量避免使用 “SELECT *”

使用 SELECT <特定字段> 来只检索所需的数据这样做有几个好处：

提高性能：只选择需要的字段可以减少数据库需要处理和返回的数据量，从而提高查询性能。假设你有一个包含很多列的表，但你只需要其中的几个字段。如果你用 SELECT *，数据库会把所有列的数据都拿出来，这样不仅浪费了资源，还可能拖慢查询速度。只选择需要的字段，比如 SELECT name, age FROM users，可以减少数据的处理量，从而提高查询性能。
减少网络流量：只获取所需的字段，可以最大限度地减少通过网络传输的数据量。
清晰易读：如果你只需要用户的姓名和年龄，用 SELECT name, age FROM users 可以明确表示你的意图。而使用 SELECT * 可能会让人不确定你真正需要哪些数据，可能导致理解上的困难，特别是当表有很多列时。
避免意外后果：如果你用 SELECT * 查询数据表，可能会包含敏感信息（如用户密码）或不必要的数据，这可能会导致安全问题或泄露隐私。

11. 使用 UTF8 作为数据库和表的字符集

这种做法有助于防止字符编码问题，并确保数据存储和检索流程的一致性。

使用 UTF8 这样的统一字符集可以避免在比较和转换不同字符集时出现问题。
如果你的数据库表使用了多种字符集，可能会影响查询性能，尤其是涉及索引时。不同字符集的比较方式可能不一致，从而导致索引失效。
如果要存储表情符号或其他特殊字符，请考虑使用 utf8mb4 而不是 UTF8。

请注意 utf8mb4 和 UTF-8 编码之间的区别。

UTF8 实际上是 UTF-8 的一种较老的实现，只能存储大约 3 字节的字符，这限制了它能表示的字符范围。

UTF8MB4是 MySQL 的一个更新版本，支持完整的 4 字节 UTF-8 编码，因此可以存储所有 Unicode 字符，包括表情符号和其他特殊字符。

12. 数据库索引的标准命名约定

为数据库中的索引命名时，使用统一的命名约定可以使数据库结构更清晰、更容易管理。

主键索引命名：主键索引唯一标识表中的每条记录。在索引名称前加上 “pk_”，然后跟上字段名称，例如 "pk_id "表示建立在"id "字段的主键索引，这样从命名上就可以清楚地看出这个id字段是主键索引
唯一索引命名：唯一索引确保某个字段的值在整个表中是唯一的。索引名称前缀为 “uk_”，后跟字段名，如 "uk_email "表示建立在 "email "字段的唯一索引。
普通索引命名 (idx_)：常规索引名称前缀为 “idx_”，后跟字段名称，如 "idx_last_name "表示建立在"last_name "字段上的普通索引。