精华内容
下载资源
问答
  • 2的时候没有使用索引的原因,所以创建联合索引的时候,需要把排除最多数据的条件字段放在最开始,以上面的sql为例,联合索引创建为 start_time, status, total 这样的顺序,因为时间条件最先判筛选掉最多的数据,...

    今天发现一条sql语句特别慢,跑了1.9秒,大致语句如下

    SELECT DISTINCT
    	c.id
    FROM
    	table_name c
    WHERE
    	c.`status` != 1
    AND c.total >= 2
    AND c.start_time >= '2020-01-01 00:00:00'
    AND c.start_time < '2020-08-01 00:00:00'
    AND c.id IN (....此处省略几百个id
    )

    因为sqlalchemy无法跨库查询,而几百个几千个id是由另一个库的表里查询出来的

    这个表已经有很多单独索引和联合索引

    使用 explain 发现,key字段里,这个查询只使用id这个单独索引,没有使用到另一个status,total,start_time的联合索引,但是possible_keys里显示,可能用到联合索引,所以就很奇怪,然后开始网上搜索,有的说!=,<>这种不走索引,我就把c.`status` != 1 改为了 c.`status` in (2,3,4,5),但是结果还是没走后面的索引,还是用了id的单独索引

    为什么呢

    之前还天真的以为,查询的字段顺序要和联合索引的字段顺序一致,结果还是没有用

    然后将status条件之后的所有判断条件都注释掉,只使用c.`status` = 2 试一下,发现还是不行,就只改成 c.`status` = 3,发现使用了status的单独索引

    后来发现是因为c.`status` = 2 这个条件数据量太大,数据库觉得使用索引开销太大,使用了全表扫描,这也是为什么c.`status` = 2的时候没有使用索引的原因,所以创建联合索引的时候,需要把排除最多数据的条件字段放在最开始,以上面的sql为例,联合索引创建为 start_time, status, total 这样的顺序,因为时间条件最先判筛选掉最多的数据,然后我查看发现status筛选掉的数据比total

    这样的联合索引创建以后,上面的sql语句不变,执行时间为0.14秒

     

    展开全文
  • 联合索引建立情况: 查询条件顺序和联合索引一样 EXPLAIN SELECT * FROM t_cmp_mission WHERE companyID = "" AND isFinish ="" AND missionType ="" AND creTm = ""; 查询条件顺序和联合索引顺序完全相反 ...

    版本:5.5.18.1

    在这里插入图片描述

    联合索引建立情况:

    在这里插入图片描述

    查询条件顺序和联合索引一样

    EXPLAIN SELECT * FROM t_cmp_mission WHERE companyID = "" AND isFinish ="" AND missionType ="" AND creTm = "";
    
    

    在这里插入图片描述

    查询条件顺序和联合索引顺序完全相反

    EXPLAIN SELECT * FROM t_cmp_mission WHERE creTm = "" AND missionType ="" AND isFinish =""  AND companyID = "" ;
    

    在这里插入图片描述

    查询条件中不包含索引的第一个

    EXPLAIN SELECT * FROM t_cmp_mission WHERE isFinish ="4" and missionType ="4" AND creTm ="";
    

    在这里插入图片描述

    查询条件只有联合索引的第一个索引

    EXPLAIN SELECT * FROM t_cmp_mission WHERE companyID = "44";
    

    在这里插入图片描述

    只有联合索引的非第一个索引

    EXPLAIN SELECT * FROM t_cmp_mission WHERE isFinish = "44";
    

    在这里插入图片描述

    非第一个索引列like

    EXPLAIN SELECT * FROM t_cmp_mission WHERE companyID = "44" AND isFinish ="4" and missionType ="4" and creTm LIKE '%000%';
    

    在这里插入图片描述

    第一个索引列like 且双边模糊匹配

    EXPLAIN SELECT * FROM t_cmp_mission WHERE companyID LIKE '%000%' AND isFinish ="4" and missionType ="4" and creTm ="";
    

    在这里插入图片描述

    非第一个索引列like 且双边模糊匹配

    EXPLAIN SELECT * FROM t_cmp_mission WHERE  companyID ="" AND isFinish LIKE '%aa%' AND missionType ="4" and creTm ="";
    

    在这里插入图片描述

    第一个索引列like 且右边模糊匹配

    EXPLAIN SELECT * FROM t_cmp_mission WHERE companyID LIKE '000%' AND isFinish ="4" and missionType ="4" and creTm ="";
    

    在这里插入图片描述

    第一个索引列like 且左边模糊匹配

    EXPLAIN SELECT * FROM t_cmp_mission WHERE companyID LIKE '%000' AND isFinish ="4" and missionType ="4" and creTm ="";
    

    在这里插入图片描述

    查询条件中包含 or

    EXPLAIN SELECT * FROM t_cmp_mission WHERE companyID ="someID" OR isFinish ="4" and missionType ="4" and creTm ="";
    

    在这里插入图片描述

    联合索引遇上 where 1=1

    EXPLAIN SELECT * FROM t_cmp_mission WHERE 1=1 AND companyID ="someID" AND isFinish ="4" and missionType ="4" and creTm ="";
    

    在这里插入图片描述

    总结

    1. 联合索引所有成员均以and 出现时与顺序无关,都能生效
    2. 条件语句中出现or 则联合索引无效
    3. 条件语句中缺少排在第一位的索引字段,整个联合索引失效
    4. 欲使联合索引要生效,排在第一位的索引为必须出现,位置不限
    5. 使用like 不一定会导致索引失效,只有like 使用在第一位的索引为且左边做了模糊匹配才会使索引失效
    6. 或许是因为mysql内部做了优化,where 1=1并不影响索引的使用,不会扫描全表
    展开全文
  • 联合索引一直在用,没想到今天栽了跟头,联合索引竟然还有最左原则(左前缀原则)这么一个说法,那什么是最左原则呢? DROP TABLE IF EXISTS `gu_suo`; CREATE TABLE `gu_suo` ( `ID` varchar(16) NOT NULL, `ADM_...

    联合索引一直在用,没想到今天栽了跟头,联合索引竟然还有最左原则(左前缀原则)这么一个说法,那什么是最左原则呢?

    DROP TABLE IF EXISTS `gu_suo`;
    CREATE TABLE `gu_suo` (
      `ID` varchar(16) NOT NULL,
      `ADM_DIV_CODE` varchar(16) NOT NULL,
      `VOUCHER_NO` varchar(16) NOT NULL,
      `NAME` varchar(16) DEFAULT '',
      `AGE` varchar(16) DEFAULT '',
      KEY `ID` (`ID`,`ADM_DIV_CODE`,`VOUCHER_NO`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    
    INSERT INTO gu_suo VALUES('1','530000','1234','小明','12');
    INSERT INTO gu_suo VALUES('2','530000','1234','小张','13');
    INSERT INTO gu_suo VALUES('3','530402','3333','小丽','14');
    INSERT INTO gu_suo VALUES('4','530701','4444','小李','15');
    INSERT INTO gu_suo VALUES('5','530000','3333','大白','15');
    INSERT INTO gu_suo VALUES('6','530400','4444','红中','14');
    INSERT INTO gu_suo VALUES('7','530700','1233','东风','14');
    INSERT INTO gu_suo VALUES('8','530921','1234','发财','13');
    INSERT INTO gu_suo VALUES('9','530100','2223','门板','12');

    先创建一张表,插入几条数据,该表联合索引顺序为 KEY `ID` (`ID`,`ADM_DIV_CODE`,`VOUCHER_NO`),即:

    ID——a

    ADM_DIV_CODE——b

    VOUCHER_NO——c

    我们先看这样几条语句:

    SELECT * FROM gu_suo WHERE ID='1' AND ADM_DIV_CODE='530000' AND VOUCHER_NO='1234'; //abc顺序 
    abc三个索引都在where条件里面且都发挥了作用
    
    SELECT * FROM gu_suo WHERE VOUCHER_NO='1234' AND ID='1' AND ADM_DIV_CODE='530000' ; //cab顺序 
    效果同上==条件顺序在查询之前会被mysql自动优化
    
    SELECT * FROM gu_suo WHERE ID='1' AND VOUCHER_NO='1234'; //ac
    a用到索引b没有 所以c是没有用到索引效果的,看下图:

    通过图对比,不难看出联合索引的最左原则,需要注意的是,联合索引不同于使用两个或多个单独的索引,所以建立联合索引时,顺序特别重要,因为对索引中的所有列执行搜索或仅对前几列执行搜索时,复合索引非常有用,但是如果仅对后面的任意列执行搜索时,复合索引则没有用处,这就完全失去了复合索引的意义。之所以栽跟头,就是在建表的过程中,对联合索引的设计太过随意,还好是在测试过程中及时发现问题。所以,在创建复合索引时应该将最常用的限制条件放在最左边,依次递减。

     

    展开全文
  • 1.联合索引失效的条件联合索引又叫复合索引。两个或更多个列上的索引被称作复合索引。对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key index...

    1.联合索引失效的条件

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

    对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key index (a,b,c)。 可以支持a|a,b|a,b,c3种组合进行查找,但不支持b,c进行查找 .当最左侧字段是常量引用时,索引就十分有效。

    利用索引中的附加列,您可以缩小搜索的范围,但使用一个具有两列的索引不同于使用两个单独的索引。复合索引的结构与电话簿类似,人名由姓和名构成,电话簿首先按姓氏对进行排序,然后按名字对有相同姓氏的人进行排序。如果您知道姓,电话簿将非常有用;如果您知道姓和名,电话簿则更为有用,但如果您只知道名不姓,电话簿将没有用处。

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

    如:建立 姓名、年龄、性别的复合索引。

    create table myTest(

    a int,

    b int,

    c int,

    KEY a(a,b,c)

    );

    (1) select * from myTest where a=3 and b=5 and c=4; ---- abc顺序

    abc三个索引都在where条件里面用到了,而且都发挥了作用

    (2) select * from myTest where c=4 and b=6 and a=3;

    where里面的条件顺序在查询之前会被mysql自动优化,效果跟上一句一样

    (3) select * from myTest where a=3 and c=7;

    a用到索引,b没有用,所以c是没有用到索引效果的

    (4) select * from myTest where a=3 and b>7 and c=3; ---- b范围值,断点,阻塞了c的索引

    a用到了,b也用到了,c没有用到,这个地方b是范围值,也算断点,只不过自身用到了索引

    (5) select * from myTest where b=3 and c=4; --- 联合索引必须按照顺序使用,并且需要全部使用

    因为a索引没有使用,所以这里 bc都没有用上索引效果

    (6) select * from myTest where a>4 and b=7 and c=9;

    a用到了 b没有使用,c没有使用

    (7) select * from myTest where a=3 order by b;

    a用到了索引,b在结果排序中也用到了索引的效果,a下面任意一段的b是排好序的

    (8) select * from myTest where a=3 order by c;

    a用到了索引,但是这个地方c没有发挥排序效果,因为中间断点了,使用 explain 可以看到 filesort

    (9) select * from mytable where b=3 order by a;

    b没有用到索引,排序中a也没有发挥索引效果

    2.索引失效的条件

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

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

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

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

    is null,is not null也无法使用索引 ---- 此处存在疑问,经测试确实可以使用,ref和const等级,并不是all

    like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作。问题:解决like‘%字符串%’时索引不被使用的方法?

    6366b41562ef

    字符串不加单引号索引失效

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

    SELECT * from staffs where name=2000; --- 未使用索引

    一般性建议

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

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

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

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

    展开全文
  • mysql 联合索引生效的条件、索引失效的条件

    万次阅读 多人点赞 2019-02-23 10:11:30
    1.联合索引失效的条件 联合索引又叫复合索引。两个或更多个列上的索引被称作复合索引。 对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key...
  • 联合索引 从上图可以看到: B+Tree 会优先比较 name 来确定下一步应该搜索的方向,往左还是往右。 如果 name相同的时候再比较 phone。 失效原因: 但是如果查询条件没有name,就不知道第一步应该查哪个节点,因为...
  • 面试的时候总会遇到面试官问MySql索引的问题,而且一般都会问到关于索引失效的情况,哪种查询会使用索引,哪种查询不会走索引。本人之前面试也会遇到这样的问题,但是总感觉不够全面,今天我就把关于MySql的相关索引...
  • mysql 联合唯一索引失效

    千次阅读 2019-04-29 21:01:10
    如下图所示,在mysql 一张表中 联合唯一索引按照业务要求由三个字段构成 , 其中name和isbn是varhar,type是tinyint 然后又一次查询接口返回报错,提示返回了多个结果,然后我一看表里面,有同样的两条数据,其中...
  • mysql 常见索引失效场景

    千次阅读 2020-09-26 21:54:50
    索引失效
  • 写在前面:最近复习以前学的Mysql索引,又发现迷迷糊糊的了。看了以前的笔记以及好多博客边思考边实践终于打破这城墙了,呜呜呜真感动。不过我发现好多博客不完整,或者有好多错误的,那就当记一下笔记吧,以后来...
  • mysql索引失效

    2019-03-13 18:13:19
    联合索引失效,index_a_b_c ,where条件中不出现a,或a,c(此时a走索引,c不走),且条件需要是and关系才会走索引,为or关系不会走索引。并且条件顺序不对索引生效造成影响。 注:mysql检查is null 和...
  • mysql索引失效的场景

    2021-01-04 14:34:30
    之前有看过许多类似的文章内容,提到过一些sql语句的使用不当会导致MySQL索引失效。还有一些MySQL“军规”或者规范写明了某些sql不能这么写,否则索引失效。 绝大部分的内容笔者是认可的,不过部分举例中笔者认为...
  • mysql联合索引

    2019-06-16 13:40:31
    mysql联合索引 如:创建联合索引(c1,c2,c3,c4) 联合索引遵循最佳左前缀法则: 在等值查询的时候,就算字段顺序不和索引一致也没关系,mysql会进行优化。 在进行范围查询的时候,范围查询的字段后的索引全失效。...
  • MySQL的最左前缀原则和联合索引失效的情况从索引的底层结构看待最左前缀原则一、多列索引在and查询中应用二、多列索引在范围查询中应用三、多列索引在排序中应用四,总结索引失效的情况 复合(联合)索引失效解析 从...
  • 2、使用联合索引时需注意设置的索引是否被正确使用上,举例说明,现在针对字段a、b、c建立联合索引: 全值匹配时,用到了索引,where子句几个搜索条件顺序调换时也会用到索引,因为Mysql中有查询优化器,会自动优化...
  • 最左前缀法则 : 如果是联合索引,查询从索引的最左侧开始,不跳过其他索引. 如果跳过,则索引失效 create index index_name on user(name,status,address);//创建组合索引 select * from user where name = ? and ...
  • Mysql防止索引失效

    2020-08-03 20:50:52
    联合索引不满足最左原则,索引一般会失效,但是这个还跟Mysql优化器有关的 查询数据列包含mysql内置函数 对索引列运算(如,+、-、*、/),索引失效。 索引字段上使用(!= 或者 ,not in)时,可能会导致索引失效 ...
  • 今天在测试过程中发现有数据重复的现象,想设计一个唯一索引达到幂等的效果,但是发现设置了唯一索引以后还是可以插入相同的数据,经过排查,发现联合唯一索引中含有null值会导致索引失效。 二.问题复现 现在我们来...
  • MySQL索引失效

    2020-08-02 23:18:13
    Mysql索引失效详解索引1、like以百分号开头2、联合索引不符合最左原则3、使用不明确判断被查询优化器优化小结 索引 MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。一般情况下...

空空如也

空空如也

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

mysql联合索引失效

mysql 订阅