良好的逻辑设计和物理设计是高性能的基石, 应该根据系统将要执行的查询语句来设计 schema
反范式的设计可以加快某些类型的查询, 单同时可能使另一类型的查询变慢, 比如添加计数表和汇总表是一种很好的优化查询的方式, 但这些表的维护成本可能会很高
1. 选择优化的数据类型
更小的通常更好
? 应该尽量使用可以正确存储数据的最小类型, 更小的数据类型通常更快, 因为他们占用更少的磁盘, 内存和 CPU 缓存, 并且处理时需要的 CPU 周期更少
简单就好
? 更简单的数据类型的操作通常需要更少的 CPU 周期例如, 整型数字比字符操作代价更低, 因为字符集和校对规则 (排序规则) 使字符比较相对整型数字比较更复杂比如, 应使用 INTERGER 存储 IP 地址(inet_aton)
尽量避免 NULL
? 通常情况下, 最好指定列为 NOT NULL 如果查询中包含可为 NULL 的列, 对 MySQL 来说更难优化, 因为可为 NULL 的列使得索引, 索引统计和值比较非常复杂, 可为 NULL 的列会使用更多的存储空间, 当可谓 NULL 的列被索引时, 每个索引记录需要一个额外的字节但是把可为 NULL 的列改成 NOT NULL 带来的性能提升比较小, 但如果计划在列上创建索引, 就应该避免设计成可为 NULL 的列
1.1 整数类型
整数类型 | 占用空间 | 范围 |
---|---|---|
TINYINT | 8 | [-2^7,2^7-1] |
SMALLINT | 16 | [-2^15,2^15-1] |
MEDIUMINT | 24 | [-2^23,2^23-1] |
INT | 32 | [-2^31,2^31-1] |
BIGINT | 64 | [-2^63,2^63-1] |
整型类型有可选的 UNSIGNED 属性, 表示不允许负值, 可以使原本正数的上线提高一倍有符号和无符号类型使用相同的存储空间, 并具有相同的性能整型之间相互计算, 是以 64 位的 BIGINT 作为中间类型进行计算的
1.2 实数类型
实数是带有小数部分的数字, 可以使用 DECIMAL 存储比 BIGINT 还大的整数
DECIMAL 类型用于存储精确的小数, 支持精确计算例如, DECIMAL(18,9)小数点两边将各存储 9 个数字, 一共使用 9 个字节, 其中小数点前面的数字使用 DECIMAL 最多允许 65 个数字
浮点类型在存储同样范围的值时, 通常比 DECIMAL 占用更少的空间, 内部计算时采用 DOUBLE 作为计算类型
因为需要额外的空间和计算开销, 尽量只在对小鼠进行精确计算时才使用 DECIMAL, 在数据量比较大的时候, 可以考虑使用 BIGINT 代替 DECIMAL, 讲需要存储的货币单位根据小数的位数乘以相应的倍数即可
1.3 字符串类型
varchar
? varchar 类型用于存储可变长字符串, 比定长更节省空间, varchar 需要使用 1 个或 2 个额外字节记录字符串的长度, 如果列的最大长度小于或等于 255 个字节, 则只是用 1 个字节表示, 否则使用 2 个字节 varchar 节省了存储空间, 所以对性能也有帮助但是, 由于行是变长的, 如果在 UPDATE 时增加了该边长列的实际存储长度, 这就导致需要额外的工作, 如果一个行占用的空间增长, 并且在页内没有更多的存储空间可以存储, 在这种情况下, InnoDB 需要分裂页来使行可以放进页内
? varchar 使用场合: 1. 字符串列的最大长度比平均长度大很多, 列的更新很少
? 2. 使用了 UTF-8 这种复合的字符集(每个字符都使用不同的字节数存储)
? MySQL 在存储和检索时会保留 varchar 尾部的空格 InnoDB 可以把过长的 VARCHAR 存储为 BLOB
char
? 定长字符串, MySQL 在存储时会去除 char 尾部的空格会造成 A 与 A 产生唯一性冲突数据如何存储取决于存储引擎, 填充和截取空格的行为是在 MySQL 服务层进行的
? 更长的列会消耗更高的内存, MySQL 通常会分配固定大小的内存来保存内部值, 尤其是使用内存临时表进行排序或操作总是会特别糟糕
blob
? 采用二进制的方式存储, 没有排序规则和字符集包含 tinyblob,blob,mediumblob,longblob
text
? 采用字符串的方式存储, 有排序规则和字符集, 包含 tinytext,text,mediumtext,longtext
与其他类型不同, MySQL 把每个 BLOB 值和 TEXT 值当作一个独立的对象处理, 存储引擎在存储时通常会做特殊处理, 当 BLOB 和 TEXT 值太大时, InnoDB 会使用专门的外部存储区域来存储, 在原本的行中使用指针指向外部的存储区域同事这两种数据格式最多只能建立前缀索引
ENUM
? 枚举不推荐使用(想了解可以参考原书)
1.4 日期和时间类型
DATETIME 和 TIMESTAMP
? 现在推荐使用 DATETIME, 范围更大, 与时区无关, 占用 8 个字节
1.5 位数据类型
? InnoDB 为每个 BIT 列使用一个足够存储的最小整数类型来存放, 使用 BIT 类型并不能节省太多的存储空间, MySQL 把 BIT 当作字符串类型, 当检索 BIT(1)的值时, 结果是一个包含二进制 0 或者 1 的字符串
2.MySQL 模式设计的陷阱
2.1 太多的列
? MySQL 的存储引擎 API 在工作的时需要在服务器层和存储引擎层通过行缓冲格式拷贝数据, 然后在服务器层将行缓冲内容解码成各个列从行缓冲中将编码过的列转换成行结构的操作代价非常的高, 转换的代价依赖于列的数量
2.2 太多的关联
? 一个粗略的经验法则, 如果希望查询执行的快且并发性好, 单个查询最好在 12 个表内做关联
2.3NULL 值
? 需要存储一个事实上的空值到列表中时, 可以使用 0, 某个特殊值, 或者空字符串代替 MySQL 会在索引中存储 NULL 值, 而 Oracle 则不会
3. 范式和反范式
? 在范式化的数据库中, 每个事实数据只会出现一次,
? 反范式化的数据库中, 信息是冗余的, 可能会存储在多个地方
3.1 范式化的优点和缺点
优点:
? 范式化的更新操作更快, 只需要更改较少的数据
? 范式化的表更小, 可以更好的放在内存里, 执行操作会更快
? 没有多余的数据, 可以减少 distinct 或 GROUP BY 的操作
缺点:
? 通常需要关联, 关联代价昂贵, 也可能使一些索引策略无效
3.2 反范式的优点和缺点
优点:
? 所有的数据都在一张表中, 可以避免关联
? 不关联的时候即使全表扫描, 也是顺序 IO
缺点:
? 冗余的多余数据, 更新更慢
? 表大, 放到内存中, 占用大, 容易挤出热数据
4. 更快的读, 更慢的写
? 为了提升读查询的速度, 经常会建一些额外索引, 增加冗余列, 甚至是创建缓存表和汇总表, 这些方法会增加写查询的负担
? 写操作变慢并不是读操作变得更快所付出的唯一代价, 还可能同时增加了读操作和写操作的并发难度
5. 加快 ALTER TABLE 操作的速度
? ALTER TABLE 操作对特大表来说, 是个大问题
? MySQL 执行大部分修改表结构的步骤:
? 1. 用新结构创建一个空表
? 2. 从旧表中查出所有数据插入新表
? 3. 删除旧表
? 一般而言, 大部分 ALTER TABLE 操作将导致 MySQL 服务对该表的访问中断
? 对于常见的场景, 常见的技巧有两种:
? 1. 现在一台不提供服务的机器上执行 ALTER TABLE 操作, 然后切换
? 2. 影子拷贝, 即和原来的步骤一样, 但是通过触发器的方式更新新表旧表数据, 然后重命名
? 所有的 MODIFY COLUMN 操作, 都会导致表重建
5.1 只修改 frm(表结构)文件
? 下面这些操作是有可能不需要重建的:
? 移除一个列的 AUTO_INCREMENT 属性
? 增加, 移除, 或更改 ENUM 和 SET 常量
? 步骤(本操作是火中取栗):
? 1. 创建一张有相同结构的空表, 进行所需要的修改
? 2. 执行 FLUSH TABLES WITH READ LOCK 关闭所有正在使用的表, 并且禁止表被打开
? 3. 交换 frm 文件
? 4. 执行 UNLOCK TABLES 来释放第二步的读锁
6. 总结
? 1. 避免设计过度复杂的数据库模式
? 2. 使用小而简单的合适数据类型, 尽可能避免使用 NULL 值
? 3. 尽量使用相同的数据类型存储相似或者相关的值
? 4. 可变长字符串在临时表和排序时有可能悲观的按照最大长度分配内存
? 5. 尽量使用自增整数列定义主键
? 6. 避免使用 MySQL 不再推荐的特性
? 7. 谨慎对待 BIT,ENUM,SET
来源: http://www.bubuko.com/infodetail-2528194.html