精华内容
下载资源
问答
  • 最左前缀匹配原则

    2021-02-14 15:22:26
    最左前缀匹配原则:在MySQL建立联合索引时会遵守最左前缀匹配原则,即左优先,在检索数据时从联合索引的左边开始匹配。 要想理解联合索引的左匹配原则,先来理解下索引的底层原理。索引的底层是一颗B+树,那么...

    最左前缀匹配原则:在MySQL建立联合索引时会遵守最左前缀匹配原则,即最左优先,在检索数据时从联合索引的最左边开始匹配。

    要想理解联合索引的最左匹配原则,先来理解下索引的底层原理。索引的底层是一颗B+树,那么联合索引的底层也就是一颗B+树,只不过联合索引的B+树节点中存储的是键值。由于构建一棵B+树只能根据一个值来确定索引关系,所以数据库依赖联合索引最左的字段来构建。

    举例:创建一个(a,b)的联合索引,那么它的索引树就是下图的样子。

    在这里插入图片描述

    可以看到a的值是有顺序的,1,1,2,2,3,3,而b的值是没有顺序的1,2,1,4,1,2。但是我们又可发现a在等值的情况下,b值又是按顺序排列的,但是这种顺序是相对的。这是因为MySQL创建联合索引的规则是首先会对联合索引的最左边第一个字段排序,在第一个字段的排序基础上,然后在对第二个字段进行排序。所以b=2这种查询条件没有办法利用索引。

    由于整个过程是基于explain结果分析的,那接下来在了解下explain中的type字段和key_lef字段。

    1.type:联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:(重点看ref,rang,index)

    system:表只有一行记录(等于系统表),这是const类型的特例,平时不会出现,可以忽略不计
        const:表示通过索引一次就找到了,const用于比较primary key 或者 unique索引。因为只需匹配一行数据,所有很快。如果将主键置于where列表中,mysql就能将该查询转换为一个const
        eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描。
        注意:ALL全表扫描的表记录最少的表如t1表
        ref:非唯一性索引扫描,返回匹配某个单独值的所有行。本质是也是一种索引访问,它返回所有匹配某个单独值的行,然而他可能会找到多个符合条件的行,所以它应该属于查找和扫描的混合体。
        range:只检索给定范围的行,使用一个索引来选择行。key列显示使用了那个索引。一般就是在where语句中出现了bettween、<、>、in等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点,结束于另一个点,不用扫描全部索引。
        index:Full Index Scan,index与ALL区别为index类型只遍历索引树。这通常为ALL块,应为索引文件通常比数据文件小。(Index与ALL虽然都是读全表,但index是从索引中读取,而ALL是从硬盘读取)
        ALL:Full Table Scan,遍历全表以找到匹配的行

    2.key_len:显示MySQL实际决定使用的索引的长度。如果索引是NULL,则长度为NULL。如果不是NULL,则为使用的索引的长度。所以通过此字段就可推断出使用了那个索引。

    计算规则:

    1.定长字段,int占用4个字节,date占用3个字节,char(n)占用n个字符。

    2.变长字段varchar(n),则占用n个字符+两个字节。

    3.不同的字符集,一个字符占用的字节数是不同的。Latin1编码的,一个字符占用一个字节,gdk编码的,一个字符占用两个字节,utf-8编码的,一个字符占用三个字节。

    (由于我数据库使用的是Latin1编码的格式,所以在后面的计算中,一个字符按一个字节算)

    4.对于所有的索引字段,如果设置为NULL,则还需要1个字节。

    接下来进入正题!!!

    示例:

    首先创建一个表
    在这里插入图片描述
    该表中对id列.name列.age列建立了一个联合索引 id_name_age_index,实际上相当于建立了三个索引(id)(id_name)(id_name_age)。

    下面介绍下可能会使用到该索引的几种情况:

    1.全值匹配查询时
    在这里插入图片描述

    通过观察上面的结果图可知,where后面的查询条件,不论是使用(id,age,name)(name,id,age)还是(age,name,id)顺序,在查询时都使用到了联合索引,可能有同学会疑惑,为什么底下两个的搜索条件明明没有按照联合索引从左到右进行匹配,却也使用到了联合索引? 这是因为MySQL中有查询优化器explain,所以sql语句中字段的顺序不需要和联合索引定义的字段顺序相同,查询优化器会判断纠正这条SQL语句以什么样的顺序执行效率高,最后才能生成真正的执行计划,所以不论以何种顺序都可使用到联合索引。另外通过观察上面三个图中的key_len字段,也可说明在搜索时使用的联合索引中的(id_name_age)索引,因为id为int型,允许null,所以占5个字节,name为char(10),允许null,又使用的是latin1编码,所以占11个字节,age为int型允许null,所以也占用5个字节,所以该索引长度为21(5+11+5),而上面key_len的值也正好为21,可证明使用的(id_name_age)索引。

    2.匹配最左边的列时

    在这里插入图片描述

    该搜索是遵循最左匹配原则的,通过key字段也可知,在搜索过程中使用到了联合索引,且使用的是联合索引中的(id)索引,因为key_len字段值为5,而id索引的长度正好为5(因为id为int型,允许null,所以占5个字节)。
    在这里插入图片描述

    由于id到name是从左边依次往右边匹配,这两个字段中的值都是有序的,所以也遵循最左匹配原则,通过key字段可知,在搜索过程中也使用到了联合索引,但使用的是联合索引中的(id_name)索引,因为key_len字段值为16,而(id_name)索引的长度正好为16(因为id为int型,允许null,所以占5个字节,name为char(10),允许null,又使用的是latin1编码,所以占11个字节)。

    在这里插入图片描述

    由于上面三个搜索都是从最左边id依次向右开始匹配的,所以都用到了id_name_age_index联合索引。

    那如果不是依次匹配呢?
    在这里插入图片描述

    通过key字段可知,在搜索过程中也使用到了联合索引,但使用的是联合索引中的(id)索引,从key_len字段也可知。因为联合索引树是按照id字段创建的,但age相对于id来说是无序的,只有id只有序的,所以他只能使用联合索引中的id索引。

    在这里插入图片描述

    通过观察发现上面key字段发现在搜索中也使用了id_name_age_index索引,可能许多同学就会疑惑它并没有遵守最左匹配原则,按道理会索引失效,为什么也使用到了联合索引?因为没有从id开始匹配,且name单独来说是无序的,所以它确实不遵循最左匹配原则,然而从type字段可知,它虽然使用了联合索引,但是它是对整个索引树进行了扫描,正好匹配到该索引,与最左匹配原则无关,一般只要是某联合索引的一部分,但又不遵循最左匹配原则时,都可能会采用index类型的方式扫描,但它的效率远不如最做匹配原则的查询效率高,index类型类型的扫描方式是从索引第一个字段一个一个的查找,直到找到符合的某个索引,与all不同的是,index是对所有索引树进行扫描,而all是对整个磁盘的数据进行全表扫描。

    在这里插入图片描述

    这两个结果跟上面的是同样的道理,由于它们都没有从最左边开始匹配,所以没有用到联合索引,使用的都是index全索引扫描。

    3.匹配列前缀

    如果id是字符型,那么前缀匹配用的是索引,中坠和后缀用的是全表扫描。

    select * from staffs where id like 'A%';//前缀都是排好序的,使用的都是联合索引
    select * from staffs where id like '%A%';//全表查询
    select * from staffs where id like '%A';//全表查询
    

    4.匹配范围值
    在这里插入图片描述

    在匹配的过程中遇到<>=号,就会停止匹配,但id本身就是有序的,所以通过possible_keys字段和key_len 字段可知,在该搜索过程中使用了联合索引的id索引(因为id为int型,允许null,所以占5个字节),且进行的是rang范围查询。

    在这里插入图片描述

    由于不遵循最左匹配原则,且在id<4的范围中,age是无序的,所以使用的是index全索引扫描。

    在这里插入图片描述

    不遵循最左匹配原则,但在数据库中id<2的只有一条(id),所以在id<2的范围中,age是有序的,所以使用的是rang范围查询。

    在这里插入图片描述

    不遵循最左匹配原则,而age又是无序的,所以进行的全索引扫描。

    5.准确匹配第一列并范围匹配其他某一列

    在这里插入图片描述

    由于搜索中有id=1,所以在id范围内age是无序的,所以只使用了联合索引中的id索引。

    展开全文
  • 最左前缀匹配原则定义: 当对某张表创建包含多个字段的联合索引后,进行查询时,会按照所定义的索引中的字段顺序从左至右进行匹配;在遇到函数、排序、不等于等运算时会停止匹配。 例子 在Student表中使用学院编号...

    最左前缀匹配原则定义:

    当对某张表创建包含多个字段的联合索引后,进行查询时,会按照所定义的索引中的字段顺序从左至右进行匹配;在遇到函数、排序、不等于等运算时会停止匹配。
    

    例子

    在Student表中使用学院编号deptId以及班级编号classId建立联合索引
    CREATE INDEX testind ON Student(deptId, classId); 
    
    查询时会按照索引中字段的顺序进行匹配,即使where语句中等值语句的顺序不与索引中顺序一致,查询时仍会按照最左前缀匹配原则进行匹配。
    SELECT * FROM Student WHERE deptId = 1024 AND classId = 10;
    
    查看该表上创建的索引
    SHOW INDEX FROM Student; 
    

    explain的解释

    EXPLAIN SELECT * FROM email WHERE deptId = 1024 AND classId = 10;
    

    explain命令结果
    (1)id: 表明查询的编号
    (2)select_type: 查询类型;用来区分普通查询(SIMPLE)、联合查询、
    子查询等 复杂查询。
    (3)table: 查询的表名称
    (4)type: ref 非唯一性索引扫描
    非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行
    (5)pssible_keys: 显示可能应用在这张表上的索引。
    (6)key:实际使用的索引
    (7)key_len: 计算查询中所使用索引的长度;不损失精确性的情况下,越短越好
    (8)ref:显示索引的哪一列被使用了;如果可能的话最好是const常数。
    (9)rows:根据表统计信息及索引使用情况,大致估算出的找到所需记录所需读取的行数。
    (10)Extra:额外信息

    参考:https://www.php.cn/mysql-tutorials-454417.html

    展开全文
  • 数据库Mysql-索引的最左前缀匹配原则 最左前缀匹配原则: 左优先,以左边的为起点任何连续的索引都能匹配上。同时如果范围查询(>、<、between、like)就会停止匹配。 一、例子来理解最左前缀匹配原则 前一...

    数据库Mysql-索引的最左前缀匹配原则

    最左前缀匹配原则: 最左优先,以最左边的为起点任何连续的索引都能匹配上。同时如果范围查询(>、<、between、like)就会停止匹配。

    一、例子来理解最左前缀匹配原则

    前一篇文中,我们已经了解到Mysql数据库的索引的底层存储是一棵B+树,那么联合索引的底层也还是一棵B+树。只不过联合索引的键值对数量不是一个,而是多个。

    假如:构建一个(a,b)的联合索引,那么它在数据库底层的索引树是下列这样的:

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vLbG66aT-1615526690562)(E:\笔记\JAVA\Java复习框架-数据库\Mysql\temp\1-1.png)]

    可以看到a的值是有顺序的,1,1,2,2,3,3,而b的值是没有顺序的1,2,1,4,1,2。所以b = 2这种查询条件没有办法利用索引,因为联合索引首先是按a排序的,b是无序的。

    同时我们还可以发现在a值相等的情况下,b值又是按顺序排列的,但是这种顺序是相对的。所以最左匹配原则遇上范围查询就会停止,剩下的字段都无法使用索引。例如a = 1 and b = 2 a,b字段都可以使用索引,因为在a值确定的情况下b是相对有序的,而a>1and b=2,a字段可以匹配上索引,但b值不可以,因为a的值是一个范围,在这个范围中b是无序的。

    二、最左前缀匹配原则适用场景

    假如建立联合索引(a,b,c)

    2.1 全值匹配查询时

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

    用到了索引

    where子句几个搜索条件顺序调换不影响查询结果,因为Mysql中有查询优化器,会自动优化查询顺序

    2.2 匹配左边的列时

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

    都从最左边开始连续匹配,用到了索引

    select * from table_name where  b = '2' 
    select * from table_name where  c = '3'
    select * from table_name where  b = '1' and c = '3' 
    

    这些没有从最左边开始,最后查询没有用到索引,用的是全表扫描

    select * from table_name where a = '1' and c = '3' 
    

    如果不连续时,只用到了a列的索引,b列和c列都没有用到

    2.3 匹配列前缀

    如果列是字符型的话它的比较规则是先比较字符串的第一个字符,第一个字符小的哪个字符串就比较小,如果两个字符串第一个字符相通,那就再比较第二个字符,第二个字符比较小的那个字符串就比较小,依次类推,比较字符串。

    如果a是字符类型,那么前缀匹配用的是索引,后缀和中缀只能全表扫描了

    select * from table_name where a like 'As%'; //前缀都是排好序的,走索引查询
    select * from table_name where  a like '%As'//全表查询
    select * from table_name where  a like '%As%'//全表查询
    

    4.匹配范围值

    select * from table_name where  a > 1 and a < 3
    

    可以对最左边的列进行范围查询

    select * from table_name where  a > 1 and a < 3 and b > 1;
    

    多个列同时进行范围查找时,只有对索引最左边的那个列进行范围查找才用到B+树索引,也就是只有a用到索引,在1<a<3的范围内b是无序的,不能用索引,找到1<a<3的记录后,只能根据条件 b > 1继续逐条过滤

    5. 精准匹配某一列并范围匹配另外一列

    如果左边的列是精确查找的,右边的列可以进行范围查找

    select * from table_name where  a = 1 and b > 3;
    

    a=1的情况下b是有序的,进行范围查找走的是联合索引

    6.排序

    一般情况下,我们只能把记录加载到内存中,再用一些排序算法,比如快速排序,归并排序等在内存中对这些记录进行排序,有时候查询的结果集太大不能在内存中进行排序的话,还可能暂时借助磁盘空间存放中间结果,排序操作完成后再把排好序的结果返回客户端。Mysql中把这种再内存中或磁盘上进行排序的方式统称为文件排序。文件排序非常慢,但如果order子句用到了索引列,就有可能省去文件排序的步骤

    select * from table_name order by a,b,c limit 10;
    

    因为b+树索引本身就是按照上述规则排序的,所以可以直接从索引中提取数据,然后进行回表操作取出该索引中不包含的列就好了

    order by的子句后面的顺序也必须按照索引列的顺序给出,比如

    select * from table_name order by b,c,a limit 10;
    

    这种颠倒顺序的没有用到索引

    select * from table_name order by a limit 10;
    select * from table_name order by a,b limit 10;
    

    这种用到部分索引

    select * from table_name where a =1 order by b,c limit 10;
    

    联合索引左边列为常量,后边的列排序可以用到索引

    展开全文
  • 1、在mysql建立联合索引时会遵循最左前缀匹配的原则,即左优先,在检索数据时从联合索引的左边开始匹配,示例:对列col1、列col2和列col3建一个联合索引 KEY index_col1_col2_col3 on test(col1,col2,col3); ...

    1、在mysql建立联合索引时会遵循最左前缀匹配的原则,即最左优先,在检索数据时从联合索引的最左边开始匹配,示例:
    对列col1、列col2和列col3建一个联合索引

    KEY index_col1_col2_col3 on test(col1,col2,col3);

    联合索引 index_col1_col2_col3 实际建立了(col1)、(col1,col2)、(col,col2,col3)三个索引。

    SELECT * FROM table WHERE col1="1" AND clo2="2" AND clo4="4"

    上面这个查询语句执行时会依照最左前缀匹配原则,检索时会使用索引(col1,col2)进行数据匹配。索引的字段可以是任意顺序的。

    2、使用联合索引的好处

    • 减少开销。每多一个索引,都会增加写操作的开销和磁盘空间的开销。对于大量数据的表,使用联合索引会大大的减少开销!
    • 效率高。索引列越多,通过索引筛选出的数据越少。

     

    转载于:https://www.cnblogs.com/kingsonfu/p/10400217.html

    展开全文
  • 联合索引的最左前缀匹配原则

    千次阅读 2020-06-02 16:43:02
    最左前缀匹配原则 左匹配原则的成因 联合索引 所谓的联合索引就是指,由两个或以上的字段共同构成一个索引。 本文测试用例的数据表结构如下,一张简简单单的学生信息表 tb_student,仅包含四个字段(student_...
  • 最左前缀匹配原则:在MySQL建立联合索引时会遵守最左前缀匹配原则,即左优先,在检索数据时从联合索引的左边开始匹配。  要想理解联合索引的左匹配原则,先来理解下索引的底层原理。索引的底层是一颗B+树,...
  • 索引最左前缀匹配原则 对于最左前缀匹配原则居然没有百度百科,实在是让我感觉不可思议。 最左前缀匹配原则,用几句话来概述就是: 顾名思义,就是左优先,在创建多列索引时,要根据业务需求,where子句中使用...
  • 最左前缀匹配原则是指where条件中在使用到 > < in between like等范围搜索的这个即以前的字段,如果可以与联合索引的前几个一一匹配,就可以使用这个索引。 但是实际操作中我发现即使顺序不一致,即使条件的...
  • 1.mysq|会-直向右匹配直到遇到范围查询(>、 <、 between、like)就停 止匹配,比如a = 3 andb = 4 andc > 5 and d = 6如果建立(a,b,c,d)顺序的 索引, d是用不到索弓|的,如果建立(a,b,d,c)的索弓|则都可以用到...
  • 联合索引的最左前缀匹配原则什么情况下会发生明明创建了索引,但是执行的时候并没有通过索引呢?为什么主键通常建议使用自增id呢? 什么是回表查询? 所谓的回表查询,是指先定位主键值,再定位行记录,性能上较之...
  • 联合索引的最左前缀匹配原则介绍

    万次阅读 多人点赞 2019-06-27 18:29:25
    最左前缀匹配原则,是一个非常重要的原则,可以通过以下这几个特性来理解。 对于联合索引,MySQL 会一直向右匹配直到遇到范围查询(> , ,between,like)就停止匹配。比如 a = 3 and b = 4 and c > 5 and d = ...
  • mysql组合索引中最左前缀匹配原理

    千次阅读 2017-10-15 22:49:08
    最左前缀原理与相关优化 高效使用索引的首要条件是知道什么样的查询会使用到索引,这个问题和B+Tree中的“最左前缀原理”有关。 这里先说一下联合索引的概念。在上文中,我们都是假设索引只引用了单个的列,实际上...
  • 实验目的:了解索引对于全列匹配,最左前缀匹配、范围查询的影响。实验所用数据库见文章底部连接。 show index from `employees`.`titles` 实验一、全列匹配 explain select * from `employees`.`titles` ...
  • 文章目录 索引 什么是索引 索引优缺点与适用场景 常见的索引 哈希索引 自适应哈希索引 B+树索引 聚集索引 非聚集索引 使用方法 联合索引 最左前缀匹配规则 覆盖索引 全文索引 使用方法 索引 什么是索引 在数据库中,...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 74,265
精华内容 29,706
关键字:

最左前缀匹配