MySQL 40条军规

1. 军规概述

1.1 什么是MySQL军规

MySQL 40条军规是经过大量实践总结出来的MySQL数据库开发、运维、管理的最佳实践规范。这些规范涵盖了DBA操作、行为规范、基本规范、库表设计、字段设计、索引设计、SQL设计等各个方面。

军规的核心价值：

避免常见错误：防止因不规范操作导致的问题
提高开发效率：统一的规范提高团队协作效率
保障系统稳定：规范的操作保障系统稳定性
提升系统性能：合理的设计提升系统性能

1.2 军规分类

MySQL 40条军规分为以下7个类别：

DBA操作规范（1-7条）：DBA日常操作规范
行为规范（8-12条）：开发和运维行为规范
基本规范（13-20条）：数据库基本使用规范
库表设计规范（21-22条）：库表设计规范
字段设计规范（23-27条）：字段设计规范
索引规范（28-34条）：索引设计和使用规范
SQL设计规范（35-40条）：SQL语句编写规范

2. DBA操作规范

2.1 军规1：业务数据修改审批流程

规范内容：

涉及业务上的修改/删除数据，在得到业务方、CTO的邮件批准后方可执行，执行前提前做好备份，必要时可逆。

重要性

原因：

数据修改/删除是不可逆操作
需要多方确认，避免误操作
备份是数据安全的最后防线

执行流程

1. 业务方提出需求
   ↓
2. 发送邮件申请（包含业务方、CTO）
   ↓
3. 获得邮件批准
   ↓
4. 执行前备份数据
   ↓
5. 执行操作
   ↓
6. 验证结果
   ↓
7. 记录操作日志

备份要求

# 执行前备份
mysqldump -uroot -pBgx123.com \
    --single-transaction \
    --master-data=1 \
    database_name table_name > backup_$(date +%F_%H%M%S).sql

# 或者使用xtrabackup
innobackupex --user=root --password=Bgx123.com /backup/

可逆操作

示例：删除数据前先备份，删除后可以恢复

-- 1. 备份数据
CREATE TABLE t1_backup AS SELECT * FROM t1 WHERE id > 1000;

-- 2. 执行删除
DELETE FROM t1 WHERE id > 1000;

-- 3. 如果需要恢复
INSERT INTO t1 SELECT * FROM t1_backup;

2.2 军规2：工单系统管理

规范内容：

所有上线需求必须走工单系统，口头通知视为无效。

重要性

原因：

工单系统有完整的记录
可以追溯和审计
避免口头通知的误解

工单内容要求

工单必须包含：

需求描述
影响范围
执行时间
回滚方案
审批流程

工单流程

1. 提交工单
   ↓
2. 技术审核
   ↓
3. 业务审核
   ↓
4. 审批通过
   ↓
5. 执行操作
   ↓
6. 验证结果
   ↓
7. 关闭工单

2.3 军规3：大表结构变更规范

规范内容：

在对大表做表结构变更时，如修改字段属性会造成锁表，并会造成从库延迟，从而影响线上业务，必须在凌晨0:00后业务低峰期执行，另统一用工具pt-online-schema-change避免锁表且降低延迟执行时间。

问题分析

传统ALTER TABLE的问题：

会锁表，影响业务
从库延迟增加
执行时间长

解决方案

使用pt-online-schema-change工具：

# pt-online-schema-change使用范例
pt-online-schema-change \
    --alter="ADD INDEX IX_id_no(id_no)" \
    --no-check-replication-filters \
    --recursion-method=none \
    --user=dba \
    --password=123456 \
    D=test,t=t1 \
    --execute

参数说明：

参数	说明
`--alter`	要执行的ALTER语句
`--no-check-replication-filters`	不检查复制过滤器
`--recursion-method`	复制检测方法
`D=test,t=t1`	数据库和表名
`--execute`	执行操作

优势：

不锁表，业务不中断
降低从库延迟
可以随时中断

MongoDB索引创建

对于MongoDB创建索引要在后台创建，避免锁表：

1 2	// MongoDB后台创建索引 db.t1.createIndex({idCardNum:1}, {background:1})

说明：

background:1：后台创建索引
不阻塞其他操作
适合生产环境

2.4 军规4：MHA高可用架构

