Shulou(Shulou.com)06/02 Report--

This article mainly explains "how to calculate rows in MySQL explain". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to calculate rows in MySQL explain".

[background groundwork]

Related tables:

CREATE TABLE test_table (id bigint (20) UNSIGNED NOT NULL AUTO_INCREMENT, name varchar (32) NOT NULL, PRIMARY KEY (id) ENGINE = InnoDB CHARSET = utf8mb4

The number of records in test_table table is about 12w +

Problem description

Related SQL:

EXPLAIN SELECT COUNT (*)

FROM test_table

WHERE id > = 10534

AND id = 10000

LIMIT 10

Will the value of 2:LIMIT not affect the value of rows?

How on earth is rows calculated?

The rows is explained on the official website as follows:

"

Rows (JSON name: rows)

The rows column indicates the number of rows MySQL believes it must examine to execute the query.

For [InnoDB] tables, this number is an estimate, and may not always be exact.

Http://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain_rows

To put it simply: this rows is the number of rows that mysql thinks need to be detected.

To explore how rows is calculated, look at the MYSQL source code to see:

File 1:sql/opt_explain_traditional.cc key part: push (& items, column_buffer.col_rows, nil) file 2:sql/opt_explain.cc key part: select- > quick- > records file 3:sql/opt_range.cc key part: check_quick_select

The function of check_quick_select is annotated in the MySQL source code as follows:

"Calculate estimate of number records that will be retrieved by a range scan on given index using given SEL_ARG intervals tree.

This method determines how many rows need to be scanned only based on the conditions given about the index and the index itself.

Summary

The value of rows in MySQL Explain is the number of rows that MySQL thinks it wants to check (for reference only), not the number of rows in the result set; and the LIMIT in SQL is not directly related to this. In addition, many optimization methods, such as associative buffers and query caching, cannot affect the display of rows. MySQL may not really have to read all the lines it estimates, nor does it know anything about the operating system or hardware cache.

Thank you for your reading, the above is the content of "how to calculate rows in MySQL explain". After the study of this article, I believe you have a deeper understanding of how the rows in MySQL explain is calculated, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.