全部标签
写点什么
开源架构OpenHarmony算法元宇宙学习方法Web3.0高效工作数据库Python音视频前端AI大数据团队管理程序员运维深度思考低代码redisgolang微服务架构flutter 查看更多

GreatSQL 为何选择全表扫描而不选索引

作者：GreatSQL
  • 2025-03-21
    福建

  • 本文字数：6080 字

    阅读完需：约 20 分钟

GreatSQL 为何选择全表扫描而不选索引

1. 问题背景

在生产环境中，发现某些查询即使有索引，也没有使用索引，反而选择了全表扫描。这种现象的根本原因在于优化器评估索引扫描的成本时，认为使用索引的成本高于全表扫描。

2. 场景复现

2.1 环境信息

  • 机器 IP：192.168.137.120

  • GreatSQL 版本：8.0.32-26

2.2 环境准备

通过脚本创建了一个包含 100 万条数据的表，并在 age 列上创建了索引 idx_age，如下所示：


#!/bin/bash
# 数据库配置db_host="192.168.137.120"db_user="root"db_pass="xxxx"db_name="test"db_port=3306table_name="t1"
my_conn="greatsql -h$db_host -P$db_port -u$db_user -p$db_pass -D$db_name"
# 创建大表create_table() {    $my_conn -e "    CREATE TABLE IF NOT EXISTS ${table_name} (        id INT primary key,        name VARCHAR(255),        age INT,        email VARCHAR(255),        address VARCHAR(255),        created_at DATETIME,        updated_at DATETIME,                key idx_age(age)    );"}
# 批量插入数据bulk_insert() {    values=""    for ((i=1; i<=1000000; i++)); do        values+="($i, 'name $i', $((RANDOM % 100)), 'email$i@example.com', 'address $i', NOW(), NOW()),"        if (( i % 1000 == 0 )); then            values=${values%,}  # 去掉最后的逗号            # 执行插入            $my_conn -e "INSERT INTO ${table_name} (id, name, age, email, address, created_at, updated_at) VALUES $values;"            values=""  # 重置values        fi    done}
# 主执行函数main() {    echo "开始创建表..."    create_table    echo "表创建完成！"
    echo "开始批量插入数据..."    start_time=$(date +%s)    bulk_insert    end_time=$(date +%s)
    echo "插入数据完成！"    echo "耗时：$((end_time - start_time)) 秒"}
# 执行主函数main
复制代码

2.3 SQL 查询

测试了两个查询：


  1. 查询 age > 80 的记录：


greatsql> EXPLAIN SELECT * FROM t1 WHERE age > 80;+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+|  1 | SIMPLE      | t1    | NULL       | ALL  | idx_age       | NULL | NULL    | NULL | 994098 |    37.05 | Using where |+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
复制代码


  1. 查询 age < 3 的记录：


greatsql> EXPLAIN SELECT * FROM t1 WHERE age < 3;+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------+| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra                 |+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------+|  1 | SIMPLE      | t1    | NULL       | range | idx_age       | idx_age | 5       | NULL | 55344 |   100.00 | Using index condition |+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------+
复制代码


对于第一个查询，优化器选择了全表扫描（type = ALL），而对于第二个查询，则使用了索引扫描（type = range）。这是因为优化器认为，在 age > 80 的查询中，索引扫描的成本较高，而全表扫描相对较低。

3. 优化器分析

启用 optimizer_trace 查看优化器的执行细节：


