一、为什么需要评估未创建索引的 Cardinality在数据库优化中Cardinality基数是决定是否创建索引、以及如何排列联合索引列顺序的核心指标。它表示索引列中不重复值的数量。核心矛盾索引尚未创建时数据库不会为其维护统计信息。我们无法直接SHOW INDEX查看 Cardinality必须手动估算或模拟。关键认知联合索引的 Cardinality ≠ 单列 Cardinality 的简单叠加而是取决于列之间的组合唯一性。二、方法一手动计算最准确2.1 基础计算查询-- 假设你想测试 (col1, col2) 的联合索引 Cardinality-- 计算唯一组合数SELECTCOUNT(*)AStotal_rows,COUNT(DISTINCTcol1)AScol1_cardinality,COUNT(DISTINCTcol2)AScol2_cardinality,COUNT(DISTINCTCONCAT(col1,-,col2))AScombined_cardinalityFROMyour_table;2.2 示例输出解读------------------------------------------------------------------- | total_rows | col1_cardinality | col2_cardinality | combined_cardinality| ------------------------------------------------------------------- | 1000000 | 50000 | 100 | 800000| -------------------------------------------------------------------分析逻辑指标数值含义total_rows1,000,000表总行数col1_cardinality50,000col1 单独的不重复值数col2_cardinality100col2 单独的不重复值数combined_cardinality800,000两列组合的不重复值数判断标准combined_cardinality80万越接近total_rows100万联合索引效果越好如果combined_cardinality≈col1_cardinality5万说明 col2 几乎没有额外区分度不建议将 col2 加入联合索引三、方法二计算选择性Selectivity选择性是 Cardinality 的标准化指标消除了表大小差异的影响更适合跨表比较。3.1 选择性计算公式-- 联合索引选择性 唯一组合数 / 总行数-- 越接近 1 越好SELECTCOUNT(DISTINCTCONCAT(col1,-,col2))/COUNT(*)ASselectivity,CASEWHENCOUNT(DISTINCTCONCAT(col1,-,col2))/COUNT(*)0.1THEN适合建索引WHENCOUNT(DISTINCTCONCAT(col1,-,col2))/COUNT(*)0.01THEN效果一般ELSE不建议建索引ENDASsuggestionFROMyour_table;3.2 选择性决策矩阵选择性范围建议说明 0.1 (10%)强烈推荐建联合索引区分度极高索引收益明显0.01 ~ 0.1 (1%~10%)效果一般根据查询频率和写操作比例决定 0.01 (1%)不建议建索引区分度太低索引维护成本高考虑其他优化方案四、方法三对比不同列顺序联合索引的列顺序直接影响查询性能。最左前缀原则要求将 Cardinality 高的列放在前面。4.1 列顺序对比查询-- 测试 (a,b) vs (b,a) 哪个更好SELECTa_firstASorder_type,COUNT(DISTINCTCONCAT(a,-,b))AScardinalityFROMyour_tableUNIONALLSELECTb_firstASorder_type,COUNT(DISTINCTCONCAT(b,-,a))AScardinalityFROMyour_table;4.2 列顺序决策原则┌─────────────────────────────────────────────────────────┐ │ 原则将 Cardinality 高的列放在联合索引的左侧前面 │ │ │ │ 原因 │ │ 1. 最左前缀原则要求查询必须从索引最左列开始匹配 │ │ 2. 高 Cardinality 列在前能更快缩小扫描范围 │ │ 3. 避免索引失效——跳过最左列后后续列无法使用索引 │ └─────────────────────────────────────────────────────────┘示例场景订单表user_id10万 Cardinalitystatus5 Cardinality推荐索引(user_id, status)—— 先按用户过滤再按状态过滤不推荐(status, user_id)—— 先按状态只有5种过滤效率极低五、方法四使用 EXPLAIN 模拟MySQL 8.0虽然无法直接查看未创建索引的 Cardinality但可以通过创建临时索引模拟查询效果。5.1 临时索引测试流程-- Step 1: 创建临时索引仅用于测试数据量大时慎用ALTERTABLEyour_tableADDINDEXidx_test(col1,col2);-- Step 2: 查看优化器是否选择该索引EXPLAINSELECT*FROMyour_tableWHEREcol1xxxANDcol2yyy;-- Step 3: 查看实际 CardinalitySHOWINDEXFROMyour_tableWHEREKey_nameidx_test;-- Step 4: 测试完成后立即删除避免影响线上性能ALTERTABLEyour_tableDROPINDEXidx_test;5.2 注意事项生产环境慎用ALTER TABLE会锁表大表操作可能导致长时间阻塞。建议在低峰期执行或先在测试环境相同数据量验证或使用 MySQL 8.0 的INSTANT ADD COLUMN部分场景支持在线DDL六、方法五快速估算大表优化对于千万级甚至亿级大表COUNT(DISTINCT)全表扫描非常慢可以采用采样估算策略。6.1 随机采样估算-- 随机采样 10000 条估算整体 CardinalitySELECTCOUNT(DISTINCTCONCAT(col1,-,col2))*(SELECTCOUNT(*)FROMyour_table)/10000ASestimated_cardinality,COUNT(DISTINCTCONCAT(col1,-,col2))*(SELECTCOUNT(*)FROMyour_table)/10000/(SELECTCOUNT(*)FROMyour_table)ASestimated_selectivityFROM(SELECTcol1,col2FROMyour_tableORDERBYRAND()LIMIT10000)ASsample;6.2 采样策略优化表大小采样行数误差范围执行时间预估 100万全表计算0% 5秒100万 ~ 1000万10,000±5%2-10秒1000万 ~ 1亿50,000±3%10-30秒 1亿100,000±2%30-60秒提示ORDER BY RAND()在超大表上也很慢可以改用主键范围采样-- 更高效的采样方式基于主键范围SELECTcol1,col2FROMyour_tableWHEREidBETWEENFLOOR(RAND()*(SELECTMAX(id)FROMyour_table))ANDFLOOR(RAND()*(SELECTMAX(id)FROMyour_table))10000LIMIT10000;七、综合评估报告模板7.1 多组候选联合索引对比-- 综合评估报告分析多组候选联合索引SELECTidx_a_bASindex_name,COUNT(DISTINCTCONCAT(a,-,b))AScardinality,COUNT(DISTINCTCONCAT(a,-,b))/COUNT(*)ASselectivity,COUNT(DISTINCTa)ASfirst_col_card,COUNT(DISTINCTb)ASsecond_col_cardFROMyour_tableUNIONALLSELECTidx_b_aASindex_name,COUNT(DISTINCTCONCAT(b,-,a))AScardinality,COUNT(DISTINCTCONCAT(b,-,a))/COUNT(*)ASselectivity,COUNT(DISTINCTb)ASfirst_col_card,COUNT(DISTINCTa)ASsecond_col_cardFROMyour_tableUNIONALLSELECTidx_a_cASindex_name,COUNT(DISTINCTCONCAT(a,-,c))AScardinality,COUNT(DISTINCTCONCAT(a,-,c))/COUNT(*)ASselectivity,COUNT(DISTINCTa)ASfirst_col_card,COUNT(DISTINCTc)ASsecond_col_cardFROMyour_table;7.2 输出示例与解读-------------------------------------------------------------------- | index_name| cardinality| selectivity| first_col_card | second_col_card | -------------------------------------------------------------------- | idx_a_b | 850000 | 0.8500 | 100000 | 200 | | idx_b_a | 850000 | 0.8500 | 200 | 100000 | | idx_a_c | 120000 | 0.1200 | 100000 | 5000 | --------------------------------------------------------------------决策分析候选索引选择性推荐列顺序结论idx_a_b0.85a在前优秀高选择性idx_b_a0.85b在前选择性相同但b的 Cardinality 低不适合放前面idx_a_c0.12a在前中等根据查询频率决定最终推荐创建INDEX idx_a_b (a, b)因为选择性高达 0.85远超 0.1 阈值a的 Cardinality10万远高于b200符合高 Cardinality 列在前原则八、已创建索引的 Cardinality 查看如果你已经创建了索引可以直接查看数据库维护的统计信息-- 查看表的索引统计信息SHOWINDEXFROMyour_table;-- 或查询 information_schemaSELECTTABLE_NAME,INDEX_NAME,COLUMN_NAME,CARDINALITY,ROUND(CARDINALITY/TABLE_ROWS,4)ASselectivityFROMinformation_schema.STATISTICSsJOINinformation_schema.TABLEStONs.TABLE_SCHEMAt.TABLE_SCHEMAANDs.TABLE_NAMEt.TABLE_NAMEWHEREs.TABLE_NAMEyour_table;注意information_schema.STATISTICS中的CARDINALITY是估算值基于采样统计可能与实际值有偏差。对于关键决策建议用本文的方法重新计算。九、决策流程图开始评估联合索引 Cardinality │ ▼ ┌─────────────────────┐ │ 表数据量 100万 │ └─────────────────────┘ │ │ 是 否 │ │ ▼ ▼ 全表 COUNT(DISTINCT) 采样估算10,000~100,000条 │ │ └──────┬───────┘ ▼ ┌─────────────────────────────┐ │ 计算 combined_cardinality │ │ 和 selectivity │ └─────────────────────────────┘ │ ▼ ┌─────────────────────────────┐ │ selectivity 0.1 ? │ └─────────────────────────────┘ │ │ 是 否 │ │ ▼ ▼ ┌──────────┐ ┌─────────────────────────┐ │ 强烈推荐 │ │ selectivity 0.01 ? │ │ 建索引 │ └─────────────────────────┘ └──────────┘ │ │ 是 否 │ │ ▼ ▼ ┌──────────┐ ┌──────────┐ │ 效果一般 │ │ 不建议 │ │ 根据查询 │ │ 建索引 │ │ 频率决定 │ │ │ └──────────┘ └──────────┘十、核心结论与公式未创建的联合索引没有现成的 Cardinality 统计但你可以通过COUNT(DISTINCT CONCAT(col1, -, col2))来计算唯一组合数这就是联合索引的实际 Cardinality。核心公式联合索引 Cardinality COUNT(DISTINCT CONCAT(col1, -, col2, -, ...)) 联合索引选择性 联合索引 Cardinality / 表总行数黄金法则法则说明值越大越好Cardinality 越接近总行数索引效果越好高列在前联合索引中Cardinality 高的列放在左侧选择性 0.1强烈推荐建索引选择性 0.01维护成本高于收益不建议建索引十一、快速参考卡片-- 一键评估联合索引 (col1, col2)SELECTCOUNT(*)AStotal_rows,COUNT(DISTINCTcol1)AScol1_card,COUNT(DISTINCTcol2)AScol2_card,COUNT(DISTINCTCONCAT(col1,-,col2))AScombined_card,ROUND(COUNT(DISTINCTCONCAT(col1,-,col2))/COUNT(*),4)ASselectivity,CASEWHENCOUNT(DISTINCTCONCAT(col1,-,col2))/COUNT(*)0.1THEN强烈推荐WHENCOUNT(DISTINCTCONCAT(col1,-,col2))/COUNT(*)0.01THEN效果一般ELSE不建议ENDASsuggestionFROMyour_table;记住Cardinality 评估是索引优化的地基地基不稳后续的 EXPLAIN 分析、SQL 改写都是空中楼阁。在创建任何联合索引前先用本文的方法算一算避免盲目建索引带来的性能反噬。