Bitmap Index(Beta)

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类型上建立。

  • 支持加速以下查询:<=<=>>=INBETWEENIS 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表中的数据。

非分区表

  1. 创建emp表。

    CREATE TABLE IF NOT EXISTS emp( 
      empno BIGINT, 
      ename STRING, 
      job STRING, 
      mgr BIGINT, 
      sex STRING 
    );
  2. 插入数据。

    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');

分区表

  1. 创建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);
  2. 建立分区。

    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. 示例

    • 示例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       | 
      +------------+------------+------------+------------+------------+
  2. 查询索引效果。

    1. 在控制台运行日志中,单击Log view链接,进入LogView页面。

      image

    2. LogView页面的Json Summary一栏中搜索Bitmap关键字,可以看到通过Bitmap Index带来的过滤效果以及构造Bitmap Index所带来的额外耗时(不包含IO的耗时)。

      image

删除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;