数据库索引选择性分析与慢查询日志深度解读

数据库索引选择性是衡量一个索引能否高效过滤数据的核心指标，计算公式很简单：Selectivity = 不同值的数量（Cardinality）/ 总行数（Rows）。当这个值接近1时，说明索引区分度高，查询效率好；当它接近0时，索引几乎没用，优化器可能直接放弃走索引而选择全表扫描。慢查询日志则是定位性能瓶颈的第一手证据，通过分析其中的执行时间、扫描行数、返回行数、锁等待等关键字段，你能精准找到哪些SQL在拖垮整个系统。这两件事放在一起分析，才是真正解决数据库性能问题的完整闭环。

很多DBA和开发人员只知道建索引，但从不评估索引质量。索引选择性（Index Selectivity）的本质是告诉你：这个索引列上有多少"不重复"的值。举个例子，一张用户表有100万行，其中gender字段只有"男"和"女"两个值，选择性就是2/1000000=0.000002，几乎为零，这种索引在查询时基本无效。而email字段假设有95万个不重复值，选择性就是0.95，非常高，适合建索引。

在MySQL中，你可以用以下SQL直接查看某个列的选择性：

SELECT 
    COUNT(DISTINCT column_name) / COUNT(*) AS selectivity,
    COUNT(DISTINCT column_name) AS cardinality,
    COUNT(*) AS total_rows
FROM table_name;

更直接的方式是查看索引统计信息。在MySQL 8.0中，执行SHOW INDEX FROM table_name，关注Cardinality列的值。如果Cardinality接近表的总行数，说明这个索引列区分度好；如果Cardinality远小于总行数，说明大量重复值，索引效率低。需要注意的是，这个统计值是估算值，不是实时精确值，但足够做判断。

实际工作中有个经验法则：选择性低于0.1的列，单独建索引意义不大；0.1到0.3之间可以考虑，但要结合查询模式；0.3以上通常值得建索引。但这不是绝对的，还要看查询是否真的用到了这个列的过滤条件。

慢查询日志（Slow Query Log）是数据库性能诊断的金矿。开启方式在MySQL中很简单：设置slow_query_log=ON，long_query_time=1（单位秒，表示超过1秒的查询都记录）。但光开启还不够，关键是你得会读里面的内容。

一条典型的慢查询日志记录包含这些核心信息：查询时间（Query_time）、锁定时间（Lock_time）、扫描行数（Rows_examined）、返回行数（Rows_sent）、使用的索引（可能没有）。其中最关键的对比是Rows_examined和Rows_sent。如果扫描了10万行但只返回了10行，说明索引没起作用或者索引选择性太差，大量无效扫描浪费了资源。理想状态是Rows_examined和Rows_sent接近，说明索引精准命中。

你可以用pt-query-digest工具（Percona Toolkit的一部分）对慢查询日志做聚合分析，它会自动把相似的SQL归为一类，按执行次数、总耗时、平均耗时排序，让你一眼看到哪类SQL最"毒"。命令如下：

pt-query-digest /var/log/mysql/slow.log --order-by=query_time:sum --limit=20

这个命令会输出耗时最长的前20类SQL，每类包含样本语句、执行次数、总耗时、平均耗时、扫描行数等。这比你一条条翻日志高效得多。

找到慢查询之后，下一步就是分析为什么慢。最常见的原因就是索引选择性不够或者根本没走索引。具体分析步骤如下：第一步，用EXPLAIN查看执行计划，重点看type列（是否为ALL全表扫描）、key列（是否用到了索引）、rows列（预估扫描行数）。第二步，结合慢查询日志中的Rows_examined，确认实际扫描量是否和EXPLAIN的预估一致。第三步，如果发现没走索引或者走了低效索引，就要回头评估相关列的选择性。

举个实际场景：一个订单表有500万行，按create_time建了索引，但查询经常是SELECT * FROM orders WHERE status=1 AND create_time > '2024-01-01'。status字段只有0、1、2、3四个值，选择性极低，光靠create_time索引也不够精准。这时候正确的做法是建联合索引(status, create_time)，把高选择性的列放后面，让索引先过滤status再按时间范围裁剪。执行计划中type会从ALL变成range，扫描行数大幅下降。

另一个常见陷阱是"隐式类型转换"导致索引失效。比如phone字段是VARCHAR类型，但查询写成WHERE phone = 13800138000（数字），MySQL会做隐式转换，导致索引无法使用。这种问题在慢查询日志里看不出来，必须结合EXPLAIN和实际SQL语句逐字检查。

当你发现某个列的选择性太低，不要急着删索引，先想想有没有优化空间。第一种策略是使用前缀索引。对于长字符串列（比如URL、地址），可以只索引前N个字符。MySQL支持这样建索引：

CREATE INDEX idx_url ON articles(url(20));

前缀长度的选择有讲究，一般选使得选择性接近完整列的长度。可以用这个公式估算：SELECT COUNT(DISTINCT LEFT(url, 7)) / COUNT(*) FROM articles，逐步增加长度直到选择性不再明显提升。

第二种策略是建立联合索引（复合索引）。单独看每个列选择性都不高，但组合起来可能很高。比如用户表中city和age单独选择性都一般，但(city, age)组合后几乎每行都不同，联合索引就非常有效。注意联合索引要遵循最左前缀原则，查询条件必须从最左列开始匹配。

第三种策略是使用覆盖索引（Covering Index）。如果查询只需要索引中包含的列，数据库就不需要回表查数据行，性能提升显著。比如索引(status, create_time, user_id)可以覆盖SELECT user_id FROM orders WHERE status=1 AND create_time > '2024-01-01'这个查询，EXPLAIN中会显示Using index。

第四种策略是考虑分区表。当数据量达到千万级以上，即使索引选择性好，单次查询扫描量仍然可能很大。按时间范围做分区，配合索引，可以让查询只扫描相关分区，物理上减少I/O。

慢查询分析不是一次性工作，而是需要持续监控的常态化任务。建议搭建一套自动化体系：每天定时用pt-query-digest分析前一天的慢查询日志，生成报告；设置阈值告警，比如单条查询超过5秒、或者某类SQL的日均执行次数突增200%时触发通知；定期（每月或每季度）回顾索引使用情况，用sys schema中的schema_unused_indexes视图找出从未被使用的冗余索引并清理。

在MySQL 8.0中，performance_schema提供了更细粒度的监控。可以查询events_statements_summary_by_digest表获取聚合后的SQL统计：

SELECT 
    DIGEST_TEXT,
    COUNT_STAR AS exec_count,
    AVG_TIMER_WAIT/1000000000 AS avg_ms,
    SUM_ROWS_EXAMINED AS total_examined,
    SUM_ROWS_SENT AS total_sent
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

这比慢查询日志更全面，因为它包含了所有SQL（不只是慢的），能帮你发现那些执行频率极高但单次不慢、累计却很耗资源的"隐形杀手"。

很多人有个误区：索引越多越好。实际上每个索引都会增加写入开销（INSERT/UPDATE/DELETE都要维护索引），索引太多反而拖慢写入性能。经验上，一张表的索引数量控制在5个以内比较合理，核心查询覆盖到就够了。

另一个误区是只看查询速度不看资源消耗。有些SQL虽然快，但扫描了大量行（Rows_examined很高），在高并发场景下会迅速耗尽IO资源。真正的优化目标不是让单条查询快，而是让整体系统在高负载下稳定。

我的一个核心观点是：索引选择性分析和慢查询日志解读必须结合业务场景。技术指标再好，如果不理解业务查询模式，建出来的索引可能完全不对路。比如一个报表系统的查询模式和一个OLTP系统完全不同，索引策略也应该截然不同。先梳理TOP 20高频查询，再针对性地设计索引，比盲目建索引有效十倍。

最后提醒一点，MySQL的统计信息可能不准确，尤其是在数据频繁变更的表上。如果你发现EXPLAIN的rows预估和实际差距很大，可以手动执行ANALYZE TABLE table_name来更新统计信息，让优化器做出更准确的判断。