我正在对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、年龄、性别、challenge_count、懒惰、城市、has_avatar、非活动
KEY城市,懒惰,has_avatar
KEY_LEN 578,1,1无
第1224行
EXTRA使用交集(city,lazy,has_avatar);使用where
复合索引"Compound_match"组合了已使用的列:id、user_id、年龄、性别、challenge_count、lazy、has_avatar、inactive
为什么mysql更喜欢交叉而不是它?因此,查询速度较慢。
索引中列的顺序很重要。您应该根据以下规则创建索引(假设所有搜索项都与AND
组合):
-
相等条件中引用的列优先。这些东西的顺序并不重要,但我更喜欢从最挑剔到最不挑剔地列出它们。
-
接下来是在范围条件中引用的一个列,或者是排序或分组所依据的一个或多个列。如果您有多个范围条件(就像在本查询中所做的那样),很抱歉,您只能期望其中一列从索引中受益。因此,选择最具选择性的列(即有助于按最佳比例缩小搜索范围)。
-
既不搜索也不排序,但希望作为仅索引查询的一部分提取的其他列。但请记住,MySQL索引中的最大列数是16。
因此,在这种情况下,在user_profile表上有以下条件:
- up.lazy=0(相等)
- up.has_avatar=1(相等)
- up.inactive=1(相等)
- up.id<>3247028(范围)
- up.city="伦敦"(相等)
- 向上挑战计数<10(范围)
- 18和28之间的最大值(范围)
- up.gender='F'(相等)
- up.id>1468899(范围)
你没有进行排序(尽管正如@Strawberry所指出的,如果你使用LIMIT,也许你应该这样做)。
您有其他未用于搜索的列,但在联接条件中被引用:
- up.user_id
- up.country_id
- up.relationship_status_id
- 向上.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会更具选择性,那么请更改顺序。
你可能还喜欢我的演示如何设计索引,真的。