current position:Home>Count() function of MySQL

Count() function of MySQL

2022-01-27 01:47:03 Under the night light

First time to know COUNT

COUNT(expr) , return SELECT In the line retrieved by the statement expr The value of the NULL The number of . The result is a BIGINT value . If the query results do not hit any records , Then return to 0

COUNT( Constant ) and COUNT(*) It refers to the number of qualified rows in the direct query database . Column values of... Are not ignored NULL

and COUNT( Name ) It refers to the qualified columns in the query database, and the value is not NULL( The null here is not just an empty string or 0, It means null) The number of rows .

COUNT(*) yes SQL92 The syntax for defining the number of standard statistics lines , Because it's standard syntax , therefore MySQL The database has been optimized a lot .

SQL92 It's one of the databases ANSI/ISO standard . It defines a language (SQL) And the behavior of the database ( Business 、 Isolation level, etc ).

COUNT(*) Optimize

Scan tables through low-cost indexes , Instead of focusing on the specific contents of the table ,InnoDB The indexes in are divided into clustered indexes ( primary key ) And non clustered indexes ( Non primary key index ), The leaf node of the cluster index stores the whole row of records , Instead, the leaf node of the non clustered index stores the value of the primary key of the row record ,MySQL The smallest non clustered index will be preferred to scan the table , The premise of optimization is that the query statement does not contain where Conditions and group by Conditions

COUNT(1) and COUNT(*) The difference between

MySQL Official documents say so :

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

therefore , about count(1) and count(*),MySQL The optimization of is exactly the same , There is no one who is faster !

But it is still recommended to use count(*), Because this is SQL92 The syntax for defining the number of standard statistics lines .

Alibaba Development Manual :

summary

On the effect of execution :

  • count(*) It includes all the columns , Equivalent to the number of lines , When it comes to statistical results , Column values of... Are not ignored NULL

  • count(1) Including all the columns , use 1 Represents the line of code , When it comes to statistical results , Column values of... Are not ignored NULL

  • count( Name ) Only include the column of the list , When it comes to statistical results , Will ignore null column values ( The null here is not just an empty string or 0, It means null) Count of , That is, the value of a field is NULL when , No statistics .

In terms of execution efficiency :

  • The column name is primary key , count( Name )  than  count(1) fast

  • Column name is not primary key , count(1)  than  count( Name ) fast

  • If the table has multiple columns and no primary key , be  count(1)  Is superior to the execution efficiency  count(*)

  • If there is a primary key , be  select count( Primary key )  The efficiency of execution is optimal

  • If the table has only one field , be  select count(*) The optimal

copyright notice
author[Under the night light],Please bring the original link to reprint, thank you.
https://en.cdmana.com/2022/01/202201270147007941.html

Random recommended