文章目录
MPP架构数据库优化总结——华为LibrA与GreenPlum
1. 简介
2. 优化点
2.1 建表时选择合适的数据类型，可以提高效率、减小空间占用
2.2 选择合理的存储模型(行存和列存)
2.3 选择表的分布方式
2.4 选择合适的分区键可以有效改善数据库的查询性能，增强可用性，方便维护，以及均衡I/O等
2.5 创建索引，提高数据的访问速度
2.6 大批量的数据导入、导出
2.7 压缩，减少空间占用
2.8 使用VACUUM和ANALYZE命令定期对每个表进行维护
2.9 减少数据库存储过程的使用
2.10 结束长时间运行的SQL
2.10 分析SQL执行计划
2.11 SQL编写优化
2.12 根据业务优化表设计
MPP架构数据库优化总结——华为LibrA与GreenPlum
1. 简介
大数据在关系型数据处理这块，为了能够快速的查询、写入海量数据数据，通常会采用MPP (Massively Parallel Processing)架构的数据库。华为LibrA与GreenPlum正是这样一款产品。通常实际生产环境中，每张表会存入海量的数据(例如我这里会有4TB、8TB、14TB等大小的表)，为了解决这些存有海量数据的表的性能问题，需要给出很多优化方案，在这里我总结出工作中常用的一些优化手段。
2. 优化点
2.1 建表时选择合适的数据类型，可以提高效率、减小空间占用
例如，人的年龄没必要使用int，可以采用TINYINT(占用1字节
，范围为0~255)
例如，优先使用TEXT和VARCHAR类型，尽量不要使用CHAR，以降低存储空间的使用
2.2 选择合理的存储模型(行存和列存)
行存表：适用于对数据需要经常更新的场景。
列存表： 适合数据批量插入、更新较少和以查询为主统计分析类的场景。列存表不适合点查询，插入单条记录性能差。
如何选择？
如果更新频繁，选择行存
如果经常点查询，选择行存
如果经常进行聚合查询，选择列存
经常一次插入大批量数据，选择列存
表字段较多，可以尝试列存
存储空间有限，希望更好的压缩数据，选择列存
2.3 选择表的分布方式
小表选择Replication方式(例如表大小为5MB)，会在每一个DataNode上存储一份全量表数据
大表选择Hash方式，会根据hash值把数据映射到对应的DataNode上
使用Hash分表策略时，需要选择合理的分布列(即字段)，选择的列要具有随机性，以保证数据均匀的分布到各个DataNode上。检查数据是否分布均匀的SQL如下:-- 如果每个node_name对应的count相差不大，即代表分布基本均匀
SELECT a.count,b.node_name
FROM (SELECT COUNT(*) AS count,xc_node_id FROM tablename GROUP BY xc_node_id) a, pgxc_node b WHERE a.xc_node_id=b.node_id
ORDER BY a.count DESC;
2.4 选择合适的分区键可以有效改善数据库的查询性能，增强可用性，方便维护，以及均衡I/O等
通常根据业务，我们可以按照日期对表进行分区(例如天、月)
查询时，选择对应的分区查询即可，可以提高效率
2.5 创建索引，提高数据的访问速度
根据业务需求选择合理的索引字段，例如经常被用作查询条件的字段、被要求排序的字段
如何选择索引字段？
经常使用WHERE子句的字段
经常出现在ORDER BY、GROUP BY、DISTINCT后的字段
经常进行多表连接的字段
如果需要创建联合索引，应注意后续SQL中的where条件的字段
2.6 大批量的数据导入、导出
当业务中需要大批量的数据导入时，请不要再使用JDBC/ODBC等方式插入数据，可以使用数据库自带的批量导入工具。华为LibrA可以参考LibrA批量数据导入。
如果要快速插入大量数据，尽量不要使用约束
2.7 压缩，减少空间占用
如果系统空间不足，又无法添加新的集器，可以考虑对表数据进行压缩(会导致性能降低)。
示例，定义一个带压缩的列存表CREATE TABLE tb_name(
code char(5),
title varchar(40),
did integer,
) WITH (ORIENTATION = COLUMN, COMPRESSION=HIGH);
列存表的有效值为YES/NO/LOW/MIDDLE/HIGH，默认值为LOW
行存表的有效值为YES/NO，默认值为NO
2.8 使用VACUUM和ANALYZE命令定期对每个表进行维护
VACUUM可以回收表或B-Tree索引中已经删除的行所占据的存储空间(DELETE实际不会真正删除数据)
ANALYZE会收集与数据库相关的统计信息，以便最有效的查询执行计划
可以尝试每日自动对表进行维护，SQL示例如下：VACUUM ANALYZE tb_name;
另外可以尝试VACUUM FULL，可以恢复更多的空间(耗时更长)
2.9 减少数据库存储过程的使用
该类型数据库，使用存储过程的性能并不好
2.10 结束长时间运行的SQL
有的长时间运行的SQL，很可能是数据库BUG、表数据存在问题、SQL自身问题导致的，应该定期进行分析，结束掉这部分SQL
查询长时间运行的SQL:SELECT current_timestamp - query_start AS runtime, datname, usename, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY 1 DESC;
查看语句执行的线程状态:SELECT * FROM PG_THREAD_WAIT_STATUS WHERE db_name='db_name';
杀掉对应的tid的SQL语句:SELECT pg_terminate_backend(140532470773504);
2.10 分析SQL执行计划
查看执行计划的逻辑，检查是否存在不合理的执行，再进行SQL优化
执行计划分析内容较多，请自行百度其他数据库的执行计划分析，都是类似的
2.11 SQL编写优化
执行较复杂的SQL，建议分多步执行，创建unlogged table或temp table缓存中间临时数据(非日志表的性能比普通表有大幅度提升)
在实际业务中，如果2个表做union，能够提前确定2个表没有交集，那么建议使用union all替代union
2个表做left Join时，小表在前、大表在后(小表驱动大表)
2个表Join时，尽量使用inner join，少使用left join
做聚合分析时，可以提前做好where过滤，以减少聚合的数据量
查询时不要使用SELECT * …，请直接指明所有字段名
2个表做Join时，如果不需要Null，请尽量加上is not null条件，对Join之前的数据进行过滤
针对同一个字段的多个or等于条件(name=‘xm’ or name=‘ls’ or name=‘xh’ …)，请修改为in或者exist
针对连续的数值条件查询，不要使用in，尽量使用between(例如 WHERE id BETWEEN 2 AND 3)
对经常要查询的SQL，创建视图View，以方便下次直接查询
where中，能明确条件的，一定不要使用like模糊查询(必须使用like时，尽量不要使用’%content%’，应尽量使用’content%’)。如果like的是分区字段，则可以不用在意。
2.12 根据业务优化表设计
没有必要为了节省空间去设计多个关联表(效率不高，大数据应该提倡以空间换时间)
针对经常要做统计的表，可以提前另作一个统计结果表，直接查询该结果表既可
一个大表中，某个字段需要经常单独用来去重或者判断exist，而又不要求实时性，同时又只是一个单一的业务需要，没有必要为其创建索引，可以每天做一次去重，单独存一个表
根据实际业务需求，可以对日期进行分区。如果前台每次默认查询需要做一个聚合请求，在能满足业务需求下，不要直接查全表日期的聚合，可以尝试查近期的聚合(例如近1~2月)。因为业务方面通常也是想看近期的数据。
如果业务中要使用分页类似的查询方式，表中需要设计id。如果只使用offset，随着表数据量的增大，会越来越慢。添加id后，可以用该语句代替：-- SELECT id,name FROM product LIMIT 20 OFFSET 100000;
SELECT id,name FROM product WHERE id> 100000 LIMIT 20
多数业务情况下，表中应设计update_time字段，以表示该条数据的插入、更新时间，方便后续操作
如果一个表的业务通常是进行聚合操作，应该尝试将该表设计为列存模式
利用业务需求，可以为表的字段设计二维索引(例如geohash)，以做到某些特殊查询需求