mysql联合索引的使用规则

下面我将详细讲解MySQL联合索引的使用规则。

什么是MySQL联合索引？

MySQL联合索引，也叫复合索引，是由多个字段组成的索引。与单列索引不同，联合索引是指同时对多个字段进行索引。联合索引可以减少查询中所需要的where条件的列索引次数，提高查询效率，在某些情况下还可以避免使用MySQL的临时表。

联合索引的使用规则

使用联合索引时需要遵循以下规则：

1. 使用联合索引的情况必须保证“最左前缀”匹配原则，也就是说，如果一个查询涉及到复合索引的第二个字段，那么它一定需要包含第一个字段，才能使用该复合索引，否则该索引将无法使用，查询将变得很慢。

2. 组合的列顺序应该根据数据中最高的选择性（selectivity）进行排序。

3. 建立联合索引时，应该保证该索引的字段组合是真正需要查询的，频繁查询的语句。

4. 当出现范围查询时（如 BETWEEN, >, <, LIKE 等），只能使用联合索引的最左部分。

下面结合两个示例来说明。

示例1

假设有一个用户表（user）包含以下字段：id、name、age、sex、phone。我们需要查询这个表中所有年龄在18到25之间、且性别为男的用户。为了优化查询效率，我们可以为表user建立一个联合索引，包括age、sex两个字段。

CREATE INDEX idx_user_age_sex ON user(age, sex);

执行以下查询语句：

SELECT * FROM user WHERE age BETWEEN 18 AND 25 AND sex = 'male';

此时，MySQL会使用idx_user_age_sex索引，将查询转换为两个单独的查询，一次查询age字段，一次查询sex字段，然后将它们合并。由于我们在where语句中所提供的条件都包含联合索引的最左的column，MySQL将可以使用该索引完成查询，提高了查询效率。

示例2

假设有一个商品表(product)包含以下字段：id、name、category、price、brand。我们需要查询价格在100元以下、且品牌为Apple的商品信息。我们可以建立一个联合索引，包括price、brand两个字段。

CREATE INDEX idx_product_price_brand ON product(price, brand);

执行以下查询语句：

SELECT * FROM product WHERE price < 100 AND brand = 'Apple';

由于我们在where语句中所提供的条件不包含联合索引的最左的column，这个查询将会变得很慢，索引也失去了作用。此时，我们可以建立一个专门针对该查询的索引，只包括brand字段，以提高查询效率。

CREATE INDEX idx_product_brand ON product(brand);

总结

MySQL联合索引通过为多个字段创建一个简单、快速的索引，加快了数据的访问速度，有效提升查询效率。我们在设计表结构时，需要考虑实际业务需求，设置合适的联合索引，以便提升数据访问效率。在使用联合索引时，必须遵循“最左前缀”匹配原则、组合列的排序和只选择需要查询的列等规则，才能有效利用索引提高查询效率。