实例详解MySQL索引最左匹配原则

文 / @WordPress主题

MySQL索引最左匹配原则是指,在联合索引中从最左边的字段开始匹配,若条件中字段在联合索引中符合从左到右的顺序则走索引,否则不走。

下面我们来看一个具体的例子。假设有一个表test,它有五个字段a、b、c、d、e,其中a为主键,有一个由b、c、d组成的联合索引。如果执行SELECT b, c, d FROM test WHERE d=2这个SQL语句,就会发现它的输出如下:

id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
---+-------------+-------+------------+------+---------------+-----+---------+-----+------+----------+-------
1 | SIMPLE | test | NULL | ref | idx_bcd | idx_bcd | 4 | const | 1 | 100.00 | Using index

可以看到,即使它不符合最左匹配原则,它依然使用了索引(使用了idx_bcd),这是为什么呢?

其实,这是由于索引覆盖造成的。覆盖索引是当查询的字段在联合索引中时,可以不回表,只需要查看一次索引树就行了,这就是索引覆盖。在这个例子中,我们建立了(b, c, d)的联合索引,因此当我们查询的字段在b、c、d中的时候,就不需要回表,只需要查看一次索引树就可以了。

需要注意的是,当最左列有序时,右边的索引列有序才能保证,所以我们需要遵守最左匹配原则。

最后,我们总结如下规则:若符合最左覆盖原则,则走ref这种索引;若不符合最左匹配原则,但是符合覆盖索引(index),就可以扫描整个索引树,从而找到覆盖索引对应的列,避免回表;若不符合最左匹配原则,也不符合覆盖索引,则需要扫描整个索引树,并且回表查询行记录,此时会走全表扫描,效率比较低。

因此,在设计表结构时,我们需要注意建立符合最左匹配原则的联合索引,以减少查询的开销。同时,我们也可以使用覆盖索引和优化查询语句,以进一步提高查询效率。

添加UTHEME为好友
扫码添加UTHEME微信为好友
· 分享WordPress相关技术文章,主题上新与优惠动态早知道。
· 微信端最大WordPress社群,限时免费入群。