Shulou(Shulou.com)06/01 Report--

This article mainly introduces "bitmap index BitMap example analysis". In daily operation, I believe many people have doubts about bitmap index BitMap example analysis. Xiaobian consulted all kinds of information and sorted out simple and easy operation methods. I hope to help you answer the doubts of "bitmap index BitMap example analysis"! Next, please follow the small series to learn together!

1. case

There is a table named table, which consists of three columns, namely name, sex and marital status, of which gender is only male and female, and marital status is married, unmarried and divorced. There are 100w records in this table. Select * from table where Gender='male' and Marital='unmarried'

Name (s)

Sex (Gender)

Marital status

Zhang San

male

married

Li si

female

married

King the fifth

male

unmarried

Zhao Liu

female

divorce

Sun Qi

female

unmarried

...

...

...

1. No index: Without an index, the database can only scan all records row by row, and then determine whether the record meets the query criteria.

2. B-tree index

For gender, the range of available values is only 'male',' female', and male and female may each have 50% of the data in the table, so adding a B-tree index still requires taking out half of the data, so it is completely unnecessary. On the contrary, if a field has a wide range of values and almost no duplication, such as ID number, it is more appropriate to use B-tree index at this time. In fact, when the extracted row data occupies most of the data in the table, even if the B-tree index is added, databases such as Oracle and MySQL will not use the B-tree index, and it is likely that all rows will be scanned.

2. bitmap index

If the cardinality of the column that the user queries is very small, that is, there are only a few fixed values, such as gender, marital status, administrative region, and so on. To index these columns with low base values, bitmap indexes are needed.

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

RowId

1

2

3

4

5

...

male

1

0

1

0

0

female

0

1

0

1

1

For the marital status column, the bitmap index generates three vectors, married is 11000..., unmarried is 00100..., Divorce is 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 "select * from table where Gender ='male ' and Marital ='unmarried';" we first take the male vector 10100... Then take the unmarried vector 00100..., And the two vectors, then generate a new vector 00100..., We can find that the third bit is 1, indicating that the third row of data in the table is the result of our query.

RowId

1

2

3

4

5

male

1

0

1

0

0

&

unmarried

0

0

1

0

1

results

0

0

1

0

0

3. Bitmap index applicable conditions

As mentioned above, bitmap indexes are suitable for columns with only a few fixed values, such as gender, marital status, administrative district, etc., while ID numbers are not suitable for bitmap indexes.

In addition, bitmap indexes are good for static data and not for columns whose indexes are frequently updated. For example, there is a busy field that records whether each machine is busy or not. When the machine is busy, busy is 1, and when the machine is not busy, busy is 0.

At this point someone might say use bitmap index because busy has only two values. OK, we use bitmap index to index busy field! Suppose user A uses update to update the busy value of a machine, such as update table set table.busy=1 where rowid=100;, but has not committed, and user B also uses update to update the busy value of another machine, update table set table.busy=1 where rowid=12; at this time, user B cannot update, and needs to wait for user A to commit.

Reason: User A updates the busy value of a machine to 1, which causes the bitmap vector of all machines with busy = 1 to change, so the database locks all rows with busy=1 and unlocks them only after commit.

At this point, the study of "Bitmap index BitMap example analysis" is over, hoping to solve everyone's doubts. Theory and practice can better match to help everyone learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.