规范内容：

所有线上业务库均必须搭建MHA高可用架构，避免单点问题。

MHA架构

MHA（Master High Availability）是MySQL高可用解决方案。

架构特点：

自动故障检测
自动主从切换
数据一致性保证
快速故障恢复

架构要求

所有线上业务库必须：

搭建主从复制
配置MHA
定期演练故障切换
监控主从状态

2.5 军规5：权限管理规范

规范内容：

给业务方开权限时，密码要用MD5加密，至少16位。权限如没有特殊要求，均为select查询权限，并做库表级限制。

密码要求

密码规范：

长度：至少16位
复杂度：包含大小写字母、数字、特殊字符
加密：使用MD5加密存储

示例：

1
2
3

-- 创建用户，密码至少16位
CREATE USER 'appuser'@'192.168.70.%' 
IDENTIFIED BY 'Complex@Password123!';

权限原则

最小权限原则：

-- 推荐：只给SELECT权限
GRANT SELECT ON app_db.* TO 'appuser'@'192.168.70.%';

-- 如果需要写权限，明确指定
GRANT SELECT, INSERT, UPDATE ON app_db.user_table 
TO 'appuser'@'192.168.70.%';

-- 库表级限制
GRANT SELECT ON app_db.table1 TO 'appuser'@'192.168.70.%';
GRANT SELECT ON app_db.table2 TO 'appuser'@'192.168.70.%';

2.6 军规6：删除默认空密码账号

规范内容：

删除默认空密码账号。

安全风险

空密码账号的风险：

任何人都可以连接
安全漏洞
不符合安全规范

执行操作

-- 删除空密码账号
DELETE FROM mysql.user WHERE user='' AND password='';

-- 刷新权限
FLUSH PRIVILEGES;

-- 验证删除
SELECT user, host FROM mysql.user WHERE user='';

2.7 军规7：审计日志功能

规范内容：

汇总库开启Audit审计日志功能，出现问题时方可追溯。

审计日志重要性

作用：

记录所有数据库操作
问题追溯
安全审计
合规要求

开启审计日志

MySQL Enterprise Audit（商业版）：

-- 安装审计插件
INSTALL PLUGIN audit_log SONAME 'audit_log.so';

-- 配置审计日志
SET GLOBAL audit_log_policy = 'ALL';

开源方案：

使用binlog记录
使用触发器记录
使用中间件记录

3. 行为规范

3.1 军规8：禁止多业务数据库混用

规范内容：

禁止一个MySQL实例存放多个业务数据库，会造成业务耦合性过高，一旦出现问题会殃及池鱼，增加了定位故障问题的难度。通常采用多实例解决，一个实例一个业务库，互不干扰。

问题分析

多业务数据库混用的问题：

业务耦合性高
故障影响范围大
难以定位问题
资源竞争

解决方案

多实例部署：

# 实例1：用户库
/data/mysql/3306/
  - my.cnf (server-id=1)
  - 数据库：user_db

# 实例2：订单库
/data/mysql/3307/
  - my.cnf (server-id=2)
  - 数据库：order_db

# 实例3：商品库
/data/mysql/3308/
  - my.cnf (server-id=3)
  - 数据库：product_db

优势：

业务隔离
独立配置
独立监控
便于管理

3.2 军规9：禁止主库执行统计查询

规范内容：

禁止在主库上执行后台管理和统计类的功能查询，这种复杂类的SQL会造成CPU的升高，进而会影响业务。

问题分析

主库执行统计查询的问题：

消耗CPU资源
影响业务性能
可能导致主从延迟

解决方案

使用从库执行统计查询：

-- 不推荐：在主库执行
SELECT COUNT(*) FROM large_table;
SELECT SUM(amount) FROM orders;

-- 推荐：在从库执行
-- 连接从库执行统计查询

或者使用专门的统计库：

从库专门用于统计
不影响主库性能
不影响业务

3.3 军规10：批量清洗数据规范

规范内容：

批量清洗数据，需要开发和DBA共同进行审查，应避开业务高峰期时段执行，并在执行过程中观察服务状态。

执行流程

1. 开发和DBA共同审查
   ↓
2. 制定执行计划
   ↓
3. 选择业务低峰期
   ↓
4. 执行前备份
   ↓
