高性能mysql：schema与数据类型优化

• 选择优化的数据类型
  • 整数类型
  • 实数类型
  • 字符串类型
    • varchar和char
    • varbinary和binary
    • blob和text
  • 日期和时间类型
  • 选择标识符
  • 特殊类型数据
• schema设计中的陷阱
• 范式和反范式
  • 范式的优缺点
  • 反范式的优缺点
  • 混用范式化和反范式化

本文系阅读《高性能MySQL》，Baron Schwartz等著一书中第四章 Schema与数据类型优化的笔记，本章关注的是mysql数据库的设计，主要介绍的是mysql数据库设计和其他关系型数据库管理系统的区别。

选择优化的数据类型

mysql支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。不管存储哪种类型的数据，下面几个简单的原则都有助于做出更好的选择：

• 更小的通常更好，尽量使用可以正确存储数据的最小数据类型，更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少
• 简单就好，简单数据类型的操作通常需要更少的CPU周期。例如，整型比字符操作代价更低，因为字符集和校对规则（排序规则）使字符比较比整型比较更复杂。使用mysql内建的类型（data、time、datatime）而不是字符串来存储日期和时间，用整型存储IP地址
• 尽量避免NULL，最好指定列为NOT NULL，除非真的需要存储NULL值，通常数值型默认为0，字符型默认为Empty String。如果查询中包含可为NULL的列，对mysql来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在mysql中也需要特殊处理。当可为NULL的列被索引时，每个索引记录需要一个额外的字节。通常把可为NULL的列改为NOT NULL带来的性能提升比较小，所以调优时没有必要首先在现有schema中查找并修改，除非确定这会导致问题，但是，如果计划在列上建索引时，就应该尽量避免设计成可为NULL的列
  • 使用count()统计某列记录数时，会忽略掉NULL值的记录，而默认值为Empty String的记录会被统计
  • 判断是否为NULL用IS NULL或者IS NOT NULL，判断是否为空字符串用= ''或者<> ''

    SELECT COUNT(userphone) FROM tb_user; //不含字段值为NULL
    SELECT COUNT(*) FROM tb_user WHERE userphone = ''; //不含字段值为NULL
    SELECT COUNT(userphone) FROM tb_user WHERE userphone = ''; //不含字段值为NULL
    SELECT COUNT(*) FROM tb_user WHERE userphone <> ''; //不含字段值为NULL
    SELECT COUNT(userphone) FROM tb_user WHERE userphone <> ''; //不含字段值为NULL
    SELECT COUNT(*) FROM tb_user WHERE userphone IS NULL; //仅含字段值为NULL
    SELECT COUNT(userphone) FROM tb_user WHERE userphone IS NULL; //不含字段值为NULL、Empty String
    SELECT COUNT(*) FROM tb_user WHERE userphone IS NOT NULL; //含字段值为Empty String
    SELECT COUNT(userphone) FROM tb_user WHERE userphone IS NOT NULL; //含字段值为Empty String
    

当为列选择数据类型时，第一步需要选择合适的大类型：数字、字符串、时间等；第二部步是选择具体类型，很多mysql的数据类型可以存储相同类型的数据，只是存储的长度和范围不一样、允许的精度不同，或者需要的物理空间（磁盘和内存空间）不同。

整数类型

有两种类型的数字：整数和实数。如果存储整数，可以使用这几种整数类型：tinyint、smallint、mediumint、int、bigint，分别使用：8、16、24、32、64位存储空间，它们可以存储值的范围从-2^(N-1)到(2^(N-1))-1，其中N是存储空间的位数。

整数类型有可选的unsigned属性，表示不允许负值，这大致可以使正数的上限提高一倍，例如无符号型tinyint可以存储的范围是0~255，而tinyint的存储范围是-128~127。有符号和无符号类型使用相同的存储空间，并具有相同的性能，因此可以根据实际情况选择合适的类型。

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

实数类型

实数是带有小数部分的数字，然而，它们不只是为了存储小数部分，也可以使用decimal存储比bigint还大的整数。mysql既支持精确类型，也支持不精确类型：

• float和double类型支持使用标准的浮点运算进行近似计算
• decimal类型用于存储精确的小数

浮点和decimal类型都可以指定精度，对于decimal列，可以指定小数点前后所允许的最大位数，这会影响列的空间消耗。mysql 5.0和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。例如，decimal(18,9)小数点两边将各存储9个数字，一共使用9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节。mysql 5.0和更高版本中的decimal类型允许最多65个数字。

浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。float使用4个字节存储，double占用8个字节，相比float有更高的精度和更大的范围。和整数类型一样，能选择的只是存储类型；mysql使用double作为内部浮点计算的类型。

因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用decimal——例如存储财务数据。但在数据量比较大的时候，可以考虑使用bigint代替decimal，将需要存储的货币单位根据小数的位数乘以相应的倍数即可，这样可以同时避免浮点存储计算不精确和decimal精确计算代价高的问题。

mysql允许使用非标准语法：float(m,d)、real(m,d)或double(m,d)，这里(m,d)表示该值一共保存m位数字，其中d位数字在小数点后面。例如，定义为float(7,4)的列保存值的范围：-999.9999~999.9999，在实际保存值时会四舍五入，如果在float(7,4)列内插入999.00009，实际保存值999.0001。

decimal和numeric在mysql中视为相同的类型，它们用于保存精确值，例如财务数据。当定义列为该类型时，可以指定精度和标度，例如，decimal(5,2)中5是精度，2是标度，精度表示可以保存数字的总位数，标度表示小数点后可以保存数字的位数。

字符串类型

mysql支持多种字符串类型，每种类型还有很多变种。

varchar和char

varchar和char是两种最主要的字符串类型。

varchar类型用于存储可变长字符串，是最常见的字符串数据类型，它比定长类型更节省空间，因为它仅使用必要的空间（例如，越短的字符串使用越少的空间）。在mysql 5.0或更高版本，存储和检索varchar时会保留末尾空格。varchar需要使用1或2个额外字节记录字符串的长度：如果列的最大长度小于或等于255字节，需额外使用1个字节，否则使用2个字节。

下面这些情况下使用varchar是合适的：

• 字符串列的最大长度比平均长度大很多
• 列的更新很少，所以碎片不是问题
• 使用了像utf-8这样复杂的字符集，每个字符都使用不同的字节数进行存储

char类型是定长的：mysql总是根据定义的字符串长度分配足够的空间。当存储char值时，mysql会删除所有的末尾空格。char适合存储很短的字符串，或者所有值都接近同一个长度：

• char非常适合存储密码的md5值，因为这是一个定长的值
• 对于经常变更的数据，char也比varchar更好，因为定长的char类型不容易产生碎片
• 对于非常短的列，char也比varchar在存储空间上也更有效率

数据如何存储取决于存储引擎，并非所有的存储引擎都会按照相同的方式处理定长和变长的字符串。

varbinary和binary

varbinary和binary类型存储的是二进制字符串。二进制字符串和常规字符串非常相似，但是二进制字符串存储的是字节码而不是字符，填充也不一样：mysql填充binary采用的是\0（零字节）而不是空格，在检索时也不会去掉填充值。

当需要存储二进制数据，并且希望mysql使用字节码而不是字符进行比较时，这些类型是非常有用的。二进制比较的优势并不仅仅体现在大小写敏感上。mysql比较binary字符串时，每次按一个字节，并且根据该字节的数值进行比较。因此，二进制比较比字符比较简单很多，所以也就更快。

blob和text

blob和text都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。实际上，它们分别属于两组不同的数据类型家族：字符类型是tinytext、text、mediumtext、longtext；对应的二进制类型是tinyblob、blob、mediumblob、longblob。

与其它类型不同，mysql把每个blob和text值当作一个独立的对象处理。存储引擎在存储时通常会做特殊处理。当blob和text值太大时，InnoDB会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要1~4个字节存储一个指针，然后在外部存储区域存储实际的值。

blob和text家族之间仅有的不同是blob类型存储的是二进制数据，没有排序规则或字符集，而text类型有字符集和排序规则。

mysql对blob和text列进行排序与其他类型是不同的：它只对每个列的最前max_sort_length字节而不是整个字符串做排序。

mysql不能将blob和text列全部长度的字符串进行索引，也不能使用这些索引消除排序。

日期和时间类型

mysql能存储的最小时间粒度为秒，提供两种相似的日期时间类型：datetime和timestamp，提供日期类型：date，提供时间类型：time。

datetime类型能保存大范围的值，从1001年到9999年，精度为妙，使用8个字节的存储空间。

