精华内容
下载资源
问答
  • 索引最左匹配原则
    千次阅读
    2021-01-14 15:23:58

    写在前面:我在上大学的时候就听说过数据库的最左匹配原则,当时是通过各大博客论坛了解的,但是这些博客的局限性在于它们对最左匹配原则的描述就像一些数学定义一样,往往都是列出123点,满足这123点就能匹配上索引,否则就不能。但是我觉得编程不是死记硬背,这个所谓最左匹配原则肯定是有他背后的原理的。所以我尝试说明一下这个原理,这样以后用上优化索引的时候就不需要去记这些像数学定理一样的东西。了解原理比记住某些表面特点,我觉得是更聪明的方式。

    1.简单说下什么是最左匹配原则

    顾名思义:最左优先,以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、

    例如:b = 2 如果建立(a,b)顺序的索引,是匹配不到(a,b)索引的;但是如果查询条件是a = 1 and b = 2或者a=1(又或者是b = 2 and b = 1)就可以,因为优化器会自动调整a,b的顺序。再比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,因为c字段是一个范围查询,它之后的字段会停止匹配。

    2.最左匹配原则的原理

    最左匹配原则都是针对联合索引来说的,所以我们有必要了解一下联合索引的原理。了解了联合索引,那么为什么会有最左匹配原则这种说法也就理解了。

    我们都知道索引的底层是一颗B+树,那么联合索引当然还是一颗B+树,只不过联合索引的健值数量不是一个,而是多个。构建一颗B+树只能根据一个值来构建,因此数据库依据联合索引最左的字段来构建B+树。

    例子:假如创建一个(a,b)的联合索引,那么它的索引树是这样的

    可以看到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是无序的。

    更多相关内容
  • 最左前缀匹配原则 在mysql建立联合索引时会遵循最左前缀匹配的原则,即最左优先,在检索数据时从联合索引左边开始匹配,示例: 对列col1、列col2和列col3建一个联合索引 KEY test_col1_col2_col3 on test(col1...
  • 主要给大家介绍了关于MySQL组合索引最左匹配原则的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用Mysql具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
  • MySQL索引最左匹配原则


    一、案例一

    1、表与索引创建

    DROP TABLE IF EXISTS med_computer_info;
    CREATE TABLE med_computer_info (
    	ID BIGINT NOT NULL AUTO_INCREMENT COMMENT '电脑id',
    	HOST_NAME VARCHAR ( 64 ) NOT NULL COMMENT '电脑id地址',
    	PORT VARCHAR ( 64 ) NOT NULL COMMENT '电脑端口',
    	TYPE INT NOT NULL COMMENT '电脑类型',
    	LAUNCH_DATE DATE NOT NULL COMMENT '电脑发布日期',
    	MODIFIED TIMESTAMP NOT NULL COMMENT '记录修改时间',
    	CREATED TIMESTAMP NOT NULL COMMENT '记录创建时间',
    	PRIMARY KEY ( ID ),
    	UNIQUE KEY INDEX_WORKER_NODE ( HOST_NAME, PORT, LAUNCH_DATE, TYPE ) 
    ) COMMENT = 'DB WorkerID Assigner for UID Generator',
    ENGINE = INNODB;
    

    2、查询语句举例

    select * from med_computer_info where PORT=3306 and Type=1  ;
    select * from med_computer_info where PORT=3307 and HOST_NAME='172.21.1.1'  ;
    select * from med_computer_info where PORT=3308 AND HOST_NAME='172.21.1.2' AND TYPE=2  ;
    

    3、那么究竟用到了哪些索引呢?

    第一句:没用到索引,在聚集索引上从左至右依次扫描过滤;

    第二句:用到了辅助索引INDEX_WORKER_NODE;

    第三句:用到了辅助索引INDEX_WORKER_NODE,但是只有HOST_NAME和PORT条件是通过索引完成的,条件TYPE是依次扫描过滤完成的;

    4、原因

    因为辅助索引是B+树实现的,虽然可以指定多个列,但是每个列的比较优先级不一样,写在前面的优先比较。一旦出现遗漏,在B+树上就无法继续搜索了(通过补齐等措施解决的除外),因此是按照最左连续匹配来的。既然是在B+树上搜索,对于条件的比较自然是要求精确匹配(即"=“和"IN”)。不过顺序倒是可以颠倒,因为查询优化器重排序一下就好了。

    第一句,由于缺少HOST_NAME,只能在聚集索引的叶节点上,从左至右的扫描,挨个比对;

    第二句,可以直接在辅助索引上查找,被找到的子树的所有叶节点就是命中的记录;

    第三句,缺少LAUNCH_DATE条件,所以只能先依据HOST_NAME和PROT在辅助索引上查找,找到的主键值作为候选记录,然后到聚集索引上读取对应记录,再比较TYPE条件是否满足。

    二、案例二

    1、表与索引创建

    DROP TABLE IF EXISTS student;  
    CREATE TABLE `student` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) DEFAULT NULL,
      `cid` int(11) DEFAULT NULL,
    
      PRIMARY KEY (`id`),
      KEY `name_cid_INX` (`name`,`cid`),
      KEY `name_INX` (`name`)
    ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
    
    

    2、查询语句举例

    依据mysql索引最左匹配原则,两个索引都匹配上了,这个没有问题

    EXPLAIN SELECT * FROM student WHERE name='小红';
    

    在这里插入图片描述

    判断条件是cid=1,而cid是(name,cid)复合索引的一部分,没有问题,可以进行index类型的索引扫描方式。explain显示结果使用到了索引,是index类型的方式。

    EXPLAIN SELECT * FROM student WHERE cid=1;
    

    在这里插入图片描述
    cid字段的索引数据也是有序的情况下才能使用,什么时候才是有序的呢?观察可知,当然是在name字段是等值匹配的情况下,cid才是有序的。

    EXPLAIN SELECT * FROM student WHERE cid=1 AND name='小红';
    

    在这里插入图片描述

    3、原因

    index:这种类型表示是mysql会对整个该索引进行扫描。要想用到这种类型的索引,对这个索引并无特别要求,只要是索引,或者某个复合索引的一部分,mysql都可能会采用index类型的方式扫描。但是呢,缺点是效率不高,mysql会从索引中的第一个数据一个个的查找到最后一个数据,直到找到符合判断条件的某个索引。

    ref:这种类型表示mysql会根据特定的算法快速查找到某个符合条件的索引,而不是会对索引中每一个数据都进行一 一的扫描判断,也就是所谓你平常理解的使用索引查询会更快的取出数据。而要想实现这种查找,索引却是有要求的,要实现这种能快速查找的算法,索引就要满足特定的数据结构。简单说,也就是索引字段的数据必须是有序的,才能实现这种类型的查找,才能利用到索引。

    4、最左匹配原则

    以该表的(name,cid)复合索引为例,它内部结构简单说就是下面这样排列的:
    在这里插入图片描述

    Mysql创建复合索引的规则是首先会对复合索引的最左边的,也就是第一个name字段的数据进行排序,在第一个字段的排序基础上,然后再对后面第二个的cid字段进行排序。其实就相当于实现了类似 order by name cid这样一种排序规则。

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

    那么什么时候才能用到呢?
    当然是cid字段的索引数据也是有序的情况下才能使用

    三、案例三

    1、表与索引创建

    DROP TABLE IF EXISTS student;  
    CREATE TABLE `student` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) DEFAULT NULL,
      `cid` int(11) DEFAULT NULL,
    	`home` VARCHAR(255) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `name_cid_INX` (`name`,`cid`),
      KEY `name_INX` (`name`)
    ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
    

    2、查询语句举例

    EXPLAIN SELECT * FROM student WHERE cid=1;
    

    在这里插入图片描述

    3、原因以及补充资料

    比案例二多了一个home字段,由于辅助索引包含聚集索引,所以案例二的辅助索引为全部字段。

    辅助索引包含了主键id用于回表操作,同时利用覆盖索引扫描可以更好的优化SQL。

    索引可以加快数据的检索,减少IO开销,会占用磁盘空间,是一种用空间换时间的优化手段,同时更新操作会导致索引频繁的合并分裂,影响索引性能,在实际的业务开发中,如何根据业务场景去设计合适的索引是非常重要的

    mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高,最后才生成真正的执行计划。所以,当然是我们能尽量的利用到索引时的查询顺序效率最高咯,所以mysql查询优化器会最终以这种顺序进行查询执行。

    展开全文
  • 联合索引最左匹配原则的成因 联合索引是指 将多个列 一起设置成一个索引,例如:将a,b设置成联合索引,则命中索引规则如下: where a=6 走索引 where a=6,b=1 ,走索引 where b=1 , 不走索引 where a like ‘a%’ ,...

    联合索引最左匹配原则的成因

    联合索引是指 将多个列 一起设置成一个索引,例如:将a,b设置成联合索引,则命中索引规则如下:

    • where a=6 走索引
    • where a=6,b=1 ,走索引
    • where b=1 , 不走索引
    • where a like ‘a%’ ,走索引
    • where a like ‘%a%’,不走索引
    • where a like ‘a%’ and b=‘2’, a 走索引,b 不走索引
    1. 最左匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,
      比如 a=3 and b=4 and c>5 and d=6,如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的
      索引则都可以用到,a,b,d的顺序可以任意调整
    2. = 和 in 可以乱序,比如:a=1 and b=2 and c=3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化
      成索引可以识别的形式

    联合索引最左匹配原则的原因?

    mysql创建复合索引的规则:是首先会对复合索引的最左边的第一个字段进行排序,然后在对最左边第一个字段排序的基础上
    再对对第二个索引字段进行排序,就相当于order by 字段1,字段2,字段3…这样的规则,所以第一个字段是绝对有序的,
    而第二个字段就是无序的了,因此通常情况下直接使用第二个字段进行条件判断是用不到索引的
    这就是所谓的联合索引最左匹配原则的原因

    测试数据脚本

    1.找到mysql的my.cnf配置文件,将max_heap_table_size改大些,改成4000M,重启下mysql服务即可。
    
    #创建一张内存表
    CREATE TABLE `person_info_memory` (  
        `id` INT (7) NOT NULL AUTO_INCREMENT,  
        `account` VARCHAR (10),   
        `name` VARCHAR (20),  
        `area` VARCHAR (20),  
        `title` VARCHAR (20), 
        `motto` VARCHAR (50),
        PRIMARY KEY (`id`),  
        UNIQUE(`account`),
        KEY `index_area_title`(`area`,`title`)
    ) ENGINE = MEMORY AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8  
    #创建一张店铺小数据表
    CREATE TABLE `shop_info_small` (  
        `shop_id` INT (2) NOT NULL AUTO_INCREMENT, 
        `shop_name` VARCHAR (20),  
        `person_id` INT (2),
        `shop_profile` VARCHAR (50),
        PRIMARY KEY (`shop_id`),
        UNIQUE(`shop_name`)
    ) ENGINE = MYISAM AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8  
    #创建一张小数据表
    CREATE TABLE `person_info_small` (  
        `id` INT (2) NOT NULL AUTO_INCREMENT,  
        `account` VARCHAR (10),   
        `name` VARCHAR (20),  
        `area` VARCHAR (20),  
        `title` VARCHAR (20), 
        `motto` VARCHAR (50),
        PRIMARY KEY (`id`),  
        UNIQUE(`account`),
        KEY `index_area_title`(`area`,`title`) 
    ) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8  
    #创建一张大数据表
    CREATE TABLE `person_info_large` (  
        `id` INT (7) NOT NULL AUTO_INCREMENT,  
        `account` VARCHAR (10),   
        `name` VARCHAR (20),  
        `area` VARCHAR (20),  
        `title` VARCHAR (20), 
        `motto` VARCHAR (50),
        PRIMARY KEY (`id`),  
        UNIQUE(`account`),
        KEY `index_area_title`(`area`,`title`) 
    ) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8  
    
    #创建一个能够返回随机字符串mysql自定义函数
    DELIMITER $$
    CREATE FUNCTION `rand_string`(n INT) RETURNS varchar(255) CHARSET utf8
    BEGIN
    DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    DECLARE return_str varchar(255) DEFAULT '' ;
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
    SET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*12 + RAND()*50),1));
    SET i = i +1;
    END WHILE;
    RETURN return_str;
    END $$
    #创建一个批量往内存表里灌数据的存储过程
    DELIMITER $$
    CREATE  PROCEDURE `add_person_info_large`(IN n int)  
    BEGIN    
      DECLARE i INT DEFAULT 1;  
        WHILE (i <= n ) DO  
          INSERT into person_info_memory  (account,name,area,title, motto) VALUEs (rand_string(10),rand_string(20),rand_string(20) ,rand_string(20),rand_string(50));  
                set i=i+1;  
        END WHILE;  
    END $$
    #创建一个批量往小表里灌数据的存储过程
    DELIMITER $$
    CREATE  PROCEDURE `add_person_info_small`(IN n int)  
    BEGIN    
      DECLARE i INT DEFAULT 1;  
        WHILE (i <= n ) DO  
          INSERT into person_info_small  (account,name,area,title, motto) VALUEs (rand_string(10),rand_string(20),rand_string(20) ,rand_string(20),rand_string(50));  
                set i=i+1;  
        END WHILE;  
    END $$
    #调用存储过程,插入100万条数据(由于我们的随机数可能会出现重复的情况,所以插入的条数也许达不到100万便会出错停止,可以自行加入些随机数优化一下)
    CALL add_person_info_large(1000000);
    #调用存储过程,插入10条数据到小表里
    CALL add_person_info_small(2);
    #将内存表的数据移动到person_info_large中
    insert into person_info_large(account,name,area,title,motto)
    select account,name,area,title,motto from person_info_memory;
    #若遇数据冲突没法到达100万的情况,通过变换唯一键值的方式来插入数据
    insert into person_info_large(account,name,area,title,motto)
    select concat(substring(account, 2),'a'),concat(substring(name, 2),'a'),area,title,motto from person_info_memory;
    insert into person_info_large(account,name,area,title,motto)
    select concat(substring(account, 2),'b'),concat(substring(name, 2),'b'),area,title,motto from person_info_memory;
    
    
    CREATE TABLE `test_myisam` (  
        `id` INT (2) NOT NULL AUTO_INCREMENT, 
        `name` VARCHAR (20),  
        `unique_id` INT (2),
        `normal_id` INT (2),
        PRIMARY KEY (`id`),
        UNIQUE(`unique_id`),
        INDEX(`normal_id`)
    ) ENGINE = MYISAM AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8 
    
    CREATE TABLE `test_innodb` (  
        `id` INT (2) NOT NULL AUTO_INCREMENT, 
        `name` VARCHAR (20),  
        `unique_id` INT (2),
        `normal_id` INT (2),
        PRIMARY KEY (`id`),
        UNIQUE(`unique_id`),
        INDEX(`normal_id`)
    ) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8 
    insert into test_innodb (name,unique_id,normal_id) values('a',1,1),('d',4,4),('h',8,8),('k',11,11);
    insert into test_myisam (name,unique_id,normal_id) values('a',1,1),('d',4,4),('h',8,8),('k',11,11);
    
    

    本站导航

    MySql慢查询日志

    mySql联合索引最左匹配原则

    mySql锁

    mySql优化

    回到主导航页

    支持我-微信扫一扫-加入微信公众号

    Aseven公众号

    赞赏作者

    赞赏作者
    展开全文
  • 使用col3,col2,col1 顺序建立联合索引,通过col3的值建立一个b+tree ,通过关键值去查找“Alice”,在叶子节点中找到两个“Alice”,那么“Alice”对于col2、col1对应的值,那么会对col2,col1分别进行一个有序的排列...

     

    使用col3,col2,col1 顺序建立联合索引,通过col3的值建立一个b+tree ,通过关键值去查找“Alice”,在叶子节点中找到两个“Alice”,那么“Alice”对于col2、col1对应的值,那么会对col2,col1分别进行一个有序的排列,因此从索引中找到col2,col1 就得有col3 通过 col3 索引找到col2,col1,因此,单独依靠col2是没办法走索引的,想要查询走索引,必须要加上col3的列条件。

    展开全文
  • 不是最左匹配原则吗? 查了下资料发现:mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高,最后才生成真正的执行计划。因此,固然是咱们能尽可能的利用到索引时的查询顺序效率最高咯,因此mysql...
  • 数据库索引最左匹配原则

    千次阅读 2020-07-26 18:48:50
    索引最左匹配原则 建立联合索引时会遵循最左匹配原则,即左优先,在检索数据时从联合索引左边开始匹配 例如: 为user表中的name、address、phone列添加联合索引 ALTER TABLE user ADD INDEX index_three(name...
  • 联合索引是什么?对多个字段同时建立的索引。联合索引是有顺序的,ABC,ACB是完全不同的两种联合索引。...最左匹配原则(A,B,C) 这样3列,mysql会首先匹配A,然后再B,C。如果用(B,C)这样的数据来检索的话,...
  • MySQL索引最左匹配原则及优化原理

    千次阅读 2020-07-17 00:07:12
    (2) 定义有外键的列一定要建立索引 : 外键列通常用于表与表之间的连接,在其上创建索引可以加快表间的连接 (3) 对于经常查询的数据列最好建立索引 ① 对于需要在指定范围内快速或频繁查询的数据列,因为索引已经排序...
  • Mysql复合索引最左匹配原则以及索引失效条件复合索引最左匹配原则用EXPLAIN 来查看语句是否用到了索引索引失效的条件一般性建议 复合索引最左匹配原则 复合索引又叫联合索引。两个或更多个列上的索引被称作复合索引...
  • 最左匹配原则 假设我们有两列a,b,a和b是联合索引,他的顺序是a,b,我们在where语句中调用a=? and b=?的时候就会走联合索引,如果调用where a = ?的时候也会走索引,但是当我们使用where b = ?的时候就不会走这个...
  • 在网上看过一些有关最左匹配原则的博客,自以为自己理解了,但是今天面试的时候被面试官深挖了一下,就暴露了其实并没有真正理解到最左匹配原则左前缀匹配原则 MySQL在建立联合索引的时候,会从左到右依次建立...
  • MYSQL 索引最左匹配原则

    千次阅读 2018-06-18 11:06:45
    转自知乎问题:mysql索引最左匹配原则的理解? 具体问题描述如下: CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `cid` int(11) DEFAULT NULL, PRIMARY...
  • mysql联合索引最左匹配原则的底层实现原理 要看懂,需要熟悉mysql b+ tree的数据结构 b+tree的叶节点和叶子节点的排序特性是按照,从小到大,从左到右的这么一个规则,int直接比大小,uuid比较ASCII码, 联合索引的排序...
  • mysql索引最左匹配原则理解
  • MySQL 索引最左匹配原则

    万次阅读 2016-10-24 22:11:58
    索引主要做3件事:过滤(filter),排序或分组(sort/group),覆盖(cover)。前两个没什么好说的,但并不是每个人都...1. 使用索引以查找匹配的记录,并得到数据的指针。 2. 使用相关数据的指针。 3. 返回查询到的记录。
  • 最左前缀匹配原则 在mysql建立联合索引时会遵循最左前缀匹配的原则,即最左优先,在检索数据时从联合索引左边开始匹配,示例: 对列col1、列col2和列col3建一个联合索引 ? 1 KEY...
  • 索引最左匹配原则 使用OR(and)搜索时,条件顺序重要(左前缀原则,索引失效,需要将索引放左边) 以下是题目内容和知识点: package org.j.mysql; /** * @author *** * @version 1.0 * @description 一个国家...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 42,978
精华内容 17,191
关键字:

索引最左匹配原则