5. 执行操作
   ↓
6. 监控服务状态
   ↓
7. 验证结果

执行时间

推荐时间：

凌晨0:00-6:00
业务低峰期
避开促销活动

监控要求

执行过程中监控：

CPU使用率
内存使用率
磁盘IO
数据库连接数
慢查询数量

3.4 军规11：促销活动提前沟通

规范内容：

促销活动等应提前与DBA当面沟通，进行流量评估，比如提前一周增加机器内存或扩展架构，防止DB出现性能瓶颈。

沟通内容

提前沟通事项：

活动时间
预期流量
业务场景
性能要求

准备工作

提前一周准备：

增加机器内存
扩展架构
优化SQL
增加从库
压力测试

流量评估

评估指标：

QPS（每秒查询数）
TPS（每秒事务数）
并发连接数
数据增长量

3.5 军规12：禁止线上压力测试

规范内容：

禁止在线上做数据库压力测试。

风险分析

线上压力测试的风险：

影响正常业务
可能导致服务不可用
数据安全风险
违反规范

解决方案

使用测试环境：

搭建与生产环境相同的测试环境
在测试环境进行压力测试
验证性能后再上线

4. 基本规范

4.1 军规13：禁止存储明文密码

规范内容：

禁止在数据库中存储明文密码。

安全要求

密码存储规范：

使用加密算法（MD5、SHA256、bcrypt）
加盐（Salt）处理
定期更换密码

示例：

-- 不推荐：明文密码
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    password VARCHAR(50)  -- 明文密码
);

-- 推荐：加密密码
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    password VARCHAR(255)  -- 加密后的密码
);

4.2 军规14：使用InnoDB存储引擎

规范内容：

使用InnoDB存储引擎。支持事务，行级锁，更好的恢复性，高并发下性能更好。InnoDB表避免使用COUNT(*)操作，因内部没有计数器，需要一行一行累加计算，计数统计实时要求较强可以使用memcache或者Redis。

InnoDB优势

InnoDB特性：

事务支持：ACID特性
行级锁：并发性能好
崩溃恢复：恢复能力强
外键支持：数据完整性
高并发：性能优秀

COUNT(*)优化

问题：InnoDB的COUNT(*)需要全表扫描

解决方案：

-- 不推荐：COUNT(*)
SELECT COUNT(*) FROM large_table;

-- 推荐：使用统计表
CREATE TABLE statistics (
    table_name VARCHAR(50),
    row_count BIGINT,
    update_time DATETIME
);

-- 或者使用缓存
-- Redis: SET table_count 1000000
-- Memcached: SET table_count 1000000

4.3 军规15：统一使用UTF8字符集

规范内容：

表字符集统一使用UTF8。不会产生乱码风险。

字符集选择

推荐UTF8MB4：

-- 创建数据库
CREATE DATABASE testdb 
DEFAULT CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

-- 创建表
CREATE TABLE t1 (
    id INT PRIMARY KEY,
    name VARCHAR(50) CHARACTER SET utf8mb4
) DEFAULT CHARACTER SET utf8mb4;

UTF8 vs UTF8MB4：

UTF8：3字节，不支持emoji
UTF8MB4：4字节，支持emoji和所有Unicode字符

推荐使用UTF8MB4。

4.4 军规16：添加中文注释

规范内容：

所有表和字段都需要添加中文注释。方便他人、方便自己。

注释规范

-- 表注释
CREATE TABLE users (
    id INT PRIMARY KEY COMMENT '用户ID',
    username VARCHAR(50) COMMENT '用户名',
    email VARCHAR(100) COMMENT '邮箱地址',
    created_at DATETIME COMMENT '创建时间'
) COMMENT='用户表';

-- 查看注释
SHOW CREATE TABLE users;

注释的重要性：

便于理解业务
降低维护成本
提高开发效率

4.5 军规17：不存储大文件

规范内容：

不在数据库中存储图片、文件等大数据。图片、文件更适合于GFS分布式文件系统，数据库里存放超链接即可。

问题分析

存储大文件的问题：

数据库体积增大
查询性能下降
备份恢复困难
不符合数据库设计原则

解决方案

使用文件系统存储：

