从设计上来考虑HQL的优化，会涉及到表设计、数据格式以及Job任务优化等。
- 分区表设计
Hive 分区是最有效的优化查询性能的方法之一。使用分区过滤的查询只加载特定分区（子目录）的数据，因此查询性能肯定比不适用分组的查询更快。分区键值选择的优劣通常是影响性能的重要因素。分区键值的选择应当是非重复数据相对较少，以避免使用过多的子目录。以下为分区键值选择的通常做法：
- 按照日期和时间分区： 当数据和日期/时间列比较相关，使用日期和时间，如年、月、日（甚至小时）等作为分区键列，这些相关分区键值列为日期或者时间数据类型，如load_date, business_date, run_date等。
- 按照地域分区： 当数据和地理位置等信息相关，使用国家、区域、州/省和城市作为分区键值。
- 按照业务逻辑分区： 当数据可以按照业务逻辑来进行区分，可以使用部门、销售区域、应用、客户等业务逻辑相关的数据列做为分区键列。
- Bucket表设计
与分区表类似，Bucket表把数据放在HDFS中不同的单独的文件中。Bucket表可以加速数据取样。同时当关联列同时是Bucket列时，Bucket表可以改善关联性能。由于Bucket列可以确保关联列只出现在某个特定的Bucket里，所以Bucket列同时是关联列时，关联性能会大大改善。较好选择的Bucket列意味着关联查询的关联列更大可能和Bucket列一致，因此通常把最有可能用于关联或者过滤的列作为Bucket列。 - Index设计
使用Index用于改善性能是关系型数据库最常用的调优手段。Hive自0.7.0之后，也支持在表或者分区上创建索引。Hive中的索引提供一个基于Key的数据视图，以及支持WHERE、GROUP BY 和JOIN等操作更好地进行数据访问。创建Index的示例为，
> CREATE INDEX idx_id_employee_id
> ON TABLE employee_id (employee_id)
> AS 'COMPACT'
> WITH DEFERRED REBUILD;
No rows affected (1.149 seconds)
COMPACT索引 保存索引列的值和它的Block ID。
BITMAP索引（自0.8.0后支持）适用于列值为较少变量值的情况。以下为BITMAP索引的示例，
> CREATE INDEX idx_gender_employee_id
> ON TABLE employee_id (gender_age)
> AS 'BITMAP'
> WITH DEFERRED REBUILD;
No rows affected (0.251 seconds)
WITH DEFERRED REBUILD选项，指定索引不会立即重建。为了重建索引，我们可以执行 ALTER INDEX ...REBUILD命令。
ALTER INDEX idx_id_employee_id ON employee_id REBUILD;
No rows affected (111.413 seconds)
ALTER INDEX idx_gender_employee_id ON employee_id REBUILD;
No rows affected (82.23 seconds)
一旦索引创建成功，每个索引会对应创建一个索引表，该索引表的命名格式为 <database_name><table_name><index_name>_:
> SHOW TABLES '*idx*';
+-----------+---------------------------------------------+-----------+
|TABLE_SCHEM| TABLE_NAME | TABLE_TYPE|
+-----------+---------------------------------------------+-----------+
|default |default__employee_id_idx_id_employee_id__ |INDEX_TABLE|
|default |default__employee_id_idx_gender_employee_id__|INDEX_TABLE|
+-----------+---------------------------------------------+-----------+
索引表包含索引列、_bucketname和_off_sets，共三列。如下例所示，
> DESC default__employee_id_idx_id_employee_id__;
+--------------+----------------+----------+
| col_name | data_type | comment |
+--------------+----------------+----------+
| employee_id | int | |
| _bucketname | string | |
| _offsets | array<bigint> | |
+--------------+----------------+----------+
3 rows selected (0.135 seconds)
> SELECT * FROM default__employee_id_idx_id_employee_id__;
+--------------+------------------------------------------------------+
| employee_id | _bucketname | _offsets |
+--------------+------------------------------------------------------+
| 100 | .../warehouse/employee_id/employee_id.txt | [0] |
| 101 | .../warehouse/employee_id/employee_id.txt | [66] |
| 102 | .../warehouse/employee_id/employee_id.txt | [123] |
| ... | ... ... | ... |
+--------------+-------------------------------------------+----------+
25 rows selected (0.219 seconds)
删除索引的命令如下所示，删表不会删相关索引。
DROP INDEX index_name ON table_name
- skewed/temporary 表的使用
自Hive 0.10.0之后，HQL开始支持创建skewed表。通过把skewed数据区分开来自动放入专门的文件和文件夹内，查询就只访问或者不访问这些数据，这样在查询中用到的文件和分区文件夹数量变小，从而改善性能。以下为创建skewed表示例，
> CREATE TABLE sample_skewed_table (
> dept_no int,
> dept_name string
> )
> SKEWED BY (dept_no) ON (1000, 2000); -- Specify value skewed
No rows affected (3.122 seconds)
> DESC FORMATTED sample_skewed_table;
+-----------------+------------------+---------+
| col_name | data_type | comment |
+-----------------+------------------+---------+
| ... | ... | |
| Skewed Columns: | [dept_no] | NULL |
| Skewed Values: | [[1000], [2000]] | NULL |
| ... | ... | |
+-----------------+------------------+---------+
33 rows selected (0.247 seconds)
临时表（temporary table）可以把多次使用的中间结果单独存放，以节省重建这些共享共通数据集所消耗的资源。另外也可以把它存放在SSD或者内存中以改善性能。
网友评论