mppdb查看建表语句_MPP架构数据库优化总结——华为LibrA
与GreenPlum
⽂章⽬录
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 (Massivel
y Parallel Processing)架构的数据库。华为LibrA与GreenPlum正是这样⼀款产品。通常实际⽣产环境中，每张表会存⼊海量的数据(例如我这⾥会有4TB、8TB、14TB等⼤⼩的表)，为了解决这些存有海量数据的表的性能问题，需要给出很多优化⽅案，在这⾥我总结出⼯作中常⽤的⼀些优化⼿段。
2. 优化点
2.1 建表时选择合适的数据类型，可以提⾼效率、减⼩空间占⽤
例如，⼈的年龄没必要使⽤int，可以采⽤TINYINT(占⽤1字节
，范围为0~255)
例如，优先使⽤TEXT和VARCHAR类型，尽量不要使⽤CHAR，以降低存储空间的使⽤
2.2 选择合理的存储模型(⾏存和列存)
⾏存表：适⽤于对数据需要经常更新的场景。
列存表： 适合数据批量插⼊、更新较少和以查询为主统计分析类的场景。列存表不适合点查询，插⼊单条记录性能差。
如何选择？
sql语句优化方式如果更新频繁，选择⾏存
如果经常点查询，选择⾏存
如果经常进⾏聚合查询，选择列存
经常⼀次插⼊⼤批量数据，选择列存
表字段较多，可以尝试列存
存储空间有限，希望更好的压缩数据，选择列存
2.3 选择表的分布⽅式
⼩表选择Replication⽅式(例如表⼤⼩为5MB)，会在每⼀个DataNode上存储⼀份全量表数据
⼤表选择Hash⽅式，会根据hash值把数据映射到对应的DataNode上
使⽤Hash分表策略时，需要选择合理的分布列(即字段)，选择的列要具有随机性，以保证数据均匀的
分布到各个DataNode上。检查数据是否分布均匀的SQL如下:-- 如果每个node_name对应的count相差不⼤，即代表分布基本均匀
de_name
FROM (SELECT COUNT(*) AS count,xc_node_id FROM tablename GROUP BY xc_node_id) a, pgxc_node b WHERE
<_node_id=
ORDER unt 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_n
ame';
杀掉对应的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)，以做到某些特殊查询需求

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。