Index: Bitmap Index understanding

The article is reproduced from HTTP://WWW.360DOC.COM/CONTENT/14/0508/15/11965070_375805586.SHTML1. Case

There is a table named tables, composed of three columns, namely, the name, gender and marital status, where the gender only male and female, marital status from married, unmarried, divorced three items, the table has a total of 100w records. Now there is a query: SELECT * FROM table where gender= ' man ' and marital= "unmarried";

name (name)	Gender (Gender)	Marital status (marital)
Zhang San	male	already Married
John Doe	female	married
Harry	male	unmarried
Zhao Liu	female	Divorce
Sun Seven	female	unmarried
...	...	...

1) Do not use the index

When you do not use an index, the database can scan all records only one row at a time, and then determine whether the record satisfies the query criteria.

2) B-Tree Index

For sex, the range of desirable values is only ' male ', ' female ', and both men and women may stand at 50% of the table's data, when adding a B-tree index or need to take out half of the data, it is completely unnecessary. Conversely, if a field has a wide range of values, with little repetition, such as a social security number, it is more appropriate to use a B-tree index at this time. In fact, when the fetched row data occupies most of the data in the table, even if the B-tree index is added, the database, such as Oracle, MySQL, will not use the B-tree index, and most likely, a row of full scans.

2. Bitmap indexing

After the bitmap index has been created, the resulting bitmap data can be understood so that, for example, two kinds of men and women, and then a total of eight data, then the production of two strings, one for the male, one for the female, the length of the string is the sum of the data, the value of the string: first (if the first data is a male Second, third, this is the future. The resulting length is the total number, containing only 01 of the string, through this string can know the first few data is male, the first few are not male, the same, the other one for the female string is the same, is the female 1, not the female 0.

If a user queries a column that has a very small cardinality, that is, only a few fixed values, such as gender, marital status, administrative districts, and so on. To have a smaller Lie Jian index for these cardinality values, you need to create a bitmap index.

For the gender column, the bitmap index forms two vectors, the male vector is 10100 ..., each of the vectors indicates whether the row is male, if it is bit 1, no 0, and the same, the female vector bit 01011.

rowid	1	2	3	4	5	...
male	1	0	1	0	0	&nbs P;
female	0	1	0	1	1	&nbs P;

For the Marital Status column, the bitmap index generates three vectors, married 11000 ..., unmarried for 00100 ..., divorce 00010 ....

RowId	1	2	3	4	5	...
Married	1	1	0	0	0
Unmarried	0	0	1	0	1
Divorce	0	0	0	1	0

When we use the query statement "SELECT * from table where gender= ' man ' and marital=" unmarried ";" The first time to remove the male vector 10100 ..., and then take out the unmarried vector 00100 ..., the two vector to do and operation, then generate a new vector 00100 ..., you can find that the third bit is 1, that the table's third row of data is the result we need to query.

RowId	1	2	3	4	5
Man	1	0	1	0	0
and
Unmarried	0	0	1	0	1
Results	0	0	1	0	0

3. Applicable conditions for bitmap indexing

As stated above, the bitmap index is suitable for columns with only a few fixed values, such as gender, marital status, administrative district, etc., and the identity card type is not suitable for bitmap indexing.

In addition, bitmap indexes are suitable for static data, not for columns that are frequently updated by indexes. For example, there is a field busy, recording the busy of each machine or not, when the machine is busy, busy is 1, when the machine is not busy, busy is 0.

This time someone would say to use a bitmap index because busy has only two values. OK, we use the bitmap index to index the busy field! Suppose user A updates the busy value of a machine with update, such as the Update table set Table.busy=1 where rowid=100, but there is no commit, and User B updates the busy value of the other machine with update, Update table set Table.busy=1 where rowid=12; This time User B can not update, need to wait for user a commit.

　　Cause: User A updated the busy value of a machine to 1, causing all the busy of the machine's bitmap vectors to change, so that the database locks all the rows of the Busy=1 and is unlocked only after a commit.

Index: Bitmap Index understanding