-- 不推荐：存储文件内容
CREATE TABLE files (
    id INT PRIMARY KEY,
    file_name VARCHAR(100),
    file_content BLOB  -- 不推荐
);

-- 推荐：存储文件路径
CREATE TABLE files (
    id INT PRIMARY KEY,
    file_name VARCHAR(100),
    file_path VARCHAR(255),  -- 文件路径
    file_url VARCHAR(255)   -- 文件URL
);

推荐方案：

GFS：Google文件系统
HDFS：Hadoop分布式文件系统
OSS：对象存储服务
CDN：内容分发网络

4.6 军规18：避免使用存储过程等

规范内容：

避免使用存储过程、视图、触发器、事件。MySQL是OLTP应用，最擅长简单的增、删、改、查操作，但对逻辑计算分析类的应用，并不适合，所以这部分的需求最好通过程序上实现。

原因分析

避免使用的原因：

可移植性差：不同数据库语法不同
调试困难：难以调试和测试
维护成本高：业务逻辑分散
性能考虑：可能影响性能

4.7 军规19：避免使用外键

规范内容：

避免使用外键，外键用来保护参照完整性，可在业务端实现。外键会导致父表和子表之间耦合，十分影响SQL性能，出现过多的锁等待，甚至会造成死锁。

问题分析

外键的问题：

性能影响：额外的检查开销
锁等待：可能导致死锁
耦合度高：表之间强耦合
维护困难：删除数据需要先删除关联数据

解决方案

在业务层实现：

-- 不推荐：使用外键
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 推荐：在业务层检查
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT
);
-- 在应用层检查user_id是否存在

4.8 军规20：日志表使用MongoDB

规范内容：

对事务一致性要求不高的业务，如日志表等，优先选择存入MongoDB。其自身支持的sharding分片功能，增强了横向扩展的能力，开发不用过多调整业务代码。

适用场景

MongoDB适用场景：

日志表
统计表
非关键业务数据
大数据量场景

优势

MongoDB优势：

支持sharding分片
横向扩展能力强
写入性能好
灵活的数据模型

5. 库表设计规范

5.1 军规21：表必须有主键

规范内容：

表必须有主键，例如自增主键。这样可以保证数据行是按照顺序写入，对于SAS传统机械式硬盘写入性能更好，根据主键做关联查询的性能也会更好，并且还方便了数据仓库抽取数据。从性能的角度来说，使用UUID作为主键是个最不好的方法，它会使插入变得随机。

主键的重要性

主键的作用：

唯一标识记录
提高查询性能
优化写入性能
便于数据仓库抽取

主键选择

推荐：自增主键：

