优化数据库对象

1. 优化表的数据类型

数据类型的选择，重点在于合适二字，如何确定选择的数据类型是否合适可参考如下：

• 使用可以存下最小数据的数据类型；

• 使用简单的数据类型，INT 要比 VARCHAR 类型在 MySQL 处理简单；

• 尽可能的使用 NOT NULL 定义字段，特别是使用了索引的字段；

• 给 WHERE 条件的字段设置索引；

• 允许部分数据冗余，这样可以避免多表的连接（JOIN）操作耗时；

• 选取合适的数据库引擎；

• 尽量少用 TEXT 类型，非用不可时最好考虑分表。

选取正确的数据类型，对于提高性能至关重要，下面给出几种原则，有利于帮助你选择何种类型。

• 更小通常更好。

  使用更小的数据类型（更少的磁盘空间占用、内存和 CPU 缓存，而且需要的 CPU 的周期也更少）。

• 简单就好

  整数代价小于字符（因为字符集和排序规则使字符更加复杂）。

  • MySQL 内建类型（如 timestamp，date）优于使用字符串保存。

  • 使用整数保存 IP 地址。

• 尽量避免 NULL

  尽可能把字段定义为 NOT NULL 。可以放置一个默认值，如 ''，0，特殊字符串等。

  原因：

  • MySQL 难以优化 NULL 列。NULL 列会使索引统计和值更加复杂；

  • NULL 列需要更多的存储空间，还需要在 MySQL 内部进行特殊处理；

  • NULL 列加索引，每条记录都需要一个额外的字节，还导致 MyISAM 中固定大小的索引变成可变大小的索引。

  决定列的数据类型，我们应该遵循下面两步。

  第一步：大致决定数据类型（判断是数字、字符串还是时间等）；

  第二步：确定特定的类型。

  很多数据类型能够保存同类型的数据，但是我们要发现，它们在存储的范围，精度和物理空间之间的差别（磁盘或内存空间）。如：DATETIME 和 TIMESTAMP 能保存同样类型的数据：日期和时间，但是二者的区别也是不同的，系统会自动给 TIMESTAMP 类型创建默认值 CURRENT_TIMESTAMP（系统日期），并且MySQL 规定 TIMESTAMP 类型字段只能有一列的默认值为 CURRENT_TIMESTAMP，如果强制修改，系统会报错提示，TIMESTAMP 还有一个重要的点就是和时区相关，插入日期时会先转换为本地时区后存放，取出时，也同样将日期转换为本地时区后显示。TIMESTAMP 的取值范围是 19700101080001到 2038 年的某一天，因此它不适合存放比较久远的日期。

2. 通过拆分提高表的访问效率

这里所说的“拆分”，是指对数据表进行拆分。如果针对 MyISAM 类型的表进行拆分，那么有两种拆分方法。

• 垂直拆分

  所谓的垂直拆分，就是把原来一个有很多列的表拆分成多个表，这解决了表的宽度问题。通常垂直拆分可以按以下原则进行：

  • 把不常用的字段单独存放到一个表中；

  • 把大字段独立存放到一个表中；

  • 把经常一起使用的字段放在一起。

• 水平拆分

  表的水平拆分是为了解决单表的数据量过大的问题，水平拆分的表每一个表的结构都是一致的。

  方法：对 id 进行 hash 运算，如果要拆分成4个表，则使用 mod(id, 4) 取模，把不同的数据存到4个表中。

3. 反规范化

反范式化是指为了查询效率的考虑把原本符合第三范式的表适当的增加冗余，已达到优化查询效率的目的，反范式化是一种以空间来换取时间的操作。

反规范化的好处是降低连接操作的需求、降低外码和索引的数目，还可能减少表的数目，相应带来的问题是出现数据的完整性问题。加快查询速度，但会降低修改速度。因此决定做反规范化时，一定要权衡利弊，仔细分析应用的数据存取需求和实际的性能特点，好的索引和其它方法能够解决性能问题，而不必采用反规范化这种方法。

在进行反规范化操作之前，要充分考虑数的存取需求、常用表的大小、一些特殊的计算（例如合计）、数据的屋里存储位置等。常用的反规范化技术有增加冗余列、增加派生列、重新组表和分割表。

• 增加冗余列：指在多个表中具有相同的列，它常用来在查询时避免连接操作。

• 增加派生列：指增加的列来自其他表中的数据，由其它表中的数据经过计算生成。增加的派生列其作用是在查询时减少连接操作，避免使用集函数。

• 重新组表：指如果许多用户需要查看两个表连接出俩的结果数据，则把这两个表重新组成一个表来减少连接而提供性能。

• 分割表：-

另外，反规范化技术需要维护数的完整性。不论使用何种反规范化技术，都需要一定的管理来维护数据的完整性，常用的方法是批处理维护、应用逻辑和触发器。

• 批处理维护是指对复制列或派生列的修改积累一定的时候后，运行一批处理作业或存储过程对复制或派生列进行修改，这只能在对实时性要求不高的情况下使用。

• 数据的完整性也可由应用逻辑来实现，这就要求必须在同一事务中对所有涉及的表进行增、删、改操作。用应用逻辑来实现数据的完整性风险较大，因为同一逻辑必须在所有的应用中使用和维护，容易遗漏，特别是在需求变化时，不易于维护。

• 另一种方式就是使用触发器，对数据的任何修改立即触发对复制列或派生列的相应修改。触发器是实时的，而且响应的处理逻辑只在一个地方出现，易于维护。一般来说，是解决这类问题比较好的办法。

4. 使用中间表提高统计查询速度

对于数据量较大的表，在其上进行统计查询通常会效率很低，并且还要考虑统计查询是 否会对在线的应用产生负面影响。通常在这种情况下，使用中间表可以提高统计查询的效率。

中间表优点如下：

• 中间表复制源表部分数据，并且与源表相“隔离”，在中间表上做统计查询不会对在线应用产生负面影响。

• 中间表上可以灵活的添加索引或增加临时用的新字段，从而达到提高统计查询效率和辅助统计查询作用。

章节内容来自《深入浅出MySQL》，本内容作为个人笔记记录，同时因为PROCEDURE ANALYZE()已经废弃移除了，所以没有收录进来，同时还参考了博客：

Mysql表结构优化

MySQL表结构的优化和设计