mysql数据库规范及关键字

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 所有规范会按照【高危】、【强制】、【建议】三个级别进行标注,遵守优先级从高到底。

一、数据库规范

所有规范会按照【高危】、【强制】、【建议】三个级别进行标注,遵守优先级从高到底。

1 设计规范

1.1 建库规范

  1. 【强制】:库的名称必须控制在32个字符以内

  2. 【建议】:库的名称格式:业务系统名称_子系统(模块)名

  3. 【建议】:一般分库名称命名格式是 库通配名_编号库通配名_时间

  4. 【建议】:创建数据库时可显式指定字符集,只能使用 utf8 或 utf8mb4 格式,数据库默认字符集为 utf8

1.2 建表规范

1.2.1 整体结构限制
  1. 【强制】:表名称必须控制在32个字符以内,表名只能使用字母、数字和下划线
  2. 【建议】:表名建议全部使用小写字符
  3. 【建议】:单独同一模块的表名建议使用相同字符作为前缀区分
  4. 【强制】:不允许创建分区表
  5. 【强制】:无特殊需求,所有表的存储引擎默认都为 InnoDB。如果需要使用其他存储引擎,必须通过开发负责人和DBA审核之后才可以使用
  6. 【建议】:创建表时可显式指定字符集,只能使用 utf8 或 utf8mb4格式,表默认字符集为 utf8
  7. 【强制】:建表必须有 comment 注释信息
  8. 【建议】:使用临时表或中间表时,建议以tmp_bak_、日期等作为表名前缀或后缀区分
1.2.2 列限制
  1. 【强制】:建表SQL列名不能设置为 MySQL关键字
  2. 【建议】:建表SQL列名使用小写字符
  3. 【强制】:建表SQL中自增列必须设置为intbigint类型,并且只能有一列自增列
  4. 【建议】:自增列初始值建议设置为1
  5. 【建议】:对核心表及配置记录表等建议设置数据创建时间、更新时间字段
  6. 【建议】:表中所有字段都需要设置默认值,并不能使用 NULL值,避免出现聚合计算偏差
  7. 【建议】:不推荐使用 enumset类型,不利于后续枚举值变更,推荐使用tinyintint类型
  8. 【强制】:所有字段都必须设置 comment 注释信息
1.2.3 索引限制
  1. 【强制】:建表SQL必须有且只有一个主键,类型为intbigint,主键最多只能指定一个字段

  2. 【建议】:表主键建议设置为自增,避免随机主键写入引起的性能下降

  3. 【强制】:不允许创建外键,同步更新需通过业务逻辑程序实现

  4. 【建议】:单个索引中每个索引记录的长度不能超过64KB

  5. 【强制】:唯一索引以uk_uq_开头,普通索引以idx_开头,以字段名称或缩写作为后缀

  6. 【建议】:单个表上的索引个数不能超过 5 个

  7. 【建议】:在建立索引时,多考虑建立联合索引,并把区分度最高的字段放在最前面

  8. 【建议】:在多表 join 的 SQL 里,保证被驱动表的连接列上有索引,这样 join 执行效率最高

  9. 【建议】:建表时,保证表里互相不存在冗余索引

1.3 改表规范

1.3.1 整体结构限制
  1. 【强制】:禁止修改表的存储引擎类型

  2. 【强制】:同个表的改表语句需合并在一条 SQL 中,避免多次改表影响性能

  3. 【高危】:禁止提交 DROP、TRUNCATE、RENAME 等高危工单

  4. 【建议】:对于超过 100W 的表结构修改,尽量发起定时执行工单,在低峰期处理

1.3.2 列限制
  1. 【强制】:禁止添加或修改字段名为 MySQL关键字
  2. 【建议】:建议添加或修改字段名称全部使用小写字符
  3. 【强制】:禁止删除字段
  4. 【强制】:禁止缩短字段长度
  5. 【强制】:禁止修改字段类型,比如int转为varchar类型等
  6. 【强制】:新增自增字段时,类型必须为intbigint
1.3.3 索引限制
  1. 【建议】:单个索引中每个索引记录的长度不能超过64KB

  2. 【强制】:唯一索引以uk_uq_开头,普通索引以idx_开头,以字段名称或缩写作为后缀

  3. 【建议】:单个表上的索引个数不能超过 5 个

  4. 【建议】:在建立索引时,多考虑建立联合索引,并把区分度最高的字段放在最前面

  5. 【建议】:在多表 join 的SQL里,保证被驱动表的连接列上有索引,这样 join 执行效率最高

  6. 【建议】:添加或删除索引时,保证表里互相不存在冗余索引

2 使用规范

2.1 DML语句

2.1.1 insert语句
  1. 【强制】:insert 语句指定具体字段名称

  2. 【强制】:insert 语句中禁止使用select *语法

  3. 【强制】:insert 语句中使用select语法时必须指定 where 条件

  4. 【建议】:insert into…values(XX),(XX),(XX)…。这里 XX 的值不要超过 5000 个,避免引起数据库主从延迟

  5. 【建议】:对于有auto_increment属性字段的表的插入操作,并发需要控制在 200 以内

2.1.2 delete语句
  1. 【强制】:delete 删除数据时需指定 where 条件

  2. 【建议】:删除数据时尽量使用主键或其他索引列作为限制条件,避免全表扫描

  3. 【高危】:禁用delete t1 … where a=XX limit XX; 这种带 limit 的删除语句。因为会导致主从不一致,导致数据错乱

  4. 【建议】:批量操作数据时,需要控制事务处理间隔时间,进行必要的 sleep,一般建议值 5-10 秒

  5. 【建议】:大批量数据删除时建议提交数据归档工单申请

