Schema与数据类型优化

最常用的数据类型分类

• 数字（int、decimal）
• 字符串（varchar、char、text）
• 时间（date、datetime、timestamp）

在PHPmyadmin工具中，还可以看到两种大类型：spatial（空间类型）、JSON，不过使用很少

如何选择数据类型？

• 简单的区分大类型：数字、字符串、时间等
• 尽量使用可以正确存储数据的最小数据类型（比如：数字范围小的考虑 tinyint 之类）
• 尽量使用简单的数据类型（整型 > 字符串，内建类型[date, timestamp, datetime] > 字符串）
• 日期。TIMESTAMP占用的存储空间是DATETIME的一半，但是TIMESTAMP的范围只能是1970~2038，需要具体分析

整数类型的范围

类型	存储位数	有符号的max(2^(n-1)-1)	显示最大宽度
TINYINT	8	127	4
SMALLINT	16	32767	6
MEDIUMINT	24	8388607	8
INT	32	2147483647	11
BIGINT	64	9223372036854775807	20

INT(11)中的11表示为整数类型指定显示的宽度。所以，对于存储与计算来说，INT(1)和INT(20)是相同的

实数（带有小数部分的数字）类型

1、 FLOAT、DOUBLE类型

精度不足，仅支持近似计算

但是由于CPU可以直接支持原生浮点型计算：

• 运算速度更快
• 空间消耗比较小

2、 DECIMAL类型

MySQL 5.0及以上的版本，将数字打包保存到一个二进制字符串中（每4个字节存9个数字），且小数点占1个字节。最多允许65个数字

建议：因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL——例如财务数据。在数据量比较大时，可以考虑使用BIGINT代替DECIMAL，乘以相应的倍数得到最终的结果。这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题

字符串类型

1、 VARCHAR、CHAR类型

最主要的两种字符串类型，一般存储的数据量较小

VARCHAR的特点：

• 用于存储可变长的字符串
• 需要1或2个额外的字节记录字符串的长度，len <= 255需要1个字节，否则需要2个
• 可以节省存储空间，但是update可能会造成碎片产生

使用VARCHAR的场景：

• 字符串列的最大长度比平均长度大很多
• 列更新较少
• 使用复杂的字符集（如UTF-8），每个字符使用不同的字节数进行存储

另外需要注意的： 虽然VARCHAR(5) 和 VARCHAR(200)来存储hello字符串，空间开销是一样的，但是后者需要分配更多的内存去保存内部值和排序。所以最好的策略是只分配真正需要的空间

InnoDB可以把过长的VARCHAR存储为BLOB

CHAR的特点：

• 根据定义的字符串长度分配足够的空间
• 不需要记录长度的额外字节

使用CHAR的场景：

• 存储很短的字符串，或者所有值都接近同一个长度（如MD5密码值等）
• 经常变更的数据，CHAR优于VARCHAR，因为CHAR不容易产生碎片

2、 BLOB、TEXT类型

为存储很大的数据而设计的字符串数据类型，BLOB采用二进制，TEXT采用字符方式存储

特点：

• 当作独立的对象处理，存储也会特殊处理（太大的数据量可能存储为指针，然后在外部存储区域存储实际的值）
• 排序只对最前max_sort_length字节排序，如果只需要排序前面一小部分字符，可以减小max_sort_length的配置，或者使用ORDER BY SUBSTRING(column, length)

mysql> select @@max_sort_length;
+-------------------+
| @@max_sort_length |
+-------------------+
|              1024 |
+-------------------+
1 row in set (0.00 sec)

问题： 如果查询使用了BLOB、TEXT类型并且需要使用隐式临时表，将不得不使用MyISAM磁盘临时表(即EXPLAIN的Extra列包含Using temporary)，造成严重的性能开销！

• 最好的解决方案是尽量避免使用BLOB和TEXT类型
• 如果无法避免则可以在用到这些类型的地方使用SUBSTRING(column, length)，将列值转换为字符串，这样就可以使用内存临时表了。但是必须确保截取的子字符串足够短，否则如果临时表大小超过max_heap_table_size 或 tmp_table_size，仍然会转换为磁盘临时表

mysql> select @@max_heap_table_size;
+-----------------------+
| @@max_heap_table_size |
+-----------------------+
|              16777216 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select @@tmp_table_size;
+------------------+
| @@tmp_table_size |
+------------------+
|         16777216 |
+------------------+
1 row in set (0.00 sec)

3、 使用枚举(ENUM)代替字符串类型

