MySQL中一定要遵守的12个SQL规范

宅哥聊构架 数据库 2024-08-09

MySQL中一定要遵守的12个SQL规范

1. 始终使用 EXPLAIN 语句

开发 SQL 查询时,养成使用 EXPLAIN 的习惯至关重要。特别要注意索引的使用效率。例如:sql

代码解读
复制代码
EXPLAIN SELECT * FROM employees WHERE department = 'Engineering';

“EXPLAIN”语句的输出将提供 MySQL 打算如何执行查询的详细信息,包括是否使用索引和操作顺序。输出可能如下所示:sql

代码解读
复制代码
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+ | id | selecttype | table     | type | possiblekeys | key  | keylen | 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

代码解读
复制代码
-- Delete only one row from the 'employees' table with a specific condition DELETE FROM employees WHERE department = 'Engineering' LIMIT 100;
  • 添加 LIMIT 子句有助于减少错误 SQL 语句的影响。如果在命令行中执行 SQL 命令时忘记添加 LIMIT,可能会不小心删除所有数据。

  • 通过添加例如 LIMIT 200,可以将潜在的损害限制在一定范围内,以便在意外删除时通过 binlog 日志恢复最多 200 条记录。

  • 在 SQL 查询中使用 LIMIT 1 可以提高效率,因为它在找到第一个匹配项后停止查询执行,避免了不必要的表扫描。

  • 此外,包含 LIMIT 子句有助于避免在 DELETE 等操作期间的长时间事务,特别是在处理大型数据集时,可以防止 CPU 过载和性能下降。

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

设计数据库表时,养成为所有表和字段添加适当注释的习惯。这种做法有助于未来维护,为数据库结构提供清晰的上下文。

示例如下:sql

