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

    万次阅读 多人点赞 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引擎在某次升级中早就自优化了。

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

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

    展开全文
  • 联合索引列索引)

    万次阅读 多人点赞 2018-08-07 15:37:09
    联合索引是指对表上的个列进行索引,联合索引也是一棵B+树,不同的是联合索引的键值数量不是1,而是大于等于2. 最左匹配原则 假定上图联合索引的为(a,b)。联合索引也是一棵B+树,不同的是B+树在对索引a排序...

    联合索引是指对表上的多个列进行索引,联合索引也是一棵B+树,不同的是联合索引的键值数量不是1,而是大于等于2.

    最左匹配原则

    假定上图联合索引的为(a,b)。联合索引也是一棵B+树,不同的是B+树在对索引a排序的基础上,对索引b排序。所以数据按照(1,1),(1,2)......顺序排放。

    对于selete * from table where a=XX and b=XX,显然是可以使用(a,b)联合索引的,

    对于selete * from table where a=XX,也是可以使用(a,b)联合索引的。因为在这两种情况下,叶子节点中的数据都是有序的。

    但是,对于b列的查询,selete * from table where b=XX。则不可以使用这棵B+树索引。可以发现叶子节点的b值为1,2,1,4,1,2。显然不是有序的,因此不能使用(a,b)联合索引。

    By the way:selete * from table where b=XX and a=XX,也是可以使用到联合索引的,你可能会有疑问,这条语句并不符合最左匹配原则。这是由于查询优化器的存在,mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高,最后才生成真正的执行计划。所以,当然是我们能尽量的利用到索引时的查询顺序效率最高咯,所以mysql查询优化器会最终以这种顺序进行查询执行。

    优化:在联合索引中将选择性最高的列放在索引最前面。

    例如:在一个公司里以age 和gender为索引,显然age要放在前面,因为性别就两种选择男或女,选择性不如age。

    展开全文
  • MySQL联合索引

    万次阅读 2020-06-02 22:31:35
    联合索引又叫复合索引,即一个覆盖中两列或者以上的索引,例如: index_name(column a,column b) 1 创建方式 执行alter table语句时创建 alter table table_name add index index_name(column_list) 1 index_name...

    联合索引概念:

    联合索引又叫复合索引,即一个覆盖表中两列或者以上的索引,例如:

    index_name(column a,column b)

    创建方式
    执行alter table语句时创建
    alter table table_name add index index_name(column_list)

    index_name是创建的联合索引的名字,可以没有,没有的话系统会根据该索引包含的第一列来赋名称;table_name是要创建该索引的表名;column_list为该索引所包含的表的字段名。

    执行create index语句时创建
    create index index_name on table_name(column_list)

    此种情况是在表已经创建好的情况下,再来创建复合索引。index_name和column_list同上;table_name是要创建索引的表名。

    例子

    create table stu
    (
    id int,
    name varchar(10),
    age int,
    primary key(id)
    );
    ALTER TABLE stu ADD INDEX LianHeIndex (name,age);
    或者
    create index LianHeIndex on stu(name,age);
    

    执行上面的语句后在表stu中就创建好了一个名叫LianHeIndex联合索引,在使用联合索引的时候,我们遵守一个最左原则,即INDEX LianHeIndex (name,age)支持name|name age组合查询,而不支持age查询;换句话说,在执行

    select * from stu where name=?

    或者

    select * from stu where name=? and age=?

    时联合索引才会有效,如果执行

    select * from stu where age=?

    则联合索引不会生效。

    如果我们是在name和age上分别创建单个索引的话,由于mysql查询每次只能使用一个索引,所以虽然这样已经相对不做索引时全表扫描提高了很多效率,但是如果在name、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(name, age)的复合索引,那么其实相当于创建了(name)、(name,age)两个索引,这被称为最佳左前缀特性。

    因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边,依次递减。

    注意事项
    只要列中包含有NULL值都将不会被包含在索引中
    复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的,所以我们在数据库设计时尽可能不要让字段的默认值为NULL。

    展开全文
  • 联合索引

    2020-06-16 15:08:42
    联合索引(各种索引) 聚集索引和非聚集索引 数据库中B+树索引可以分为聚集索引和非聚集索引(辅助索引) 聚集索引 每张只有一个聚集索引,且是建立在主键上面的。...联合索引上的个列...

    联合索引(各种索引)

    聚集索引和非聚集索引

    数据库中B+树索引可以分为聚集索引和非聚集索引(辅助索引)

     

    聚集索引

    每张表只有一个聚集索引,且是建立在主键上面的。

     

    主键索引

    在InnoDB存储引擎中,每张表都有个主键,如果在创建表时没有显式地定义主键,则InnoDB存储引擎会按如下方式选择或创建主键

    • 首先判断表中是否存在非空的唯一索引,如果有,则该列即为主键
    • 如果不符合上述条件,InnoDB存储引擎自动创建一个6字节大小的指针。

     

     

     

    联合索引

    联合索引在表上的多个列进行索引。

    1.联合索引

    创建如下的表,并创建一个联合索引(a,b)

     

    CREATE TABLE t( a INT, b INT, PRIMARY KEY (a), KEY idx_a_b (a,b) )ENGINE=INNODB

     

     

    2.能够使用联合索引的情况

    ①全匹配

    select * from t where a=xxx and b=xxx

    ②最左前缀匹配

    对于单个的a列,也可以用到(a,b)联合索引

    select * from t where a=xxx

    ③不能使用联合索引

    叶子节点上b的值为1,2,1,4,1,2,显然不是排序的。

    select * from t where b=xxx

     

    同理,如果建立(a,b,c)索引,则下面的查询都能用到索引。

    select * from t where a=xxx and b=xxx and c=xxx select * from t where a=xxx and b=xxx select * from t where a=xxx select * from t where b=xxx and c=xxx

     

    3.联合索引可对第二个列进行排序处理,减少一次filesort。

    在联合索引(a,b)中,由于a相同的情况下b本来就是排序的,所以下面的查询能够用到(a,b)索引,且不需要额外再进行排序。

    select * from t where a=xxx order by b

     

    同理,如果建立(a,b,c)索引,下面的查询也能少一次fileSort。

    select * from t where a=xxx and b=xxx order by c select * from t where b=xxx order by c select * from t where a=xxx order by b

    展开全文
  • 建立联合索引

    2015-09-25 16:21:00
    建立联合索引后,以两个字段为例,如果想查询速度快,where条件中要么带上两个字段,要么带上前面的,如果只有后面的不会变快。 实例:以下联合索引:itemid和clock 这个联合索引 如果where字段查...
  • mysql联合索引

    千次阅读 2018-08-04 16:09:58
    联合索引又叫复合索引,即一个覆盖中两列或者以上的索引 ,例如:index (column a,column b)。 1.2创建方式 1.2.1执行alter table语句时创建 alter table table_name add index index—name(column_list) ...
  • 联合索引优化条件查询

    万次阅读 2016-07-19 00:39:31
    联合索引是由个字段组成的组合索引。若经常需要使用个字段的条件查询(WHERE col1 = … AND col2 = … AND col3 = …),可以考虑使用联合索引。现在数据myIndex中i_testID是主键列,其他列无任何索引:...
  • mysql联合索引优化(锁)

    千次阅读 2019-11-14 10:34:43
    1.场景 通过案例,我们能发现77w数据,查100条,时间57s ...遇到这种问题,创建联合索引就行 ...注意,联合索引是偏左原则,如有3列 A B C where条件后通过c过滤,而没有A和B,C列是不会走索引的 ...
  • 一.oracle回扫描例子:test 10w数据,10个字段,主键为id1.select * from test where id = 5回扫描介绍:执行这个sql的执行计划,可以看到,首先它走了主键的索引,可以看到一个index rang...
  • 复合索引 /列索引 /联合索引 /组合索引,一个意思,不同叫法。 含有个列字段的索引 联合索引也是一棵B+树,不同的是联合索引的键值数量不是1,而是大于等于2. 例如: 创建数据库 CREATE TABLE `test` ('aaa'...
  • oracle 联合索引

    2015-01-28 11:15:18
    oracle 联合索引 比如我有 TABLE_A 字段ABCD 创建联合索引 create index indx_t on TABLE_A(B,C); 能使用索引的条件: where 条件 有B 或者 B 和 C 只有C 无法使用索引...
  • mysql 联合索引生效的条件、索引失效的条件

    万次阅读 多人点赞 2019-02-23 10:11:30
    1.联合索引失效的条件 联合索引又叫复合索引。两个或更个列上的索引被称作复合索引。 对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key...
  • mysql联合索引测试

    千次阅读 2018-05-11 17:41:43
    mysql联合索引和查询条件的匹配原则:只有当联合索引的第一个字段在where条件中出现时,才会使用索引查询,不然就是全扫描查询。的索引为:KEY`Index_query`(`transorderid`,`userorderid`,`tradetype`,`orgcode...
  • Oracle联合索引

    2015-10-15 22:24:29
    Oracle 联合索引 分类: Oracle 2012-12-08 09:35 2110人阅读 评论(0) 收藏 举报 我现在用的是oracle 9i 1.一个建立多少个索引比较合适?比如说不大于5个 这个没有定论,楼主要综合查询效率和dml效率自己...
  • 数据库之联合索引

    千次阅读 2017-09-18 16:37:55
     1):查询条件中出现联合索引第一列,或者全部,则能利用联合索引.  2):条件列中只要条件相连在一起,以本文例子来说就是:  last_name=’1′ and first_name=’1′  与  first_name=’1′ and last_name=’1′ ...
  • SQL联合索引

    千次阅读 2017-04-04 16:06:47
    1):查询条件中出现联合索引第一列,或者全部,则能利用联合索引. 2):条件列中只要条件相连在一起,以本文例子来说就是: last_name=’1′ and first_name=’1′ 与 first_name=’1′ and last_name=’1′ ...
  • 单列索引和联合索引

    2020-12-13 20:56:03
    联合索引的结构与电话簿类似,人名由姓和名构成,电话簿首先按姓氏进行排序,然后按名字对有相同姓氏的人进行排序。如果您知道姓,电话簿将非常有用,如果您知道姓和名,电话簿则更为有用,但如果您只知道名不知道姓...
  • 数据库-联合索引

    千次阅读 2018-05-28 17:54:28
    实际例子分析面试过程中经常问到的联合索引,给定一个查询SQL和联合索引,问是否会走索引。结构:CREATE TABLE person ( id int(2) NOT NULL, address varchar(255) DEFAULT NULL, age int(11) NOT NULL, name...
  • 那么当查询条件为2个及以上时,我们是创建个单列索引还是创建一个联合索引好呢?他们之间的区别是什么?哪个效率高呢? 一、联合索引测试 注:Mysql版本为 5.7 创建测试: CREATE TABLE `jlyx_user` ( `...
  • 联合索引:优化条件查询

    千次阅读 2018-07-04 11:52:57
    联合索引是由个字段组成的组合索引。若经常需要使用个字段的条件查询(WHERE col1 = … AND col2 = … AND col3 = …),可以考虑使用联合索引。现在数据myIndex中i_testID是主键列,其他列无任何索引:...
  • 那么当查询条件为2个及以上时,我们是创建个单列索引还是创建一个联合索引好呢?他们之间的区别是什么?哪个效率高呢?我在这里详细测试分析下。 一、联合索引测试 注:Mysql版本为 5.7.20 创建测试(记录...
  • 联合索引(复合索引)和单个索引

    千次阅读 2019-02-23 15:56:41
    那么当查询条件为2个及以上时,我们是创建个单列索引还是创建一个联合索引好呢?他们之间的区别是什么?哪个效率高呢?我在这里详细测试分析下。 一、联合索引测试 注:Mysql版本为 5.7.20 创建测试(记录数...
  • Mysql联合索引生效判断

    千次阅读 2020-07-09 15:43:10
    对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key index (a,b,c)。 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最...
  • 那么当查询条件为2个及以上时,我们是创建个单列索引还是创建一个联合索引好呢?他们之间的区别是什么?哪个效率高呢?我在这里详细测试分析下。 一、联合索引测试 注:Mysql版本为 5.7.20 创建测试(记录数...
  • 全文索引----创建多表solr索引

    万次阅读 热门讨论 2016-05-07 21:10:39
     我们在使用solr作为索引服务器时,通常会将个字段作为联合索引,对快速的数据查询也是solr服务器高效率的体现。这片文章介绍下如何基于个数据创建索引。  一 无关联多表创建索引  1.1 ...
  • (当一个中查询大的情况下,where条件中有个,如果使用个单列索引,根据mysql优化器策略,造成可能只使用一个索引,其他索引会失效,导致会全盘扫描,具体看下面链接) 组合索引,即一个索包含个列。(当...
  • 本文实例讲述了MySQL联合索引。分享给大家供大家参考,具体如下: 员工 userid 部门 deptid 员工部门 条件:一个员工可以对应个部门 问题:怎么样设置数据库,让其不能重复添加 userid 和deptid? uuid ...
  • Elasticsearch 可以用于:分布式实时文件存储,并将每一个字段都编入索引,使其可以被搜索;实时分析的分布式搜索引擎;可以扩展到上百台服务器,处理PB级别的结构化或非结构化数据。 Elasticsearch的文件存储   ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 96,107
精华内容 38,442
关键字:

多表联合索引