Summary of usage differences of MySQL Count function

Preface

Hello everyone, how much do you know about Count in MySQL?

The demand for statistical data is very easy to encounter in our daily development. Well, MySQL also supports a variety of calculation functions.

Next, let’s take a look at the differences between them and whether they have any pitfalls. [Recommended learning: MySQL video tutorial]

The difference between count(*), count(1), and count(id)

The meaning of count: count() is an aggregate function. The returned result set is judged row by row. If the parameter of the count function is not NULL, the cumulative value will be increased by 1, otherwise it will not be added. Finally, the cumulative value is returned.

count (field) is special, it means returning the total number of data rows that meet the conditions, and the parameter "field" is not NULL

Analysis Performance difference principle

• Give the server layer whatever it wants;

• InnoDB only gives necessary values;

• The current optimizer only optimizes the semantics of count(*) to "get the number of rows", and does not do other "obvious" optimizations

Example:

count (primary key id), the InnoDB engine will traverse the entire table, take out the id value of each row, and return it to the server layer. After the server layer gets the id, it judges that it cannot be empty, so it accumulates it row by row.

count(1), the InnoDB engine traverses the entire table but does not take values. The server layer puts a number "1" into each row returned. If it is judged that it cannot be empty, it is accumulated by row.

count (field), if the server needs a field, it will return the field. If the field is empty, statistics will not be performed. If the value of the field is too large, it will cause low efficiency.

Efficiency sorting

count(字段)<count(主键 id)<count(1)≈count(*)

##Why is count(*) the fastest?

The optimizer does its job and finds the smallest number to traverse.

InnoDB is an index-organized table. The leaf nodes of the primary key index tree are data, while the leaf nodes of the ordinary index tree are the primary key values. Therefore, the ordinary index tree is much smaller than the primary key index tree. For operations such as count(*), the results obtained by traversing any index tree are logically the same. Therefore, the MySQL optimizer will find the smallest tree to traverse. On the premise of ensuring that the logic is correct, minimizing the amount of data scanned is one of the general rules of database system design.

Several pitfalls about NULL

// 数据中存在null值数据
select count(*) from audit_log a;
结果：1

select count(id) from audit_log a;
结果：0

We see that the results of count are inconsistent. The number of records should be 1, but count(id) is not. Equal to 0

This is because count (field) is not counted and the field value is null

So when the field is a non-empty field, you need to use count(*) to solve it this problem.

count(*) summary

• MyISAM table although count(*) is fast, it does not support transactions;

• Although the show table status command returns quickly, it is not accurate;

• InnoDB table direct count(*) will traverse the entire table, although The results are accurate, but can cause performance issues.

The above is the detailed content of Summary of usage differences of MySQL Count function. For more information, please follow other related articles on the PHP Chinese website!