MariaDB SQL 书写规范

规范的重要性

SQL书写规范是数据库开发和运维的基础，良好的SQL规范可以：

提高SQL语句的可读性和可维护性
减少语法错误和逻辑错误
提高查询性能
便于团队协作
降低后续优化成本

本文将详细介绍MariaDB SQL的书写规范，包括命名规范、语法规范、性能优化建议和最佳实践。

命名规范

1. 数据库对象命名

对象类型	命名规则	示例
数据库	小写字母+下划线，清晰表达业务含义	`user_center`, `order_system`
表	小写字母+下划线，使用复数形式，清晰表达业务含义	`users`, `orders`, `order_items`
视图	以`v_`或`view_`开头，小写字母+下划线	`v_active_users`, `view_order_summary`
存储过程	以`sp_`或`proc_`开头，小写字母+下划线	`sp_get_user_info`, `proc_update_order_status`
函数	以`fn_`或`func_`开头，小写字母+下划线	`fn_calculate_total`, `func_format_date`
触发器	以`tr_`开头，表名+操作类型+触发时机	`tr_users_after_insert`, `tr_orders_before_update`
索引	以`idx_`开头，表名+列名组合（复合索引）	`idx_user_email`, `idx_order_user_status`
主键	以`pk_`开头，表名	`pk_users`, `pk_orders`
外键	以`fk_`开头，从表名+主表名+列名	`fk_order_user_id`, `fk_order_item_order_id`

2. 列命名

使用小写字母+下划线，清晰表达列的含义
避免使用保留字（如order, group, select等）
对于布尔类型，使用is_或has_前缀 | is_active, has_deleted |
对于日期时间类型，使用清晰的后缀 | created_at, updated_at, deleted_at |
保持命名一致性，相同含义的列在不同表中使用相同名称 | user_id在所有表中都表示用户ID |

3. 变量命名

使用小写字母+下划线
清晰表达变量的含义
存储过程和函数参数：使用p_前缀 | p_user_id, p_order_status |
局部变量：使用v_前缀 | v_total_amount, v_current_date |

语法规范

1. 大小写规范

SQL关键字使用大写 | SELECT, FROM, WHERE, JOIN, GROUP BY, ORDER BY |
数据库对象名（表、列、索引等）使用小写 | users, user_id, idx_user_email |
字符串常量使用单引号 | 'active', '2024-01-01' |
数字常量直接使用，不使用引号 | 123, 45.67 |

2. 缩进和换行

每个关键字独占一行
使用4个空格进行缩进，避免使用制表符
较长的SQL语句进行合理换行
逗号放在行尾，便于添加和删除列
运算符两侧添加空格

示例：

sql

-- 推荐
SELECT 
    u.user_id,
    u.username,
    o.order_id,
    o.order_date,
    o.total_amount
FROM 
    users u
JOIN 
    orders o ON u.user_id = o.user_id
WHERE 
    u.is_active = 'Y'
    AND o.order_date >= '2024-01-01'
    AND o.total_amount > 100
ORDER BY 
    o.order_date DESC,
    o.total_amount DESC
LIMIT 100;

-- 不推荐
select u.user_id,u.username,o.order_id,o.order_date,o.total_amount from users u join orders o on u.user_id=o.user_id where u.is_active='Y' and o.order_date>='2024-01-01' and o.total_amount>100 order by o.order_date desc,o.total_amount desc limit 100;

3. 注释规范

使用--进行单行注释
使用/* */进行多行注释
注释内容清晰、简洁，说明SQL的功能和逻辑
复杂SQL语句必须添加注释
对于存储过程和函数，添加详细的注释说明参数、返回值和功能

示例：

sql

-- 查询活跃用户的订单信息
SELECT 
    u.user_id,
    u.username,
    o.order_id,
    o.order_date,
    o.total_amount
FROM 
    users u
JOIN 
    orders o ON u.user_id = o.user_id
