mysql中有sa_SA工作-mysql设计规范

表的设计

字段尽量设置not null，mysql的null值和空字符串不一样，null值占用空间，空字符串不占用空间。Null值占用空间，比较时会参与比较，但b树索引不会存储null值，因此当索引的字段包含null值，效率会下降。

一般没有业务因素影响，采用数值型的自增长列作为主键。

Mysql的字段设计需要集约化，越小的占用存储空间越好，能数值不字符，能日期不字符。

注意Mysql的varchar类型要计算好长度，因为mysql在表被查询后会把表信息缓存到内存中，在内存中申请是按照最大长度申请。

将不常用的字段或大字段从主表拆分出来放到子表，通过主键关联访问；对于核心访问频度极高的表，需要反范式设计，增加冗余字段，减少与其它表的join操作。

索引的设计

主键字段尽量短，数值型是较好的选择。Mysql innodb单列索引默认情况下限制长度767，使用utf8字符集，长度限制为255。

当查询包含的条件和字段很少时，可以创建覆盖索引。这样数据可以全部在索引中获得，将不再回表。

当需要在长文本字段上创建索引时，可以考虑增加一个虚拟hash列，采用crc32计算一个hash值，针对这个列创建索引。只用这个索引做等值查询，索引的空间占会小很多。

组合索引在多列上创建，单列索引在一个列上创建。

查询使用索引的条件不同一般组合索引需要按照“最左前缀”来执行查询，并不是每个列都需要覆盖，只是从左边的列开始组合。

例如有索引key(a,b,c)

where a=xx and b=xx and c=xxx 此语句可以用到索引

where b=xx and a=xx and c=xxx 同上，顺序没有关系，同样能用到索引

where a=xx and b=xx 可以用到索引

where a=xx and c=xx 可以用到索引

where b=xx and c=xx 用不到索引 where b=xx 用不到索引 where c=xx 用不到索引

Innodb只支持索引组织表，为了提高系统的性能，增加查询速度，所以要建立索引.

好处：   1) 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性

2) 大大加快数据的检索速度

3) 可以加速表和表之间的连接，特别是在实现数据的参考完整性方

4) 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间

开发管理项目中的索引应用，使用到普通索引、唯一索引、主键索引、组合索引.

例如：

1)sp_issue.product产品字段，时常做为查询条件：设置字段不为NULL，且增加索引

ALTER TABLE sp_issue MODIFY product VARCHAR(100) NOT NULL;

ALTER TABLE sp_issue ADD INDEX INX_PRODUCT (PRODUCT);

2)人员权限表sp_user_priv无主键，留着普通索引，建联合主键.

alter table sp_user_priv add constraint pk_sp_user_priv primary key (user_code,priv_type,priv_value);

序号

常用Oracle数据类型对应MySQL数据类型

Oracle

MySQL

1

TIMESTAMP(6)

DATETIME(6)或timestamp(6)

2

NUMBER

int or bigint

3

CHAR

char

4

CLOB

text or varchar

5

DATE

datetime

6

RAW

char

7

VARCHAR2

vharchar

8

BLOB

blob

数值型字段尽量不用字符类型存储

原因：UTF-8 下：mysql下 VARCHAR(20)  ;表明字段可以存放20个字符，若中文则是占用60个byte(1中文=3BYTE)，若英文或者数字则为占用20个byte。

INT 大小为4 字节(定义)， 存储的数值范围是 (0，4 294 967 295)， 即只要存储的数值是这个范围内，永远都是占用4个BYTE。

假设 存放 1000这样一个值， 如果存放到数据类型VARCHAR中， 那么就是 1000个byte,就是1kb大小

而存放到 int中，就只是占用4个byte，即只有0.004KB，差距很大。

字段不设置为NULL

当字段时常做为查询条件时，设置改字段不为NULL，可以设置默认值来填充方式.

原因：1)MySQL的NULL不等于空字符串；WEB开发的时候，如果有的地方插入记录是指定空字符串，有的地方插入记录没有指定值，查询=’’的时候就会过滤掉为(NULL)的数据

2)索引不会包含有NULL值的列 只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL

例如：设置sp_issue.product 不为NULL

ALTER TABLE sp_issue MODIFY product VARCHAR(100) NOT NULL;

2.1 数值型字段尽量不用字符类型存储

原因：UTF-8 下：mysql下VARCHAR(20)  ;表明字段可以存放20个字符，若中文则是占用60个byte(1中文=3BYTE)，若英文或者数字则为占用20个byte。

INT 大小为4字节(定义)， 存储的数值范围是(0，4 294 967 295)， 即只要存储的数值是这个范围内，永远都是占用4个BYTE。

假设 存放 1000这样一个值， 如果存放到数据类型VARCHAR中， 那么就是1000个byte,就是1kb大小

而存放到 int中，就只是占用4个byte，即只有0.004KB，差距很大。