greatsql> SET optimizer_trace="enabled=on";greatsql> SELECT * FROM t1 WHERE age > 80;+-----+----------+------+----------------------+-------------+---------------------+---------------------+| id  | name     | age  | email                | address     | created_at          | updated_at          |+-----+----------+------+----------------------+-------------+---------------------+---------------------+|  48 | name 48  |   81 | email48@example.com  | address 48  | 2024-12-26 11:25:00 | 2024-12-26 11:25:00 ||  87 | name 87  |   81 | email87@example.com  | address 87  | 2024-12-26 11:25:00 | 2024-12-26 11:25:00 || 130 | name 130 |   81 | email130@example.com | address 130 | 2024-12-26 11:25:00 | 2024-12-26 11:25:00 || 201 | name 201 |   81 | email201@example.com | address 201 | 2024-12-26 11:25:00 | 2024-12-26 11:25:00 || 232 | name 232 |   81 | email232@example.com | address 232 | 2024-12-26 11:25:00 | 2024-12-26 11:25:00 |......| 999998 | name 999998 |   99 | email999998@example.com | address 999998 | 2024-12-26 11:27:22 | 2024-12-26 11:27:22 |+--------+-------------+------+-------------------------+----------------+---------------------+---------------------+greatsql> SELECT trace FROM information_schema.optimizer_trace\Gtrace: {  "steps": [    {      "join_preparation": {        "select#": 1,        "steps": [          {            "expanded_query": "/* select#1 */ select `t1`.`id` AS `id`,`t1`.`name` AS `name`,`t1`.`age` AS `age`,`t1`.`email` AS `email`,`t1`.`address` AS `address`,`t1`.`created_at` AS `created_at`,`t1`.`updated_at` AS `updated_at` from `t1` where (`t1`.`age` > 80)"          }        ]      }    },    {      "join_optimization": {        "select#": 1,        "steps": [          {            "condition_processing": {              "condition": "WHERE",              "original_condition": "(`t1`.`age` > 80)",              "steps": [                {                  "transformation": "equality_propagation", --传播等式                  "resulting_condition": "(`t1`.`age` > 80)"                },                {                  "transformation": "constant_propagation", --传播常量                  "resulting_condition": "(`t1`.`age` > 80)"                },                {                  "transformation": "trivial_condition_removal", --移除无关条件                  "resulting_condition": "(`t1`.`age` > 80)"                }              ]            }          },          {            "substitute_generated_columns": {            }          },          {            "table_dependencies": [ --表依赖              {                "table": "`t1`",                "row_may_be_null": false,                "map_bit": 0,                "depends_on_map_bits": [                ]              }            ]          },          {            "ref_optimizer_key_uses": [            ]          },          {            "rows_estimation": [ --行数估算              {                "table": "`t1`",                "range_analysis": {                  "table_scan": { --全表扫描                    "rows": 994078,  --  994078 行需要被扫描                    "cost": 106040  -- 106040 是执行全表扫描的估算成本                  },                  "potential_range_indexes": [ --潜在范围索引                    {                      "index": "PRIMARY",                      "usable": false,  -- 主键索引不适用于此次查询                      "cause": "not_applicable"                      },                    {                      "index": "idx_age",                      "usable": true,  -- idx_age索引可用于此次查询                      "key_parts": [  -- 索引基于age，id创建                        "age",                        "id"                      ]                    }                  ],                  "setup_range_conditions": [                  ],                  "group_index_range": {                    "chosen": false,                    "cause": "not_group_by_or_distinct"                  },                  "skip_scan_range": {                    "potential_skip_scan_indexes": [                      {                        "index": "idx_age",                        "usable": false,                        "cause": "query_references_nonkey_column"                      }                    ]                  },                  "analyzing_range_alternatives": {                    "range_scan_alternatives": [                      {                        "index": "idx_age",                        "ranges": [                          "80 < age"                        ],                        "index_dives_for_eq_ranges": true,                        "rowid_ordered": false,                        "using_mrr": false,                        "index_only": false,                        "in_memory": 0,                        "rows": 379410,  -- 使用idx_age索引估算扫描行数                        "cost": 417303,  -- 使用idx_age索引估算成本                        "chosen": false,  -- 未选择该索引                        "cause": "cost"                      }                    ],                    "analyzing_roworder_intersect": {                      "usable": false,                      "cause": "too_few_roworder_scans"                    }                  }                }              }            ]          },          {            "considered_execution_plans": [ --考虑的执行计划              {                "plan_prefix": [                ],                "table": "`t1`",                "best_access_path": {  -- 最优访问方式                  "considered_access_paths": [                    {                      "rows_to_scan": 994078,                      "access_type": "scan",                        "resulting_rows": 994078,                      "cost": 106038,                      "chosen": true                      }                  ]                },                "condition_filtering_pct": 100,                "rows_for_plan": 994078,                  "cost_for_plan": 106038,                  "chosen": true  -- 优化器最终选择了全表扫描，行数为994708，成为106038              }            ]          },          {            "attaching_conditions_to_tables": { --将条件附加到表              "original_condition": "(`t1`.`age` > 80)",              "attached_conditions_computation": [              ],              "attached_conditions_summary": [                {                  "table": "`t1`",                  "attached": "(`t1`.`age` > 80)"                }              ]            }          },          {            "finalizing_table_conditions": [ --最终确定表条件              {                "table": "`t1`",                "original_table_condition": "(`t1`.`age` > 80)",                "final_table_condition   ": "(`t1`.`age` > 80)" --最终的表条件              }            ]          },          {            "refine_plan": [ --优化计划细节              {                "table": "`t1`"              }            ]          }        ]      }    },    {      "join_execution": {        "select#": 1,        "steps": [        ]      }    }  ]}
复制代码