WHERE 
    u.is_active = 'Y' -- 只查询活跃用户
    AND o.order_date >= '2024-01-01' -- 只查询2024年以后的订单
    AND o.total_amount > 100 -- 只查询金额大于100的订单
ORDER BY 
    o.order_date DESC,
    o.total_amount DESC
LIMIT 100;

/*
 * 存储过程：获取用户信息
 * 参数：
 *   p_user_id: 用户ID
 * 返回：
 *   用户详细信息
 */
CREATE PROCEDURE sp_get_user_info(
    IN p_user_id INT
)
BEGIN
    -- 存储过程逻辑
END;

4. 别名规范

表别名使用简短、有意义的缩写（1-3个字符）
列别名清晰表达列的含义，避免使用无意义的别名
对于复杂表达式，使用别名提高可读性
使用AS关键字明确指定别名

示例：

sql

-- 推荐
SELECT 
    u.user_id AS id,
    u.username AS name,
    COUNT(o.order_id) AS order_count,
    SUM(o.total_amount) AS total_spent
FROM 
    users u
LEFT JOIN 
    orders o ON u.user_id = o.user_id
GROUP BY 
    u.user_id,
    u.username;

-- 不推荐
SELECT 
    a.b AS c,
    d.e AS f,
    COUNT(g.h) AS i,
    SUM(g.j) AS k
FROM 
    users a
LEFT JOIN 
    orders g ON a.b = g.l;

SELECT语句规范

1. 明确指定列名

避免使用SELECT *，只选择需要的列
明确指定列名可以提高查询性能（减少网络传输和内存消耗）
避免因表结构变化导致的查询错误

示例：

sql

-- 推荐
SELECT user_id, username, email FROM users;

-- 不推荐
SELECT * FROM users;

2. 合理使用LIMIT

对于只需要少量数据的查询，使用LIMIT限制返回行数
避免返回大量数据，导致性能问题
分页查询必须使用LIMIT

示例：

sql

-- 推荐：只查询前100条记录
SELECT user_id, username FROM users ORDER BY created_at DESC LIMIT 100;

-- 推荐：分页查询
SELECT user_id, username FROM users ORDER BY created_at DESC LIMIT 0, 20;

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

在索引列上使用函数会导致索引失效
将函数或表达式移到查询条件的右侧

示例：

sql

-- 推荐
SELECT * FROM users WHERE created_at >= '2024-01-01';

-- 不推荐
SELECT * FROM users WHERE YEAR(created_at) = 2024;

4. 合理使用JOIN

优先使用INNER JOIN，避免不必要的外连接
连接条件使用索引列
避免过多的表连接（建议不超过5个表）
对于复杂查询，考虑使用临时表或视图

示例：

sql

-- 推荐：使用INNER JOIN，连接条件使用索引列
SELECT 
    u.user_id,
    u.username,
    o.order_id,
    o.order_date
FROM 
    users u
INNER JOIN 
    orders o ON u.user_id = o.user_id;

-- 不推荐：不必要的外连接
SELECT 
    u.user_id,
    u.username,
    o.order_id,
    o.order_date
FROM 
    users u
LEFT JOIN 
    orders o ON u.user_id = o.user_id
WHERE 
    o.order_id IS NOT NULL;

5. 合理使用GROUP BY和HAVING

GROUP BY子句中的列必须出现在SELECT列表中（除非使用聚合函数）
避免在HAVING子句中使用非聚合列
对于过滤条件，优先使用WHERE子句，而不是HAVING子句

示例：

sql

-- 推荐：使用WHERE过滤，然后GROUP BY
SELECT 
    user_id,
    COUNT(order_id) AS order_count
FROM 
    orders
WHERE 
    order_date >= '2024-01-01'
GROUP BY 
    user_id;

-- 不推荐：使用HAVING过滤
SELECT 
    user_id,
    COUNT(order_id) AS order_count
FROM 
    orders
