MySQL查询优化使用USE INDEX时报错Key不存在及优化建议咨询

关于USE INDEX语法的参数说明 #

USE INDEX (index_list) 中的index_list必须填写对应表在建表/新增索引时定义的索引名称，不能填写列名、带别名的列名。

你遇到ERROR 1176 (42000): Key 'index_name' doesn't exist in table 'ecs'报错的常见排查点：

• 首先执行SHOW INDEX FROM ecs;查看该表所有索引的Key_name字段值，只有这个字段的取值才是可以填入USE INDEX的合法参数
• 如果你在查询中给ecs表设置了别名，USE INDEX必须紧跟在表别名的后方，示例写法：SELECT * FROM ecs AS e USE INDEX (实际索引名) WHERE 过滤条件
• 如果MySQL部署在大小写敏感的系统（如Linux）上，需要保证填写的索引名大小写和定义时完全一致

MySQL查询优化实用技巧 #

索引优化相关 #

• 优先创建联合索引，遵守最左前缀匹配原则，过滤性越高的字段越要放在联合索引的左侧
• 不要在索引列上执行函数、算术运算、隐式类型转换，这类操作会直接导致索引失效
• 区分度极低的字段（如只有2-3种取值的状态字段、性别字段）不要单独创建索引，当查询过滤后返回数据量超过全表20%时，全表扫描的效率会高于走索引
• 定期清理冗余索引：比如已经存在联合索引idx(a,b)的场景下，单独的idx(a)就属于冗余索引，可以直接删除

查询写法优化相关 #

• 避免使用SELECT *，只查询业务需要的字段，降低数据传输开销和回表开销
• 大分页查询（如LIMIT 100000,10）优化为：先定位偏移位置的主键ID，再关联查询数据，示例写法：SELECT * FROM 表名 WHERE id > (SELECT id FROM 表名 LIMIT 100000,1) LIMIT 10
• 关联查询的表数量不要超过3张，所有关联字段必须创建索引，遵循小表驱动大表的原则降低关联开销
• 尽量避免使用子查询，绝大多数场景都可以改为JOIN关联查询，优化器的优化效率更高

执行计划分析技巧 #

• EXPLAIN结果的type字段优先级从优到劣为：system > const > eq_ref > ref > range > index > ALL，至少要保证查询达到range级别，最优状态为ref级别
• 重点关注Extra字段的异常提示：如果出现Using filesort、Using temporary说明需要优化排序逻辑或者索引结构；出现Using index说明触发了覆盖索引，不需要回表查询，属于最优状态
• rows字段代表优化器预估需要扫描的行数，该数值越小越好

通用运维优化建议 #

• 单表数据量超过千万级别时，可以根据业务场景做分区表、分库分表优化，降低单表数据量级
• 定期执行ANALYZE TABLE 表名更新表统计信息，避免优化器因为统计信息过时选择错误的执行计划
• 复杂查询可以先拆分执行，确认每个子部分的执行效率后再组合，更方便定位性能瓶颈

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