Currently, one of our table size is 500 Million rows (with 35 columns), and we are trying to determine, how big can our table be before it impacts our performance on running query on that table?
目前，我们的表大小之一是5亿行(包含35列)，我们正在尝试确定，在表影响我们在该表上运行查询的性能之前，它能有多大?
2 个解决方案
#1
1
Performance cannot be measured like rows*columns.
性能不能像行*列那样进行度量。
It depends on the data types, joins, aggregations, etc. Your query performance can be drastically improved, for example, by creating int keys (adding columns) instead of char/varchar keys if used in joins.
它依赖于数据类型、连接、聚合等。例如，如果在连接中使用，可以创建int键(添加列)而不是char/varchar键，从而大大提高查询性能。
#2
1
An important addition to @vtuhtan 's answer : enable compression. Create tables with compression enabled for various data types - lzo, runlength etc. Proper compression type is also suggested by Redshif on tables with ANALYZE COMPRESSION SQL command. This reduces the read throughput and drastically increases your query performance. This will also make the table consume less storage space.
@vtuhtan回答的一个重要补充是:启用压缩。创建可以用于各种数据类型(lzo、runlength等)的压缩表。Redshif还建议使用ANALYZE compressql命令在表上使用适当的压缩类型。这降低了读取吞吐量，并显著提高了查询性能。这也将使表消耗更少的存储空间。
Doc on analyzing compression enabled tables
文档分析支持压缩的表
Loading tables with compression.
加载表与压缩。
#1
1
Performance cannot be measured like rows*columns.
性能不能像行*列那样进行度量。
It depends on the data types, joins, aggregations, etc. Your query performance can be drastically improved, for example, by creating int keys (adding columns) instead of char/varchar keys if used in joins.
它依赖于数据类型、连接、聚合等。例如，如果在连接中使用，可以创建int键(添加列)而不是char/varchar键，从而大大提高查询性能。
#2
1
An important addition to @vtuhtan 's answer : enable compression. Create tables with compression enabled for various data types - lzo, runlength etc. Proper compression type is also suggested by Redshif on tables with ANALYZE COMPRESSION SQL command. This reduces the read throughput and drastically increases your query performance. This will also make the table consume less storage space.
@vtuhtan回答的一个重要补充是:启用压缩。创建可以用于各种数据类型(lzo、runlength等)的压缩表。Redshif还建议使用ANALYZE compressql命令在表上使用适当的压缩类型。这降低了读取吞吐量，并显著提高了查询性能。这也将使表消耗更少的存储空间。
Doc on analyzing compression enabled tables
文档分析支持压缩的表
Loading tables with compression.
加载表与压缩。