Usage of count() in mysql large table and optimization of count() in mysql

The content this article brings to you is about the usage of count() in mysql large tables and the optimization of count() in mysql. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you. helped.

A single table contains 60 million data, but you cannot split it. You need to separately count how much data there is in the table, how many products A have, and how many products B have.

Before optimization, the table structure is as follows. In order to hide the content, I fuzzified the corresponding fields.

CREATE TABLE `xxxx` (
  `link` varchar(200) DEFAULT NULL,
  `test0` varchar(500) DEFAULT NULL,
  `test1` varchar(50) DEFAULT NULL,
  `test2` int(11) DEFAULT NULL,
  `test3` varchar(20) DEFAULT NULL,
  `test4` varchar(50) DEFAULT NULL,
  `test5` varchar(50) NOT NULL,
  `inserttime` datetime DEFAULT NULL,
  `test6` bit(1) NOT NULL DEFAULT b'0',
  `A` bit(1) NOT NULL DEFAULT b'0',
  `B` bit(1) NOT NULL DEFAULT b'0' ,
  PRIMARY KEY (`test5`),
  KEY `test6` (`test6`) USING BTREE,
  KEY `A` (`A`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This is a regular InnoDB table, so its count(*) is compared to MyISAM's The efficiency is much slower. The number of rows displayed by InnoDB is not very accurate, so I need to count it here. There are several strategies.
A total of 61500000 data

count(*) takes 1539.499 s

count(1) takes 907.581s

count(A) Count the index.

count(test6) Count the primary key.

Without exception, since this table is not optimized, any of the above will take thousands of seconds, which we cannot bear.

Let’s start to analyze and deal with this problem.

It is expected that the count(*) of the entire table should be normal within 200s, good within 100, and excellent within 50.

First of all, I extracted test6 from it and formed a separate table. Perform the operation.
A total of 61,500,000 data

count(*) takes 10.238s

count(1) takes 8.710s

count(test6) Perform the operation on the primary key count. It takes 12.957s

count(1) is the most efficient, and is faster than the slowest count(pk)52.0%.

Change the fields you can determine to the optimal value, for example:

varchar is more char. Although varchar can automatically allocate the size of storage space, .varchar needs to use 1 to 2 Additional bytes are used to record the length of the string and increase its update operation time.

datetime is changed to timestamp which is between 1978-2038

Finally use count(1 ) is the fastest time-consuming test, 168s. Although it is a bit slow, it is acceptable.

Summary:

Redesign the fields in your table and try to optimize its length. Don’t blindly use it Multiple varchar.

Use count(1) instead of count(*) to retrieve.

Related recommendations:

Code implementation of Infinitus classification in mysql

Summary of Mysql database optimization methods (must read)

The above is the detailed content of Usage of count() in mysql large table and optimization of count() in mysql. For more information, please follow other related articles on the PHP Chinese website!