Hive之索引优化

概述

Hive支持索引，但是Hive的索引与关系型数据库中的索引并不相同，比如，Hive不支持主键或者外键。

Hive索引可以建立在表中的某些列上，以提升一些操作的效率，例如减少MapReduce任务中需要读取的数据块的数量。

在可以预见到分区数据非常庞大的情况下，分桶和索引常常是优于分区的。而分桶由于SMB Join对关联键要求严格，所以并不是总能生效。

三种索引

Hive原始索引（弃用）

Hive索引使用过程繁杂，而且性能一般，在Hive3.0中已被删除，在工作环境中不推荐优先使用，在分区数量过多或查询字段不是分区字段时，索引可以作为补充方案同时使用。推荐使用ORC文件格式的索引类型进行查询。

行组索引（Row Group Index）

一个ORC文件包含一个或多个stripes(groups of row data)，每个stripe中包含了每个column的min/max值的索引数据，当查询中有<,>,=的操作时，会根据min/max值，跳过扫描不包含的stripes。

而其中为每个stripe建立的包含min/max值的索引，就称为行组索引，也叫大小对比索引（min-max Index）。这种索引主要用于数值型字段的查询过滤优化上。

在建立ORC格式表时，指定表参数'orc.create.index'='true'之后，便会建立行组索引。

为了使Row Group Index有效利用，向表中加载数据时，必须对需要使用索引的字段进行排序，否则，min/max会失去意义。

条件:

要求表必须是ORC存储格式。
需要对那个字段进行索引，必须对这个字段进行排序，否则索引意义不大。
在创建表的时候，必须开启行组索引：'orc.create.index'='true'。
主要应用在< > =场景中，针对数值类型数据。

创建表/插入数据：

CREATE TABLE lxw1234_orc2 stored AS ORC
TBLPROPERTIES
(
    'orc.compress'='SNAPPY',
    -- 开启行组索引
    'orc.create.index'='true'
)
AS
    SELECT CAST(siteid AS INT) AS id,
    pcid
    FROM lxw1234_text
    -- 插入的数据保持排序
    DISTRIBUTE BY id sort BY id;

查询：

1 2	set hive.optimize.index.filter=true; SELECT COUNT(1) FROM lxw1234_orc1 WHERE id >= 1382 AND id <= 1399;

布隆过滤索引（Bloom Filter Index）

在建表时候，通过表参数'orc.bloom.filter.columns'='pcid'来指定为那些字段建立BloomFilter索引，这样，在生成数据的时候，会在每个stripe中，为该字段建立BloomFilter的数据结构，当查询条件中包含对该字段进行等值过滤的时候，先从BloomFilter中获取以下是否包含该值，如果不包含，则跳过该stripe。

条件:

必须是ORC类型的表。
需要按照哪一个字段进行等值连接，需要对这个字段构建布隆索引。
仅适合做等值连接，不局限于数据类型
在建表的时候，必须指定为那些字段开启：'orc.bloom.filter.columns'='字段1,字段2...'

建表：

CREATE TABLE lxw1234_orc2 stored AS ORC
TBLPROPERTIES
(
    'orc.compress'='SNAPPY',
    'orc.create.index'='true',
    -- pcid字段开启BloomFilter索引
    'orc.bloom.filter.columns'='pcid'
)
AS
SELECT CAST(siteid AS INT) AS id,
pcid
FROM lxw1234_text
DISTRIBUTE BY id sort BY id;

查询：

1
2
3

SET hive.optimize.index.filter=true;
SELECT COUNT(1) FROM lxw1234_orc1 WHERE id >= 0 AND id <= 1000  
AND pcid IN ('0005E26F0DCCDB56F9041C','A');

Tips:

行组索引：建议常开。在插入数据时，如果不明确对哪个字段使用行组索引，可以任意的插入；在条件合适情况下，也在使用行组索引。
布隆过滤索引：主要用于等值连接的字段，一般是join关联字段以及清洗字段。