SQL规范，注意事项。
MySQL 常用的存储引擎
数据引擎表
| 存储引擎 | 事务 | 锁粒度 | 主要应用 | 忌用 |
|---|---|---|---|---|
| MYISAM | 不支持 | 支持并发插入的表级锁 | SELECT,INSERT | 读写操作频繁 |
| MRG_MYISAM | 不支持 | 支持并发插入的表级锁 | 分段归档,数据仓库 | 数全局查找过多的场景 |
| Innodb | 支持 | 支持MVCC的行级锁 | 事务处理 | 无 |
| Archive | 不支持 | 行级锁 | 日志记录只支持insert,select | 需要随机读取,更新,删除 |
| Ndb cluster | 支持 | 行级锁 | 高可用性 | 大部份应用 |
表及字段的命名规则
规则
- 可读性原则
- 使用大写和小写来格式化的库对象名字以获得良好的可读性。
- 例如:使用CustAddress而不是Custaddress来提高可读性。(这里要注意有些DBMS系统对表名的大小写是敏感的)
- 使用大写和小写来格式化的库对象名字以获得良好的可读性。
- 表意性原则
- 对象的名字应该能够描述它所标识的对象。
- 例如,对于表,表的名称应该能够体现表中存储的数据内容;对于存储过程,存储过程名称应该能够体现存储过程的功能。
- 对象的名字应该能够描述它所标识的对象。
- 长名原则
- 尽可能少使用或者不使用缩写,
- 适用于数据库(DATABASE)名之外的任一对象。
- 尽可能少使用或者不使用缩写,
字段类型的选择原则
原因
列的数据类型一方面影响数据存储空间的开销,另一方面也会影响数据查询性能。当一个列可以选择多种数据类型时,应该优先考虑数字类型,其次是日期或二进制类型,最后是字符类型。对于相同级别的数据类型,应该优先选择占用空间小的数据类型。
| 列类型 | 存储空间 |
|---|---|
| TINYINT | 1字节 |
| SMALLINT | 2个字节 |
| MEDIUMINT | 3个字节 |
| INT | 4个字节 |
| BIGINT | 8个字节 |
| DATE | 13个字节 |
| DATETIME | 8个字节 |
| TIMESTAMP | 4个字节 |
| CHAR(M) | M字节,1 <= M <= 255 |
| VARCHAR(M) | L+1字节,在此L <= M1 <= M <= 255 |
- 以上选择原则主要是从下面两个角度考虑
- 在对数据进行比较(查询条件、JOIN条件及排序)操作时:
同样的数据,字符处理往往比数字处理慢。 - 在数据库中,数据处理以页为单位,列的长度越小,利于性能提升。
- 在对数据进行比较(查询条件、JOIN条件及排序)操作时:
char varchar 如何选择
原则
- 如果列中要存储的数据长度差不多是一致的,则应该考虑用char;否则应该考虑用varchar。
- 如果列中的最大数据长度小于50Byte,则一般也考虑用char。(当然,如果这个列很少用,则基于节省空间和减少I/O的考虑,还是可以选择用varchar)
- 一般不宜定义大于50 Byte的char类型列。
decimal与float 如何选择
原则:
- decimal用于存储精确数据,而float只能用于存储非精确数据。故精确数据只能选择用decimal类型。
- 由于float的存储空间开销一般比decimal小(精确到7位小数只需要4个字节,而精确到15位小数只需要8字节)故非精确数据优先选择float类型。
时间类型如何存储
使用int来存储时间字段的优缺点
- 优点:字段长度比datetime小。
- 缺点:使用不方便,要进行函数转换。
- 限制
- 只能存储到2038-1-19 11:14:07即2^32 为2147483648
- 需要存储的时间粒度
- 年月日 小时 分 秒 周
如何选择主键
区分业务主键和数据库主键
- 业务主键用于标识业务数据,进行表与表之间的关联;数据库主键为了优化数据存储(Innodb会生成6个字节的隐含主键)
- 根据数据库的类型考虑主键是否要顺序增长
- 有些数据库是按主键的顺序逻辑存储的
主键的字段类型所占空间要尽可能的小
对于使用聚集索引方式存储的表,每个索引后都会附加主键信息。
避免使用外键约束
- 降低数据导入的效率
- 增加维护成本
- 虽然不建议使用外键约束,但是相关联的列上一定要建立索引
避免使用触发器
:1. 降低数据导入的效率。
2. 可能会出现意想不到的数据异常。
3. 使业务逻辑变得复杂。
关于预留字段
原因
- 无法准确的知道预留字段的类型。
- 无法准确的知道预留字段中所存储的内容。
- 后期维护预留字段所要的成本,同增加一个字段所需要的成本是相同的。
- 严禁使用预留字段。