GROUP BY 
    user_id
HAVING 
    order_date >= '2024-01-01';

INSERT语句规范

1. 明确指定列名

明确指定插入的列名，避免因表结构变化导致的插入错误
提高SQL语句的可读性和可维护性

示例：

sql

-- 推荐
INSERT INTO users (username, email, password) VALUES ('john', 'john@example.com', 'password123');

-- 不推荐
INSERT INTO users VALUES (NULL, 'john', 'john@example.com', 'password123', NOW());

2. 批量插入

对于大量数据插入，使用批量插入（INSERT INTO ... VALUES (...), (...), ...）
批量插入可以减少网络往返次数，提高插入性能
控制批量插入的大小（建议每批次1000-5000条）

示例：

sql

-- 推荐：批量插入
INSERT INTO users (username, email, password) VALUES 
    ('john', 'john@example.com', 'password123'),
    ('jane', 'jane@example.com', 'password456'),
    ('bob', 'bob@example.com', 'password789');

-- 不推荐：多次单条插入
INSERT INTO users (username, email, password) VALUES ('john', 'john@example.com', 'password123');
INSERT INTO users (username, email, password) VALUES ('jane', 'jane@example.com', 'password456');
INSERT INTO users (username, email, password) VALUES ('bob', 'bob@example.com', 'password789');

3. 避免使用INSERT ... SELECT

对于大量数据迁移，考虑使用LOAD DATA INFILE或其他工具
INSERT ... SELECT会锁定表，影响其他操作
对于大数据量，使用分批处理

UPDATE语句规范

1. 明确指定WHERE条件

UPDATE语句必须包含WHERE条件，避免误更新所有记录
使用主键或唯一索引作为WHERE条件
对于批量更新，使用LIMIT限制更新行数

示例：

sql

-- 推荐：使用主键作为WHERE条件
UPDATE users SET email = 'new_email@example.com' WHERE user_id = 123;

-- 推荐：批量更新使用LIMIT
UPDATE users SET status = 'inactive' WHERE last_login < '2023-01-01' LIMIT 1000;

-- 不推荐：没有WHERE条件，可能更新所有记录
UPDATE users SET status = 'inactive';

2. 避免更新索引列

更新索引列会导致索引重建，影响性能
对于频繁更新的列，避免创建索引
考虑使用冗余列或其他设计方式

3. 批量更新

对于大量数据更新，使用分批处理
控制每批次更新的行数（建议每批次1000-5000条）
在业务低峰期进行批量更新

DELETE语句规范

1. 明确指定WHERE条件

DELETE语句必须包含WHERE条件，避免误删除所有记录
使用主键或唯一索引作为WHERE条件
对于批量删除，使用LIMIT限制删除行数

示例：

sql

-- 推荐：使用主键作为WHERE条件
DELETE FROM users WHERE user_id = 123;

-- 推荐：批量删除使用LIMIT
DELETE FROM users WHERE last_login < '2023-01-01' LIMIT 1000;

-- 不推荐：没有WHERE条件，可能删除所有记录
DELETE FROM users;

2. 优先使用软删除

对于重要数据，考虑使用软删除（添加is_deleted或deleted_at列）
软删除可以保留数据，便于恢复
避免误删除导致的数据丢失

示例：

sql

-- 推荐：软删除
UPDATE users SET is_deleted = 1, deleted_at = NOW() WHERE user_id = 123;

-- 不推荐：硬删除
DELETE FROM users WHERE user_id = 123;

3. 批量删除

对于大量数据删除，使用分批处理
控制每批次删除的行数（建议每批次1000-5000条）
在业务低峰期进行批量删除
考虑使用TRUNCATE TABLE（如果适合），但要注意其特性（不记录日志，无法回滚）

事务和锁规范

1. 合理使用事务

保持事务短小精悍，避免长时间占用锁
只在事务中包含必要的操作
避免在事务中执行耗时操作（如网络请求、文件IO等）
合理设置事务隔离级别

