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

This article will explain in detail what the MYSQL 8 Histogram statistics histogram is like, and the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have some understanding of the relevant knowledge after reading this article.

On Saturday and Sunday, relaxed and sinful, MYSQL officially entered the ranks of opening and hanging databases from 8.0. it is estimated that after 8.0 was rolled out, most of the original MYSQL experience carried out a reshuffle, starting with the MYSQL histogram today.

MYSQL has introduced the function of histogram since 8.03. what is histogram and what is the help of histogram to database query? let's learn below.

First of all, the introduction of histograms into databases is what most databases have already done. I listened to a lecture given by TIDB 2 years ago, when TIDB introduced histograms. MYSQL didn't introduce the histogram until 8.03.

A query actually queries the value of each column and displays it, according to the corresponding conditions, which are also obtained from each column. So here are three questions.

1 how many kinds of median are there in each column?

2 what is the number of each kind of worth in each column, which is more and that is less

3 how many rows are there in a table

From the above three questions, we can actually calculate a percentage, the percentage of a value in a column in the total number of rows in this column. The following figure is just an example, here is just an example, the histogram has horizontal and vertical coordinates, for example, the Abscissa is a range, which marks the range of each region in each histogram, and the ordinate is the number of each range in the entire table or column.

Without going into a deeper theory, let's try to build a histogram and then move on.

In the picture above

ANALYZE TABLE employees UPDATE HISTOGRAM ON first_name WITH 32 BUCKETS

Through the above statement, we create a histogram, which is an empirical value about why BUCKETS is 32. It is officially recommended to establish the given value of the histogram for the first time. Of course, the larger the value, the higher the accuracy, but the improvement in accuracy will not be so obvious after adjusting to a higher number. In fact, the number of buckets is related to the Abscissa of the above figure, which means that each worthwhile range will be fine.

Of course, we can also give multiple fields to create a histogram.

Note here that if you create a histogram for the same field multiple times, the newly created histogram will overwrite the previous histogram. The number of barrels in the histogram is 1-1024.

I would also like to remind you that the establishment of the histogram may be due to the wrong input, entered the wrong field name, and the histogram will create a histogram for the wrong column name.

Of course, you can delete the histogram where you created the wrong histogram.

With regard to the use of the histogram, there is a restriction on whether this information can be put into memory, which limits whether the histogram can be put into memory.

Jude

The following figure

SELECT table_name,histogram- > >'$. "sampling-rate" 'FROM information_schema.column_statistics WHERE table_name = "employees" AND column_name = "first_name"

Through the query, we can see that we have created the bucket, and how many sample data have been put into the bucket, 16%

At this point, it is estimated that some people have wondered what the use of this histogram is, why not use the index, there is an index to update the index statistics.

Let's look down.

1 I do not build an index

For the first time, I did not build a histogram when I queried the data

3 I build histogram, not index query

I set up an index query

Delete the established histogram first

ANALYZE TABLE employees drop HISTOGRAM ON last_name

ANALYZE TABLE employees drop HISTOGRAM ON first_name

Query data

We can see that filtered is 20%.

We build a histogram.

You can clearly see that filtered filtering 11% is beneficial to the query or improve the performance of the query without an index.

So the question is, if I build an index, in fact, you don't have to ask, the actual answer is generally faster to go index.

The following is the query time for walking the index

After I delete the index, the overall query takes 0.14 seconds

In the following figure, I removed the histogram again, and without index and histogram, it took 0.15 seconds.

As a new thing of MYSQL, histogram should be supported, but in fact, it belongs to chicken rib technology at present.

1 the functions of most established databases

2 currently need to manually establish updates

3The simple use of histogram is of limited help to the query of data.

Of course, if you don't like this feature,

Set optimizer_switch='condition_fanout_filter=off'

You can close it.

If you want to know, how many histograms have you built?

Select * from information_schema.column_statistics\ G

You can check column_statistics to get the data

Histogram can be used as a supplementary means of index query at present, and the function in the future should become stronger. MYSQL is no longer an Internet database since 8.0. it is moving forward to omni-directional, enterprise-level data. of course, in the face of ORACLE PG, MSSQL, in the enterprise application, the road ahead is also hard!

So much for sharing the MYSQL 8 Histogram statistics histogram. I hope the above content can help you and learn more. If you think the article is good, you can share it for more people to see.

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.