As for analyzing the performance difference, you can keep the following principles in mind:

Give the server tier what it wants

InnoDB only gives the necessary values

The current optimizer only optimizes the semantics of count(*) for ? Take the number of rows? and the rest? obvious? optimizations are not done. Next, let's go through them one by one.

For count(primary key id), InnoDB engine traverses the whole table, takes out the id value of each row, and returns it to the server layer, which, after getting the id, determines that it is impossible to be null, and then accumulates it by rows.

For count(1), InnoDB engine traverses the whole table, but does not take the value. server layer for each row returned, put a number ?1? into it, judge it is impossible to be null, and then add up by row.

Looking at the difference between these two uses alone, you can contrast that count(1) performs faster than count(primary key id). This is because returning the id from the engine involves parsing the rows of data and copying the field values.

For count(field):

If the ? field? is defined as not null, the field is read from inside the record row by row, judged not to be null, and accumulated by row;

If the ? field? Definition allows for null, then the implementation of the time, judgment to the possibility of null, but also to take out the value of the judgment again, not null before adding up. That is, the first principle of the previous, server layer want what field, InnoDB will return what field.

But count(*) is the exception, and will not take out all the fields, but specifically optimized, not to take the value. count(*) is certainly not null, according to the rows of accumulation.

See here, you must say, the optimizer can not judge for themselves, the primary key id is certainly non-null ah, why can not according to count(*) to deal with, how simple optimization ah.

Of course, mysql is optimized specifically for this statement, not impossible. But there are so many cases where this needs to be specifically optimized, and mysql has already optimized count(*), that it's just as easy to use this usage directly.

So the conclusion is: if you sort by efficiency, count(field) < count(primary key id) < count(1) ? count(*). So it is recommended to use count(*) as much as possible.

Copy Source:/2020/c00d182e.html#more

Mysql Performance Optimization ------ Get how many rows of mysql

Tags: simple no copy nod based on tps record take out is