高性能MySQL笔记(第四章 Schema与数据类型优化)

p147~p176.

数据类型优化的简单原则

1. 更小的通常更好.
2. 简单就好.
3. 尽量避免NULL.

整数类型

• TINYINT(8), SMALLINT(16), MEDIUMINT(24), INT(32), BIGINT(64).
• 所有整数类型可以加上UNSIGNED表示无符号数, 如TINYINT UNSIGNED.
• 整数类型可以指定宽度, 不会限制值的合法范围. 如int(11)与int(20)是一样的.

小数

• 精确类型, DECIMAL, 一般用于存储财务数据. 数据量比较大的时候, 可以考虑用BIGINT代替DECIMAL, 将数据乘以相应的倍数.
• 不精确类型, FLOAT和DOUBLE.

字符串类型

每个字符串可以定义自己的字符集和排序规则.
VARCHAR类型

• 可以存储可变长字符串, 需要使用1或2个额外字节记录字符串长度.
• UPDATE可能是行变得比原来长, 在一个页内无法放下, 导致一行无法放在一个页内, 这时MyISAM会将行拆成不同的片段存储, InnoDB需要分裂页, 生成一个新页来存储这个行.
• 适用情况: 字符串列的最大长度比平均长度大很多, 列的更新很少, 所以碎片不是问题; 使用了像UTF-8这样复杂的字符集, 每个字符使用不同的长度进行存储.
• InnoDB将过长的VARCHAR存储为BLOB.

CHAR类型

• 类型定长, MySQL总是根据定义的字符串长度分配足够的空间. 删除末尾空格.
• 适合存储固定长度的字符串, 如MD5值,

VARCHAR(5)与VARCHAR(200)的存储开销相同, 但是更长的列消耗的内存更多, 尤其适用内存临时表进行排序或操作.

BLOB类型和TEXT类型

• TINYBLOB, SMALLBLOB, BLOB, MEDIUMBLOB, LONGBLOB. BLOB = SMALLBLOB. TEXT类型类似.
• MySQL把BLOB和TEXT值当作一个独立的对象处理. 值太大时, InnoDB使用"外部"存储区域来进行存储. 此时每个值在行内需要1~4个字节存储一个指针, 然后在外部存储实际值.
• 区别: BLOB类型存储的是二进制数据, 没有排序规则和字符集, TEXT有.
• 排序的特殊: 只对每个列的最前max_sort_length字节而不是整个字符串做排序. 若只需要排序前面一小部分字符, 则使用ORDER BY SUSTRING(column, length).

SET max_length_for_sort_data = 1024;
SHOW VARIABLES LIKE '%max_length_for_sort_data%';

日期和时间类型

MySQL能存储的最小时间为秒.

• DateTime范围从1001年到9999年, 精度为秒.与时区无关, 8字节存储空间. 例如"2008-01-16 22:37:08".
• TimeStamp保存了从1970年1月1日以来的秒数, 与Unix时间戳相同. 范围从1970年到2038年, 4字节存储空间.
  From_UnixTime()把timestamp转成datetime, Unix_TimeStamp把datetime转成timestamp.
• TimeStamp与时区有关, 存储值为0的TimeStamp在美国东部时区显示为"1969-12-31 19:00:00".

位数据类型

• BIT, 可以在一列中存储一个或多个true/false值. BIT(n)定义包含n个位的字段. 这个类型的设计有些难理解, 最好避免使用这种类型.
• SET, 可以保存多个true/false值. 类似c#的Flags, 用一列保存是否可读, 是否可写, 是否可删除等信息.

选择标识符

标识符尽量选择Int系列进行存储, 尽量不使用字符串. 如果是字符串, 应该是有序的, 否则会造成大量碎片, 大大降低读和写的效率.

MySQL注意的地方

• 一个表不要使用太多的列, 不要超过1000列.
• 不要太多的关联, 一般来说, 一次查询最好在12个表内做关联.
• 防止过度使用枚举
• 尽量不使用NULL, 但是需要的时候可以使用, 如DateTime类型

范式和反范式

• 范式的优点是存储的数据量小, 需要更新值的地方少.
• 范式的缺点是一次查询可能要关联很多表.
• 反范式是数据冗余, 提高了查询速度, 但是更新值的地方变多了, 如果漏了修改的地方, 会造成数据不统一.

缓存表和汇总表

缓存表, 表示存储那些可以简单地从其他表获取数据的表, 冗余字段.
汇总表, 表示保存的是使用group by语句聚合数据的表, 累加计算.

物化视图

预先计算并且存储在磁盘上的表, 是物理表. 通过各种各样的策略刷新和更新.
MySQL原生不支持物化视图, Fexviews可以自己实现物化视图. 参考swanhart-tools

计数器表

Update hit_counter Set cnt = cnt + 1;

要想提高并发性能, 往这个表添加100行数据, 选择一个随机的slot进行更新

Update hit_counter set cnt = cnt + 1 where slot = rand() * 100;

执行聚合查询, select sum(cnt) from hit_counter.

如果希望减少行数, 可以开启一个定时任务, 定期将数据汇总到第0行, 将其他行删除.