MySQL数据类型与优化

关于数据类型的优化

1、假如只需要存0~255之间的数,无负数,应使用tinyint unsigned(保证最小数据类型)
2、如果长度不可定,如varchar,应该选择一个你认为不会超过范围的最小类型
比如: varchar(20),可以存20个中文、英文、符号,不要无脑使用varchar(150)
3、整形比字符操作代价更低。比如应该使用MySQL内建的类型(date/time/datetime)而不是字符串来存储日期和时间
4、应该使用整形存储IP地址,而不是字符串
5、尽量避免使用NULL,通常情况下最好指定列为NOT NULL,除非真的要存储NULL值
6、DATETIME和TIMESTAMP列都可以存储相同类型的数据:时间和日期,且精确到秒。然而TIMESTAMP只使用DATETIME一半的内存空间,并且会根据时区变化,具有特殊的自动更新能力。另一方面,TIMESTAMP允许的时间范围要小得多,有时候它的特殊能力会变成障碍


整数类型 
1、TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT分别使用8(-127~127或0~255)、16(0~65535)、24(0~1600万)、32、64位存储空间。 
2、整数类型有可选的UNSIGNED属性,表示不允许负值,这大致可以使正数的上限提高一倍。有符号和无符号类型使用相同的存储空间,有相同的性能,具体情况具体考虑。
3、MySQL可以为整数类型指定宽度,例如INT(11),对大多数应用这是没有意义的,INT(1)和INT(20)相同,它不会限制值的合法长度,除非使用zerofill。


实数类型
1、实数是带有小数部分的数字。然而它们不只是为了存储小数部分,还可以使用DECIMAL存储比BIGINT还大的整数。MySQL既支持精确类型,也支持不精确类型。
2、FLOAT和DOUBLE类型支持使用标准的浮点运算进行"近似"计算,注意!是近似计算。
3、DECIMAL类型用于存储精确的小数。但在MySQL4.1 以及更早版本只能使用"浮点运算"来实现DECIMAL的计算,这样可能会导致精度损失。因为CPU不支持对DECIMAL的直接计算,在MySQL5.0及更高版本中,MySQL服务器自身实现了DECIMAL的高精度计算。相对而言,CPU直接支持原生的浮点运算,所有浮点运算明显更快。
4、浮点和DECIMAL类型都可以指定精度。对于DECIMAL列,可以指定小数点前后所允许的最大位数,这会影响列的空间消耗。
5、MySQL5.0或更高的版本,将数字打包保存到一个二进制字符串中(每4个字节存9个数字)。DECIMAL(18,9)使用了9个字节,小数点左右各4个字节,小数点1个字节。

VARCHAR和CHAR类型
VARCHAR类型
1、VARCHAR和CHAR是两种最重要的字符串类型。
2、VARCHAR类型需要使用1或2个额外字节记录字符串的长度:如果列的最大长度小于或等于255字节,则只使用1个字节表示,否则使用2个字节。
3、INNODB会把过长的VARCHAR存储为BLOB(二进制方式存储的字符串)。
CHAR类型
1、CHAR类型是定长的,对于不确定长的字符串,VARCHAR更省空间,因为它并定长类型更节省空间,仅使用必要的空间。
2、CHAR类型适合存储很短的字符串,或者所有值都很接近同一个长度,例如MD5。
3、经常变更的数据,CHAR也比VARCHAR更好,因为定长的CHAR类型不易产生碎片。
4、在MySQL5.0或更高的版本,VARCHAR类型在存储和检索的时候会保留末尾空格,但在4.1或更老的版本,会剔除末尾的空格。而CHAR类型在新老版本都会查询末尾的空格。

BLOB和TEXT类型
1、BLOB和TEXT类型都是为了存储很大的数据而设计的字符串数据类型,只是存储方式不同,分别采用二进制和字符方式存储。
2、实际上,它们分别属于两组不同的数据类型家族:字符类型是TINYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT、LONGTEXT;对应的二进制类型是TINYBLOB、SMALLBLOB、BLOB、MEDIUMBLOB、LONGBLOB、BLOB。其中BLOB是SAMLLBLOB的同义词,TEXT是TINYTEXT的同义词。
3、与其他类型不同,MySQL把每个BLOB和TEXT值当做一个独立的对象处理。存储引擎在存储时通常会特殊处理。当BLOB和TEXT值太大时,InnoDB会使用专门的"外部"存储区域来进行存储,此时每个值在行内需要1~4个字节存储一个指针,然后在外部存储趋于存储实际的值。
4、BLOB和TEXT家族之间仅有的不同是BLOB类型存储的是二进制数据,没有排序规则或字符集,而TEXT类型有字符集和排序规则。
5、MySQL对BLOB和TEXT列进行排序与其他类型是不同的:它只对每个列的最前max_sort_length字节而不是整个字符串做排序。如果只需要排序前面一小部分字符,则可以减小max_sort_length的配置,或者使用ORDER BY SUBSTRING(column, length),将列值转换为字符串(在ORDER BY子句中也适用),这样就可以生成内存临时表了。这招对内存中创建大临时表和文件排序,以及在存盘上创建大临时表和文件排序这两种情况都很有帮助。
6、MySQL不能将BLOB和TEXT列全部长度的字符串进行索引,也不能使用这些索引消除排序。
7、PLUS:如果EXPLAIN执行计划的Extra列包含"Using temporary",则说明这个查询使用了隐式临时表。

