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

This article mainly shows you the "MySQL count (1), count (*), count (field) what are the differences", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "what are the differences between count (1), count (*) and count (field) in MySQL" this article.

With regard to the count of rows in the database, whether it is MySQL or Oracle, there is a function that can be used, and that is COUNT.

However, it is this commonly used COUNT function that hides a lot of mysteries, especially in the interview, which will be abused accidentally. If you don't believe me, please try to answer the following questions:

> 1. How many uses are there for COUNT?

> 2. What is the difference between COUNT (field name) and COUNT (*) query results?

> 3. What's the difference between COUNT (1) and COUNT (*)?

> 4. Which is more efficient between COUNT (1) and COUNT (*)?

> 5. Why the Alibaba Java Development Manual recommends using COUNT (*)

> 6. What optimizations have been made to COUNT (*) by MySQL's MyISAM engine?

> 7. What optimizations have been made to COUNT (*) by MySQL's InnoDB engine?

> 8. What is a key prerequisite for the optimization of COUNT (*) made by MySQL mentioned above?

> 9. Is there any difference in the condition of adding where when SELECT COUNT (*)?

> 10. What is the execution process of COUNT (*), COUNT (1), and COUNT (field name)?

If you can answer all the above 10 questions correctly, then you really know a lot about COUNT functions.

1. First acquaintance of COUNT

1. COUNT (expr), which returns the number of expr in the row retrieved by the SELECT statement that is not NULL. The result is a BIGINT value.

2. If the query result does not hit any records, 0 is returned.

3. It is worth noting, however, that the statistics of COUNT (*) include the number of rows with a value of NULL.

In addition to COUNT (id) and COUNT (*), you can use COUNT (constant) (such as COUNT (1)) to count rows, so what's the difference between these three SQL statements? Which is more efficient? Why is it mandatory in Alibaba's Java Development Manual that COUNT (column name) or COUNT (constant) should not be used instead of COUNT (*)?

Differences between 2.COUNT (field), COUNT (constant), and COUNT (*)

COUNT (constant) and COUNT (*) represent the number of rows that directly query eligible database tables.

The COUNT (column name) represents the number of rows in which the value of the eligible column of the query is not NULL.

COUNT (*) is the standard row count syntax defined by SQL92, and because it is the standard syntax, the MySQL database has been optimized a lot.

SQL92 is an ANSI/ISO standard of database. It defines a language (SQL) and the behavior of the database (transactions, isolation levels, etc.).

Optimization of 3.COUNT (*)

MySQL mainly uses two kinds of execution engines:

InnoDB engine

MyISAM engine

MyISAM does not support transactions, and locks in MyISAM are table-level locks, while InnoDB supports transactions and row-level locks.

MyISAM

MyISAM makes a simple optimization to record the total number of rows of the table separately, which can be returned directly if count (*) is executed, as long as there are no where conditions. MyISAM is a table-level lock, and there are no concurrent row operations, so the results are accurate.

InnoDB

InnoDB cannot use this caching operation because transactions are supported, most operations are row-level locks, and rows may be modified in parallel, so the cache record is inaccurate.

However, InnoDB does some optimizations for the COUNT (*) statement.

Scan the table through a low-cost index without paying attention to the specific contents of the table.

Indexes in InnoDB are divided into clustered index (primary key index) and non-clustered index (non-primary key index). The leaf node of clustered index stores the whole row of records, while the leaf node of non-clustered index stores the value of the primary key of the row record.

MySQL will first choose the smallest non-clustered index to scan the table.

The premise of optimization is that the query statement does not contain where condition and group by condition.

4.COUNT (*) and COUNT (1)

The official MySQL document says:

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

So, for count (1) and count (*), the optimization of MySQL is exactly the same, and there is no one faster!

However, count (*) is still recommended because this is the standard row count syntax defined by SQL92.

5.COUNT (field)

Perform a full table scan to determine whether the value of the specified field is NULL, or accumulate if it is not NULL.

Performance is slower than count (1) and count (*).

6. Summary

The use of the COUNT function is mainly used to count the number of rows in a table. The main uses are COUNT (*), COUNT (field) and COUNT (1).

Because COUNT (*) is the standard syntax for counting rows defined by SQL92, MySQL optimizes it a lot. The total number of rows of the table is recorded separately in MyISAM for COUNT (*) query, while InnoDB selects the smallest index when scanning the table to reduce costs. Of course, these optimizations are based on the absence of conditional queries for where and group.

There is no difference in the implementation of COUNT (*) and COUNT (1) in InnoDB, and the efficiency is the same, but COUNT (field) requires non-NULL judgment of the field, so it is less efficient.

Because COUNT (*) is the standard SQL92-defined syntax for counting rows and is efficient, use COUNT (*) directly to query the number of rows in the table!

The above is all the content of this article entitled "what are the differences between count (1), count (*) and count (field) in MySQL? thank you for reading!" I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.