GreatSQL 为何选择全表扫描而不选索引
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
[*]查询 age < 3 的记录：
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 |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+对于第一个查询，优化器选择了全表扫描（type = ALL），而对于第二个查询，则使用了索引扫描（type = range）。这是因为优化器认为，在 age > 80 的查询中，索引扫描的成本较高，而全表扫描相对较低。
3. 优化器分析
启用 optimizer_trace 查看优化器的执行细节：
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 |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------+从优化器的执行筹划中可以看到：
[*]全表扫描：优化器选择了全表扫描的估算成本为 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
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]