精华内容
下载资源
问答
  • 个单列索引和联合索引的区别详解

    万次阅读 多人点赞 2018-06-24 17:40:58
    那么当查询条件为2个及以上时,我们是创建个单列索引还是创建一个联合索引好呢?他们之间的区别是什么?哪个效率高呢?我在这里详细测试分析下。 一、联合索引测试 注:Mysql版本为 5.7.20 创建测试表(表记录...

    背景:
    为了提高数据库效率,建索引是家常便饭;那么当查询条件为2个及以上时,我们是创建多个单列索引还是创建一个联合索引好呢?他们之间的区别是什么?哪个效率高呢?我在这里详细测试分析下。


    一、联合索引测试

    注:Mysql版本为 5.7.20

    创建测试表(表记录数为63188):

    CREATE TABLE `t_mobilesms_11` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `userId` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '用户id,创建任务时的userid',
      `mobile` varchar(24) NOT NULL DEFAULT '' COMMENT '手机号码',
      `billMonth` varchar(32) DEFAULT NULL COMMENT '账单月',
      `time` varchar(32) DEFAULT NULL COMMENT '收/发短信时间',
      `peerNumber` varchar(64) NOT NULL COMMENT '对方号码',
      `location` varchar(64) DEFAULT NULL COMMENT '通信地(自己的)',
      `sendType` varchar(16) DEFAULT NULL COMMENT 'SEND-发送; RECEIVE-收取',
      `msgType` varchar(8) DEFAULT NULL COMMENT 'SMS-短信; MSS-彩信',
      `serviceName` varchar(256) DEFAULT NULL COMMENT '业务名称. e.g. 点对点(网内)',
      `fee` int(11) DEFAULT NULL COMMENT '通信费(单位分)',
      `createTime` datetime DEFAULT NULL COMMENT '创建时间',
      `lastModifyTime` datetime DEFAULT NULL COMMENT '最后修改时间',
      PRIMARY KEY (`id`),
      KEY `联合索引` (`userId`,`mobile`,`billMonth`)
    ) ENGINE=InnoDB AUTO_INCREMENT=71185 DEFAULT CHARSET=utf8 COMMENT='手机短信详情'
    

    我们为userId, mobile, billMonth三个字段添加上联合索引!

    我们选择 explain 查看执行计划来观察索引利用情况:


    1.查询条件为 userid

    EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE userid='2222'
    

    这里写图片描述

    可以通过key看到,联合索引有效


    2.查询条件为 mobile

    EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE mobile='13281899972'
    

    这里写图片描述
    可以看到联合索引无效


    3.查询条件为 billMonth

    EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE billMonth='2018-04'
    

    这里写图片描述
    联合索引无效


    4.查询条件为 userid and mobile

    EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE userid='2222' AND mobile='13281899972'
    

    这里写图片描述
    联合索引有效


    5.查询条件为 mobile and userid

    EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE  mobile='13281899972' AND userid='2222' 
    

    这里写图片描述
    在4的基础上调换了查询条件的顺序,发现联合索引依旧有效


    6.查询条件为 userid or mobile

    EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE userid='2222' OR mobile='13281899972'
    

    这里写图片描述
    and 换成 or,发现联合所索引无效


    7.查询条件为 userid and billMonth

    EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE userid='2222' AND billMonth='2018-04'
    

    这里写图片描述
    这两个条件分别位于联合索引位置的第一和第三,测试联合索引依旧有效


    8.查询条件为 mobile and billMonth

    EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE mobile='13281899972' AND billMonth='2018-04'
    

    这里写图片描述
    这两个条件分别位于联合索引位置的第二和第三,发现联合索引无效


    9.查询条件为 userid and mobile and billMonth

    EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE  userid='2222' AND mobile='13281899972' AND billMonth='2018-04'
    

    这里写图片描述
    所有条件一起查询,联合索引有效!(当然,这才是最正统的用法啊!)


    二、单列索引测试

    创建三个单列索引:
    这里写图片描述

    1.查询条件为 userid and mobile and billMonth

    EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE  userid='2222' AND mobile='13281899972' AND billMonth='2018-04'
    

    这里写图片描述
    我们发现三个单列索引只有 userid 有效(位置为查询条件第一个),其他两个都没有用上。

    那么为什么没有用上呢?按照我们的理解,三个字段都加索引了,无论怎么排列组合查询,应该都能利用到这三个索引才对!

    其实这里其实涉及到了mysql优化器的优化策略!当多条件联合查询时,优化器会评估用哪个条件的索引效率最高!它会选择最佳的索引去使用,也就是说,此处userid 、mobile 、billMonth这三个索引列都能用,只不过优化器判断使用userid这一个索引能最高效完成本次查询,故最终explain展示的key为userid。


    2.查询条件为 mobile and billMonth

    EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE mobile='13281899972' AND billMonth='2018-04'
    

    这里写图片描述
    我们发现此处两个查询条件只有 mobile 生效(位置也为查询条件第一个)


    3.查询条件为 userid or mobile

    EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE  userid='2222' OR mobile='13281899972' 
    

    这里写图片描述
    这次把 and 换成 or,发现两个查询条件都用上索引了!

    我们在网上可能常常看到有人说or会导致索引失效,其实这并不准确。而且我们首先需要判断用的是哪个数据库哪个版本,什么引擎?

    比如我用的是mysql5.7版本,innodb引擎,在这个环境下我们再去讨论索引的具体问题。

    关于or查询的真相是:
    所谓的索引失效指的是:假如or连接的俩个查询条件字段中有一个没有索引的话,引擎会放弃索引而产生全表扫描。我们从or的基本含义出发应该能理解并认可这种说法,没啥问题。

    此刻需要注意type类型为index_merge
    我查资料说mysql 5.0 版本之前 使用or只会用到一个索引(即使如上我给userid和mobile都建立的单列索引),但自从5.0版本开始引入了index_merge索引合并优化!也就是说,我们现在可以利用上多个索引去优化or查询了。

    index_merge作用:
    1、索引合并是把几个索引的范围扫描合并成一个索引。
    2、索引合并的时候,会对索引进行并集,交集或者先交集再并集操作,以便合并成一个索引。
    3、这些需要合并的索引只能是一个表的。不能对多表进行索引合并。

    index_merge应用场景:

    1.对OR语句求并集,如查询SELECT * FROM TB1 WHERE c1="xxx" OR c2=""xxx"时,如果c1和c2列上分别有索引,可以按照c1和c2条件进行查询,再将查询结果合并(union)操作,得到最终结果

    2.对AND语句求交集,如查询SELECT * FROM TB1 WHERE c1="xxx" AND c2=""xxx"时,如果c1和c2列上分别有索引,可以按照c1和c2条件进行查询,再将查询结果取交集(intersect)操作,得到最终结果

    3.对AND和OR组合语句求结果


    三、结论

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

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


    重点:

    多个单列索引多条件查询时优化器会选择最优索引策略可能只用一个索引,也可能将多个索引全用上! 但多个单列索引底层会建立多个B+索引树,比较占用空间,也会浪费一定搜索效率,故如果只有多条件联合查询时最好建联合索引!


    最左前缀原则:

    顾名思义是最左优先,以最左边的为起点任何连续的索引都能匹配上,
    注:如果第一个字段是范围查询需要单独建一个索引
    注:在创建联合索引时,要根据业务需求,where子句中使用最频繁的一列放在最左边。这样的话扩展性较好,比如 userid 经常需要作为查询条件,而 mobile 不常常用,则需要把 userid 放在联合索引的第一位置,即最左边


    同时存在联合索引和单列索引(字段有重复的),这个时候查询mysql会怎么用索引呢?

    这个涉及到mysql本身的查询优化器策略了,当一个表有多条索引可走时, Mysql 根据查询语句的成本来选择走哪条索引;


    有人说where查询是按照从左到右的顺序,所以筛选力度大的条件尽量放前面。网上百度过,很多都是这种说法,但是据我研究,mysql执行优化器会对其进行优化当不考虑索引时,where条件顺序对效率没有影响真正有影响的是是否用到了索引


    联合索引本质:

    当创建**(a,b,c)联合索引时,相当于创建了(a)单列索引**,(a,b)联合索引以及**(a,b,c)联合索引**
    想要索引生效的话,只能使用 a和a,b和a,b,c三种组合;当然,我们上面测试过,a,c组合也可以,但实际上只用到了a的索引,c并没有用到!
    注:这个可以结合上边的 通俗理解 来思考!


    其他知识点:

    1、需要加索引的字段,要在where条件中
    2、数据量少的字段不需要加索引;因为建索引有一定开销,如果数据量小则没必要建索引(速度反而慢)
    3、避免在where子句中使用or来连接条件,因为如果俩个字段中有一个没有索引的话,引擎会放弃索引而产生全表扫描
    4、联合索引比对每个列分别建索引更有优势,因为索引建立得越多就越占磁盘空间,在更新数据的时候速度会更慢。另外建立多列索引时,顺序也是需要注意的,应该将严格的索引放在前面,这样筛选的力度会更大,效率更高


    最后的说明:

    网上关于索引优化等文章太多了,针对各个数据库各个版本各种引擎都可能存在不一样的说法

    我们的SQL引擎自带的优化也越来越强大,说不定你的某个SQL优化认知,其SQL引擎在某次升级中早就自优化了。

    所以要么跟进官方文档,要么关注数据库大牛的最新文章,要么在现有数据库环境下自己去亲手测试!

    数据库领域的水很深。。大家加油。。共勉 ~

    展开全文
  • Mysql多索引引起死锁问题

    千次阅读 2019-06-24 16:53:02
    本人原文地址:Mysql多索引引起死锁问题 之前在数据库中,发现一条sql语句会导致死锁,但是其中查询的键都加了索引了,这死锁从何而来呢? 于是去查看了sql语句和建表语句,如下: 一、问题 UPDATE eagle_riskcon...

    本人原文地址:Mysql多索引引起死锁问题

    之前在数据库中,发现一条sql语句会导致死锁,但是其中查询的键都加了索引了,这死锁从何而来呢?

    于是去查看了sql语句和建表语句,如下:

    一、问题

    UPDATE eagle_riskcon_third SET                 msessage = ?               , time = ?             
    where  ip_id = ? and type = ?  
    

    查看时,报错为Deadlock found when trying to get lock

    二、查错

    那么确实是发生了死锁,究竟是因为什么呢?经过查询和找寻资料,发现了问题所在:

           update时,如果where条件里面涉及多个字段,区分度都比较高且字段都分别建了索引的话,mysql会多个索引各走一遍,然后结果取个交集;

           单条记录更新不会引发问题; 多条记录并发更新时,如果索引行数有重叠,因加锁顺序可能不同,互相等待可能会导致死锁,为什么加锁顺序会不同呢?

          我们的sql中where条件的顺序是一定的,那么加锁顺序也应该一定,为什么会有加锁顺序不同情况。情况是这样的:因为我们使用的是两个单值索引,where条件中是复合条件,那么mysql会使用index merge进行优化,优化过程是mysql会先用索引1进行扫表,在用索引2进行扫表,然后求交集形成一个合并索引。这个使用索引扫表的过程和我们本身的sql使用索引的顺序可能存在互斥,所以造成了死锁。

    三、解决

    第一、添加ip_id+type的组合索引,这样就可以避免掉index merge;

    第二、将优化器的index merge优化关闭;

    建议选择第一种方法来避免此问题的发生。

    展开全文
  • Mysql 索引问题-日期索引使用

    万次阅读 2018-03-16 15:07:50
    最近在使用日期索引时,通过explain发现一直不走日期索引,在网上查询了下,发现使用过程中要注意以下情况:1、在查询数据条数约占总条数五分之一以下时能够使用到索引,但超过五分之一时,则使用全表扫描了。...

    最近在使用日期索引时,通过explain发现一直不走日期索引,在网上查询了下,发现使用过程中要注意以下情况:

    1、在查询数据条数约占总条数五分之一以下时能够使用到索引,但超过五分之一时,则使用全表扫描了。

    2、查询条件有日期索引和其他条件的话,只有所有条件都有索引的情况下,才会走日期索引,例如:

    WHERE stringId='1223333' and dayid <='2018-03-15 00:00:00';如果想走dayid 的索引,表中必须有stringId字段的索引才能走,暂时没发现为啥会出现此问题,有知道可以分享下哦

    展开全文
  • mysql like 索引问题

    千次阅读 2019-04-03 15:06:52
    可以看到,只有d后面有%时,type是range也就是索引范围查询,通过索引字段范围获取表中部分数据记录。 当d前面有%时,走的是全索引扫描,和ALL类型类似,只不过ALL类型是全表扫描,而index类型则仅仅扫描所有的所有...

    下面使用的mysql官方测试库测试

    当索引是字符型且是主键时:

    departments表:

    可以看到,只有d后面有%时,type是range也就是索引范围查询,通过索引字段范围获取表中部分数据记录。

    当d前面有%时,走的是全索引扫描,和ALL类型类似,只不过ALL类型是全表扫描,而index类型则仅仅扫描所有的所有,而不扫描数据。

     

    当索引是字符型表中只有索引字段与主键时,结果与上面相同。

    当索引是字符型,表中存在非索引字段时:

    测试表:test_table

     

    可以看到,当d前面没有%时,type是range也就是索引范围查询,通过索引字段范围获取表中部分数据记录。

    当d前面有%时,type是all,即扫描全表,这个类型是查询性能最差的查询之一,这样的查询在数据量大的情况下,随数据库的性能是灾难级的。 这里之所以是all不是index,是因为name字段不存在于value的索引树上,导致了扫表。

     

    当索引是非字符型时:

    employees表:

    无论%加在哪都会进行扫表,type为All,这里因为主键是数字型,查询条件是字符型,数据类型不匹配所以没法通过索引查询

    展开全文
  • pandas Series索引问题

    2016-02-02 07:57:53
    索引列 价格列 索引列是时间,但是由于数据商的原因,毫秒都被标记成了0,因此出现了索引重复的问题 比如说 时间 价格 9:00:00 1500 9:00:00 1499 9:00:01 1498 9:00:01 1498 9:00:02 1497 9:00:03 ...
  • 索引相关问题

    千次阅读 2018-03-09 16:43:57
    今天记录两个索引相关的问题 Q1:主键索引和唯一索引的区别 1.主键是一种约束,唯一索引是一种索引,两者在本质上是不同的。 2.主键创建后一定包含一个唯一性索引,唯一性索引并不一定就是主键。 3.唯一性索引列...
  • 性别字段建立索引问题

    万次阅读 2018-09-25 18:32:35
    性别字段能不能建立索引 之前面试被问到一个问题 ...如:我建了个tb_test表,往里面插入了七百万数据,先给性别建立索引,查询开启 sql运行时间记录。 创建名为tb_index_test 的索引 alter ta...
  • 有时由于数据需求,需要将数据做成二重index,但是对于二重索引和正常一重索引区别很大,下面就讲讲,关于多重索引问题
  • MySQL索引索引长度问题

    千次阅读 2017-01-19 17:14:56
    MySQL的每个单表中所创建的索引长度是有限制的,且对不同存储引擎下的表有不同的限制。 在MyISAM表中,创建组合索引时,创建的索引长度不能超过1000,注意这里索引的长度的计算是根据表字段设定的长度来标量的,例如...
  • 前言:在很系统中,比如本人目前管理的数据库,索引经常被滥用,甚至使用DTA(数据库引擎优化顾问)来成批创建索引(DTA目前个人认为它的真正用处应该是在发现缺失的统计信息,在以前的项目中,用过一次DTA,...
  • Mysql索引命中问题

    千次阅读 2018-11-09 15:33:24
    索引概念和作用   索引是一种使记录有序化的技术,它可以指定按某列/某几列预先排序,从而大大提高查询速度(类似于汉语词典中按照拼音或者笔画查找)。   索引的主要作用是加快数据查找速度,提高数据库的...
  • 联合主键索引问题

    千次阅读 2019-08-05 14:59:27
    字段a、b、c作为联合主键,每个主键字段在什么情况下可以使用索引? 测试环境 mysql8.0.15 InnoDB引擎 测试过程 建立个user表,具体信息如下: user表信息如下: 上表中对id,age,sum这个三个字段设定...
  • python中DataFrame多重索引问题

    千次阅读 2019-06-05 12:25:34
    在Python数据处理中如果实现excel透视表中的功能,则多重索引问题就比较突出了。近来为实现Python自动邮件,需要对数据进行透视表功能,遇到多重索引和表头问题,下面总结下用法。 一、多重索引 常规的单索引这里...
  • 索引

    万次阅读 2020-02-19 23:37:23
    MySQL索引笔记
  • Mysql: mysql between 日期索引 索引问题-日期索引使用 表结构: dep_date  dep arr 联合索引: ind_coll_date_route (dep_date ,dep,arr) 这两天发现原来的查询效率慢了,使用explain 查看,居然没有使用索引, ...
  • MySQL单列索引索引

    千次阅读 2018-09-21 16:12:09
    在设计MySql表索引的时候,可能有个问题,就是个单列索引好,还是设计为索引好;下面从不同角度分析下这个问题;1.个单列索引: 定义:即是在表中在需要索引的字段上为每个字段设计一个索引; 特点:简单,...
  • 假如我在字段A中创建普通单索引,然后又在字段A创建复合索引,但是A不是复合索引 的初始列,现在我只用字段A作为查询条件进行查询,请问会用到索引么,还是全表查询
  • MySQL中不等号索引问题

    千次阅读 2019-01-31 17:22:06
    在主键字段和唯一索引字段中会走索引,在普通索引的字段上不会走索引。   1.当不等号&lt;&gt;作用在普通索引字段上   表信息: 使用explain查看普通索引字段,没有走索引 2.当不等号&lt;&...
  • 前言: 在很系统中,比如本人目前管理的数据库,索引经常被滥用,甚至使用DTA(数据库引擎优化顾问)来成批创建索引(DTA目前个人认为它的真正用处应该是在发现缺失的统计信息,在以前的项目中,用过一次DTA,...
  • pandas层次化索引 # 导入模块,将其别名 import numpy as np import pandas as pd from pandas import Series,DataFrame ...1)最常见的方法是给DataFrame构造函数的index参数传递两个或更的数组,...
  • elasticsearch通过顶部多索引,实现联合查询

    万次阅读 热门讨论 2019-05-06 15:15:38
    之前试了下多索引查询,就是索引以数组的方式进行查询,发现这种方式是可以的。但是如果两个索引的名字,字段都不相同,可以实现类似于mysql的那种join效果吗,试试吧 elasticsearch的多索引联合查询以及范围日期...
  • mysql 日期与索引问题

    万次阅读 2018-07-02 21:05:00
    日期类型可以直接和string格式...'2018-06-02' 可以使用索引, mysql默认会把后面的字符串转成date类型。可以使用between and select * from xxx where date(event_time)>'2018-06-02' 不能使用索引 如果时间戳...
  • eclipse项目无法建立索引问题

    千次阅读 2016-12-26 11:15:53
    我从SVN检索出一个C++项目后,一直无法建立索引。按照网上说的重置indexer办法也没用。试着把项目删除后,重新new一个project,选项按照普通C++项目去选择。 然后在location中选择svn项目在本地的文件夹...
  • Mysql索引面试常见问题

    千次阅读 2020-03-18 23:30:02
    常见的问题: 1)Mysql常用到的存储引擎:MySIAM和Innodb 2)索引的实现级别是表级别 索引为什么不用二叉树? 二叉树的高度不可控,避免出现深度过大的情况 索引为什么不用btree Degree 节点的数据存储个数 :叶...
  • Xpath中的索引问题

    千次阅读 2018-12-31 13:00:29
    xpath使用的索引从1开始,这个地方容易引起习惯性的错误。
  •  最近在一次面试中,讨论了一个这样的问题:主键和索引有什么区别?当时我的回答是这样的:“主键就是加了唯一性约束的聚集索引。” “你确定你所说的是对的?” 面试官反问到。 “应该是对的。” 我不加思索地回答...
  • mysql groupBy 索引问题

    千次阅读 2019-04-19 18:01:05
    其中groupby用到的所有字段都单独建立了索引,也尝试过建立组合索引,explain了一下发现都没有走索引,于是去404网站查了一下,发现了 这个链接 ,其中的loose index scan部分说到了groupby可以使用索引的情况,我...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 939,518
精华内容 375,807
关键字:

多索引问题