timestamp类型保存了从1970年1月1日午夜（格林尼治标准时间）以来的秒数，它和UNIX时间戳相同，使用4个字节的存储空间，因此它的范围比datetime小的多：只能表示从1970年到2038年。timestamp显示的值也依赖于时区，mysql服务器、操作系统，以及客户端连接都有时区设置。

选择标识符

为标识列（identifier column）选择合适的数据类型非常重要。一旦选定了一种类型，要确保在所有关联表中都使用同样的类型，类型之间需要精确匹配。在可以满足值的范围的需求，并且预留未来增长空间的前提下，应该选择最小的数据类型:

• 整数通常是标识列最好的选择，因为它们很快并且可以使用AUTO_INCREMENT
• 尽量避免使用字符串类型作为标识符，因为它们很消耗空间，并且通常比数字类型慢。对于完全随机的字符串也需要多加注意，随机产生的值会任意分布在很大的空间内，这会导致insert以及一些select语句变的很慢

特殊类型数据

经常使用varchar(15)列来存储IP地址，然而，它们实际上是32位无符号整数，不是字符串，用小数点将地址分成四段的表示方法只是为了阅读。所以应该用无符号整数存储IP地址，mysql提供INET_ATON()、INET_NTOA()函数在这两种表示方法之间转换，示例如下。

`ip` int(10) unsigned DEFAULT '0';

UPDATE tb_test SET ip = INET_ATON('192.168.1.1');

SELECT INET_NTOA(ip) FROM tb_test;

schema设计中的陷阱

schema等价于数据库，如下所示：

mysql> SELECT SCHEMA_NAME FROM information_schema.SCHEMATA;
+-----------------------+
| SCHEMA_NAME           |
+-----------------------+
| information_schema    |
| blogstation           |
| cloudhospital         |
| cncounter             |
| freshmommybbqjl       |
| freshmommyhhzce       |
| freshmommywy          |
| hibernate             |
| hospital              |
| inforbase             |
| jeesite               |
| mobilemedicalplatform |
| mybatis               |
| mydb                  |
| mysql                 |
| pciplatform           |
| performance_schema    |
| questiondatabase      |
| redis                 |
| remotecooperate       |
| sharelife             |
| sonar                 |
| sourceshareplat       |
| springsession         |
| test                  |
| videoplatform         |
| wonders_bi            |
+-----------------------+
27 rows in set (0.00 sec)

在mysql特定实现下，设计schema时需要避免的错误：

• 太多的列，mysql的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。InnoDB的行结构总是需要转换，转换的代价依赖于列的数量，如果计划使用数千个字段，必须意识到服务器的性能运行特征会有一些不同
• 太多的关联，mysql限制了每个关联操作最多只能有61张表。一个粗略的经验法则，如果希望查询执行的快速且并发性好，单个查询最好在12个表以内做关联
• 全能的枚举，注意防止过度使用ENUM
• 变相的枚举
• 非此发明的NULL

范式和反范式

对于任何给定的数据通常都有很多种表示方法，从完全的范式化到完全的反范式化，以及两者的折中。在范式化的数据库中，每个事实数据会出现并且只出现一次。相反，在反范式化的数据库中，信息是冗余的，可能会存储在多个地方。

范式的优缺点

因为性能问题而寻求帮助时，经常会被建议对schema进行范式化设计，尤其是写密集的场景：

• 范式化的更新操作通常比反范式化要快
• 当数据较好的范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据
• 范式化的表通常更小，可以更好的放在内存里，所以执行操作会更快
• 很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句

范式化设计的schema的缺点是通常需要关联。稍微复杂一些的查询语句在符合范式化的schema上都可能需要至少一次关联，也许更多，这不但代价昂贵，也可能使一些索引策略无效。

反范式的优缺点

反范式化的schema因为所有数据都在一张表中，可以很好的避免关联。如果不需要关联表，则对大部分查询最差的情况——即使表没有使用索引——是全表扫描。当数据比内存大时这可能比关联要快的多，因为这样避免了随机I/O。

混用范式化和反范式化

事实是，完全的范式化和完全的反范式化schema都是实验室里才有的东西，在实际应用中经常需要混用，可能使用部分范式化的schema、缓存表，以及其他技巧。

最常见的反范式化数据的方法是复制或者缓存，在不同的表中存储相同的特定列。在mysql 5.0和更新版本中，可以使用触发器更新缓存值，这使得实现这样的方案变的更简单。