良好的逻辑设计和物理设计是高性能的基石,应该根据系统将要执行的查询语句来设计 schema,这往往需要权衡各种因素。
MySQL支持的数据类型非常多,选择正确的数据类型对于获得高性能至关重要。不管 存储哪种类型的数据,下面几个简单的原则都有助于做出更好的选择。
在为列选择数据类型时:
第一步需要确定合适的大类型:数字、字符串、时间等;
第二步是选择具体类型。很多MySQL的数据类型可以存储相同类型的数据,只是存储 的长度和范围不一样、允许的精度不同,或者需要的物理空间不同(相同大类型的不同子类型数据有时也有一些特殊的行为和属性)。
有两种类型的数字:整数和实数。
整数类型:
整数类型有可选的UNSIGNED属性,表示不允许为负数,大致可以使得正数的上限提高一倍。
有符号和无符号具有相同的存储空间和性能,根据实际情况选择合适的类型。
Tips:整数计算一般使用 64位的BIGINT整数,即使在32位环境也是如此,不同的数据可以决定的只有MySQL是怎么在内存和磁盘中保存数据的。 例如INT(11),对大多数应用这是没有意义的:它不会限制值的合法范围,只是规定了MySQL的一些交互工具(例如MySQL命令行客户端) 用来显示字符的个数。对于存储和计算来说,INT(1)和INT(20)是相同的
实数是带有小数部分的数字。
FLOAT和DOUBLE使用标准的浮点运算进行近似运算,如果需要知道浮点运算是 怎么计算的,则需要研究所使用的平台的浮点数的具体实现。
DECIMAL支持精确计算,但CPU不支持对DECIMAL的直接计算,MySQL自己实现了DECIMAL的高精度计算,所以DECIMAL在性能上要弱一些。
Tips:DECIMAL需要额外的空间和计算消耗,当数据量比较大时,可以考虑使用BITINT来代替,将存储的数据根据小数的位数乘以相应的倍数即可。这样可以解决浮点类型计算不准确,DECIMAL计算开销太大的问题。
VARCHAR用于存储可变长的字符,比定长更节省空间,越短的字符占用空间越少。 但有一种例外:当表使用“ROW_FORMAT=FIXED”创建时,每一行都会使用定长存储,浪费空间。
VARCHAR需要使用1或2个额外字节记录字符串的长度:如果列的最大长度小于或 等于255字节,则只使用1个字节表示,否则使用2个字节。假设采用latinl字符集, 一个VARCHAR(IO)的列需要11个字节的存储空间。VARCHAR(1000)的列则需要1002 个字节,因为需要2个字节存储长度信息
VARCHAR节省了存储空间,对性能也有帮助。但是由于行是变长的,所以UPDATE时可能比原来更长,这就需要MySQL为其额外再分配存储空间,导致UPDATE时开销比定长类型要大。
CHAR类型是定长的,MySQL会根据定义的长度去分配存储空间,所以不会有VARCHAR进行UPDATE时的额外开销。 CHAR类型存储时,会自动去除末尾的空格,这一点需要注意。
CHAR适合存储短的,长度固定的字符,例如MD5值,UUID等…
由于UPDATE时没有额外的开销,对于经常变更的数据,CHAR的性能也比VARCHAR更好。
BLOB和TEXT都是为了存储很大的数据而设计的字符串类型,分别采用二进制和字符的方式进行存储。
它们分别属于不同的数据类型家族:
字符类型:TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT。 二进制类型:TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB。
与其他类型不同,MySQL会将BLOB和TEXT当做单独的对象处理。
当值太大时,MySQL会使用专门的存储区域来存储数据,行内使用1~4字节来存储一个指针,指向对应的大文本字符。
BLOB和TEXT的不同之处在于:由于BLOB是二进制,所以没有字符集和排序规则,但是TEXT有。
即使TEXT有排序规则,MySQL对其进行排序时,也不会对整个文本进行排序,只会对前max_sort_length字节进行排序,可以通过修改max_sort_length进行配置。
MySQL不能将BLOB和TEXT全部长度的字符进行索引。
枚举可以把一些不重复的字符串存储成一个预定义的集合,MySQL在存储枚举时非常紧凑,会根据列表值压缩到1到2个字节中。
MySQL在内部会将列中的枚举值保存为整数,在.frm文件中保存一个“数字->字符串”的映射关系,通过数字快速的查找到具体的枚举值。
枚举字段排序时,并不会按照给定的字符串排序,而是根据内部的整数排序,所以建议列举枚举时按照预想的顺序给出。
MySQL提供了多种类型来保存时间和日期,例如:YEAR、DATE、DATETIME。 MySQL能存储的最小时间粒度为秒(有的第三方存储引擎支持微秒)。
MySQL提供了两种相似的事件类型:DATETIME和TIMESTAMP。
用来保存大范围的时间,从1001年到9999年,精度为秒。 它把时间封装到格式为YYYYMMDDHHMMSS的整数中,与时区无关,使用8个字节来存储。
保存了从1970年1月1日凌晨以来的秒数,和UNIX时间戳相同。 使用4个字节来保存,比DATETIME节省空间,具有更高的性能。 但是范围比DATETIME要小得多,只能存储1970年到2038年。
TIMESTAMP显示的值依赖于时区,MySQL服务器,操作系统以及客户端的连接都有时区的设置。
除了特殊行为之外,应该尽量使用TIMESTAMP,它比DATETIME空间效率要高。
如果需要存储比秒更小粒度的时间,MySQL目前没有提供合适的数据类型,可以考虑使用BIGINT来存储微秒级别的时间戳。
可以使用BIT列存储一个或多个true/false值,BIT(1)包含单个位的字段,最多可包含64个位。
MySQL将BIT当做字符串类型,而不是数字类型。 当查询BIT(1)时,结果是一个包含二进制0或1的字符串,而不是ASCII码中的“0”或“1”。
BIT列进行比较时,MySQL会将位字符串转换为十进制数字进行比较。 例如:‘111’ = 7。
对于大部分应用,最好慎用BIT类型。
为标识列选择合适的数据类型十分重要。
一般来说标识列很可能用来在不同的表之间进行比较,甚至作为外键来使用。 合适的数据类型可以提升系统的整体性能,减少数据比较的系统开销。
一旦选定了类型,一定要确保关联表中也是相同的数据类型,混用不同的数据类型会带来很多麻烦。 例如:将字符串与整形做比较,会导致严重的性能问题。
一般来说,在没有特殊要求的情况下,整型 通常是标识列最好的选择,因为它很快,而且可以自动递增。
如果可以的话,应该尽量避免使用字符串当做标识列,它很消耗空间,而且比整型慢。
很多人喜欢用随机的字符串来作为标识列,例如:UUID。 由于生成的字符没有规律,会导致INSERT和SELECT语句变得很慢:
Tips:如果需要使用UUID当做标识列,那么应该移除“-”这种没有意义的字符。 最好的解决方案是:用UNHEX()将UUID转换为16位的二进制数据,没有字符集,没有排序,而且占用更少的磁盘空间,可以很好的提升性能。
有些类型的数据并不直接与MySQL的内置类型一致,微秒型的时间戳就是个例子。
还有例如:IPv4地址,应该使用无符号的整数来保存,而非字符串。 MySQL内置的函数INET_ATON和INET_NTOA可以很好的转换。
虽然有一些普遍的好或坏的设计原则,但也有一些问题是由MySQL的实现机制导致的, 这意味着有可能犯一些只在MySQL下发生的特定错误。
了解讨论设计MySQL的 schema的问题。这也许会帮助我们避免这些错误,并且选择在MySQL特定实现下工作得更好的替代方案。
对于任何给定的数据通常都有很多种表示方法,从完全的范式化到完全的反范式化,以及两者的折中。
设计关系型数据库时,需要遵从不同的规范,设计合理的关系型数据库,不同的规范被称为不同的范式,各种范式呈递次规范,越高的范式数据库冗余约小。在关系型数据库中有六中范式:第一范式(1NF),第二范式(2NF),第三范式(3NF),BCNF,第四范式(4NF),第五范式(5NF)。一般数据库设计到第三范式就行了
这里简单介绍一下三大范式:
当为性能问题而寻求帮助时,经常会被建议对schema进行范式化设计,尤其是写密集 的场景。这通常是个好建议。因为下面这些原因,范式化通常能够带来好处:
范式化设计的schema的缺点是通常需要关联。稍微复杂一些的査询语句在符合范式的 schema上都可能需要至少一次关联,也许更多。这不但代价昂贵,也可能使一些索引策 略无效。例如,范式化可能将列存放在不同的表中,而这些列如果在一个表中本可以属 于同一个索引。
反范式的优点:
在真实环境中很少会极端地使用范式化或者反范式化的schema。而是可能使用部分范式化的schema、缓存表、以及其它技巧。最常见的反范式化数据的方法是复制或者缓存,在不同的表中存储相同的特定的列。
范式化和反范式化的schema各有优劣,怎么选择最佳的设计?
事实是,完全的范式化和完全的反范式化schema都是实验室里才有的东西:在真实 世界中很少会这么极端地使用。在实际应用中经常需要混用,可能使用部分范式化的 schema、缓存表,以及其他技巧。
最常见的反范式化数据的方法是复制或者缓存,在不同的表中存储相同的特定列。在 MySQL 5.0和更新版本中,可以使用触发器更新缓存值,这使得实现这样的方案变得更 简单。
MySQL 对于大表的
ALTER
操作是非常慢的,因为 MySQL 对于ALTER
操作的的方法是创建一个新结构的表,然后将旧结构表中的数据复制过去,最后将旧表删除。如此操作对于海量数据的表来说花费的时间是非常长的。
一般而言,大部分ALTER TABLE操作将导致MySQL服务中断。我们会展示一些在DDL 操作时有用的技巧,但这是针对一些特殊的场景而言的。对常见的场景,能使用的技巧 只有两种:
一种是先在一台不提供服务的机器上执行ALTER TABLE操作,然后和提供服 务的主库进行切换;
另外一种技巧是影子拷贝,影子拷贝的技巧是用要求的表结构创建一张和源表无关的新表,然后通过重命名和删表操作交换两张表。
如果愿意冒一些风险,可以让MySQL做一些其他类型的修改而不用重 建表。
下面这些操作是有可能不需要重建表的:
基本的技术是为想要的表结构创建一个新的**.frm文件,然后用它替换掉已经存在的那张 表的.frm**文件,像下面这样:
为了高效地载入数据到MylSAM表中,有一个常用的技巧是先禁用索引、载入数据,然后重新启用索引,这个技巧能够发挥作用,是因为构建索引的工作被延迟到数据完全载入以后,这个时候 已经可以通过排序来构建索引了。这样做会快很多,并且使得索引树注”的碎片更少、更紧凑。
不幸的是,这个办法对唯一索引无效,因为DISABLE KEYS只对非唯一索引有效。 MylSAM会在内存中构造唯一索引,并且为载入的每一行检査唯一性。一旦索引的大小<33 超过了有效内存大小,载入操作就会变得越来越慢。
下面是操作步骤:
良好的schema设计原则是普遍适用的,但MySQL有它自己的实现细节要注意。概括来 说,尽可能保持任何东西小而简单总是好的。MySQL喜欢简单,需要使用数据库的人 应该也同样会喜欢简单的原则:
范式是好的,但是反范式(大多数情况下意味着重复数据)有时也是必需的,并且能带 来好处。
参考: