一、什么是索引选择性
咱们先打个比方。假设你在一家图书馆找书，如果每本书的书脊上都贴着完全相同的标签，那这个标签对你找书就毫无帮助——这就是索引选择性差的情况。反过来，如果每本书都有独一无二的ISBN号，通过这个号能精准定位到某一本书——这就是高选择性的索引。
在MySQL中，索引选择性指的是索引列中不重复值的数量（也叫基数）与表中总行数的比值。计算公式很简单：
-- 计算user表中name列的索引选择性
SELECT
COUNT(DISTINCT name) / COUNT(*) AS selectivity
FROM user;
如果结果是1，说明该列每个值都唯一（比如主键）；如果接近0，说明大量重复值存在（比如性别字段）。选择性越高，索引效率越好，因为数据库能更快过滤掉无关数据。
二、为什么要评估索引选择性
你可能会问："我给所有常用查询字段都建索引不就行了？" 但索引不是免费的午餐，它会带来两个代价：
- 存储开销：每个索引都是一棵B+树，占用磁盘空间
- 维护成本：每次INSERT/UPDATE/DELETE都要同步更新索引
举个实际案例。我们有个电商订单表，结构如下：
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
user_id INT, -- 用户ID
status TINYINT, -- 订单状态(1待支付 2已支付...)
create_time DATETIME, -- 创建时间
INDEX idx_status (status)
);
这个idx_status索引有问题吗？假设表中有100万订单，但status只有5种状态：
-- 查看状态分布
SELECT status, COUNT(*)
FROM orders
GROUP BY status;
结果显示：每个状态都有约20万行数据。这时候用WHERE status=2查询，索引其实帮不上大忙——MySQL发现要扫描20%的数据，还不如全表扫描快！这就是典型的低选择性索引陷阱。
三、如何正确评估索引有效性
3.1 使用EXPLAIN分析
最直接的方式是用EXPLAIN查看执行计划：
EXPLAIN
SELECT * FROM orders
WHERE status = 2 AND create_time > '2023-01-01';
重点关注两个字段：
type：如果是index或range说明用了索引rows：预估扫描行数，越小越好
3.2 计算索引区分度
通过系统表查看索引统计信息：
-- 查看orders表的所有索引信息
SELECT
index_name,
stat_value AS '基数',
(SELECT COUNT(*) FROM orders) AS '总行数',
ROUND(stat_value/(SELECT COUNT(*) FROM orders),4) AS '选择性'
FROM mysql.innodb_index_stats
WHERE table_name = 'orders' AND stat_name = 'n_diff_pfx1';
3.3 组合索引策略
对于低选择性列，可以结合高选择性列创建组合索引：
-- 改进方案：创建(status, create_time)组合索引
ALTER TABLE orders ADD INDEX idx_status_time(status, create_time);
这时候查询WHERE status=2 AND create_time > '2023-01-01'会先通过status快速定位范围，再用create_time二次过滤，效率提升明显。
四、实战中的注意事项
4.1 避免过度索引
曾经遇到一个表有20个索引，导致写入性能下降50%。建议：
- 单表索引不超过5个
- 组合索引字段不超过3个
4.2 警惕隐式转换
-- user_id是INT类型，但用字符串查询会导致索引失效
SELECT * FROM orders WHERE user_id = '10086';
4.3 定期维护索引
使用ANALYZE TABLE更新统计信息：
-- 优化表统计信息
ANALYZE TABLE orders;
对于碎片化严重的索引，可以用OPTIMIZE TABLE重建：
-- 重建表及索引
OPTIMIZE TABLE orders;
五、不同场景下的最佳实践
5.1 高并发查询场景
在用户中心的查询中，我们这样优化：
-- 原始查询（慢）
SELECT * FROM users WHERE nickname LIKE '%张%';
-- 优化方案：增加全文索引
ALTER TABLE users ADD FULLTEXT INDEX ft_nickname(nickname);
-- 使用MATCH语法查询
SELECT * FROM users WHERE MATCH(nickname) AGAINST('张');
5.2 时间范围查询
日志表常见优化方式：
-- 按月分表+日期索引
CREATE TABLE access_log_202301 (
id BIGINT,
access_time DATETIME,
INDEX idx_time (access_time)
) PARTITION BY RANGE (TO_DAYS(access_time));
5.3 枚举类型处理
对于像订单状态这类低选择性枚举值，建议：
-- 热数据单独处理
CREATE TABLE hot_orders (
id BIGINT PRIMARY KEY,
status TINYINT,
INDEX idx_status (status)
) ENGINE=Memory;
六、总结
索引选择性的评估就像给数据库做"体检"：
- 听诊器是
EXPLAIN和索引统计信息 - 治疗方案包括组合索引、覆盖索引、分区表等
- 康复护理需要定期
ANALYZE TABLE维护
记住一个黄金法则：选择性小于30%的列通常不适合单独建索引。好的索引设计能让查询速度提升百倍，而错误的索引反而会成为性能杀手。希望这些实战经验能帮你避开我踩过的坑！
评论