MySQL优化（一）：表结构优化

表优化

此文章用于记录《高性能MySQL》一书的知识点。

数据类型的选择

• 避免列的值为NULL

  查询包含值为NULL的列，会使索引、索引统计和值比较更加复杂，如果计划在列上建索引，就应该尽量避免索引列含有NULL值

• 在保证足够的范围内，选择最小、最简单的数据类型
  更小的数据类型占用更少的磁盘、内存和CPU缓存、处理也更快

• VARCHAR和CHAR

  • VARCHAR需要适用1或2两个额外字节记录字符串长度，适用于:
  1. 字符串列最大长度比平均长度大很多
  2. 列更新少，所以不用担心碎片问题
  3. 使用了UTF-8等复杂字符集，每个字符用不同字节数存储
  • CHAR适用的场景
  1. 经常变更的数据，因为定长的CHAR不易产生碎片
  2. 非常短的列，CHAR不需要额外的字节，因此存储空间更有效率

• 日期和时间类型

  • DATETIME 使用8字节，不能自动根据时区转换
  • TIMESTAMP 使用4字节，值与时区有关，会自动转换

  通常情况下应尽量使用TIMESTAMP，因为它的空间效率更高

选择标识符（主键）的类型

在满足足够的范围需求，并且预留未来增长空间的前提下，应选择最小、最简单的数据类型

• 整数通常是标识列最好的选择，因为整数不仅快而且可以使用自增等特性
• 应避免使用字符串作为标识列
  • 原因：
  1. 字符串更消耗空间，同时比较时比数字类型慢
  2. 随机的字符串如：MD5、SHA1或者UUID，插入新数据时会随机地插入到索引的不同位置，导致页分裂、磁盘随机访问。

错误的表结构

• 一张表中有太多列

  MySQL的存储引擎需要在服务器层与存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。过多的列会导致上述过程转换代价过高。

• 太多的关联

  如果希望查询执行快速且并发性好，单个查询最好在12个表内做关联。

适当建立冗余数据

• 混用范式和反范式

  范式化的表，更新更快，同时只有很少或没有重复的数据，表更小，但是查询时通常需要昂贵的关联操作，可能导致部分索引失效，而反范式会增加冗余数据但可以减少或避免关联操作。

• 建立缓存表和汇总表

  案例：统计某个表的总数时，可以通过
  SELECT COUNT(*) FROM table，但次操作需要扫描表中大部分数据，效率较低。可以通过建立汇总表来统计表中的数据数，并在每次插入数据时更新汇总表中的数据。

参考

《高性能的MySQL》