2.1.3 update语句
  1. 【强制】:update 更新数据时需指定 where 条件

  2. 【建议】:更新数据时尽量使用主键或其他索引列作为限制条件,避免全表扫描

  3. 【高危】:禁用update t1 … where a=XX limit XX;这种带 limit 的更新语句。因为会导致主从不一致,导致数据错乱

  4. 【高危】:update 禁止使用关联子查询,如update t1 set … where name in(select name from user where…); 效率极其低下

  5. 【强制】:禁止在业务的更新类 SQL 语句中使用 join

2.2 查询语句

  1. 【强制】:select 语句必须指定具体字段名称,禁止写为select *

  2. 【建议】:select 查询时建议使用索引字段作为限制条件,并且查询数据量不要超过全表的25%,确保查询使用到合适索引

  3. 【强制】:where 条件里等号左右字段类型必须一致,否则无法利用索引

  4. 【强制】:WHERE 子句中禁止只使用全模糊的 LIKE 条件进行查找,必须有其他等值或范围查询条件,否则无法利用索引

  5. 【建议】:索引列不要使用函数或表达式,否则无法利用索引

  6. 【建议】:in 值列表限制在 500 以内,可以减少底层扫描,减轻数据库压力从而加速查询

  7. 【建议】:select语句尽量使用 union all 代替 union,并且关联子句个数限制在 5 个以内。因为 union all 不需要去重,节省数据库资源,提高性能

  8. 【建议】:减少使用 or 语句,可将 or 语句优化为 union,然后在各个 where 条件上建立索引

  9. 【建议】:分页查询,当 limit 起点较高时,可先用过滤条件进行过滤

  10. 【强制】:禁止跨 db 的 join 语句

  11. 【建议】:不建议使用子查询,建议将子查询 SQL 拆开结合程序多次查询,或使用 join 来代替子查询

  12. 【建议】:线上环境,多表 join 不要超过 3 个表

  13. 【建议】:多表连接查询推荐使用别名,且 SELECT 列表中要用别名引用字段,数据库.表格式

  14. 【建议】:在多表 join 中,尽量选取结果集较小的表作为驱动表,来 join 其他表

  15. 【建议】:减少使用order by,尽量在业务程序上排序,减少对机器 CPU 的性能损耗

  16. 【建议】:order bygroup bydistinct这些 SQL 尽量利用索引直接检索出排序好的数据。如where a=1 order by b可以利用key(a,b)

  17. 【建议】:包含了order bygroup bydistinct这些查询的语句,where 条件过滤出来的结果集请保持在 1000 行以内,否则 SQL 会很慢。

附录

附录1:MySQL索引失效的常见情况

  1. 最左前缀原则。例如:存在联合索引idx_a_b(a, b),查询条件使用where b = 1则无法使用索引
  2. LIKE 前置模糊查询。例如:col_name like '%test'col_name like '%test%'
  3. 索引列使用函数或存在计算。例如:存在索引idx_col(col),查询条件使用where left(col, 2) = 'te'
  4. 查询条件使用 is not null。设计表结构时尽量设置 not null约束
  5. 字段类型出现隐式转换。例如:存在字段test_colvarchar类型,查询时使用了where test_col = 1,隐式转为了int类型导致索引失效
  6. 条件中有 or 存在可能不会使用索引。例如:查询条件为where a = 'testa' or b = 'testb',存在索引idx_a(a),此时也不会使用索引,除非为b字段也添加索引
  7. 查询结果超过整体结果的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)
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
6小时前
|
存储 关系型数据库 MySQL
轻松入门MySQL:数据库设计之范式规范,优化企业管理系统效率(21)
轻松入门MySQL:数据库设计之范式规范,优化企业管理系统效率(21)
|
6小时前
|
存储 关系型数据库 MySQL
12:企业规范约束-MySQL
12:企业规范约束-MySQL
45 0
|
6小时前
|
数据库
MybatisPlus属性字段为数据库关键字
MybatisPlus属性字段为数据库关键字
22 0
|
6小时前
|
存储 关系型数据库 MySQL
Mysql数据库设计规范和技巧
Mysql数据库设计规范和技巧
|
7月前
|
存储 关系型数据库 MySQL
Mysql(一) 数据库的设计与规范
假设,课程的学分发生了变更,那我们就需要把整表关于该课程的学分都要更新一次,但如果我们拆分出课程表,那我们就只需要把课程表中的课程信息更新就行。
147 0
|
8月前
|
SQL 关系型数据库 MySQL
MySQL数据库如何实现AX规范
我们一起来看一下 XA 规范相关的内容
51 0
|
9月前
|
SQL 关系型数据库 MySQL
第11章 数据库的设计规范【2.索引及调优篇】【MySQL高级】4
第11章 数据库的设计规范【2.索引及调优篇】【MySQL高级】4
55 0
|
9月前
|
SQL 关系型数据库 MySQL
第11章 数据库的设计规范【2.索引及调优篇】【MySQL高级】3
第11章 数据库的设计规范【2.索引及调优篇】【MySQL高级】3
121 0
|
6小时前
|
关系型数据库 MySQL 程序员
MYSQL数据库设计规范与原则
MYSQL数据库设计规范与原则
100 0
|
6月前
|
存储 关系型数据库 数据库
第22章_数据库的设计规范
第22章_数据库的设计规范
118 6