第四章 Schema与数据类型优化的第二篇笔记。

伟大的心胸，应该表现出这样的气概——用笑脸来迎接悲惨的厄运，用百倍的勇气来应付一切的不幸。

— 鲁迅

2. MySQL schema设计陷阱

2.1 太多的列

MySQL的存储引擎API工作时需要在核心服务层和存储引擎层之间通过行缓冲格式拷贝数据，然后在核心服务中将缓冲内容解码成各个列，这个转换过程代价很高。

2.2 太多的关联

所谓的”实体-属性-值“(EAV)设计模式是一个糟糕的设计模式。MySQL规定每个关联操作最多61张表，但是EAV需要很多自关联，实际上，太多关联时解析和优化的代价也会成为MySQL的性能问题。

2.3 全能的枚举

注意防止使用过多枚举，一般情况下不要使用枚举。

2.4 非此发明(not invent here)的NULL

一般情况字段值不推荐位null，即使需要事实上的空值时也不一定非要使用null，可以使用0或者没一个特殊值。但是确实需要null值时也不要害怕使用null。

3. 范式和反范式

写密集的场景下，范式化设计schema会有不错的效果:

  • 范式化的更新操作通常比反范式要快
  • 数据较好的范式化时，只有很少或者没有重复数据，修改的数据会更少
  • 范式化的表通常更小，操作会更快
  • 很少的冗余，意味着检索时更少需要distinct或者group by与语句

范式化缺点：范式化设计的表通常需要很多关联，这不仅代价昂贵，也可能导致索引失效。范式可能会将同属一个索引的列分散到不同的表中。

反范式的schema数据都在一张表里，可以很好的避免关联。实际项目开发时经常混用范式和反范式。但这其中需要平衡更新和查询的频率来设计schema

4. 缓存表和汇总表

有时候提升性能的最好方法是在同一张表中保存衍生的冗余数据。

  • “缓存表”来表示存储那些可以比较简单的从schema其他表获取(但是获取比较慢)数据的表(逻辑上冗余的数据)。
  • “汇总表”表示的是使用Group By语句聚合数据的表(逻辑上不冗余)

为什么建立汇总表：实时计算统计值是很昂贵的操作，因为要么需要扫描表中的大部分数据，要么查询语句只能在某些特定的索引上才能有效运行，而这类索引一般会对update有影响。

缓存表：对优化搜索和检索查询语句很有效。这些查询语句经常需要特殊的表和索引。

此外，使用缓存表和汇总表时，需要决定时实时维护数据还是定期重建。重建并不只是节省资源，也可以 保持表有极少的碎片，以及有完全顺序组织的索引。

重建表时通常需要保证原表在重建过程中仍然可用，可以通过使用“影子”表来实现：完成建表之后可以通过一个原子的重命名操作来切换影子表和原表。例如，要重建my_table，可先新建my_table_new，填充好数据之后，和原表做切换：

mysql>drop table if exists my_table_new,my_table_old;
mysql>create table my_table_new like my_table;
mysql>rename table my_table to my_table_old,my_table_new to my_table;

这样就可以实现在下一次重建之前一致保留旧表数据，如果新表有问题，可以快速切换回滚。

4.1 物化视图

物化视图实际上是预先计算并存储在磁盘上的表，通过各种策略来刷新和更新。不过MySQL原生并不支持物化识图，可通过第三方工具来实现支持。

4.2 计数器表

计数器表可以单独建立一张表：

mysql> create table hit_counter(
		cnt int unsigned not null	
	)ENGIN=InnoDB;

每次点击之后都会导致对计数器更新：

mysql> update hit_counter set cnt = cnt+1;

问题在于，对于任何想要更新的事务来说，这条记录上都有一个全局互斥锁，使得无法并发，只能串行化。可以这样做：将记录保存在多行中，每次随机更新一条记录，然后统计结果时使用sum来计算：

-- 修改表
mysql> create table hit_counter(
    	slot tinyint unsigned not null primary key,
		cnt int unsigned not null
	)ENGIN=InnoDB;
-- 初始化时插入100条数据，然后每次更新随机一个slot来更新
mysql> update hit_counter set cnt = cnt+1 where slot =RAND() * 100;
-- 统计结果
mysql>select sum(cnt) from hit_counter;

一个需求是每个一段时间开始一个新的计数器(每天一个)

-- 1.修改表
mysql> create table daily_hit_counter(
    	day date not null,
    	slot tinyint unsigned not null ,
		cnt int unsigned not null,
    	primary key(day,slot)
	)ENGIN=InnoDB;
-- 2.不需要初始化，采用on duplicate key update来替代
mysql>insert into daily_hit_counter(day,slot,cnt)
	values(CURRENT_DATE,RAND()*100.1)
	on duplicate key update cnt = cnt + 1;
-- 3.避免表太大，可以合并所有结果到0号slot
mysql> update daily_hit_counter as c
		inner join(
        	select day,sum(cnt) as cnt ,min(slot) as mslot
             from daily_hit_counter
             group by day
        ) as x using(day)
        set c.cnt  = if(c.slot = x.mslot,x.cnt,0)
        set c.slot = if(c.slot = x.slot,0,x.slot);
-- 4.然后删除其他slot
mysql> delete form daily_hit_counter where slot <>0 and cnt = 0;

为了提高读的速度，可以增加一些额外索引，增加冗余项，甚至创建缓存表和汇总表。这样会导致写查询的负担，也增加了额外的维护操作，但设计高性能的数据库时这是必要的技巧：更快的读，更慢的写，但是同时也增加了读操作和写操作的的开发难度。

5. 加快alter table操作的速度

MySQL执行大部分修改表操作都是用新结构创建一个空表，然后从旧表中查询出所有数据再插入新表，然后删除旧表。

一般而言，大部分alter table操作将会中断MySQL的服务。对于常见的场景，能使用的技巧：

  • 一种是在一台不提供服务的机器上执行alter table操作，然后和提供服务的主库做切换；

  • 影子拷贝：用要求的表结构创建一张表，然后通过原子更名交换两张表名实现切换

修改表某一列的默认值为5：

-- 方法1：
mssql>alter table my_table
		modify column colu1 tinyint(3) not null default 5;
-- 方法1的sql会拷贝整个表到一张新表，只是为了修改默认值
-- 理论上，可以跳过新建表的过程，列的默认值存在表的.frm文件中，直接修改这个文件就行，所有的modify column都会导致表重建。会很慢。

-- 方法2：
mysql> alter table my_table
		alter column colu1 set default 5;
-- 这个方法会直接修改.frm文件，将很快。

6. 总结

简单的原则：

  • 尽量避免过度设计
  • 使用小而简单的数据类型，除非真实数据有确切需要，否则尽可能避免null值
  • 尽量使用相同的字符串，在临时表和排序时肯恶搞导致悲观的按最大长度分配内存
  • 尽量使用整型定义标识列
  • 避免使用MySQL已经遗弃的特性，例如指定浮点数的精度，或者整数的显示宽度
  • 小心使用enum和set
  • alter table时可以在备机上完成之后且为主库，或者影子重建方式。