从优化器的执行计划中可以看到：


  • 全表扫描：优化器选择了全表扫描的估算成本为 106038。

  • 索引扫描：虽然索引 idx_age 在 age 列上可以被使用，但由于它并不包含查询中所有列（如 id, name, email 等），因此需要回表操作，导致索引扫描的估算成本为 417303，远高于全表扫描。

4. 问题分析

优化器选择使用全表扫描而不是索引扫描的原因主要是因为：


  • 回表开销：idx_age 索引仅包含 age 和 id 列，而查询需要 age, id, name, email, address, created_at, updated_at 等字段。因此，使用索引后需要额外的回表操作，这会增加查询的成本。特别是在数据量较大时，回表次数增多，导致整体性能下降。

  • 估算成本：在某些情况下，优化器评估使用索引的成本比全表扫描高。例如，如果查询涉及的列较多，而索引并不覆盖这些列，回表的代价可能超过直接扫描整张表的代价。优化器会倾向于选择代价较低的执行计划，即全表扫描。

5. 解决方案与优化建议

  • 查询优化：可以通过调整查询条件，减少涉及的列数，或者使用 EXPLAIN 分析不同查询条件的执行计划，选择最优的查询方式。

  • 分析索引选择性：确保索引列的选择性较高，即索引能有效减少扫描的行数。如果某个列的选择性较低（如范围条件 age > 80），全表扫描可能仍然是最优选择。

  • 调整配置参数：根据表的数据分布和查询特征，可能需要调整 MySQL 的优化器相关参数（如 optimizer_search_depth 或 optimizer_switch），以优化查询执行计划的选择。

  • 在本案例中，条件 age > 80 读取扫描的数据量太大了，在真实业务中只有很少数情况需要读取这么大量数据，因此建议加上 LIMIT N 限定读取行数（N 通常不高于 1000）。

  • 在部分其他场景中，有时可以尝试通过使用直方图来优化查询效率，可以根据实际情况选择。关于直方图可以参考文章：深入聊聊MySQL直方图的应用。


通过这些方法，可以有效减少全表扫描的发生，提高查询性能。


划线
评论
复制
发布于: 刚刚阅读数: 2

版权声明: 本文为 InfoQ 作者【GreatSQL】的原创文章。

原文链接:【http://xie.infoq.cn/article/09a403b536a0f47ab7b1856fc】。文章转载请联系作者。

用户头像

GreatSQL

关注

GreatSQL社区 2023-01-31 加入

GreatSQL是由万里数据库维护的MySQL分支，专注于提升MGR可靠性及性能，支持InnoDB并行查询特性，是适用于金融级应用的MySQL分支版本。 社区：https://greatsql.cn/ Gitee: https://gitee.com/GreatSQL/GreatSQL

评论

发布
暂无评论
Copyright © 2025, Geekbang Technology Ltd. All rights reserved. 极客邦控股（北京）有限公司 | 京 ICP 备 16027448 号 - 5京公网安备京公网安备 11010502039052号 | 产品资质
GreatSQL 为何选择全表扫描而不选索引_GreatSQL_InfoQ写作社区