高性能Mysql——Schema与数据类型优化

选择优化的数据类型

MySQL支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。通常又以下几个原则：

1. 更小的通常更好。
   一般情况下，应该尽量使用可以正确存储数据的最小数据类型（比如，只需要存储0-200，使用tinyint unsigned更好）。更小的数据类型通常更快，因为占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。
2. 简单就好
   简单的数据类型的操作需要更少的CPU周期。例如，整型比字符操作代价更低。又比如，应该用MySQL内建的类型（比如date、time、datetime）而不是字符串来存储日期和时间。
3. 尽量避免NULL
   很多表都包含可为NULL的列，实际上，通常情况下最好指定列为NOT NULL，除非真的需要存储NULL值。因为，如果查询中包含可为NULL的列，对于Mysql来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。当可为NULL的列被索引时，每个索引记录需要一个额外的字节。可为NULL的列会使用更多的存储空间。
   在调优时，把NULL的列以为NOT NULL带来的性能提升比较小，所以没有必要在调优时更改，但是如果计划在列上建立索引，最好设计为NOT NULL。

整数类型

如果存储整数，可以使用这几种类型：

• tinyint、smallint、mediumint、int、bigint。
  分别使用8、16、24、32、64位存储空间。
  也就是存储$-2^{(N-1)}$到$2^{(N-1)}-1$，其中N是存储空间的位数。

整数类型可选的unsigned属性，表示不允许为负，所以基本可以把正数的上限提高一倍（x2，原本存储的正负号的那位可以表示数字）。比如tinyint和tinyint unsigned，存储范围从-128～127变成了0~255。

注意：整数类型指定宽度（比如int(11)），它其实对于存储和计算是没有意义的，int(11)和int(1)消耗的存储和计算是一样的，不同的只是Mysql交互工具来显示字符的个数。

实数类型

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

float和double支持使用标准的浮点运算来近似计算。
decimal类型用于存储精确的小数，在mysql5.0以上版本，支持精确计算。
因为CPU不支持对decimal的直接计算，但支持原生浮点计算，所以浮点计算明显更快。
浮点和decimal都可以指定精度（小数点后允许的最大位数），这会影响列的空间消耗。
因为需要额外的空间和计算开销，所以尽量只在对小数进行精确计算时才使用decimal，在数据量比较大的时候可以考虑使用bigint代替decimal。比如如果要求财务数据精确到万分之一分，可以把所有金额乘以一百万，然后存储在bigint中，避免浮点运算的不精确和decimal运算的代价高。

字符串类型

Mysql支持多种字符串类型，并且，可以自定义字符集和排序规则、校对规则，这些会很大程度上影响性能。

varchar和char类型
这两个类型是最主要的字符串类型，存储方式跟存储引擎的具体实现有关，下面描述使用存储引擎：InnoDB。

varchar用于存储可变长字符串，它比定长类型节省空间（除非Mysql表使用ROW_FORMAT=FIXED），由于可变长，所以使用了1或2个额外字节来记录长度，如果列的最大长度小于等于255个字节，则用一个字节来表示，否则使用2个字节。
varchar节省了存储空间，但是由于是可变长的，在UPDATE操作时，可能会比原来长，这就需要额外工作，InnoDB使用分裂页来使得行能够放进页内。并且InnoDB会把过长的varchar存储为blob。

char类型是定长的，所以很适合用来存储短的字符串或者所有值都接近同一个长度。对于经常变更的数据，char类型也比varchar更不容易产生碎片。另外，char比varchar存储空间上也更有效率，比如char(1)使用一个字节，而varchar(1)使用两个字节。
还需要注意的细节是，innoDB中，char类型的会删除字符串末尾的空格，而varchar类型会保留字符串末尾的空格。

使用varchar(10)和varchar(200)存储’hello’的空间开销是一样的，不过更推荐使用前者，因为更长的列会消耗更多的内存，因为Mysql通常会分配固定大小的内存来保存内部值，最好的策略是分配真正需要的空间。

binary和varbinary类型
与char和varchar类似，只不过它们存储的是二进制字符串。二进制字符串跟常规字符串非常相似，只不过存储的是字节码而不是字符。另外填充采用的是\0而不是空格，在检索时也不会去掉填充值。
在敏感大小写的情况下，或者需要对字节进行比较时，二进制比较比字符比较简单也更快。

blob和text类型
blob类型和text类型都是为了存储很大的数据而设计的字符串数据类型，分别采用二进制和字符的方式进行存储。

字符类型：tinytext、smalltext、text、mediumtext、longtext。
二进制类型：tinyblob、smallblob、blob、mediumblob、longblob。

与之前的类型不同的是，Mysql把blob和text当成一个独立的对象去处理，存储引擎在存储时通常会做特殊处理，当它们过大时，InnoDB会使用“外部”的存储区来存储，使用指针来指向外部。
Mysql对它们的排序，使用MAX_SORT_LENGTH来排序，也就是只排序前面一小部分字符。

使用枚举类型（ENUM）来代替字符串类型

CREATE TABLE `enum` (
  `type` enum('fish','dog','pig','cat') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据：

BEGIN;
INSERT INTO `enum` VALUES ('fish');
INSERT INTO `enum` VALUES ('dog');
INSERT INTO `enum` VALUES ('cat');
COMMIT;

插入的三行实际存储的是整数而不是字符串，可以通过下面的查询看到：

select type+0 from enum 

并且其排序也是根据整数来排序的，而不是字符串。比如：

select type from enum order by type

枚举类型完全可以使用事先规定的tinyint等类型规避掉，所以不建议使用。

日期和时间类型

Mysql能使用很多日期类型，能存储的最小时间粒度是秒（MariaDB是微秒），不过Mysql能使用微秒级的粒度进行临时运算。

大部分时间类型没有替代品，因此没有什么最佳选择问题，主要是需求问题。

datetime类型
从1001年到9999年，精度是秒，它把日期封装到格式为YYYYMMDDHHMMSS的整数中，使用8个字节的存储空间。
默认情况下，Mysql使用可排序的格式显示其值，比如"2019-7-29 22:37:08"。

timestamp类型
该类型保存了从1970年1月1日午夜（格林尼治时间）以来的秒数，使用4个字节的存储空间（与INT相同，但比INT可读性高），所以范围比datetime小得多，表示1970年到2038年的时间。

位数据类型

bit类型
对大部分应用，应该避免使用这个类型。
可以使用tinyint等代替。