MySQL SELECT中LIMIT时EXPLAIN估算ROWS不准确的替代方法

最新推荐文章于 2024-11-27 11:37:10 发布

原创最新推荐文章于 2024-11-27 11:37:10 发布 · 1w 阅读

CC 4.0 BY-SA版权

本文通过创建测试表并插入数据，详细介绍了如何使用MySQL的EXPLAIN命令来估算SQL执行过程中的性能耗时。通过实例展示了如何解读EXPLAIN输出的rows值，并对比了实际扫描行数，帮助读者更好地理解MySQL的执行计划。

在MySQL性能调试中，常常使用EXPLAIN解释MySQL执行计划，从而用来估算性能耗时。其中，rows用来表示在SQL执行过程中会被扫描的行数，该数值越大，意味着需要扫描的行数，相应的耗时更长。但是需要注意的是EXPLAIN中输出的rows只是一个估算值，不能完全对其百分之百相信，如EXPLAIN中对LIMITS的支持就比较有限。可以参考文章《MySQL EXPLAIN limits and errors》。

1. 创建一个测试用表TEMP作为实现

CREATE TABLE TEMP (

TYPE varchar(64) NOT NULL,

CREATED_AT timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)

);

2. 创建相应Index

CREATE INDEX IDX_TEMP_type_createdAt on TEMP(TYPE, CREATED_AT);

3. 随机插入数据

随机插入10000条数据，其中TYPE字段随机插入['A', 'B', 'C', 'D', 'E']五个值中的任意一个

4. 使用EXPLAIN进行rows解释

EXPLAIN SELECT * FROM TEMP WHERE TYPE = 'A' ORDER BY CREATED_AT DESC LIMIT 10;

结果：

select_type typekey key_len ref rowsfiltered Extra
SIMPLE ref IDX_TEMP_type_createdAt258 const2017 100Using where; Using index

可以看到rows是2017，有2017行被扫描

5. 使用SHOW STATUS尝试解释

a)首先执行SQL，注意去掉EXPLAIN

SELECT * FROM TEMP WHERE TYPE = 'A' ORDER BY CREATED_AT DESC LIMIT 10;

b)执行SHOW STATUS查看当前状态

SHOW SESSION STATUS LIKE "Handler%"

结果：

Variable_nameValue
Handler_commit 1
Handler_delete 0
Handler_discover 0
Handler_external_lock 2
Handler_mrr_init 0
Handler_prepare 0
Handler_read_first 1
Handler_read_key 2
Handler_read_last 0
Handler_read_next 0
Handler_read_prev9
Handler_read_rnd 0
Handler_read_rnd_next 3
Handler_rollback 0
Handler_savepoint 0
Handler_savepoint_rollback 0
Handler_update 0
Handler_write 0

可以看到还剩9条数据没有读，这才是实际准确的值