重复索引识别及索引正确创建使用优化咨询

可用工具 #

• Percona Toolkit 自带的pt-duplicate-key-checker：直接扫描目标库所有表，自动识别重复、冗余索引，输出可直接执行的删除语句，不需要手动逐表核对，适合大库快速排查。
• MySQL 8.0+ 内置sys库视图：查询sys.schema_redundant_indexes可以直接获取冗余索引和被覆盖的目标索引对应关系，查询sys.schema_unused_indexes可以拿到实例启动后从未被使用过的索引列表。

手动排查SQL（兼容MySQL 5.7/8.0） #

执行前把你的库名替换为实际业务库名即可，判断逻辑为：如果索引A的所有列按顺序完全是索引B的最左前缀，且B的唯一性约束不弱于A，A即为冗余索引。

SELECT
    a.TABLE_NAME AS 表名,
    a.INDEX_NAME AS 冗余索引名,
    GROUP_CONCAT(DISTINCT a.COLUMN_NAME ORDER BY a.SEQ_IN_INDEX) AS 冗余索引列,
    b.INDEX_NAME AS 被覆盖的现有索引名,
    GROUP_CONCAT(DISTINCT b.COLUMN_NAME ORDER BY b.SEQ_IN_INDEX) AS 现有索引列
FROM information_schema.STATISTICS a
JOIN information_schema.STATISTICS b
    ON a.TABLE_SCHEMA = b.TABLE_SCHEMA
    AND a.TABLE_NAME = b.TABLE_NAME
    AND a.SEQ_IN_INDEX = b.SEQ_IN_INDEX
    AND a.COLUMN_NAME = b.COLUMN_NAME
    AND a.INDEX_NAME <> b.INDEX_NAME
LEFT JOIN information_schema.STATISTICS c
    ON c.TABLE_SCHEMA = a.TABLE_SCHEMA
    AND c.TABLE_NAME = a.TABLE_NAME
    AND c.INDEX_NAME = b.INDEX_NAME
    AND c.SEQ_IN_INDEX = b.SEQ_IN_INDEX +1
WHERE a.TABLE_SCHEMA = '你的库名'
    AND a.NON_UNIQUE >= b.NON_UNIQUE
GROUP BY a.TABLE_NAME,a.INDEX_NAME,b.INDEX_NAME
HAVING MAX(a.SEQ_IN_INDEX) = COUNT(*)
    AND COUNT(c.COLUMN_NAME) = 0;

你当前给出的索引配置如下：

PRIMARY KEY (`id`),
UNIQUE KEY `TLC` (`tlc_unique_check`),
KEY `idx_uniqueId` (`uniqueId`),
KEY `idx_jobs_feed_state_city` (`state`,`city`),
KEY `idx_jobs_feed_guid` (`guid`),
KEY `idx_jobstatus_is_deleted_jobtitle` (`job_status`,`is_deleted`,`jobtitle`),
KEY `Idx_delfgfjpost` (`deleted_from_gfj`,`posted_to_gfj`),
KEY `idx_is_updated` (`is_updated`),
KEY `idx_company_logo` (`company`,`custom_logo`),
KEY `idx_is_deleted_locExpand` (`is_deleted`,`locExpand`),
KEY `idx_locexpand_jobid` (`locExpand`,`clientJobId`)

可直接删除的索引 #

• idx_is_updated：is_updated是布尔类字段，区分度普遍低于10%，走二级索引需要回表查主键数据，成本远高于全表扫描，线上场景这类索引90%以上不会被优化器选中，只会占用存储、拖慢写入性能，直接删除。
• 特殊判断：如果uniqueId和tlc_unique_check是同业务含义的唯一标识字段，idx_uniqueId属于重复索引，直接删除；如果两个字段业务含义完全不同，该索引保留。

可合并调整的索引 #

• 合并idx_is_deleted_locExpand和idx_locexpand_jobid：两个索引都包含locExpand字段，且前者前导列is_deleted是低区分度标记字段，绝大多数业务查询都会默认带is_deleted=0（未删除）的过滤条件，合并为KEY idx_is_deleted_locExpand_jobid (is_deleted, locExpand, clientJobId)即可覆盖两个索引的所有查询场景，减少一个索引的维护成本。
• 调整Idx_delfgfjpost：两个索引列都是低区分度的布尔标记字段，单独使用筛选效率极低，建议结合高频查询场景，把高频过滤、返回的字段追加到索引尾部，比如常见同步场景可以调整为KEY Idx_delfgfjpost (deleted_from_gfj, posted_to_gfj, id, job_status)，做成覆盖索引避免回表。
• 优化idx_jobstatus_is_deleted_jobtitle：如果业务中jobtitle经常做前缀模糊匹配（比如jobtitle like 'Java%'），当前字段顺序是合理的；如果经常做全模糊匹配（jobtitle like '%Java%'），这个字段放在联合索引里没有实际作用，可以替换为其他高频过滤字段。

可保留的合理索引 #

以下索引符合最左前缀原则、前导列区分度足够，没有冗余问题，可以保留：

• 主键索引PRIMARY KEY (id)
• 唯一索引TLC (tlc_unique_check)
• 地址类联合索引idx_jobs_feed_state_city (state,city)
• 高区分度单列索引idx_jobs_feed_guid (guid)
• 企业信息联合索引idx_company_logo (company,custom_logo)

• 严格遵循最左前缀原则：联合索引把区分度最高、最常做等值匹配的字段放在最左侧，低区分度的状态、布尔字段放在索引前部时，要确认筛选后剩余数据量低于全表的20%，否则优化器不会选择走该索引。
• 控制单表索引数量：核心业务表索引数量尽量控制在5个以内，最多不超过8个。过多索引会占用额外存储，且每次增删改操作都要同步更新所有关联索引，写入性能会随索引数量增加线性下降。
• 避免低价值索引：不要给区分度低于10%的字段（比如布尔值、个位数枚举值的状态字段）建单列索引，这类索引的回表成本远高于全表扫描收益，优化器大概率不会命中。
• 优先用覆盖索引：针对高频查询，把过滤条件、排序分组字段、查询返回字段都放到索引中，查询时可以直接从索引取数，不需要回表查主键，性能通常可以提升1~2个数量级。
• 上线前做执行计划验证：索引发布前用EXPLAIN查看目标SQL的执行计划，确认type达到range/ref级别、key命中目标索引、rows扫描行数远低于全表行数，再上线到生产环境。

内容的提问来源于stack exchange，提问作者Chowdary