高性能MySql进化论(二):数据类型的优化_下

页面导航:首页 > 数据库 > Mysql > 高性能MySql进化论(二):数据类型的优化_下

高性能MySql进化论(二):数据类型的优化_下

来源: 作者: 时间:2016-02-05 10:57 【

高性能MySql进化论(二):数据类型的优化_下高性能MySql进化论(一):数据类型的优化_上http://www.2cto.com/database/201309/240780.html BLOB/TEXT在实际的应用程序中往往需要存储两种体积较大的数据,一
高性能MySql进化论(二):数据类型的优化_下
 
高性能MySql进化论(一):数据类型的优化_上
http://www.2cto.com/database/201309/240780.html
 
·        BLOB/TEXT
在实际的应用程序中往往需要存储两种体积较大的数据,一种是较大的Binary数据,e.g. 一张10M的图片,另外一种是 较大的文本 e.g.一篇几万字的文章。在Oracle中有BOLB和CLOB来应对这两种数据,而在MySQL中对应的是BLOB以及TEXT.
鉴于这两种数据类型的特殊性,在MySQL中对BLOB以及TEXT的存储和操作做了特殊的处理:
          1) BLOB/TEXT 的值往往是作为对象来处理,这些对象有自己的ID,以及独立的存储空间
          2) BLOB/TEXT的值被用来排序的时候,只有前N个字节会被使用,N 对应的是中的一个常量值 (max_sort_length), 如果你想指定更多的字节被用来排序,那么你可以增加max_sort_length的值或者是使用ORDER BY SUBSTRING(column, length)函数来处理
          3) 当BLOB/TEXT 被用作索引或者排序的时候,不能使用整个字段的值.
在万不得已的情况下要避免把BOLB/TEXT用作索引或是排序
 
因为MySQL 的Memory 引擎不支持BLOB 和TEXT 类型,所以,如果查询的过程中涉及到BLOB /TEXT,则需要使用MyISAM 磁盘临时表,即使只有几行数据也是如此(在最新的Percona Server 的Memory 引擎支持BLOB 和TEXT 类型)。
Memory引擎频繁的访问磁盘临时表会产生严重的性能开销,最好的解决方案是尽量避免使用BLOB 和TEXT 类型。如果实在无法避免,有一个技巧是在所有用到BLOB 字段的地方都使用SUBSTRING(column, length) 将列值转换为字符串(在ORDER BY 子句中也适用),这样就可以使用内存临时表了。但是要确保截取的子字符串足够短,不会使临时表的大小超过max_heap_table_size 或tmp_table_size,超过以后MySQL 会将内存临时表转换为MyISAM 磁盘临时表。
 
最坏情况下的长度分配对于排序的时候也是一样的,所以这一招对于内存中创建大临时表和文件排序,以及在磁盘上创建大临时表和文件排序这两种情况都很有帮助。例如,假设有一个1 000 万行的表,占用几个GB 的磁盘空间。其中有一个utf8字符集的VARCHAR(1000) 列。每个字符最多使用3 个字节,最坏情况下需要3 000字节的空间。如果在ORDER BY 中用到这个列,并且查询扫描整个表,为了排序就需要超过30GB 的临时表
 
·       DATETIME/TIMESTAMP
在MySQL中包含两种时间格式 DATETIME,TIMESTAMP, 通常在使用的过程中这两种类型区别不是很大,但是在细节上还是存在差别
 
DATETIME
TIMESTAMP
占用空间
8Bytes
4Bytes
可表示区间(年)
1001-9999
1970-2038
是否与时区有关
存储内容
日期和时间封装到格式为YYYYMMDDHHMMSS 的整数中
保存了从1970 年1 月1 日午夜(格林尼治标准时间)以来的秒数,它和UNIX 时间戳相同
显示方式是否与时区有关
否(ANSI 标准定义的日期和时间表示方法)
特殊属性
 
(1)TIMESTAMP 列默认为NOT NULL,默认值为当前时间
因为TMESSTAMP会占用更小的存储空间,所以可以使用它作为默认的时间格式
 
·       ENUM
这种类型的字段主要是通过枚举的方式来保存列的值,因为在使用的过程中会涉及到枚举位置与实际值的转换,所以对于整体的性能可能会有一定的影响,而且枚举的值是存储在.frm(数据表结构定义文件)中,所以当建立完ENUM的列后,如果你想对EMUM的内容进行更新,也就相当于做了表结构的更新。
下面是个简单建立ENUM列的例子:
 
> CREATE TABLEenum_test(
->  e ENUM('fish', 'apple', 'dog') NOT NULL
-> );
mysql> INSERT INTOenum_test(e) VALUES('fish'), ('dog'), ('apple');

 

 
·        BIT
如果需要让你设计一个表示布尔值的字段要求占用的空间最少,你会如何去设计?用INT,还是用CHAR(1)?相比INT以及CHAR(1)而言BIT(1)或许是个更好的选择,因为它占用的空间只是一个BIT。它可以通过BIT(N)的方式来表达多个BIT的值,这种方式最大支持到BIT(64)。
 
在MySQL5.0之前的版本中,BIT被认为是和TINYINT等同的,在新的版本中被作为两种完全不同的类型来对待。
 
当你把一个BIT字段从数据库中检索出来显示在控制台上时,值会被显示成ASCII编码,当字段的值出在一个数字运算的上下文时,它会被当成是BIT的十进制的值,下面的一个例子可以很清楚的说明这两种情况
 
mysql>CREATE TABLE bittest(a bit(8));
mysql> INSERT INTObittest VALUES(b'00111001');
mysql> SELECT a, a+ 0 FROM bittest;
+------+-------+
| a | a + 0 |
+------+-------+
| 9 | 57 |
+------+-------+

 

 
上面的这个例子或许会让你感到困惑,很有可能让你不再想使用这种机制来存储单个的位,作为一种替代方案可以把相关字段设置成CHAR(0),NULL用来表示False,””(Empty String)表示True
 
Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<