索引新增第三列及列序对高效查询的影响咨询

当前索引方案的有效性 #

你计划创建的索引KEY (movie_id, category_id, num_of_theaters)是合理的，category_id基数远低于num_of_theaters的情况不会影响索引效果。原因如下：

• 复合索引的核心规则是：等值过滤列在前，范围过滤列在后。你的查询中，movie_id和category_id是精确匹配的等值条件，num_of_theaters是>0或<>的范围条件，这个列顺序完全符合最优索引逻辑。
• 基数高低只影响单索引的选择性，但在复合索引的等值条件部分，不管基数高低，只要是精确匹配，就能快速缩小数据范围。这里先通过movie_id定位，再用category_id进一步筛选，最后用num_of_theaters过滤，整个流程的效率不受category_id低基数的影响。

是否需要调整索引列顺序？ #

不需要调整。如果把num_of_theaters提前到category_id前面，索引变成(movie_id, num_of_theaters, category_id)，会导致category_id无法被索引有效利用——因为范围条件（num_of_theaters）之后的列，索引的有序性会失效，数据库无法通过category_id快速过滤，反而会降低查询效率。

更高效的优化方案 #

1. 优化为覆盖索引：如果你的查询仅需要返回movie_id、category_id、num_of_theaters，当前索引已经是覆盖索引，能直接从索引获取数据，避免回表到主键索引，效率拉满。如果查询还需要customer_score，可以把它加到索引末尾：KEY (movie_id, category_id, num_of_theaters, customer_score)，彻底消除回表操作。
2. 字段类型优化：num_of_theaters存储的是影院数量，建议从double改成int类型，减少存储空间，提升数值比较的效率。
3. 验证索引使用：创建索引后用EXPLAIN查看查询计划，确认索引被命中，且出现Using index标识（覆盖索引生效）。

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