如何创建引用两张表列的索引？GROUP BY对索引设计有何影响

首先明确一个基础规则：不存在依赖其他表字段创建的索引，所有常规关系型数据库的索引都是单表级别的对象，只能基于当前表的字段创建。跨表关联查询的性能优化，本质是针对每个参与查询的表，分别匹配查询的过滤、关联、取值逻辑建对应索引，不需要也没法创建跨多表的索引。

针对给出的查询场景的索引方案 #

你贴的SQL存在一处笔误：tab2 = something不符合语法，应为tab2.[具体过滤列名] = something，以下方案基于这个修正后的逻辑设计：

SELECT DISTINCT tab1.name
FROM tab1, tab2
WHERE tab1.s = tab2.s AND tab2.[filter_col] = something

这类两表内连接查询，优化器通常会先处理有常量过滤条件的tab2，筛出符合条件的s值集合，再关联tab1匹配行，最后对name做去重返回，对应索引建法如下：

• 针对tab2：建联合索引(filter_col, s)
  这个索引是覆盖索引：最左列是过滤条件字段，可以直接定位到符合filter_col = something的记录，不需要回表就能直接拿到关联需要的s字段，全程只扫索引树，没有额外回表开销。
• 针对tab1：建联合索引(s, name)
  这个索引同样是覆盖索引：最左列是关联字段s，可以快速匹配从tab2传过来的s值，同时索引里已经包含了最终需要返回的name字段，不需要回表查主键数据；而且索引内同s值下的name是按序存储的，做DISTINCT去重时可以直接跳过重复值，不需要额外做内存/磁盘排序。

带GROUP BY name的场景对索引设计的影响 #

会影响，核心调整逻辑是尽可能利用索引的有序性，避免GROUP BY阶段触发filesort、临时表开销，规则如下：

• 如果GROUP BY仅对name做分组、没有额外引用tab1的其他字段，比如常见的SELECT tab1.name, COUNT(*) FROM ... GROUP BY tab1.name场景，之前设计的(s, name)索引依然适用：索引本身按s、name有序，关联完成后的数据天然按name排序，分组时不需要额外排序。
• 如果GROUP BY伴随其他字段的聚合计算，比如需要SUM(tab1.amount)、取MAX(tab1.create_time)这类逻辑，要把聚合引用的字段追加到联合索引的尾部，比如调整为(s, name, amount, create_time)，保证整个查询需要的字段都在索引里，全程不回表。
• 如果优化器根据数据分布调整了关联顺序（比如小表驱动大表的规则下，选择先扫tab1再关联tab2），不需要刻意改索引，只要遵循「过滤列放最前、关联列放中间、返回/分组/聚合列放最后」的联合索引设计原则，就能保证索引效率。

注意：不要为了跨表查询随意建冗余的跨表冗余字段做索引，除非是数据量极大、关联性能瓶颈非常明显的数仓场景，常规OLTP场景下按上述规则建单表覆盖索引就足够支撑性能。

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