-- 推荐：自增主键
CREATE TABLE t1 (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

优势：

顺序写入，性能好
索引效率高
关联查询快

不推荐：UUID主键：

-- 不推荐：UUID主键
CREATE TABLE t1 (
    id VARCHAR(36) PRIMARY KEY,  -- UUID
    name VARCHAR(50)
);

问题：

随机写入，性能差
索引效率低
占用空间大

5.2 军规22：禁止使用分区表

规范内容：

禁止使用分区表。分区表的好处是对于开发来说，不用修改代码，通过后端DB的设置，比如对于时间字段做拆分，就可以轻松实现表的拆分。但这里面涉及一个问题，查询的字段必须是分区键，否则会遍历所有的分区表，并不会带来性能上的提升。此外，分区表在物理结构上仍旧是一张表，此时我们更改表结构，一样不会带来性能上的提升。所以应采用切表的形式做拆分，如程序上需要对历史数据做查询，可通过union all的方式关联查询。

分区表的问题

分区表的限制：

查询必须包含分区键
否则遍历所有分区
表结构变更性能差
物理上仍是一张表

解决方案

使用切表方式：

-- 不推荐：分区表
CREATE TABLE orders (
    id INT PRIMARY KEY,
    order_date DATE
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2018 VALUES LESS THAN (2019),
    PARTITION p2019 VALUES LESS THAN (2020)
);

-- 推荐：切表
CREATE TABLE orders_2018 (
    id INT PRIMARY KEY,
    order_date DATE
);

CREATE TABLE orders_2019 (
    id INT PRIMARY KEY,
    order_date DATE
);

-- 查询历史数据使用UNION ALL
SELECT * FROM orders_2018
UNION ALL
SELECT * FROM orders_2019;

优势：

查询性能好
表结构变更快
便于管理
可以独立备份

6. 字段设计规范

6.1 军规23：使用DECIMAL代替FLOAT

规范内容：

用DECIMAL代替FLOAT和DOUBLE存储精确浮点数。浮点数的缺点是会引起精度问题。

精度问题演示

-- 创建测试表
mysql> CREATE TABLE t3 (c1 FLOAT(10,2), c2 DECIMAL(10,2));
Query OK, 0 rows affected (0.05 sec)

-- 插入数据
mysql> INSERT INTO t3 VALUES (999998.02, 999998.02);
Query OK, 1 row affected (0.01 sec)

-- 查询结果
mysql> SELECT * FROM t3;
+-----------+-----------+
| c1        | c2        |
+-----------+-----------+
| 999998.00 | 999998.02 |
+-----------+-----------+
1 row in set (0.00 sec)

问题：FLOAT类型精度丢失，999998.02变成了999998.00

解决方案

使用DECIMAL类型：

-- 推荐：使用DECIMAL
CREATE TABLE products (
    id INT PRIMARY KEY,
    price DECIMAL(10,2)  -- 精确到分
);

-- 不推荐：使用FLOAT
CREATE TABLE products (
    id INT PRIMARY KEY,
    price FLOAT(10,2)  -- 可能精度丢失
);

适用场景：

货币金额
精确计算
财务数据

6.2 军规24：使用TINYINT代替ENUM

规范内容：

使用TINYINT来代替ENUM类型。采用enum枚举类型，会存在扩展的问题，例如用户在线状态，如果此时增加了：5表示请勿打扰、6表示开会中、7表示隐身对好友可见，那么增加新的ENUM值要做DDL修改表结构操作了。

ENUM的问题

ENUM类型的限制：

扩展需要DDL操作
修改表结构影响业务
不够灵活

解决方案

使用TINYINT：

-- 不推荐：使用ENUM
CREATE TABLE users (
    id INT PRIMARY KEY,
    status ENUM('offline', 'online', 'away', 'busy')
);

-- 推荐：使用TINYINT
CREATE TABLE users (
    id INT PRIMARY KEY,
    status TINYINT COMMENT '0=离线,1=在线,2=离开,3=忙碌,4=隐身'
);

优势：

扩展不需要DDL
更灵活
性能相同

6.3 军规25：字段长度按需分配

规范内容：

字段长度尽量按实际需要进行分配，不要随意分配一个很大的容量。选择字段的一般原则是保小不保大，能用占用字节少的字段就不用大字段。

字段选择原则

保小不保大原则：

-- 不推荐：随意使用INT
CREATE TABLE users (
    id INT PRIMARY KEY,
    age INT  -- 年龄不会超过255，用INT浪费
);

-- 推荐：使用合适的数据类型
CREATE TABLE users (
    id INT PRIMARY KEY,
    age TINYINT UNSIGNED  -- 0-255，1字节
);

数据类型对比

类型	字节	范围	适用场景
TINYINT	1	-128~127	年龄、状态
SMALLINT	2	-32768~32767	年份、数量
MEDIUMINT	3	-8388608~8388607	ID、计数
INT	4	-2147483648~2147483647	主键、ID
BIGINT	8	很大	大ID、时间戳

INT长度说明

重要：INT(10)和INT(1)没有区别，10和1仅是显示宽度。

-- 创建测试表
CREATE TABLE test(
    id INT(10) ZEROFILL,
    id2 INT(1)
);

-- 插入数据
INSERT INTO test VALUES(1, 1);
INSERT INTO test VALUES(1000000000, 1000000000);

-- 查询结果
SELECT * FROM test;
+------------+------------+
| id         | id2        |
+------------+------------+
| 0000000001 |          1 |
| 1000000000 | 1000000000 |
+------------+------------+

说明：只有在设置了ZEROFILL时，显示宽度才有意义。

6.4 军规26：NOT NULL要提供默认值

规范内容：

字段定义为NOT NULL要提供默认值。从应用层角度来看，可以减少程序判断代码，比如你要查询一条记录，如果没默认值，你是不是得先判断该字段对应变量是否被设置，如果没有，你得通过java把该变量置为’’或者0，如果设了默认值，判断条件可直接略过。NULL值很难进行查询优化，它会使索引统计更加复杂，还需要MySQL内部进行特殊处理。

NULL值的问题

NULL值的影响：

查询优化困难
索引统计复杂
需要特殊处理
应用层判断复杂

解决方案

使用NOT NULL和默认值：

-- 不推荐：允许NULL
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),  -- 允许NULL
    age INT            -- 允许NULL
);

