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

2025-03-21 153

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

问题背景

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

场景复现

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=3306
table_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 查询

测试了两个查询：

查询 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 |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+

查询 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 的查询中，索引扫描的成本较高，而全表扫描相对较低。

优化器分析

启用 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\G
trace: {
  "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，远高于全表扫描。

问题分析

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

回表开销 ：idx_age 索引仅包含 age 和 id 列，而查询需要 age, id, name, email, address, created_at, updated_at 等字段。因此，使用索引后需要额外的回表操作，这会增加查询的成本。特别是在数据量较大时，回表次数增多，导致整体性能下降。
估算成本：在某些情况下，优化器评估使用索引的成本比全表扫描高。例如，如果查询涉及的列较多，而索引并不覆盖这些列，回表的代价可能超过直接扫描整张表的代价。优化器会倾向于选择代价较低的执行计划，即全表扫描。

解决方案与优化建议

查询优化 ：可以通过调整查询条件，减少涉及的列数，或者使用 EXPLAIN 分析不同查询条件的执行计划，选择最优的查询方式。
分析索引选择性 ：确保索引列的选择性较高，即索引能有效减少扫描的行数。如果某个列的选择性较低（如范围条件 age > 80），全表扫描可能仍然是最优选择。
调整配置参数 ：根据表的数据分布和查询特征，可能需要调整 MySQL 的优化器相关参数（如 optimizer_search_depth 或 optimizer_switch），以优化查询执行计划的选择。
在本案例中，条件 age > 80 读取扫描的数据量太大了，在真实业务中只有很少数情况需要读取这么大量数据，因此建议加上 LIMIT N 限定读取行数（N通常不高于1000）。
在部分其他场景中，有时可以尝试通过使用直方图来优化查询效率，可以根据实际情况选择。关于直方图可以参考文章：深入聊聊MySQL直方图的应用。

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

Enjoy GreatSQL :)

关于 GreatSQL

GreatSQL是适用于金融级应用的国内自主开源数据库，具备高性能、高可靠、高易用性、高安全等多个核心特性，可以作为MySQL或Percona Server的可选替换，用于线上生产环境，且完全免费并兼容MySQL或Percona Server。

相关链接： GreatSQL社区 Gitee GitHub Bilibili

GreatSQL社区：

社区有奖建议反馈： https://greatsql.cn/thread-54-1-1.html

社区博客有奖征稿详情： https://greatsql.cn/thread-100-1-1.html

（对文章有疑问或者有独到见解都可以去社区官网提出或分享哦~）

技术交流群：

微信&QQ群：

QQ群：533341697

微信群：添加GreatSQL社区助手（微信号：wanlidbc ）好友，待社区助手拉您进群。

微信关注我们

原文链接：https://my.oschina.net/GreatSQL/blog/17961353

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

语音智能体：大模型如何“接管”人类对话？

在人力成本飙升、企业降本增效需求迫切的当下，传统呼叫中心正面临一场静默革命。云蝠智能创始人兼 CEO 魏佳星向 OSCHINA 透露，通过大模型构建的语音智能体（Voice Agent），已能将单次通话成本从5元压缩至0.5元——这不仅是技术迭代的结果，更是一场涉及多模型协同、工程化落地与组织转型的深度变革。从药房用药追踪到电力集团调研，从银行不良资产处置到“拟人化”情感互动，这条赛道正以超乎想象的速度重构着企业与用户的对话方式。开发者们需要关注的是：在开源模型生态爆发之际，如何用工程化思维加速大模型能力转化为可复用的行业经验？而当 AI 意图理解率突破99%时，真正的战场又转向了哪些细节？ OSCHINA 采访了云蝠智能创始人\CEO 魏佳星，与他聊了聊语音智能体的应用与开发现状，以下是采访实录。 5元→0.5元：语音交互的“成本塌陷”与全流程重构我们主要做的是一种叫做语音智能体的业务，其核心目标是让诸如通义、千问或豆包这样的大型模型能够在电话或语音端完成有效的互动。呼入呼出、业务回访场景下，以往一通电话的综合人力成本是 5 元，大模型可以把这个成本压缩 10 倍。举个例子...

2025-03-20

293

编者按：为什么说 DeepSeekMoE 的"共享专家隔离"设计，既能保留通用知识又能减少冗余？传统 MoE 的专家真的"专精"吗？传统 MoE 专家易"崩溃"，DeepSeekMoE 如何通过"更细粒度的专家分割"让每个专家专注更小领域，解决负载不均衡问题？作者巧妙地用餐厅厨师的比喻，将抽象的技术概念形象化 ------ 是聘用一位熟悉多种菜系的厨师，还是聘用多位各有专长的厨师更明智？随后，文章深入剖析了 DeepSeekMoE 的两大创新：更细粒度的专家分割通过增加专家数量并降低单个专家的参数规模，促进了专家的专业化；共享专家隔离则通过预留部分专家处理通用知识，减少了专家间的知识冗余。实验结果表明，在相同计算成本下，DeepSeekMoE不仅性能更优，其专家的不可替代性也更强，知识冗余度更低。作者 | Shirley Li 编译 | 岳扬这是 DeepSeek-V3 系列的第二篇文章，本文将解析 DeepSeek[1,2,3] 模型的另一个关键架构创新：DeepSeekMoE[4]。「DeepSeek-V3 技术解析」专栏其他文章：「DeepSeek-V3 技术解析」：...

2025-03-21

317

资源下载

更多资源

Mario

马里奥是站在游戏界顶峰的超人气多面角色。马里奥靠吃蘑菇成长，特征是大鼻子、头戴帽子、身穿背带裤，还留着胡子。与他的双胞胎兄弟路易基一起，长年担任任天堂的招牌角色。

Nacos

Nacos /nɑ:kəʊs/ 是 Dynamic Naming and Configuration Service 的首字母简称，一个易于构建 AI Agent 应用的动态服务发现、配置管理和AI智能体管理平台。Nacos 致力于帮助您发现、配置和管理微服务及AI智能体应用。Nacos 提供了一组简单易用的特性集，帮助您快速实现动态服务发现、服务配置、服务元数据、流量管理。Nacos 帮助您更敏捷和容易地构建、交付和管理微服务平台。

Spring

Spring框架（Spring Framework）是由Rod Johnson于2002年提出的开源Java企业级应用框架，旨在通过使用JavaBean替代传统EJB实现方式降低企业级编程开发的复杂性。该框架基于简单性、可测试性和松耦合性设计理念，提供核心容器、应用上下文、数据访问集成等模块，支持整合Hibernate、Struts等第三方框架，其适用范围不仅限于服务器端开发，绝大多数Java应用均可从中受益。

Sublime Text

Sublime Text具有漂亮的用户界面和强大的功能，例如代码缩略图，Python的插件，代码段等。还可自定义键绑定，菜单和工具栏。Sublime Text 的主要功能包括：拼写检查，书签，完整的 Python API ， Goto 功能，即时项目切换，多选择，多窗口等等。Sublime Text 是一个跨平台的编辑器，同时支持Windows、Linux、Mac OS X等操作系统。