gpt4 book ai didi

未使用 MySQL 复合索引(交集)

转载 作者:行者123 更新时间:2023-11-29 06:33:22 38 4
gpt4 key购买 nike

我在 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 None

ROWS 1224

EXTRA Using intersect(city,lazy,has_avatar); Using where

复合索引 'compound_match' 结合使用的列:id,user_id,age,gender,challenge_count,lazy,has_avatar,inactive

为什么 mysql 更喜欢 intersect 而不是它?结果查询很慢。

最佳答案

索引中列的顺序很重要。您应该在创建索引时牢记以下规则(假设所有搜索词均使用 AND 组合):

  1. 相等条件下引用的列优先。这些顺序并不重要,但我更喜欢从最有选择性的到最少的顺序列出它们。

  2. 下一个是在范围条件中引用的一个列,或者是您排序或分组所依据的一个或多个列。如果您有多个范围条件(如您在此查询中所做的那样),抱歉,您只能期望其中一列受益于索引。因此,选择最具选择性的列(即有助于按最佳比例缩小搜索范围)。

  3. 您既不根据其进行搜索也不进行排序,但希望将其作为仅索引查询 的一部分进行提取的其他列。但请记住,MySQL 索引中的最大列数是 16。

所以在这种情况下,您在 user_profile 表上具有以下条件:

  • up.lazy = 0(相等)
  • up.has_avatar = 1(平等)
  • up.inactive = 1(相等)
  • up.id <> 3247028(范围)
  • up.city = '伦敦'(平等)
  • up.challenge_count < 10(范围)
  • 年龄在 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 .

关于未使用 MySQL 复合索引(交集),我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/26538615/

38 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com