MySQL query_cost与实际执行时间的关联疑问及SQL优化咨询

核心疑问 #

• query_cost与实际执行是否无必然关联？
• 排除网络、机器性能等外部因素，应该用什么量化SQL优化效果？
• 案例中SQL1和SQL2的执行时间差异是否源于聚簇索引使用方式？若正确，为何query_cost未体现？

测试环境与案例信息 #

测试表包含1000000条数据，DDL如下：

CREATE TABLE `testdo` (
`id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`value` int NOT NULL,
`version` int NOT NULL ,
`created_at` datetime DEFAULT NULL,
`created_by` varchar(50) DEFAULT NULL,
`is_deleted` bit(1) DEFAULT NULL,
`modified_at` datetime DEFAULT NULL,
`modified_by` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

SQL 1（耗时:465ms query_cost:100873.90） #

查询语句：

select * from testdo order by id limit 810000,30;

执行explain format=json结果：

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "100873.90"
    },
    "ordering_operation": {
      "using_filesort": false,
      "table": {
        "table_name": "testdo",
        "access_type": "index",
        "key": "PRIMARY",
        "used_key_parts": [
          "id"
        ],
        "key_length": "8",
        "rows_examined_per_scan": 810030,
        "rows_produced_per_join": 994719,
        "filtered": "100.00",
        "cost_info": {
          "read_cost": "1402.00",
          "eval_cost": "99471.90",
          "prefix_cost": "100873.90",
          "data_read_per_join": "599M"
        },
        "used_columns": [
          "id",
          "name",
          "value",
          "version",
          "created_at",
          "created_by",
          "is_deleted",
          "modified_at",
          "modified_by"
        ]
      }
    }
  }
}

explain ANALYZE结果：

-> Limit/Offset: 30/810000 row(s)  (cost=69914.55 rows=30) (actual time=465.053..465.098 rows=30 loops=1)
    -> Index scan on testdo using PRIMARY  (cost=69914.55 rows=810030) (actual time=0.082..444.687 rows=810030 loops=1)

SQL 2（耗时:187ms query_cost:471480.20） #

查询语句：

select t.* from (select id from testdo limit 810000,30)a,testdo t where a.id = t.id;

执行explain format=json结果：

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "471480.20"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "a",
          "access_type": "ALL",
          "rows_examined_per_scan": 810030,
          "rows_produced_per_join": 810030,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "10127.88",
            "eval_cost": "81003.00",
            "prefix_cost": "91130.88",
            "data_read_per_join": "12M"
          },
          "used_columns": [
            "id"
          ],
          "materialized_from_subquery": {
            "using_temporary_table": true,
            "dependent": false,
            "cacheable": true,
            "query_block": {
              "select_id": 2,
              "cost_info": {
                "query_cost": "98323.63"
              },
              "table": {
                "table_name": "testdo",
                "access_type": "index",
                "key": "PRIMARY",
                "used_key_parts": [
                  "id"
                ],
                "key_length": "8",
                "rows_examined_per_scan": 962512,
                "rows_produced_per_join": 962512,
                "filtered": "100.00",
                "using_index": true,
                "cost_info": {
                  "read_cost": "2072.43",
                  "eval_cost": "96251.20",
                  "prefix_cost": "98323.63",
                  "data_read_per_join": "580M"
                },
                "used_columns": [
                  "id"
                ]
              }
            }
          }
        }
      },
      {
        "table": {
          "table_name": "t",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "id"
          ],
          "key_length": "8",
          "ref": [
            "a.id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 810030,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "299346.33",
            "eval_cost": "81003.00",
            "prefix_cost": "471480.20",
            "data_read_per_join": "488M"
          },
          "used_columns": [
            "id",
            "name",
            "value",
            "version",
            "created_at",
            "created_by",
            "is_deleted",
            "modified_at",
            "modified_by"
          ]
        }
      }
    ]
  }
}

explain ANALYZE结果：

-> Limit: 200 row(s)  (cost=397678.84 rows=30) (actual time=187.429..187.622 rows=30 loops=1)
    -> Nested loop inner join  (cost=397678.84 rows=30) (actual time=187.428..187.621 rows=30 loops=1)
        -> Table scan on a  (cost=98326.73..98329.51 rows=30) (actual time=187.410..187.413 rows=30 loops=1)
            -> Materialize  (cost=98326.63..98326.63 rows=30) (actual time=187.409..187.409 rows=30 loops=1)
                -> Limit/Offset: 30/810000 row(s)  (cost=98323.63 rows=30) (actual time=187.376..187.392 rows=30 loops=1)
                    -> Covering index scan on testdo using PRIMARY  (cost=98323.63 rows=962512) (actual time=0.034..167.223 rows=810030 loops=1)
        -> Single-row index lookup on t using PRIMARY (id=a.id)  (cost=0.37 rows=1) (actual time=0.007..0.007 rows=1 loops=30)

问题解答 #

1. query_cost与实际执行的关联 #

query_cost是优化器基于统计信息估算的成本，和实际执行时间没有绝对的必然关联。它的计算基于预设的IO、CPU成本系数，以及统计的行数、数据量等，但实际执行中，缓存命中率、数据页加载方式、执行阶段的优化（比如覆盖索引的实际IO节省）都可能让实际耗时和估算成本偏差很大。

2. 量化SQL优化效果的指标 #

排除外部因素后，优先参考以下指标：

• 实际执行时间：多次执行取平均值，减少偶然波动影响
• 扫描行数（rows examined）：explain analyze中的实际扫描行数，直接反映数据访问量
• IO相关统计：通过SHOW SESSION STATUS LIKE 'Innodb_rows_read'查看实际读取行数，或利用Performance Schema获取更细粒度的IO统计
• 临时表/文件排序：explain结果中的using_filesort、using_temporary_table标记，这些是明确的性能损耗点
• CPU使用率：结合操作系统工具（如top、vmstat）查看SQL执行期间的CPU占用情况

3. 案例中执行时间差异的原因及query_cost偏差解释 #

你的猜测完全正确：

• SQL1直接扫描聚簇索引并返回所有列，InnoDB的聚簇索引叶子节点存储整行数据，因此扫描到第810000条时，每一行都需要加载完整的数据页到内存——即使最终只取30行，前面的810000行也需要读取完整的行数据，IO开销极大。
• SQL2的子查询是覆盖索引扫描（仅读取主键id，主键索引本身就是覆盖索引），不需要加载叶子节点的整行数据，仅需读取索引的非叶子节点和叶子节点的id部分，数据量远小于整行扫描，IO开销显著降低；之后通过id回表取30行整数据，仅需30次单页查找，总IO远小于SQL1。

query_cost未体现该差异的原因：
优化器估算成本时，对聚簇索引整行扫描和覆盖索引扫描的成本系数区分不够精准，尤其是在大偏移量的limit场景下，它会基于扫描行数乘以预设成本值，未考虑覆盖索引实际读取的数据量远小于整行扫描的情况。另外，子查询的物化操作成本被高估——实际中物化的临时表仅存储30个id，开销极小，但优化器按全量扫描行数估算成本，导致整体query_cost偏高。

推荐技术文档 #

• MySQL官方文档：《Optimizer Cost Model》（核心讲解成本估算逻辑）
• MySQL官方文档：《InnoDB Cluster Index》（深入理解聚簇索引存储结构）
• 《高性能MySQL》（书中详细讲解执行计划分析与索引优化的实际场景）

内容的提问来源于stack exchange，提问作者hhhxxx