MySQL：数据库对象的优化

目录

1、优化表的数据类型

2、表的拆分

2.1 垂直拆分

2.2 水平拆分

3、使用中间表

4、逆规范化

---

1、优化表的数据类型

    MySQL中可以使用procedure analyse()函数对当前应用的表进行分析：

select * from table_name procedure analyse();
select * from table_name procedure analyse(16, 256);

    输出的每一行表示一个字段的分析信息，每一列信息都会对列的数据类型提出优化建议。示例：

可用的优化建议包括：

• 字段长度缩减；
• varchar如果长度不变，可用修改成char；
• 字段的值只有少量确定结果的情况下，可用使用枚举enum；

2、表的拆分

2.1 垂直拆分

    把主码和一些列放入一个表，把主码和另一些列放入另一个表。如果表中有一些不常用的列，就可以使用垂直拆分。

    优点：垂直拆分可以使数据行变小，一个数据页就能存放更多的数据，这样在查询时就能减少IO次数。

    缺点：需要管理冗余列，查询所有数据需要联合join操作。

2.2 水平拆分

    根据一列或者多列数据的值，把数据行放到两个独立的表中。

    水平拆分的使用场景：

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

    缺点：水平拆分会给应用增加复杂度，通常在查询时需要多个表名，查询所有数据需要UNION操作。很多时候这种复杂性会超过其优点，因为只要索引关键字不大，则在所以用于查询时，表中增加2~3倍的数据量，在查询时也就增加读一个索引层的磁盘此时。所以水平拆分要考虑数据量的增长速度，根据实际情况慎重考虑。

3、使用中间表

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

    使用中间表的流程：

1. 创建中间表，使中间表的表结构和源表完全相同；
2. 将需要统计的数据放入到中间表；

    中间表的优点：

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

4、逆规范化

    规范化越高，产生的关系就越多，过多的关系会导致表之间的连接操作越来越频繁，导致查询速度降低。所以，对于查询较多的应用，可以根据需要逆规范化对数据进行设计，通过逆规范化提高查询的性能。

    常用的逆规范化技术：

• 增加冗余列：
• 增加派生列：
• 重新组表：
• 分割表：

    逆规范化的优点是降低连接操作的需求，降低外键和索引的数量，甚至降低表的数量，可以提高查询效率；

    缺点是可能会出现数据完整性方面的问题；加快查询，但是会降低修改速度。

    逆规范化也需要维护数据的完整性，常用的方法是批处理维护、应用逻辑和触发器：

• 批处理维护：对复制列或者派生列的修改积累一定时间后，运行一批批处理作业或者存储过程，来对复制列或者派生列进行修改；
• 应用逻辑：这要求必须在同一事务中对所有涉及到的表进行增删改操作。由应用逻辑来实现数据的完整性风险较大，因为同一逻辑必须在所有的应用中使用和维护，容易遗漏，尤其在需求变化的时候，不易于维护。
• 触发器：对数据的任何修改，立即触发对复制列或派生列的相应修改。触发器是实时的，而且相应的处理逻辑只在一个地方出现，易于维护。一般来说，这是最好的方法。