精华内容
下载资源
问答
  • 什么时候创建组合索引? 当我们的where查询存在多个条件查询的时候,我们需要对查询的列创建组合索引 为什么不对没一列创建索引 减少开销 覆盖索引 效率高 减少开销:假如对col1、col2、col3创建组合索引,相当...
  • 主要介绍了Mysql之组合索引方法详解,文中通过示例代码和查询结果展示介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧
  • 组合索引

    千次阅读 2020-05-20 06:12:24
    首先我们看看组合索引的与单列索引的区别: 一.为什么要使用联合索引 减少开销 建一个联合索引(col1,col2,col3),实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引,都会增加写操作的...

    首先我们看看组合索引的与单列索引的区别:

    一.为什么要使用联合索引

    1. 减少开销
      建一个联合索引(col1,col2,col3),实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引,都会增加写操作的开销和磁盘空间的开销。对于大量数据的表,使用联合索引会大大的减少开销!

    2. 覆盖索引
      对联合索引(col1,col2,col3),如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据,而无需回表,这减少了很多的随机io操作。减少io操作,特别的随机io其实是dba主要的优化策略。所以,在真正的实际应用中,覆盖索引是主要的提升性能的优化手段之一。

    3. 效率高
      索引列越多,通过索引筛选出的数据越少。有1000W条数据的表,有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据,如果只有单值索引,那么通过该索引能筛选出1000W10%=100w条数据,然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据,然后再排序,再分页;如果是联合索引,通过索引筛选出1000w10% 10% *10%=1w,效率提升可想而知!

    二.组合索引如何使用
    最左原则
    1.例如组合索引(a,b,c),组合索引的生效原则是

    从前往后依次使用生效,如果中间某个索引没有使用,那么断点前面的索引部分起作用,断点后面的索引没有起作用;

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

    两个或更多个列上的索引被称作复合索引。
    利用索引中的附加列,您可以缩小搜索的范围,但使用一个具有两列的索引 不同于使用两个单独的索引。复合索引的结构与电话簿类似,人名由姓和名构成,电话簿首先按姓氏对进行排序,然后按名字对有相同姓氏的人进行排序。如果您知 道姓,电话簿将非常有用;如果您知道姓和名,电话簿则更为有用,但如果您只知道名不姓,电话簿将没有用处。
    所以说创建复合索引时,应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时,复合索引非常有用;仅对后面的任意列执行搜索时,复合索引则没有用处。

    文章参考自
    https://www.cnblogs.com/CKhomepage/p/10613618.html

    展开全文
  • 什么是组合索引?   给多个字段一个索引叫组合索引。 问题:在哪些场景中,组合索引会失效? 场景: 数据表:job_status_trace_log【说明:id是主键】 数据量:35w 创建索引:ALTER table job_status_trace_log add...
    什么是组合索引?

      由多个字段组成的索引叫组合索引。

    问题:在哪些场景中,组合索引会失效?

    场景

    数据表:job_status_trace_log【说明:id是主键】
    数据量:35w
    创建索引:ALTER table job_status_trace_log add INDEX creation_time_index(creation_time,job_name,source)
    

    注意:创建表的时候,字段要超过组合索引字段的个数,因为它会命中覆盖索引,导致跟我下面测试的数据不一致

    例子:组合索引(a,b,c)都有哪些排列组合

    • a,b,c
    • a,c,b
    • c,a,b
    • c,b,a
    • b,c,a
    • a,b
    • b,a
    • a,c
    • c,a
    • b,c
    • a
    • b
    • c
    • …就列举那么多已经够了

    先看看总结,再看验证过程,可能会更棒

    总结:

    1、组合索引字段无论顺序如何改变都会用到索引,前提是所有字段都在where条件上
    2、如果想要使用一个或者两个字段在where条件上,必须有组合索引里的第一个字段,但是与顺序无关,例如a,c或c,a,这种场景是可以命中索引的。但是,b,c或c,b这种是不会命中索引的。
    3、如果组合索引存在范围查询,则组合索引可能会命中索引,这个跟B+Tree的叶子节点中存储的数据是否在当前的叶子节点中,即InnoDB存储引擎的最小存储单元——页,InnoDB页的大小默认是16k,可以通过参数查看页的默认大小:show global status like ‘innodb_page_size’;如果想要修改InnoDB页的大小,需要通过修改mysql源码才可以修改,找到源码文件(storage/innobase/include/univ.i),找到参数:UNIV_PAGE_SIZE,该参数必须是2的n次方,例如4k、8k、16k、32k、64k等等。
    4、order by 只能使用a,才能用到索引

    排列组合一:a,b,c,恭喜成功命中索引
    mysql> explain select * from job_status_trace_log where creation_time = '2020-01-01 00:00:00' and job_name ='member.channelRouteTask' and source='LITE_EXECUTOR'\G
    *************************** 1.***************************
    id           : 1
    select_type  : SIMPLE
    table        : job_status_trace_log
    partitions   : NULL
    type         : ref
    possible_keys: creation_time_index
    key          : creation_time_index
    key_len      : 609
    ref          : const,const,const
    rows         : 1
    filtered     : 100.00
    Extra        : NULL
    1 行于数据集 (0.02)
    
    排列组合二:a,c,b,恭喜成功命中索引
    mysql> explain select * from job_status_trace_log where creation_time = '2020-01-01 00:00:00' and source='LITE_EXECUTOR' and job_name ='member.channelRouteTask'\G
    *************************** 1.***************************
    id           : 1
    select_type  : SIMPLE
    table        : job_status_trace_log
    partitions   : NULL
    type         : ref
    possible_keys: creation_time_index
    key          : creation_time_index
    key_len      : 609
    ref          : const,const,const
    rows         : 1
    filtered     : 100.00
    Extra        : NULL
    1 行于数据集 (0.03)
    
    排列组合三:c,a,b,恭喜成功命中索引
    mysql> explain select * from job_status_trace_log where source='LITE_EXECUTOR' and creation_time = '2020-01-01 00:00:00' and job_name ='member.channelRouteTask'\G
    *************************** 1.***************************
    id           : 1
    select_type  : SIMPLE
    table        : job_status_trace_log
    partitions   : NULL
    type         : ref
    possible_keys: creation_time_index
    key          : creation_time_index
    key_len      : 609
    ref          : const,const,const
    rows         : 1
    filtered     : 100.00
    Extra        : NULL
    1 行于数据集 (0.03)
    
    排列组合四:c,b,a,恭喜成功命中索引
    mysql> explain select * from job_status_trace_log where source='LITE_EXECUTOR' and job_name ='member.channelRouteTask' and creation_time = '2020-01-01 00:00:00'\G
    *************************** 1.***************************
    id           : 1
    select_type  : SIMPLE
    table        : job_status_trace_log
    partitions   : NULL
    type         : ref
    possible_keys: creation_time_index
    key          : creation_time_index
    key_len      : 609
    ref          : const,const,const
    rows         : 1
    filtered     : 100.00
    Extra        : NULL
    1 行于数据集 (0.07)
    
    排列组合五:b,c,a,恭喜成功命中索引
    mysql> explain select * from job_status_trace_log where job_name ='member.channelRouteTask' and source='LITE_EXECUTOR' and creation_time = '2020-01-01 00:00:00'\G
    *************************** 1.***************************
    id           : 1
    select_type  : SIMPLE
    table        : job_status_trace_log
    partitions   : NULL
    type         : ref
    possible_keys: creation_time_index
    key          : creation_time_index
    key_len      : 609
    ref          : const,const,const
    rows         : 1
    filtered     : 100.00
    Extra        : NULL
    1 行于数据集 (0.02)
    
    排列组合六:a,b,恭喜成功命中索引
    mysql> explain select * from job_status_trace_log where creation_time = '2020-01-01 00:00:00' and job_name ='member.channelRouteTask'\G
    *************************** 1.***************************
    id           : 1
    select_type  : SIMPLE
    table        : job_status_trace_log
    partitions   : NULL
    type         : ref
    possible_keys: creation_time_index
    key          : creation_time_index
    key_len      : 407
    ref          : const,const
    rows         : 2
    filtered     : 100.00
    Extra        : NULL
    1 行于数据集 (0.04)
    
    排列组合七:b,a,恭喜成功命中索引
    mysql> explain select * from job_status_trace_log where job_name ='member.channelRouteTask' and creation_time = '2020-01-01 00:00:00'\G
    *************************** 1.***************************
    id           : 1
    select_type  : SIMPLE
    table        : job_status_trace_log
    partitions   : NULL
    type         : ref
    possible_keys: creation_time_index
    key          : creation_time_index
    key_len      : 407
    ref          : const,const
    rows         : 2
    filtered     : 100.00
    Extra        : NULL
    1 行于数据集 (0.03)
    
    排列组合八:a,c,恭喜成功命中索引
    mysql> explain select * from job_status_trace_log where creation_time = '2020-01-01 00:00:00' and source='11'\G 
    *************************** 1.***************************
    id           : 1
    select_type  : SIMPLE
    table        : job_status_trace_log
    partitions   : NULL
    type         : ref
    possible_keys: creation_time_index
    key          : creation_time_index
    key_len      : 5
    ref          : const
    rows         : 17
    filtered     : 10.00
    Extra        : Using index condition
    1 行于数据集 (0.04)
    
    排列组合九:c,a,恭喜成功命中索引
    mysql> explain select * from job_status_trace_log where source='11' and creation_time = '2020-01-01 00:00:00'\G
    *************************** 1.***************************
    id           : 1
    select_type  : SIMPLE
    table        : job_status_trace_log
    partitions   : NULL
    type         : ref
    possible_keys: creation_time_index
    key          : creation_time_index
    key_len      : 5
    ref          : const
    rows         : 17
    filtered     : 10.00
    Extra        : Using index condition
    1 行于数据集 (0.04)
    
    排列组合十:b,c,很遗憾全表扫描了
    mysql> explain select * from job_status_trace_log where job_name ='member.channelRouteTask' and source='LITE_EXECUTOR'\G
    *************************** 1.***************************
    id           : 1
    select_type  : SIMPLE
    table        : job_status_trace_log
    partitions   : NULL
    type         : ALL
    possible_keys: NULL
    key          : NULL
    key_len      : NULL
    ref          : NULL
    rows         : 4316676
    filtered     : 1.00
    Extra        : Using where
    1 行于数据集 (0.03)
    
    排列组合十一:a,恭喜成功命中索引
    mysql> explain select * from job_status_trace_log where creation_time = '2020-01-01 00:00:00'\G
    *************************** 1.***************************
    id           : 1
    select_type  : SIMPLE
    table        : job_status_trace_log
    partitions   : NULL
    type         : ref
    possible_keys: creation_time_index
    key          : creation_time_index
    key_len      : 5
    ref          : const
    rows         : 17
    filtered     : 100.00
    Extra        : NULL
    1 行于数据集 (0.02)
    
    排列组合十二:b,很遗憾全表扫描了
    mysql> explain select * from job_status_trace_log where job_name ='member.channelRouteTask'\G
    *************************** 1.***************************
    id           : 1
    select_type  : SIMPLE
    table        : job_status_trace_log
    partitions   : NULL
    type         : ALL
    possible_keys: NULL
    key          : NULL
    key_len      : NULL
    ref          : NULL
    rows         : 4316693
    filtered     : 10.00
    Extra        : Using where
    1 行于数据集 (0.04)
    
    排列组合十三:c,很遗憾全表扫描了
    mysql> explain select * from job_status_trace_log where source='LITE_EXECUTOR'\G 
    *************************** 1.***************************
    id           : 1
    select_type  : SIMPLE
    table        : job_status_trace_log
    partitions   : NULL
    type         : ALL
    possible_keys: NULL
    key          : NULL
    key_len      : NULL
    ref          : NULL
    rows         : 4316702
    filtered     : 10.00
    Extra        : Using where
    1 行于数据集 (0.06)
    
    排列组合十四:a>0,b=1,c=1,很遗憾全表扫描了
    mysql> explain select * from job_status_trace_log where creation_time > '2020-01-01 00:00:00' and job_name ='member.channelRouteTask' and source='LITE_EXECUTOR'\G
    *************************** 1.***************************
    id           : 1
    select_type  : SIMPLE
    table        : job_status_trace_log
    partitions   : NULL
    type         : ALL
    possible_keys: creation_time_index
    key          : NULL
    key_len      : NULL
    ref          : NULL
    rows         : 4316703
    filtered     : 0.38
    Extra        : Using where
    1 行于数据集 (0.04)
    
    
    排列组合十五:a=1,b>0,c=1,很遗憾全表扫描了
    mysql> explain select * from job_status_trace_log where  job_name ='member.channelRouteTask' and creation_time > '2020-01-01 00:00:00' and source='LITE_EXECUTOR'\G
    *************************** 1.***************************
    id           : 1
    select_type  : SIMPLE
    table        : job_status_trace_log
    partitions   : NULL
    type         : ALL
    possible_keys: creation_time_index
    key          : NULL
    key_len      : NULL
    ref          : NULL
    rows         : 4316727
    filtered     : 0.38
    Extra        : Using where
    1 行于数据集 (0.02)
    
    排列组合十六:a=1,b=1,c>0,很遗憾全表扫描了
    mysql> explain select * from job_status_trace_log where  job_name ='member.channelRouteTask'  and source='LITE_EXECUTOR' and creation_time > '2020-01-01 00:00:00'\G
    *************************** 1.***************************
    id           : 1
    select_type  : SIMPLE
    table        : job_status_trace_log
    partitions   : NULL
    type         : ALL
    possible_keys: creation_time_index
    key          : NULL
    key_len      : NULL
    ref          : NULL
    rows         : 4316730
    filtered     : 0.38
    Extra        : Using where
    1 行于数据集 (0.06)
    
    排列组合十七:a>0,很遗憾全表扫描了
    mysql> explain select * from job_status_trace_log where creation_time > '2020-01-01 00:00:00'\G
    *************************** 1.***************************
    id           : 1
    select_type  : SIMPLE
    table        : job_status_trace_log
    partitions   : NULL
    type         : ALL
    possible_keys: creation_time_index
    key          : NULL
    key_len      : NULL
    ref          : NULL
    rows         : 4316730
    filtered     : 38.09
    Extra        : Using where
    1 行于数据集 (0.03)
    
    总结:
    • 1、组合索引字段无论顺序如何改变都会用到索引,前提是所有字段都在where条件上。
    • 2、如果想要使用一个或者两个字段在where条件上,必须有组合索引里的第一个字段,但是与顺序无关,例如a,c或c,a,这种场景是可以命中索引的。但是,b,c或c,b这种是不会命中索引的。
    • 3、如果组合索引存在范围查询,则组合索引可能会命中索引,这个跟B+Tree的叶子节点中存储的数据是否在当前的叶子节点中,即InnoDB存储引擎的最小存储单元——页,InnoDB页的大小默认是16k,可以通过参数查看页的默认大小:show global status like ‘innodb_page_size’;如果想要修改InnoDB页的大小,需要通过修改mysql源码才可以修改,找到源码文件(storage/innobase/include/univ.i),找到参数:UNIV_PAGE_SIZE,该参数必须是2的n次方,例如4k、8k、16k、32k、64k等等。
    • 4、order by 只能使用a,才能用到索引。

    欢迎关注我的微信公众号,里面有很多干货,各种面试题
    在这里插入图片描述

    展开全文
  • mysql组合索引与字段顺序

    千次阅读 2019-06-11 17:15:25
    一般来说,可能是某些字段没有创建索引,或者是组合索引中字段的顺序与查询语句中字段的顺序不符。 看下面的例子: 假设有一张订单表(orders),包含order_id和product_id二个字段。 一共有31条数据。符合下面语句的...

    很多时候,我们在mysql中创建了索引,但是某些查询还是很慢,根本就没有使用到索引!一般来说,可能是某些字段没有创建索引,或者是组合索引中字段的顺序与查询语句中字段的顺序不符。

    看下面的例子:
    假设有一张订单表(orders),包含order_id和product_id二个字段。
    一共有31条数据。符合下面语句的数据有5条。执行下面的sql语句:

    1

    2

    3

    select product_id

    from orders

    where order_id in (123312223132224);

    这条语句要mysql去根据order_id进行搜索,然后返回匹配记录中的product_id。所以组合索引应该按照以下的顺序创建:

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    create index orderid_productid on orders(order_id, product_id)

    mysql> explain select product_id from orders where order_id in (123312223132224) \G

    *************************** 1. row ***************************

               id: 1

      select_type: SIMPLE

            table: orders

             type: range

    possible_keys: orderid_productid

              key: orderid_productid

          key_len: 5

              ref: NULL

             rows: 5

            Extra: Using where; Using index

    1 row in set (0.00 sec)

    可以看到,这个组合索引被用到了,扫描的范围也很小,只有5行。如果把组合索引的顺序换成product_id, order_id的话,mysql就会去索引中搜索 *123 *312 *223 *132 *224,必然会有些慢了。

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

    16

    17

    18

    19

    mysql> create index orderid_productid on orders(product_id, order_id);                                                      

    Query OK, 31 rows affected (0.01 sec)

    Records: 31  Duplicates: 0  Warnings: 0

     

    mysql> explain select product_id from orders where order_id in (123312223132224) \G

     

    *************************** 1. row ***************************

     

               id: 1

      select_type: SIMPLE

            table: orders

             type: index

    possible_keys: NULL

              key: orderid_productid

          key_len: 10

              ref: NULL

             rows: 31

            Extra: Using where; Using index

    1 row in set (0.00 sec)

    这次索引搜索的性能显然不能和上次相比了。rows:31,我的表中一共就31条数据。索引被使用部分的长度:key_len:10,比上一次的key_len:5多了一倍。不知道是这样在索引里面查找速度快,还是直接去全表扫描更快呢?

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

    16

    17

    18

    19

    mysql> alter table orders add modify_a char(255default 'aaa';

    Query OK, 31 rows affected (0.01 sec)

    Records: 31  Duplicates: 0  Warnings: 0

     

    mysql>

    mysql>

    mysql> explain select modify_a from orders where order_id in (123312223132224) \G         

    *************************** 1. row ***************************

               id: 1

      select_type: SIMPLE

            table: orders

             type: ALL

    possible_keys: NULL

              key: NULL

          key_len: NULL

              ref: NULL

             rows: 31

            Extra: Using where

    1 row in set (0.00 sec)

    这样就不会用到索引了。 刚才是因为select的product_id与where中的order_id都在索引里面的。


    为什么要创建组合索引呢?这么简单的情况直接创建一个order_id的索引不就行了吗?果只有一个order_id索引,没什么问题,会用到这个索引,然后mysql要去磁盘上的表里面取到product_id。如果有组合索引的话,mysql可以完全从索引中取到product_id,速度自然会快。再多说几句组合索引的最左优先原则:
    组合索引的第一个字段必须出现在查询组句中,这个索引才会被用到。果有一个组合索引(col_a,col_b,col_c),下面的情况都会用到这个索引:

    1

    2

    3

    4

    col_a = "some value";

    col_a = "some value" and col_b = "some value";

    col_a = "some value" and col_b = "some value" and col_c = "some value";

    col_b = "some value" and col_a = "some value" and col_c = "some value";

    对于最后一条语句,mysql会自动优化成第三条的样子~~。下面的情况就不会用到索引:

    1

    2

    col_b = "aaaaaa";

    col_b = "aaaa" and col_c = "cccccc";

    列转自:http://hi.baidu.com/liuzhiqun/blog/item/4957bcb1aed1b5590823023c.html

    通过实例理解单列索引、多列索引以及最左前缀原则。实例:现在我们想查出满足以下条件的用户id:
    mysql>SELECT `uid` FROM people WHERE lname`='Liu'  AND `fname`='Zhiqun' AND `age`=26
    因为我们不想扫描整表,故考虑用索引。

    单列索引:
    ALTER TABLE people ADD INDEX lname (lname);
    将lname列建索引,这样就把范围限制在lname='Liu'的结果集1上,之后扫描结果集1,产生满足fname='Zhiqun'的结果集2,再扫描结果集2,找到 age=26的结果集3,即最终结果。

    由 于建立了lname列的索引,与执行表的完全扫描相比,效率提高了很多,但我们要求扫描的记录数量仍旧远远超过了实际所需 要的。虽然我们可以删除lname列上的索引,再创建fname或者age 列的索引,但是,不论在哪个列上创建索引搜索效率仍旧相似。

    2.多列索引:
    ALTER TABLE people ADD INDEX lname_fname_age (lame,fname,age);
    为了提高搜索效率,我们需要考虑运用多列索引,由于索引文件以B-Tree格式保存,所以我们不用扫描任何记录,即可得到最终结果。

    注:在mysql中执行查询时,只能使用一个索引,如果我们在lname,fname,age上分别建索引,执行查询时,只能使用一个索引,mysql会选择一个最严格(获得结果集记录数最少)的索引。

    3.最左前缀:顾名思义,就是最左优先,上例中我们创建了lname_fname_age多列索引,相当于创建了(lname)单列索引,(lname,fname)组合索引以及(lname,fname,age)组合索引。

    注:在创建多列索引时,要根据业务需求,where子句中使用最频繁的一列放在最左边。

    建立索引的时机

    到这里我们已经学会了建立索引,那么我们需要在什么情况下建立索引呢?一般来说,在WHERE和JOIN中出现的列需要建立索引,但也不完全如此,因为MySQL只对<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE才会使用索引。例如:

    1

    SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city='郑州'

    此时就需要对city和age建立索引,由于mytable表的userame也出现在了JOIN子句中,也有对它建立索引的必要。

    刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时,MySQL不会使用索引。例如下句会使用索引:

    1

    SELECT * FROM mytable WHERE username like'admin%'

    下句就不会使用:

    1

    SELECT * FROM mytable WHEREt Name like'%admin'

    因此,在使用LIKE时应注意以上的区别。

    索引的不足之处

    上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:

    • 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。
    • 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快。索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。

    使用索引的注意事项

    使用索引时,有以下一些技巧和注意事项:

    • 索引不会包含有NULL值的列

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

    • 使用短索引

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

    • 索引列排序

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

    • like语句操作

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

    • 不要在列上进行运算

    select * from users where YEAR(adddate)<2007; 

    将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成

    select * from users where adddate<‘2007-01-01’;  

    • 不使用NOT IN和<>操作

     

    原文地址:https://www.cnblogs.com/goody9807/p/7396195.html

    展开全文
  • Mysql组合索引使用和用法

    万次阅读 2018-12-31 17:30:51
    一般来说,可能是某些字段没有创建索引,或者是组合索引中字段的顺序与查询语句中字段的顺序不符。 看下面的例子: 假设有一张订单表(orders),包含order_id和product_id二个字段。 一共有31条数据...

    下列转自:http://www.tech-q.cn/archiver/tid-11673.html

    很多时候,我们在mysql中创建了索引,但是某些查询还是很慢,根本就没有使用到索引!一般来说,可能是某些字段没有创建索引,或者是组合索引中字段的顺序与查询语句中字段的顺序不符。

    看下面的例子:
    假设有一张订单表(orders),包含order_id和product_id二个字段。
    一共有31条数据。符合下面语句的数据有5条。执行下面的sql语句:

    1

    2

    3

    select product_id

    from orders

    where order_id in (123312223132224);

    这条语句要mysql去根据order_id进行搜索,然后返回匹配记录中的product_id。所以组合索引应该按照以下的顺序创建:

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    create index orderid_productid on orders(order_id, product_id)

    mysql> explain select product_id from orders where order_id in (123312223132224) \G

    *************************** 1. row ***************************

               id: 1

      select_type: SIMPLE

            table: orders

             type: range

    possible_keys: orderid_productid

              key: orderid_productid

          key_len: 5

              ref: NULL

             rows: 5

            Extra: Using where; Using index

    1 row in set (0.00 sec)

    可以看到,这个组合索引被用到了,扫描的范围也很小,只有5行。如果把组合索引的顺序换成product_id, order_id的话,mysql就会去索引中搜索 *123 *312 *223 *132 *224,必然会有些慢了。

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

    16

    17

    18

    19

    mysql> create index orderid_productid on orders(product_id, order_id);                                                      

    Query OK, 31 rows affected (0.01 sec)

    Records: 31  Duplicates: 0  Warnings: 0

     

    mysql> explain select product_id from orders where order_id in (123312223132224) \G

     

    *************************** 1. row ***************************

     

               id: 1

      select_type: SIMPLE

            table: orders

             type: index

    possible_keys: NULL

              key: orderid_productid

          key_len: 10

              ref: NULL

             rows: 31

            Extra: Using where; Using index

    1 row in set (0.00 sec)

    这次索引搜索的性能显然不能和上次相比了。rows:31,我的表中一共就31条数据。索引被使用部分的长度:key_len:10,比上一次的key_len:5多了一倍。不知道是这样在索引里面查找速度快,还是直接去全表扫描更快呢?

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

    16

    17

    18

    19

    mysql> alter table orders add modify_a char(255default 'aaa';

    Query OK, 31 rows affected (0.01 sec)

    Records: 31  Duplicates: 0  Warnings: 0

     

    mysql>

    mysql>

    mysql> explain select modify_a from orders where order_id in (123312223132224) \G         

    *************************** 1. row ***************************

               id: 1

      select_type: SIMPLE

            table: orders

             type: ALL

    possible_keys: NULL

              key: NULL

          key_len: NULL

              ref: NULL

             rows: 31

            Extra: Using where

    1 row in set (0.00 sec)

    这样就不会用到索引了。 刚才是因为select的product_id与where中的order_id都在索引里面的。


    为什么要创建组合索引呢?这么简单的情况直接创建一个order_id的索引不就行了吗?果只有一个order_id索引,没什么问题,会用到这个索引,然后mysql要去磁盘上的表里面取到product_id。如果有组合索引的话,mysql可以完全从索引中取到product_id,速度自然会快。再多说几句组合索引的最左优先原则:
    组合索引的第一个字段必须出现在查询组句中,这个索引才会被用到。果有一个组合索引(col_a,col_b,col_c),下面的情况都会用到这个索引:

    1

    2

    3

    4

    col_a = "some value";

    col_a = "some value" and col_b = "some value";

    col_a = "some value" and col_b = "some value" and col_c = "some value";

    col_b = "some value" and col_a = "some value" and col_c = "some value";

    对于最后一条语句,mysql会自动优化成第三条的样子~~。下面的情况就不会用到索引:

    1

    2

    col_b = "aaaaaa";

    col_b = "aaaa" and col_c = "cccccc";

    列转自:http://hi.baidu.com/liuzhiqun/blog/item/4957bcb1aed1b5590823023c.html

    通过实例理解单列索引、多列索引以及最左前缀原则。实例:现在我们想查出满足以下条件的用户id:
    mysql>SELECT `uid` FROM people WHERE lname`='Liu'  AND `fname`='Zhiqun' AND `age`=26
    因为我们不想扫描整表,故考虑用索引。

    单列索引:
    ALTER TABLE people ADD INDEX lname (lname);
    将lname列建索引,这样就把范围限制在lname='Liu'的结果集1上,之后扫描结果集1,产生满足fname='Zhiqun'的结果集2,再扫描结果集2,找到 age=26的结果集3,即最终结果。

    由 于建立了lname列的索引,与执行表的完全扫描相比,效率提高了很多,但我们要求扫描的记录数量仍旧远远超过了实际所需 要的。虽然我们可以删除lname列上的索引,再创建fname或者age 列的索引,但是,不论在哪个列上创建索引搜索效率仍旧相似。

    2.多列索引:
    ALTER TABLE people ADD INDEX lname_fname_age (lame,fname,age);
    为了提高搜索效率,我们需要考虑运用多列索引,由于索引文件以B-Tree格式保存,所以我们不用扫描任何记录,即可得到最终结果。

    注:在mysql中执行查询时,只能使用一个索引,如果我们在lname,fname,age上分别建索引,执行查询时,只能使用一个索引,mysql会选择一个最严格(获得结果集记录数最少)的索引。

    3.最左前缀:顾名思义,就是最左优先,上例中我们创建了lname_fname_age多列索引,相当于创建了(lname)单列索引,(lname,fname)组合索引以及(lname,fname,age)组合索引。

    注:在创建多列索引时,要根据业务需求,where子句中使用最频繁的一列放在最左边。

    建立索引的时机

    到这里我们已经学会了建立索引,那么我们需要在什么情况下建立索引呢?一般来说,在WHERE和JOIN中出现的列需要建立索引,但也不完全如此,因为MySQL只对<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE才会使用索引。例如:

    1

    SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city='郑州'

    此时就需要对city和age建立索引,由于mytable表的userame也出现在了JOIN子句中,也有对它建立索引的必要。

    刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时,MySQL不会使用索引。例如下句会使用索引:

    1

    SELECT * FROM mytable WHERE username like'admin%'

    下句就不会使用:

    1

    SELECT * FROM mytable WHEREt Name like'%admin'

    因此,在使用LIKE时应注意以上的区别。

    索引的不足之处

    上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:

    • 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。
    • 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快。索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。

    使用索引的注意事项

    使用索引时,有以下一些技巧和注意事项:

    • 索引不会包含有NULL值的列

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

    • 使用短索引

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

    • 索引列排序

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

    • like语句操作

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

    • 不要在列上进行运算

    select * from users where YEAR(adddate)<2007; 

    将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成

    select * from users where adddate<‘2007-01-01’;  

    • 不使用NOT IN和<>操作
    展开全文
  • MongoDB组合索引

    千次阅读 2018-08-17 11:00:15
    MongoDB支持组合索引,就是一个索引结构里面包括一个集合文档的多个字段。下图展示了一个拥有两个字段的组合索引组合索引的字段不能超过31个 组合索引查询的时候支持多个字段的匹配   一、创建一个组合...
  • mysql组合索引,abc索引命中

    千次阅读 2020-09-23 15:28:29
    mysql联合索引,abc的争议实践 原因: 在一次和同事讨论mysql联合索引的面试题时出现了争议。主要问题是:a、b、c三个字段作为联合索引,b、c;和a、c情况到底会不会命中索引? 网上查阅相关博客发现很多答案不一样...
  • 组合索引中列的顺序问题

    千次阅读 2020-10-28 17:37:59
    组合索引中列的顺序对执行计划的影响比较大。下面先简单介绍组合索引的适用场景,后面再根据一个具体的SQL说说组合索引中列的顺序对DM7执行计划的影响。 1、使用组合索引查询的场景 兹有 Index (A,B,C) ——组合...
  • 复合索引 /多列索引 /联合索引 /组合索引,一个意思,不同叫法。 含有多个列字段的索引 联合索引也是一棵B+树,不同的是联合索引的键值数量不是1,而是大于等于2. 例如: 创建数据库表 CREATE TABLE `test` ('aaa'...
  • 在网络上有很多MySQL的索引的结构的介绍,相信你对索引的结构已经非常了解了,关于组合索引呢?组合索引是在B+树中的结构是怎么样的?怎么排序的?索引中相同的值,存储的id是什么结构?在本篇文章中就跟你一一说明 ...
  • MySQL单列索引和组合索引的区别介绍

    万次阅读 2018-08-06 14:16:01
    MySQL单列索引是我们使用MySQL数据库中经常会见到的,MySQL单列索引和组合索引的区别可能有很多人还不是十分的了解,下面就为您分析两者的主要区别,供您参考学习。  为了形象地对比两者,再建一个表:   ...
  • Oracle中组合索引的使用详解

    千次阅读 2018-08-11 17:49:02
    在Oracle中可以创建组合索引,即同时包含两个或两个以上列的索引。在组合索引的使用方面,Oracle有以下特点: 1、 当使用基于规则的优化器(RBO)时,只有当组合索引的前导列出现在SQL
  • 发现index merge局限性,优化器会自动判断是否使用 index merge 优化技术,查询还是需要组合索引【推荐阅读:对mysql使用索引的误解】 MySQL单列索引和组合索引(联合索引)的区别详解 初始我写这篇文章的原因在于...
  • innodb组合索引的最左前缀原则

    万次阅读 2020-08-21 16:37:16
    我们在使用数据库的时候,为了增加访问速度,经常会给某张表增加一些索引。单列索引我之前已经写过一篇文章提过,今天我们主要讲一下怎么使用多列索引,和多列索引的最左前缀原则。 关于最左前缀的误解 早些年读高...
  • 数据库组合索引

    千次阅读 2018-03-12 14:59:07
    2018、03、12面试中的问题组合索引是什么?组合索引建立的原则。b树和b+树的区别。一次查询只能用到一个索引,所以 首先枪毙 a,b各建索引方案a还是b? 谁的区分度更高(同值的最少),建谁!当然,联合索引也是个...
  • MySQL单列索引和组合索引的区别

    千次阅读 2017-02-05 20:44:48
    MySQL单列索引和组合索引究竟有何区别呢?下文形象地对比了MySQL单列索引和组合索引的区别,希望...MySQL单列索引和组合索引的区别可能有很多人还不是十分的了解,下面就为您分析两者的主要区别,供您参考学
  • 复合索引(组合索引)

    2019-03-19 13:05:57
    用户可以在多个列上建立索引,这种索引叫做复合索引(组合索引); 复合索引在数据库操作期间所需的开销更小,可以代替多个单一索引; 同时有两个概念叫做窄索引和宽索引,窄索引是指索引列为1-2列的索引,宽索引也就是索引...
  • 背景: 为了提高数据库效率,建索引是家常便饭;那么...
  • 前言: 5月初面试了字节跳动抖音电商实习岗,一上来,面试官就问了组合索引的问题,并且给出了很多题用于判断哪些情况下组合索引会生效,当时答得很差,只记之前在网上看了下什么范围查找会失效,面试下来才知道错...
  • 索引之---- 唯一索引和组合索引

    千次阅读 2019-06-19 11:01:37
    之前一直没明白唯一索引和组合索引是做什么用,用在什么地方,怎么用的。看了网上很多的博客,愣是没看懂。最近项目刚好涉及到这方面,所以码一下使用经验。 普通索引就不解释了,直接上硬货: 序号① 为唯一...
  • 点击链接 普通索引 唯一索引 全文索引 组合索引 ...
  • MongoDB组合索引的优化

    千次阅读 2018-07-02 09:37:14
    MongoDB中组合索引的最佳建立方法以及索引中字段的最优顺序。并通过explain()输出的结果来验证实际性能,同时还分析了MongoDB的查询优化器的索引选择机制。项目背景预想中的项目是在MongoDB上建立一个类Disqus的...
  • mysql如何创建组合索引

    千次阅读 2019-08-15 10:50:08
    组合索引,也就是表中的某几列数据组合起来,结果要是唯一的。 具体语法为: CREATE UNIQUE INDEX index ON table( col1, col2, col3 ) ; table为表名,括号里为字段名 需注意:varchar字段不能太长,太长会报错 ...
  • 在创建组合索引时,网上一般的说法会提到:查询时where条件中的列的顺序要和创建索引时列的顺序保持一致,否则组合索引会失效。 更进一步来说,组合索引的各个列中,到底谁应该在前,谁应该在后,还是说创建时顺序...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 324,125
精华内容 129,650
关键字:

组合索引