Shulou(Shulou.com)05/31 Report--

Editor to share with you how to achieve count without filtering conditions in MySQL, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Count (*)

Realize

1. MyISAM: store the total number of rows of the table on disk. Queries without filtering conditions can be returned directly.

If there is a filter condition of count (*), MyISAM will not return soon.

2. InnoDB: read the data row by line from the storage engine, and then add up the count

Because of MVCC, it is uncertain how many rows InnoDB should return at the same time

Sample

Suppose table t has 10000 records

Session Asession Bsession CBEGIN

SELECT COUNT (*) FROM t; (returns 10000)

INSERT INTO t; (insert a row)

BEGIN

INSERT INTO t (insert a row)

SELECT COUNT (*) FROM t; (return 10000) SELECT COUNT (*) FROM t; (return 10002) SELECT COUNT (*) FROM T; (return 10001)

At the last minute, three sessions queried the total number of rows of t at the same time, but the results were different.

The default transaction isolation level for InnoDB is RR, which is implemented through MVCC

Each transaction needs to determine whether each line of record is visible to itself.

Optimize

1. InnoDB is an index organization table

Clustered index tree: leaf nodes are data

Secondary index tree: leaf node is the primary key value

2. The space occupied by the secondary index tree is much smaller than that of the clustered index tree.

3. On the premise of ensuring correct logic, the optimizer will traverse the smallest index tree to minimize the amount of scanned data.

For count operations without filtering conditions, no matter which index tree is traversed, the effect is the same.

The optimizer will choose the best index tree for count (*).

Show table status

Mysql > SHOW TABLE STATUS\ G * * 1. Row * * Name: t Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 100256 Avg_row_length: 47 Data_length: 4734976Max_data_length: 0 Index_length: 5275648 Data_free: 0 Auto_increment: NULL Create_time: 2019-02-01 17:49: 07 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment:

SHOW TABLE STATUS is also estimated by sampling (very imprecise), with an error of 40% to 50%.

Maintenance count

Caching

Scheme

Use Redis to save the total number of rows of the table (without filtering conditions)

The Redis count is + 1 for each row inserted in this table, and the Redis count is-1 for each row deleted.

Shortcoming

Missing updates

1. Redis may lose updates

2. Solution: after an abnormal restart of Redis, execute count (*) to the database.

Abnormal restarts are not common, when the cost of a full table scan is acceptable

Logic is imprecise-fatal

1. Scenario: displays the total number of operation records and 100 records of recent operations

2. Redis and MySQL are two different storage systems, which do not support distributed transactions, so they cannot get an accurate consistent view.

Time series A

At T3, session B found 100 rows of results with newly inserted records, but Redis has not yet + 1, which is logically inconsistent.

Time session Asession BT1

T2 inserts a row of data R

T3

Read Redis count

Query the last 100 records; T4Redis count + 1

Time series B

In session B, there are no newly inserted records in the 100th row of results checked at T3, but Redis has been + 1, which is logically inconsistent.

Time session Asession BT1

T2Redis count + 1

T3

Read Redis count

Query the last 100 records; T4 insert a row of data R

Database

Put the count values in a separate count table C in the database

The problem of crash loss is solved by using the crash-safe feature of InnoDB.

The problem of consistent view is solved by using the transaction-supporting feature of InnoDB.

Session B at T3, the transaction of session A has not yet been committed, the count value + 1 of Table C is not visible to itself, and the logic is consistent.

Time session Asession BT1

T2BEGIN

Count values in Table C + 1

T3

BEGIN

Reading meter C count value

Query the latest 100 records

COMMIT;T4 inserts a row of data R

COMMIT

Performance of count

Semantic meaning

1. Count () is an aggregate function that judges the returned result set row by line.

If the parameter value of the count function is not NULL, the cumulative value is + 1, otherwise it is not added, and the cumulative value is returned.

2. Count (field F)

Field F may be NULL

Indicates that field F in the result set that meets the criteria is not the total number of NULL

3. Count (primary key ID), count (1), count (*)

It can't be NULL

Represents the total number of result sets returned that meet the criteria

4. The InnoDB engine returns whatever field is needed in the Server layer.

With the exception of count (*), it does not return the entire row, only a blank line

Performance comparison

Count (Field F)

1. If field F is defined as not allowed to be NULL, read the field line by line from the record, and add it by line after passing the judgment.

Judging from the table structure, the field is impossible to be NULL.

2. If field F is defined as allowing NULL, read the field line by line from the record, and add it by line after passing the judgment.

Judging from the table structure, it is possible that the field is NULL.

Determine whether the field value is actually NULL

3. If there is no secondary index on field F, you can only traverse the entire table (clustered index)