-- 推荐：NOT NULL + 默认值
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL DEFAULT '',
    age TINYINT UNSIGNED NOT NULL DEFAULT 0,
    status TINYINT NOT NULL DEFAULT 1
);

优势：

减少应用层判断
提高查询性能
简化索引统计

6.5 军规27：尽可能不使用TEXT/BLOB

规范内容：

尽可能不使用TEXT、BLOB类型。增加存储空间的占用，读取速度慢。

TEXT/BLOB的问题

问题：

存储空间大
读取速度慢
影响SELECT *性能
备份恢复慢

解决方案

拆分到子表：

-- 不推荐：TEXT和主表在一起
CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT  -- 不推荐
);

-- 推荐：拆分到子表
CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200)
);

CREATE TABLE article_content (
    id INT PRIMARY KEY,
    content TEXT,
    FOREIGN KEY (id) REFERENCES articles(id)
);

优势：

主表查询快
按需查询内容
便于管理

7. 索引规范

7.1 军规28：索引不是越多越好

规范内容：

索引不是越多越好，按实际需要进行创建。索引是一把双刃剑，它可以提高查询效率但也会降低插入和更新的速度并占用磁盘空间。

索引的代价

索引的缺点：

降低写入速度
占用磁盘空间
维护成本高

索引选择原则

创建索引的原则：

经常用于WHERE条件
经常用于JOIN
经常用于ORDER BY
高选择性字段

不创建索引的情况：

很少查询的字段
低选择性字段
频繁更新的字段

7.2 军规29：查询字段必须创建索引

规范内容：

查询的字段必须创建索引。如：1、SELECT、UPDATE、DELETE语句的WHERE条件列；2、多表JOIN的字段。

必须创建索引的场景

WHERE条件列：

-- 必须为id创建索引
SELECT * FROM t1 WHERE id = 100;
UPDATE t1 SET name = 'test' WHERE id = 100;
DELETE FROM t1 WHERE id = 100;

JOIN字段：

1
2
3

-- 必须为关联字段创建索引
SELECT * FROM t1 
JOIN t2 ON t1.id = t2.user_id;  -- t1.id和t2.user_id都要有索引

7.3 军规30：不在索引列进行运算

规范内容：

不在索引列进行数学运算和函数运算。无法使用索引，导致全表扫描。

问题示例

-- 不推荐：使用函数
SELECT * FROM t WHERE YEAR(d) >= 2016;
-- 即使d字段有索引，也会全表扫描

-- 推荐：直接比较
SELECT * FROM t WHERE d >= '2016-01-01';
-- 可以使用索引

常见错误

数学运算：

-- 不推荐
SELECT * FROM t WHERE id + 1 = 100;

-- 推荐
SELECT * FROM t WHERE id = 99;

函数运算：

-- 不推荐
SELECT * FROM t WHERE UPPER(name) = 'TEST';

-- 推荐
SELECT * FROM t WHERE name = 'test';

7.4 军规31：不在低基数列建立索引

规范内容：

不在低基数列上建立索引，例如’性别’。有时候，进行全表浏览要比必须读取索引和数据表更快，尤其是当索引包含的是平均分布的数据集是更是如此。

低基数列示例

低基数列：

性别：只有2个值（男、女）
状态：只有几个值
布尔值：只有2个值

解决方案

使用复合索引：

-- 不推荐：单独为性别创建索引
CREATE INDEX idx_gender ON users(gender);

-- 推荐：使用复合索引
CREATE INDEX idx_gender_age ON users(gender, age);

7.5 军规32：不使用%前导查询

规范内容：

不使用%前导的查询，如like ‘%xxx’。无法使用索引，导致全表扫描。

问题分析

-- 不推荐：%前导
SELECT * FROM t WHERE name LIKE '%de%';
-- 无法使用索引，全表扫描

