mysql数据库规范
大家好，今天我们来聊一聊MySQL数据库规范，MySQL是一个广泛使用的开源关系型数据库管理系统，良好的规范可以提高数据库的性能、可靠性和可维护性。下面是一些MySQL数据库规范的重要指南，还附了一些索引失效的常见情况和关键字列表，希望对大家有所帮助。
一、数据库规范
所有规范会按照【高危】、【强制】、【建议】三个级别进行标注，遵守优先级从高到低。
1 设计规范
1.1 建库规范
- 【强制】：库的名称必须控制在32个字符以内
- 【建议】：库的名称格式：
业务系统名称_子系统(模块)名 - 【建议】：一般分库名称命名格式是
库通配名_编号或库通配名_时间 - 【建议】：创建数据库时可显式指定字符集，只能使用 utf8 或 utf8mb4 格式，数据库默认字符集为 utf8
1.2 建表规范
1.2.1 整体结构限制
- 【强制】：表名称必须控制在32个字符以内，表名只能使用字母、数字和下划线
- 【建议】：表名建议全部使用小写字符
- 【建议】：单独同一模块的表名建议使用相同字符作为前缀区分
- 【强制】：不允许创建分区表
- 【强制】：无特殊需求，所有表的存储引擎默认都为 InnoDB。如果需要使用其他存储引擎，必须通过开发负责人和DBA审核之后才可以使用
- 【建议】：创建表时可显式指定字符集，只能使用 utf8 或 utf8mb4格式，表默认字符集为 utf8
- 【强制】：建表必须有 comment 注释信息
- 【建议】：使用临时表或中间表时，建议以
tmp_、bak_、日期等作为表名前缀或后缀区分
1.2.2 列限制
- 【强制】：建表SQL列名不能设置为 MySQL关键字
- 【建议】：建表SQL列名使用小写字符
- 【强制】：建表SQL中自增列必须设置为
int或bigint类型，并且只能有一列自增列 - 【建议】：自增列初始值建议设置为1
- 【建议】：对核心表及配置记录表等建议设置数据创建时间、更新时间字段
- 【建议】：表中所有字段都需要设置默认值，并不能使用 NULL值，避免出现聚合计算偏差
- 【建议】：不推荐使用
enum、set类型，不利于后续枚举值变更，推荐使用tinyint或int类型 - 【强制】：所有字段都必须设置 comment 注释信息
1.2.3 索引限制
- 【强制】：建表SQL必须有且只有一个主键，类型为
int或bigint，主键最多只能指定一个字段 - 【建议】：表主键建议设置为自增，避免随机主键写入引起的性能下降
- 【强制】：不允许创建外键，同步更新需通过业务逻辑程序实现
- 【建议】：单个索引中每个索引记录的长度不能超过64KB
- 【强制】：唯一索引以
uk_或uq_开头，普通索引以idx_开头，以字段名称或缩写作为后缀 - 【建议】：单个表上的索引个数不能超过 5 个
- 【建议】：在建立索引时，多考虑建立联合索引，并把区分度最高的字段放在最前面
- 【建议】：在多表 join 的 SQL 里，保证被驱动表的连接列上有索引，这样 join 执行效率最高
- 【建议】：建表时，保证表里互相不存在冗余索引
1.3 改表规范
1.3.1 整体结构限制
- 【强制】：禁止修改表的存储引擎类型
- 【强制】：同个表的改表语句需合并在一条 SQL 中，避免多次改表影响性能
- 【高危】：禁止提交 DROP、TRUNCATE、RENAME 等高危工单
- 【建议】：对于超过 100W 的表结构修改，尽量发起定时执行工单，在低峰期处理
1.3.2 列限制
- 【强制】：禁止添加或修改字段名为 MySQL关键字
- 【建议】：建议添加或修改字段名称全部使用小写字符
- 【强制】：禁止删除字段
- 【强制】：禁止缩短字段长度
- 【强制】：禁止修改字段类型，比如
int转为varchar类型等 - 【强制】：新增自增字段时，类型必须为
int或bigint
1.3.3 索引限制
- 【建议】：单个索引中每个索引记录的长度不能超过64KB
- 【强制】：唯一索引以
uk_或uq_开头，普通索引以idx_开头，以字段名称或缩写作为后缀 - 【建议】：单个表上的索引个数不能超过 5 个
- 【建议】：在建立索引时，多考虑建立联合索引，并把区分度最高的字段放在最前面
- 【建议】：在多表 join 的SQL里，保证被驱动表的连接列上有索引，这样 join 执行效率最高
- 【建议】：添加或删除索引时，保证表里互相不存在冗余索引
2 使用规范
2.1 DML语句
2.1.1 insert语句
- 【强制】：insert 语句指定具体字段名称
- 【强制】：insert 语句中禁止使用
select *语法 - 【强制】：insert 语句中使用select语法时必须指定 where 条件
- 【建议】：
insert into…values(XX),(XX),(XX)…。这里 XX 的值不要超过 5000 个，避免引起数据库主从延迟 - 【建议】：对于有
auto_increment属性字段的表的插入操作，并发需要控制在 200 以内
2.1.2 delete语句
- 【强制】：delete 删除数据时需指定 where 条件
- 【建议】：删除数据时尽量使用主键或其他索引列作为限制条件，避免全表扫描
- 【高危】：禁用
delete t1 … where a=XX limit XX;这种带 limit 的删除语句。因为会导致主从不一致，导致数据错乱 - 【建议】：批量操作数据时，需要控制事务处理间隔时间，进行必要的 sleep，一般建议值 5-10 秒
- 【建议】：大批量数据删除时建议提交数据归档工单申请
2.1.3 update语句
- 【强制】：update 更新数据时需指定 where 条件
- 【建议】：更新数据时尽量使用主键或其他索引列作为限制条件，避免全表扫描
- 【高危】：禁用
update t1 … where a=XX limit XX;这种带 limit 的更新语句。因为会导致主从不一致，导致数据错乱 - 【高危】：update 禁止使用关联子查询，如
update t1 set … where name in(select name from user where…);效率极其低下 - 【强制】：禁止在业务的更新类 SQL 语句中使用 join
2.2 查询语句
- 【强制】：select 语句必须指定具体字段名称，禁止写为
select * - 【建议】：select 查询时建议使用索引字段作为限制条件，并且查询数据量不要超过全表的25%，确保查询使用到合适索引
- 【强制】：where 条件里等号左右字段类型必须一致，否则无法利用索引
- 【强制】：WHERE 子句中禁止只使用全模糊的 LIKE 条件进行查找，必须有其他等值或范围查询条件，否则无法利用索引
- 【建议】：索引列不要使用函数或表达式，否则无法利用索引
- 【建议】：in 值列表限制在 500 以内，可以减少底层扫描，减轻数据库压力从而加速查询
- 【建议】：select语句尽量使用 union all 代替 union，并且关联子句个数限制在 5 个以内。因为 union all 不需要去重，节省数据库资源，提高性能
- 【建议】：减少使用 or 语句，可将 or 语句优化为 union，然后在各个 where 条件上建立索引
- 【建议】：分页查询，当 limit 起点较高时，可先用过滤条件进行过滤
- 【强制】：禁止跨 db 的 join 语句
- 【建议】：不建议使用子查询，建议将子查询 SQL 拆开结合程序多次查询，或使用 join 来代替子查询
- 【建议】：线上环境，多表 join 不要超过 3 个表
- 【建议】：多表连接查询推荐使用别名，且 SELECT 列表中要用别名引用字段，数据库.表格式
- 【建议】：在多表 join 中，尽量选取结果集较小的表作为驱动表，来 join 其他表
- 【建议】：减少使用
order by，尽量在业务程序上排序，减少对机器 CPU 的性能损耗 - 【建议】：
order by、group by、distinct这些 SQL 尽量利用索引直接检索出排序好的数据。如where a=1 order by b可以利用key(a,b)。 - 【建议】：包含了
order by、group by、distinct这些查询的语句，where 条件过滤出来的结果集请保持在 1000 行以内，否则 SQL 会很慢。
附录
附录1：MySQL索引失效的常见情况
- 最左前缀原则。例如：存在联合索引
idx_a_b(a, b)，查询条件使用where b = 1则无法使用索引 - LIKE 前置模糊查询。例如：
col_name like '%test'或col_name like '%test%' - 索引列使用函数或存在计算。例如：存在索引
idx_col(col)，查询条件使用where left(col, 2) = 'te' - 查询条件使用
is not null。设计表结构时尽量设置not null约束 - 字段类型出现隐式转换。例如：存在字段
test_col为varchar类型，查询时使用了where test_col = 1，隐式转为了int类型导致索引失效 - 条件中有 or 存在可能不会使用索引。例如：查询条件为
where a = 'testa' or b = 'testb'，存在索引idx_a(a)，此时也不会使用索引，除非为b字段也添加索引 - 查询结果超过整体结果的25%或三分之一，或者表数据量比较少时，MySQL认为全表扫描代价更小，会导致索引失效
附录2：MySQL关键字列表
R 表示为 MySQL 预留关键字
关键字 | 关键字 | 关键字 |
|---|---|---|
ACCESSIBLE（R） | ACCOUNT | ACTION |
ADD（R） | AFTER | AGAINST |
AGGREGATE | ALGORITHM | ALL（R） |
ALTER（R） | ALWAYS | ANALYSE |
ANALYZE（R） | AND（R） | ANY |
AS（R） | ASC（R） | ASCII |
ASENSITIVE（R） | AT | AUTOEXTEND_SIZE |
AUTO_INCREMENT | AVG | AVG_ROW_LENGTH |
BACKUP | BEFORE（R） | BEGIN |
BETWEEN（R） | BIGINT（R） | BINARY（R） |
BINLOG | BIT | BLOB（R） |
BLOCK | BOOL | BOOLEAN |
BOTH（R） | BTREE | BY（R） |
BYTE | CACHE | CALL (R) |
CASCADE (R) | CASCADED | CASE (R) |
CATALOG_NAME | CHAIN | CHANGE (R) |
CHANGED | CHANNEL | CHAR (R) |
CHARACTER (R) | CHARSET | CHECK (R) |
CHECKSUM | CIPHER | CLASS_ORIGIN |
CLIENT | CLOSE | COALESCE |
CODE | COLLATE (R) | COLLATION |
COLUMN (R) | COLUMNS | COLUMN_FORMAT |
COLUMN_NAME | COMMENT | COMMIT |
COMMITTED | COMPACT | COMPLETION |
COMPRESSED | COMPRESSION | CONCURRENT |
CONDITION (R) | CONNECTION | CONSISTENT |
CONSTRAINT (R) | CONSTRAINT_CATALOG | CONSTRAINT_NAME |
CONSTRAINT_SCHEMA | CONTAINS | CONTEXT |
CONTINUE (R) | CONVERT (R) | CPU |
CREATE (R) | CROSS (R) | CUBE |
CURRENT | CURRENT_DATE (R) | CURRENT_TIME (R) |
CURRENT_TIMESTAMP (R) | CURRENT_USER (R) | CURSOR (R) |
CURSOR_NAME | DATA | DATABASE (R) |
DATABASES (R) | DATAFILE | DATE |
DATETIME | DAY | DAY_HOUR (R) |
DAY_MICROSECOND (R) | DAY_MINUTE (R) | DAY_SECOND (R) |
DEALLOCATE | DEC (R) | DECIMAL (R) |
DECLARE (R) | DEFAULT (R) | DEFAULT_AUTH |
DEFINER | DELAYED (R) | DELAY_KEY_WRITE |
DELETE (R) | DESC (R) | DESCRIBE (R) |
DES_KEY_FILE | DETERMINISTIC (R) | DIAGNOSTICS |
DIRECTORY | DISABLE | DISCARD |
DISK | DISTINCT (R) | DISTINCTROW (R) |
DIV (R) | DO | DOUBLE (R) |
DROP (R) | DUAL (R) | DUMPFILE |
DUPLICATE | DYNAMIC | EACH (R) |
ELSE (R) | ELSEIF (R) | ENABLE |
ENCLOSED (R) | ENCRYPTION | END |
ENDS | ENGINE | ENGINES |
ENUM | ERROR | ERRORS |
ESCAPE | ESCAPED (R) | EVENT |
EVENTS | EVERY | EXCHANGE |
EXECUTE | EXISTS (R) | EXIT (R) |
EXPANSION | EXPIRE | EXPLAIN (R) |
EXPORT | EXTENDED | EXTENT_SIZE |
FALSE (R) | FAST | FAULTS |
FETCH (R) | FIELDS | FILE |
FILE_BLOCK_SIZE | FILTER | FIRST |
FIXED | FLOAT（R） | FLOAT4（R） |
FLOAT8（R） | FLUSH | FOLLOWS |
FOR（R） | FORCE（R） | FOREIGN（R） |
FORMAT | FOUND | FROM（R） |
FULL | FULLTEXT（R） | FUNCTION |
GENERAL | GENERATED（R） | GEOMETRY |
GEOMETRYCOLLECTION | GET（R） | GET_FORMAT |
GLOBAL | GRANT（R） | GRANTS |
GROUP（R） | GROUP_REPLICATION | HANDLER |
HASH | HAVING（R） | HELP |
HIGH_PRIORITY（R） | HOST | HOSTS |
HOUR | HOUR_MICROSECOND（R） | HOUR_MINUTE（R） |
HOUR_SECOND（R） | IDENTIFIED | IF（R） |
IGNORE（R） | IGNORE_SERVER_IDS | IMPORT |
IN（R） | INDEX（R） | INDEXES |
INFILE（R） | INITIAL_SIZE | INNER（R） |
INOUT（R） | INSENSITIVE（R） | INSERT（R） |
INSERT_METHOD | INSTALL | INSTANCE |
INT（R） | INT1（R） | INT2（R） |
INT3（R） | INT4（R） | INT8（R） |
INTEGER（R） | INTERVAL（R） | INTO（R） |
INVOKER | IO | IO_AFTER_GTIDS（R） |
IO_BEFORE_GTIDS（R） | IO_THREAD | IPC |
IS（R） | ISOLATION | ISSUER |
ITERATE（R） | JOIN（R） | JSON |
KEY（R） | KEYS（R） | KEY_BLOCK_SIZE |
KILL（R） | LANGUAGE | LAST |
LEADING（R） | LEAVE (R) | LEAVES |
LEFT (R) | LESS | LEVEL |
LIKE (R) | LIMIT (R) | LINEAR（R） |
LINES（R） | LINESTRING | LIST |
LOAD（R） | LOCAL | LOCALTIME（R） |
LOCALTIMESTAMP（R） | LOCK（R） | LOCKS |
LOGFILE | LOGS | LONG（R） |
LONGBLOB（R） | LONGTEXT（R） | LOOP（R） |
LOW_PRIORITY（R） | MASTER | MASTER_AUTO_POSITION |
MASTER_BIND（R） | MASTER_CONNECT_RETRY | MASTER_DELAY |
MASTER_HEARTBEAT_PERIOD | MASTER_HOST | MASTER_LOG_FILE |
MASTER_LOG_POS | MASTER_PASSWORD | MASTER_PORT |
MASTER_RETRY_COUNT | MASTER_SERVER_ID | MASTER_SSL |
MASTER_SSL_CA | MASTER_SSL_CAPATH | MASTER_SSL_CERT |
MASTER_SSL_CIPHER | MASTER_SSL_CRL | MASTER_SSL_CRLPATH |
MASTER_SSL_KEY | MASTER_SSL_VERIFY_SERVER_CERT（R） | MASTER_TLS_VERSION |
MASTER_USER | MATCH（R） | MAXVALUE（R） |
MAX_CONNECTIONS_PER_HOUR | MAX_QUERIES_PER_HOUR | MAX_ROWS |
MAX_SIZE | MAX_STATEMENT_TIME | MAX_UPDATES_PER_HOUR |
MAX_USER_CONNECTIONS | MEDIUM | MEDIUMBLOB（R） |
MEDIUMINT（R） | MEDIUMTEXT（R） | MEMORY |
MERGE | MESSAGE_TEXT | MICROSECOND |
MIDDLEINT（R） | MIGRATE | MINUTE |
MINUTE_MICROSECOND（R） | MINUTE_SECOND（R） | MIN_ROWS |
MOD（R） | MODE | MODIFIES（R） |
MODIFY | MONTH | MULTILINESTRING |
MULTIPOINT | MULTIPOLYGON | MUTEX |
MYSQL_ERRNO | NAME | NAMES |
NATIONAL | NATURAL（R） | NCHAR |
NDB | NDBCLUSTER | NEVER |
NEW | NEXT | NO |
NODEGROUP | NONBLOCKING | NONE |
NOT（R） | NO_WAIT | NO_WRITE_TO_BINLOG（R） |
NULL（R） | NUMBER | NUMERIC（R） |
NVARCHAR | OFFSET | OLD_PASSWORD |
ON（R） | ONE | ONLY |
OPEN | OPTIMIZE（R） | OPTIMIZER_COSTS（R） |
OPTION（R） | OPTIONALLY（R） | OPTIONS |
OR（R） | ORDER（R） | OUT（R） |
OUTER（R） | OUTFILE（R） | OWNER |
PACK_KEYS | PAGE | PARSER |
PARSE_GCOL_EXPR | PARTIAL | PARTITION（R） |
PARTITIONING | PARTITIONS | PASSWORD |
PHASE | PLUGIN | PLUGINS |
PLUGIN_DIR | POINT | POLYGON |
PORT | PRECEDES | PRECISION（R） |
PREPARE | PRESERVE | PREV |
PRIMARY（R） | PRIVILEGES | PROCEDURE（R） |
PROCESSLIST | PROFILE | PROFILES |
PROXY | PURGE（R） | QUARTER |
QUERY | QUICK | RANGE（R） |
READ（R） | READS（R） | READ_ONLY |
READ_WRITE（R） | REAL（R） | REBUILD |
RECOVER | REDOFILE | REDO_BUFFER_SIZE |
REDUNDANT | REFERENCES（R） | REGEXP（R） |
RELAY | RELAYLOG | RELAY_LOG_FILE |
RELAY_LOG_POS | RELAY_THREAD | RELEASE（R） |
RELOAD | REMOVE | RENAME（R） |
REORGANIZE | REPAIR | REPEAT（R） |
REPEATABLE | REPLACE（R） | REPLICATE_DO_DB |
REPLICATE_DO_TABLE | REPLICATE_IGNORE_DB | REPLICATE_IGNORE_TABLE |
REPLICATE_REWRITE_DB | REPLICATE_WILD_DO_TABLE | REPLICATE_WILD_IGNORE_TABLE |
REPLICATION | REQUIRE（R） | RESET |
RESIGNAL（R） | RESTORE | RESTRICT（R） |
RESUME | RETURN（R） | RETURNED_SQLSTATE |
RETURNS | REVERSE | REVOKE（R） |
RIGHT（R） | RLIKE（R） | ROLLBACK |
ROLLUP | ROTATE | ROUTINE |
ROW | ROWS | ROW_COUNT |
ROW_FORMAT | RTREE | SAVEPOINT |
SCHEDULE | SCHEMA（R） | SCHEMAS（R） |
SCHEMA_NAME | SECOND | SECOND_MICROSECOND（R） |
SECURITY | SELECT（R） | SENSITIVE（R） |
SEPARATOR（R） | SERIAL | SERIALIZABLE |
SERVER | SESSION | SET（R） |
SHARE | SHOW（R） | SHUTDOWN |
SIGNAL（R） | SIGNED | SIMPLE |
SLAVE | SLOW | SMALLINT（R） |
SNAPSHOT | SOCKET | SOME |
SONAME | SOUNDS | SOURCE |
SPATIAL (R) | SPECIFIC (R) | SQL (R) |
SQLEXCEPTION (R) | SQLSTATE (R) | SQLWARNING (R) |
SQL_AFTER_GTIDS | SQL_AFTER_MTS_GAPS | SQL_BEFORE_GTIDS |
SQL_BIG_RESULT（R） | SQL_BUFFER_RESULT | SQL_CACHE |
SQL_CALC_FOUND_ROWS（R） | SQL_NO_CACHE | SQL_SMALL_RESULT（R） |
SQL_THREAD | SQL_TSI_DAY | SQL_TSI_HOUR |
SQL_TSI_MINUTE | SQL_TSI_MONTH | SQL_TSI_QUARTER |
SQL_TSI_SECOND | SQL_TSI_WEEK | SQL_TSI_YEAR |
SSL（R） | STACKED | START |
STARTING（R） | STARTS | STATS_AUTO_RECALC |
STATS_PERSISTENT | STATS_SAMPLE_PAGES | STATUS |
STOP | STORAGE | STORED（R） |
STRAIGHT_JOIN（R） | STRING | SUBCLASS_ORIGIN |
SUBJECT | SUBPARTITION | SUBPARTITIONS |
SUPER | SUSPEND | SWAPS |
SWITCHES | TABLE（R） | TABLES |
TABLESPACE | TABLE_CHECKSUM | TABLE_NAME |
TEMPORARY | TEMPTABLE | TERMINATED（R） |
TEXT | THAN | THEN（R） |
TIME | TIMESTAMP | TIMESTAMPADD |
TIMESTAMPDIFF | TINYBLOB（R） | TINYINT（R） |
TINYTEXT（R） | TO（R） | TRAILING（R） |
TRANSACTION | TRIGGER (R) | TRIGGERS |
TRUE (R) | TRUNCATE | TYPE |
TYPES | UNCOMMITTED | UNDEFINED |
UNDO（R） | UNDOFILE | UNDO_BUFFER_SIZE |
UNICODE | UNINSTALL | UNION（R） |
UNIQUE（R） | UNKNOWN | UNLOCK（R） |
UNSIGNED（R） | UNTIL | UPDATE（R） |
UPGRADE | USAGE（R） | USE（R） |
USER | USER_RESOURCES | USE_FRM |
USING（R） | UTC_DATE（R） | UTC_TIME（R） |
UTC_TIMESTAMP（R） | VALIDATION | VALUE |
VALUES（R） | VARBINARY（R） | VARCHAR（R） |
VARCHARACTER（R） | VARIABLES | VARYING（R） |
VIEW | VIRTUAL（R） | WAIT |
WARNINGS | WEEK | WEIGHT_STRING |
WHEN（R） | WHERE（R） | WHILE（R） |
WITH（R） | WITHOUT | WORK |
WRAPPER | WRITE（R） | X509 |
XA | XID | XML |
XOR（R） | YEAR | YEAR_MONTH（R） |
ZEROFILL（R） |
腾讯云开发者
