MySQL同名多key有什么影响？不同索引设计的优劣与适用场景如何？

认知澄清 #

你首先混淆了两种完全不同的索引结构：你手写SQL创建的是两个独立的单列非唯一索引，而CodeIgniter 3代码生成的是单个包含两列的联合（复合）索引，你看到的Key_name相同、Seq_in_index不同是联合索引的正常结构，不是“多个同名索引”。

问题1：会不会引发字段歧义？ #

完全不会。索引名是单个索引的唯一标识，同一个联合索引的不同组成列天然共享同一个索引名，Seq_in_index只是标记列在联合索引里的排序优先级，不存在字段歧义问题。

问题2：两种设计的性能差异 #

两种结构的适用场景完全不同，性能差异直接和查询模式绑定：

• 联合索引的优势：
  1. 存储开销更低：只需要维护1棵B+树，远小于两个单列索引的总存储开销
  2. 多条件匹配性能更高：如果查询条件同时命中estimate_id和order_number，联合索引只需要一次索引树查找，不需要做两个单列索引的合并操作，性能明显更好
  3. 支持最左前缀匹配：单独查询estimate_id时，联合索引可以正常生效，性能和单独的estimate_id单列索引基本一致
• 联合索引的劣势：
  1. 不支持非最左前缀的单独查询：如果查询条件只有order_number，联合索引完全无法生效，只能走全表扫描，而单独的order_number单列索引可以直接命中
  2. 排序/分组灵活性更差：如果需要单独对order_number做排序、分组操作，联合索引无法提供加速，单列索引可以

问题3：什么时候需要用独立单列索引？ #

满足以下任意一种场景时，更建议创建名称独立的单列索引：

• 你经常需要单独查询联合索引中非最左位置的字段（比如经常单独按order_number筛选数据）
• 两个字段的组合查询出现频率极低，绝大多数查询都是单独筛选其中某一个字段
• 你需要经常对其中单个字段做排序、分组、去重操作

CodeIgniter 3创建独立单列索引的正确写法 #

如果需要生成两个独立的单列索引，不要把两个字段放进同一个add_key调用的数组里，分开调用即可：

// 生成第一个索引，键名为estimate_id
$this->dbforge->add_key('estimate_id');
// 生成第二个索引，键名为order_number
$this->dbforge->add_key('order_number');

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