精华内容
下载资源
问答
  • 主要介绍了MySQL联合索引用法,结合实例形式分析了MySQL联合索引的具体定义与使用方法,需要的朋友可以参考下
  • 在mysql建立联合索引时会遵循最左前缀匹配的原则,即最左优先,在检索数据时从联合索引的最左边开始匹配,示例: 对列col1、列col2和列col3建一个联合索引 KEY test_col1_col2_col3 on test(col1,col2,col3); 联合...
  • Elasticsearch 可以用于:分布式实时文件存储,并将每一个字段都编入索引,使其可以被搜索;实时分析的分布式搜索引擎;可以扩展到上百台服务器,处理PB级别的结构化或非结构化数据。 Elasticsearch的文件存储   ...
  • 今天小编就为大家分享一篇关于一个案例彻底弄懂如何正确使用mysql inndb联合索引,小编觉得内容挺不错的,现在分享给大家,具有很好的参考价值,需要的朋友一起跟随小编来看看吧
  • 本文实例讲述了MySQL联合索引功能与用法。分享给大家供大家参考,具体如下: 联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如...
  • 主要介绍了Laravel Validator 两个或多个字段联合索引唯一,非常不错,具有一定的参考借鉴价值,需要的朋友可以参考下
  • 查询背景 有一个表tmp_test_course大概有10万条记录,然后有个json字段叫outline,存了一对多关系(保存了多个编码,例如jy1577683381775) 我们需要在这10万条数据中检索特定类型的数据,目标总数据量:2931条 ...
  • 年前从深圳这边的公司离职了,然后回西安上周五新入职的新公司。然后入职的第一个任务居然是优化SQL。 有如下两条SQL, select sum(silvers_num) from tb_video_cost_log where anchor_id = ...
  • 网站系统上线至今,数据量已经不知不觉上到500M,近8W记录了。涉及数据库操作的基本都是变得很慢了,这篇文章主要是说明配置并不是数据库操作慢的主要原因
  • 插入a、b、c联合索引 ALTER TABLE `t_demo` ADD INDEX `INDEX_A_B_C` ( `a`, `b`, `c` ) USING BTREE; 4.测试 采用explain查看执行计划,其中key就是使用索引情况,如果对explain不太了解可以看这篇:...

    1.建表

    CREATE TABLE `t_demo` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `a` varchar(15) DEFAULT NULL,
      `b` varchar(15) DEFAULT NULL,
      `c` varchar(15) DEFAULT NULL,
      `d` varchar(15) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `INDEX_A_B_C` (`a`,`b`,`c`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

    2.插入10万条数据

    快速生产10万数据方法,执行main方法后,会将sql文件存入E盘,放到MySQL执行就行了

        public static void main(String[] args) throws IOException {
            for (int x = 1; x <= 100; x++) {
                StringBuilder sql = new StringBuilder("INSERT INTO `t_demo`(a, b, c, d) VALUES ");
                for (int i = 1; i <= 999; i++) {
                    splice(sql, ",");
                }
                splice(sql, ";");
                sql.append("\r\n");
                //System.out.println(sql);
                File file = new File("E:/demo.sql");
                FileWriter fw = new FileWriter(file, true);
                BufferedWriter bw = new BufferedWriter(fw);
                bw.write(sql.toString());
                bw.close();
                fw.close();
            }
        }
    
        private static void splice(StringBuilder sql, String s) {
            String value = "('%s', '%s', '%s', '%s')";
            String a = RandomStringUtils.randomNumeric(4);
            String b = RandomStringUtils.random(2, true, false);
            String c = RandomStringUtils.random(5, true, false);
            String d = String.valueOf(System.currentTimeMillis());
            sql.append(String.format(value, a, b, c, d)).append(s);
        }

     

    3.插入a、b、c联合索引

    ALTER TABLE `t_demo` ADD INDEX `INDEX_A_B_C` ( `a`, `b`, `c` ) USING BTREE;

    4.测试

    采用explain查看执行计划,其中key就是使用索引情况,如果对explain不太了解可以看这篇:https://blog.csdn.net/Anenan/article/details/114525818

    1.WHERE条件是a、b、c三个,查询abc所有排列组合情况:

    mysql> EXPLAIN SELECT * FROM `t_demo` WHERE a = "8166" AND b = "Or" AND c = "tGMvk";
    +----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
    | id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref               | rows | filtered | Extra |
    +----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
    |  1 | SIMPLE      | t_demo | NULL       | ref  | INDEX_A_B_C   | INDEX_A_B_C | 144     | const,const,const |    1 |   100.00 | NULL  |
    +----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
    1 row in set (0.01 sec)
    
    mysql> EXPLAIN SELECT * FROM `t_demo` WHERE a = "8166" AND c = "tGMvk" AND b = "Or";
    +----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
    | id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref               | rows | filtered | Extra |
    +----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
    |  1 | SIMPLE      | t_demo | NULL       | ref  | INDEX_A_B_C   | INDEX_A_B_C | 144     | const,const,const |    1 |   100.00 | NULL  |
    +----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
    1 row in set (0.01 sec)
    
    mysql> EXPLAIN SELECT * FROM `t_demo` WHERE b = "Or" AND a = "8166" AND c = "tGMvk";
    +----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
    | id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref               | rows | filtered | Extra |
    +----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
    |  1 | SIMPLE      | t_demo | NULL       | ref  | INDEX_A_B_C   | INDEX_A_B_C | 144     | const,const,const |    1 |   100.00 | NULL  |
    +----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
    1 row in set (0.02 sec)
    
    mysql> EXPLAIN SELECT * FROM `t_demo` WHERE b = "Or" AND c = "tGMvk" AND a = "8166";
    +----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
    | id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref               | rows | filtered | Extra |
    +----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
    |  1 | SIMPLE      | t_demo | NULL       | ref  | INDEX_A_B_C   | INDEX_A_B_C | 144     | const,const,const |    1 |   100.00 | NULL  |
    +----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
    1 row in set (0.02 sec)
    
    mysql> EXPLAIN SELECT * FROM `t_demo` WHERE c = "tGMvk" AND a = "8166" AND b = "Or";
    +----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
    | id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref               | rows | filtered | Extra |
    +----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
    |  1 | SIMPLE      | t_demo | NULL       | ref  | INDEX_A_B_C   | INDEX_A_B_C | 144     | const,const,const |    1 |   100.00 | NULL  |
    +----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
    1 row in set (0.03 sec)
    
    mysql> EXPLAIN SELECT * FROM `t_demo` WHERE c = "tGMvk" AND b = "Or" AND a = "8166";
    +----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
    | id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref               | rows | filtered | Extra |
    +----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
    |  1 | SIMPLE      | t_demo | NULL       | ref  | INDEX_A_B_C   | INDEX_A_B_C | 144     | const,const,const |    1 |   100.00 | NULL  |
    +----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
    1 row in set (0.03 sec)

    2.WHERE条件是a、b、c选两个,查询abc两个中所有排列组合情况:

    mysql> EXPLAIN SELECT * FROM `t_demo` WHERE a = "8166" AND b = "Or";
    +----+-------------+--------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+
    | id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref         | rows | filtered | Extra |
    +----+-------------+--------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+
    |  1 | SIMPLE      | t_demo | NULL       | ref  | INDEX_A_B_C   | INDEX_A_B_C | 96      | const,const |    1 |   100.00 | NULL  |
    +----+-------------+--------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+
    1 row in set (0.01 sec)
    
    mysql> EXPLAIN SELECT * FROM `t_demo` WHERE b = "Or" AND a = "8166";
    +----+-------------+--------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+
    | id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref         | rows | filtered | Extra |
    +----+-------------+--------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+
    |  1 | SIMPLE      | t_demo | NULL       | ref  | INDEX_A_B_C   | INDEX_A_B_C | 96      | const,const |    1 |   100.00 | NULL  |
    +----+-------------+--------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+
    1 row in set (0.02 sec)
    
    mysql> EXPLAIN SELECT * FROM `t_demo` WHERE a = "8166" AND c = "tGMvk";
    +----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
    | id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra                 |
    +----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
    |  1 | SIMPLE      | t_demo | NULL       | ref  | INDEX_A_B_C   | INDEX_A_B_C | 48      | const |   13 |    10.00 | Using index condition |
    +----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
    1 row in set (0.02 sec)
    
    mysql> EXPLAIN SELECT * FROM `t_demo` WHERE c = "tGMvk" AND a = "8166";
    +----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
    | id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra                 |
    +----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
    |  1 | SIMPLE      | t_demo | NULL       | ref  | INDEX_A_B_C   | INDEX_A_B_C | 48      | const |   13 |    10.00 | Using index condition |
    +----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
    1 row in set (0.02 sec)
    
    mysql> EXPLAIN SELECT * FROM `t_demo` WHERE b = "Or" AND c = "tGMvk";
    +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
    | id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
    +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
    |  1 | SIMPLE      | t_demo | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99918 |     1.00 | Using where |
    +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
    1 row in set (0.03 sec)
    
    mysql> EXPLAIN SELECT * FROM `t_demo` WHERE c = "tGMvk" AND b = "Or";
    +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
    | id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
    +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
    |  1 | SIMPLE      | t_demo | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99918 |     1.00 | Using where |
    +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
    1 row in set (0.03 sec)

    3.WHERE条件是a、b、c其中一个的情况:

    mysql> EXPLAIN SELECT * FROM `t_demo` WHERE a = "8166";
    +----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
    | id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
    +----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
    |  1 | SIMPLE      | t_demo | NULL       | ref  | INDEX_A_B_C   | INDEX_A_B_C | 48      | const |   13 |   100.00 | NULL  |
    +----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
    1 row in set (0.02 sec)
    
    mysql> EXPLAIN SELECT * FROM `t_demo` WHERE b = "Or";
    +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
    | id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
    +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
    |  1 | SIMPLE      | t_demo | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99918 |    10.00 | Using where |
    +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
    1 row in set (0.02 sec)
    
    mysql> EXPLAIN SELECT * FROM `t_demo` WHERE c = "tGMvk";
    +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
    | id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
    +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
    |  1 | SIMPLE      | t_demo | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99918 |    10.00 | Using where |
    +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
    1 row in set (0.02 sec)

    4.结果分析

    1. 查询条件是a、b、c时,无论是什么顺序,由于优化器优化,都会走INDEX_A_B_C联合索引;
    2. 查询条件是a、b时,会走联合索引;
    3. 查询条件是a、c时,也会走联合索引,但是Extra信息里面多了一行:Using index condition,意思是先条件过滤索引,过滤完索引后找到所有符合索引条件的数据行,随后用WHERE子句中的其他条件去过滤这些数据行,这种情况只有a条件用到联合索引,c条件回表到聚簇索引过滤。
    4. 查询条件是b、c时,不走联合索引;
    5. 查询条件是a时,会走联合索引;
    6. 查询条件是b时,不走联合索引;
    7. 查询条件是c时,不走联合索引;

    5.总结

    联合索引符合最左匹配原则,按照索引建的顺序,一个查询可以只使用索引中的一部份,但只能是最左侧部分。

    例如:以a、b、c为顺序建的联合索引,条件为下列情况都能生效:

    1. WHERE a = ?
    2. WHERE a = ? AND b = ?
    3. WHERE a = ? AND b = ? AND c = ?

    注意:与WHERE后面的条件顺序无关,优化器会将条件顺序优化成上面三种情况后执行。

    另外 WHERE a = ? AND c = ? 也会走联合索引,但是只有a条件命中,c条件不走联合索引。

    还有,需要避免索引失效的情况,如:LIKE %xxx,或者条件中使用函数等。

     

    展开全文
  • 多个单列索引和联合索引的区别详解

    万次阅读 多人点赞 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-06-16 15:08:42
    联合索引(各种索引) 聚集索引和非聚集索引 数据库中B+树索引可以分为聚集索引和非聚集索引(辅助索引) 聚集索引 每张表只有一个聚集索引,且是建立在主键上面的。 主键索引 在InnoDB存储引擎中,每张表都有...

    联合索引(各种索引)

    聚集索引和非聚集索引

    数据库中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

    展开全文
  • MySQL B+树如何实现联合索引 “同学,你来画一下MySQL的B+树如何实现联合索引的?” “额,这个嘛……这个……俺不晓得……” 之前大言不惭说对MySQL还算了解的我今天被这个问题糊的一脸懵逼,本着对问题的求知和...

    MySQL B+树如何实现联合索引

    “同学,你来画一下MySQL的B+树如何实现联合索引的?”

    “额,这个嘛……这个……俺不晓得……”

    我是菜狗

    之前大言不惭说对MySQL还算了解的我今天被这个问题糊的一脸懵逼,本着对问题的求知和探索精神,今天就来聊一聊这个问题,MySQL B+树如何实现联合索引。

    MySQL索引的数据结构B+树

    我们都知道在MySQL的InnoDB引擎中,数据的存储是基于聚簇索引来进行构建的,聚簇索引的数据结构为B+树,关于这部分的内容在之前的文章中已经讲过,这里我们再来回顾一下。
    传送门:闲聊MySQL:(七)InnoDB之索引结构

    B+树

    上图就是一棵B+树的示意图,在InnoDB的B+树聚簇索引数据结构中,只有在数据页(即叶子节点)上存放的是完整的每行记录,而在非数据页的索引页中,存放的仅仅是键值及指向数据页的偏移量,而不是一个完整的行记录。

    同时,从物理存储结构上来看,聚簇索引的存储并不是连续的,而是逻辑上连续的。

    这其中有两点:

    1、叶子节点是通过双向链表进行链接的,页按照主键的顺序排序;

    2、每个页中的记录也是通过双向链表进行维护的,物理存储上可以同样不按照主键存储。

    而对于二级索引(辅助索引)来说,其数据结构同样是一样的B+树结构,不同的是,其叶子节点不包含记录的全部数据。叶子节点除了包含键值以外,每个叶子节点中的索引行中还包含了一个书签。该书签用来告诉InnoDB引擎哪里可以找到与索引对应的行数据。即其叶子节点存储的是执行聚簇索引键值的指针。
    二级索引
    好了,前情回顾完毕,复习了一下聚簇索引和二级索引的B+树结构,下面进入正题,我们来说说联合索引的结构。

    联合索引的B+树结构

    首先我们来看一个问题,我们有一个表user,有几个字段idc1c2c3c4,其中id是主键,c1、c2、c3字段建立联合索引,那么执行下面几个SQL,来看一下索引执行情况:

    select * from user where c1= 12 and c2= 14 and c3 = 3 // 索引全匹配
    select * from user where c1= 12 and c2= 14 // 索引部分匹配
    select * from user where c1= 12 and c3 = 3 // 索引部分匹配
    select * from user where c2= 12 and c3 = 3 // 索引无法匹配

    可以看到,对于联合索引,全部命中索引字段可以执行索引;部分命中并符合最左匹配原则,也可能会执行索引;不满足最左匹配原则,则无法命中索引,那么这是为什么呢?

    我们来分析一下对于联合索引的B+树的数据结构。
    联合索引数据结构
    上图就是一个联合索引的B+树示意图,InnoDB会使用聚簇索引在B+树维护索引和数据文件,然后我们创建了一个联合索引name、age、point也会生成一个索引树,同样是B+树的结构,只不过它的data部分存储的是联合索引所在行的主键值。

    对于联合索引来说只不过比单值索引多了几列,而这些索引列全都出现在索引树上。对于联合索引,存储引擎会首先根据第一个索引列排序,如上图我们可以单看第一个索引列,横着看,如,1 1 5 12 13…他是单调递增的;如果第一列相等则再根据第二列排序,依次类推就构成了上图的索引树,上图中的c1列都等于1时,则根据c2排序,此时c2列也相等则按c3列排序,如:1 1 4 ,1 1 5,c=4在c=5前面,以及13 12 4,13 16 1,13 16 5就可以说明这种情况。

    联合索引的查找方式

    当我们的SQL可以应用到联合索引的时候,比如select * from user where c1= 12 and c2= 14 and c3 = 3

    存储引擎首先从根节点(一般常驻内存)开始查找:

    • 第一个索引的第一个索引列为1,12大于1;
    • 第二个索引的第一个索引列为56,12小于56;
    • 于是从这俩索引的中间读到下一个节点的磁盘文件地址,从磁盘上Load这个节点,通常伴随一次磁盘IO,然后在内存里去查找。
    • 当Load叶子节点的第二个节点时又是一次磁盘IO,比较第一个元素,b=12,c=14,d=3完全符合,于是找到该索引下的data元素即ID值,再从主键索引树上找到最终数据。

    查找过程

    最左匹配原则

    好了,上面我们了解了联合索引的B+树检索过程,那么再来考虑另一个问题,为什么联合索引会有最左匹配原则?

    之所以会有最左前缀匹配原则和联合索引的索引构建方式及存储结构是有关系的。

    首先我们创建的c1、c2、c3索引,相当于创建了(c1)、(c1、c2)(c1、c2、c3)三个索引,看完下面你就知道为什么相当于创建了三个索引。
    我们看,联合索引是首先使用多列索引的第一列构建的索引树,用上面的例子就是优先使用c1列构建,当c1列值相等时再以c2列排序,若c2列的值也相等则以c3列排序。我们可以取出索引树的叶子节点看一下。


    索引的第一列也就是c1列可以说是从左到右单调递增的,但我们看c2列和c3列并没有这个特性,它们只能在c1列值相等的情况下这个小范围内递增,如第一叶子节点的第1、2个元素和第二个叶子节点的后三个元素。

    由于联合索引是上述那样的索引构建方式及存储结构,所以联合索引只能从多列索引的第一列开始查找。所以如果你的查找条件不包含c1列如(c2, c3)、(c2)、(c3) 是无法应用缓存的,以及跨列也是无法完全用到索引如(c1, c3),只会用到c1列索引。

    结语

    MySQL的索引机制其实还是比较复杂的,全面理解还是需要较深入的学习实践才可以充分掌握,本文如有不对的地方,欢迎指正探讨,完结撒花~

    展开全文
  • 联合索引在B+Tree上的存储结构及数据查找方式

    千次阅读 多人点赞 2020-08-14 09:49:45
    最困难的事情就是认识自己! 个人网站,欢迎访问! 前言: 本篇文章主要是阐述下 联合索引 在 B+Tree 上的实际存储...很多博客中都是说:联合索引在B+树上的 非叶子节点 中只会存储 联合索引 中的第一个索引字段 的.
  • 联合索引的树结构、最左匹配原则、如何选择合适的索引列顺序、索引下推图文讲解
  • 那么当查询条件为2个及以上时,我们是创建多个单列索引还是创建一个联合索引好呢?他们之间的区别是什么?哪个效率高呢?我在这里详细测试分析下。 一、联合索引测试 注:Mysql版本为 5.7.20 创建测试表(表记录数...
  • 1.学习了mysql联合索引,以及联合索引使用的注意事项。 联合索引:MySQL中使用多个字段同时建立一个索引联合索引。 在联合索引中,如果想要命中索引,需要按照建立索引时的字段顺序一次使用,否则无法命中索引。 在...
  • Mysql联合索引生效判断

    千次阅读 2020-07-09 15:43:10
    对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key index (a,b,c)。 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最...
  • 背景 索引:IDX(b,c) sql select id where b = xx; select id where c = xx; 上面的两句sql会走b,c的联合索引吗? 答案是第一条会走,第二条不会 ...只要没有使用联合索引的第一个字段,则不会走联合索引 ...
  • 联合索引(复合索引)和单个索引

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

    千次阅读 2019-03-16 17:57:45
    mysql普通索引以及联合索引介绍 命名规则:表名_字段名 1、需要加索引的字段,要在where条件中 2、数据量少的字段不需要加索引 3、如果where条件中是OR关系,加索引不起作用 4、符合最左原则 ...
  • MySQL联合索引性能比较

    千次阅读 2019-05-16 09:56:45
    在分析联合索引性能之前,温故下基础知识。 1 数据结构 1.1 B-树 一个m阶树满足以下条件: 每个节点至多拥有m颗子树; 根节点至少2颗子树(若存在子树的情况下); 非根节点至少拥有m/2颗子树,其范围为m/2 &...
  • 73 通过一步一图来深入理解联合索引查询原理以及全值匹配规则l.pdf
  • 聚簇索引 介绍 聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。InnoDB的聚簇索引实际上是通过一个结构中保存了B-Tree索引和数据行。因为无法同时把数据行存在两个不同的地方,所以一个表只能有一个聚簇...
  • 单列索引和联合索引

    2020-12-13 20:56:03
    联合索引的结构与电话簿类似,人名由姓和名构成,电话簿首先按姓氏进行排序,然后按名字对有相同姓氏的人进行排序。如果您知道姓,电话簿将非常有用,如果您知道姓和名,电话簿则更为有用,但如果您只知道名不知道姓...
  • 在《面试官:为啥加了索引查询会变快?》一文中,我们介绍了索引的数据结构,正是因为索引使用了B+树,才使得查询变快。说白了,索引的原理就是减少查询的次数、减少磁盘IO,达到快速查找所需数据的目的 我们一起来...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 116,167
精华内容 46,466
关键字:

联合索引