精华内容
参与话题
问答
  • 多个单列索引和联合索引的区别详解

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

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

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

    展开全文
  • 联合索引

    万次阅读 2020-09-15 23:06:32
    MySQL可以使用多个字段同时建立一个索引,叫做联合索引.在联合索引中,如果想要命中索引,需要按照建立索引时的字段顺序挨个使用,否则无法命中索引。 MySQL使用索引时需要索引有序,假设现在建立了"name,age,school"的...

           MySQL可以使用多个字段同时建立一个索引,叫做联合索引.在联合索引中,如果想要命中索引,需要按照建立索引时的字段顺序挨个使用,否则无法命中索引。

          MySQL使用索引时需要索引有序,假设现在建立了"name,age,school"的联合索引,那么索引的排序为: 先按照name排序,如果name相同,则按照age排序,如果age的值也相等,则按照school进行排序.

    当进行查询时,此时索引仅仅按照name严格有序,因此必须首先使用name字段进行等值查询,之后对于匹配到的列而言,其按照age字段严格有序,此时可以使用age字段用做索引查找,,,以此类推.因此在建立联合索引的时候应该注意索引列的顺序,一般情况下,将查询需求频繁或者字段选择性高的列放在前面.此外可以根据特例的查询或者表结构进行单独的调整.

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

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

    1.联合索引失效的条件

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

     

    对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key index (a,b,c)。 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 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‘%字符串%’时索引不被使用的方法?

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

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

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

    一般性建议

    1. 对于单键索引,尽量选择针对当前query过滤性更好的索引
    2. 在选择组合索引的时候,当前Query中过滤性最好的字段在索引字段顺序中,位置越靠前越好。
    3. 在选择组合索引的时候,尽量选择可以能够包含当前query中的where子句中更多字段的索引
    4. 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

     

    展开全文
  • MySQL联合索引

    2018-12-19 19:49:33
    一、什么是联合索引  两个或更多个列上的索引被称作联合索引联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key ...

    一、什么是联合索引

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

    二、命名规则

    1、需要加索引的字段,要在where条件中
    2、数据量少的字段不需要加索引
    3、如果where条件中是OR关系,加索引不起作用
    4、符合最左原则

    三、创建索引

    在执行CREATE TABLE语句时可以创建索引,也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。

    【1】ALTER TABLE

    ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。
    例如:
    ALTER TABLE table_name ADD INDEX index_name (column_list)
    ALTER TABLE table_name ADD UNIQUE (column_list)
    ALTER TABLE table_name ADD PRIMARY KEY (column_list)
      其中table_name是要增加索引的表名,column_list指出对哪些列进行索引,多列时各列之间用逗号分隔。索引名index_name可选,缺省时,MySQL将根据第一个索引列赋一个名称。另外,ALTER TABLE允许在单个语句中更改多个表,因此可以在同时创建多个索引。

    【2】CREATE INDEX

    CREATE INDEX可对表增加普通索引或UNIQUE索引。
    例如:
    CREATE INDEX index_name ON table_name (column_list)
    CREATE UNIQUE INDEX index_name ON table_name (column_list)
    table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义,索引名不可选。另外,不能用CREATE INDEX语句创建PRIMARY KEY索引。

    四、索引类型

      在创建索引时,可以规定索引能否包含重复值。如果不包含,则索引应该创建为PRIMARY KEY或UNIQUE索引。对于单列惟一性索引,这保证单列不包含重复的值。对于多列惟一性索引,保证多个值的组合不重复。
      PRIMARY KEY索引和UNIQUE索引非常类似。
      事实上,PRIMARY KEY索引仅是一个具有名称PRIMARY的UNIQUE索引。这表示一个表只能包含一个PRIMARY KEY,因为一个表中不可能具有两个同名的索引。
      下面的SQL语句对students表在sid上添加PRIMARY KEY索引。
        ALTER TABLE students ADD PRIMARY KEY (sid)

    五、删除索引

      可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句,DROP INDEX可以在ALTER TABLE内部作为一条语句处理,语法如下。
    DROP INDEX index_name ON talbe_name
    ALTER TABLE table_name DROP INDEX index_name
    ALTER TABLE table_name DROP PRIMARY KEY
      其中,前两条语句是等价的,删除掉table_name中的索引index_name。

    第3条语句只在删除PRIMARY KEY索引时使用,因为一个表只可能有一个PRIMARY KEY索引,因此不需要指定索引名。如果没有创建PRIMARY KEY索引,但表具有一个或多个UNIQUE索引,则MySQL将删除第一个UNIQUE索引。
    如果从表中删除了某列,则索引会受到影响。对于多列组合的索引,如果删除其中的某列,则该列也会从索引中删除。如果删除组成索引的所有列,则整个索引将被删除。

    六、什么情况下使用索引

    【1】为了快速查找匹配WHERE条件的行。
    【2】为了从考虑的条件中消除行。
    【3】如果表有一个multiple-column索引,任何一个索引的最左前缀可以通过使用优化器来查找行。
    【4】查询中与其它表关联的字,字段常常建立了外键关系
    【5】查询中统计或分组统计的字段
    select max(hbs_bh) from zl_yhjbqk
    select qc_bh,count(*) from zl_yhjbqk group by qc_bh

    七、注意事项

    MySql在建立索引优化时需要注意的问题

     

    设计好MySql的索引可以让你的数据库飞起来,大大的提高数据库效率。设计MySql索引的时候有一下几点注意:

    1,创建索引

    对于查询占主要的应用来说,索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的,或者说没有添加更为有效的索引导致。如果不加

    索引的话,那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描,如果一张表的数据量很大而符合条件的结果又很少,那么不加索引会引起致命的性能下降。
    但是也不是什么情况都非得建索引不可,比如性别可能就只有两个值,建索引不仅没什么优势,还会影响到更新速度,这被称为过度索引。

    2,复合索引

    比如有一条语句是这样的:select * from users where area=’beijing’ and age=22;

    如果我们是在area和age上分别创建单个索引的话,由于mysql查询每次只能使用一个索引,所以虽然这样已经相对不做索引时全表扫描提高了很多效

    率,但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age,salary)的复合索引,那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引,这被称为最佳左前缀特性。
    因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边,依次递减。

    3,索引不会包含有NULL值的列

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

    4,使用短索引

    对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的 列,如果在前10 个或20 个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

    5,排序的索引问题

    mysql查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引

    6,like语句操作

    一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

    7,不要在列上进行运算

    select * from users where

    YEAR(adddate)

    8,不使用NOT IN和操作

    NOT IN和操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替

    三、使用explain分析索引

    在不确定应该在哪些数据列上创建索引的时候,我们可以从EXPLAIN SELECT命令那里往往可以获得一些帮助。这其实只是简单地给一条普通的SELECT命令加一个EXPLAIN关键字作为前缀而已。有了这个关键字,MySQL将不是去执行那条SELECT命令,而是去对它进行分析。MySQL将以表格的形式把查询的执行过程和用到的索引(如果有的话)等信息列出来。这里我基本阐述下每个信息字段含义,不展开阐述,我们只要注意几个关键点(关键点以下用红色加粗显示)能大概看懂即可呵呵~~

    1、id:SQL执行的顺序的标识。

    sql从里向外执行,通过以上观察发现sql是按照id从大到小执行的。

    2、select_type: select类型

    1)、SIMPLE(不使用UNION或子查询等) 

    2) 、PRIMARY:最外层的select

    3)、DERIVED:派生表的SELECT(FROM子句的子查询)

    4)、UNION:UNION中的第二个或后面的SELECT语句

     

    5)、UNION RESULT:UNION的结果。

    6)、DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询

     

    7)、SUBQUERY:子查询中的第一个SELECT

    8)、DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询

    9、table:表的名字。

    有时不是真实的表名字,看到的是derivedx(x是个数字,我的理解是第几步执行的结果)

    10、type:连接操作的类型。

    这列很重要,显示了连接使用了哪种类别,有无使用索引。在各种类型的关联关系当中,效率最高的是system,然后依次是const、eq_ref、ref、range、index和 All。一般来说,得保证查询至少达到range级别,最好能达到ref,否则就可能会出现性能问题。

     

    1)、system 

    表只有一行:system表。这是const连接类型的特殊情况

    2)、const 

    表中的一个记录的最大值能够匹配这个查询(索引可以是主键或惟一索引)。因为只有一行,这个值实际就是常数,因为MYSQL先读这个值然后把它当做常数来对待 

    3)、eq_ref 

    在连接中,MYSQL在查询时,从前面的表中,对每一个记录的联合都从表中读取一个记录,它在查询使用了索引为主键或惟一键的全部时使用 

    4)、ref 

    这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分(比如,利用最左边前缀)时发生。对于之前的表的每一个行联合,全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少(越少越好)

    5)、range 

    这个连接类型使用索引返回一个范围中的行,比如使用>或<查找东西时发生的情况 

    6)、index 

    这个连接类型对前面的表中的每一个记录联合进行完全扫描(比ALL更好,因为索引一般小于表数据) 

    7)、ALL 

    这个连接类型对于前面的每一个记录联合进行完全扫描,这一般比较糟糕,应该尽量避免。因为它要扫描整个表。你可以加入更多的索引来解决这个问题。

    5、possible_key:MySQL在搜索数据记录时可以选用的各个索引名。

    这里的索引名是创建索引时指定的索引昵称;如果索引没有昵称,则默认显示的是索引中第一个列的名字(在上一节举的例子中是“firstname”)。默认索引名字的含义往往不是很明显。

    6、key:它显示了MySQL实际使用的索引。

    key数据列是MySQL实际选用的索引,如果它为空(或NULL),则MySQL不使用索引。

    7、key_len:索引中被使用部分的长度,以字节计。

    key_len的值可以告诉你在联合索引中mysql会真正使用了哪些索引。 在上例中,key_len是102,其中firstname占50字节,lastname占50字节,age占2字节(smallint存储大小为2字节)。如果MySQL只使用索引中的firstname部分,则key_len将是50。 在不损失精确性的情况下 ,key_len数据列里的值越小越好(意思是更快)。

    8、ref:显示使用哪个列或常数与key一起从表中选择行。

    ref数据列给出了关联关系中另一个数据表里的数据列的名字。

    9、rows:MySQL所认为的它在找到正确的结果之前必须扫描的记录数。

    显然,这里最理想的数字就是1。

    10、extra:附加信息

    Using index和Using where会遇到的比较多,可以重点记下,其他的我没怎么遇到过了解即可,遇到具体问题可以查阅哈

    1)、Distinct 

    一旦MYSQL找到了与行相联合匹配的行,就不再搜索了 

    2)、Not exists 

    MYSQL优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行,就不再搜索了 

    3)、Range checked for each 

    没有找到理想的索引,因此对于从前面表中来的每一个行组合,MYSQL检查使用哪个索引,并用它来从表中返回行。这是使用索引的最慢的连接之一 

    4)、Using filesort 

    看到这个的时候,查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行 

    5)Using index 

    列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,这发生在对表的全部的请求列都是同一个索引的部分的时候 

    6)Using temporary 

    看到这个的时候,查询需要优化了。这里,MYSQL需要创建一个临时表来存储结果,这通常发生在对不同的列集进行ORDER BY上,而不是GROUP BY上   

    7)Using where

    使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行,并且连接类型ALL或index,这就会发生,或者是查询有问题

     

    展开全文
  • 在《面试官:为啥加了索引查询会变快?》一文中,我们介绍了索引的数据结构,正是因为索引使用了B+树,才使得查询变快。说白了,索引的原理就是减少查询的次数、减少磁盘IO,达到快速查找所需数据的目的 我们一起来...
  • 聚集索引 primary key 唯一索引 unique 普通索引 alter table t1 all index index_name(id) 索引的作用是加速查找 覆盖索引: select name from t1 where id = 20 命中了辅助索引但是为覆盖到索引 还需要从聚集索引...
  • 联合索引 指定了多少个列的关键字 作为B+树的关键字 联合索引是一个索引 联合索引比较时从左开始 所以遵循最左前置规则。 单列索引是一种特殊的联合索引 当一个A = 1 B > 2 C 3列的联合索引 这样就用不到C这个...
  • 聚簇索引 介绍 聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。InnoDB的聚簇索引实际上是通过一个结构中保存了B-Tree索引和数据行。因为无法同时把数据行存在两个不同的地方,所以一个表只能有一个聚簇...
  • 主要介绍了MySQL联合索引用法,结合实例形式分析了MySQL联合索引的具体定义与使用方法,需要的朋友可以参考下
  • 1.学习了mysql联合索引,以及联合索引使用的注意事项。 联合索引:MySQL中使用多个字段同时建立一个索引联合索引。 在联合索引中,如果想要命中索引,需要按照建立索引时的字段顺序一次使用,否则无法命中索引。 在...
  • 这是一个实验总结帖,包括联合索引的实验 和总结。我们是以邮件的形式发送和回复的 所以只能隐去真实姓名 请见谅。请自底到顶阅读 。==================================================...
  • 联合索引和唯一索引

    2019-10-09 23:37:06
    联合索引和唯一索引 背景:目前WEB的普及太快,很多网站都会因为大流量的数据而发生服务器习惯性死机,一个查询语句只能适用于一定的网络环境.没有优化的查询当遇上大数据量时就不适用了. 联合索引使用结论: 1):查询...
  • 联合索引和单列索引

    2019-04-12 18:04:00
    2. 联合索引又叫复合索引,它是对多个字段同时建立的索引(有顺序,ABC,ACB是完全不同的两种联合索引)。对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。...
  • 今天小编就为大家分享一篇Django model 中设置联合约束和联合索引的方法,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧

空空如也

1 2 3 4 5 ... 20
收藏数 4,315
精华内容 1,726
关键字:

联合索引