最近看到一份MySQL开发规范，受益颇多，规范里隐藏了很多MySQL的原理，大家有兴趣可以分析一下这些规范被如此设定的原因。
命名规范
-
库名、表名、字段名、索引名必须使用小写字母，并采用下划线分割。
-
库名、表名、字段名支持最多64个字符但禁止超过32个字符。
-
库名、表名、字段名必须见名知意。命名与业务、产品线相关联。
-
库名、表名、字段名禁止使用MySQL保留字(如：add/analyze/check/char/to/and/as/before/between/column...)。
-
数据库初始化会删除test库 ，库名、表名、字段名禁止使用test开头。
-
创建一rubbish库，存放无效表、垃圾表，线上系统无异常，确认无误后，定期清理。
-
创建一archive库，存放本地归档，确认无使用计划后，定期转移到归档备份服务器中。
-
临时库、表名必须以tmp为前缀，并以日期为后缀。例如 tmp_table_20140401。
-
禁止使用存储过程、触发器、视图、UDF、EVENT等。
-
账号命名规则，本业务访问账号：业务名_wn/业务名_rn；跨业务访问账号：访问组_要访问业务名_w/访问组_要访问业务名_r
库表规范
-
默认使用InnoDB存储引擎。
-
库、表、字段、client字符集默认使用UTF8，如遇到EMOJ等表情符号的存储需求，可申请使用UTF8MB4字符集。
-
单库不要超过500个表、单表字段数不要超过50个
-
表、字段添加comment（除主键）
-
禁止在数据库中存储图片、文件等大数据。
-
字符类型居多的表建议控制在3000W以内，整型居多的表建议控制在5000W以内。
-
不建议或禁止在线上做数据库压力测试。
-
禁止测试环境直连生产环境调试，如有数据测试需求，请走特别申请流程。
-
依据数据冷热，分级存储，历史归档
库表设计
表设计
-
前端禁止使用分区表。
-
将关键字段、使用率频繁的字段拆分到单独表中，将大字段、访问频率低的字段拆分到单独表中存储，冷热分离。
-
推荐使用HASH、RANGE进行散表，表名后缀使用数字，数字必须从0开始。
-
散表或归档分表名称需符合YYYY-MM-DD-HH格式，例如table_201401、table_20140422
字段设计
表字段少而精，尽量选择最小数据类型，表结构设计时适当冗余，不在数据库上做大量的cpu运算
-
建表默认5字段，主键、创建时间、创建者、修改时间、修改者。id、create_time、create_user、update_time、update_user
-
建议使用UNSIGNED存储非负数值。
-
建议使用INT UNSIGNED存储IPV4。
-
用DECIMAL代替FLOAT和DOUBLE存储精确浮点数，例如支付相关数据。
-
建议整型字段直接使用INT，INT类型固定占4字节存储， 默认是11，最大有效显示宽度是255，例如INT(4)仅代表字符宽度为4位，不代表存储长度。
-
区分使用TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT数据类型和取值范围(TINYINT>SMALLINT>MEDIUMINT>INT>BIGINT>DECIMAL---存储空间逐渐变大,而性能却逐渐变小)。
-
禁止字段使用enum数据类型。ENUM类型不允许修改默认值，只允许顺序添加。
-
禁止使用TEXT、BLOB类型，如必须使用，建议和主键一起进行拆表处理，可考虑VARCHAR替换。
-
使用VARBINARY类型默认区分大小写，没有字符集概念，可存储大小写敏感的变长字符串或二进制内容。
-
使用尽可能小的VARCHAR字段。VARCHAR(N)中的N表示字符数而非字节数。比如VARCHAR(255)，可以最大可存储255个汉字，需要根据实际的宽度来选择N。
-
区分使用DATETIME和TIMESTAMP。存储年使用YEAR类型。存储日期使用DATE类型。 存储时间(精确到秒)建议使用TIMESTAMP类型。
-
所有字段均定义为NOT NULL。尽量避免使用NULL，要是必须用NULL，那也可考虑使用0、特殊值或空串来进行代替。
索引规范
-
单张表中索引数量不超过5个，单个索引中的字段数不超过5个。
-
非唯一索引按照“idx_字段名称[_字段名称]”进行命名。
-
唯一索引按照“uk_字段名称[_字段名称]”进行命名。
-
联合索引建议包含所有字段名，过长的字段名可以缩写形式。
-
表必须有主键，推荐使用UNSIGNED自增列作为主键并且该主键为非业务字段。
-
唯一键由3个以下字段组成，并且字段都是整形时，可使用唯一键作为主键。其他情况下，建议使用自增列或全局生成器作主键。
-
禁止冗余、重复索引。
-
禁止索引null列。
-
禁止使用外键，避免产生死锁，通过程序来保证约束。
-
避免数据类型转换，联表查询时，JOIN列的数据类型必须相同，并且要建立索引。
-
选择区分度大的列建立索引。字段的顺序对组合索引有至关重要的作用，组合索引中，区分度大的字段要放在最前面。
-
不在区分度低的上建立索引，例如“性别”。
-
对较长的字符串字段、blob、text使用前缀索引，前缀索引长度不超过8个字符。（使用前缀索引，不仅节省存储空间，提高索引缓存命中率，还可减少disk的IO操作。）
-
不对过长的VARCHAR字段建立索引。建议优先考虑添加CRC32或MD5伪列，并对伪列建立索引，减少索引长度，提高效率。
-
合理创建联合索引，(a，b，c) 相当于 (a) 、(a，b) 、(a，b，c)。
-
合理使用覆盖索引减少IO，避免排序。
-
添加优化的索引之后，再删除多余的索引。
-
单个索引尽可能覆盖更多的sql，更新频率比较高的表要控制索引的数量。
-
大批量变更数据，索引维护成本很高，因此大批量数据更新要拆分出小粒度。
SQL设计
-
使用prepared statement，可以提升性能并避免SQL注入。
-
用IN代替OR。SQL语句中IN包含的值不应过多，应少于500个。
-
禁止隐式类型转换。数值类型禁止加引号；字符串类型必须加引号。
-
避免使用JOIN和子查询，禁止使用3表以上的JOIN，必要时推荐用JOIN代替子查询。
-
避免在MySQL中索引列进行数学运算和函数运算。
-
减少与数据库交互次数，尽量采用批量提交SQL语句（INSERT INTO table （column1、column2、column3） VALUES(),(),()），不宜过多。
-
拆分复杂SQL为多个小SQL，避免大事务。
-
获取大量数据时，建议分批次获取数据，每次获取数据少于500条，结果集应小于1M。
-
用UNION ALL替换UNION。
-
禁止在前端业务中使用count(*)，可放在memcache、redis、某一从库或增加统计表来维护。
-
多表关联时禁止使用select *语句，只取需要列。
-
SQL中避免出现rand()、sysdate()、current_user()等不确定结果的函数。
-
INSERT INTO语句必须显示指明字段名称。
-
禁止使用 INSERT INTO B SELECT * FROM A。
-
禁止单条SQL语句同时更新多个表。
-
建议使用合理的分页方式以提高分页效率。
-
禁止在生产环境从库进行查询分析统计操作，必要时请申请专用统计服务器，前后台分离。
-
程序应有捕获SQL异常的处理机制，必要时通过rollback显式回滚。
-
程序不要修改数据库中事务隔离机制，控制锁的行为。
-
避免全表扫描，重要SQL必须被索引：update、delete的where条件列、order by、group by、distinct字段、多表join字段。
-
如果只是分组，用group by a order by null替换group by a去除排序。
-
禁止使用%前导查询，例如：like “%abc”，无法利用到索引。
-
禁止使用负向查询，例如 not in、!=、not like、<>。
-
禁止在 where 子句中对字段进行 null 值判断，字段通过增加默认值处理。
-
使用EXPLAIN判断SQL语句是否合理使用索引，尽量避免extra列出现：Using File Sort、Using Temporary。
-
禁止DML语句中出现@num变量替代字段名称。
-
UPDATE或DELETE语句加上WHERE条件索引列更新（如果使用非索引列更新会导致锁全表，造成Lock wait timeout exceeded; try restarting transaction），禁用LIMIT语句。
-
拼接sql语句时，注意where条件，防止sql注入时表记录被清空（如：delete from where a/a=a/1=1/a=0 or 1=1/exist返回布尔值为恒真）。
-
DML：sql语句中不允许有drop、truncate、delelet all data等语句。
-
DDL：添加字段语句禁止使用after，特别是对于mycat或gaea的分布式代理的数据库，如果加上after，会造成数据错误。例如：alter table t add column a after b;
参考
新建表参考
create table tb_cart_goods_special (
id int unsigned not null auto_increment,
area varchar(10) not null default 'cn' comment '区域简称, e.g. cn,hk,tw,sg',
region_name varchar(20) not null default '' comment '区域名称',
primary key (id)
）engine=innodb auto_increment=1 default charset=utf8 comment='购物车下面单个商品的推荐调整’;
添加字段参考
alter table tb_cart_goods_special add goods_id int unsigned not null default '0' comment '商品编号’;
添加索引参考
alter table tb_cart_goods_special add index idx_area(area),add unique index uk_goods_id(goods_id);
注意
-
任何ddl/dml操作语句都不要加数据库名称，由DBA切换到对应的数据库来执行。
-
新建表自增auto_increment从1开始。
-
工单提交的sql，不要加上特殊字符，如"`"
最后
大家如果喜欢我的文章，可以关注我的公众号（程序员麻辣烫）
我的个人博客为：shidawuhen.github.io/