4. Because InnoDB must return field F, the optimizer can make fewer optimization decisions

For example, you can't choose the best index to traverse

Count (primary key ID)

InnoDB traverses the entire table (clustered index), takes the id value of each row and returns it to the Server layer

After the Server layer gets the id, it determines that it is impossible to be NULL, and then accumulates by line.

The optimizer may choose the optimal index to traverse

Count (1)

The InnoDB engine traverses the entire table (clustered index), but takes no values

For each row returned, the Server layer puts a number 1 in it, determines that it is impossible to NULL, and accumulates by row

Count (1) is faster than count (primary key ID) because count (primary key ID) involves two-part operations.

Parse data rows

Copy field valu

Count (*)

Count (*) does not take out all the values, but optimizes them specially and does not take values, because "*" is definitely not NULL and accumulates by line.

No value: InnoDB returns a blank line that tells Server that the layer is not NULL and can be counted

Efficiency ranking

Count (Field F)

SHOW CREATE TABLE prop_action_batch_reward\ G * * 1. Row * * Table: prop_action_batch_rewardCreate Table: CREATE TABLE `prop_action_batch_ reward` (`id`bigint (20) NOT NULL, `source` int (11) DEFAULT NULL, `serial_ id` bigint (20) NOT NULL, `create_ time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `user_ ids` mediumtext, `serial_ index` tinyint (4) DEFAULT'0' PRIMARY KEY (`id`), UNIQUE KEY `uniq_serial_id_source_ index` (`serial_ id`, `source`, `serial_ index`), KEY `idx_create_ time` (`create_ time`) ENGINE=InnoDB DEFAULT CHARSET=utf8

Count (Field F)

No index

There is no index on user_ids, and InnoDB must return the user_ids field and can only traverse the clustered index

Mysql > EXPLAIN SELECT COUNT (user_ids) FROM prop_action_batch_reward +-- +-+ | id | select_type | table | | type | possible_keys | key | key_len | ref | rows | Extra | +-- -+-+ | 1 | SIMPLE | prop_action_batch_reward | ALL | NULL | 16435876 | NULL | +-- -+ mysql > SELECT COUNT (user_ids) FROM prop_action_batch_reward +-+ | count (user_ids) | +-+ | 17689788 | +-+ 1 row in set (10.93 sec)

Have an index

1. There is an index on serial_id, so you can traverse uniq_serial_id_source_index.

2. However, because the InnoDB must return the serial_id field, it will not traverse the logical equivalent of the better choice idx_create_time

If you select idx_create_time and return the serial_id field, this means that you must return to the table

Mysql > EXPLAIN SELECT COUNT (serial_id) FROM prop_action_batch_reward +-+ -+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+-- +-- +-+ | 1 | SIMPLE | prop_action_batch_reward | index | NULL | uniq_serial_id_source_index | 15 | NULL | 16434890 | Using index | + -+- -+ mysql > SELECT COUNT (serial_id) FROM prop_action_batch_reward +-+ | count (serial_id) | +-+ | 17705069 | +-+ 1 row in set (5.04 sec)

Count (primary key ID)

The optimizer chooses the optimal index idx_create_time to traverse instead of clustered indexes.

Mysql > EXPLAIN SELECT COUNT (id) FROM prop_action_batch_reward +-+-+ -+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+ -+ | 1 | SIMPLE | prop_action_batch_reward | index | NULL | idx_create_time | 5 | NULL | 16436797 | Using index | +-+-- -+-+ mysql > SELECT COUNT (id) FROM prop_action_batch_reward +-+ | count (id) | +-+ | 17705383 | +-+ 1 row in set (4.54 sec)

Count (1)

Mysql > EXPLAIN SELECT COUNT (1) FROM prop_action_batch_reward +-+-+ -+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+ -+ | 1 | SIMPLE | prop_action_batch_reward | index | NULL | idx_create_time | 5 | NULL | 16437220 | Using index | +-+-- -+-+ mysql > SELECT COUNT (1) FROM prop_action_batch_reward +-+ | count (1) | +-+ | 17705808 | +-+ 1 row in set (4.12sec)

Count (*)

Mysql > EXPLAIN SELECT COUNT (*) FROM prop_action_batch_reward +-+-+ -+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+ -+ | 1 | SIMPLE | prop_action_batch_reward | index | NULL | idx_create_time | 5 | NULL | 16437518 | Using index | +-+-- -+-+ mysql > SELECT COUNT (*) FROM prop_action_batch_reward +-+ | count (*) | +-+ | 17706074 | +-+ 1 row in set (4.06sec) these are all the contents of the article "how to achieve count without filtering conditions in MySQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.