Schema与数据类型优化

Schema与数据类型优化

• 选择优化的数据类型

  • 尽量使用可以正确存储存储数据的最小数据类型
  • 尽量使用尽可能简单的数据类型，尽量使用MySQL内建类型date/time/datetime而非字符串存储时间，此外TIMESTAMP只用了DATETIME一半存储空间且性能更优。
  • 尽量避免NULL，可以保证不出错的情况下尽量把列指定为not null（难优化，索引失效且空间大）

• 具体数据类型

  • 整数类型：tinyint, smallint, mediumint, int, bigint分别使用8, 16, 24, 32, 64位存储空间，其可存储值的范围为-2^(n-1)~2^(n-1)-1，其中n表示存储空间的位数。注意指定宽度的INT(11)等无意义。

  • 实数类型

    • decimal：存储精确的小数，最多允许65个数字。CPU不支持直接计算，需要MySQL自身实现。存储财务数据常用。decimal (m,n)中，m是指整数部分和小数部分位数之和，n则仅仅指小数部分位数。
    • float与double：浮点运算求得较精确的值。float使用4个字节存储，double占用8个字节。mysql使用double作为内部浮点计算的类型。

  • 字符串类型：在严格的SQL模式下insert的数据超过varchar和char的最大长度时会报错，非严格则自动将超过长度后面的字符删除存储，并予以警告，而不是报错。

    • VARCHAR：用于存储可变长字符串，最常见，默认仅使用必要空间，比定长类型更加节省空间。
      • 需要额外使用1个或2个字节记录字符串的长度。若列最大长度大于255字节，采用2个额外字节记录，反之1个字节。
      • UPDATE时需要做额外工作。若空间需要增长时页内空间不够，MyISAM会拆分成不同片段存储，InnoDB则分裂页来使行可以放进页内。其他存储引擎可能直接不在原位置更新。
    • CHAR：定长，存储时会自动删除所有的末尾空格（VARCHAR保留）
      • 适合存储很短的，或所有值都接近同一个长度的字符串，或经常变更的数据。

  • 为存储很大数据而设计的字符串数据类型：BLOB（二进制方式存储）/ TEXT（字符方式存储）

    • 存储引擎把每个blob和text当作一个独立对象处理。当值太大，InnoDB会利用**“外部”存储区域来进行存储，此时在每个值的行内存储一个指针**，然后在实际区域存储实际值。需要使用磁盘临时表，耗时

    • 排序是对其前max_sort_length个字节的字符进行排序，可手动设置max_sort_length的值，或使用order by sustring(column, length)

    • 不能将BLOB和TEXT列全部长度的字符串进行索引，也不能使用这些索引消除排序。（后面展开）

  • 使用枚举ENUM代替字符串类型

    • MySQL在内部会将每个值在列表中的位置保存为整数，并将**“数字-字符串”映射关系的“查找表”存储于.frm**文件中（建议不要使用数字作为ENUM枚举常量）
    • 排序时使用内部存储的整数进行排序，而不是定义的字符串进行排序（可以显示自定义）
    • 添加/删除字符串使用ALTER TABLE（创建临时表→为临时表创建索引→index_read→ index_write →将原表的索引数据写入新索引→将原表数据写入临时表→将原表进行rename）
    • 列关联时的效率：enum关联enum > varchar关联varchar > enum和varchar互相关联

  • 日期和时间类型：存储粒度为秒，计算粒度为微妙

    • DATETIME：能保存大范围的值，从1001年到9999年，精度为秒，装到为YYYYMMDDHHMMSS的整数中，与时区无关，使用8字节的存储空间
    • TIMESTAMP：1970.1.1~2038，使用4个字节存储空间，依赖时区，通常建议尽量使用timestamp

  • 位数据类型：最好避免使用

    • BIT：可以使用bit列在一列中存储一个或者多个true/false值，bit(1)就是1位，bit(2)就是2位。bit存储的是二进制0/1字符串，故内部不是ASCⅡ码的0/1。谨慎使用。
    • SET：如果需要保存很多true/false值，可将它们合并到一个set数据类型（一些列打包的位的集合）

  • 选择标识符/标识列（能唯一标识一条数据的字段）：整数√ ENUM / SET / STRING ×

  • 特殊类型数据，如IP地址推荐用无符号整数，INET_ATION()和INET_NTOA()实现转换

• schema设计中的陷阱

  • 避免过多的列：存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲数据解码成各个列。转换代价依赖于列的数量
  • 避免过多的关联
  • 避免过度使用枚举：每次增加/删除都要ALTER TABLE
  • 避免使用NULL，建议用空值代替，但不用过度害怕NULL

• 范式和反范式

  • 范式化优点：
    • 更新操作更快，修改时只需要修改较少的数据
    • 表更小，可以更好的放在内存里，执行操作会更快
    • 没有多余的数据，可以减少distinct或GROUP BY的操作
  • 范式化缺点：通常需要关联，关联代价昂贵，也可能使一些索引策略无效
  • 反范式化优点：
    • 所有的数据都在一张表中，可以避免关联。单表也能独立使用更有效的索引策略。
    • 数据量大时可能比关联快很多，因为关联是随机I/O，全表是顺序I/O
  • 反范式化缺点：冗余的多余数据，更新更慢，且表大，放到内存中占用大，容易挤出热数据。

• 混用范式化和反范式化：部分范式化的schema，缓存表和其他技巧

  • 缓存表：表示存储那些可以比较简单地从schema其他表获取（但每次获取速度都比较慢）数据的表（例如逻辑上冗杂的数据）—— 优化搜索和检索查询语句
    • 当需要展示一个很详细的业务数据时，需要关联很多张表并进行相关运算，每次查询速度都比较慢，则可以将定期查询该SQL并放到一张缓存表中，等需要的时候直接取这张缓存表中的数据即可，然后定时维护这张缓存表以更新数据。
  • 汇总表：表示存储那些使用group by语句聚合的数据
    • 要看网站最近一个月每天的点击量，则要做group by操作，可以每天定时执行一个的SQL，将当天的点击量记录到这张汇总表中，等需要的时候直接where between就行，不用做group by
  • 重建汇总表和缓存表时需要通过**”影子表“**保证数据在操作后仍然能用，操作结束后使用原子的重命名切换影子表和原表。

• 物化视图：预先计算并存储在磁盘上的表，并通过各种策略来自动更新该表（视图）（一笔带过了）

• 计数器表：统计一个网站最近一个月每天的点击量的情况，可以通过定义一张下面所示的表，每次收到用户访问，就随机选一个槽进行更新（避免锁冲突）。再设置一个定时任务，每天将昨天的数据汇总到0号槽，并删除其他槽，这样就是一个统计每一天的访问量的计数器表。

• 加快ALTER TABLE的速度（原本：创建临时表→为临时表创建索引→index_read→ index_write →将原表的索引数据写入新索引→将原表数据写入临时表→将原表进行rename）

  (2条消息) 【五】高性能MySql笔记——快速ALTER TABLE（Hack手法）_Codertoy-酷玩的博客-CSDN博客