Zonemap Index is automatically maintained by the system. Users do not need to create it and cannot proactively create or delete it via SQL.Zonemap Index is a built-in system index that requires no configuration from users. The system automatically records statistical information, such as the maximum value, minimum value, and whether NULL values are included, for each column and each data block.=), range queries (>, <, >=, <=, BETWEEN), and IS NULL, the optimizer can use the maximum value, minimum value, and NULL flag to determine whether a data file or data block contains data that meets the conditions. If it does not, the corresponding file or data block is skipped. This approach reduces unnecessary I/O overhead and effectively accelerates the query process.Bloom Filter Index is a skip index based on a Bloom filter. It uses the Bloom filter to quickly determine whether a specified data block might contain data that matches an equality query condition. Data blocks that cannot possibly contain matches are skipped directly, thereby reducing I/O and accelerating queries.Bloom Filter Index is suitable for accelerating equality queries (= and IN), and it performs even better on high-cardinality fields (fields with a large number of distinct values).Bloom Filter Index accelerates queries only for predicates such as = and IN. It is ineffective for other predicates, including !=, NOT IN, >, <, and LIKE.SMALLINT, MEDIUMINT, INT, BIGINT (does not support TINYINT).DECIMALCHAR, VARCHAR, TEXT, and so onDATE, TIME, DATETIMEBloom Filter Index has been created on a field involved in an equality predicate or an IN predicate within the WHERE clause, the optimizer automatically applies that index to accelerate the query. No explicit user specification is required.CREATE INDEX [IF NOT EXISTS] index_name USING BLOOM_FILTER ON table_name (column_name);
DROP INDEX [IF EXISTS] index_name ON table_name;
-- Method 1: View the table creation statement.SHOW CREATE TABLE table_name;-- Method 2: View all indexes on a table.SHOW INDEX FROM table_name;-- Method 3: Query metadata.SELECT * FROM information_schema.STATISTICSWHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME = 'table_name';
Bitmap Index is an index represented by bitmaps, where a bitmap is created for each key value in a column. Compared to other indexes, Bitmap Index occupies less storage space and offers higher efficiency for both construction and queries. However, its modification operations involve a larger lock granularity, making it unsuitable for scenarios with frequent updates.SELECT COUNT(*) FROM usersWHERE city = 'Nanjing' AND job = 'doctor' AND device_type = 'iphone' AND gender = 'male';
Bitmap Index is established on each query condition column, the database can precisely locate target data through efficient bitwise operations (AND / OR), reducing disk I/O. The smaller the filtered result set, the more pronounced the advantage of the Bitmap Index becomes.Bitmap Index on each of those 20 columns, enabling all related queries to benefit from index acceleration.Bitmap Index supports the following predicates: =, !=, >, <, >=, <=, IN, IS NULL, IS NOT NULL. When multiple predicates are combined, using AND connections yields the best pruning effect.TINYINT, SMALLINT, MEDIUMINT, INT, BIGINTDECIMALCHAR, VARCHAR, TEXT, and so on.DATE, TIME, DATETIMECREATE INDEX [IF NOT EXISTS] index_name USING BITMAP ON table_name (column_name);
DROP INDEX [IF EXISTS] index_name ON table_name;
-- Method 1: View the table creation statement.SHOW CREATE TABLE table_name;-- Method 2: View all indexes on a table.SHOW INDEX FROM table_name;-- Method 3: Query metadata.SELECT * FROM information_schema.STATISTICSWHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME = 'table_name';
Esta página foi útil?
Você também pode entrar em contato com a Equipe de vendas ou Enviar um tíquete em caso de ajuda.
comentários