高性能MySQL读书摘要（三）Schema与数据类型优化

最新推荐文章于 2024-05-22 21:40:48 发布

gonghaiyu

最新推荐文章于 2024-05-22 21:40:48 发布

阅读量186

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/gonghaiyu/article/details/107031239

版权

MySQL 专栏收录该内容

22 篇文章 1 订阅

订阅专栏

良好的逻辑设计和物理设计是高性能的基石。应该根据系统将要执行的查询语句来设计schema，这往往需要权衡各种因素。例如，反范式的设计可以加快某些类型的查询，但是同时会使另一些类型的查询变慢。

4.1 选择优化的数据类型

MySQL支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。
更小的通常更好：一般情况下，应该尽量使用可以正确存储数据的最小数据类型。比如，只需要存0~200，tinyint unsigned更好。
简单就好：简单的数据类型需要更少的CPU周期。例如，整型比字符操作代价更低，因为字符集和校对规则使字符比较比整型比较更复杂。比如，一个是应该使用MySQL内建的类型而不是字符串来存储日期和时间（这里阿里巴巴规范中强制约束），另外一个是应该用整型存储IP地址。
尽量避免NULL:很多表都包含可为NULL(空值)的列，即使应用程序并不需要保存NULL也是如此，这是因为可为NULL是列的默认属性。如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。当可为NULL的列被索引时，每个索引记录需要一个额外的字节，在MyISAM里甚至还可能导致固定大小的索引（例如只有一个整数列的索引）变成可变的大小的索引了。通常把可为NULL的列改为NOT NULL带来的性能提升比较少。最好是，如果计划在列上建索引，就应该尽量避免设计成可为NULL的列。
在选择列的数据类型，第一步需要确定合适的大类型：数字、字符串、时间等。下一步是选择具体类型。MySQL的数据类型可以存储相同类型的数据，只是存储的长度和范围不一样、允许的精度不同，或者需要的物理空间不同。相同大类型的不同子类型数据有时也有一些特殊的行为和属性。如，DATETIME和TIMESTAMP列都可以存储相同类型的数据：时间和日期，精确到秒。然而TIMESTAMP只使用DATETIME一半的存储空间，并且会根据时区变化，具有特殊的自动更新能力。另一方面，TIMESTAMP允许的时间范围要小很多，有时候它的特殊能力会成为障碍。这里阿里巴巴规范中建议使用DATETIME,因为MySQL5.7以后的版本，也是支持自动更新的。

4.1.1 整数类型

有两种类型的数字：整数和实数。MySQL可以为整数类型指定宽度，例如INT(11),对大多数应用这是没有意义的：它不会限制值的合法范围，只是规定了MySQL的一些交互工具用来显示字符的个数。对于存储和计算来说，INT(1)和INT(20)是相同的。

4.1.2 实数类型

实数是带有小数部分的数字。也可以用DECIMAL存储比BIGINT还大的整数。MySQL中FLOAT和DOUBLE类型支持近似计算。而DECIMAL类型用于存储精确的小数。DECIMAL类型支持精确计算。在MySQL5.0和更高版本中将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。例如，DECIMAL（18，9）小数点两边将各存储9个数字，一共使用9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个自己字节。因为需要额外的空间和计算开销，所有应该尽量只在对小数进行精确计算时才使用DECIMAL—例如存储财务数据。但在数据量比较大的时候，可以考虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。

4.1.3 字符串类型

