Maturest

# 最左前缀原则

最左前缀原则指的是在使用复合索引（即联合索引）进行查询时，MySQL会按照索引中最左边的列开始匹配查询条件。如果查询条件中包含了索引的最左列，那么MySQL就可以利用这个索引来加速查询过程。但是，如果查询条件跳过了索引的最左列，那么MySQL就无法使用这个索引，从而导致查询效率下降。

# 使用案例

创建复合索引

CREATE INDEX idx_name_age_gender ON users(name, age, gender);

编写查询语句

# 使用了复合索引
SELECT * FROM users WHERE name = 'John' AND age < 30;

# 跳过了索引的最左列
SELECT * FROM users WHERE age < 30;

当查询条件中包含范围查询（如>、<、BETWEEN、LIKE等）时，最左前缀原则会在遇到第一个范围查询条件时停止匹配后续的索引列。因此，在创建复合索引时，应该尽量将等值查询条件（如=）的列放在前面，将范围查询条件的列放在后面。

为了更高效地利用索引，可以根据实际业务需求和数据分布来优化索引的设计。例如，如果某个列在查询条件中频繁出现，且该列的数据分布较为均匀，那么可以将该列放在复合索引的最前面。

只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL 值，那么这一列对于此复合索引就是无效的。所以在数据库设计时不要让字段的默认值为 NULL。

再次举例：

# 假设有一个复合索引 index(a,b,c)

# 能用上a b
select * from users where a = 1 and b = 2

# 能用上a b（有mysql查询优化器）
select * from users where a = 1 and b = 2

# 能用上a
select * from users where a = 2 and c = 3

# 能用上a
select * from users where a > 2 and c = 3 and b =4

# 不能
select * from users where b = 2 and c = 3

# 注意事项

  • 索引列的顺序：在创建复合索引时，应该根据查询条件中列的使用频率和重要性来安排索引列的顺序。最常用的列或过滤性最强的列应该放在索引的最左边。

  • 查询条件的顺序：编写查询时，尽量保证WHERE子句中的条件顺序与索引列的顺序一致。虽然MySQL查询优化器有时会重新排列条件以匹配索引，但手动优化查询条件顺序可以确保查询性能的可预测性。

  • 避免跳过索引列：尽量避免在查询条件中跳过索引中的列。如果查询条件只包含索引中的非最左列，那么该索引将不会被使用。

  • 索引选择性和基数：索引的选择性（Selectivity）是指索引列中不同值的数量与表中总记录数的比例。高选择性的列更适合放在索引的前面，因为它们能更好地缩小搜索范围。基数（Cardinality）是索引列中不同值的数量，也是衡量索引选择性的一个指标。

  • 前缀索引：如果索引列是字符串类型，并且查询条件中经常只使用列的前缀，那么可以考虑使用前缀索引来减少索引大小和提高查询效率。但是，前缀索引的长度需要仔细选择，以确保既能保持较高的选择性，又能减少索引占用的空间。

  • 索引覆盖：尽量使查询只通过索引就能获取所需的数据，而不需要回表查询。

  • 使用EXPLAIN分析查询：使用MySQL的EXPLAIN命令来分析查询的执行计划，查看是否利用了索引以及索引的使用方式。这有助于发现潜在的索引优化机会。

  • 避免在索引列上使用函数或表达式：在索引列上使用函数或表达式会阻止MySQL使用索引。因此，在编写查询时，应该尽量避免在索引列上进行函数操作或计算。

  • 注意索引的维护：随着数据的增加和变化，索引的性能可能会受到影响。因此，需要定期监控索引的性能并进行必要的维护操作，如重建索引、优化表等。

  • 考虑查询的实际需求：在设计索引时，不仅要考虑查询性能，还要考虑索引对插入、更新和删除操作的影响。过多的索引会减慢这些操作的速度，因为MySQL需要在每个索引上维护数据的一致性。因此，需要根据实际的查询需求和数据变更频率来平衡索引的数量和类型。

← 慢查询 →