02 表结构优化
Yishto
2018-03-23 18:26:32
Categories：
Tags：
- 由于MySQL数据库是基于行(Row)存储的数据库 , 而数据库操作 IO 的时候是以 page(block) 的方式 , 也就是说 , 如果我们每条记录所占用的空间量减小 , 就会使每个 page 中可存放的数据行数增大 , 那么每次 IO 可访问的行数也就增多了 . 反过来说 , 处理相同行数的数据 , 需要访问的 page 就会减少 , 也就是 IO 操作次数降低 , 直接提升性能 . 此外 , 由于我们的内存是有限的 , 增加每个page中存放的数据行数 , 就等于增加每个内存块的的缓存数据量 , 同时还会提升内存缓存数据命中的几率 , 也就是缓存命中率 .
数据类型选择
数据库操作中最为耗时的操作就是 IO 处理 , 大部分数据库操作 90% 以上的时间花在了 IO 读写上面 . 所以尽可能减少 IO 读写量 , 可以在很大程度上提高数据库操作的性能 .
我们无法改变数据库中需要存储的数据 , 但是我们可以在这些数据的存储方式方面花一些心思 . 下面的这些关于字段类型的优化建议主要适用于记录条数较多 , 数据量较大的场景 , 因为精细化的数据类型设置可能带来维护成本的提高 , 过高优化也可能会带来其他的问题 :
- 数字类型 : 非万不得已不要使用 DOUBLE , 不仅仅是存储长度的问题 , 同时还会存在精确性的问题 . 同样 , 固定精度的小数 , 也不建议使用 DECIMAL , 乘以固定背书转化成整数存储 , 可以大大节省存储空间 , 且不会带来任何附加维护成本 . 对于整数的存储 , 在数据量较大的情况下 , 建议区分开 TINYINT / INT / BIGINT 的选择 , 因为三者所占得存储空间也有很大的差别 , 能确定不会使用附属的字段 , 建议添加 unsigined 定义 . 当然 , 如果数据量较小的数据库 , 也可以不用严格区分三个整数类型 .
- 字符串类型 : 非万不得已不要使用 TEXT 数据类型 , 其处理方式决定了他的性能要低于 char 或者 varchar 数据的处理 . 定长字段 , 建议使用 CHAR 类型 , 不定长字段尽量使用 VARCHAR ,且仅仅设定适当的最大长度 , 而不是非常随意的给一个很大的最大长度限定 , 因为不同的长度范伟 , MySQL 也会有不一样的存储处理 .
- 时间类型 : 尽量使用 TIMESTAMP 类型, 因为其存储空间只需要 DTETIME 类型的一半 , 对于只需要精确到某一天的数据类型 , 建议使用DATE类型 , 因为他的存储空间只需要3个字节 , 比 TIMESTAMP 还少 . 不建议通过 INT 类型存储一个 unix timestam 的值 , 因为这不太只管 , 也给维护带来不必要的麻烦 , 同时还不会带来任何好处 .
- ENUM & SET :对于状态字段 , 可以尝试使用ENUM 来存放 , 因为可以极大的降低存储空间 , 而且即使需要增加新的类型 , 只要增加于末尾 , 修改结构也不需要重建表数据 . 如果是存放可预先定义的属性数据呢 ? 可以尝试使用 SET 类型 , 即使存在多种属性 , 同样可以游刃有余 , 同时还可以节省不小的存储空间 .
- LOB 类型 : 强烈反对在数据库中存放LOB类型数据 , 虽然数据库提供了这样的功能 , 但这不是他擅长的 , 我们更应该让合适的工具做他擅长的事情 , 才能将其发挥到极致 . 在数据库中存储 LOB 数据就像让一个多年前在学校学过一点 Java 的营销专业的人员来写Java 代码一样 .
字符编码
- 字符集直接决定了数据在MySQL中的存储数据编码方式 , 由于同样的内容使用不同字符集表示所占用的空间大小会有较大的差异 , 所以通过使用合适的字符集 , 可以帮助我们尽可能的减少数据量 , 进而减少 IO 操作次数 .
- 纯拉丁字符能表示的内容 , 没有必要选择 latin1 之外的其他字符编码 , 因为这回节约大量的存储空间 .
- 如果我们可以确定不需要存放多种语言 , 就没必要非得使用UTF8或者其他UNICODE 字符类型 , 这会造成大量的
的存储空间浪费 .
- MySQL的数据类型可以精确到字段 , 所以当我们需要大型数据库中存放多字节数据的时候 , 可以通过不同表不同字段使用不同的数据类型来较大程度减小数据存储量 , 进而降低 IO 操作次数并提高缓存的命中率 .
适当拆分
有些时候 ， 我们可能会希望将一个完整的对象对应于一张数据库表 ， 这对于应用程序开发来说是很好的 ， 但是有些时候可能会在性能上带来较大的问题 .
当我们的表中存在类似于 TEXT 或者是很大的 VARCHA 类型的大字段的时候 , 如果我们大部分访问的这张表的时候都不需要这个字段 , 我们该义无反顾的将其拆分到另外的独立表中 , 以减少常用数据所占用的存储空间 . 这样做的一个明显的好处就是每个数据块中可以存储的数据条数可以大大增加 , 既减少物理 IO 次数 , 也能大大提高内存中的缓存命中率 .
- 上面几点的优化都是为了减少每条记录的存储空间大小 , 让每个数据库中能够存储更多的记录条数 , 以达到减少 IO 操作次数 ,提高缓存命中率 . 下面这个优化建议可能很多开发人员都会觉得不太理解 , 因为这是典型的反范式设计 , 而且也和上面的几点优化的目标相违背 .
适当冗余
为什么我们要冗余 ? 这不是增加了每条数据块可存放记录条数吗?
确实 , 这样做是会增大每条记录的大小 , 降低每条记录中可存放数据的条数 , 但是在有些场景下我们仍然还是不得不这样做 :
- 被频繁引用且只能通过 Join 2张(或者更多) 大表的方式才能得到的独立小字段 .
- 这样的场景由于每次 Join 仅仅只是为了取得某个小字段值 , Join 到的记录又大 , 会造成大量不必要的 IO , 完全可以通过空间换取时间的方式来优化 . 不过 , 冗余的同时需要确保数据的一致性不会遭到破坏 , 确保更新的同时冗余字段也被更新 .
尽量使用 NOT NULL
NULL 类型比较特殊 , SQL 难优化. 虽然 MySQL NULL 类型和 Oracle 的 NULL 有差异 , 会进入索引中 , 但如果是一个组合索引 , name这个 NULL 类型的字段会极大影响整个索引的效率 . 此外 , NULL 在索引中的处理也是特殊的 ,也会占用额外的存放空间 .
很多人觉得 NULL 会节省一些空间 , 所以尽量让NULL 来达到节省 IO 的目的 , 但是大部分时候这会适得其反 , 虽然空间上可能确实有一定节省 , 到时带来了很多其他的优化问题 ,不但没有将 IO 量节省下来 , 反而加大了 SQL 的 IO 量 . 所以尽量确保 DEFAULT 值不是 NULL 也是一个很好的表结构设计优化习惯 .