2023年2月8日 10:21:19go评论65阅读模式

英文:

Why is row-access high when comparison, order, limit for PK in mysql's query?

问题

在这个例子中，id 是主键。

而总行数是 541。

在下面的第一个例子中，访问了所有id > 50的行，但由于limit 5，预期rows值为 5。问题在哪里？

order by列是主键，where comparison列也是主键。

英文:

In this example, id is pk.

And the total number of rows is 541.

In the first example below, all rows with id > 50 are accessed, but because of limit 5, the
rows value is expected to be 5. What's the problem?

The order by column is pk, and the where comparison column is also pk.

=======

explain select * from mytable where id > 50 order by id limit 5;

select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
SIMPLE	cpc_pre_booking		range	PRIMARY	PRIMARY	8		491	100	Using where

explain select * from mytable where id > 200 order by id limit 5;

select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
SIMPLE	cpc_pre_booking		range	PRIMARY	PRIMARY	8		341	100	Using where

explain select * from mytable where id > 400 order by id limit 5;

select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
SIMPLE	cpc_pre_booking		range	PRIMARY	PRIMARY	8		141	100	Using where

I want to know what am i missing

答案1

得分: 1

“EXPLAIN”中的“Rows”是基于统计数据的估计，通常_不_考虑“LIMIT”值。不要过于信任它们。

事后，慢查询日志将提供准确的“Rows_examined”和“Rows_returned”。

还有另一种方法（也是通过实际运行查询）：

FLUSH STATUS;
SELECT ...;
SHOW SESSION STATUS LIKE 'Handler%';

英文:

The "Rows" in EXPLAIN are estimates based on statistics, and usually without taking into account LIMIT value. Don't trust them very far.

After the fact, the slowlog will provide exact "Rows_examined" and "Rows_returned".

Here's a another way (also by actually running the query):

FLUSH STATUS;
SELECT ...;
SHOW SESSION STATUS LIKE &#39;Handler%&#39;;

通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。