Aim at the peak of the climber and not drinking some footprints in along the way, in the world of code farmers, beautiful application experience, from the programmer for the processing of detail, and the requirements of the self, agriculture in the yard a member of the young people is busy, every day, every week, can leave some footprints, is the creation of content, there is a persistent, is I don’t know why, If you are confused, take a look at the track of code farmers.

  • Beautiful musical beats take you through the coding process of this effect
  • Insist on every day, is the pursuit of every ideal youth
  • Follow in the footsteps of young people, and maybe your answer is right here
  • Take a look here if you’re confused

In real business development, you might often need to count the number of rows in a table, so you might use a query statement

 select count(*) from question_extracting
Copy the code

As shown below:The above query took 640 ms, so we might as well add another filter

SELECT
	count(*)
FROM
	question_extracting
WHERE
	create_time BETWEEN '2019-01-05'
AND '2019-06-05'
Copy the code

It took 1.6 seconds, which is insane. Do you know what happened?

1 Implementation principle of MySQL Count (*)

The InnoDB engine reads data line by line from the engine when it executes count(*) and accumulates the count.

In InnoDB’s transaction design, repeatable reads are the default isolation level. When InnoDB calculates data in a table, each row determines whether it is visible to the query. Only visible rows can be used to calculate the total number of rows in a table “based on this query”.

MySQL > query table count(*); MySQL > query table count(*); MySQL > query table count(*);

2 Store counts in the cache system?

Redis is a good choice. You can use a Redis service to hold the total number of rows of the table, and then increment the Redis count by 1 for each row that is inserted into the table and decrease the Redis count by 1 for each row that is deleted.

However, when Redis restarts abnormally, your data may be lost, so the solution is when Redis restarts abnormally, and then executes a separate count(*) in the database to obtain the true number of rows saved.

In the case of concurrency, the calculation of this cache is extremely imprecise because, for example, the query is 200 rows

  • There are 200 rows of results with the most recent insertion, and the Redis count has not yet been incremented by 1
  • There is no newly inserted record in the 200 rows, and 1 has been added to the Redis count

3 Save the count database

There are problems of losing data and inaccurate counting when using the cache system to save the counting results, so we can consider saving the counting results in a separate table, which can solve the counting problem.

4 count(primary key ID)

In the InnoDB engine, we can query counts in different ways like count(*), count(primary key ID), count(field) and count(1).

Count () is an aggregate function that evaluates the returned result set line by line.

Count (*), count(primary key ID), and count(1) all indicate the total number of rows that return a result set that meets the criteria.

Count (field) indicates the total number of rows that meet the condition (the parameter “field” is not NULL).

4.1 count(primary key ID)

The InnoDB engine iterates through the table, picks out the ID of each row, returns it to the server layer, and then decides to add up by row in the server layer.

4.2 the count (1)

The InnoDB engine iterates through the table but does not value it. The server layer adds a number “1” for each row returned, so count(1) is faster than count(primary key ID).

4.3 Count (field)

If the “field” is defined as not null, then InnoDB engine reads the field from the record line by line. Server layer determines that the field cannot be null and accumulates the field line by line.

If the “field” definition is allowed to be NULL, the InnoDB engine will read the field row by row from the record and then pull out the value to determine if it is not NULL.

4.4 the count (*)

MySQL optimizes count(*) to scan the primary key index directly. Count (*) does not fetch all columns from the primary key index.

Count (field)<count(primary key ID)<count(1)≈count(*)

The completion of

Not limited to thinking, not limited to language restrictions, is the highest realm of programming.

With xiaobian character, must be to record a set of video, and then upload

If you are interested, you can follow the watermelon video – the early riser or the public account of my big front-end career