Mysql联合索引和最左匹配例子说明

前言

是什么是索引？
索引是一种数据结构，用于加速数据库查询。

当没有索引时，数据库系统需要执行全表逐行扫描来满足查询需求。这意味着它会逐行读取整个表中的数据，并在内存中进行比较，以找到满足查询条件的数据行。由于数据通常存储在磁盘上，而磁盘的读取速度相对较慢，因此全表扫描会导致大量的磁盘 I/O 操作，这些磁盘 I/O 操作会耗费大量的时间。此外，全表扫描还会导致大量的数据被加载到内存中，这可能会耗尽内存资源，并导致性能下降

总结：在没有索引的情况下，MySQL必须从第一行开始逐行扫描整个表来查找相关的行。而有了索引，MySQL可以快速定位到数据文件中的某个位置，而无需查看所有数据。这比顺序读取每一行要快得多。

用个比喻解释：想象一下你手里拿着一本庞大的《新华字典》，你想查找某个字的解释，比如“锁”。如果没有索引，你可能会从第一页开始，一页一页地翻阅，直到找到这个字为止。这样的查找过程会非常耗时，因为你需要逐页逐字地浏览整本字典。新华字典通过查找目录可以快速定位到要查找字的位置。

联合索引

联合索引是一种数据库索引类型，它涵盖了多个列。与单列索引不同，联合索引允许同时在多个列上创建索引，以便在查询中更有效地过滤和定位数据。

举例：当我们对 (a, b, c) 字段建立索引时，实际上会创建三个索引：(a)、(a, b)、(a, b, c)。

最左匹配原则

最左匹配原则是指在使用联合索引进行查询时，只有索引的最左边的列开始的查询条件才会被利用到。如果查询条件不是从最左边的列开始，索引将不会被使用。

换句话说，当你创建了一个联合索引，比如 (a, b, c)，如果你的查询条件中包含了索引的最左边的列 a，那么这个索引可以被用到。但如果查询条件只涉及到 b 或者 c 而不涉及到 a，那么这个索引将不会被使用。

但是就算是(a,b,c)，当遇到范围查询（例如 >、<、BETWEEN、LIKE）时，就会停止匹配。 (见后面例子第三种)

举例说明

准备表：

CREATE TABLE test_index
(
    id    INT(10) NOT NULL PRIMARY KEY,
    col_a INT(11) NOT NULL DEFAULT 0 COMMENT '字段a',
    col_b INT(11) NOT NULL DEFAULT 0 COMMENT '字段b',
    col_c INT(11) NOT NULL DEFAULT 0 COMMENT '字段c',
    col_d INT(11) NOT NULL DEFAULT 0 COMMENT '字段d',
    KEY index_a_b_c (col_a, col_b, col_c) # 创建联合索引
) ENGINE = InnoDB;

1
2
3
4
5
6
7
8
9
10

分析查询语句：
第一种

explain select t.id, t.col_c, t.col_b, t.col_c, t.col_d
from test_index as t
where col_a=1 and col_b=1 and col_c=1;
1
2
3

结果可以发现正常使用到了联合索引

注意：
对于索引 (col_a, col_b, col_c)，查询条件为 col_c=1 and col_b=1 and col_a=1，依然可以使用索引。尽管查询条件的顺序与索引定义的顺序不同，但因为查询条件包含了索引的所有列，数据库优化器仍然可以利用这个索引进行优化查询。

第二种：

explain select t.id, t.col_c, t.col_b, t.col_c, t.col_d
from test_index as t
where col_b=1 and col_c=1;
1
2
3

查询条件不满足最左前缀匹配原则，查询条件 col_b=1 and col_c=1 不是以索引的最左边列 col_a 开始的，因此无法利用索引。

第三种：

explain select t.id, t.col_c, t.col_b, t.col_c, t.col_d
from test_index as t
where col_a>1 and col_b=1 and col_c=1;
1
2
3

但是就算是(a,b,c)，当遇到范围查询（例如 >、<、BETWEEN、LIKE）时，就会停止匹配。