MySql数据库设计规范
1、数据库命名规范
- 采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线'_'组成;
- 命名简洁明确(长度不能超过30个字符);
- 例如：galaxy, life也可以galaxy_bak, life_log给数据库加个后缀;
- 除非是备份数据库可以加0-9自然数：galaxy_db_20170504;
2、数据库表命名规范
- 采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线'_'组成;
- 命名简洁明确,多个单词用下划线'_'分隔;
- 例如：galaxy_user, galaxy_repinggu, galaxy_rehead, 目前官网以为历史原因统一前缀了galaxy_, 最好能通过前缀来反应表之间的关系，如user_profile, user_role等等
3、数据库表字段命名规范
- 采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线'_'组成;
- 命名简洁明确,多个单词用下划线'_'分隔;
- 例如：user_login表字段 user_id, user_name, pass_word, eamil, tickit, status, mobile, posttime;
- 每个表中必须有自增主键, posttime(默认写入数据时的系统时间)
- 表与表之间的相关联字段名称要求尽可能的相同;
4、数据库表字段类型规范
- 用尽量少的存储空间来存放一个字段的数据；
例如：能使用int就不要使用varchar、char，能用varchar(16)就不要使用varchar(256);
- IP地址最好使用int类型;
- 固定长度的类型最好使用char，例如：邮箱、手机号(+86);
- 能使用tinyint就不要使用smallint, int;
- 对于一些boolean型或者枚举类型，可以用BOOLEAN、SET、ENUM;
- 最好给每个字段一个默认值，最好不能为null;
5、数据库表索引规范
- 命名简洁明确，例如：user_login表user_name字段应为user_name_index唯一索引;
- 为每个表创建一个主键索引;
- 为每个表创建合理的索引;
- 建立复合索引请慎重;
6、简单熟悉数据库范式
- 第一范式(1NF):字段值具有原子性，不能再分(所有关系型数据库系统都满足第一范式);
例如：姓名字段，其中姓和名是一个整体，如果区分姓和名那么必须设立两个独立字段;
- 第二范式(2NF):一个表必须有主键，即每行数据都能被唯一的区分;
备注：必须先满足第一范式;
- 第三范式(3NF):一个表中不能包涵其他相关表中非关键字段的信息，即数据表不能有冗余字段;
备注：必须先满足第二范式；
备注：往往我们在设计表中不能遵守第三范式，因为合理的冗余字段将会给我们减少join的查询；例如：相册表中会添加图片的点击数字段,在相册图片表中也会添加图片的点击数字段;
MySql数据库设计原则
1、核心原则
- 不在数据库做运算;
- cpu计算务必移至业务层;
- 控制列数量(字段少而精,字段数建议在20以内);
- 平衡范式与冗余(效率优先，往往牺牲范式);
- 拒绝3B(拒绝大sql语句、拒绝大事务transaction、拒绝大批量batch);
2、字段类原则
- 用好数值类型(用合适的字段类型节约空间);
- 字符转化为数字(能转化的最好转化，同样节约空间、提高查询性能);
- 避免使用NULL字段(NULL字段很难查询优化、NULL字段的索引需要额外空间、NULL字段的复合索引无效);
- 少用text类型(尽量使用varchar代替text字段);
3、索引类原则
- 合理使用索引(改善查询，减慢更新，索引一定不是越多越好);
- 字符字段必须建前缀索引;
- 不在索引做列运算;
- innodb主键推荐使用自增列(主键建立聚簇索引,主键不应该被修改,字符串不应该做主键)(理解Innodb的索引保存结构就知道了);
- 不用外键(由程序保证约束);
4、sql类原则
- sql语句尽可能简单(一条sql只能在一个cpu运算，大语句拆小语句，减少锁时间，一条大sql可以堵死整个简单的事务);
- 简单的事务;
- 避免使用trig/func(触发器、函数不用客户端程序取而代之);
- 不用select *(消耗cpu，io，内存，带宽，这种程序不具有扩展性);
- OR改写为IN(or的效率是n级别);
- OR改写为UNION(mysql的索引合并很差);
例如：
select id from table where phone='123' or name = 'joker';
=>
select id from table where phone='123'
union
select id from table where name='joker'
- 避免负向%;
- 慎用count(*),建议用count(id)来代替;
- limit高效分页(limit越大，效率越低);
- 使用union all代替union(union有去重开销);
- 少用连接join;
- 使用group by;
- 请使用同类型比较;
- 打散批量更新;
5、性能分析工具
- show profile;
- mysqlsla;
- mysqldumpslow;
- explain;
- show slow log;
- show processlist;