数据库设计那些事

物理设计 建立表结构

1、选择数据库系统:应用特点、成本
2、定义库、表、字段的命名规范(数据库系统对此有限制)
3、根据系统选择合适的字段类型
4、反范式化设计:增加冗余,提高效率

字段类型选择

1、影响:存储空间开销、数据查询性能
2、数字>日期或二进制>字符>
同级别中优先占用空间小的
3、MySQL类型占用空间如图(TIMESTAMP只能到2037年)

数据库设计

数据库设计

4、原因:进行比较时,同样的数据字符处理往往比数字处理慢;数据处理以页为单位,列长越小,性能越好

字段类型选择具体原则

1、列中存储的数据长度差不多一致的,char
最大长度小于50字节 char 也可以用varchar(查询少,尽量节省空间时)
2、非精确小数优先float
3、时间类型,int长度小,只能到20130119 11:14:07,查询需要转换,查询少时用int 查询多时用datetime存储较好

其他注意事项
一、主键

区分~
业务主键:标识业务数据,表与表之间的关联
数据库主键:优化存储

2、根据数据库类型,考虑主键是否要顺序增长(有些数据按主键的顺序逻辑存储)
3、主键字段类型尽可能的小(查询性能)
二、高并发避免外键约束:降低数据导入效率,增加维护成本,相关联的列上一定要建索引
三、避免使用触发器:降低导入效率,可能会出现意想不到的数据异常,使业务逻辑变的复杂
四、严禁使用预留字段:无法准确知道预留字段的类型,无法知道存储内容,后期维护预留字段的成本与新增是一样的

数据库维护和优化:

1、维护数据字典;
2、维护索引;
3、维护表结构;
4、在适当的时候对表进行水平拆分或垂直拆分;

维护数据字典

1、第三方工具(依数据库系统)
2、利用数据库本身特点
如MySQL 给库、表、字段增加备注,然后导出数据字典

维护索引

建立索引的列:
1、出现在where、group by、order by 从句中的列
2、可选择性高的列放到索引前面(条件列顺序不要求与索引列顺序一致)
3、索引列数据不要太长,(如text进行md5处理)

注意:
1、索引不是越多越好(过多的索引也会降低读的效率:多个索引选择的过程)
2、定期维护索引碎片
3、(MySQL)SQL中不要使用强制索引关键字

维护(修改)表结构

注意事项

1、MySQL5.5之前会锁表,可使用第三方工具;5.6之后本身支持在线表结构变更
2、同时维护数据字典
3、控制表的宽度和大小

适合的操作

1、批量操作(数据库中)逐条操作(应用程序中)
2、尽量少用”select * “查询
3、控制使用用户自定义函数(使用函数,索引不起作用)
4、不要使用全文索引(中文支持不好,需要另建索引文件)

垂直拆分
列太多,拆成多个小表,数据量不变

1、经常一起查询的列放到一起
2、text、blob等大字段拆分到附加表中

水平拆分
数据量太大,拆成多个相同结构的表,字段数不变

Hash Key