mysql schema数据混乱_高性能MySQL笔记(第四章 Schema与数据类型优化)

p147~p176.

数据类型优化的简单原则

更小的通常更好.

简单就好.

尽量避免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行, 将其他行删除.