合集 - mysql(4)
1.《mysql高性能》系列2：选择合适的数据类型
2.《mysql高性能》系列3：创建合适的索引3.《mysql高性能》系列4：字符集和校对4.《mysql高性能》系列1：mysql总体构架
收起

1 选择数据类型的几个原则

  • 选择更小的数据类型
    比如，存储城市id可以用int,那就不要使用bigint了。更小的数据类型占用的磁盘、内存和CPU缓存都会比较小，处理起来也更快。但是，要记得不要低估需要存储的值的范围。

  • 简单的数据类型更好
    例如，能用整型存储ip地址，就不要用字符存储ip地址。因为，字符的操作代价比整形高。

  • 尽量避免值为NULL
    值为NULL会给索引带来额外的负担。因此，如果计划将某列设置为索引，则不要默认为NULL。

在选择数据类型时，通常是先选择大的数据类型，例如，数字（包括整型和实数类型）、字符串、时间，再选择具体的类型（例如，整型分为TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT）。不同的具体类型，存储的数值的范围不同、精度不一样，同时，需要的物理空间（磁盘和内存）也不一样。

2 各种数据类型

2.1 整数类型

整数类型有：TINYINT, SMALLINT, MEIDUMINT, INT, BIGINT，分别使用8位，16位，24位，32位，64位存储空间，他们对应的存储的值的范围为-2^(N-1)到2^(N-1)-1，其中，N为存储空间的位数。

2^10 = 1024 (约1000），因此，2^20 约为 100万，2^30约为10亿

整数类型有可以选择的UNSIGNED属性，表示不允许负值。因此，正数的上限可以提高一倍。

2.2 实数类型

实数类型有：FLOAT、DOUBLE和DECIMAL。

FLOAT和DOUBLE支持对浮点运算进行近似计算，在CPU上可以进行计算。FLOAT占用4个字节，DOUBLE占用8个字节。

DECIMAL类型用于存储精确的小数，占用的位数可以自己指定。在mysql5.0及以上的版本，DECIMAL支持精确的计算。由于CPU不支持对DECIMAL的直接计算，因此，DECIMAL的计算在mysql服务器层实现，在时间和空间性能上，FLOAT和DECIMAL比DECIMAL的更好一些。

对于一些财务数字，为了实现精确的存储和计算，可以有两种考虑：1）使用DECIMAL类型，但这样会占用更大的空间和计算开销；2）将存储的货币单位根据小数的位数乘以相应的倍数之后，转化为整数存储。比如，13.33元，则存储为1333，单位为角。

2.3 字符串类型

VARCHAR

用于存储可变长度的字符串，是最常见的字符串类型。在分配内存来存储时，只会分配必要的内存，而不会分配多余的空间。

VARCHAR需要使用1个或2个额外的字节来记录字符串的长度。比如，varchar(10)存储了一个8个字节的字符串，那么，还需要1个字节来表示长度，因此，共占用9个字节。varchar（1000）存储了一个900字节的字符串，那么，就需要额外的2个字节（1个字节最多表示255）来表示长度，共占用902个字节。

在mysql5.0及以上，mysql在存储和检索时会保留VARCHAR类型末尾的空格。

CHAR

使用固定的字节来存储字符串。当存储CHAR值时，mysql会删去所有的末尾空格，因为CHAR值会根据需要采用空格来填充末尾多余的空间。

VARCHAR和CHAR的比较

  • 字符串列的长度
    如果字符串列的最大长度比平均长度大很多（说明有少数的列值很大），那么，适合使用VARCHAR。
    如果字符串的长度很短，或者所有值接近一个长度，则适合采用CHAR。比如，密码的md5值都是一个长度。

  • 列是否会更新
    如果字符串列值更新很少，或者更新后的字符串长度和之前一样，则适合使用VARCHAR。因为，如果在UPDATE之后，使得行变得比原来更长，导致页内没有更多的空间来存储，那么，存储引擎需要做额外的工作，或者将行拆成不同的部分存储，或者分裂之前的页，来存放更多的内容。这需要消耗额外的时间。

  • 特殊字符
    如果需要允许字符串末尾有空格，则使用VARCHAR。

BINARY和VARBINARY

BINARY存储二进制字符串，存储的是字节码。mysql采用’\0’来填充BINARY类型值的末尾多余的空间。mysql在比较BINARY字符串时，每次按照一个字节，并且根据该字节的数值来进行比较。

VARBINARY变长的二进制字节码。

BLOB和TEXT

BLOB和TEXT都是为存储很大的字符串而设计的，分别采用二进制和字符方式存储。

BLOB和TEXT类型的值太大时，mysql会使用一个额外的区域来存储这个值，每个值在行内有一个1到4字节的指针来指向它。对BLOB和TEXT类型，mysql只对每个列最前max_sort_length字节而不是整个字符串做排序。如果只需要排序最前的n个字符，则可以通过减小max_sort_length的配置。因为行内存储的不是实际的值，因此，不能对整个长度进行索引，也不能使用这些索引来消除排序。

2.4 日期和时间类型

  • DATE
    只有日期，没有时间，检索和显示的格式为’YYYY-MM-DD’。支持的日期区间为：’1000-01-01’ 到 ’9999-12-31’。

  • DATETIME
    包含日期和时间这两部分，检索和显示的格式为’YYYY-MM-DD HH:MM:SS’，支持的区间为：’1000-01-01 00:00:00’ 到’9999-12-31 23:59:59’。这个存储的值可以认为是一个表示日期和时间的文本，与时区无关。

  • TIMESTAMP
    保存了从1970年1月1日午夜（格林尼治标准时间）以来的秒数，和UNIX时间戳相同。TIMESTAMP使用4个字节的存储空间，因此，只能表示从1970年到2038年。另外，这个值与时趣有关。如果在美国东部时区显示，则0表示为’1969-12-31 19：00：00’。

注意：mysql5.6 DATETIME和TIMESTAMP类型是可以存储到微妙级别的时间的。

3 schema设计

3.1 范式和反范式

范式主要是从数据存储的角度来考虑的，要求数据没有冗余。反范式则允许数据有冗余。范式的优缺点如下。

优点：
1）很少或者没有数据冗余。因此，在更新时，通常只需要更新一张表就可以了，容易避免数据的不一致，更新的效率也更高。
2）由于很少有数据冗余，因此，存储空间更小。

缺点：
1）在查询时，可能需要关联多个表才可以拿到所需要的数据。
2）在建联合索引时，可能发现多个字段不在一个表中。

反范式的优缺点与范式正好相反。

3.2 缓存表和汇总表

缓存表：如果某些表的数据获取比较慢，则可以使用单独的一张表来缓存这些数据。这个单独使用的表就是缓存表。
汇总表：保存的是使用GROUP BY语句聚合的数据

缓存表和汇总表都保存了冗余的数据，但这可以提高查询的性能。