MySQL数据库规范（详细）
MySQL规范
⼀、基础规范
【强制】使⽤InnoDB存储引擎
解读：InnoDB存储引擎是MySQL默认存储引擎，⽀持事务和⾏级锁，并发性能更好，CPU及内存缓存页优化使得资源利⽤率更⾼
【强制】使⽤utf8字符集，如果有字段需要存储emoji表情之类的，则需要将字段或表设置成utf8mb4
解读：万国码，⽆需转码，⽆乱码风险，节省空间，utf8mb4向下兼容utf8
【强制】数据表、数据字段必须加⼊中⽂注释
解读：便于识别表和字段的⽤途
【强制】禁⽌使⽤存储过程、视图、触发器、Event
解读：⾼并发⼤数据的互联⽹业务，架构设计思路是“解放数据库CPU，将计算转移到服务层”，并发量⼤的情况下，这些功能很可能将数据库拖死，业务逻辑放到服务层具备更好的扩展性，能够轻易实现“增机器就加性能”。数据库擅长存储与索引，CPU计算还是上移吧
【强制】禁⽌存储⼤⽂件或者⼤照⽚
解读：⼤⽂件和照⽚存储在⽂件，数据库⾥存URI多好
⼆、命名规范
【强制】库名、表名、字段名：⼩写字母，下划线风格，禁⽌数字开头，禁⽌两个下划线中间只出现数字，禁⽌复数名词。
解读：正例：getter_admin，task_config，level3_name 反例：GetterAdmin，taskConfig，level_3_name
【强制】命名中不允许出现MYSQL数据库中的保留字。如desc、range、match、delayed等，请参考MySQL官⽅保留字。
【强制】索引命名格式为：索引类型_字段名。普通索引名idx_xxx，唯⼀索引名uniq_xxx
三、表设计规范
【强制】单实例表数⽬必须⼩于500，单表列数⽬必须⼩于30
【建议】⽆特殊情况下，建议显式指定⼀个⽆业务⽤途的⾃增unsigned bigint型主键
解读：主键递增，数据⾏写⼊可以提⾼插⼊性能，可以避免page分裂，减少表碎⽚提升空间和内存的使⽤
【建议】表必须有create_time和update_time两个字段，并指定datetime类型
解读：
建表时建议按照以下格式设计：
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP;
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
加字段时建议按照以下格式设计：
ADD COLUMN `create_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00';
ADD COLUMN `update_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP;
insert语句字段顺序timestamp的使⽤有以下的限制，所以建议统⼀使⽤datetime类型
a) timestamp⽀持的时间范围⼩，范围是从’1970-01-01 00:00:01′到 ’2038-01-19 03:14:07′ UTC;
b) timestamp的插⼊和查询受当地时区的影响;
c) timestamp的属性会受到MySQL版本和SQL mode的影响。
【强制】禁⽌使⽤外键，如果有外键完整性约束，需要应⽤程序控制
解读：外键会导致表与表之间耦合，update与delete操作都会涉及相关联的表，⼗分影响sql的性能，甚⾄会造成死锁。⾼并发情况下容易造成数据库性能，⼤数据⾼并发业务场景数据库使⽤以性能优先
【建议】单表⾏数超过500万⾏或者单表容量超过2GB，才推荐进⾏分库分表。
解读：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。
四、字段设计规范
【强制】把字段定义为NOT NULL并且提供默认值
解读：
以下只是建议的默认值，可根据业务需要设置成其它默认值
tinyint/int/bigint 类型默认值：0
char/varchar 类型默认值：' '
date 类型默认值：'0000-00-00'
time 类型默认值：'00:00:00'
datetime 类型默认值：'0000-00-00 00:00:00'
a)null的列使索引/索引统计/值⽐较都更加复杂，对MySQL来说更难优化
b)null 这种类型MySQL内部需要进⾏特殊处理，增加数据库处理记录的复杂性；同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多
c)null值需要更多的存储空间，⽆论是表还是索引中每⾏中的null的列都需要额外的空间来标识
d)对null 的处理时候，只能采⽤is null或is not null，⽽不能采⽤=、in、<、<>、!=、not in这些操作符号。如：where
name!=’shenjian’，如果存在name为null值的记录，查询结果就不会包含name为null值的记录
【建议】禁⽌使⽤TEXT、BLOB类型，可以做垂直拆分到⼦表中
解读：会浪费更多的磁盘和内存空间，⾮必要的⼤量的⼤字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能
【强制】存储货币⽤decimal或整数类型，禁⽌使⽤float和double
解读：float和double在存储的时候，存在精度损失的问题
【建议】⼩数类型⽤decimal，禁⽌使⽤float和double
解读：float和double在存储的时候，存在精度损失的问题，很可能在值的⽐较时，得到不正确结果。如果存储的数据范围超过decimal 的范围，建议将数据拆成整数和⼩数分开存储。
【建议】枚举类型禁⽌使⽤ENUM，可使⽤TINYINT代替
解读：
a)增加新的ENUM值要做DDL操作
b)ENUM的内部实际存储就是整数
【建议】表达是与否概念的字段，必须使⽤is_xxx的⽅式命名，数据类型是unsigned tinyint( 1表⽰是，0表⽰否)
【建议】使⽤varchar(20)存储⼿机号
解读：
a)涉及到区号或者国家代号，可能出现+-()
b)⼿机号不会做数学运算
c)varchar可以⽀持模糊查询，例如：like“138%”
【建议】如果存储的字符串长度⼏乎相等，使⽤char定长字符串类型。
【建议】varchar是可变长字符串，不预先分配存储空间，长度不要超过5000。
【建议】字段允许适当冗余，以提⾼性能，但是必须考虑数据同步的情况。
解读：冗余字段应遵循：不是频繁修改的字段，不是varchar超长字段，更不能是text字段。正例：商品类⽬名称使⽤频率⾼，字段长度短，名称基本⼀成不变，可在相关联的表中冗余存储类⽬名称，避免关联查询。
【建议】选择合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。
五、索引设计规范
【建议】单表索引建议控制在5个以内
解读：索引过多会增加存储开销和增删改的开销
【建议】禁⽌在更新⼗分频繁、区分度不⾼的属性上建⽴索引
解读：
a)更新会变更B+树，更新频繁的字段建⽴索引会⼤⼤降低数据库性能
b)“性别”这种区分度不⼤的属性，建⽴索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似
【建议】尽量使⽤组合索引，建⽴组合索引时必须把区分度⾼的字段放在前⾯，字段数不允许超过5个
解读：能够更加有效的过滤数据，索引上字段超过5个时，实际已经起不到有效过滤数据的作⽤了
【建议】在排序、分组、取唯⼀字段上创建索引，经常与其他表进⾏关联的表，在关联字段上应该建⽴索引，经常出现在Where⼦句中的字段，特别是⼤表的字段，应该建⽴索引
【建议】业务上具有唯⼀特性的字段，即使是组合字段，也必须建成唯⼀索引。
解读：不要以为唯⼀索引影响了insert速度，这个速度损耗可以忽略，但提⾼查速度是明显的；另外，即使在应⽤层做了⾮常完善的校验和控制，只要没有唯⼀索引，根据墨菲定律，必然有脏数据产⽣。
【建议】在varchar字段上建⽴索引时，必须指定索引长度，没必要对全字段建⽴索引，根据实际⽂本区分度决定索引长度。
解读：索引的长度与区分度是⼀对⽭盾体，⼀般对字符串类型数据，长度为20的索引，区分度会⾼达90%以上，可以使⽤count(distinct left(列名, 索引长度))/count(*)的区分度来确定。
六、SQL使⽤规范
【强制】禁⽤select *，必须指定列
解读：
a)读取不需要的列会增加CPU、IO、NET消耗
b)不能有效的利⽤覆盖索引
c)使⽤SELECT *容易在增加或者删除字段后出现程序BUG
【强制】insert语句需指定列，INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
解读：避免在增加或者删除字段后出现程序BUG
【强制】禁⽌使⽤属性隐式转换
解读：SELECT uid FROM t_user WHERE phone=138******** 会导致全表扫描，⽽不能命中phone索引，因为phone是varchar类型，需要添加引号
【建议】禁⽌在WHERE条件的属性上使⽤函数或者表达式
解读：SELECT uid FROM t_user WHERE from_unixtime(day)>='2017-02-15' 会导致全表扫描
正确的写法是：SELECT uid FROM t_user WHERE day>= unix_timestamp('2017-02-15 00:00:00')
【强制】禁⽌负向查询，以及%开头的模糊查询
解读：
a)负向查询条件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，会导致全表扫描
b)%开头的模糊查询，会导致全表扫描
【建议】禁⽌⼤表使⽤JOIN查询，禁⽌⼤表使⽤⼦查询
解读：会产⽣临时表，消耗较多内存与CPU，极⼤影响数据库性能
【建议】禁⽌使⽤OR条件，必须改为IN查询
解读：旧版本Mysql的OR查询是不能命中索引的，即使能命中索引，为何要让数据库耗费更多的CPU帮助实施查询优化呢?
【强制】应⽤程序必须捕获SQL异常，并有相应处理
【建议】超过三个表禁⽌join。需要join的字段，数据类型保持绝对⼀致；多表关联查询时，保证被关联的字段需要有索引。
解读：即使双表join也要注意表索引、SQL性能。
【强制】页⾯搜索严禁左模糊或者全模糊，如果需要请⾛搜索引擎来解决。
解读：索引⽂件具有B-Tree的最左前缀匹配特性，如果左边的值未确定，那么⽆法使⽤此索引。
【建议】使⽤order by请注意利⽤索引的有序性。
解读：order by 最后的字段是组合索引的⼀部分，并且放在索引组合顺序的最后，避免出现file_sort的情况，影响查询性能。
正例：where a=? and b=? order by c; 索引：a_b_c
反例：索引中有范围查，那么索引有序性⽆法利⽤，如：WHERE a>10 ORDER BY b; 索引a_b⽆法排序。
【建议】利⽤覆盖索引来进⾏查询操作，来避免回表操作。
解读：如果⼀本书需要知道第11章是什么标题，会翻开第11章对应的那⼀页吗?⽬录浏览⼀下就好，这个⽬录就是起到覆盖索引的作⽤。正例：能够建⽴索引的种类：主键索引、唯⼀索引、普通索引，⽽覆盖索引是⼀种查询的⼀种效果，⽤explain的结果，extra列会出现：using index。
【建议】利⽤延迟关联或者⼦查询优化超多分页场景。
解读：MySQL并不是跳过offset⾏，⽽是取offset+N⾏，然后返回放弃前offset⾏，返回N⾏，那当offset特别⼤的时候，效率就⾮常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进⾏SQL改写。正例：先快速定位需要获取的id段，然后再关联： SELECT a.* FROM 表1 a, (select id from 表1 where 条件 LIMIT 100000,20 ) b where a.id=b.id
【建议】SQL性能优化的⽬标：⾄少要达到 range 级别，要求是ref级别，如果可以是consts最好。
解读：
1)consts 单表中最多只有⼀个匹配⾏(主键或者唯⼀索引)，在优化阶段即可读取到数据。 2)ref 指的是使⽤普通的索引(normal index)。
3)range 对索引进⾏范围检索。
反例：explain表的结果，type=index，索引物理⽂件全扫描，速度⾮常慢，这个index级别⽐较range还低，与全表扫描是⼩巫见⼤巫。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。