表的优化:
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.gendermysql> select count(*) from t3 as ta,t3 as tb where ta.gender=tb.gender
列列
时间
Enumenum
10.53
Charchar
24.65
爱图表
AI驱动的智能化图表创作平台
99 查看详情
Enumchar
18.22
如果t2表的优势不明显,加大t3的gender列,char(15),
char(20)…
随着t3 gender列的变大,t2表优势逐渐明显.
原因—-无论enum(‘manmaman’,’womanwomanwoman’)枚举的字符多长,
内部都是用整型表示, 在内存中产生的数据大小不变,
而char型,却在内存中产生的数据越来越多.
总结: enum 和enum类型关联速度比较快
Enum 类型 节省了IO
以上就是mysql 优化（1）表的优化与列类型选择的内容，更多相关内容请关注PHP中文网（www.php.cn）！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/419229.html
微信扫一扫 
支付宝扫一扫 
