高性能MySQL第四章

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

（2）char类型会删除末尾的空格再进行存储，而varchar不会删除末尾空格。

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

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

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互相关联

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

除了特殊行为之外，尽量使用timestamp,因为它比datetime空间效率更高。

10.最好避免使用BIT和SET类型。

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

4.2 schema设计中的陷阱

1.避免过多的列和过多的关联
2.避免NULL，或者使用其他值代替NULL

4.3 范式和反范式

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

4.3.1 范式化的优点和缺点

优点：

1.范式化的更新操作更快，只需要更改较少的数据。

2.范式化的表更小，可以更好的放在内存里，执行操作会更快。

3.没有多余的数据，可以减少distinct或GROUP BY的操作。

缺点：

通常需要关联，关联代价昂贵，也可能使一些索引策略无效。

4.3.2 反范式的优点和缺点

优点:

1.所有的数据都在一张表中，可以避免关联。

2.不关联的时候即使全表扫描，也是顺序IO。

缺点：

1.冗余的多余数据，更新更慢

2.表大，放到内存中，占用大，容易挤出热数据

4.4 缓存表和汇总表

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

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

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

4.4.1物化视图

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

4.4.2 计数器表

计数器表：统计一个网站最近一个月每天的点击量的情况，可以通过定义一张下面所示的表，每次收到用户访问，就随机选一个槽进行更新（避免锁冲突）。再设置一个定时任务，每天将昨天的数据汇总到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

然后网站的每一次点击都会对计数器表更新，随机放到一个槽：

insert into daily_click values(now(), round(rand(), 2) * 100, 1) on duplicate key update cnt = cnt + 1;

合并每天的所有槽的点击到0号槽

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';

最后删除其他槽

delete from daily_click where day = '2020-11-22' and slot <> 0;

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文件来进行修改，而不用重建表。

2.修改表结构的技巧：

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

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