MySQL-Schema与数据类型优化

文章目录

目录

文章目录

前言

一、优化的数据类型

二、schema(数据库)优化

总结

---

前言

主要介绍了MySQL的Schema与数据类型优化。

---

一、优化的数据类型

• 更小的、简单的、避免NULL(InnoDB用单独的位存储null值，对稀疏数据有很好的空间效率，MyISAM不是这样)
• 整数
• 实数
  精确DECIMAL代价高，浮点FLOAT不精确，可以乘以倍数10ⁿ存在BIGINT中解决这个问题
• 字符串
  • VARCHAR(节省空间，但修改行数据时会导致额外开销(MyISAM将行分段存储，InnoDB进行页分裂))、CHAR
    存储引擎自行决定如何存储这些类型的数据 (如Memory存储引擎只能存定长的数据，定义为VARCHAR也是按照定长存)
  • BLOB大二进制数据串、TEXT大字符串，InnoDB用外部空间存储BLOB和TEXT对象，在行内只存指向它们的指针，对这两种类型的数据排序时只对其最前面的部分字节排序，也不能用其全部长度数据做索引
    Memory存储引擎不支持BLOB和TEXT类型数据，需要构建Memory内存临时表时，因为有BLOB或者TEXT类型数据会转变为存MyISAM磁盘临时表
    最好不用BLOB和TEXT，实在不行BLOB字段上用SUBSTRING将列值截取并转换为字符串(但是不能超过Memory内存临时表最大数据量否则也会转为MyISAM磁盘临时表)
  • 枚举ENUM代替字符串类型
    ENUM会将字符串存为整数，且ENUM中的字符串紧凑的存储，可以减小存储空间的占用，这样在枚举类字符串上建立主键会更高效，适合于与不易改变的字符串相关联
• 时间类型
  DATETIME(大范围、不分时区、空间效率低)、TIMESTAMP(小范围、分时区、空间效率高)
• 位数据类型
  MySQL将BIT视为字符串(ASCII码对应)，数字上下文场景中视作数字(2进制转换成10进制)，最好不要使用BIT而是用(一位)char(0)或者(多位)SET代替
  MyISAM将BIT打包存储、InnoDB和Memory将BIT存在最小整数里不能节约内存
• 选择标识符
  整数√、ENUM&SET尽量不要、字符串×
• 特殊类型数据
  不直接与实际内置类型一致的数据类型，如低于秒级精度的时间戳、IPv4地址(人们用VARCHAR(15)存，实际应该是无符号整数)

二、schema(数据库)优化

• schema设计中的陷阱
  • 太多列(缓存中将编码后列转换成行数据耗费时间)、太多关联(MySQL规定关联最多61个表)、过渡使用ENUM枚举、使用变相枚举-集合SET、特定的NULL(-1表示NULL)
• 范式/反范式
  一般混合使用
  • 范式化：一个综合表分裂成没有冗余数据的多个表，表变小，查询速度快，但是查询时可能需要建立关联，降低效率
  • 反范式：有很多冗余数据，查询无需建立关联，但是修改一个数据可能导致需要很多数据的关联修改
• 缓存表/汇总表
  通过缓存表和汇总表可以大大提升查询的读效率，虽然写成本和维护成本都增加，但是如果读远大于写，效率提升还是很可观的
  • 缓存表：存放从schema其他表中获取的经常被查询的数据(在整个系统中属于冗余数据)的表
    利于优化搜索和检索查询数据，用缓存表(使用不同存储引擎)建立含有特殊索引结构的表以提高查询效率
  • 汇总表：存放使用GROUP BY语句聚合非冗余数据的表
    方便统计非精确的累计数据，每个小时统计一张汇总表最后累加以得到直至该时间所有查询语句总数
  • 物化视图：预先计算并存储在磁盘上的表
    对缓存表和汇总表的进一步优化，通过提取源表更改，可以增量的重新计算物化视图的内容，还可以知道每行数据的旧版本，无需查找源表本身
  • 计数器表：统计数量的缓存表，维护可能遇到并发问题
    用多个变量累计计算，防止并发时只能对计数变量上互斥锁，导致事务串行执行
• 加快ALTER TABLE操作的速度
  无服务的机器上执行ALTER TABLE然后和提供服务的主库切换；影子拷贝，利用新表结构建立表，然后通过重命名+删表交换两张表；通过修改表的 .frm文件修改列值
  • 只修改 .frm 文件：创建相同结构的空表更改其.frm文件，关闭正在使用的表且禁止打开，交换.frm文件，释放表锁
    移除一个列的AUTO_INCREMENT属性、增加/移除/更改ENUM和SET常量 无需重建表，只需改.frm文件
  • 快速创建MyISAM索引：先禁用索引，导入数据后，再通过排序创建索引(提高索引创建效率)
    对唯一索引无效

---

总结