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

It is believed that many inexperienced people have no idea about how to use the COUNT query function in MySQL. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

First, create a table with only three id,name,sex fields, and use the stored procedure to randomly insert 1 million pieces of data:

First, let's post the most basic count function syntax:

Select count (expr) from table

You can see that the count function actually needs to pass expr. Generally speaking, there are three values for this expr:

Column name: retrieves the number of entries whose corresponding column value is not NULL.

*: query the number of rows that meet the criteria, regardless of whether the column value is NULL. The returned result will be returned.

Constant: the number of rows that meet the condition of the query, regardless of whether the column value is NULL or not, the returned result will be returned.

Efficiency comparison of count (*), count (id) and count (1)

In fact, there are different opinions about which is more efficient, count (*) or count (1). We can first look at the efficiency comparison:

It can be found that the execution speed of the two SQL statements is about the same. Count (1) and count (*) both query the number of rows of data in the whole table. Many comments on the Internet may say that count (*) actually takes the count (1) query, so using count (1) query can save conversion time. In fact, no matter count (*) or count (1) are exactly the same, they all indicate the specified non-empty expression. So the number of rows that meet the criteria is queried. Why would I say that the two statements have the same effect? Because the official Mysql document says:

InnoDB handles SELECT COUNT (*) and SELECT COUNT (1) operations in the same way. There is no performance difference.

So in fact, there is no difference between count (*) and count (1) in terms of execution efficiency, so since there is no difference, which one should I use? I personally recommend using count (*) because the SQL92 standard defines count (*) as the standard syntax for counting rows, so Mysql has been constantly optimizing count (*). Then some people say that count (id) will be more efficient? Because you can use the primary key index, shouldn't it be more efficient to use the index? But in fact, count (id) queries are slower than count (*) queries. We can test it:

Some people may wonder that the purpose of adding index columns is to improve query efficiency. Usually we retrieve the data belongs to the scope of the query, query the specified data, so walking the index can improve the query efficiency, but count (id) to choose a large index base of the primary key index is certainly less efficient. Because the primary key index and the data file are stored together, fetching the number of bars through the primary key id retrieves the data file, count (id) retrieves the entire table, then traverses the id of each row of data, and then returns the id of each row in the server layer, which is + 1 if not empty count, while the whole table is retrieved like count (*), but the id value is not taken, because the result can be obtained in the index tree. Therefore, count (id) needs to get the data and filter the data from id to null. The data efficiency must be much slower.

Count (*) optimization

There is no difference between count (*) and count (1), but the execution time is more than 1s, and the amount of data is only 1 million, so we certainly need to make proper optimization. Because count (*) actually optimizes the query using the index of the smallest field, but because there is only one primary key index in our table, and we just said that count (id) is less efficient than count (*), we do not use index query by default. We can test it with explain:

You can see that the query does not take any indexes, so it is inevitable that it is inefficient. Count (*) just said that the index optimization query with the smallest field will be found by default, so we add a status field to the table, make it tinyint, and add a secondary index, and then test the count (*) execution time:

You can see that count (*) million-level data has been successfully optimized to 0.32 seconds. It is fully in line with our daily needs. So when we need to fetch the entire number of rows of the table, we can add a non-empty tinyint field to the table, and add a secondary index, and count (*) will use this secondary index to optimize the query speed.

Count (col)

Having said count (*), we know that if we can use count (*) when we need to return all rows, then what do we do if we need to query the total number of rows whose names are not empty? We can add a where to achieve this quickly:

You can see that the number of entries is only 500000, and the other 500000 names are ignored because the value of name is null. The execution time is 1.38s, which is the shortest time I have tested and fetched many times. Therefore, million-level data needs seconds that obviously do not meet the requirements, so we can use count (field name) to filter a single field, and automatically remove the blank lines. We can test it: we can see that the total number of entries can be queried in 1.1 seconds, but it still doesn't meet our needs. It's still seconds. Let's use explain to check the running parameters:

You can see that the full table retrieval scans the data file because the index is not used. Now let's add an index to the name field and test it again:

You can see that it only takes 0.33 seconds to complete the execution. Use explain to check:

Obviously, this query is indexed, what is the reason for speeding up the query? Because we created a secondary index in the name field, when there is no secondary index, the count operation can only retrieve data from the whole table. When we count the total number of entries through the secondary index, there is no need to scan the data file, because the data stored in the secondary index is the value of the name field and the primary key id value. So when you count (col), you can add a secondary index to the field to speed up the retrieval rate.

The count function specifies the where condition

There are also two cases of scoring here: count (*) and count (col).

Count (*): by adding an index to the where condition, you can use the index to optimize the query. For example, the type column we just added indicates whether user accounts are available. We now need to query the total number of available accounts and write: select count (*) from order_info where type = 0

You can check the running parameters in explain to find that the index is indeed used to optimize the query:

Count (col): if you query the total number of columns that are not empty and add where conditions, you need col to add indexes and where uses col for conditional restrictions. We can first look at how name adds indexes but where uses sex constraints: then we query name and use name constraints to see if indexes can be used to optimize the query:

You can see that the column name of the query is consistent with that of the where condition, and the index query can improve the efficiency. So in the case of a large amount of data to query the total number of items, we should reasonably use the index to optimize the query.

After reading the above, have you mastered how to use the COUNT query function in MySQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.