《高性能MySQL》学习笔记——第四章 Schema与数据类型优化

第四章 Schema与数据类型优化

4.1 选择优化的数据类型

1、应该尽量使用可以正确存储存储数据的最小数据类型。如能使用tinyint就不要使用int，能使用varchar(20)，就不要使用varchar(100)。

2、应该尽量使用尽可能简单的数据类型，如日期类型尽量使用date、time、datetime，而不是使用varchar存储日期数据，另外应该使用整形存储IP地址，而不是使用varchar。

3、应该尽量避免null，可以保证不出错的情况下尽量把列指定为not null。一方面在于在查询时，如果有null，则需要使用ifnull，而这样的话会使索引失效；另一方面，可为null的列会占用更多的存储空间。

4、int(11)通常是没有意义的；decimal(m,n)中m是指整数部分和小数部分位数之和，n则仅仅指小数部分位数。

5、财务数据建议用decimal存储，可以对小数部分进行精确计算，而在数据量比较大时，可以考虑使用BIGINT代替decimal，并根据需要存储的小数位乘以相应的倍数就行，如需要保留到万分之一，可以将原始数据乘以一百万，再存到bigint里面，因为bigint相比decimal的好处在于计算更精确和计算效率高。（但是这种方式要注意使用该金额时可能会出现忘记除掉相应的倍数的情况，个人建议还是使用decimal，更安全）

6、varchar和char：

（1）varchar和char类型消耗的存储空间的字节数由其字符集决定，如使用utf8mb4时，英文字母和数字占用1个字节，而大部分中文占用3个字节，少量特殊字符占用4个字节。使用char_length()和length()可以查看字符串长度和字符串所占用的字节长度。

mysql> select test1, length(test1), char_length(test1) from test_varchar1;
+--------+---------------+--------------------+
| test1  | length(test1) | char_length(test1) |
+--------+---------------+--------------------+
| abc    |             3 |                  3 |
| 123    |             3 |                  3 |
| 你好啊 |             9 |                  3 |
+--------+---------------+--------------------+
3 rows in set (0.00 sec)

（2）varchar(n)是变长，char(n)是定长，即varchar消耗的存储空间是随字符串长度而改变的，char消耗的存储空间是既定的。此外varchar还会消耗1-2个字节存储字符串的长度，而char不会。因此对于存储定长数据，使用char更好，因此不需要额外存储一个字节来保存字符串长度，但在实际业务中这种需求较少，反倒使用enum的都比使用char的多。

（3）char类型会删除末尾的空格再进行存储（危！），而varchar不会删除末尾空格（实际上在MySQL4.1或更老的版本中varchar也会删末尾空格）。

mysql> select test2, test3, char_length(test2), char_length(test3), length(test2), length(test3), concat('(', test2, ')'), concat('(', test3, ')') from test_varchar2; -- test2为varchar类型，而test3为char类型。
+--------+-------+--------------------+--------------------+---------------+---------------+-------------------------+-------------------------+
| test2  | test3 | char_length(test2) | char_length(test3) | length(test2) | length(test3) | concat('(', test2, ')') | concat('(', test3, ')') |
+--------+-------+--------------------+--------------------+---------------+---------------+-------------------------+-------------------------+
|   ab   |   ab  |                  6 |                  4 |             6 |             4 | (  ab  )                | (  ab)                  |
|   ab   |   ab  |                  6 |                  4 |             6 |             4 | (  ab  )                | (  ab)                  |
+--------+-------+--------------------+--------------------+---------------+---------------+-------------------------+-------------------------+
2 rows in set (0.00 sec)

（4）当使用严格的SQL模式时，insert的数据超过varchar和char的最大长度时，都会报错；当启动非严格的SQL模式时，他们则是将超过长度后面的字符删除存储，并予以警告，而不是报错。

（5）最好的策略是根据业务需求选择最适合的类型，只分配真正需要的空间。

7、blob与text都是用于存储长度特别长（超过65535个字节）的数据类型，对他们的排序并不是对整个字符串进行排序，都是对其前max_sort_length个字节的字符进行排序，可以手动设置max_sort_length的值，或者使用order by sustring(column, length)。区别在于blob存储的是字符串的二进制，而text存储的是原始字符串。

8、enum类型会将“数字-字符串”映射关系的“查找表”存储于.frm文件中，而数据中只存储“数字”键，这种双重性容易导致混乱，特别是排序的时候，enum排序是使用内部存储的整数进行排序，而不是定义的字符串进行排序。除非使用FIELD()函数自定义排序顺序。列关联时的效率：enum关联enum > varchar关联varchar > enum和varchar互相关联

mysql> create table `enum1`(column1 enum('Y', 'M', 'N'));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into enum1 values('Y'), ('Y'), ('N'), ('M');
Query OK, 4 rows affected (0.00 sec)

mysql> select column1 from enum1;
+---------+
| column1 |
+---------+
| Y       |
| Y       |
| N       |
| M       |
+---------+
4 rows in set (0.00 sec)

mysql> select column1 + 1 from enum1;
+-------------+
| column1 + 1 |
+-------------+
|           2 |
|           2 |
|           4 |
|           3 |
+-------------+
4 rows in set (0.00 sec)

mysql> select column1 from enum1 order by column1;
+---------+
| column1 |
+---------+
| Y       |
| Y       |
| M       |
| N       |
+---------+
4 rows in set (0.00 sec)

