表的优化:
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
以上就是mysql 优化(1)表的优化与列类型选择的内容,更多相关内容请关注PHP中文网(m.sbmmt.com)!