Bitmap Index作为一种全新的索引类型,支持在基数较大(存在大量相同值)的列上建立索引,能够提供高效的点值查询和范围查询的能力。本文为您介绍在MaxCompute中如何使用Bitmap Index索引类型。
背景信息
MaxCompute当前共提供四种索引类型:Hash Clustering、Range Clustering、Bloomfilter Index和Bitmap Index。这四种索引都能够提供点查询过滤的能力。相比于Range Clustering在满足最左匹配原则的情况下才能够提供范围过滤的能力,Bloomfilter Index能很好地应对点查场景,而Bitmap Index更多面向于范围查询场景,在范围过滤场景下可过滤掉超过60%以上的数据,从而起到查询加速的效果。
Bitmap Index具有以下优势:
每个Bitmap Index的查询都是独立的,没有最左匹配原则限制。
支持多列。可以对表的一列或者多列建立Bitmap Index,并且能够与其他索引搭配共同加速查询。
过滤效果好。Bitmap Index通过对每一个值建立一个位图的方式,使得过滤结果能够精确到行,在基数较高且数据分布紧凑的场景下有很好的过滤效果。
适用于交并集运算(OR和AND运算),可以优化多维查询。
适用场景
适用于重复度高的列,且仅包含有限几种取值,比如性别、城市等。
适合执行逻辑运算,例如AND、OR等,能够高效地对Bitmap进行位运算。
在基数过大或查询过滤效果不佳的情况下,可能会出现占用较多的存储空间以及读取性能变差的情况,因此不建议对类似以下的列建立Bitmap Index索引:
值重复度低的列,如:身份证号、手机号码等。
经常需要更新修改的列。
计费说明
存储部分:索引构建后会占用额外存储空间,会根据索引实际存储大小统计,与项目数据的存储用量合并计费,定价与标准存储费用相同。
计算部分:索引的构建会触发额外的计算任务,增加计算资源消耗。预付费情况下会直接使用项目的预付费资源,后付费情况下,索引相关计算任务的费用=
SQL后付费单价*复杂度1*索引相关任务输入数据量
。
使用限制
当前仅支持在非嵌套的STRING、CHAR、VARCHAR、TINYINT、SMALLINT、INT和BIGINT类型上建立。
支持加速以下查询:
<=
、<
、=
、>
、>=
、IN
、BETWEEN
和IS NULL
。
注意事项
创建Bitmap Index前请执行
setproject odps.schema.evolution.enable=true;
命令,设置允许表结构变更(Schema Evolution)。目前仅支持1次对1列创建Bitmap Index,若您需要为表的多个列创建Bitmap Index,则需要多次执行创建Bitmap Index语句。
创建索引之后,插入数据时系统会自动对表生成Bitmap索引。
Bitmap Index若要对存量数据生效,需要重建Bitmap Index。
示例数据
Bitmap Index相关语法示例均基于emp
表和sale_detail
表中的数据。
非分区表
创建
emp
表。CREATE TABLE IF NOT EXISTS emp( empno BIGINT, ename STRING, job STRING, mgr BIGINT, sex STRING );
插入数据。
INSERT INTO emp(empno,ename,job,mgr,sex) VALUES (7369,'smith','clerk',7902,'Male'), (7499,'allen','salesman',7698,'Female'), (7521,'ward','salesman',7698,'Male'), (7654,'martin','salesman',7698,'Male'), (7698,'blake','manager',7839,'Male'), (7782,'clark','manager',7839,'Male'), (7788,'scott','analyst',7566,'Male'), (7839,'king','president',NULL,'Male'), (7844,'turner','salesman',7698,'Female'), (7876,'adams','clerk',7788,'Female'), (7900,'james','clerk',7698,'Male'), (7902,'ford','analyst',7566,'Male'), (7934,'miller','clerk',7782,'Female');
分区表
创建
sale_detail
分区表。CREATE TABLE IF NOT EXISTS sale_detail( shop_name STRING, customer_id STRING, total_price DOUBLE) PARTITIONED BY (sale_date STRING, region STRING);
建立分区。
ALTER TABLE sale_detail ADD PARTITION (sale_date='2023', region='china') PARTITION (sale_date='2024', region='shanghai');
创建Bitmap Index
语法
CREATE BITMAP INDEX <index_name> ON TABLE <table_name> FOR COLUMNS(<col_name>) [COMMENT 'indexcomment'];
参数说明
index_name:必填,索引名称。
table_name:必填,表名。
col_name:必填,列名。针对此列建立的索引。
示例
为
emp
表中的empno
列建立索引,名称为empno_index
。CREATE BITMAP INDEX empno_index ON TABLE emp FOR COLUMNS(empno) COMMENT 'idxcomment';
为
emp
表中的job
职位列建立索引,名称为job_index
。CREATE BITMAP INDEX job_index ON TABLE emp FOR COLUMNS(job) COMMENT 'idxcomment';
为
sale_detail
表的shop_name
列建立索引,名称为shop_name_index
。CREATE BITMAP INDEX shop_name_index ON TABLE sale_detail FOR COLUMNS(shop_name) COMMENT 'indexcomment';
重建Bitmap Index
语法
使用如下命令对存量数据重新生成Bitmap Index。
非分区表重建。
ALTER TABLE <table_name> REBUILD BITMAP INDEX;
分区表重建。
ALTER TABLE <table_name> PARTITION (<partition_name1=value1>[, partition_name2=value2, ...]) REBUILD BITMAP INDEX;
说明分区表一次只能对一个分区进行重建。
参数说明
非分区表
table_name:必填,表名。
分区表
table_name:必填,表名。
partition_name:必填,分区名称。
value:必填,分区值。
示例
示例1:非分区表。
ALTER TABLE emp REBUILD BITMAP INDEX;
示例2:分区表。
ALTER TABLE sale_detail PARTITION (sale_date='2023', region='china') REBUILD BITMAP INDEX;
查询Bitmap Index
语法
SHOW INDEXES ON <table_name>;
参数说明
table_name: 必填,表名。
示例
--查看emp表上的索引
SHOW INDEXES ON emp;
返回结果如下。
{"Indexes": [{
"id": "00c84b0e9e6e4097bdfe3a01b91848ac",
"indexColumns": [{"name": "job"}],
"name": "job_index",
"properties": {"comment": "jobidx"},
"type": "BITMAP"},
{
"id": "18a9755c7a8a4182a6b51165e786aa62",
"indexColumns": [{"name": "empno"}],
"name": "empno_index",
"properties": {"comment": "idxcomment"},
"type": "BITMAP"}]}
查询数据并查看Bitmap Index效果
示例
示例1:对
job
职位列进行点查。SELECT * FROM emp WHERE job = 'clerk';
返回结果如下。
+------------+-------+-----+------------+------------+-----+ | empno | ename | job | mgr | hiredate | sex | +------------+-------+-----+------------+------------+-----+ | 7369 | smith | clerk | 7902 | NULL | Male | | 7876 | adams | clerk | 7788 | NULL | Female | | 7900 | james | clerk | 7698 | NULL | Male | | 7934 | miller | clerk | 7782 | NULL | Female | +------------+-------+-----+------------+------------+-----+
示例2:对
empno
列进行范围查询。SELECT * FROM emp WHERE empno BETWEEN 7300 AND 7800;
返回结果如下。
+------------+------------+------------+------------+------------+ | empno | ename | job | mgr | sex | +------------+------------+------------+------------+------------+ | 7369 | smith | clerk | 7902 | Male | | 7499 | allen | salesman | 7698 | Female | | 7521 | ward | salesman | 7698 | Male | | 7654 | martin | salesman | 7698 | Male | | 7698 | blake | manager | 7839 | Male | | 7782 | clark | manager | 7839 | Male | | 7788 | scott | analyst | 7566 | Male | +------------+------------+------------+------------+------------+
查询索引效果。
在控制台运行日志中,单击Log view链接,进入LogView页面。
在LogView页面的Json Summary一栏中搜索Bitmap关键字,可以看到通过Bitmap Index带来的过滤效果以及构造Bitmap Index所带来的额外耗时(不包含IO的耗时)。
删除Bitmap Index
语法
DROP INDEX [IF EXISTS] <index_name> ON TABLE <table_name>;
参数说明
index_name:必填,索引名称。
table_name:必填,表名。
示例
DROP INDEX IF EXISTS job_index ON TABLE emp;