VARCHAR和CHAR类型
很难精确解释怎么存储在内存中的，因为这跟存储引擎的具体实现有关。
VARCHAR类型用于存储可变长字符串。它通常比定长类型更节省空间，因为它仅使用必要的空间。如果MySQL表使用ROW_FORMAT=FIXED创建的话，每一行都会使用定长存储，这会浪费空间。VARCHAR需要使用1或2个额外的字节记录字符串的长度。如果列长度小于255，则使用1个字节表示，否则使用2个字节。由于行是变长的，在UPDATE时可能使行变得比原来更长，这就导致需要做额外的工作。如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，在这种情况下，不同的存储引擎的处理方式是不一样的。例如，MyISAM会将行拆成不同的片段存储，InnoDB则需要分裂页使行可以放进页内。其他一些存储引擎也许从不在原数据位置更新数据。InnoDB则更加灵活，它可以把过长的VARCHAR存储为BLOG。
CHAR类型是定长的，MySQL总是根据定义的字符串长度分配足够的空间。当存储为CHAR值时，MySQL会删除所有的末尾空格。与C语言不一样。CHAR适合存储很短的字符串，或者所有值都接近同一个长度。例如，CHAR非常适合存储密码的MD5值，因为这是一个定长的值。对于经常变更的数据，CHAR也比VARCHAR更好，因为定长的CHAR类型不容易产生碎片。与CHAR和VARCHAR类似的类型还有BINARY和VARBINARY，它们存储的都是二进制字符串。二进制字符串存储的是字节码而不是字符。填充也不一样，MySQL填充BINARY采用的是/0而不是空格。二进制比较的优势不仅仅体现在大小写敏感上，MySQL比较BINARY字符串时，每次按一个字节，并且根据该字节的数值进行比较。因此，二进制比较比字符串简单，所以速度也快。
BLOG和TEXT类型
BLOG和TEXT都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。与其他类型不同，MySQL把每个BLOG和TEXT值当做一个独立的对象处理。InnoDB会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要1-4个字节存储一个指针，然后在外部存储区域存储实际的值。
BLOG和TEXT家族之间仅有的不同是BLOG类型存储的是二进制数据，没有排序规则或字符集，而TEXT类型有字符集和排序规则。
MySQL对BLOG和TEXT列进行排序与其他类型是不同的：它只对每个列的最前max_sort_length字节而不是整个字符串做排序。MySQL不能将BLOG和TEXT列全部长度的字符串进行索引，也不能使用这些索引消除排序。建议不要使用BLOG和TEXT类型。
使用枚举（ENUM）代替字符串类型（阿里规范中，不建议使用数据库枚举）
MySQL在内部会将每个值在列表中的位置保存为整数，并且在表的.frm文件中保存“数字-字符串”映射关系的“查找表”。另一个让人吃惊的地方是，枚举字段是按照内部存储的整数而不是定义的字符串进行排序的。一种绕过这种限制的方式是按照需要的顺序来定义枚举列。另外也可以在查询中使用FIELD()函数显示地指定排序顺序，但这会导致MySQL无法利用索引消除排序。枚举最不好的地方，字符串列表是固定的，添加或删除字符串必须使用ALTER TABLE。这会导致表锁。
日期和时间类型
MySQL提供两种相似的日期类型：DATETIME和TIMESTAMP。DATETIME这个类型能保存大范围的值，从1001年到9999年，精度为秒。它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关。使用8个字节的存储空间。
TIMESTAMP
TIMESTAMP类型保存了从1970年1月1日午夜以来的秒数，它和UNIX时间戳相同。TIMESTAMP只使用4个字节的存储空间，因此它的范围比DATETIME小很多：只能表示从1970年到2038年。MySQL提供了FROM_UNIXTIME()函数把Unix时间戳转换为日期，并提供了UNIX_TIMESTAMP()函数把日期转换为Unix时间戳。TIMESTAMP显示的值也依赖于时区。MySQL服务器、操作系统，以及客户端连接都有时区设置。如果在多个时区存储或访问数据，TIMESTAMP和DATETIME的行为将很不一样。前者提供的值与时区有关系，后者则保留文本表示的日期和时间。如果需要存储比秒更小的日期和时间值怎么办？MySQL目前没有提供合适的数据类型，但是可以使用自己的存储格式：可以使用BIGINT类型存储微秒级别的时间戳，或者使用DOUBLE存储秒之后的小数部分。

4.1.5 位数据类型