相对固定且少量的字符串，可以用枚举类型代替，在MySQL中存储为整数，并用一个“数字 - 字符串”的映射表保存

mysql> create table enum_test(e ENUM('fish', 'apple', 'dog') NOT NULL);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO enum_test(e) VALUES ('fish'), ('dog'), ('apple');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select e from enum_test;
+-------+
| e     |
+-------+
| fish  |
| dog   |
| apple |
+-------+
3 rows in set (0.00 sec)

mysql> select e+0 from enum_test;
+-----+
| e+0 |
+-----+
|   1 |
|   3 |
|   2 |
+-----+
3 rows in set (0.01 sec)

mysql> desc enum_test;
+-------+----------------------------+------+-----+---------+-------+
| Field | Type                       | Null | Key | Default | Extra |
+-------+----------------------------+------+-----+---------+-------+
| e     | enum('fish','apple','dog') | NO   |     | NULL    |       |
+-------+----------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

有些问题需要注意：

• 枚举类型在ORDER的时候，是根据ENUM的顺序排序，如果需要对字符串特定排序，可以使用FIELD函数显式指定。 ORDER BY FIELD(e, 'apple', 'dog', 'fish')
• 需要增加或删除字符串的时候，除非只在列表末尾添加元素，否则需要重建整个表
• 在关联（JOIN）多表的情况下，ENUM之间关联，速度比VARCHAR之间关联会快很多，所以在优化中很值得使用。另外有时候，VARCHAR与ENUM之间关联，会比VARCHAR之间关联更慢，一定要注意保证ENUM与ENUM关联

4、 日期和时间类型

MySQL能存储的最小时间粒度为秒，最常用的类型：DATETIME、TIMESTAMP

DATETIME

从1001年到9999年，把日期封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关，使用8个字节存储

TIMESTAMP（无特殊要求的情况下，建议使用）

从1970年到2038年，对应UNIX时间戳，依赖于时区，只需要4个字节存储

存储比秒更小粒度的时间：

• BIGINT
• DOUBLE
• 使用MariaDB替代MySQL

范式与反范式

在范式化的数据库中，每个事实数据会出现且只出现一次。相反，在反范式化的数据库中，信息是冗余的，可能会存储在多个地方

范式的优缺点：

• 范式化更新操作通常比反范式化要快

• 当数据较好地规范化时，就只有很少或者没有重复数据，所以只需要修改更少的数据

• 范式化的表通常更小，可以更好地放到内存中，执行操作会更快

• 检索时更少需要DISTINCT或者GROUP BY语句

• 通常需要关联一次或多次，代价昂贵且使一些索引策略失效

反范式的优缺点：

• 如果不需要关联，即使表没有使用索引，最差的情况也只是全表扫描，避免了随机I/O
• 单独的表可以使用更有效的索引策略

缓存表和汇总表

有时提高性能最好的方法是在同一张表中保存衍生的冗余数据，有时也需要创建一张完全独立的汇总表或缓存表

缓存表示例： 可以把复杂查询的结果放在一个索引合理的表中，便于多次查询

汇总表示例： 假设需要计算之前24小时内发送的消息数，可以每小时生成一张汇总表，或者在汇总表的基础上，把之前23个完整的小时的统计表中的计数全部加起来，最后加上当前小时内的计数即可

在重建缓存表和汇总表的时候，通常需要保证数据在操作时依然可用，需要通过“影子表”来实现

DROP TABLE IF EXISTS my_s_new, my_s_old;
CREATE TABLE my_s_new LIKE my_s;
-- 按照需要去填充my_s_new
RENAME TABLE my_s TO my_s_old, my_s_new TO my_s;

计数器表的例子

如果应用在表中保存计数器，则在更新计数器时可能碰到并发问题，出现一个全局的互斥锁（mutex），这会使得这些事务只能串行执行

可以预先在一张表中增加100行数据，随机选择一个槽（slot）进行更新，使用聚合查询来统计总数

UPDATE hit_counter SET cnt = cnt + 1 WHERE slot = RAND() * 100;
SELECT SUM(cnt) FROM hit_counter;

可以用ON DUPLICATE KEY UPDATE来代替预先生成行，统计每日的数据

INSERT INTO daily_hit_counter(day, slot, cnt) VALUES (CURRENT_DATE, RAND() * 100, 1) ON DUPLICATE KEY UPDATE cnt = cnt + 1;

上面的方法都是“更快地读，更慢地写”，通过建立一些额外索引、增加冗余列、创建缓存表和汇总表，虽然增加写查询的负担，但是会提升读查询的速度