Bitmap Index is an index represented by bitmaps, where a bitmap is created for each key value in a column. Compared to other indexes, it occupies very little storage space and is very fast to create and use. However, it has a disadvantage of having a large lock granularity for modification operations, making it unsuitable for frequent updates.
count
, or
, and
logical operations that only require bitwise operations. For example: querying with multiple conditions combined, select count(*) from table where city = 'Nanjing' and job = 'Doctor' and phonetype = 'iphone' and gender = 'Male'.
If bitmap indexes are established on each query condition column, the database can perform efficient bit operations, accurately locate the required data, reduce disk IO, and the smaller the filtered result set, the more obvious the advantage of bitmap indexes.Creating a bitmap index named index_name on the column siteid in the table named table_name:
CREATE INDEX [IF NOT EXISTS] index_name ON table1 (siteid) USING BITMAP;
Displaying indexes under the specified table_name:
SHOW INDEX FROM table_name;
Deleting the index named index_name under the specified table_name:
DROP INDEX [IF EXISTS] index_name ON table_name;
TINYINT
SMALLINT
INT
BIGINT
CHAR
VARCHAR
DATE
DATETIME
LARGEINT
DECIMAL
BOOL