BIT（不建议使用）
可以使用BIT列在一列中存储一个或多个true/false值。BIT(1)定义一个包含单个位的字段，BIT(2)存储2个位。BIT列的最大长度是64个位。BIT的行为因存储引擎而异。所以17个单独的BIT列只需要17个位存储，这样MyISAM只使用3个字节就能存储这17个BIT列。但Memory和InnoDB，为每个BIT列使用一个足够存储的最小整数类型来存放，所以不能节省存储空间。MySQL把BIT当做字符串类型，而不是数字类型。当检索BIT(1)的值时，结果是一个包含二进制0或1值的字符串，而不是ASCII码的“0”或“1”。对于大部分应用，建议避免使用这种类型。
SET（不建议使用）
它在MySQL内部是以一系列打包的位的集合来表示的。它的主要缺点是改变列的定义的代价较高：需要ALTER TABLE，这对大表来说是非常昂贵的操作。

4.1.6 选择标识符

整数类型
整数通常是标识列最好的选择，因为它们很快并且可以使用AUTO_INCREMENT。
ENUM和SET类型
避免使用ENUM和SET。
字符串类型
如果可能，应该避免使用字符串类型作为标识列（阿里规范），因为它们很消耗空间，并且通常比数字类型慢。对于完全“随机”的字符串也需要多加注意，例如MD5()、SHA1()、或者UUID()产生的字符串。这些函数生成的新值会任意分布在很大的空间内，这会导致INSERT以及一些SELECT语句变得很慢。因为（1）插入值会随机地写到索引不同的位置，所以使得INSERT语句更慢。（2）SELECT语句会变得更慢，因为逻辑上相邻的行为分布在磁盘和内存的不同地方。但是这种伪随机实际上可以帮助消除热点。如果存储UUID值，则应该移除“-“符号；或者更好的做法是，用UNHEX()函数转换UUID值为16字节的数字，并且存储在一个BINARY(16)列中。检索时可以通过HEX()函数来格式化为十六进制格式。

4.1.7 特殊数据类型

另一个例子是一个IPv4地址。人们经常使用VARCHAR(15)列来存储IP地址。然而，它们实际上是32位无符号整数，不是字符串。所以应该用无符号整数存储IP地址。MySQL提供了INET_ATON()和INET_NTOA()函数在这两种表示方法之间转换。

4.2 MySQL schema设计中的陷阱

太多的列。太多的关联（一个粗略的经验法则，如果希望查询执行得快速且并发性好，单个查询最好在12个表以内做关联）。非此发明的NULL（即使需要存储一个事实上的“空值”到表中时，可以使用0、某个特殊值，或者空字符串作为代替。MySQL会在索引中存储NULL值。）CREAT TABLE …( dt DATETIME NOT NULL DEFAULT ‘0000-00-00 00:00:00’)。

4.3 范式和反范式

事实上，完全范式化和完全的反范式化schema都是实验室里才有的东西：在真实的世界中很少会有这么极端地使用。最常见的反范式化数据的方法是复制或者缓存，在不同的表中存储相同的特定列。

4.4 缓存表和汇总表

不建议使用数据库自身的缓存，应该在系统外部加入缓存。

4.5 加快ALTER TABLE操作的速度

大部分ALTER TABLE操作将导致MySQL服务中断。对常见的场景，能使用的技巧只有两种：一种是先在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换；另外一种技巧是”影子拷贝”。影子拷贝的技巧是用要求的表结构创建一张和源表无关的新表，然后通过重命名和删表操作交换两张表。
不是所有的ALTER TABLE操作都会引起表重建。ALTER TABLE 允许三种方式修改列：ALTER COLUMN,MODIFY COLUMN,CHANGE COLUMN语句修改列。ALTER TABLE film MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5; 通过SHOW STATUS可以得知，它拷贝了整张表到一张新表，甚至列的类型、大小和可否为NULL属性没有改变。理论上，列的默认值实际上是存储表的.frm文件中，所以可以直接修改这个文件而不需要改动表本身。然而MySQL还没有采用这种优化的方法，所有的MODIFY COLUMN操作都将导致表重建。ALTER TABLE film ALTER COLUMN rental_duration SET DEFAULT 5; //这个语句会直接修改.frm文件而不涉及表数据。