一、SQL基础规范与标准化

1.1 命名规范体系

表命名需遵循业务模块_数据类型结构，例如user_profile表示用户画像表。字段命名采用小写驼峰式，如create_time而非CreateTime。临时表需添加tmp_前缀，视图使用v_前缀，确保代码可读性。

索引命名规则为idx_表名_字段名，唯一索引使用uk_前缀。存储过程与函数命名需体现业务逻辑，例如sp_calculate_user_score。所有数据库对象注释必须包含创建人、创建时间及业务说明，示例如下：

CREATE TABLE order_detail (
    order_id BIGINT COMMENT '订单ID，关联order_main表',
    product_id VARCHAR(32) COMMENT '商品唯一标识',
    quantity INT DEFAULT 0 COMMENT '购买数量',
    PRIMARY KEY (order_id, product_id),
    INDEX idx_order_product (product_id)
) ENGINE=InnoDB COMMENT='订单明细表，存储商品级订单信息';

1.2 SQL语句编写准则

SELECT子句优化：禁止使用SELECT *，明确指定所需字段。多表关联时，字段前需添加表别名避免歧义
WHERE条件设计：数值类型字段禁止使用字符串比较，日期范围查询需包含边界值
JOIN操作规范：小表驱动大表，优先使用INNER JOIN。LEFT JOIN需确保右表可能为NULL的情况已处理
分页查询优化：大数据量分页必须使用WHERE id > ? LIMIT n替代LIMIT m,n

二、查询性能优化策略

2.1 执行计划分析

通过EXPLAIN命令获取查询执行路径，重点关注以下指标：

type列：理想值应为const、eq_ref或ref，出现ALL需立即优化
key列：确认是否使用预期索引
rows列：预估扫描行数，超过1000需警惕
Extra列：避免出现Using filesort和Using temporary

2.2 索引优化方案

2.2.1 索引选择原则

高选择性字段优先建索引（如用户ID）

复合索引遵循最左前缀原则，示例：

-- 复合索引(a,b,c)可支持：
WHERE a=1
WHERE a=1 AND b=2
WHERE a=1 AND b=2 AND c=3
-- 但不支持：
WHERE b=2
WHERE c=3

2.2.2 索引维护策略

定期分析表统计信息：ANALYZE TABLE table_name
监控慢查询日志，对高频低效查询建立专用索引
避免过度索引，每个表的索引数量建议不超过5个

2.3 查询重写技巧

2.3.1 子查询优化

将IN子查询改为JOIN操作：

-- 优化前
SELECT * FROM products 
WHERE category_id IN (SELECT id FROM categories WHERE parent_id=10);
-- 优化后
SELECT p.* FROM products p
JOIN categories c ON p.category_id = c.id
WHERE c.parent_id = 10;

2.3.2 大表更新策略

分批更新避免锁表：

-- 每次处理1000条
UPDATE large_table SET status=1 
WHERE id IN (
    SELECT id FROM (
        SELECT id FROM large_table 
        WHERE status=0 LIMIT 1000
    ) tmp
);

三、事务与并发控制

3.1 事务设计原则

事务粒度控制：单个事务操作行数建议不超过100行
事务隔离级别选择：读已提交（RC）为默认级别，特殊场景可使用可重复读（RR）
避免长事务：事务执行时间超过10秒需拆分

3.2 死锁处理机制

3.2.1 死锁预防策略

固定访问顺序：所有事务按相同顺序访问表
减少事务持有锁时间：尽早提交或回滚
合理设置锁等待超时：innodb_lock_wait_timeout=50

3.2.2 死锁分析流程

通过SHOW ENGINE INNODB STATUS获取死锁日志
识别死锁参与事务及持有资源
调整SQL执行顺序或添加适当索引

四、安全与权限管理

4.1 最小权限原则

数据库用户仅授予必要权限
禁止使用root账户进行业务操作

应用程序连接使用专用账户，权限示例：

GRANT SELECT, INSERT, UPDATE ON db_name.table_name TO 'app_user'@'%';

4.2 数据脱敏方案

敏感字段存储加密：使用AES_ENCRYPT函数

查询结果脱敏处理：

SELECT 
    user_id,
    CONCAT(LEFT(phone,3),'****',RIGHT(phone,4)) AS phone,
    MD5(id_card) AS id_card_hash
FROM user_info;

4.3 审计与监控

开启通用查询日志：general_log=1
设置慢查询阈值：long_query_time=2
定期审查权限分配：SELECT * FROM mysql.user

五、高级特性应用

5.1 分区表实践

按时间范围分区示例：

CREATE TABLE sales_data (
    id BIGINT AUTO_INCREMENT,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

5.2 JSON字段处理

MySQL 5.7+支持JSON数据类型：

-- 插入JSON数据
INSERT INTO user_settings (user_id, settings) 
VALUES (1001, '{"theme": "dark", "fontSize": 14}');
-- 查询JSON字段
SELECT user_id, settings->>'$.theme' AS theme 
FROM user_settings;
-- 更新JSON字段
UPDATE user_settings 
SET settings = JSON_SET(settings, '$.fontSize', 16) 
WHERE user_id = 1001;

本手册系统梳理了企业级SQL开发的核心要点，通过标准化规范、性能优化技巧、安全控制机制及高级特性应用四大模块，构建了完整的SQL开发知识体系。建议数据库开发团队以此为基准建立内部规范，定期组织技术分享与代码审查，持续提升数据操作效率与系统稳定性。

企业内部SQL使用最佳实践指南