(GO) database Btree Index, hash index, bitmap bitmap index pros and cons

tested on: MySQL 5.5.25

The current version of the test is MySQL 5.5.25 There are only btree and hashes of two index types, default is Btree. There are bitmap indexes (bitmap indexes) in Oracle or other types of databases, which are also provided here as comparisons.

Btree Index

BTree (Multi-path search tree, not binary) is a common data structure. Using the BTREE structure can significantly reduce the intermediate process that is experienced when locating records, thus speeding up the access speed. According to translation, B is generally regarded as the abbreviation of balance. This data structure is generally used to index the database, the overall efficiency is high. --Baidu Encyclopedia

Not suitable for:

• Columns with single-column indexes cannot contain null records, and individual columns of a composite index cannot contain simultaneous null records, otherwise full table scans;
• Not suitable for columns with less key values (columns with more data);
• A leading fuzzy query cannot take advantage of an index (like '%XX ' or like '%xx% ')

Hash Hash Index

A hash hash index is an index built on a hash algorithm. Although the hash index efficiency is high, but the hash index itself because of its particularity also brought a lot of limitations and drawbacks, mainly have the following.

For:

• Exact lookup is very fast (including = <> and in), its retrieval efficiency is very high, index retrieval can be positioned once, unlike Btree index need from the root node to the side point, so the Hash index query efficiency is much higher than the B-tree index.

Not suitable for:

• Not suitable for fuzzy query and scope query (including Like,>,<,between......and, etc.), because the hash index comparison is the hash value after the hash operation, so it can only be used for the filtering of the equivalent, not for the range-based filtering, because the corresponding hash The size relationship of the hash value after the algorithm is not guaranteed to be exactly the same as before the hash operation;
• Not suitable for sorting, the database can not take advantage of indexed data to improve the sorting performance, also because the size of the hash value is uncertain;
• Composite index can not take advantage of partial index field query, hash index in the calculation of the hash value is the combination of index key merge and then calculate the hash value together, rather than calculate the hash value alone, so by combining the index of the previous or several index key query, the hash index can not be exploited.
• Also not suitable for columns with less key values (columns with more repeating values);

Bitmap Bitmap Index

is the index represented by the bitmap, which establishes a bitmap for each key value of the column. With respect to the Btree index, the footprint is very small, creating and using very quickly. Bitmap indexing takes up very little space because it stores only the starting and ending rowid and bitmaps of the key values. If there is a column like state in the test table, 10 rows of data are as follows:

10 20 30 20 10 30 10 30 20 30

Then three bitmaps will be created, as follows:

BLOCK1 key=10 1 0 0 0 1 0 1 0 0 0
BLOCK2 key=20 1 0 0 0 1 0 1 0 0 0
BLOCK3 key=30 1 0 0 0 1 0 1 0 0 0

For

• Suitable for decision support system;
• When select count (XX), it is possible to directly access a bitmap in the index to quickly obtain statistical data;
• When making And,or or in (x, y,..) based on key values When querying, directly with the bitmap of the index or operation to quickly obtain the result row data.

Not suitable for

• Not suitable for columns with a large number of key values (columns with fewer duplicates);
• Columns that are not suitable for update, insert, delete frequently, are expensive.

Original sticker: http://blog.sina.com.cn/s/blog_4b9eab320102w5vx.html

(GO) database Btree Index, hash index, bitmap bitmap index pros and cons