示例：

sql

-- 推荐：短事务
START TRANSACTION;
UPDATE orders SET status = 'paid' WHERE order_id = 123;
INSERT INTO order_payments (order_id, payment_method, amount) VALUES (123, 'credit_card', 100);
COMMIT;

-- 不推荐：长事务
START TRANSACTION;
UPDATE orders SET status = 'processing' WHERE order_id = 123;
-- 执行耗时操作
CALL sp_send_email('order_processing', 123);
UPDATE orders SET status = 'shipped' WHERE order_id = 123;
COMMIT;

2. 避免死锁

保持事务中操作的顺序一致
避免在同一事务中多次更新同一行记录
合理设置锁超时时间
使用SELECT ... FOR UPDATE或SELECT ... LOCK IN SHARE MODE时要谨慎

3. 合理使用锁

只锁定必要的数据
优先使用行级锁，避免表级锁
对于读多写少的场景，考虑使用乐观锁
对于写多读少的场景，考虑使用悲观锁

性能优化建议

1. 使用索引

为查询条件中的列创建索引
为JOIN条件中的列创建索引
为ORDER BY和GROUP BY中的列创建索引
避免过度索引

2. 优化子查询

对于复杂子查询，考虑使用JOIN替代
避免在WHERE子句中使用IN子查询（对于大数据量）
考虑使用EXISTS替代IN

示例：

sql

-- 推荐：使用JOIN替代子查询
SELECT u.user_id, u.username
FROM users u
JOIN orders o ON u.user_id = o.user_id
WHERE o.order_date >= '2024-01-01';

-- 不推荐：使用IN子查询
SELECT user_id, username
FROM users
WHERE user_id IN (SELECT user_id FROM orders WHERE order_date >= '2024-01-01');

3. 避免使用DISTINCT

DISTINCT会导致排序操作，影响性能
考虑使用GROUP BY替代DISTINCT
检查查询逻辑，是否真的需要去重

4. 合理使用临时表

对于复杂查询，考虑使用临时表存储中间结果
临时表可以提高查询性能，减少重复计算
注意临时表的生命周期，及时清理

5. 优化排序操作

为排序列创建索引，避免文件排序
减少排序的数据量
考虑在应用程序中进行排序

常见问题（FAQ）

Q1: 为什么要避免使用SELECT *？

A: 避免使用SELECT *的原因包括：

只选择需要的列可以减少网络传输和内存消耗
避免因表结构变化导致的查询错误
明确指定列名可以提高查询性能
便于阅读和维护

Q2: 如何处理复杂查询？

A: 处理复杂查询可以考虑以下方法：

分解为多个简单查询
使用临时表存储中间结果
考虑使用视图
优化子查询，考虑使用JOIN替代
为查询创建合适的索引

Q3: 批量插入的最佳大小是多少？

A: 批量插入的最佳大小取决于多个因素，包括：

数据库配置
服务器资源
网络环境
数据大小

一般建议每批次插入1000-5000条记录，具体可以通过测试确定最佳值。

Q4: 如何避免死锁？

A: 避免死锁的方法包括：

保持事务中操作的顺序一致
避免在同一事务中多次更新同一行记录
合理设置锁超时时间
减少事务的持有时间
避免使用不必要的锁

Q5: 什么时候使用软删除，什么时候使用硬删除？

A: 选择软删除或硬删除取决于业务需求：

软删除：适用于需要保留数据历史、便于恢复的场景，如用户数据、订单数据等
硬删除：适用于临时数据、日志数据等不需要保留的数据

Q6: 如何优化子查询？

A: 优化子查询的方法包括：

使用JOIN替代IN子查询
使用EXISTS替代IN子查询
为子查询中的列创建索引
考虑使用临时表存储子查询结果

Q7: 什么时候使用临时表？

A: 适合使用临时表的场景包括：

