一.设计规范
- 建表约束
- 必须有字段id(主键id,一般设置为自增(步数为1),当并发性需求较高的时候,不建议自增,可以自定义实现机制), create_time(建表时间), update_time(更新时间,设置为自动更新)。
- 表名和字段名必须使用小写字母或数字,禁止数字开头,同时使用下划线分割不同的单词,Windows环境下Mysql忽略大小写,Linux下大小写是有区分的,因此为了统一,全部是小写字母加数字,同时命名不能用复数名词,表明和字段名应用来表示含义。
- 表示有是否含义的字段时,必须用is_XXX来表示字段名,用tinyint unsigned(1是, 0否),当然也可以使用char(1)来表示'Y'(是), 'N'(否)。
- 同时禁止使用MySql保留字段,desc,range等,用过的同学应该知道这个会报错。
- 主键的索引名必须为pk_xxx,唯一索引名为uk_xxx,普通索引名为idx_xxx。
- 对于数字类型的定义比如bigint(1)与bigint(5)的原理和占用空间都是一样的,不同的长度是不同工具对数字的表形式不同,两者没有任何差别。
- 小数类型用decimal来储蓄,尽量根据单位换算用Bigint unsigned等整形来表示,因为MySql小数的运算相比整数消耗资源会更多一点。
- char用来表示定长字符,varchar用来储蓄可变字符,根据需求进行字段长度的设置,可以多余,但要适量。
- 对于业务上不发生改变的字段设为枚举,在MySql中枚举的储蓄也更加的紧凑,查询的效率也更好点。
- 对于需要用有限数字来表示字段信息时,不要用‘0’来表示某种状态,某些情况下JAVA中部分字段的默认值为‘0’,可能会发生一些不必要的错误,正
- 索引约束
- 业务上唯一的字段,索引必须为唯一索引。比如user_id(假设每个用户只有一个id,若此字段需要建立索引,必须使用唯一索引。
- 在varchar字段上建立索引时,索引长度必须指定,根据业务情况的区分度来设置,一半20的区分度为90%。
- 建立组合索引时,区分度高的在左边。
- 同时,索引的数量单表至多6个,因为索引的维护(频繁的删除和插入操作),会带来比较高的资源消耗,因此不要在频繁删除和更新的字段加索引。
- 案列表
-
CREATE TABLE `product` ( `id` BIGINT ( 20 ) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增ID', `uuid` CHAR ( 32 ) NOT NULL COMMENT '产品UUID', `user_uuid` CHAR ( 32 ) NOT NULL COMMENT '用户UUID', `pack_amount` SMALLINT ( 6 ) NOT NULL COMMENT '打包价格\r\n', `term` INT ( 10 ) UNSIGNED NOT NULL COMMENT '借款限期,单位-天', `remark` VARCHAR ( 5 ) DEFAULT '' COMMENT '备注', `product_type` TINYINT ( 4 ) NOT NULL COMMENT '产品类型(1:赚,2:借款,3:转售)', `status` TINYINT ( 4 ) UNSIGNED NOT NULL COMMENT '产品状态', `expend_rate` DECIMAL ( 16, 2 ) NOT NULL COMMENT '利率', `effective_time` datetime NOT NULL COMMENT '标的发布的有效期(时间戳)', `create_time` datetime NOT NULL COMMENT '创建时间', `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY ( `id` ), UNIQUE KEY `idx_uuid` ( `uuid` ), KEY `idx_user_uuid` ( `user_uuid` ), KEY `idx_create_time` ( `create_time` ) ) ENGINE = INNODB AUTO_INCREMENT = 2 DEFAULT CHARSET = utf8 COMMENT = '产品基础信息表';
-
二.优化策略
- 字段的选取(最小原则)
- 越小的数据类型,通常处理的速度更快,因为占用的磁盘,内存,CPU缓存更少,处理时需要的CPU周期也更短。对于不确定的数值范围,选取最小的可储蓄类型。
数据类型 | 数值范围 | 字节 | 常用场景 |
tinyint unsigned | 0-255 | 1 | 表示人的年龄等 |
smallint unsigned | 0-65535 | 2 | 乌龟年龄等 |
int unsigned | 0-42.9亿 | 4 | 恐龙化石时间等 |
bigint unsigned | 0-10^19 | 8 | 太阳的年龄 |
char(N) | 最大为N个字节 | N | 对于固定长度的字段(比如人名) |
varchar(N) | 最大为N个字节,N尽量小于255/5000 | N | 根据业务需求 |
datatime | 1601-9999年 | 8 | 常用的日期储蓄 |
timestamp | 1601-9999年 | 4 | 有自动更新机制,update_time |
- 简单原则
- 简单的数据类型需要的CPU周期更短,整形比字符整体的操作代价少很多,建议IP的储蓄用整型。
- 尽量避免使用NULL
- 通常情况下,字段设置为NOT NULL,NULL为默认属性,对于查询来说有NULL的列,MySql优化很难,查询起来会更麻烦,占用的储蓄空间可更多
三.SQL优化(常用)
- 查询优化,在where以及order by 涉及的字段上建立索引。
- 避免使用where 属性 = null,这样MySQL会进行全局查询。
- 避免在where使用 != 或 < > 等字段。
- 避免where 与 or 进行连接查询,使用 union all 进行代替.
- 避免使用 in 和 not in 等,连续的范围尽量用between
- 避免使用like "%XX%".
- 避免where 的字段使用表达式操作.where num/2 = x等
- 避免where的字段使用函数操作。
- 字段含有大量的相同值比如(sex..),索引的优化没有效果,,MySQL根据字段的内容进行优化,因此进行建立索引;
- 索引提高的select 的效率,但降低了insert ,update的效率,建议索引最多建立6个.
- 字段的内容如果都为数字的话,避免使用字符作为属性,字符根据每个字符进行比较,数字只会比较一次;
- select * from table,尽量使用字段代替 * ,减少查询量,即使需要查询字段也要用全部字段代替 *。