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 查询
测试了两个查询：
- 查询
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 的查询中，索引扫描的成本较高，而全表扫描相对较低。
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直方图的应用。
通过这些方法，可以有效减少全表扫描的发生，提高查询性能。
431
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
