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

测试了两个查询：

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

2. 查询 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\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，远高于全表扫描。

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直方图的应用。

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

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 ）好友，待社区助手拉您进群。