由于Mysql独有的特性和实现细节对性能的影响是很明显的,因为做好Mysql数据库的设计很关键。对于数据库设计,我们不得不提表字段的类型选择,由于Mysql支持的数据类型非常多,因此如何选择正确的数据类型对于获得高性能至关重要。不管要存储的数据是什么类型,我们都需要根据一些数据库设计原则来考虑。
选择数据类型的思考
更小的通常是更好的(一般情况下,应该尽可能使用正确存储数据的最小数据类型。)
为什么呢?
(1) 因为更小的数据类型通常更快,因为它们占用更少的磁盘、内存和CPU缓存,并且处理时需要的CPU周期也更短。
(2) 要确保没有低估需要存储的值的范围,更小是相对与数据类型的最大值范围来讲的。
(3) 如果无法确定哪个数据类型是最好的,就选择你认为不会超过范围的最小类型。
简单就好(简单数据类型的操作通常需要更短的CPU周期。)
为什么呢?下面有几个例子说明一下原因。
(1) 整型比字符串操作代价更低,因为字符串集和校对规则(排序规则)是的字符比较比整型比较更复杂。
(2) 存储日期和时间应该使用Mysql内建的类型(date,time,datatime)。
(3) IP地址的存储应该用整型(int)。
尽量避免 NULL (空值)
为什么呢?
(1) 很多表都包含可为NULL的列,就算程序并不需要保存NULL也是如此,这是因为列的默认属性就是可为NULL。通常情况下最好指定列NOT NULL,除非真的需要存储NULL。
(2) 如果查询中包含可为NULL的列,对于Mysql来说是很难优化的,因为NULL的列使得索引,索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间,在Mysql里也需要特殊处理。当可为NULL的列被索引时,每个索引记录需要一个额外的字节,在MyISAM里甚至还可能导致固定大小的索引变成可变大小的索引。
(3) 通常把可为NULL的列改为NOTNULL带来性能提升比较小,如果计划在列上建索引的话,就应该尽量避免设计成可为NULL的列。(也有一个例外,那就是在InnoDB中,会使用单独的位(bit)来存储NULL值,所以对稀疏数据有很好的空间效率。)
总结
在为列选择数据类型时,第一步需要确定合适的大类型(数字、字符串、时间等等),这通常是很简单的,那么下一步就是选择具体的类型了。
很多Mysql的数据类型可以存储相同类型的数据,只是存储的长度和范围不一样、允许的精度不同,或者需要的物理空间(磁盘和内存空间)不同。相同大类型的不同子类型数据有时候也有一些特殊的行为和属性。比如:DATATIME 和 TIMESAMP列都可以存储相同类型的数据(时间和日期)并且精确到秒,然而TIMESTAMP只使用DATATIME一半的存储空间,并且会根据时区变化,具有特殊的自动更新能力。另外TIMESTAMP允许的时间范围要小得多,有时候它的特殊能力会成为障碍,这都是我们开发者需要考虑的。
整数类型
有两个类型的数字:整数(whole number)和实数(real number)。
如果存储整数,可以使用这几种整数类型:TINNYINT(8)、SMALLINT(16)、MEDIUMINT(24)、INT(32)、BIGINT(64)。
整数类型有可选的的UNSIGNED属性,表示不允许为负值,这大致可以是正数的上限提高一倍。
比如:TINYINT UNSIGNED可以存储的范围是0~255,而TINYINT的存储范围是-127~128.
有符号和无符号类型使用相同的存储空间,并具有相同的功能.
因此可以根据实际情况选择合适的类型。
你的选择决定Mysql是怎么在内存和磁盘中保存数据的。
整数一般选择64位的BIGINT整数,即使在32位环境下也是如此。(但是一些聚合函数是例外,它们是使用DECIMAL或DOUBLE进行计算的)
Mysql可以为整数类型指定宽度。
比如:INT(11),对大多数应用这是没有意义的:它不会限制值的合法范围,只是规定了Mysql的一些交互工具(例如Mysql命令行客户端)用来显示字符的个数。对于存储和计算来讲,INT(1)和INT(20)是相同的。
一些第三方存储引擎(比如Infobright)有时也有自定义的存储格式和压缩方案,并不一定使用常见的Mysql内置引擎的方式。
实数类型
实数是带有小数部分的数字。
它们不只是未来存储小数部分,也可以使用DECIMAL存储比BIGINT还要大的整数。Mysql既支持精确类型,也支持不精确类型。DECIMAL类型用于存储精确的小数。
在Mysql5.0或者更高版本支持精确运算,而在Mysql4.1以及更早版本中使用浮点运算会出现异常(主要是精度的损失导致的)FLOAT和DECIMAL类型都可以指定进度。
对于DECIMAL列可以指定小数点前后所允许的最大位数,这会影响列的空间消耗。有很多方法可以指定FLOAT(浮点)列所需要的精度,这会使得Mysql悄悄选择了不同的数据类型,或者在存储时对值进行取舍,但是这些精度往往都是非标准的,所以一般建议只指定数据类型不指定精度。
由于需要额外的空间和计算开销,所以应该尽量只在对小数进行精确计算时才使用DECIMAL。比如存储财务数据,但是如果数据量比较大的时候,可以考虑使用BIGINT代替DECIMAL,将需要存储的货币单位根据小数的位数乘以相应的倍数即可。FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算。
字符串类型
Mysql支持多种字符串类型,每种类型还有很多变种。其中VARCHAR和CHAR是两种最主要的字符串类型。
注意:Mysql存储引擎存储CHAR或者VARCHAR值的方式在内存中和在磁盘上可能不一样,所以Mysql服务器从存储引擎读取的值可能需要转换为另外一种存储格式。
VARCHAR类型用于存储可变长字符串,是最常见的字符串数据类型。
VARCHAR比定长类型更节省空间,因为它仅使用必要的空间(越短的字符串使用越少的空间)。
VARCHAR需要使用1或2个额外字节记录字符串的长度。
VARCHAR节省了存储空间,所以对性能是有帮助的。
下面是一些VARCHAR适合使用的场景:
(1)字符串列的最大长度比平均长度大很多。
(2)列的更新很少,所以碎片不是问题。
(3)使用了像UTF-8这样复杂的字符集,每个字符都使用不同的字节数进行存储。
CHAR类型是定长的。(Mysql总是根据定义的字符串长度分配足够的空间)
CHAR适合存储很短的字符串,或者所有值都接近同一个长度。
和VARCHAR和CHAR类似的类型还有BINARY和VARBINARY,它们存储的都是二进制字符串。
注意:使用VARCAHR(5)和VARCHAR(200)存储“hello”的空间开销都是一样的,那么使用更短的列有什么优势呢?(事实证明有很大的优势)
更长的列会消耗更多的内存,因为Mysql通常会分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排序或者操作时会特别糟糕。在利用磁盘临时表进行排序时也同样糟糕。
注意:归根到底,最好的策略是只分配真正需要的空间。
BLOB和TEXT类型
BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型,分别使用二进制和字符方式存储。
实际上它们分别属于两组不同的数据类型家族:字符串类型有TINYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT、LONGTEXT;
二进制类型有TINYBLOB、SMALLBLOB、BLOB、MEDIUMBLOB、LONGBLOB;
ENUM类型
可以使用枚举(ENUM)代替字符串类型。很多时候建议使用枚举列代替常用的字符串类型。
(1)枚举列可以把一些不重复的字符串存储成一个预定义的集合。
(2)Mysql在存储枚举时非常紧凑,会根据列表值的数量压缩到一到两个字节中。
(3)Mysql在内部会将每个值在列表中的位置保存为整数,并且在表的.frm文件中保存“数字-字符串”映射关系的“查找表”。
注意:有一个令人吃惊的地方是,枚举字段是按照内部存储的整数而不是定义的字符串进行排序的。
注意:枚举最不好的地方是:字符串列表是固定的,添加或者删除字符串必须使用ALTER TABLE,因此对于一系列未来可能会改变的字符串,使用枚举并不是一个好主意,除非接受只能在列表末尾添加元素。
注意:由于Mysql把每个枚举值保存为整数,并且必须进行查找才能转换为字符串,所以枚举列有一些开销。
日期和时间类型
Mysql有很多类型可以保存日期和时间值,比如YEAR和DATE。
Mysql能存储的最小时间粒度为秒(MariaDB支持微秒级别的事件类型)。但是Mysql也可以使用微秒级别的粒度进行临时运算。
大部分时间类型都没有替代品,因此没有什么是最佳选择的问题。
接下来唯一的问题是保存日期和时间的时候需要做什么。
DATETIME
(1)这个类型能保存大范围的值,从1001年到9999年,精度为秒。
(2)DATETIME把时间和日期封装到格式为YYYYMMDDHHMMSS的整数中,与时区无关。
(3)DATETIME使用8个字节的存储空间。
TIMESTAMP
(1)TIMESTAMP类型保存了从1970年1月1日午夜以来的秒数,它和UNIX时间戳相同。
(2)TIMESTAMP只使用4个字节的存储空间,因此它的范围比DATETIME小得多。
(3)TIMESTAMP显示的值依赖时区。
DATETIME和TIMESTAMP的对比:
(1)默认情况下,如果插入时没有指定第一个TIMESTAMP列的值,Mysql则设置这个列的值为当前时间。(这是DATETIME没有的特性)
(2)在插入一行记录时,Mysql默认也会更新第一个TIMESTAMP列的值。
(3)TIMESTAMP列默认为NOT NULL,这与其他的数据类型不一样。
总结
(1)除了特殊行为之外,通常也应该尽可能使用TIMESTAMP,因为它比DATETIME空间效率更高。
(2)一般来讲不建议把UNIX时间戳保存为整数值,这不会带来任何收益,用整数保存时间戳格式通常不方便处理。
(3)如果需呀存储比秒更小粒度的日期和时间值,可以使用BIGINT类型存储微秒级别的时间戳,或者使用DOUBLE存储秒之后的小数部分,也可以用MariaDB替代Mysql。
位数据类型
BIT定义一个包含单个位的字段,BIT(2)存储2个位,最大长度是64个位。
注意:一般建议谨慎使用BIT类型,对于大部分应用来讲最好避免使用这种类型。
选择标识符
为identifier(标识列)选择合适的数据类型非常重要。
一般来讲更有可能用标识列与其他值进行比较,或者通过标识列寻找其他列。
当选择标识列的类型时,不仅仅需要考虑存储类型,还需要考虑Mysql对这种类型怎么执行计算和比较。
一旦选定了一种类型,要确保在所有关联表中都使用同样的类型。
在可以满足值的范围需求,并且预留未来增长空间的前提下,应该选择最小的数据类型。
注意:整数通常是标识列最好的选择,因为它们很快而且可以使用AUTO_INCREMENT。注意:ENUM和SET是最糟糕的选择了;如果可能也尽可能避免使用字符串作为标识列,因为它们很消耗空间并且通常比数字类慢。
以上就是对MySQL数据类型的认识的详细内容,更多请关注亿速云其它相关文章!