深入学习MySQL 03 Schema与数据类型优化

Schema是什么鬼

schema就是数据库对象的集合，这个集合包含了各种对象如：表、视图、存储过程、索引等。为了区分不同的集合，就需要给不同的集合起不同的名字，默认情况下一个用户对应一个集合，用户的schema名等于用户名，并作为该用户缺省schema。所以schema集合看上去像用户名。

如果把database看作是一个仓库，仓库很多房间（schema），一个schema代表一个房间，table可以看作是每个房间中的储物柜，user是每个schema的主人，有操作数据库中每个房间的权利，就是说每个数据库映射的user有每个schema（房间）的钥匙。

选择优化的数据类型

选择正确的数据类型对于获得高性能至关重要,一下3个原则能有助于更好的选择。

1.更小的通常更好 更小的数据类型通常更快,因为它们占用更少的磁盘,内存,CPU。

2.简单就好 简单数据类型的操作通常需要更少的CPU周期。例如:整型比字符操作代价更低,因为字符集和校对规则使字符比较比整型比较更复杂。

3.尽量避免NULL 通常情况下最好制定列为NOT NULL,除非真的需要存储NULL值

可为NULL列的缺点:

• 使得索引,索引统计和值比较都更复杂
• 使用更多的存储空间,在MySQL中也需要特殊处理

数据类型

整数

存储整数可以使用这几种:TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT。分别使用8,16,24,32,64为存储空间。

整数类型可选的UNSIGNED属性,表示不允许负值,这大致可以使正数的上限提高一倍。例如:TINYINT UNSIGNED可以存储的范围是0~255,而TINYINT的存储范围是-128 ~ 127

有符号和无符号类型使用相同的存储空间,且具有相同的性,因此根据实际情况选取即可

实数:带小数的数

3种数据类型可以存储实数:float,double,decimal

float和double存储的数据有可能不准确,decimal可存储精确小数,存储财务数据或经度要求高时使用decimal

float和double不准确的原因:mysql中float和double分别分配了32位、64位的存储空间,当float、double类型的数据转换为二进制时,第32位/64位之后的数都会被截断,从而造成数据有可能不准确

decimal的存储需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL。在数据量比较大的时候，可以考虑使用BIGINT代替DECIMAL，将对应的值扩大N倍。

字符串

char:

• 长度范围:0～255
• 存储:固定长度,存储数据未达到指定长度时,右边填充空格以达到指定的长度;当查询CHAR值时，尾部的空格被删除掉。
• 优点:效率高

varchar:

• 长度范围:0~65535
• 存储:可变长度,保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节);存储数据未达到指定长度时不进行填充;当值保存和检索时尾部的空格仍保留
• 优点:节省空间

需要分情况来决定: 当确定字符串为定长、数据变更频繁、数据检索需求少时,使用char; 当不确定字符串长度、对数据的变更少、查询频繁时,使用varchar。

大数据

blob:

• 数据类型:tinyblob、smallblob、blob、mediumblob、longblob
• 存储:采用二进制方式存储,无排序规则和字符集

text:

• 数据类型:tinytext、smalltext、text、mediumtext、longtext
• 存储:采用字符方式存储,有排序规则和字符集

MySQL会把每个blob和text当做独立的对象处理,存储引擎存储时会做特殊处理,当值太大,innoDB使用专门的外部存储区域进行存储,行内存储指针,然后在外部存储实际的值；

mysql对他们的列排序：只对每列前max_sort_length字节排序,且不能将列全部长度的字符串进行索引

日期和时间

datetime:

• 范围:1001年~9999年
• 存储:8个字节存储,以YYYYMMDDHHMMSS的格式存储,与时区无关

timestamp:

• 范围:1970年~2038年
• 存储:4个字节存储,存储格式与UNIX时间戳相同,与时区有关

1.通常尽量使用timestamp,因为它的空间效率高

2.可以使用BIGINT类型存储微秒级别的时间戳

选择标识符(identifier，主键)

• 整数类型通常是标识列最好的选择,因为它们很快并且可以使用AUTO_INCREMENT
• 应该避免使用字符串类型作为标识列,因为它们很消耗空间,并且通常比数字类型慢
• 对于完全"随机"的字符串也需要多加注意。例如：MD5(),SHAI()或者UUID()产生的字符串。这些函数生成的新值也任意分布在很大空间内，这会导致INSERT和一些SELECT语句很缓慢
• 要确保在所有关联表中的外键与主键使用同样的类型。用不同数据类型可能导致性能问题或在比较操作时隐式类型转换也可能导致很难发现错误。

另外

IP地址实际是32位无符号整数,MySQL提供INET_ATON()和INET_NTOA()函数在这两种表示方法之间转换。(inet_ntoa(3507806248) --> 209.20.224.40 )

范式和反范式

通常建议使用范式化设计,因为范式化通常会使得执行操作更快。但这并不是绝对的,范式化也是有缺点的,通常需要关联查询，不仅代价昂贵,也可能使一些索引策略无效。

所以,我们有时需要混同范式化和反范式化,比如一个更新频率低的字段可以冗余在别的表中,避免关联查询

缓存表和汇总表

• 缓存表：存储那些可以比较容易的从schema其他表获取(但每次获取速度缓慢)数据的表
• 汇总表：保存的是使用GROUP BY语句聚合数据的表。实时计算统计值是很昂贵的操作。

在使用缓存表和汇总表时,必须决定是实时维护数据还是定期重建。哪个更好依赖于应用程序,但是定期重建并不只是节省资源,可以保持表不会有很多碎片,以及完全顺序组织的索引。

总结

• 使用小而简单的合适数据类型
• 尽量避免使用NULL值
• 关联条件中使用的列尽量使用相同的数据类型
• 尽量使用整型做主键
• 切勿过度范式化,范式化与反范式化混用有时是最好的选择

参考: 《高性能MySql》 https://www.cnblogs.com/csniper/p/5509620.html http://blog.leanote.com/post/weibo-007/mysql_float_double_decimal https://www.cnblogs.com/Jtianlin/p/5143873.html