数据库 mysql 基本规范

TooYoung · 2018年09月12日 · 301 次阅读
本帖已被设为精华帖!

1.命名规范

库名、表名、字段名必须使⽤⼩写字⺟,并采⽤下划线分割 库名、表名、字段名禁⽌超过32个字符。须见名知意 库名、表名、字段名禁⽌使⽤MySQL保留字 临时库、表名必须以tmp为前缀,并以⽇期为后缀 备份库、表必须以bak为前缀,并以⽇期为后缀 例子: create table TTT (insert int(10) not null ...)
create table abc_1202 ...
alter table t add index idx_uid_mid_time(uid,mid,time)
alter table t add index idx_uid(uid,mid,time) ->NO tmp_test01_0704 ->NO bak_test01_20130704

2.基础规范

使⽤INNODB存储引擎
表字符集使⽤UTF8
所有表都需要添加注释
单表数据量建议控制在5000W以内 ->500W内性能最佳 不在数据库中存储图⽚、⽂件等⼤数据
禁⽌在线上做数据库压⼒测试
禁⽌从测试、开发环境直连数据库

3.库表设计

慎用分区表
拆分⼤字段和访问频率低的字段,分离冷热数据
⽤HASH进⾏散表,表名后缀使⽤⼗进制数,下标从0开始
按⽇期时间分表需符合YYYY[MM][DD][HH]格式
采⽤合适的分库分表策略。例如千库⼗表、⼗库百表等 、 例子: comment_20120815
comment_20120816
comment_120817
user_39
user_3A
user_3B
user_3C

4.字段设计

尽可能不使⽤TEXT、BLOB类型
⽤DECIMAL代替FLOAT和DOUBLE存储精确浮点数
Simple is good 将字符转化为数字
使⽤TINYINT来代替ENUM类型
Generosity can be unwise 存储 “hello”时VARCHAR(5) VS VARCHAR(200) The best strategy is to allocate only as much space as you really need. ENUM('Mercury', 'Venus', 'Earth') numbers ENUM('0','1','2') 存储index,⽽不是字符串 枚举值改变会导致DDL Avoid null if possible
可为NULL的列影响索引统计数据⽣成
可为NULL的列加索引会占⽤额外空间 所有字段均定义为NOT NULL
Smaller is usually better 使⽤UNSIGNED存储⾮负整数
INT类型固定占⽤4字节存储
使⽤timestamp存储时间
使⽤INT UNSIGNED存储IPV4
使⽤VARBINARY存储⼤⼩写敏感的变长字符串
禁⽌在数据库中存储明⽂密码

5.索引规范

索引的⽤途 去重
加速定位
避免排序
覆盖索引
索引数量控制 单张表中索引数量不超过5个
单个索引中的字段数不超过5个
对字符串使⽤前缀索引,前缀索引⻓度不超过8个字符
建议优先考虑前缀索引,必要时可添加伪列并建⽴索引 主键准则 表必须有主键
不使⽤更新频繁的列
尽量不选择字符串列
不使⽤UUID MD5 HASH
默认使⽤⾮空的唯⼀键
建议选择⾃增或发号器 重要的SQL必须被索引 区分度最⼤的字段放在前⾯ 核⼼SQL优先考虑覆盖索引 避免冗余和重复索引 索引不是越多越好 综合评估数据密度和分布
考虑查询和更新⽐例 索引是⼀把双刃剑:降低插⼊和更新速度,占⽤磁盘空间 索引禁忌 不在低基数列上建⽴索引,例如“性别”
不在索引列进⾏数学运算和函数运算
尽量不使⽤外键 外键⽤来保护参照完整性,可在业务端实现
对⽗表和⼦表的操作会相互影响,降低可⽤性
INNODB本⾝对online DDL的限制
不使⽤%前导的查询,如like “%ab” 不使⽤负向查询,如not in/like ⽆法使⽤索引,导致全表扫描 全表扫描导致bufer pool利⽤率降低 尺有所短,⼨有所⻓,换⼀种⼯具试试!

6.SQL设计

避免使⽤存储过程、触发器、UDF、events等 让数据库做最擅长的事
降低业务耦合度,为scale out、sharding留有余地
避开BUG
避免使⽤⼤表的JOIN MySQL最擅长的是单表的主键/⼆级索引查询
JOIN消耗较多内存,产⽣临时表
避免在数据库中进⾏数学运算 MySQL不擅长数学运算和逻辑判断
⽆法使⽤索引 md5()/order by rand()
select ... where to_days(current_date) - to_days(date_col)<=10
select ... where date_col>=date_sub(current_date, interval 10 day)
select ... where date_col>=date_sub(‘2013-08-17’, interval 10 day)
select ... where date_col>=‘2013-08-07’ 数据库是有状态的服务,调整代码部署更灵活、简单、⾼效! 减少与数据库的交互次数 INSERT ... ON DUPLICATE KEY UPDATE
REPLACE INTO、INSERT IGNORE 、INSERT INTO VALUES(),(),()
UPDATE … WHERE ID IN(10,20,50,…)
合理的使⽤分页 限制分页展⽰的页数
只能点击上⼀页、下⼀页
采⽤延迟关联(join的延迟)
拒绝⼤SQL,拆分成⼩SQL 充分利⽤QUERY CACHE
充分利⽤多核CPU 使⽤in代替or,in的值不超过1000个 (所在列没有索引的情况差别巨大) 禁⽌使⽤order by rand()
使⽤EXPLAIN诊断,避免⽣成临时表
⽤union all⽽不是union(union会多做一次去重的操作)
程序应有捕获SQL异常的处理机制
禁⽌单条SQL语句同时更新多个表
不使⽤select * 消耗CPU和IO、消耗网络带宽
⽆法使⽤覆盖索引
减少表结构变更带来的影响
因为⼤,select/join 可能⽣成临时表 select * from opp where phone=‘12345678’ or phone=‘234234234’
select * from opp where phone in (’12345678’, ‘234234234’)
select * from app where phone=‘010-88886666’ or cellphone=‘18618111111’
select * from opp where phone=’010-88886666’
union all select * from opp where cellphone=’ 18618111111’

7.行为规范

批量导⼊、导出数据必须提前通知DBA协助观察
禁⽌在线上从库执⾏后台管理和统计类查询
禁⽌有super权限的应⽤程序账号存在
产品出现⾮数据库导致的故障时及时通知DBA协助排查
推⼲活动或上线新功能必须提前通知DBA进⾏流量评估
数据库数据丢失,及时联系DBA进⾏恢复
对单表的多次alter操作必须合并为⼀次操作
不在MySQL数据库中存放业务逻辑
重⼤项目的数据库⽅案选型和设计必须提前通知DBA参与
对特别重要的库表,提前与DBA沟通确定维护和备份优先级
不在业务⾼峰期批量更新、查询数据库
提交线上建表改表需求,必须详细注明所有相关SQL语句 良好的线上环境需要⼤家共同的努⼒!

共收到 0 条回复
es6china 将本帖设为了精华贴 09月12日 20:08
需要 登录 后方可回复, 如果你还没有账号请点击这里 注册