开发者社区> 技术小美> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

高性能的MySQL(4)数据类型的优化

简介:
+关注继续查看

一、基本原则

1、更小的通常更好

更小的数据类型通常更快,因为占用更少的磁盘、内存和CPU缓存,并且处理时需要的CPU周期也更少。

但是要确保没有低估需要存储的值的范围,因为在schema中的多个地方增加数据类型的范围是个非常耗时的操作。

2、简单就好

例如,整数比字符串操作代价更低,应该用内建类型而不是字符串来存储时间和日期,用整型存储IP。

3、尽量避免NULL

可为NULL的列使用更多的存储空间,需要特殊的处理。特别是可为NULL的列被索引时,每个索引需要额外的字节,在Myisam引擎里甚至还可能导致固定大小的索引,所以计划建立索引的列,要避免使用NULL。

二、数据类型

1、整数类型

TINYINT、SMALLINT、MEDIUINT、INT、BIGINT,分别使用8、16、24、32、64位存储空间。范围从-2(N-1)次方到2(N-1)次方-1。

指定范围,只是一种显示,对存储和计算来说INT(1)和INT(20)是一样的。

2、实数类型

FLOAT和DOUBLE支持使用标准的浮点运算进行近似计算。

DECIMAL类型用于存储精确的小数

浮点类型在存储同样的范围的值是,通常比DECIMAL使用的空间更少。因为额外的空间和计算开销所以应该只在对小数进行精确计算时才使用,也可以考虑使用BIGINT代替DECIMAL,将小数的位数乘以相应的倍数即可。

3、字符串类型

VARCHAR类型用于存储可变长字符串,但是需要1-2个额外字节记录字符串的长度。由于行是变长的,在UPDATE时不同的引擎需要不同的额外处理工作。同时存储和检索时会保留末尾空格。

CHAR类型是定长的。存储时会删除末尾的空格。

对于字符串最大长度比平均长度大很多,列的更新很少,适合VARCHAR。对于经常更新的数据,CHAR更好,因为不容易产生碎片。

4、BLOB和TEXT类型

都是为存储大的数据而设计的字符串类型,分别采用二进制和字符方式存储,BLOB没有排序规则和字符集。

MySQL对BLOB和TEXT列进行排序和其他类型是不同的,它只针对每个列的最前max_sort_length字节排序,或者使用order by substring(column,length).

如果查询使用了BLOB和TEXT列并且需要使用隐式临时表,将不得不用到MYIASM磁盘临时表,这是很大的系统开销。如果无法避免,有一个技巧是在所有使用到BLOB字段的地方使用substring(column,length)将列值转换为字符串,这样就可以使用内存临时表了。但是要确保截取的字符串足够短,不会使临时表的大小超过max_heap_table_size和tmp_table_size,如果超过还是会使用磁盘临时表的。

如果explain的Extra列包含“Using temporary”,则说明用到了隐式临时表

5、ENUM类型

把一些不重复字符串存储成一个预定义的集合,非常节省空间。mysql在内部会将每一个值在列表中的位置保存为整数,并且在表的.frm文件中保存“数字-字符串”的映射关系的查找表。只有在进行查找时才会转化为字符串。

155358802.png

所以要尽量避免使用数字作为枚举值来存储。

另外一个要注意的是枚举字段是按照内部存储的整数来排序的,而不是字符串。

155645946.png

所以尽量按照需要的顺序来定义枚举列,也可以使用field()函数指定排序,但会导致无法利用索引消除排序。

160416557.png

枚举类型有另外一个好处。根据show table status命令结果看data_length列的值,可以让表的大小缩小1/3,同样,转换后主键也只有原来的一半了。

6、日期和时间类型

DATETIME:保存从1001年到9999年,精度为秒,把日期和时间封装到整数中,与时区无关,使用8个字节存储空间。

TIMESTAMP:保存从1970到2038年的时间戳,可以使用UNIX_TIMESTAMP()和FROM_UNIXTIME()来相互转换。

除了特殊行为之外,通常也应该尽量使用TIMESTAMP,因为空间效率更高。

7、BIT类型

存储一个或多个true/false值,最大64位。

mysql把bit当作字符串类型而不是数字类型。当检索bit(1)时,结果是包含二进制0或1的字符串,而不是ASCII码的0或1,然而,在数字场景中检索时,结果是位字符串转换成的数字。

164222614.png

所以不建议使用BIT类型。

8、SET类型

保存多个true/false值,修改列定义代价较高,需要alter table,而且无法使用索引。

可以使用整数列来进行按位操作,但是逻辑上的查询语句就会很难些,因人而异选择吧。

使用set

165952976.png

使用tinyint

170502841.png

标识列或者可能做外键的列,最好使用整型类型。






















本文转自shayang8851CTO博客,原文链接:http://blog.51cto.com/janephp/1308826,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
【数据库】必须知道的MySQL优化
MySQL是关系型数据库在高并发场景下,会承受巨大的压力,就需要进行优化了
0 0
MySQL数据库4种常用优化方式,sql语句书写优化规范
MySQL数据库4种常用优化方式,sql语句书写优化规范
0 0
【MySQL技术之旅】(6)一直都倾向于优化查询,这次学习一下优化Insert插入语句
【MySQL技术之旅】(6)一直都倾向于优化查询,这次学习一下优化Insert插入语句
0 0
MySQL对于千万级的大表要怎么优化(SQL语句的优化)?
1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引
0 0
mysql的schema和数据类型优化
mysql的schema和数据类型优化
0 0
Mysql优化-全面详解(学习总结---从入门到深化)
在程序的运行过程中,我们会发现这样的一个现象,随着程序运行 时间的不断推移以及数据量越来越大,程序响应的时间逐渐变慢, 程序变得卡顿,但最开始的时候并不是这样的,那是什么原因导致 的呢?
0 0
Mysql优化后的sql里出现了:where 1
Mysql优化后的sql里出现了:where 1
0 0
+关注
文章
问答
文章排行榜
最热
最新
相关电子书
更多
MySQL 技术大全:开发、优化与运维实战
立即下载
MySQL 5.7让优化更轻松
立即下载
MySQL 5.7优化不求人
立即下载