《高性能MySQL》 第四章 Schema与数据类型优化 读书笔记

Schema与数据类型优化

选择优化的数据类型

简单就好

• 所占用的空间和CPU处理周期数更少

避免NULL

• 允许为NULL的索引占用更多的空间（每个索引占用额外1个BIT）
• 可以考虑NULL的情况：列值很稀疏（NULL值很多的情况）

整数类型

• TINY SMALL MEDIUM INT BIG

实数类型

• DECIMAL只是一种存储格式，MySQL5.0以上支持存储65位，在运算时仍会转为double
• 只在进行精确的小数计算时建议选择DECIMAL，如果数据量较大时，可以使用BIGINT计算避免DECIMAL计算代价高与浮点数存储不精确的问题

字符串类型

• CHAR适用于长度较短且固定长度的字符串，优点是不易产生碎片

• VARCHAR

  • VARCHAR当字符串长度小于等于255时，长度占1个字节，否则占用2个字节

  • 问题：VARCHAR(5)与VARCHAR(200)所占用磁盘空间相同，实际使用有何区别？

    • VARCHAR(5)在内存中使用隐式临时表进行排序时(执行计划中出现了Using temporary)按固定大小5个字节（5.X以上为字符数）计算排序，而VARCHAR(200)占用200字节，占用内存与计算代价高
    • 索引磁盘占用量：在不指定索引长度的情况下，默认时，VARCHAR(5)占用索引大小(UTF8编码)5*3 + 2(长度) + 1(NULL) = 18，VARCHAR(200)占用603 （待定）

• BLOB与TEXT类型

  • 只能对前max_sort_length个字节进行排序
  • 第一章已谈到使用了BLOB与TEXT字段的内存临时表会强制转为MYSIAM表，处理时I/O会增大，即使配置了虚拟磁盘（将内存划出一块区域，作为磁盘使用），也会由于系统开销增大而导致性能严重下降
  • 如果实在需要使用BLOB与TEXT字段，可以使用SUBSTRING将BLOB与TEXT转为字符串
  • 当内存临时表大小超过max_heap_table_size或超过tmp_table_size时，内存临时表还是会转为MYSIAM磁盘临时表

• 枚举类型

  • 存储紧凑，1-2个字节
  • 但枚举的字符串列表固定，修改/删除时需要alter table(如果仅在队尾添加元素不用ALTER)

日期和时间类型

• DATETIME

  • 1001-9999
  • 8字节

• TIMESTAMP

  • 1970-2038
  • 4字节
  • 默认NOTNULL

• 如果要存储更小粒度的时间可借助bigint或使用MariaDB

位数据

• BIT

  • 5.0之前BIT与TINYINT相同

  • 5.0之后

    • MYSIAM引擎中，BIT(1)占1位，BIT(17)占3个字节
    • 在Innodb和Memory中，按最小整数处理

• SET

  • 用途：保存多个true/false值

  • 用法：CREATE TABLE acl (
    perms SET(‘CAN_READ’, ,CAN_WRITE, ‘CAN^DELETE’) NOT NULL
    )
    SELECT perms FROM acl WHERE FIND_INSET(‘CAN_READ’, perms);用于查找已经置位的记录

  • 特点

    • 更改时需要使用ALTER TABLE
    • 不支持索引

选择标识符的原则

• 关联时将关联字段选用相向的数据类型（UNSIGNED与非UNSIGNED不混用），以避免可能出现的隐式转换而导致的性能问题
• 在满足需求的前提下尽可能选用小的数据类型
• 应尽量避免选用字符串类型作为标识列，其消耗空间，且通常比数字类型慢，MYSIAM使用压缩索引，会导致查询较慢(压缩索引即前缀压缩索引，如performance字任串的索引地址类似于(7,ance)其中7为perform的长度)

特殊类型数据

• IP地址选用INT UNSIGNED 保存，使用权INET_ATON() 和INET_NTOA()转换

MySQL schema设计中的陷阱

数据在服务器层和存储引擎层中通过行缓冲拷贝数据，在服务器层将数据转换为各个列，不定长的MYSIAM行和INNODB行总是需要转换，而转换的代价与列数成正比，因此列数越多，转换时间消耗就越大

MYSQL限制最多关联61张表，建议查询时关联表小于12个

枚举ALTER时代价过高

DATETIME默认如果设置为全0，可能会出现问题

范式理论

缓存表和汇总表

缓存表：简单从其它schema获取的数据表（子表）

汇总表：在其它schema上进行GROUPBY聚合后的表

实例：计算过去24小时的网站点击量，可以采用每小时计算汇总计算一次点击量，将前23小时的汇总值勤和当前小时的计数值进行SUM求和，避免全表扫描

物化视图

• 即预先计算并且存储在磁盘上的表，MYSQL并不原生支持物化视图，Flexviews可通过BINLOG计算更新物化视图

计数器表

• 如果在表中采用单条记录存储计数值，只有串行执行才可保证计数值无误，这样在大并发下会影响性能，可以采用类似于LongAdder的方案，在表中设置多条计数值，每次请求随机给其中的一条记录加1，求多条的总和作为总的计数值

加快ALTER TABLE操作的速度

所有MODIFY COLUMN操作均会导致表的重建

• 修改FRM文件

  • 1. 创建一张有相同结构的空表(辅助表)，并进行所需要的修改（例如增加ENUM常量）。
  • 2. 执行FLUSH TABLES WITH READ L0CK这将会关闭所有正在使用的表，并且禁止任 何表被打开。
  • 3 .交换,frm文件.
  • 4.执行UNLOCK TABLES来释放第2步的读锁。
  • 5.删除辅助表

• 如果仅仅修改列的默认值可使用ALTER TABLE sakila.film -> ALTER COLUMN rentaljuration SET DEFAULT 5

快速重建MYSIAM索引

• 首先DISABLE KEYS ，再插入数据，最后ENABLE KEYS（对唯一索引无效）