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

Recently saw a very good information, which a few words with a few figures on the column-based storage (Column-based Storage) explained clearly, cattle ah! What I like most is that it is easy to understand and explain the background knowledge clearly, rather than talking about concepts at length.

1 Why do you store them in columns?

Columnar or column-based storage is the opposite of row-based storage in traditional relational databases. In short, the difference between the two is how to organize the table:

Row-based storage stores atable in a sequence of rows.

Column-based storage storesa table in a sequence of columns.

Here's an example:

As you can clearly see from the above figure, the data in the next table in row storage are all together, but the data in column storage are all stored separately. So they have these advantages and disadvantages:

Row storage Column storage Advantages

The data is stored together.

INSERT/UPDATE Easy

Only the columns involved will be read when querying

Projection is very efficient.

Any column can be indexed.

All data is read even if only a few columns are involved in Selection

When selection is complete, selected columns are reassembled

INSERT/UPDATE is more troublesome

Note: Relational Database Theory Review-Selection and Projection

2 Supplement: Data compression

I skipped over another technique mentioned in the literature: compressing data through dictionary tables. For the sake of later explanation, this part is also mentioned in passing.

Below is what the chart looks like. After the dictionary table for data compression, the table strings are turned into numbers. Because each string appears only once in the dictionary, compression is achieved (a bit like normalization and denormalization).

3 Query execution performance

The following is the most cattle diagram, through a query execution process to illustrate the advantages of column storage (and data compression):

The key steps are as follows:

1. Go to the dictionary to find the number corresponding to the string (only perform a string comparison).

2. Match the numbers in the list, and set the position on the match to 1.

3. The matching results of different columns are subjected to bit operation to obtain record subscripts which meet all conditions.

4. Use this subscript to assemble the final result set.

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.