mysql 优化（1）表的优化与列类型选择

表的优化:

1: 定长与变长分离

如 id int, 占4个字节, char(4)占4个字符长度,也是定长, time 即每一单元值占的字节是固定的.

核心且常用字段,宜建成定长,放在一张表.  而varchar, text,blob,这种变长字段,适合单放一张表,用主键与核心表关联起来.

sql 执行查询100000条数据 会因为所有都是定常而跳过的非常迅速  

2:常用字段和不常用字段要分离.

需要结合网站具体的业务来分析,分析字段的查询场景,查询频度低的字段,单拆出来.

3:在1对多,需要关联统计的字段上,添加冗余字段.  减少关联查询

看如下BBS的效果 统计的发帖数  不要数 而是通过在栏目下的添加冗余字段，每次发文章更新文章数+1 这样就会减少查询强度

列选择原则:

1:字段类型优先级 整型> date,time >enum,char>varchar > blob,text

列的特点分析:整型:定长,没有国家/地区之分,没有字符集的差异

比如 tinyint 1,2,3,4,5 <-->  char(1)  a,b,c,d,e,   从空间上,都是占1个字节,但是order by 排序,前者快

原因: 后者需要考虑字符集与校对集(就是排序规则)

time定长,运算快,节省空间. 考虑时区,写sql时不方便 where > ‘2005-10-12’; 时间存int 型；

enum: 能起来约束值的目的, 内部用整型来存储,但与char联查时,内部要经历串与值的转化

Char 定长, 考虑字符集和(排序)校对集

varchar, 不定长 要考虑字符集的转换与排序时的校对集,速度慢.

text/Blob 无法使用内存临时表(排序等操作只能在磁盘上进行)

性别:  以utf8为例

char(1) , 3个字长字节

enum(‘男’,’女’);  // 内部转成数字来存,多了一个转换过程

tinyint() ,  // 0 1 2 // 定长1个字节.

sql 优化书籍 《MYSQL 高性能优化》

关于date/time的选择,大师的明确意见,直接选int unsgined not null ,存储时间戳http://www.xaprb.com/blog/2014/01/30/timestamps-in-mysql/

时间--->存成整型

2: 恰好够用就行,不要慷慨(如smallint,varchar(N))

原因: 大的字段浪费内存,影响速度,

以年龄为例 tinyint unsigned not null ,可以存储255岁,足够.用int浪费了3个字节

以varchar(10) ,varchar(300)存储的内容相同,但在表联查时,varchar(300)要花更多内存

3: 尽量避免用NULL()

原因: NULL不利于索引,要用特殊的字节来标注.

在磁盘上占据的空间其实更大.(mysql5.7已对null做的改进,但查询仍是不便)

实验:

可以建立2张字段相同的表,一个允许为null,一个不允许为Null,各加入1万条,查看索引文件的大小.可以发现,为null的索引要大些.(mysql5.5里,关于null已经做了优化,大小区别已不明显)

另外: null也不便于查询,

where 列名=null;   

where 列名!=null;都查不到值,

where 列名 is null  ,或is not null才可以查询.

create table dictnn (

id int,

word varchar(14) not null default '',

key(word)

)engine myisam charset utf8;

 

create table dictyn (

id int,

word varchar(14),

key(word)

)engine myisam charset utf8;

 

alter table dictnn disable keys;

alter table dictyn disable keys;

 

insert into dictnn select id,if(id%2,word,'') from dict limit 10000;

insert into dictyn select id,if(id%2,word,null) from dict limit 10000;

 

alert table dictnn enable keys;

alter table dictyn enable keys;

 

 

Enum列的说明

1: enum列在内部是用整型来储存的

2: enum列与enum列相关联速度最快

3: enum列比(var)char的弱势---在碰到与char关联时,要转化.要花时间.

4: 优势在于,当char非常长时,enum依然是整型固定长度.

当查询的数据量越大时,enum的优势越明显.

5: enum与char/varchar关联,因为要转化,速度要比enum->enum,char->char要慢,

但有时也这样用-----就是在数据量特别大时,可以节省IO.

试验:

create table t2 (

id int,

gender enum('man','woman'),

key(gender)

)engine myisam charset utf8;

 

create table t3 (

id int,

gender char(5) not null default '',

key(gender)

)engine myisam charset utf8;

 

alter table t2 disable keys;

alter table t3 disable keys;

 

insert into t2 select id,if(id%2,'man','woman') from dict limit 10000;

insert into t3 select id,if(id%2,'man','woman') from dict limit 10000;

 

alter table t2 enable keys;

alter table t3 enable keys;

mysql> select count(*) from t2 as ta,t2 as tb where ta.gender=tb.gender
mysql> select count(*) from t3 as ta,t3 as tb where ta.gender=tb.gender

 

列<---->列	时间
Enum<--->enum	10.53
Char<---->char	24.65
Enum<---->char	18.22

如果t2表的优势不明显,加大t3的gender列,char(15), char(20)...

随着t3 gender列的变大,t2表优势逐渐明显.

 

原因----无论enum(‘manmaman’,’womanwomanwoman’)枚举的字符多长,

内部都是用整型表示, 在内存中产生的数据大小不变,

而char型,却在内存中产生的数据越来越多.

 

总结: enum 和enum类型关联速度比较快

     Enum 类型 节省了IO