未使用MySQL复合索引（交集）-Java 学习之路

我在user_profile表上运行搜索，包含单个索引以及复合索引：

SELECT ••• 
      FROM user_profile up
      JOIN auth_user au
        ON au.id = up.user_id 
      LEFT 
      JOIN _basecountry bc
        ON bc.id = up.country_id = 
      LEFT 
      JOIN _relationshipstatus rs
        ON rs.id = up.relationship_status_id 
      LEFT 
      JOIN _workstatus ws
        ON ws.id = up.work_status_id
      LEFT 
      JOIN _fieldofwork fw
        ON fw.id = up.field_of_work_id 
      LEFT 
      JOIN _fieldofstudy fs
        ON fs.id = up.field_of_study_id 
      LEFT 
      JOIN _educationlevel el
        ON el.id = up.education_level_id 
      LEFT 
      JOIN _religion r
        ON r.id = up.religion_id 
     WHERE up.lazy = 0 
       AND up.has_avatar = 1 
       AND up.inactive = 1 
       AND up.id <> 3247028 
       AND up.city = 'London' 
       AND up.challenge_count < 10 
       AND up.age BETWEEN 18 AND 28 
       AND up.gender = 'F' 
       AND up.id > 1468899 
     LIMIT 25

解释结果是：

POSSIBLE_KEYS PRIMARY，user_id，compound_match，age，gender，challenge_count，lazy，city，has_avatar，inactive KEY city，lazy，has_avatar KEY_LEN 578,1,1无ROWS 1224 EXTRA使用intersect（city，lazy，has_avatar）;在哪里使用

复合索引'compound_match'组合使用的列：id，user_id，age，gender，challenge_count，lazy，has_avatar，inactive

为什么mysql喜欢交叉呢？结果查询很慢 .

1 回答

2
索引中列的顺序很重要 . 您应该记住以下规则创建索引（假设所有搜索词都与 AND 结合）：
- equality 条件中引用的列首先出现 . 这些顺序并不重要，但我更喜欢将它们从最具选择性到最少选择 .
- 下一个是 range 条件中引用的一列，或者是您要对其进行排序或分组的一个或多个列 . 如果您有多个范围条件（就像在此查询中一样），抱歉，您可以预期只有一个列可以从索引中受益 . 因此，选择最具选择性的列（即有助于以最佳比例缩小搜索范围） .
- 您既不会搜索也不会进行排序的其他列，但您希望将其作为 index-only query 的一部分进行提取 . 但请记住，MySQL索引中的最大列数为16 .
因此，在这种情况下，user_profile表上有以下条件：
- up.lazy = 0（相等）
- up.has_avatar = 1（相等）
- up.inactive = 1（相等）
- up.id <> 3247028（范围）
- up.city = 'London'（平等）
- up.challenge_count <10（范围）
- up.age在18和28之间（范围）
- up.gender = 'F'（平等）
- up.id> 1468899（范围）
你没有进行排序（尽管@Strawberry指出，如果你使用LIMIT，也许你应该这样做） .

您有其他列未用于搜索，但在连接条件中引用：
- up.user_id
- up.country_id
- up.relationship_status_id
- up.work_status_id
- up.field_of_work_id
- up.field_of_study_id
- up.education_level_id
- up.religion_id
所以我将按以下顺序创建包含列的索引：
```
ALTER TABLE user_profile ADD INDEX
  (city, lazy, has_avatar, inactive, gender, /* equality conditions */
   id /* range */
   challenge_count, age, /* also in range conditions, but the index won't be used */
   user_id, country_id, relationship_status_id, work_status_id, field_of_work_id,
   field_of_study_id, education_level_id, religion_id /* covering index */
);
```
这是16列，索引的最大值 . 如果您引用选择列表中的其他列，则会破坏覆盖索引优化，因此您可以跳过所有这些额外的列 .

我猜测 id 将是范围条件中最具选择性的列，但如果您认为challenge_count或age会更具选择性，那么请更改顺序 .

您可能也喜欢我的演讲How to Design Indexes, Really .
回复于 2024-04-30T11:29:13+08:00

未使用MySQL复合索引（交集）

1 回答

相关问题