精华内容
下载资源
问答
  • 最左匹配
    千次阅读
    2021-01-16 22:56:33

    为什么会有这个 最左匹配原则?

    答 : mysql 底层使用的索引是 B+ 树 , B+ 树的存放方式是 从左到右依次有序【特定的结构】,我们写sql时用到联合索引,按照索引的数据结构,按照特定的方式写查询 sql 的条件,最大化的提高查询速度。

    最左匹配原则是什么?

    简单来讲:在联合索引中,只有左边的字段被用到,右边的才能够被使用到。

    左边是带头大哥, 必须在

    假如我们创建联合索引 create index idx_a_b on shopTable(a,b);

    有如下B+树

    在这里插入图片描述

    我们看到 最左边的a 都是有序的,分别是 : 1,1,2,2,3,3 但是右边的b 不一定有序: 1,2,1,4,3,2

    但是在a都为 1 的情况下 b是有序的, 如: a=1时 b =1,2 ; a=2时, b= 1,4; a=3时 ,b=1,2;

    如果我们筛选数据的时候, 直接筛选b ,整个就是无序的,需要做全表扫描

    如果先a,再b 那么 ,就可以利用树来加快查找速度。

    联合索引失效的情形

    即不满足最左匹配原则

    假如建立如下索引

    create index idx_name_age on admin(name,age);
    
    mysql> show index from admin;
    +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | Table | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
    +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | admin |          0 | PRIMARY      |            1 | admin_id    | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
    | admin |          1 | idx_name_age |            1 | name        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
    | admin |          1 | idx_name_age |            2 | age         | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
    +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    
    
    • name和age都走了索引的情况
    mysql> explain select * from admin where name='1' and age=2;
    +----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------+
    | id | select_type | table | partitions | type | possible_keys | key          | key_len | ref         | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------+
    |  1 | SIMPLE      | admin | NULL       | ref  | idx_name_age  | idx_name_age | 44      | const,const |    1 |   100.00 | NULL  |
    +----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)
    
    • 单查age , 未用到索引
    mysql> explain select * from admin where age=2;
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | admin | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)
    
    • 单查name, 用到了索引
    mysql> explain select * from admin where name='1';
    +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
    | id | select_type | table | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
    |  1 | SIMPLE      | admin | NULL       | ref  | idx_name_age  | idx_name_age | 39      | const |    1 |   100.00 | NULL  |
    +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)
    

    可以看到, 使用了左边字段 name查 ,可以使用索引, 用了右面的age查,无法用到索引

    这里只列举这一种索引失效的情况 ,其余还有: 范围值之后失效

    • 这种情况下: 由于name使用了范围索引, 导致后面的age 没有走索引
    mysql> explain select * from admin where name > '1' and age = 1;
    +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
    | id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                 |
    +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
    |  1 | SIMPLE      | admin | NULL       | range | idx_name_age  | idx_name_age | 39      | NULL |    1 |   100.00 | Using index condition |
    +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
    1 row in set, 1 warning (0.00 sec)
    
    更多相关内容
  • 在mysql建立联合索引时会遵循最左前缀匹配的原则,即最左优先,在检索数据时从联合索引的左边开始匹配,示例: 对列col1、列col2和列col3建一个联合索引 KEY test_col1_col2_col3 on test(col1,col2,col3); 联合...
  • MySQL索引之最左匹配原则

    千次阅读 2022-05-09 19:13:08
    本文介绍MySQL最左匹配原则的详细工作机制。

    简介

    这篇文章的初衷是很多文章都告诉你最左匹配原则,却没有告诉你,实际场景下它到底是如何工作的,本文就是为了阐述清这个问题。

    准备

    为了方面后续的说明,我们首先建立一个如下的表(MySQL5.7),表中共有5个字段(abcde),其中a为主键,有一个由bcd组成的联合索引,存储引擎为InnoDB,插入三条测试数据。强烈建议自己在MySQL中尝试本文的所有语句。

    CREATE TABLE `test` (
      `a` int NOT NULL AUTO_INCREMENT,
      `b` int DEFAULT NULL,
      `c` int DEFAULT NULL,
      `d` int DEFAULT NULL,
      `e` int DEFAULT NULL,
      PRIMARY KEY(`a`),
      KEY `idx_abc` (`b`,`c`,`d`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
    
    INSERT INTO test(`a`, `b`, `c`, `d`, `e`) VALUES (1, 2, 3, 4, 5);
    INSERT INTO test(`a`, `b`, `c`, `d`, `e`) VALUES (2, 2, 3, 4, 5);
    INSERT INTO test(`a`, `b`, `c`, `d`, `e`) VALUES (3, 2, 3, 4, 5);
    

    这时候,我们如果执行下面这个SQL语句,你觉得会走索引吗?

    SELECT b, c, d FROM test WHERE d = 2;
    

    如果你按照最左匹配原则(简述为在联合索引中,从最左边的字段开始匹配,若条件中字段在联合索引中符合从左到右的顺序则走索引,否则不走,可以简单理解为(a, b, c)的联合索引相当于创建了a索引、(a, b)索引和(a, b, c)索引),这句显然是不符合这个规则的,它走不了索引,但是我们用EXPLAIN语句分析,会发现一个很有趣的现象,它的输出如下是使用了索引的。

    在这里插入图片描述
    这就很奇怪了,最左匹配原则失效了吗?事实上,并没有,我们一步步来分析。

    理论详解

    由于现在基本上以InnoDB引擎为主,我们以InnoDB为例进行主要说明。

    聚集索引和非聚集索引

    MySQL底层使用B+树来存储索引,数据均存在叶子节点上。对于InnoDB而言,主键索引和行记录时存储在一起的,因此叫做聚集索引(clustered index)。除了聚集索引,其他所有都叫做非聚集索引(secondary index),包括普通索引、唯一索引等。

    在InnoDB中,只存在一个聚集索引:

    • 若表存在主键,则主键索引就是聚集索引;
    • 若表不存在主键,则会把第一个非空的唯一索引作为聚集索引;
    • 否则,会隐式定义一个rowid作为聚集索引。

    我们以下图为例,假设现在有一个表,存在id、name、age三个字段,其中id为主键,因此id为聚集索引,name建立索引为非聚集索引。关于id和name的索引,有如下的B+树,可以看到,聚集索引的叶子节点存储的是主键和行记录,非聚集索引的叶子节点存储的是主键。

    在这里插入图片描述

    回表查询

    从上面的索引存储结构来看,我们可以看到,在主键索引树上,通过主键就可以一次性查出我们所需要的数据,速度很快。这很直观,因为主键就和行记录存储在一起,定位到了主键就定位到了所要找的包含所有字段的记录。

    但是对于非聚集索引,如上面的右图,我们可以看到,需要先根据name所在的索引树找到对应主键,然后通过主键索引树查询到所要的记录,这个过程叫做回表查询。

    索引覆盖

    上面的回表查询无疑会降低查询的效率,那么有没有办法让它不回表呢?这就是索引覆盖。所谓索引覆盖,就是说,在使用这个索引查询时,使它的索引树的叶子节点上的数据可以覆盖你查询的所有字段,就可以避免回表了。我们回到一开始的例子,我们建立的(b,c,d)的联合索引,因此当我们查询的字段在b、c、d中的时候,就不会回表,只需要查看一次索引树,这就是索引覆盖。

    最左匹配原则

    指的是联合索引中,优先走最左边列的索引。对于多个字段的联合索引,也同理。如 index(a,b,c) 联合索引,则相当于创建了 a 单列索引,(a,b)联合索引,和(a,b,c)联合索引。

    我们可以执行下面的几条语句验证一下这个原则。

    EXPLAIN SELECT * FROM test WHERE b = 1;
    

    在这里插入图片描述

    EXPLAIN SELECT * FROM test WHERE b = 1 and c = 2;
    

    在这里插入图片描述

    EXPLAIN SELECT * FROM test WHERE b = 1 and c = 2 and d = 3;
    

    在这里插入图片描述

    接着,我们尝试一条不符合最左原则的查询,它也如图预期一样,走了全表扫描。

    EXPLAIN SELECT * FROM test WHERE d = 3;
    

    在这里插入图片描述

    详细规则

    我们先来看下面两个语句,他们的输出如下。

    EXPLAIN SELECT b, c from test WHERE b = 1 and c = 1;
    EXPLAIN SELECT b, d from test WHERE d = 1;
    
    id|select_type|table|partitions|type|possible_keys|key    |key_len|ref        |rows|filtered|Extra      |
    --+-----------+-----+----------+----+-------------+-------+-------+-----------+----+--------+-----------+
     1|SIMPLE     |test |          |ref |idx_bcd      |idx_bcd|10     |const,const|   1|   100.0|Using index|
    i
    d|select_type|table|partitions|type |possible_keys|key    |key_len|ref|rows|filtered|Extra                   |
    --+-----------+-----+----------+-----+-------------+-------+-------+---+----+--------+------------------------+
     1|SIMPLE     |test |          |index|idx_bcd      |idx_bcd|15     |   |   3|   33.33|Using where; Using index|
    

    显然第一条语句是符合最左匹配的,因此type为ref,但是第二条并不符合最左匹配,但是也不是全表扫描,这是因为此时这表示扫描整个索引树。

    具体来看,index 代表的是会对整个索引树进行扫描,如例子中的,列 d,就会导致扫描整个索引树。ref 代表 mysql 会根据特定的算法查找索引,这样的效率比 index 全扫描要高一些。但是,它对索引结构有一定的要求,索引字段必须是有序的。而联合索引就符合这样的要求,联合索引内部就是有序的,你可以理解为order by b,c,d这种排序规则,先根据字段b排序,再根据字段c排序,以此类推。这也解释了,为什么需要遵守最左匹配原则,当最左列有序才能保证右边的索引列有序。

    因此,我们总结最后的原则为,若符合最左覆盖原则,则走ref这种索引;若不符合最左匹配原则,但是符合覆盖索引(index),就可以扫描整个索引树,从而找到覆盖索引对应的列,避免回表;若不符合最左匹配原则,也不符合覆盖索引(如本例的select *),则需要扫描整个索引树,并且回表查询行记录,此时,查询优化器认为这样两次查找索引树,还不如全表扫描来得快(因为联合索引此时不符合最左匹配原则,要不普通索引查询慢得多),因此,此时会走全表扫描。

    展开全文
  • MySQL最左匹配原则,道儿上兄弟都得知道的原则

    万次阅读 多人点赞 2020-09-11 19:31:00
    目录一、最左匹配原则的原理二、违背左原则导致索引失效的情况三、查询优化器偷偷干了哪些事儿四、需要你mark的知识点1、如何通过有序索引排序,避免冗余执行order by2、like 语句的索引问题3、不要在列上进行运算...

      自MySQL5.5版本起,主流的索引结构转为B+树。B+树的节点存储索引顺序是从左向右存储,在检索匹配的时候也要满足自左向右匹配。

      通常我们在建立联合索引的时候,相信建立过索引的同学们会发现,无论是Oracle还是 MySQL 都会让我们选择索引的顺序,比如我们想在a,b,c三个字段上建立一个联合索引,我们可以选择自己想要的优先级,(a、b、c),或是 (b、a、c) 或者是(c、a、b) 等顺序。
    在这里插入图片描述

      为什么数据库会让我们选择字段的顺序呢?不都是三个字段的联合索引么?这里就引出了数据库索引的最重要的原则之一,最左匹配原则

      在我们开发中经常会遇到这种问题,明明这个字段建了联合索引,但是SQL查询该字段时却不会使用这个索引。难道这索引是假的?白嫖老子资源?!

    比如索引abc_index:(a,b,c)是a,b,c三个字段的联合索引,下列sql执行时都无法命中索引abc_index;

    select * from table where c = '1';
    
    select * from table where b ='1' and c ='2';
    

    以下三种情况却会走索引:

    select * from table where a = '1';
    
    select * from table where a = '1' and b = '2';
    
    select * from table where a = '1' and b = '2'  and c='3';
    

      从上面两个例子大家有木有看出点眉目呢?

      是的,索引abc_index:(a,b,c),只会在where条件中带有(a)、(a,b)、(a,b,c)的三种类型的查询中使用。其实这里说的有一点歧义,其实当where条件只有(a,c)时也会走,但是只走a字段索引,不会走c字段。

      那么这都是为什么呢?我们一起来看看其原理吧。

    一、最左匹配原则的原理

    MySQL 建立多列索引(联合索引)有最左匹配的原则,即最左优先:
    如果有一个 2 列的索引 (a, b),则已经对 (a)、(a, b) 上建立了索引;
    如果有一个 3 列索引 (a, b, c),则已经对 (a)、(a, b)、(a, b, c) 上建立了索引;

    假设数据 表 LOL (id,sex,price,name) 的物理位置(表中的无序数据)如下:
    (注:下面数据是测试少量数据选用的,只为了方便大家看清楚。实际操作中,应按照使用频率、数据区分度来综合设定索引顺序喔~)

    主键id  sex(a)   price(b)      name(c)    
    (1)     1         1350         AAA安妮
    (2)     2         6300         MMM盲僧
    (3)     1         3150         NNN奈德丽
    (4)     2         6300         CCC锤石
    (5)     1         6300         LLL龙女
    (6)     2         3150         EEE伊泽瑞尔
    (7)     2         6300         III艾克
    (8)     1         6300         BBB暴走萝莉
    (9)     1         4800         FFF发条魔灵
    (10)    2         3150         KKK卡牌大师
    (11)    1         450          HHH寒冰射手
    (12)    2         450          GGG盖伦
    (13)    2         3150         OOO小提莫
    (14)    2         3150         DDD刀锋之影
    (15)    2         6300         JJJ疾风剑豪
    (16)    2         450          JJJ剑圣
    

      当你在LOL表创建一个联合索引 abc_index:(sex,price,name)时,生成的索引文件逻辑上等同于下表内容(分级排序)

    sex(a)   price(b)       name(c)         主键id
    1        450            HHH寒冰射手      (11)
    1        1350           AAA安妮          (1)
    1        3150           NNN奈德丽        (3)
    1        4800           FFF发条魔灵       (9)
    1        6300           BBB暴走萝莉       (8)
    1        6300           LLL龙女          (5)
    2        450            GGG盖伦          (12)
    2        450            JJJ剑圣          (16)
    2        3150           DDD刀锋之影       (14)
    2        3150           EEE伊泽瑞尔       (6)
    2        3150           KKK卡牌大师       (10)
    2        3150           OOO小提莫         (13)
    2        6300           CCC锤石          (4)
    2        6300           III艾克          (7)
    2        6300           JJJ疾风剑豪       (15)
    2        6300           MMM盲僧          (2)
    

      小伙伴儿们有没有发现B+树联合索引的规律?感觉还有点模糊的话,那咱们再来看一张索引存储数据的结构图,或许更明了一些。

    在这里插入图片描述
      这是一张来自思否上的图片,层次感很清晰,小伙伴可以看到,对于B+树中的联合索引,每级索引都是排好序的。联合索引 bcd_index:(b,c,d) , 在索引树中的样子如图 , 在比较的过程中 ,先判断 b 再判断 c 然后是 d 。

      由上图可以看出,B+ 树的数据项是复合的数据结构,同样,对于我们这张表的联合索引 (sex,price,name)来说 ,B+ 树也是按照从左到右的顺序来建立搜索树的,当SQL如下时:

    select sex,price,name from LOL where sex = 2 and price = 6300 and name = 'JJJ疾风剑豪'; 
    

      B+ 树会优先比较 sex 来确定下一步的指针所搜方向,如果 sex 相同再依次比较 price 和 name,最后得到检索的数据;

    二、违背最左原则导致索引失效的情况

    (下面以联合索引 abc_index:(a,b,c) 来进行讲解,便于理解)
    1、查询条件中,缺失优先级最高的索引 “a”
      当 where b = 6300 and c = 'JJJ疾风剑豪' 这种没有以 a 为条件来检索时;B+树就不知道第一步该查哪个节点,从而需要去全表扫描了(即不走索引)。因为建立搜索树的时候 a 就是第一个比较因子,必须要先根据 a 来搜索,进而才能往后继续查询b 和 c,这点我们通过上面的存储结构图可以看明白。

    2、查询条件中,缺失优先级居中的索引 “b”
      当 where a =1 and c =“JJJ疾风剑豪” 这样的数据来检索时;B+ 树可以用 a 来指定第一步搜索方向,但由于下一个字段 b 的缺失,所以只能把 a = 1 的数据主键ID都找到,通过查到的主键ID回表查询相关行,再去匹配 c = ‘JJJ疾风剑豪’ 的数据了,当然,这至少把 a = 1 的数据筛选出来了,总比直接全表扫描好多了。

      这就是MySQL非常重要的原则,即索引的最左匹配原则。

    三、查询优化器偷偷干了哪些事儿

    当对索引中所有列通过"=" 或 “IN” 进行精确匹配时,索引都可以被用到。

    1、如果建的索引顺序是 (a, b)。而查询的语句是 where b = 1 AND a = ‘陈哈哈’; 为什么还能利用到索引?

      理论上索引对顺序是敏感的,但是由于 MySQL 的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引,所以 MySQL 不存在 where 子句的顺序问题而造成索引失效。当然了,SQL书写的好习惯要保持,这也能让其他同事更好地理解你的SQL。

    2、还有一个特殊情况说明下,下面这种类型的SQL, a 与 b 会走索引,c不会走。

    select * from LOL where a = 2 and b > 1000  and c='JJJ疾风剑豪';
    

      对于上面这种类型的sql语句;mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配(包括like '陈%'这种)。在a、b走完索引后,c已经是无序了,所以c就没法走索引,优化器会认为还不如全表扫描c字段来的快。所以只使用了(a,b)两个索引,影响了执行效率。

      其实,这种场景可以通过修改索引顺序为 abc_index:(a,c,b),就可以使三个索引字段都用到索引,建议小伙伴们不要有问题就想着新增索引哦,浪费资源还增加服务器压力。

      综上,如果通过调整顺序,就可以解决问题或少维护一个索引,那么这个顺序往往就是我们DBA人员需要优先考虑采用的。

    四、需要你mark的知识点

    1、如何通过有序索引排序,避免冗余执行order by

    order by用在select语句中,具备排序功能。如:

    SELECT sex, price, name FROM LOL ORDER BY sex;
    

    是将表 LOL 中的数据按 “sex” 一列排序。

      而只有当order by 与where 语句同时出现,order by的排序功能无效。换句话说,order by 中的字段在执行计划中利用了索引时,不用排序操作。如下SQL时,不会按 sex 一列排序,因为 sex 本身已经是有序的了。

    SELECT sex, price, name FROM LOL where sex = 1 ORDER BY sex ;
    

      所以,只有order by 字段出现在where条件中时,才会利用该字段的索引而避免排序。

    对于上面的语句,数据库的处理顺序是:

    • 第一步:根据where条件和统计信息生成执行计划,得到数据。

    • 第二步:将得到的数据排序。当执行处理数据(order by)时,数据库会先查看第一步的执行计划,看order by 的字段是否在执行计划中利用了索引。如果是,则可以利用索引顺序而直接取得已经排好序的数据。如果不是,则排序操作。

    • 第三步:返回排序后的数据。

    2、like 语句的索引问题

      如果通配符 % 不出现在开头,则可以用到索引,但根据具体情况不同可能只会用其中一个前缀,在 like “value%” 可以使用索引,但是 like “%value%” 违背了最左匹配原则,不会使用索引,走的是全表扫描。

    3、不要在列上进行运算

      如果查询条件中含有函数或表达式,将导致索引失效而进行全表扫描
    例如 :

    select * from user where YEAR(birthday) < 1990
    

    可以改造成:

     select * from users where birthday <1990-01-01

    4、索引不会包含有 NULL 值的列

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

    5、尽量选择区分度高的列作为索引

      区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是 1,而一些状态、性别字段可能在大数据面前区分度就是 0。一般需要 join 的字段都要求区分度 0.1 以上,即平均 1 条扫描 10 条记录

    6、覆盖索引的好处

      如果一个索引包含所有需要的查询的字段的值,我们称之为覆盖索引。覆盖索引是非常有用的工具,能够极大的提高性能。因为,只需要读取索引,而无需读表,极大减少数据访问量,这也是不建议使用Select * 的原因。

    展开全文
  • MySQL 最左匹配原则

    千次阅读 2021-06-06 10:56:18
    通常我们在建立联合索引的时候,...这里就引出了数据库索引的重要的原则之一,最左匹配原则。 在我们开发中经常会遇到这种问题,明明这个字段建了联合索引,但是SQL查询该字段时却不会使用这个索引。难道这索引是假

    通常我们在建立联合索引的时候,相信建立过索引的同学们会发现,无论是Oracle还是 MySQL 都会让我们选择索引的顺序,比如我们想在a,b,c三个字段上建立一个联合索引,我们可以选择自己想要的优先级,(a、b、c),或是 (b、a、c) 或者是(c、a、b) 等顺序。
    在这里插入图片描述
    为什么数据库会让我们选择字段的顺序呢?不都是三个字段的联合索引么?这里就引出了数据库索引的最重要的原则之一,最左匹配原则。

    在我们开发中经常会遇到这种问题,明明这个字段建了联合索引,但是SQL查询该字段时却不会使用这个索引。难道这索引是假的?白嫖老子资源?!
      比如索引abc_index:(a,b,c)是a,b,c三个字段的联合索引,下列sql执行时都无法命中索引abc_index;

    select * from table where c = '1';
    
    select * from table where b ='1' and c ='2';
    

    以下三种情况却会走索引:

    select * from table where a = '1';
    
    select * from table where a = '1' and b = '2';
    
    select * from table where a = '1' and b = '2'  and c='3';
    

    从上面两个例子大家有木有看出点眉目呢?

    是的,索引abc_index:(a,b,c),只会在where条件中带有(a)、(a,b)、(a,b,c)的三种类型的查询中使用。其实这里说的有一点歧义,其实当where条件只有(a,c)时也会走,但是只走a字段索引,不会走c字段。

    那么这都是为什么呢?我们一起来看看其原理吧。

    一、最左匹配原则的原理

    MySQL 建立多列索引(联合索引)有最左匹配的原则,即最左优先:
    如果有一个 2 列的索引 (a, b),则已经对 (a)、(a, b) 上建立了索引;
    如果有一个 3 列索引 (a, b, c),则已经对 (a)、(a, b)、(a, b, c) 上建立了索引;

    假设数据 表 LOL (id,sex,price,name) 的物理位置(表中的无序数据)如下:
    (注:下面数据是测试少量数据选用的,只为了方便大家看清楚。实际操作中,应按照使用频率、数据区分度来综合设定索引顺序喔~)

    主键id  sex(a)   price(b)      name(c)    
    (1)     1         1350         AAA安妮
    (2)     2         6300         MMM盲僧
    (3)     1         3150         NNN奈德丽
    (4)     2         6300         CCC锤石
    (5)     1         6300         LLL龙女
    (6)     2         3150         EEE伊泽瑞尔
    (7)     2         6300         III艾克
    (8)     1         6300         BBB暴走萝莉
    (9)     1         4800         FFF发条魔灵
    (10)    2         3150         KKK卡牌大师
    (11)    1         450          HHH寒冰射手
    (12)    2         450          GGG盖伦
    (13)    2         3150         OOO小提莫
    (14)    2         3150         DDD刀锋之影
    (15)    2         6300         JJJ疾风剑豪
    (16)    2         450          JJJ剑圣
    

    当你在LOL表创建一个联合索引 abc_index:(sex,price,name)时,生成的索引文件逻辑上等同于下表内容(分级排序):

    sex(a)   price(b)       name(c)         主键id
    1        450            HHH寒冰射手      (11)
    1        1350           AAA安妮          (1)
    1        3150           NNN奈德丽        (3)
    1        4800           FFF发条魔灵       (9)
    1        6300           BBB暴走萝莉       (8)
    1        6300           LLL龙女          (5)
    2        450            GGG盖伦          (12)
    2        450            JJJ剑圣          (16)
    2        3150           DDD刀锋之影       (14)
    2        3150           EEE伊泽瑞尔       (6)
    2        3150           KKK卡牌大师       (10)
    2        3150           OOO小提莫         (13)
    2        6300           CCC锤石          (4)
    2        6300           III艾克          (7)
    2        6300           JJJ疾风剑豪       (15)
    2        6300           MMM盲僧          (2)
    

    小伙伴儿们有没有发现B+树联合索引的规律?感觉还有点模糊的话,那咱们再来看一张索引存储数据的结构图,或许更明了一些。
    在这里插入图片描述
    这是一张来自思否上的图片,层次感很清晰,小伙伴可以看到,对于B+树中的联合索引,每级索引都是排好序的。联合索引 bcd_index:(b,c,d) , 在索引树中的样子如图 , 在比较的过程中 ,先判断 b 再判断 c 然后是 d 。

    由上图可以看出,B+ 树的数据项是复合的数据结构,同样,对于我们这张表的联合索引 (sex,price,name)来说 ,B+ 树也是按照从左到右的顺序来建立搜索树的,当SQL如下时:

    select sex,price,name from LOL where sex = 2 and price = 6300 and name = 'JJJ疾风剑豪'; 
    

    B+ 树会优先比较 sex 来确定下一步的指针所搜方向,如果 sex 相同再依次比较 price 和 name,最后得到检索的数据;

    二、违背最左原则导致索引失效的情况

    (下面以联合索引 abc_index:(a,b,c) 来进行讲解,便于理解)
    1、查询条件中,缺失优先级最高的索引 “a”
      当 where b = 6300 and c = ‘JJJ疾风剑豪’ 这种没有以 a 为条件来检索时;B+树就不知道第一步该查哪个节点,从而需要去全表扫描了(即不走索引)。因为建立搜索树的时候 a 就是第一个比较因子,必须要先根据 a 来搜索,进而才能往后继续查询b 和 c,这点我们通过上面的存储结构图可以看明白。

    2、查询条件中,缺失优先级居中的索引 “b”
      当 where a =1 and c =“JJJ疾风剑豪” 这样的数据来检索时;B+ 树可以用 a 来指定第一步搜索方向,但由于下一个字段 b 的缺失,所以只能把 a = 1 的数据主键ID都找到,通过查到的主键ID回表查询相关行,再去匹配 c = ‘JJJ疾风剑豪’ 的数据了,当然,这至少把 a = 1 的数据筛选出来了,总比直接全表扫描好多了。

    这就是MySQL非常重要的原则,即索引的最左匹配原则。

    三、查询优化器偷偷干了哪些事儿

    当对索引中所有列通过"=" 或 “IN” 进行精确匹配时,索引都可以被用到。

    1、如果建的索引顺序是 (a, b)。而查询的语句是 where b = 1 AND a = ‘陈哈哈’; 为什么还能利用到索引?

    理论上索引对顺序是敏感的,但是由于 MySQL 的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引,所以 MySQL 不存在 where 子句的顺序问题而造成索引失效。当然了,SQL书写的好习惯要保持,这也能让其他同事更好地理解你的SQL。

    2、还有一个特殊情况说明下,下面这种类型的SQL, a 与 b 会走索引,c不会走

    select * from LOL where a = 2 and b > 1000  and c='JJJ疾风剑豪';
    

    对于上面这种类型的sql语句;mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配(包括like '陈%'这种)。在a、b走完索引后,c已经是无序了,所以c就没法走索引,优化器会认为还不如全表扫描c字段来的快。所以只使用了(a,b)两个索引,影响了执行效率。

    其实,这种场景可以通过修改索引顺序为 abc_index:(a,c,b),就可以使三个索引字段都用到索引,建议小伙伴们不要有问题就想着新增索引哦,浪费资源还增加服务器压力。

    综上,如果通过调整顺序,就可以解决问题或少维护一个索引,那么这个顺序往往就是我们DBA人员需要优先考虑采用的。

    四、需要你mark的知识点

    1、如何通过有序索引排序,避免冗余执行order by

    order by用在select语句中,具备排序功能。如:

    SELECT sex, price, name FROM LOL ORDER BY sex;
    

    是将表 LOL 中的数据按 “sex” 一列排序。

    而只有当order by 与where 语句同时出现,order by的排序功能无效。换句话说,order by 中的字段在执行计划中利用了索引时,不用排序操作。如下SQL时,不会按 sex 一列排序,因为 sex 本身已经是有序的了。

    SELECT sex, price, name FROM LOL where sex = 1 ORDER BY sex ;
    

    所以,只有order by 字段出现在where条件中时,才会利用该字段的索引而避免排序。

    对于上面的语句,数据库的处理顺序是:

    • 第一步:根据where条件和统计信息生成执行计划,得到数据。

    • 第二步:将得到的数据排序。当执行处理数据(order by)时,数据库会先查看第一步的执行计划,看order by 的字段是否在执行计划中利用了索引。如果是,则可以利用索引顺序而直接取得已经排好序的数据。如果不是,则排序操作。

    • 第三步:返回排序后的数据。

    2、like 语句的索引问题

    如果通配符 % 不出现在开头,则可以用到索引,但根据具体情况不同可能只会用其中一个前缀,在 like “value%” 可以使用索引,但是 like “%value%” 违背了最左匹配原则,不会使用索引,走的是全表扫描。

    3、不要在列上进行运算

    如果查询条件中含有函数或表达式,将导致索引失效而进行全表扫描
    例如 :

    select * from user where YEAR(birthday) < 1990
    

    可以改造成:

     select * from users where birthday <1990-01-01

    4、索引不会包含有 NULL 值的列

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

    5、尽量选择区分度高的列作为索引

    区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是 1,而一些状态、性别字段可能在大数据面前区分度就是 0。一般需要 join 的字段都要求区分度 0.1 以上,即平均 1 条扫描 10 条记录

    6、覆盖索引的好处

    如果一个索引包含所有需要的查询的字段的值,我们称之为覆盖索引。覆盖索引是非常有用的工具,能够极大的提高性能。因为,只需要读取索引,而无需读表,极大减少数据访问量,这也是不建议使用Select * 的原因。

    展开全文
  • 联合索引-最左匹配原则

    千次阅读 2022-03-28 13:50:27
    三、总结: 1)为什么最左匹配: 是因为mysql创建联合索引时,首先会对左边字段排序,也就是第一个字段,然后再在保证第一个字段有序的情况下,再排序第二个字段,以此类推。 所以联合索引左列是绝对有序的,...
  • Mysql最左匹配原则

    千次阅读 2022-01-15 23:06:56
    原文链接:Mysql最左匹配原则_Summer的博客-CSDN博客_最左匹配原则 ⛽️ 原文链接:Mysql最左匹配原则_Summer的博客-CSDN博客_最左匹配原则 索引的底层是一颗B+树,构建一颗B+树只能根据一个值来构建。...
  • MySQL最左匹配原则

    千次阅读 2021-02-06 00:59:04
    情况一:where a>1 and b=2,由于a>1的情况下,b是无序的,所以a能用到索引,b用不到 情况二:where a=1 and b=2,由于a=1的情况下,b是有序的,所以a,b都能用到索引 最左匹配原则:左优先,遇到范围查询会停止...
  • MySQL索引最左匹配原则

    千次阅读 2022-02-10 15:52:06
    MySQL索引最左匹配原则
  • 联合索引最左匹配原则

    千次阅读 2021-03-20 19:41:24
    联合索引最左匹配原则的成因 联合索引是指 将多个列 一起设置成一个索引,例如:将a,b设置成联合索引,则命中索引规则如下: where a=6 走索引 where a=6,b=1 ,走索引 where b=1 , 不走索引 where a like ‘a%’ ,...
  • mysql的最左匹配原则

    千次阅读 2021-08-26 09:58:11
    目录 一、原理 ...B+树的节点存储引擎顺序是从到右存储,在检索匹配的时候也要满足从到右匹配。 一、原理 比如一个表有a,b,c 三个字段,然后建立联合索引 index(a,b,c) 注意这里索引字段的顺序
  • MySql 索引的最左匹配原则举例详解

    千次阅读 2022-04-04 12:35:38
    最左匹配原则 最左匹配原则就是指在联合索引中,如果你的 SQL 语句中用到了联合索引中的左边的索引,那么这条 SQL 语句就可以利用这个联合索引去进行匹配。 例如某表现有索引(a,b,c),现在你有如下语句: select *...
  • 索引最左匹配原则

    千次阅读 2021-01-14 15:23:58
    写在前面:我在上大学的时候就听说过数据库的最左匹配原则,当时是通过各大博客论坛了解的,但是这些博客的局限性在于它们对最左匹配原则的描述就像一些数学定义一样,往往都是列出123点,满足这123点就能匹配上索引...
  • 索引是基于B+树实现,所以这个最左匹配原则肯定要站在B+树的角度上来思考。 首先我们看一下单个索引的B+树: 如上图,一颗B+树根据一个值来构建,很容易理解索引的使用。 假如是联合索引的话,我们可以想象一下...
  • 深入浅析Mysql联合索引原理 之 最左匹配原则。

    万次阅读 多人点赞 2019-04-28 11:38:46
    前言 之前在网上看到过很多关于mysql联合...在mysql建立联合索引时会遵循最左前缀匹配的原则,即最左优先,在检索数据时从联合索引的左边开始匹配, 示例: CREATE TABLE `student` ( `Id` int(11) unsign...
  • 最左匹配原则的底层原理

    千次阅读 多人点赞 2020-09-10 00:55:52
    什么是最左匹配原则 顾名思义:左优先,以左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。 例如:如果建立(a,b)顺序的索引,我们的条件只有b=xxx,是匹配不...
  • Mysql索引失效原理与最左匹配原则

    千次阅读 2020-05-27 21:23:38
    构建一颗B+树只能根据一个值来构建,因此数据库依据联合索引最左的字段来构建B+树。 例子:假如创建一个(a,b)的联合索引,那么它的索引树是这样的 可以看到a的值是有顺序的,1,1,2,2,3,3,而b的值是没有顺序...
  • 数据库索引最左匹配原则

    千次阅读 2020-07-26 18:48:50
    索引最左匹配原则 建立联合索引时会遵循最左匹配原则,即左优先,在检索数据时从联合索引的左边开始匹配 例如: 为user表中的name、address、phone列添加联合索引 ALTER TABLE user ADD INDEX index_three(name...
  • 最左匹配原则及其成因

    千次阅读 2019-08-22 23:34:04
    二、最左匹配原则的概念 三、最左匹配原则的成因 一、案例分析 1.假设数据库存在一个联合索引键: 2.使用查询语言 3.使用explain分析: 发现走的是联合索引键 4.如果只查询area 发现,仍旧走的是...
  • Mysql 索引失效-最左匹配原则 我们在建立复合索引的时候,常常会出现索引失效的问题。这时,我们可能就违背了左原则。 例如:我创建的学生表 创建了一个组合索引(N_STUDENT_ID,VC_STUDENT_CODE,VC_STUDENT_NAME) ...
  • 面试题:最左匹配原则

    千次阅读 2020-08-22 00:36:10
    那么当SQL中对应有:a或a,b或a,b,c的时候,可称为完全满足最左原则;当SQL中查询条件对应只有a,c的时候,可称为部分满足最左原则;当SQL中没有a的时候,可称为不满足最左原则。 注:MySQL5.7开始,会自动优化,...
  • Mysql索引的最左匹配原则原理

    千次阅读 2019-03-02 15:39:59
    总所周知,mysql的索引遵循的是最左匹配原则 那最左匹配原则在mysql里是怎么玩的? 假设我们在表中有2个字段,一共有6条数据,其中id的索引树见上图 假设我们现在要查id=6的数据 ...
  • MySQL索引最左匹配原则及优化原理

    千次阅读 2020-07-17 00:07:12
    2 最左前缀匹配 当查询条件精确匹配索引的左边连续一个或几个列时,如或,索引可以被用到,但是只能用到一部分,即条件所组成的最左前缀。 从分析结果看用到了PRIMARY索引,但是key_len为4,说明只用到了索引的第一...
  • 索引6:联合索引的最左匹配原则

    千次阅读 2020-02-13 18:17:48
    联合索引的最左匹配原则 什么是最左匹配原则? 举例 索引列A和列B 建立联合索引 index(A,B)(A,B顺序有序) explain select X from XX where A=’‘and B=’’ 走 ab索引 explain select X from XX where A=’‘走 ab...
  • Mysql复合索引最左匹配原则以及索引失效条件复合索引最左匹配原则用EXPLAIN 来查看语句是否用到了索引索引失效的条件一般性建议 复合索引最左匹配原则 复合索引又叫联合索引。两个或更多个列上的索引被称作复合索引...
  • MYSQL | 最左匹配原则

    千次阅读 2021-01-18 22:15:23
    最左匹配原则最左匹配原则就是指在联合索引中,如果你的 SQL 语句中用到了联合索引中的左边的索引,那么这条 SQL 语句就可以利用这个联合索引去进行匹配。例如某表现有索引(a,b,c),现在你有如下语句:select * ...
  • 关于SQL Server索引的最左匹配原则

    千次阅读 2019-09-18 06:59:10
    近期,在交流群中有网友谈到SQL Server索引的最左匹配原则,理解为T-SQL中Where条件的书写顺序的问题,这是一个误解。下面先看下实验结果。1、准备数据。CREATE TABLE [dbo].[t6]( [id] [int] IDENTITY(1,1) NOT ...
  • 什么是最左匹配原则 左优先,以查询条件左边为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。上面说到建立一个复合索引(col1,col2,col3)相当于建立了三个索引,但除此...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 595,125
精华内容 238,050
关键字:

最左匹配

友情链接: STV6120.zip