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

This article mainly introduces how to solve the problem of slow count query in mysql, which has certain reference value and can be used for reference by friends who need it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

Count () Optimization of MySQL large Table

The following discussion is based on the mysql5.7 InnoDB storage engine. X86 windows operating system.

The structure of the created table is as follows (data volume is 1 million):

First of all, it is about which of mysql's count (*), count (competition) or count (1) is fast.

The implementation results are as follows:

It makes no difference! After adding the WHERE clause, the time of the three queries is the same, so I won't post the picture.

I wrote a SQL statement of select count (*) from table when I was in the company, which is very slow when there is a lot of data. So how to optimize it?

It starts with InnoDB's index, and InnoDB's index is B+Tree.

For a primary key index: it stores data only on the leaf node, its key is the primary key, and the value is the whole piece of data.

For secondary indexes: key is the indexed column and value is the primary key.

This gives us two messages:

1. The whole piece of data will be found according to the primary key.

two。 Only the primary key can be found according to the secondary index, and then the remaining information must be found through the primary key.

So if we want to optimize the count (*) operation, we need to find a short column and build a secondary index for it.

In my case, this is status, although its "severelity" is almost 0. 5%.

Index first: ALTER TABLE test1 ADD INDEX (status)

Then query, as shown in the following figure:

As you can see, the query time decreased from 3.35s to 0.26s, and the query speed increased nearly 13 times.

What happens if the index is the str column?

Index first: alter table test1 add index (str)

The results are as follows:

As you can see, the time is 0.422s, which is also very fast, but there is still a gap of about 1.5 times compared with the status column.

As a bold experiment, I delete the index in the status column, create a joint index of status and left (omdb,200) (this column averages 1000 characters), and look at the query time.

Indexing: alter table test1 add index (status,omdb)

The results are as follows:

The time is 1.172s

Alter table test1 add index (status,imdbid)

Supplement!

Pay attention to the failure of the index!

What it looks like after the index is established:

You can see that the key_len is 6 and the description of Extra is using index.

And if the index fails:

There are many cases of index failure, such as the use of functions,! = operations, etc. For details, please refer to the official documentation.

There is no deep research on MySQL. The above is based on my judgment based on the data structure of the B+ tree and the conjecture of the experimental results. If there are any deficiencies, please correct them.

Thank you for reading this article carefully. I hope it is helpful for everyone to share how to solve the problem of slow count query in mysql. At the same time, I also hope you can support us, pay attention to the industry information channel, and find out if you encounter problems. Detailed solutions are waiting for you to learn!

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.