MySQL的最左原则

一、简述

  • MySQL索引的最左原则指的是,当使用多列索引时,MySQL会优先使用索引中最左边的列。如果查询条件中包含了索引的最左列,那么MySQL会使用这个索引来加速查询。
  • 更具体的描述:建立一个索引,对于索引中的字段,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配

二、实际场景

  1. 单一列索引:

    假设有一个表users,其中有一个age列,我们为这个列创建了一个索引idx_age。如果查询条件是WHERE age > 18,那么这个索引就会被用到。如果查询条件是WHERE name = 'John',那么这个索引就不会被用到,因为name列并没有被包含在索引中。

  2. 联合索引:

    假设有一个表users,其中有两个列agename,我们为这两个列创建了一个联合索引idx_age_name。如果查询条件是WHERE age > 18 AND name = 'John',那么这个索引就会被用到,因为查询条件中包含了索引的最左侧列age。如果查询条件是WHERE name = 'John' AND age > 18,那么这个索引就不会被用到,因为查询条件中没有包含索引的最左侧列age

  3. 前缀索引:

    假设有一个表products,其中有一个brand列,我们为这个列创建了一个前缀索引idx_brand_prefix。如果查询条件是WHERE brand LIKE 'A%',那么这个索引就会被用到,因为查询条件中包含了索引的最左侧连续若干列。如果查询条件是WHERE brand LIKE '%A',那么这个索引就不会被用到,因为查询条件中没有包含索引的最左侧列。


三、具体例子

  • 例子1:假设有一个索引idx(a, b, c),其中a是第一列,b是第二列,c是第三列。如果查询条件是WHERE a = 1 AND b = 2,那么MySQL会使用这个索引来加速查询。因为a是索引中最左边的列,它符合最左原则。

    但是,如果查询条件是WHERE b = 2 AND c = 3,那么MySQL不会使用idx(a, b, c)索引,因为它不满足最左原则。在这个情况下,MySQL可能会使用一个名为idx2(b, c)的另一个索引来加速查询。

  • 例子2:a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的(c中有>),如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整


四、注意事项

  1. 查询条件的顺序很重要。查询条件中要按照索引的最左列到最右列的顺序出现,才能利用索引进行加速查询。否则,索引就无法被用到。
  2. 单个字符的通配符可以使用索引。比如,查询条件是WHERE name LIKE 'A%',这样可以利用索引进行加速查询。但是,如果通配符出现在字符串的中间或者右侧,比如WHERE name LIKE '%A',那么就无法使用索引了。
  3. 不要在列上进行计算。如果查询条件中含有函数或者表达式,会导致索引失效而进行全表扫描。比如,查询条件是WHERE YEAR(birthday) < 1990,这样无法使用索引进行加速查询。正确的做法是,将函数或者表达式放到查询条件的右侧,比如WHERE birthday < '1990-01-01'
  4. 索引不会包含有NULL值的列。只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。因此在数据库设计时不要让字段的默认值为NULL。
  5. 尽量选择区分度高的列作为索引。区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0。覆盖索引是一个非常有用的工具,能够极大的提高性能。
  6. 最左原则仅适用于多列索引。对于单列索引,因为它本身就是最左的列,所以不需要使用最左原则。

热门相关:落地一把98K   重生之将门毒后   慕少,你老婆又重生了   重生之嫡女祸妃   重生之将门毒后