mysql> select column1 from enum1 order by field(column1, 'M', 'N', 'Y');
+---------+
| column1 |
+---------+
| M       |
| N       |
| Y       |
| Y       |
+---------+
4 rows in set (0.00 sec)

9、datetime和timestamp

类型	占用字节数	支持的时间范围
datetime	8	1000-01-01 00:00:00至9999-12-31 23:59:59
timestamp	4	1970-01-01 00:00:00至2038-01-19 23:59:59

10、标识列（即能唯一标识一条数据的字段）数据类型通常用unsigned int auto_increment或UUID()两种：当该标识列索引使用BTREE（innodb默认使用BTREE）时，使用unsigned int auto_increment更好（io速度更快、存储空间更小等），当标识列索引使用hash索引（innodb不显式支持hash索引，但当支持自适应hash索引，等后面讲）时，两者差不多。

11、最好避免使用BIT和SET类型。

4.2 schema设计中的陷阱

1、避免过多的列和过多的关联

2、避免NULL，或者使用其他值代替NULL

4.3 范式和反范式

设计方面的东西，详见原文或者参考其他博客，如这里

4.4 缓存表和汇总表

缓存表：表示存储那些可以比较简单的从schema其他表获取（但每次获取速度都比较慢）数据的表（例如，逻辑上冗杂的数据）。

汇总表：表示存储那些使用group by语句聚合的数据。

使用缓存表的情况是，比如展示一个很详细的业务数据，要关联很多张表并进行相关运算，每次查询速度都比较慢，则可以将定期查询该SQL并放到一张缓存表中，等需要的时候直接取这张缓存表中的数据即可，然后定时维护这张缓存表以更新数据。这种情况数据虽然有延迟，但对于用户来说能很快的看到数据。

使用汇总表的情况是，比如要看网站最近一个月每天的点击量，则要做group by操作，可以每天定时执行一个的SQL，将当天的点击量记录到这张汇总表中，等需要的时候直接where between就行，不用做group by。

物化视图：预先计算并存储在磁盘上的表，并通过各种策略来自动更新该表（视图）。MySQL可以用第三方工具：Justin Swanhart的Flexviews。

计数器表：再比如上面那个网站最近一个月每天的点击量的情况，可以通过定义一张下面所示的表，每次收到用户访问，就随机选一个槽进行更新（避免锁冲突）。再设置一个定时任务，每天将昨天的数据汇总到0号槽，并删除其他槽，这样就是一个统计每一天的访问量的计数器表。

CREATE TABLE `daily_click` (
  `day` date NOT NULL,
  `slot` int unsigned NOT NULL,
  `cnt` int DEFAULT 0,
  PRIMARY KEY (`day`,`slot`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

mysql> insert into daily_click values(now(), round(rand(), 2) * 100, 1) on duplicate key update cnt = cnt + 1; -- 我这里执行了六次
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from daily_click;
+------------+------+------+
| day        | slot | cnt  |
+------------+------+------+
| 2020-11-22 |   22 |    1 |
| 2020-11-22 |   29 |    1 |
| 2020-11-22 |   48 |    1 |
| 2020-11-22 |   53 |    2 |
| 2020-11-22 |   95 |    1 |
+------------+------+------+
5 rows in set (0.00 sec)

mysql> update daily_click as c,
      (select day, sum(cnt) as cnt, min(slot) as slot from daily_click group by day) as x
      set c.cnt = if(c.slot = x.slot, x.cnt, 0),
      c.slot = if(c.slot = x.slot, 0, c.slot)
      where c.day = x.day and c.day = '2020-11-22';
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> delete from daily_click where day = '2020-11-22' and slot <> 0;
Query OK, 4 rows affected (0.00 sec)

mysql> select * from daily_click;
+------------+------+------+
| day        | slot | cnt  |
+------------+------+------+
| 2020-11-22 |    0 |    6 |
+------------+------+------+
1 row in set (0.00 sec)

4.5 加快ALTER TABLE的速度

1、修改列的三种方式：

（1）ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name]：这种方法是整列换成一个新列的定义，包括列名也可以修改，会引起表的重建，即删除旧列，构造新列；

（2）ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]：这种方法也是整列换成一个新列，但是不能修改表名，只能修改属性，也会引起表的重建；

（3）ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT {literal | (expr)} | DROP DEFAULT}：这种方法局限性很高，只能修改列的默认值属性，这个语句会直接修改表的.frm文件，不涉及表数据，不会引起表的重建，因此速度很快。

即如果是需要修改的东西实际存在于.frm文件中，都可以通过直接修改.frm文件来进行修改，而不用重建表。注意：《高性能MySQL》这本书对用的MySQL版本是8.0以下的版本，MySQL8.0及以后没有.frm文件了，表结构和表数据都在整个.ibd文件的表空间中。以上三种修改，第三种也是最快的，但是只是底层原理上有了较大的变化，详见MySQL官方文档。

2、修改表结构的技巧：

（1）先在一台不提供服务的库上执行alter table操作，然后和提供服务的主库进行切换。过程（个人盲猜的）：停止从主备库同步，备用库执行alter table，重新同步主备库，待同步成功再切换主备库。问题：切换主备库会不会导致整个服务器停顿？

（2）影子拷贝。建一个新的空表，表结构为原表修改后的表结构，在新表中建三个INSERT UPDATE DELETE的触发器，将旧表数据拷贝到新表，最新数据会通过触发器更新过去，然后通过重命名表和删表的方式交换两张表。