Shulou(Shulou.com)05/31 Report--

Editor to share with you the differences between count (*), count (1), count (primary key) and count (field) in mysql. I hope you will get something after reading this article. Let's discuss it together.

Select count (*) should be a commonly used statement to count the number of rows recorded. But slowly you will find that this sentence is getting slower and slower. Why?

In MySQL, count (*) is implemented differently for different storage engines.

1. MyISAM engine, which is simple and rough, stores the total number of rows of the table directly on disk, so it is very efficient.

2. In the InnoDB engine, when count (*) is executed, the whole table is traversed, and the data needs to be checked out row by row, so the performance is poor.

Why can MyISAM do this? Because it doesn't support transactions, you don't have to worry about data inconsistencies, so you can quickly return count (*).

Suppose that there are 1000 pieces of data in table t and the following three user parallel sessions:

1. A starts the transaction and queries the total number of rows of the table

2. C inserts a piece of data directly, and then queries the total number of rows

3. B starts the transaction, inserts a piece of data, and then queries the total number of rows

4. Total rows of C query

Notice that none of the transactions initiated above have been committed.

The results of A, B, and C queries are all different.

B reads 1002 because of the existence of repeatable isolation levels.

On the other hand, C does not open the transaction, so you cannot see the updates of other transactions.

In InnoDB, primary key indexes store data, while secondary indexes store only primary key values. Secondary indexes are much smaller and lighter than primary key indexes.

In this case, when InnoDB executes count (*), it determines which index to use and selects the smallest tree to traverse. On the premise of ensuring correct logic, reducing the amount of scanned data as much as possible is one of the general rules for database system design.

To sum up, in the InnoDB engine, in each session, you need to read the data line by line, optimize count (*), and then count and return the total number of rows.

Differences between count (*), count (1), count (primary key), and count (field)

(1) for count (primary key id), InnoDB traverses the entire table, takes the primary key id of each row, and returns it to the server layer. After getting the data, the server layer makes a judgment and accumulation.

(2) for count (1), InnoDB still traverses the whole table, but does not take a value. The server layer adds a 1 to each row of data returned, and then judges and accumulates.

(3) for count (field):

1. If the "field" is defined as not null, read out the field line by line from the record, determine that it cannot be null, and accumulate by line.

2. If the definition of "field" is allowed to be null, then when executing, it is judged that it may be null, and the value should be taken out and judged again. It is not null that accumulates.

Performance differences:

1. Column name is primary key, count (1) = count (*) = count (column name)

two。 Column name is not primary key, count (1) = count (*) > count (column name)

3. The column name is not the primary key, and the column name creates an index count (1) = count (*) = count (column name)

Use selection:

1.COUNT (*) is essentially the same as COUNT (1), and the specific response time is related to the storage engine and WHERE conditions. Individuals are used to using COUNT (*).

two。 Indexes are important for the COUNT () function, and MySQL automatically optimizes the use of the appropriate index if an index is to be used.

3.COUNT (column name) needs to note that the statistics are non-NULL columns.

After reading this article, I believe you have a certain understanding of "what is the difference between count (*), count (1), count (primary key) and count (field) in mysql?" if you 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.