代码解读
复制代码
CREATE TABLE `account` (   `id` int(11NOT NULL AUTOINCREMENT COMMENT '主键',   `name` varchar(255DEFAULT NULL COMMENT '账户名称',   `balance` int(11DEFAULT NULL COMMENT '账户余额',   `createtime` datetime NOT NULL COMMENT '创建时间',   `updatetime` datetime NOT NULL ON UPDATE CURRENTTIMESTAMP COMMENT '更新时间',   PRIMARY KEY (`id`),   KEY `idxname` (`name`) USING BTREE ) ENGINE=InnoDB AUTOINCREMENT=1570068 DEFAULT CHARSET=utf8 ROWFORMAT=REDUNDANT COMMENT='账户信息表';

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

SQL 书写规范中强调关键字大写和使用缩进,是为了提升代码的可读性和整洁度。关键字大写使得关键词更加醒目,便于快速识别,而缩进则清晰展现了语句结构,有助于理解代码逻辑,两者结合极大地增强了代码的可维护性和团队协作效率。

正例:sql

代码解读
复制代码
SELECT stu.name, SUM(stu.score) FROM Student AS stu WHERE stu.classNo = '10' GROUP BY stu.name;

反例:sql

代码解读
复制代码
SELECT stu.name, sum(stu.score) from Student stu WHERE stu.classNo = '10' group by stu.name.

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

这样做可以确保数据插入的准确性和健壮性。明确指定字段名能避免因表结构变动引发的错误,使得插入操作更加明确且易于维护,尤其是在表结构频繁调整的场景下。此外,这种方法还能提升代码的可读性,便于其他开发者理解插入操作的具体意图,以及在出现问题时快速定位和调试。sql

代码解读
复制代码
INSERT INTO Student (id, name, score) VALUES (123, 'user1', 100);

6. 先在测试环境中验证 SQL

在执行任何 SQL 之前,先在测试环境中验证一次,然后再到生产环境中执行;这是因为:

  • 安全性:避免直接在生产环境执行可能影响数据完整性和安全性的 SQL 语句。

  • 错误检测:在测试环境中可以发现并修复 SQL 语句中的错误或性能问题,减少在生产环境中出现问题的风险。

  • 性能评估:测试环境中可以模拟生产环境的数据量和使用情况,评估 SQL 语句的性能,确保其在生产环境中的效率。

  • 影响控制:测试环境通常对业务影响较小,可以控制 SQL 变更的影响范围,避免对生产环境造成不必要的中断或损失。

7. 表中必须包含主键、创建时间、更新时间字段

这些字段在数据库设计中是常用的最佳实践,有助于提高数据的可靠性、可维护性和可追溯性。

  • 主键:主键是用于唯一标识表中每个记录的字段。它确保了每条记录的唯一性,并为数据库提供了快速的数据定位和索引。主键还用于建立数据表之间的关联关系,实现数据的一致性和完整性。

  • 创建时间:创建时间字段记录了每条记录的插入时间。它对于跟踪数据的创建顺序和时间线非常有用。创建时间可以用于数据分析、审计和排查问题,还可以帮助识别过时的数据或定期清理不再需要的数据。

  • 更新时间:更新时间字段记录了每条记录的最后一次更新时间。它对于追踪数据的变更、维护数据的一致性和追溯数据流动非常重要。更新时间可以用于监控数据的变化、检测数据的实效性,并支持数据恢复和修复操作。

示例 :sql

代码解读
复制代码
CREATE TABLE `exampletable` (   `id` INT AUTOINCREMENT PRIMARY KEY,   `name` VARCHAR(255),   `createtime` TIMESTAMP DEFAULT CURRENTTIMESTAMP,   `updatetime` TIMESTAMP DEFAULT CURRENTTIMESTAMP ON UPDATE CURRENTTIMESTAMP );

8. Update 和 Delete 语句必须要指定 Where 条件

执行 Update 和 Delete 语句时必须指定 Where 条件,是为了精确地定位并更改或删除数据库中指定的数据行,避免无差别地更改或删除所有数据,从而防止数据丢失或损坏,保证操作的准确性和安全性。同时可以减少数据库操作的范围,只对符合条件的数据行进行更新或删除。这样可以提高操作的执行效率,减少资源的消耗,提高操作的性能和效率。

示例:sql

代码解读
复制代码
-- Update the salary of the employee with ID 123 UPDATE employees SET salary = 60000 WHERE id = 123;

9. 使用 InnoDB 存储引擎

InnoDB 是一种流行的 MySQL 存储引擎,它被广泛使用,主要因为它具有以下优点:

  • 事务支持:InnoDB 是一个支持事务的存储引擎,它提供了 ACID(原子性、一致性、隔离性和持久性)特性。这意味着你可以在 InnoDB 表上执行复杂的事务操作,确保数据的一致性和完整性。

  • 行级锁:与表级锁定相比,InnoDB 的行级锁定可以提供更好的并发性能,因为它只锁定数据行而不是整个表。

  • 外键约束:InnoDB 支持外键约束,可以在数据库层面实现关联表之间的数据完整性和一致性。外键可以帮助维护表与表之间的关系,并防止不一致或无效的数据关联。

  • 并发控制:InnoDB 使用多版本并发控制(MVCC)来处理并发访问。这意味着它可以在不同的事务之间提供高度的隔离性,避免数据的冲突和锁竞争。

  • 崩溃恢复:InnoDB 提供了崩溃恢复机制,可以在数据库发生故障或断电后自动进行恢复。

示例:sql

代码解读
复制代码
CREATE TABLE users (     id INT AUTOINCREMENT PRIMARY KEY,     name VARCHAR(100),     email VARCHAR(100) ) ENGINE=InnoDB; CREATE TABLE orders (     id INT AUTOINCREMENT PRIMARY KEY,     userid INT,     amount DECIMAL(102),     createdat TIMESTAMP,     FOREIGN KEY (userid) REFERENCES users(id) ) ENGINE=InnoDB;

10. 避免使用 SELECT *

使用 SELECT <特定字段>只检索所需要数据有以下好处:

  • 提高查询性能:只查询需要的字段可以减少数据库的工作量和查询时间。数据库不需要检索和处理不相关的数据列,可以更快地返回结果。

  • 减少网络传输量:因为从数据库服务器到客户端只需要传输实际需要的数据,这可以显著减少数据传输时间和带宽消耗。

  • 降低内存消耗:查询结果中只包含需要的字段可以减少应用程序的内存消耗。如果返回的数据集较大,只保留必要的字段可以减少内存使用量,并降低应用程序的内存压力。

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

设计数据库中的库和表时选择 UTF8(更准确地说是 UTF8MB4)作为字符集,主要原因如下:

  • 国际化支持:UTF8MB4 字符集能够支持全球大多数语言的文字,包括各种特殊字符、表情符号等,这使得应用能够无障碍地服务于多语言环境下的用户。

  • 兼容性:UTF8MB4 是 UTF8 的超集,它向下兼容 UTF8,意味着使用 UTF8MB4 不会影响原有的 UTF8 数据,同时还能支持 4 字节的 Unicode 字符,确保了数据的完整性和广泛适用性。

  • 未来扩展性:UTF8 是一种可扩展的字符集,它支持 Unicode 编码,并具备适应未来字符需求的能力。

12. 索引的命名规范

统一命名规范能让索引更易读、易懂,方便团队协作和维护,减少操作错误,利于自动化脚本处理,以及符合标准化要求,从而提升数据库的管理效率和系统稳定性。

  • 对于主键索引,使用“pk”作为前缀后跟字段名,如“pk_id”表示“id”字段的主键索引,清晰表明此索引代表主键约束。

  • 对于唯一索引,使用“uk”作为前缀后跟字段名,如“uk_email”表示“email”字段的唯一索引,表示此索引强制字段上的唯一约束。

  • 对于常规索引,使用“idx”作为前缀后跟字段名,如“idx_lastname”表示“lastname”字段的索引,用于优化指定字段的搜索。

转载来源:https://juejin.cn/post/7371384453358632975

Apipost 私有化火热进行中

评论