精华内容
下载资源
问答
  • 2022-03-14 16:05:03

    最左匹配原则就是指在联合索引中,如果你的 SQL 语句中用到了联合索引中的最左边的索引,那么这条 SQL 语句就可以利用这个联合索引去进行匹配。例如某表现有联合索引(a,b,c),现在你有如下语句:

    select * from t where a=1 and b=1 and c =1;     #这样可以利用到定义的索引(a,b,c),用上a,b,c
    
    select * from t where a=1 and b=1;     #这样可以利用到定义的索引(a,b,c),用上a,b
    
    select * from t where b=1 and a=1;     #这样可以利用到定义的索引(a,b,c),用上a,c(mysql有查询优化器)
    
    select * from t where a=1;     #这样也可以利用到定义的索引(a,b,c),用上a
    
    select * from t where b=1 and c=1;     #这样不可以利用到定义的索引(a,b,c)
    
    select * from t where a=1 and c=1;     #这样可以利用到定义的索引(a,b,c),但只用上a索引,b,c索引用不到
    

    在 InnoDB 中联合索引只有先确定了前一个(左侧的值)后,才能确定下一个值。如果有范围查询的话,那么联合索引中使用范围查询的字段后的索引在该条 SQL 中都不会起作用。
    值得注意的是,in 和 = 都可以乱序,比如有索引(a,b,c),语句 select * from t where c =1 and a=1 and b=1,这样的语句也可以用到最左匹配,因为 MySQL 中有一个优化器,他会分析 SQL 语句,将其优化成索引可以匹配的形式,即 select * from t where a =1 and a=1 and c=1

    更多相关内容
  • Mysql复合索引最左匹配原则以及索引失效条件复合索引最左匹配原则用EXPLAIN 来查看语句是否用到了索引索引失效的条件一般性建议 复合索引最左匹配原则 复合索引又叫联合索引。两个或更多个列上的索引被称作复合索引...

    复合索引最左匹配原则

    复合索引又叫联合索引。两个或更多个列上的索引被称作复合索引。

    对于复合索引:例如索引是key index (a,b,c)。可以支持a | a,b| a,c| a,b,c 组合进行查找,但不支持 b| c| b,c进行查找。就算是你弄乱了顺序如 c,b,a,mysql也会自动帮你改为a,b,c,然后用到索引 。这就是mysql最左匹配原则,查询条件里面要有复合索引最左边的那个字段才会用到索引。当最左侧字段是常量引用时,索引就十分有效。
    如我的表格设计是这样子:

    KEY `waybill_key` (`waybill_code`,`waybill_id`),
    

    这里给waybill_code和waybill_id添加了复合索引
    执行语句:

    select * from waybill where waybill_code = '2132131' 
    

    用到了索引(explain查询type为ref),但是执行语句:

    select * from waybill where waybill_id = '2132131' 
    

    并没有用到索引(explain查询type为all)

    所以说创建复合索引时,应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时,复合索引非常有用;仅对后面的任意列执行搜索时,复合索引则没有用处。

    用EXPLAIN 来查看语句是否用到了索引

    可以用 EXPLAIN 来查看索引是否生效,如:

    EXPLAIN select * from waybill where waybill_code = '%2132131%' 
    

    查询结果:
    在这里插入图片描述
    这里的type为ref说明已经用到了索引,这里附上这些type的说明:

    类型说明
    nullMYSQL不用访问表或者索引就直接能到结果
    system表只有一行,MyISAM引擎
    const常量连接,表最多只有一行匹配,通用用于主键或者唯一索引比较时
    eq_ref每次与之前的表合并行都只在该表读取一行,这是除了system,const之外最好的一种,特点是使用=,而且索引的所有部分都参与join且索引是主键或非空唯一键的索引
    ref如果每次只匹配少数行,那就是比较好的一种,使用=或<=>,可以是左覆盖索引或非主键或非唯一键
    fulltext全文搜索
    ref_or_null与ref类似,但包括NULL
    index_merge表示出现了索引合并优化(包括交集,并集以及交集之间的并集),但不包括跨表和全文索引。这个比较复杂,目前的理解是合并单表的范围索引扫描(如果成本估算比普通的range要更优的话)
    unique_subquery在in子查询中,就是value in (select…)把形如select unique_key_column的子查询替换。PS:所以不一定in子句中使用子查询就是低效的
    index_subquery同上,但把形如”select non_unique_key_column“的子查询替换
    range常数值的范围
    index索引树扫描。a.当查询是索引覆盖的,即所有数据均可从索引树获取的时候(Extra中有Using Index);b.以索引顺序从索引中查找数据行的全表扫描(无 Using Index);c.如果Extra中Using Index与Using Where同时出现的话,则是利用索引查找键值的意思;d.如单独出现,则是用读索引来代替读行,但不用于查找
    all全表扫描(full table scan)

    从上往下性能越来越慢,即:

    null > system > const > eq_ref > ref > range > index  > all 
    

    all 全表扫描的时候最慢

    mysql explain type 说明出自:mysql explain type的区别和性能优化

    索引失效的条件

    1、不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描

    2、存储引擎不能使用索引范围条件右边的列

    3、尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select *

    4、mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描

    5、is null,is not null也无法使用索引

    6、like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作。

    问题:解决like‘%字符串%’时索引不被使用的方法?
    在这里插入图片描述
    字符串不加单引号索引失效

    SELECT * from staffs where name='2000';  
    -- 因为mysql会在底层对其进行隐式的类型转换
     
    SELECT * from staffs where name=2000;  
    --- 未使用索引
    
    

    一般性建议

    1、对于单键索引,尽量选择针对当前query过滤性更好的索引

    2、在选择组合索引的时候,当前Query中过滤性最好的字段在索引字段顺序中,位置越靠前越好。

    3、在选择组合索引的时候,尽量选择可以能够包含当前query中的where子句中更多字段的索引

    4、尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

    出自:联合索引生效和失效的条件看这里

    展开全文
  • 主要给大家介绍了关于MySQL组合索引最左匹配原则的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用Mysql具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
  • 什么是复合索引 基于表的多列上创建的索引,也叫联合索引。 为什么使用复合索引 减少开销:建一个复合索引(col1,col2,col3),实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引,...
    • 什么是复合索引

    基于表的多列上创建的索引,也叫联合索引。

    • 为什么使用复合索引

    减少开销:建一个复合索引(col1,col2,col3),实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引,都会增加写操作的开销和磁盘空间的开销。对于大量数据的表,使用联合索引会大大的减少开销!

    覆盖索引:对复合索引(col1,col2,col3),如果有如下的sql: select col1,col2,col3 from newtable where col1=1 and col2=2。可以直接通过遍历索引取得数据,而无需回表,这减少了很多的随机io操作。减少io操作,特别的随机io其实是dba主要的优化策略。所以,在真正的实际应用中,覆盖索引是主要的提升性能的优化手段之一。

    效率高:索引列越多,通过索引筛选出的数据越少。有1000W条数据的表,有如下sql:select * from newtable where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据,如果只有单值索引,那么通过该索引能筛选出1000W10%=100w条数据,然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据,然后再排序,再分页;如果是联合索引,通过索引筛选出1000w10% 10% *10%=1w,效率提升可想而知!

    • 什么是最左匹配原则

    最左优先,以查询条件最左边为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。上面说到建立一个复合索引(col1,col2,col3)相当于建立了三个索引,但除此之外其他的条件组合方式不能使用这个复合索引。

    比如:(col1,col3)、(col2m,col3)、(col2)、(col3)这四种条件无法使用索引

    Q1:IN查询会不会引起停止匹配

    A1:不会

     

    Q2: NOT IN查询条件导致索引停止匹配

    A2:会,因为NOT IN或者<>会导致索引失效,所以不仅是停止匹配,同时NOT IN字段也不会使用索引

    • 创建复合索引时列的选择原则

    1. 经常用的列优先(最左匹配原则)
    2. 离散度高的列优先(离散度高原则)
    3. 宽度小的列优先(最少空间原则)

    列的离散性计算:count(distinct col)/ count(col)

    创建复合索引时列的选择原则引自:https://www.cnblogs.com/wangkaihua/p/10220462.html

    展开全文
  • 1、单一索引和复合索引单一索引是指索引列为一列的情况,另外,用户可以在多个列上建立索引,这种索引叫做复合索引,也叫组合索引。复合索引在数据库操作期间所需的开销更小,可以代替多个单一索引。2、建立复合索引...

    1、单一索引和复合索引

    单一索引是指索引列为一列的情况,另外,用户可以在多个列上建立索引,这种索引叫做复合索引,也叫组合索引。

    复合索引在数据库操作期间所需的开销更小,可以代替多个单一索引。

    2、建立复合索引

    创建索引:

    create index idx1 on table1(col1,col2,col3)

    查询(使用索引):

    select * from table1 where col1= A and col2= B and col3 = C

    这时候查询优化器,不在扫描全表,而是利用索引查询。

    3、复合索引的使用注意事项:索引最左匹配原则

    对于复合索引,在查询使用时,最好将条件顺序按找索引的顺序,这样效率最高,例如:select * from table1 where col1=A AND col2=B AND col3=D。如果使用 where col2=B AND col1=A 或者 where col2=B 将不会使用索引。

    63679181eeb32eea4bd510da6118054f.png

    48d47a90b6e212120a42fd1b53316758.png

    mysql创建复合索引的规则是首先会对复合索引的最左边的,也就是第一个name字段的数据进行排序,在第一个字段的排序基础上,然后再对后面第二个的age字段进行排序。

    其实就相当于实现了类似 order by name age这样一种排序规则。

    所以:第一个name字段是绝对有序的,而第二字段就是无序的了。所以通常情况下,直接使用第二个age字段进行条件判断是用不到索引的, 这就是所谓的mysql为什么要强调最左前缀原则的原因。

    943b3c3b3f934ae4216ffb86860847be.png

    那么什么时候才能用到age字段的索引呢? 前提当然是age字段的索引数据也是有序的情况下才能使用咯,什么时候才是有序的呢?观察可知,是在name字段是等值匹配的情况下,age才是有序的。

    这也就是mysql索引规则中要求复合索引要想使用第二个索引,必须先使用第一个索引的原因,而且第一个索引必须是等值匹配。

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

    千次阅读 2021-01-14 15:23:58
    我在上大学的时候就听说过数据库的最左匹配原则,当时是通过各大博客论坛了解的,但是这些博客的局限性在于它们对最左匹配原则的描述就像一些数学定义一样,往往都是列出123点,满足这123点就能匹配上索引,否则就不...
  • 联合索引最左匹配原则 1.左前缀匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如 a = 3 and b = 4 and c > 5 and d = 6如何建立(a,b,c,d)...
  • 复合索引&最左原则 -- 实战

    千次阅读 2021-05-04 05:26:16
    注意:比较一下第2个sql语句和第8个sql语句,二者的区别在于: 1>第2个sql语句查询了非索引列sex,故根据最左原则,无法使用该索引; 2>第8个sql语句只查询了索引列,(若只查询索引列,则只需扫描索引树即可)故可以...
  • MySQL 最左匹配原则

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

    千次阅读 2022-02-10 15:52:06
    MySQL索引最左匹配原则
  • 联合索引最左匹配原则的成因 联合索引是指 将多个列 一起设置成一个索引,例如:将a,b设置成联合索引,则命中索引规则如下: where a=6 走索引 where a=6,b=1 ,走索引 where b=1 , 不走索引 where a like ‘a%’ ,...
  • 联合索引-最左匹配原则

    千次阅读 2022-03-28 13:50:27
    二、最左匹配原则左优先,以左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。 1)全列匹配时: 结论:修改查询列顺序,发现结果一样。是以为MySQL会通过优化器...
  • 最左前缀匹配原则在mysql建立联合索引时会遵循最左前缀匹配的原则,即最左优先,在检索数据时从联合索引左边开始匹配,示例:对列col1、列col2和列col3建一个联合索引KEY test_col1_col2_col3 on test(...
  • 复合索引最左匹配原则探究 文章目录复合索引最左匹配原则探究一、概述二、联合索引是长什么样子的?三、如何建立和使用复合索引四、覆盖索引是什么?五、总结 一、概述 ​ 上一篇文章中介绍了B+树是如何根据索引...
  • 下面就说下复合索引: 以该表的(name,cid)复合索引为例,它内部结构简单说就是下面这样排列的: mysql创建复合索引的规则是首先会对复合索引左边的,也就是第一个name字段的数据进行排序,在第一个字段的排序...
  • 深入浅析Mysql联合索引原理 之 最左匹配原则

    万次阅读 多人点赞 2019-04-28 11:38:46
    前言 ...最左前缀匹配原则 在mysql建立联合索引时会遵循最左前缀匹配的原则,即最左优先,在检索数据时从联合索引左边开始匹配, 示例: CREATE TABLE `student` ( `Id` int(11) unsign...
  • 当b+树的数据项是复合的数据结构,比如(name,age,sex)的时候,b+数是按照从到右的顺序来建立搜索树的,比如当(张三,20,F)这样的数据来检索的时候,b+树会优先比较name来确定下一步的所搜方向,如果name相同再依次...
  • 联合索引是什么?对多个字段同时建立的索引。联合索引是有顺序的,ABC,ACB是完全不同的两种联合索引。...最左匹配原则(A,B,C) 这样3列,mysql会首先匹配A,然后再B,C。如果用(B,C)这样的数据来检索的话,...
  • 最左前缀匹配原则是指where条件中在使用到 > < in between like等范围搜索的这个即以前的字段,如果可以与联合索引的前几个一一匹配,就可以使用这个索引。但是实际操作中我发现即使顺序不一致,即使条件的...
  • MySQL索引最左匹配原则及优化原理

    千次阅读 2020-07-17 00:07:12
    (2) 定义有外键的列一定要建立索引 : 外键列通常用于表与表之间的连接,在其上创建索引可以加快表间的连接 (3) 对于经常查询的数据列最好建立索引 ① 对于需要在指定范围内快速或频繁查询的数据列,因为索引已经排序...
  • 对于多列索引来说(a,b,c)其相当于3个索引(a),(a,b),(a,b,c)3个索引,又由于mysql的索引优化器,其where条件后的语句是可以乱序的,比如(b,c,a)也是可以用到索引。如果条件中a,c出现的多,为了更好的利用索引故最好...
  • MySQL索引最左匹配原则实战
  • 索引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...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 12,298
精华内容 4,919
关键字:

复合索引最左匹配原则