MySQL中的最左前缀匹配原则

最左前缀匹配原则是 MySQL 在使用索引时遵循的一种规则，尤其在涉及到组合索引（联合索引）时。

最左前缀匹配原则指的是在使用组合索引时，MySQL 会从最左边的索引列开始匹配，直到遇到第一个无法继续匹配的列为止。这意味着，如果你想使用组合索引来加速查询，你的查询条件必须遵循 “最左前缀” 的要求，即必须从组合索引最左边的字段开始匹配，逐步向右，不能跳过任何列。

---

在MySQL中，我们可以为多个字段创建组合索引，例如：

CREATE INDEX idx_user ON users (first_name, last_name, age);

我们为 users 表创建了一个组合索引 idx_user，包含了三个列：first_name、last_name 和 age。这个索引可以帮助加快查询速度，但是遵循最左前缀匹配的规则，具体体现在以下几个方面：

1. 完全匹配最左前缀：当你查询条件完全包含最左边的字段，MySQL可以使用这个索引。例如：

   SELECT * FROM users WHERE first_name = 'John';
   

   在这种情况下，查询完全匹配索引中的第一个字段 first_name，因此可以使用 idx_user 索引。

2. 匹配多个最左前缀的字段：如果查询条件包含索引中前面多个字段，那么MySQL也会使用索引。例如：

   SELECT * FROM users WHERE first_name = 'John' AND last_name = 'Doe';
   

   这时，查询匹配了 first_name 和 last_name，MySQL仍然可以使用组合索引 idx_user。

3. 部分前缀匹配：如果查询条件只匹配索引的前几个字段中的一部分，MySQL仍然可以利用索引。例如：

   SELECT * FROM users WHERE first_name = 'John' AND age = 25;
   

   在这种情况下，由于中间的 last_name 被跳过了（而 first_name 后面直接是 age），索引无法很好地工作，只能利用 first_name 的部分匹配部分，而不会利用 age。

继续以 users 表和组合索引 (first_name, last_name, age) 为例，看看不同的查询能否使用索引：

1. 可以使用索引：

   SELECT * FROM users WHERE first_name = 'Alice';
   

   • 只使用了第一个字段，符合最左前缀原则。

   SELECT * FROM users WHERE first_name = 'Alice' AND last_name = 'Smith';
   

   • 使用了前两个字段，符合最左前缀原则。

   SELECT * FROM users WHERE first_name = 'Alice' AND last_name = 'Smith' AND age = 30;
   

   • 使用了所有字段，符合最左前缀原则。

2. 不能使用索引：

   SELECT * FROM users WHERE last_name = 'Smith';
   

   • last_name 不是最左前缀的第一个字段，MySQL不会使用索引 idx_user。

   SELECT * FROM users WHERE age = 30;
   

   • age 不是最左前缀的第一个字段，同样不会使用组合索引。

3. 部分使用索引：

   SELECT * FROM users WHERE first_name = 'Alice' AND age = 30;
   

   • 这里查询中间跳过了 last_name，只会对 first_name 部分使用索引，而无法有效地对 age 进行索引加速。

---

LIKE 查询

在进行 LIKE 查询时，最左前缀原则也同样适用。例如，对于一个索引 (first_name)：

• 查询 SELECT * FROM users WHERE first_name LIKE 'A%' 会利用索引，因为查询从最左边开始匹配。
• 而 SELECT * FROM users WHERE first_name LIKE '%A' 则无法利用索引，因为它并不是从最左边匹配，而是存在前缀通配符。

---

覆盖索引

在某些情况下，使用最左前缀匹配还可以带来覆盖索引的好处。当所查询的字段全部包含在组合索引中时，MySQL 可以直接通过索引获取结果，而不需要访问数据行。这种现象称为覆盖索引（Covering Index），它可以极大地提高查询效率。例如：

SELECT first_name, last_name FROM users WHERE first_name = 'Alice' AND last_name = 'Smith';

在这个查询中，所有被查询的字段都在索引中，所以可以直接从索引中获取这些数据，而不需要再回到表中读取数据行。

原文地址：https://blog.csdn.net/2404_87410060/article/details/143088844

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！