枚举(ENUM)
1、有时候可以使用枚举类型代替常用的字符串类型。枚举列可以把一些不重复的字符串存储成一个预定义的集合。MySQL在存储枚举时非常紧凑,会根据列表值的数量压缩到一个或两个字节中。MySQL在内部会将每个值在列表中的位置保存为整数,并且在表的.frm文件中保存"数字-字符串"映射关系的"查找表"。
2、枚举字段是按照内部存储的整数而不是定义的字符串进行排序的。绕过这种限制的方式是按照需要的顺序来定义枚举列,另外也可以在查询中使用FIELD()函数显式地指定排序顺序,但这会导致MySQL无法利用索引消除排序,如果定义时候就是按照字母顺序,就没有那么做的必要了。
3、枚举最不好的地方就是字符串列表是固定的,添加或删除字符串必须使用ALTER TABLE。对于一系列将来可能会改变的字符串,枚举可能不是一个很好的选择,除非能接收在列表末尾添加元素。
4、由于MySQL把每个枚举值都保存为整数,并且必须进行查找才能转换为字符串,所以枚举列有一些开销,通常枚举列都比较小,所以开销还可以控制,在特定情况下,把CHAR/VARCHAR列与枚举列进行关联可能会直接比关联CHAR/VARCHAR列更慢。

日期和时间类型
MySQL可以使用许多类型来保存日期和时间值,例如YEAR和DATE。MySQL能存储的最小时间粒度为秒(MariaDB支持微妙级别的时间类型)。
DATETIME
1、这个类型能保存大范围的值,从1001年到9999年,精确度为秒。
2、它把日期和时间装到格式为YYYYMMDDHHMMSS的整数中,与时区无关。使用8个字节的存储空间。
3、默认情况下,MySQL以一种可排序的、无歧义的格式显示DATETIME值,例如"2019-11-05 21:35:54"。这是ANSI标准定义的日期和时间表示方法。

TIMESTAMP
1、TIMESTAMP类型保存了从1970年1月1日午夜(格林尼治时间)以来的秒数,它和UNIX时间戳相同。
2、TIMESTAMP只使用4个字节存储空间,它只能表示从1970年到2038年。
3、MySQL提供了FROM_UNIXTIME()函数把UNIX时间戳转换为日期,提供了UNIX_TIMESTAMP()函数把日期转换为Unix时间戳。
4、TIMESTAMP显示的值依赖于时区,如果在多个时区存储和访问数据,TIMESTAMP和DATETIME的行为很不一样。前者提供的值与时区有关系,后者则保留文本表示的日期和时间。
5、默认情况下,如果插入时没有指定第一个TIMESTAMP的值,MySQL则设置这个列的值为当前时间。在插入一行记录时,MySQL默认也会更新第一个TIMESTAMP列的值(除非在UPDATE语句中明确指定了值)。
6、TIMESTAMP列默认为NOT NULL。
总结:尽量使用TIMESTAMP,因为它比DATETIME空间效率更高。

 

标识符
1、整数通常是标志符最好的选择,因为它们很快并且可以使用AUTO_INCREMENT。
2、应避免使用字符串类型作为标志符,因为它们很消耗空间,并且通常比数字类型慢,有其是在MyISAM表中使用字符串作为标识符要特别小心,MyISAM默认对字符串压缩索引,这会导致查询慢很多。
3、对于完全"随机"的字符串也需要多加注意,例如MD5()、SHA1()或者UUID()产生的字符串。这些函数生成的新值会任意分布在很大的空间内,这会导致INSERT以及一些SELECT语句变得很慢。因为新插入的值会随机写索引到不同的位置,所以使得INSERT语句很慢。这会导致页分裂、磁盘随机访问,以及对于聚簇索引存储引擎产生聚簇索引碎片。SELECT语句会变得更慢,因为逻辑上相邻的行会分布在磁盘和内存的不同地方。
4、UUID生成的值与加密散列函数例如SHA1()生成的值有不同的特征;UUID值虽然分布不均匀,但还是有一定的顺序,尽管如此都不如递增的整数好用

特殊类型数据(IP地址)
1、对于IPV4地址,人们常用VARCHAR(15)列来存储IP地址。然而它们实际上是32位无符号整数,不是字符串。用小数点分成四段的表示方法只是为了让人们阅读更容易。所以应该用无符号整数存储IP地址。MySQL提供INET_ATON()和INET_NTOA()函数在这两种表示方法之间转换。

MySQL schema设计中的陷阱
1、太多的列。MySQL的存储引擎API工作时需要在服务层和存储引擎层之间通过行缓冲格式拷贝数据,然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。MyISAM的定长行结构实际上与服务器层的行结构正好匹配,所以不需要转换。然而MyISAM的变长行结构和InnoDB的行结构总是需要转换,转换的代价依赖于列的数量。
2、太多关联。MySQL限制了每个关联操作最多只能有61张表,但单个查询最好在12个表以内做关联。
3、防止过度使用枚举。
4、尽可能避免使用NULL值,可以使用其他值替代。

©️2020 CSDN 皮肤主题: 终极编程指南 设计师:CSDN官方博客 返回首页