精华内容
下载资源
问答
  • 联合索引
    2021-01-18 22:27:50

    一般来说.如果有where a=? and b=? and c=? 的语句.

    如果表也有DML, 我一般只在a 上建索引.  这也是代价平衡的结果. 一方面 只在a 上建索引那么是

    index range scan, 不像联合索引那样可以index  unique scan , 我觉得速度差的不多(数据量不大的情况).

    另一方面,DML也会更新index,更新三个column肯定比更新一个column慢些. 所以我一般采取折中.只建单列或2列索引.

    联合索引对于查询

    where a=? and b=? and c=?

    这样的语句时,速度比分开的索引要快很多!

    我现在有一个应用

    几乎就是按照这种顺序来查找记录的

    所以,我就建立了联合索引

    ----------------------------------------------------

    建立联合索引之后,也不影响你再在这个字段上面创建普通索引。

    索引怎么建立,除了你的程序应用,还应当要考虑到表的活动是否频繁,

    如果是典型的oltp,索引就不要建立太多,位图索引就不用考虑,

    但是dss系统,主要是为了检索,索引多一点就无所谓

    联合索引使用结论:

    1):查询条件中出现联合索引第一列,或者全部,则能利用联合索引.

    2):条件列中只要条件相连在一起,以本文例子来说就是:

    last_name=’1′ and first_name=’1′

    first_name=’1′ and last_name=’1′

    ,无论前后,都会利用上联合索引.

    3):查询条件中没有出现联合索引的第一列,而出现联合索引的第二列,或者第三列,都不会利用联合索引查询.

    单一列索引的应用结论:

    1):只要条件列中出现索引列,无论在什么位置,都能利用索引查询.

    两者的共同点:

    1):要想利用索引,都要符合SARG标准.

    2) :都是为了提高查询速度.

    3):都需要额外的系统开销,磁盘空间.

    补充说明: stmtText信息来产生,在查询语句前面加上:SET STATISTICS PROFILE on.可以通过运行它,来观察你的查询是否合理,这样才能真正做到优化.

    本文主旨:讨论什么情况下能利用上索引.

    索引:创建索引可以根据查询业务的不同分为两种:单一列的索引,联合索引. 顾名思义,单一列索引就是指在表的某一列上创建索引,联合索引是在多个列上联合创建索引.

    优缺点比较:

    1):索引所占用空间:单一列索引相对要小.

    2):索引创建时间:单一列索引相对短.

    3):索引对insert,update,delete的影响程序:单一列索引要相对低.

    4):在多条件查询时,联合索引效率要高.

    索引的使用范围:单一列索引可以出现在where 条件中的任何位置,而联合索引需要按一定的顺序来写.

    本文所用测试软件环境如下:SQL05

    DEMO:创建一个人员表,包含人员ID,姓名.在人员ID上创建一个聚集索引,在first_name和last_name上创建一个联合

    索引.

    create table person (id int, last_name varchar(30), first_name varchar(30))

    create unique clustered index person_id on person (id)

    create index person_name on person (last_name, first_name)

    在上例中,id上创建了聚集索引,下面的查询都会用了聚集索引.

    where id=1

    where id>1

    where id<1

    where id between 1 and n

    where id like ’1%’

    where id in(1,2,3…)

    说明: id 列出现在条件中的位置并不一定要求第一列,不受位置影响.

    不过下面的查询方式则不会用上聚集索引.

    where person_id +1=n

    where person_id like ‘%5′

    where person_id like ‘%5%’

    where person_id abs(15)

    联合索引列比起单一列索引最大的好处在于,对于多条件的查询它比起单一列索引更加精确.拿上面的人员表来说吧,如果

    要查询一个人的全名,只知道first_name是很难马上找到这个人的全名的,如果知道first_name和last_name则会非常容易找到.

    下面根据不同的条件与输出列顺序说明索引的应用.

    第一种情况:–条件和输出列和索引列顺序相同

    select last_name,first_name from person where last_name=’1′ and first_name=’1′

    stmtText

    Index Seek(OBJECT:([bdg_web_vaction].[dbo].[person].[person_name]),

    SEEK:([bdg_web_vaction].[dbo].[person].[last_name]=[@1]

    AND [bdg_web_vaction].[dbo].[person].[first_name]=[@2]) ORDERED FORWARD)

    结果:利用person_name联合索引查找

    第二种情况:–条件列与索引列顺序不同,但输出列相同

    select last_name,first_name from person where first_name=’1′ and last_name=’1′

    stmtText

    Index Seek(OBJECT:([bdg_web_vaction].[dbo].[person].[person_name]),

    SEEK:([bdg_web_vaction].[dbo].[person].[last_name]=[@2] AND [bdg_web_vaction].

    [dbo].[person].[first_name]=[@1]) ORDERED FORWARD)

    结果:利用person_name联合索引查找

    第三种情况:–条件列与输出列与索引列的顺序都不相同

    select first_name,last_name from person where first_name=’1′ and last_name=’1′

    Index Seek(OBJECT:([bdg_web_vaction].[dbo].[person].[person_name]),

    SEEK:([bdg_web_vaction].[dbo].[person].

    [last_name]=[@2] AND [bdg_web_vaction].[dbo].[person].[first_name]=[@1]) ORDERED FORWARD)

    结果:利用person_name联合索引查找

    第四种情况:–条件列在first_name和last_name中间加入另外一个条件

    SELECT id, first_name,last_name from person where first_name=’1′ AND id=1 and last_name=’1′

    Clustered Index Seek(OBJECT:([bdg_web_vaction].[dbo].[person].[person_id]),

    SEEK:([bdg_web_vaction].[dbo].[person].[id]=CONVERT_IMPLICIT(int,[@2],0)),

    WHERE:([bdg_web_vaction].[dbo].[person].[first_name]=[@1] AND [bdg_web_vaction].[dbo].[person].[las

    结果:不能利用person_name联合索引查找

    第五种情况:--在输出列中分开first_name和last_name

    SELECT first_name,id,last_name from person where first_name='1' and last_name='1'

    Index Seek(OBJECT:([bdg_web_vaction].[dbo].[person].[person_name]),

    SEEK:([bdg_web_vaction].[dbo].[person].

    [last_name]=[@2] AND [bdg_web_vaction].[dbo].[person].[first_name]=[@1])

    ORDERED FORWARD)

    结果:利用person_name联合索引查找

    第六种情况:条件列没有出现联合索引的第一列

    SELECT first_name,id,last_name from person where first_name=’1′

    SELECT first_name,last_name from person where first_name=’1′

    SELECT last_name ,first_name from person where first_name=’1′

    Index Scan(OBJECT:([bdg_web_vaction].[dbo].[person].[person_name]),

    WHERE:([bdg_web_vaction].[dbo].[person].[first_name]=[@1]))

    结果:不能利用person_name联合索引.

    第七种情况:–条件列出现联合索引的第一列

    SELECT first_name,id,last_name from person where last_name=’1′

    SELECT first_name,last_name from person where last_name=’1′

    SELECT last_name ,first_name from person where last_name=’1′

    Index Seek(OBJECT:([bdg_web_vaction].[dbo].[person].[person_name]),

    SEEK:([bdg_web_vaction].[dbo].[person].[last_name]=[@1]) ORDERED FORWARD)

    结果:利用person_name联合索引查找

    联合索引使用总结:

    1):查询条件中出现联合索引第一列,或者全部,则能利用联合索引.

    2):条件列中只要条件相连在一起,以本文例子来说就是:

    last_name=’1′ and first_name=’1′

    first_name=’1′ and last_name=’1′

    ,无论前后,都会利用上联合索引.

    3):查询条件中没有出现联合索引的第一列,而出现联合索引的第二列,或者第三列,都不会利用联合索引查询.

    单一列索引的应用总结:

    1):只要条件列中出现索引列,无论在什么位置,都能利用索引查询.

    两者的共同点:

    1):要想利用索引,都要符合SARG标准.

    2) :都是为了提高查询速度.

    3):都需要额外的系统开销,磁盘空间.

    补充说明: stmtText信息来产生,在查询语句前面加上:SET STATISTICS PROFILE on.可以通过运行它,来观察你的查询是否合理,这样才能真正做到优化.

    总结:即使表上创建了索引,但如果查询语句写的不科学的话(不符合SARG标准),也于事无补,要根据表索引情况来优化查询语句,如没有合适的索引可用,则要创建相应索引

    更多相关内容
  • 主要介绍了MySQL联合索引用法,结合实例形式分析了MySQL联合索引的具体定义与使用方法,需要的朋友可以参考下
  • 主要介绍了Mysql联合索引最左匹配原则,使用联合索引的好处多多,具体内容详情大家跟随脚本之家小编一起学习吧
  • Elasticsearch 可以用于:分布式实时文件存储,并将每一个字段都编入索引,使其可以被搜索;实时分析的分布式搜索引擎;可以扩展到上百台服务器,处理PB级别的结构化或非结构化数据。 Elasticsearch的文件存储   ...
  • 主要介绍了MySQL联合索引功能与用法,结合具体实例形式分析了联合索引的概念、功能、具体使用方法与相关注意事项,需要的朋友可以参考下
  • 联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 ...
  • 主要介绍了Laravel Validator 两个或多个字段联合索引唯一,非常不错,具有一定的参考借鉴价值,需要的朋友可以参考下
  • 今天小编就为大家分享一篇Django model 中设置联合约束和联合索引的方法,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧
  • 插入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,或者条件中使用函数等。

     

    展开全文
  • 介绍MySQL联合索引和覆盖索引的联系及特点

    1、一个简单的例子

    首先我们来看一个简单的例子,有表T,有字段如下:

    • uid int(32) 主键
    • a’int(32) 索引列
    • ‘b’ int(32) 非索引列
      执行以下语句:
      1、insert into T values (1,1,1), (3,3,3), (5,5,5), (6,6,6);
      2、select * from T where a between 3 and 5;

    我们来分析下语句2的执行过程:

    • 首先查询条件使用了索引字段a,会根据索引树快速定位到存储a = 3记录的叶子节点(数据页),里面存储有a = 3这条记录的主键值
    • 然后取到a = 3的记录的主键值,然后根据该主键值查询主键索引树,这个过程称为回表查询,主键索引树的叶子节点存储的是一包含所有字段数据的记录,如此就可以取出想要查询的内容了
    • 接着读取下一条a = 5,继续进行回表查询,再读取a = 6,不满足条件,查询结束

    可以看到在这个过程中,虽然在二级索引的叶子节点是连在一起的,只需遍历一次二级索引树,但是由于二级索引叶子节点不包含所有要查询的字段数据,所以每条记录都需要进行一次回表查询,效率比较低下。因此,回表查询的代价也经常作为是否选择索引的依据,看下面的例子:

    1. 创建表:

    create table ‘foo’ (
    ‘uid’ int Not Null,
    ‘name’ varchar(50),
    ‘idx’ int,
    PRIMARY KEY(‘uid’),
    Key ‘idx’ (‘idx’)
    ) engine = InnoDB;

    1. 插入以下数据:
    • ±----±-----±---------+
      | uid | idx | name |
      ±----±-----±---------+
      | 1 | 1 | zhangsan |
      | 4 | 4 | zhangsan |
      | 10 | 10 | zhangsan |
      | 16 | 16 | zhangsan |
      | 20 | 20 | zhangsan |
      ±----±-----±---------+
    1. 查看以下两条语句的执行计划:
    • explain select name,idx from foo where idx >= 10;
      explain select name,idx from foo where idx > 10;

    • 结果如下:
      在这里插入图片描述
      在这里插入图片描述
      可以看到第一条语句使用了全表扫描,原因是name不在二级索引的叶子节点上,需要进行回表查询,而全表只有5条记录且占用很小,mysql判定回表代价(二级索引扫描3条索引记录,回表需扫描3条完整记录)大于全表扫描(扫描5条完整记录)。

      而第二条语句缩减了范围后,回表代价(二级索引扫描2条索引记录,回表2条完整记录)就小于全表扫描(扫描5条完整记录)

      注:需要注意的是,比较扫描代价实际上是比较需要扫描的数据量,比如为该表增加一个长度100的char字段,全表扫描的代价增大,第一条语句就会使用二级索引,感兴趣的同学可自行实验。

    回表会增加额外的代价,那么如何避免回表呢?答案是覆盖索引

    2、覆盖索引

    我们将上面例子的查询语句改为:
    select uid from T where between 3 and 5;

    由于字段a的索引树叶子节点包含了主键列uid,所以在这条语句中,只需要查询一次二级索引树就可以得到全部数据了,也就是索引树a覆盖了查询内容,这种情况就称之为覆盖索引

    但是只查询主键的业务毕竟少,有什么办法可以让覆盖索引大规模应用到项目中吗?答案自然是可以的,联合索引可以帮助我们做到这一点

    3、联合索引

    联合索引也即使用多个字段建立一个索引,联合索引的一个显著的好处就是可以形成覆盖索引,提高查询效率。我们通过一个简单例子来了解

    假设有表tab,有字段a、b、c均为int类型,a经常作为查询条件,可以作为索引列,而b和c则是经常作为查询内容出现,也即经常出现以下语句:

    • select b,c from tab where a = …;

    那么我们就可以建立联合索引 index(a,b,c),这样索引树的叶子节点就会同时具有a、b、c三个字段的数据了,查询时就不需要频繁回表了,大大提高查询效率。

    学到联合索引,就必须了解它的另外一项特性,否则容易踩坑,这项特性名为最左前缀原则

    4、最左前缀原则

    最左前缀原则不仅仅是联合索引独有的特性,在字符串字段中也同样有该特性,我们进行分别讨论

    • 联合索引:查询条件当中必须有声明联合索引时的第一个字段,索引才会生效;
      以上面的例子来说就是a必须出现在查询条件当中,但是出现的顺序却没有要求,如:
      1、where a = 1 and b = 2
      2、where b = 2 and a = 1
      3、where a = 1 and c = 3
      4、where b = 2 and c = 3
      其中,1和2是等价的,只要包含a,顺序无所谓,都是生效的;3的索引也是生效的,尽管b没有作为查询条件,但是实际进行定位的是使用a,故而生效;而4这种情况则不生效

      之所以需要遵循最左前缀原则,是因为联合索引的索引树是先对索引建立时最先声明的字段(a)进行排序,然后再按后面声明的字段依次排序(b、c)

    • 字符串索引:使用字符串索引时,需要从该字符串的第一个字符开始匹配,如有字段name为索引,那么 name like ‘张%’ 这样的查询条件是生效的;而如果没有从第一个字符开始匹配,则索引不会生效,如 name like ‘%三’

    由于维护索引需要耗费一定的空间资源和时间资源。那么基于最左匹配原则,可以适当调整联合索引的字段顺序,使得经常作为查询条件的字段位于最左侧,可以减少索引的数量。如上面的例子中,a作为查询条件的频率较高,那么就让a位于最左侧,这样就不需要再建立单独的索引 index(a)了

    5、索引下推

    联合索引的另外一个重要特性就是索引下推,这是MySQL5.6及以后才有的特性,前文说到联合索引
    index (a,b,c) 在进行查询时实际上是使用a进行定位到具体记录,那么b和c除了作为覆盖索引的产生条件以外就没有其他作用了吗?

    答案是有的,同样用一个简单的例子来说明,对表T执行以下语句:
    select * from T where a > 5 and b < 10;
    我们来分析下执行情况

    • 首先根据a快速定位到符合条件的第一条记录,由于此时不满足覆盖索引的产生条件,因此需要进行回表查询;
    • 在MySQL5.6以下的版本,拿到记录后就会立即进行回表查询,对于b < 10这个条件并不会在二级索引查询时进行判断,也就是说会对所有满足a > 5的记录进行回表查询;
    • 而在MySQL5.6版本,则在二级索引查询时,会下推到联合索引的下一个字段b进行条件判断,倘若不满足b < 10这个条件,那么就不会进行回表查询。这是因为MySQL5.6的引擎接口进行了升级,使其可以支持索引下推的功能
    展开全文
  • 联合索引(也叫组合索引、复合索引、多列索引)是指对表上的多个列进行索引。联合索引的创建方法跟单个索引的创建方法一样,不同之处仅在于有多个索引列。 开讲之前我们先弄一张学生表,表数据如下: 下面我们给出...

    一、前言

    上一节我们讲解了聚集索引和非聚集索引的区别(索引知识系列一:聚集索引与非索引详解 ),我们知道非聚集索引在查询过程中有回表的过程,这就造成了效率的下降。那如何不用回表或者减少回表以提高查询速度呢?这就是本章要讲的内容。

    二、联合索引

    联合索引(也叫组合索引、复合索引、多列索引)是指对表上的多个列进行索引。联合索引的创建方法跟单个索引的创建方法一样,不同之处仅在于有多个索引列。

    开讲之前我们先弄一张学生表,表数据如下:
    在这里插入图片描述
    下面我们给出一个需求:查询表中以字母"L"开头的姓名及年龄。

    1、常规的写法(回表查询)

    SELECT name,age FROM `t_user` where name like 'l%' ;
    

    这种写法,明显效率是低下的,我们用explain 分析一下:
    在这里插入图片描述
    由图中可以看出,在数据库中进行了全表扫描。下面我们看一下数据库中的执行过程。

    第一步:全表扫描数据,找出以“l”开头的主键id.
    第二步:将所有查询出来的数据每一个都回表,根据id来查询出想要的数据。
    

    2.优化写法(索引覆盖)
    因为我们要查询name和age。所以,我们对name和age建立了联合索引,建立后的索引图如下:
    在这里插入图片描述
    从图中我们可以看出,叶子节点中的键值都是按顺序存储的并且都包含了名字和年龄,即(“Ann”,36)、(“HanMeimei”,17)、(“Kate”,17)、(“LiLei”,18)、(“Lili”,16)、(“Lisa”,19)、(“Lucy”,17)、(“WeiHua”,32)、(“ZhangWei”,18)、(“ZhangWei”,25)。

    索引会先根据 name 排序,如果 name 相同,再根据 age 进行排序。

    我们对name和age建立索引后,当我们查询name和age二个字段时,直接会从索引中查出来而不需要回表查询,这种方式就是索引覆盖。执行步骤是这样的:

    第一步:使用联合索引(name,age)查询以“l”开头的数据
    第二步:在索引中取出name和age.
    

    这种方式是不是高效多了,你要是还不信,我们用explain看一下,如下图:

    EXPLAIN SELECT name,age FROM `t_user` where name like 'l%' ;
    

    在这里插入图片描述
    从图中我们看的出,使用了(name,age)索引。

    2.1 联合索引最左匹配原则

    联合索引在使用的时候一定要注意顺序,一定要注意符合最左匹配原则。

    最左匹配原则:在通过联合索引检索数据时,从索引中最左边的列开始,一直向右匹配,如果遇到范围查询(>、<、between、like等),就停止后边的匹配。

    这个定义不太好理解,我解释一下:

    假如对字段 (a, b, c) 建立联合索引,现在有这样一条查询语句:

    where a > xxx and b=yyy and c=zzz
    where a like 'xxx%' and b=yyy and c=zzz
    

    在这个条件语句中,只有a用到了索引,后面的b,c就不会用到索引。这就是“如果遇到范围查询(>、<、between、like等),就停止后边的匹配。”的意思。

    我们还是假如对字段 (a, b, c) 建立联合索引,

    1.如下查询语句可以使用到索引:

    where a = xxx
    where a = xxx and b = xxx
    where a = xxx and b = xxx and c = xxx
    where a like 'xxx%'
    where a > xxx
    where a = xxx order by b
    where a = xxx and b = xxx order by c group by a
    

    2.如下查询条件也会使用索引:

    where b = xxx and a = xxx
    where a = xxx and c = xxx and b = xxx
    

    虽然b和a的顺序换了,但是mysql中的优化器会帮助我们调整顺序。

    3.如下查询条件只用到联合索引的一部分:

    where a = xxx and c = xxx   可以用到 a 列的索引,用不到 c 列索引。
    where a like 'xxx%' and b = xxx 可以用到 a 列的索引,用不到 b 列的索引。
    where a > xxx and b = xxx 可以用到 a 列的索引,用不到 b 列的索引。
    

    4.如下查询条件完全用不到索引

    where b = xxx
    where c = xxx
    where a like '%xxx'			-- 不满足最左前缀
    where d = xxx order by a	-- 出现非排序使用到的索引列 d 
    where a + 1 = xxx	-- 使用函数、运算表达式及类型隐式转换等
    

    如何选择合适的联合索引

    1.where a = xxx and b = xxx and c = xxx 如果我们的查询是这样的,建索引时,就可以考虑将选择性高的列放在索引的最前列,选择性低的放后边。

    2.如果是 where a > xxx and b = xxx 或 where a like ‘xxx%’ and b = xxx 这样的语句,可以对 (b, a) 建立索引。

    3.如果是 where a = xxx order by b 这样的语句,可以对 (a, b) 建立索引。

    三、索引覆盖

    索引覆盖在上面我们已经介绍了。由上面的介绍我们知道,建立了联合索引后,直接在索引中就可以得到查询结果,从而不需要回表查询聚簇索引中的行数据信息。

    索引覆盖可以带来很多的好处:

    • 辅助索引不包含行数据的所有信息,故其大小远小于聚簇索引,因此可以减少大量的IO操作。
    • 索引覆盖只需要扫描一次索引树,不需要回表扫描聚簇索引树,所以性能比回表查询要高。
    • 索引中列值是按顺序存储的,索引覆盖能避免范围查询回表带来的大量随机IO操作。 判断一条语句是否用到索引覆盖:

    这个我们需要用explain查看一下。
    在这里插入图片描述
    Using index 就表示使用到了索引 , 并且所取的数据完全在索引中就能拿到,也就是用到了索引覆盖。

    四、索引下推

    索引下推是索引下推是 MySQL 5.6 及以上版本上推出的,用于对查询进行优化。

    索引下推是把本应该在 server 层进行筛选的条件,下推到存储引擎层来进行筛选判断,这样能有效减少回表。

    举例说明:

    首先使用联合索引(name,age),现在有这样一个查询语句:

    select *  from t_user where name like 'L%' and age = 17;
    

    这条语句从最左匹配原则上来说是不符合的,原因在于只有name用的索引,但是age并没有用到。

    不用索引下推的执行过程:

    第一步:利用索引找出name带'L'的数据行:LiLei、Lili、Lisa、Lucy 这四条索引数据
    第二步:再根据这四条索引数据中的 id 值,逐一进行回表扫描,从聚簇索引中找到相应的行数据,将找到的行数据返回给 server 层。
    第三步:在server层判断age = 17,进行筛选,最终只留下 Lucy 用户的数据信息。
    

    使用索引下推的执行过程:

    第一步:利用索引找出name带'L'的数据行:LiLei、Lili、Lisa、Lucy 这四条索引数据
    第二步:根据 age = 17 这个条件,对四条索引数据进行判断筛选,最终只留下 Lucy 用户的数据信息。
    (注意:这一步不是直接进行回表操作,而是根据 age = 17 这个条件,对四条索引数据进行判断筛选)
    第三步:将符合条件的索引对应的 id 进行回表扫描,最终将找到的行数据返回给 server 层。
    

    比较二者的第二步我们发现,索引下推的方式极大的减少了回表次数。

    索引下推需要注意的情况:

    下推的前提是索引中有 age 列信息,如果是其它条件,如 gender = 0,这个即使下推下来也没用

    开启索引下推:

    索引下推是 MySQL 5.6 及以上版本上推出的,用于对查询进行优化。默认情况下,索引下推处于启用状态。我们可以使用如下命令来开启或关闭。

    set optimizer_switch='index_condition_pushdown=off'; 	-- 关闭索引下推
    set optimizer_switch='index_condition_pushdown=on';		-- 开启索引下
    

    五、结尾

    好了,本章就讲到这里吧,下一章,我们对所有的知识进行一下总结。

    另外大家帮忙关注我,每天更新优质内容。关注我有大量学习资料和学习视频赠送。
    在这里插入图片描述
    扫二维码关注公众号【Java程序员的奋斗路】可领取如下学习资料:
    1.1T视频教程(大约有100多个视频):涵盖Javaweb前后端教学视频、机器学习/人工智能教学视频、Linux系统教程视频、雅思考试视频教程,android.等
    2.项目源码:20个JavaWeb项目源码

    展开全文
  • mysql 里创建‘联合索引’的意义问题?因为什么需求,要创建‘联合索引’?最实际好处在于什么?如果是为了更快查询到数据,有单列索引不是Ok?为什么有‘联合索引’的存在?简单的说有两个主要原因:"一个顶三个"。...
  • 数据库联合索引

    千次阅读 2022-04-11 15:34:15
    数据库、索引、优化、联合索引、组合索引
  • 联合索引-最左匹配原则

    千次阅读 2022-03-28 13:50:27
    举个例子:可以把联合索引看成“电话簿”,姓名作为联合索引,姓是第一列,名是第二列,当查找人名时,是先确定这个人姓再根据名确定人。只有名没有姓就查不到。 2)建多个单列索引: 多个单列索引在多条件查询时...
  • 73 通过一步一图来深入理解联合索引查询原理以及全值匹配规则l.pdf
  • 关于联合索引的加锁分析,网上的文章一直较少,因此在这里记录下自己的分析,用作备忘。 关于一些简单语句的加锁分析,此处就不进行分析了,可以直接参考网上其他的文章。 表结构 CREATE TABLE `aaa` ( `id` int ...
  • mysql之联合索引

    千次阅读 2021-01-19 04:27:11
    mysql之联合索引测试:前期准备:建立联合索引?CREATE TABLE `test` (`id` bigint(16) NOT NULL AUTO_INCREMENT,`aaa` varchar(16) NOT NULL,`bbb` varchar(16) NOT NULL,`ccc` int(11) NOT NULL,PRIMARY KEY (`id`...
  • mysql索引(五)联合索引

    千次阅读 2021-07-14 11:35:42
    Mysql索引大概有五种类型: 普通索引(INDEX):最基本的索引,没有任何限制 唯一索引(UNIQUE):与"普通索引"类似,不同的就是:索引列...联合(组合)索引:为了更多的提高mysql效率可建立组合索引,遵循”最左前缀“原
  • 那么问题来了,如果查询条件为2个及以上,我们是创建多个单列索引好呢,还是建一个联合索引比较好? 正文 我们首先来建一张表,并且指定其中的3个字段(学号、姓名、电话)为联合索引: CREATE TABLE `student` ( `...
  • mysql联合索引详解

    千次阅读 2021-01-20 13:25:24
    联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key index (a,b,c)。 可以支持a | a,b| a,b,c 3种组合进行查找,但不...
  • 能坚持别人不能坚持的,才能拥有别人未曾拥有的。关注编程大道公众号,让我们一同坚持心中所想,一起成长!...但都是基于单值索引,由于文章篇幅原因也只是在文末略提了一下联合索引,并没有大篇幅的展开讨论,所...
  • --联合索引查询第一个、第二个参数(走索引)explain select* from emp where ENAME = 'wang' and JOB单例索引:在数据库的单例上建立的索引。联合索引:在数据库的多列上建立的索引。两者使用区别:如果在一个大量的...
  • 多个单列索引和联合索引的区别详解

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

    千次阅读 多人点赞 2020-08-14 09:49:45
    最困难的事情就是认识自己! 个人网站,欢迎访问! 前言: 本篇文章主要是阐述下 联合索引 在 B+Tree 上的实际存储...很多博客中都是说:联合索引在B+树上的 非叶子节点 中只会存储 联合索引 中的第一个索引字段 的.
  • 在InnoDB中索引分为好几种类型,下面我们一起来看一种常见的索引类型吧。
  • MySQL联合索引的原理

    2021-05-17 15:47:31
    面试中被问到了联合索引,突然就涉及到了知识盲区,对不起,我只知道B+树,B树,哈希索引,聚簇索引,非聚簇索引,联合索引的原理?。。 对不起涉及到了我的知识盲区了。 这里对联合索引做一个总结,联合索引...
  • Oracle 19c 联合索引 vs 多个列索引 我们在创建索引的时候。到底是 选择联合索引还是选择多个单列索引? 环境准备 数据库oracle 19c 100万条随机数据 单列索引(多个) 建表 (多个单列索引测试): CREATE ...
  • MySQL联合索引与索引下推图文详解

    千次阅读 2020-12-11 00:19:29
    联合索引的树结构、最左匹配原则、如何选择合适的索引列顺序、索引下推图文讲解

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 141,116
精华内容 56,446
关键字:

联合索引