复杂查询需要多次使用中间结果
需要对大量数据进行预处理
减少重复计算
提高查询可读性和可维护性

Q8: 如何优化排序操作？

A: 优化排序操作的方法包括：

为排序列创建索引，避免文件排序
减少排序的数据量
考虑在应用程序中进行排序
合理设置sort_buffer_size参数

总结

良好的SQL书写规范是数据库开发和运维的基础，对于提高查询性能、减少错误、便于团队协作至关重要。

DBA和开发人员应该：

遵循统一的命名规范
保持良好的语法规范和缩进
编写高效、可维护的SQL语句
合理使用索引、事务和锁
定期优化和重构SQL语句

通过遵循本文介绍的SQL书写规范，可以提高MariaDB数据库的性能和可靠性，为业务提供高效、稳定的数据服务。

MariaDB SQL 书写规范 ​

规范的重要性 ​

命名规范 ​

1. 数据库对象命名 ​

2. 列命名 ​

3. 变量命名 ​

语法规范 ​

1. 大小写规范 ​

2. 缩进和换行 ​

3. 注释规范 ​

4. 别名规范 ​

SELECT语句规范 ​

1. 明确指定列名 ​

2. 合理使用LIMIT ​

3. 避免在WHERE子句中使用函数 ​

4. 合理使用JOIN ​

5. 合理使用GROUP BY和HAVING ​

INSERT语句规范 ​

1. 明确指定列名 ​

2. 批量插入 ​

3. 避免使用INSERT ... SELECT ​

UPDATE语句规范 ​

1. 明确指定WHERE条件 ​

2. 避免更新索引列 ​

3. 批量更新 ​

DELETE语句规范 ​

1. 明确指定WHERE条件 ​

2. 优先使用软删除 ​

3. 批量删除 ​

事务和锁规范 ​

1. 合理使用事务 ​

2. 避免死锁 ​

3. 合理使用锁 ​

性能优化建议 ​

1. 使用索引 ​

2. 优化子查询 ​

3. 避免使用DISTINCT ​

4. 合理使用临时表 ​

5. 优化排序操作 ​

常见问题（FAQ） ​

Q1: 为什么要避免使用SELECT *？ ​

Q2: 如何处理复杂查询？ ​

Q3: 批量插入的最佳大小是多少？ ​

Q4: 如何避免死锁？ ​

Q5: 什么时候使用软删除，什么时候使用硬删除？ ​

Q6: 如何优化子查询？ ​

Q7: 什么时候使用临时表？ ​

Q8: 如何优化排序操作？ ​

总结 ​

MariaDB SQL 书写规范

规范的重要性

命名规范

1. 数据库对象命名

2. 列命名

3. 变量命名

语法规范

1. 大小写规范

2. 缩进和换行

3. 注释规范

4. 别名规范

SELECT语句规范

1. 明确指定列名

2. 合理使用LIMIT

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

4. 合理使用JOIN

5. 合理使用GROUP BY和HAVING

INSERT语句规范

1. 明确指定列名

2. 批量插入

3. 避免使用INSERT ... SELECT

UPDATE语句规范

1. 明确指定WHERE条件

2. 避免更新索引列

3. 批量更新

DELETE语句规范

1. 明确指定WHERE条件

2. 优先使用软删除

3. 批量删除

事务和锁规范

1. 合理使用事务

2. 避免死锁

3. 合理使用锁

性能优化建议

1. 使用索引

2. 优化子查询

3. 避免使用DISTINCT

4. 合理使用临时表

5. 优化排序操作

常见问题（FAQ）

Q1: 为什么要避免使用SELECT *？

Q2: 如何处理复杂查询？

Q3: 批量插入的最佳大小是多少？

Q4: 如何避免死锁？

Q5: 什么时候使用软删除，什么时候使用硬删除？

Q6: 如何优化子查询？

Q7: 什么时候使用临时表？

Q8: 如何优化排序操作？

总结