Skip to content

26. 优化数据库对象

1. 优化表的数据类型⚓

不推荐让很多字段都留有大量的冗余，既浪费磁盘存储空间，又浪费内存。

MySQL有自带的分析函数给出优化建议：

SELECT * FROM tbl_name PROCEDURE ANALYSE();
SELECT * FROM tbl_name PROCEDURE ANALYSE(16,256);

第二个的意思是不要为那些包含的值多于 16 个或者 256 字节的 ENUM 类型提出建议。如果没有这样的限制，输出信息可能很长；ENUM 定义通常很难阅读。

如果是对一个大表进行分析，提出的建议会更准确。

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

指对数据表进行拆分。如果针对 MyISAM 类型的表进行，那么有两种拆分方法：

  • 第一种方法是垂直拆分， 即把主码和一些列放到一个表， 然后把主码和另外的列放到另一个表中。

    垂直拆分可以使得数据行变小，一个数据页就能存放更多的数据，在查询时就会减少 I/O 次数。其缺点是需要管理冗余列，查询所有数据需要联合（JOIN）操作。

  • 第二种方法是水平拆分， 即根据一列或多列数据的值把数据行放到两个独立的表中。
    水平拆分通常在以下几种情况下使用：

    • 表很大， 分割后可以降低在查询时需要读的数据和索引的页数， 同时也降低了索引的层数，提高查询速度。
    • 表中的数据本来就有独立性
    • 需要把数据存放到多个介质上。

    水平拆分会给应用增加复杂度， 它通常在查询时需要多个表名， 查询所有数据需 要 UNION 操作。在许多数据库应用中，这种复杂性会超过它带来的优点。水平拆分要考虑数据量的增长速度， 根据实际情况决定是否需要对表进行水平拆分。

3. 逆规范化⚓

因为规范化越高，那么产生的关系就越多，关系过多的直接结果就是导致表之间的连接操作越频繁， 而表之间的连接操作是性能较低的操作， 直接影响到查询的速度。

反规范的好处是降低连接操作的需求、降低外码和索引的数目，还可能减少表的数目， 相应带来的问题是可能出现数据的完整性问题。 加快查询速度， 但会降低修改速度。

好的索引和其他方法经常能够解决性能问题， 而不必采用反规范这种方法。

在进行反规范操作之前， 要充分考虑数据的存取需求、 常用表的大小、 一些特殊的计算（例如合计） 、数据的物理存储位置等。

技术手段：

  • 增加冗余列：指在多个表中具有相同的列，它常用来在查询时避免连接操作。
  • 增加派生列：指增加的列来自其他表中的数据，由其他表中的数据经过计算生成。
  • 重新组表：指如果许多用户需要查看两个表连接出来的结果数据，则把这两个表重新组成一个表来减少连接而提高性能。
  • 分割表：参考上一章节

无论使用何种反规范技术， 都需要一定的管理来维护数据的完整性：

  • 批处理维护是指对复制列或派生列的修改积累一定的时间后， 运行一批处理作业或存储过程对复制或派生列进行修改， 这只能在对实时性要求不高的情况下使用。
  • 数据的完整性也可由应用逻辑来实现，数据的完整性风险较大。
  • 使用触发器， 对数据的任何修改立即触发对复制列或派生列的相应修改。

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

对于数据量较大的表，在其上进行统计查询通常会效率很低，使用中间表可以改善这种情况。

转移要统计的数据到中间表,然后在中间表上进行统计，但是复制数据很耗时。例：

-- 创建临时表，和原表结构一致
CREATE TABLE  tmp_session ...
-- 复制数据
insert into tmp_session select * from session where cust_date>adddate(now(),-7);
-- 统计数据
select sum(cust_amount) from tmp_session;

中间表在统计查询中经常会用到，其优点如下:

  • 中间表复制源表部分数据，并且与源表相“隔离” ，在中间表上做统计查询不会对在线应用产生负面影响。
  • 中间表上可以灵活的添加索引或增加临时用的新字段,从而达到提高统计查询效率和辅助统计查询作用。