-- 推荐：%后缀
SELECT * FROM t WHERE name LIKE 'de%';
-- 可以使用索引

解决方案

如果必须使用%前导：

使用全文索引（Elasticsearch）
使用搜索引擎
在应用层处理

7.6 军规33：不使用反向查询

规范内容：

不使用反向查询，如 not in / not like。无法使用索引，导致全表扫描。

问题示例

-- 不推荐：NOT IN
SELECT * FROM t WHERE id NOT IN (1,2,3);
-- 无法使用索引

-- 推荐：使用NOT EXISTS或LEFT JOIN
SELECT * FROM t1 
WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.id = t1.id);

7.7 军规34：避免冗余或重复索引

规范内容：

避免冗余或重复索引。联合索引IX_a_b_c(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)，那么索引 (a) 、(a,b) 就是多余的。

索引冗余示例

-- 不推荐：冗余索引
CREATE INDEX idx_a ON t1(a);
CREATE INDEX idx_a_b ON t1(a, b);
CREATE INDEX idx_a_b_c ON t1(a, b, c);
-- idx_a和idx_a_b是多余的

-- 推荐：只创建联合索引
CREATE INDEX idx_a_b_c ON t1(a, b, c);
-- 可以满足 (a), (a,b), (a,b,c) 的查询

检查重复索引

1 2	# 使用pt-duplicate-key-checker检查 pt-duplicate-key-checker -u root -p Bgx123.com

8. SQL设计规范

8.1 军规35：不使用SELECT *

规范内容：

不使用SELECT *，只获取必要的字段。消耗CPU和IO、消耗网络带宽；无法使用覆盖索引。

问题分析

**SELECT *的问题**：

读取不需要的字段
消耗CPU和IO
消耗网络带宽
无法使用覆盖索引

解决方案

-- 不推荐
SELECT * FROM users;

-- 推荐：只选择需要的字段
SELECT id, name, email FROM users;

覆盖索引优势：

-- 如果索引包含所有需要的字段，可以直接从索引获取数据
CREATE INDEX idx_covering ON users(id, name, email);

-- 查询可以直接使用索引，不需要回表
SELECT id, name, email FROM users WHERE id = 1;

8.2 军规36：用IN替换OR

规范内容：

用IN来替换OR。

性能对比

-- 不推荐：使用OR
SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;

-- 推荐：使用IN
SELECT * FROM t WHERE LOC_ID IN (10, 20, 30);

优势：

IN可以使用索引
OR可能无法使用索引
IN性能更好

8.3 军规37：避免数据类型不一致

规范内容：

避免数据类型不一致。

问题示例

-- 不推荐：类型不一致
SELECT * FROM t WHERE id = '19';
-- id是INT类型，'19'是字符串，需要类型转换

-- 推荐：类型一致
SELECT * FROM t WHERE id = 19;

影响：

类型转换消耗CPU
可能无法使用索引
性能下降

8.4 军规38：减少数据库交互次数

规范内容：

减少与数据库的交互次数。

批量操作

INSERT优化：

-- 不推荐：多次INSERT
INSERT INTO t (id, name) VALUES(1,'Bea');
INSERT INTO t (id, name) VALUES(2,'Belle');
INSERT INTO t (id, name) VALUES(3,'Bernice');

-- 推荐：批量INSERT
INSERT INTO t (id, name) VALUES
    (1,'Bea'),
    (2,'Belle'),
    (3,'Bernice');

UPDATE优化：

1 2	-- 推荐：批量UPDATE UPDATE t SET status = 1 WHERE id IN (1,2,3,4);

ALTER优化：

-- 推荐：一次ALTER多个操作
ALTER TABLE tbl_name 
    ADD COLUMN col1 INT,
    ADD COLUMN col2 VARCHAR(50);

8.5 军规39：拒绝大SQL，拆分成小SQL

规范内容：

拒绝大SQL，拆分成小SQL。

问题分析

大SQL的问题：

执行时间长
锁表时间长
难以优化
影响其他查询

解决方案

-- 不推荐：大SQL
SELECT * FROM tag
JOIN tag_post ON tag_post.tag_id = tag.id
JOIN post ON tag_post.post_id = post.id
WHERE tag.tag = 'mysql';

-- 推荐：拆分成小SQL
-- 第一步
SELECT * FROM tag WHERE tag = 'mysql';
-- 结果：tag_id = 1234

-- 第二步
SELECT * FROM tag_post WHERE tag_id = 1234;
-- 结果：post_id in (123, 456, 567, 9098, 8904)

-- 第三步
SELECT * FROM post WHERE post_id IN (123, 456, 567, 9098, 8904);

优势：

执行时间短
便于优化
减少锁表时间
提高并发性能

8.6 军规40：禁止使用ORDER BY RAND()

规范内容：

禁止使用order by rand()。

问题分析

ORDER BY RAND()的问题：

性能极差
需要全表扫描
无法使用索引
数据量大时几乎不可用

解决方案

-- 不推荐：ORDER BY RAND()
SELECT * FROM t1 WHERE 1=1 ORDER BY RAND() LIMIT 4;

-- 推荐：使用随机数
SELECT * FROM t1 
WHERE id >= CEIL(RAND() * 1000) 
LIMIT 4;

-- 或者：应用层随机选择
-- 1. 查询总数
SELECT COUNT(*) FROM t1;
-- 2. 应用层生成随机ID
-- 3. 查询随机记录
SELECT * FROM t1 WHERE id IN (随机ID列表);

9. 军规总结

9.1 军规分类统计

类别	数量	军规编号
DBA操作规范	7条	1-7
行为规范	5条	8-12
基本规范	8条	13-20
库表设计规范	2条	21-22
字段设计规范	5条	23-27
索引规范	7条	28-34
SQL设计规范	6条	35-40

9.2 核心原则

安全第一：所有操作都要有备份和审批
性能优先：设计时考虑性能影响
规范统一：团队统一规范，提高效率
持续优化：根据实际情况持续优化

9.3 架构师建议

建立规范：制定团队开发规范
代码审查：定期进行代码审查
培训教育：定期培训开发人员
工具支持：使用工具检查规范
持续改进：根据实践持续改进规范

9.4 检查清单

所有操作是否有审批和备份
是否使用工单系统
大表变更是否使用pt工具
是否搭建MHA高可用
用户权限是否最小化
是否使用InnoDB引擎
表是否有主键
字段是否使用合适的数据类型
索引是否合理
SQL是否优化

相关文章：

第438集MySQL40条军规

MySQL 40条军规

1. 军规概述

1.1 什么是MySQL军规

1.2 军规分类

2. DBA操作规范

2.1 军规1：业务数据修改审批流程

重要性

执行流程

备份要求

可逆操作

2.2 军规2：工单系统管理

重要性

工单内容要求

工单流程

2.3 军规3：大表结构变更规范

问题分析

解决方案

MongoDB索引创建

2.4 军规4：MHA高可用架构

MHA架构

架构要求

2.5 军规5：权限管理规范

密码要求

权限原则

2.6 军规6：删除默认空密码账号

安全风险

执行操作

2.7 军规7：审计日志功能

审计日志重要性

开启审计日志

3. 行为规范

3.1 军规8：禁止多业务数据库混用

问题分析

解决方案

3.2 军规9：禁止主库执行统计查询

问题分析

解决方案

3.3 军规10：批量清洗数据规范

执行流程

执行时间

监控要求

3.4 军规11：促销活动提前沟通

沟通内容

准备工作

流量评估

3.5 军规12：禁止线上压力测试

风险分析

解决方案

4. 基本规范

4.1 军规13：禁止存储明文密码

安全要求

4.2 军规14：使用InnoDB存储引擎

InnoDB优势

COUNT(*)优化

4.3 军规15：统一使用UTF8字符集

字符集选择

4.4 军规16：添加中文注释

注释规范

4.5 军规17：不存储大文件

问题分析

解决方案

4.6 军规18：避免使用存储过程等

原因分析

推荐方案

4.7 军规19：避免使用外键

问题分析

解决方案

4.8 军规20：日志表使用MongoDB

适用场景

优势

5. 库表设计规范

5.1 军规21：表必须有主键

主键的重要性

主键选择

5.2 军规22：禁止使用分区表

分区表的问题

解决方案

6. 字段设计规范

6.1 军规23：使用DECIMAL代替FLOAT