通过与 Jira 对比,让您更全面了解 PingCode

  • 首页
  • 需求与产品管理
  • 项目管理
  • 测试与缺陷管理
  • 知识管理
  • 效能度量
        • 更多产品

          客户为中心的产品管理工具

          专业的软件研发项目管理工具

          简单易用的团队知识库管理

          可量化的研发效能度量工具

          测试用例维护与计划执行

          以团队为中心的协作沟通

          研发工作流自动化工具

          账号认证与安全管理工具

          Why PingCode
          为什么选择 PingCode ?

          6000+企业信赖之选,为研发团队降本增效

        • 行业解决方案
          先进制造(即将上线)
        • 解决方案1
        • 解决方案2
  • Jira替代方案

25人以下免费

目录

Mysql的覆盖索引和联合索引的区别在哪里

联合索引时指对表上的多个列合起来做一个索引。联合索引的创建方法与单个索引的创建方法一样;InnoDB存储引擎支持覆盖索引(covering index,或称索引覆盖),即从辅助索引中就可以得到查询记录。

一、Mysql的覆盖索引和联合索引的区别

联合索引

  • 联合索引时指对表上的多个列合起来做一个索引。联合索引的创建方法与单个索引的创建方法一样,不同之处在仅在于有多个索引列,如下

mysql> create table t(

    -> a int,

    -> b int,

    -> primary key(a),

    -> key idx_a_b(a,b)

    -> );

Query OK, 0 rows affected (0.11 sec)

  • 那么何时需要使用联合索引呢?在讨论这个问题之前,先来看一下联合索引内部的结果。从本质上来说,联合索引就是一棵B+树,不同的是联合索引的键值得数量不是1,而是>=2。接着来讨论两个整型列组成的联合索引,假定两个键值得名称分别为a、b如图
  • 可以看到这与我们之前看到的单个键的B+树并没有什么不同,键值都是排序的,通过叶子结点可以逻辑上顺序地读出所有数据,就上面的例子来说,即(1,1),(1,2),(2,1),(2,4),(3,1),(3,2),数据按(a,b)的顺序进行了存放。
  • 因此,对于查询select * from table where a=xxx and b=xxx, 显然是可以使用(a,b) 这个联合索引的,对于单个列a的查询select * from table where a=xxx,也是可以使用(a,b)这个索引的。
  • 但对于b列的查询select * from table where b=xxx,则不可以使用(a,b) 索引,其实你不难发现原因,叶子节点上b的值为1、2、1、4、1、2显然不是排序的,因此对于b列的查询使用不到(a,b) 索引
  • 联合索引的第二个好处是在名列前茅个键相同的情况下,已经对第二个键进行了排序处理,例如在很多情况下应用程序都需要查询某个用户的购物情况,并按照时间进行排序,最后取出最近三次的购买记录,这时使用联合索引可以帮我们避免多一次的排序操作,因为索引本身在叶子节点已经排序了,如下

#===========准备表==============

create table buy_log(

    userid int unsigned not null,

    buy_date date

);

insert into buy_log values

(1,’2009-01-01′),

(2,’2009-01-01′),

(3,’2009-01-01′),

(1,’2009-02-01′),

(3,’2009-02-01′),

(1,’2009-03-01′),

(1,’2009-04-01′);

alter table buy_log add key(userid);

alter table buy_log add key(userid,buy_date);

#===========验证==============

mysql> show create table buy_log;

| buy_log | CREATE TABLE `buy_log` (

  `userid` int(10) unsigned NOT NULL,

  `buy_date` date DEFAULT NULL,

  KEY `userid` (`userid`),

  KEY `userid_2` (`userid`,`buy_date`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

#可以看到possible_keys在这里有两个索引可以用,分别是单个索引userid与联合索引userid_2,但是优化器最终选择了使用的key是userid因为该索引的叶子节点包含单个键值,所以理论上一个页能存放的记录应该更多

mysql> explain select * from buy_log where userid=2;

+—-+————-+———+——+—————–+——–+———+——-+——+——-+

| id | select_type | table   | type | possible_keys   | key    | key_len | ref   | rows | Extra |

+—-+————-+———+——+—————–+——–+———+——-+——+——-+

|  1 | SIMPLE      | buy_log | ref  | userid,userid_2 | userid | 4       | const |    1 |       |

+—-+————-+———+——+—————–+——–+———+——-+——+——-+

1 row in set (0.00 sec)

#接着假定要取出userid为1的最近3次的购买记录,用的就是联合索引userid_2了,因为在这个索引中,在userid=1的情况下,buy_date都已经排序好了

mysql> explain select * from buy_log where userid=1 order by buy_date desc limit 3;

+—-+————-+———+——+—————–+———-+———+——-+——+————————–+

| id | select_type | table   | type | possible_keys   | key      | key_len | ref   | rows | Extra                    |

+—-+————-+———+——+—————–+———-+———+——-+——+————————–+

|  1 | SIMPLE      | buy_log | ref  | userid,userid_2 | userid_2 | 4       | const |    4 | Using where; Using index |

+—-+————-+———+——+—————–+———-+———+——-+——+————————–+

1 row in set (0.00 sec)

#ps:如果extra的排序显示是Using filesort,则意味着在查出数据后需要二次排序(如下查询语句,没有先用where userid=3先定位范围,于是即便命中索引也没用,需要二次排序)

mysql> explain select * from buy_log order by buy_date desc limit 3;

+—-+————-+———+——-+—————+———-+———+——+——+—————————–+

| id | select_type | table   | type  | possible_keys | key      | key_len | ref  | rows | Extra                       |

+—-+————-+———+——-+—————+———-+———+——+——+—————————–+

|  1 | SIMPLE      | buy_log | index | NULL          | userid_2 | 8       | NULL |    7 | Using index; Using filesort |

+—-+————-+———+——-+—————+———-+———+——+——+—————————–+

#对于联合索引(a,b),下述语句可以直接使用该索引,无需二次排序

select … from table where a=xxx order by b;

#然后对于联合索引(a,b,c)来首,下列语句同样可以直接通过索引得到结果

select … from table where a=xxx order by b;

select … from table where a=xxx and b=xxx order by c;

#但是对于联合索引(a,b,c),下列语句不能通过索引直接得到结果,还需要自己执行一次filesort操作,因为索引(a,c)并未排序

select … from table where a=xxx order by c;

覆盖索引

InnoDB存储引擎支持覆盖索引(covering index,或称索引覆盖),即从辅助索引中就可以得到查询记录,而不需要查询聚集索引中的记录。

  • 使用覆盖索引的一个好处是:辅助索引不包含整行记录的所有信息,故其大小要远小于聚集索引,因此可以减少大量的IO操作
  • 注意:覆盖索引技术较早是在InnoDB Plugin中完成并实现,这意味着对于InnoDB版本小于1.0的,或者MySQL数据库版本为5.0以下的,InnoDB存储引擎不支持覆盖索引特性
  • 对于InnoDB存储引擎的辅助索引而言,由于其包含了主键信息,因此其叶子节点存放的数据为(primary key1,priamey key2,…,key1,key2,…)。例如

select age from s1 where id=123 and name = ‘wot’; #id字段有索引,但是name字段没有索引,该sql命中了索引,但未覆盖,需要去聚集索引中再查找详细信息。

最牛逼的情况是,索引字段覆盖了所有,那全程通过索引来加速查询以及获取结果就ok了

mysql> desc s1;

+——–+————-+——+—–+———+——-+

| Field | Type | Null | Key | Default | Extra |

+——–+————-+——+—–+———+——-+

| id    | int(11) | NO |    | NULL    |          |

| name | varchar(20) | YES | | NULL | |

| gender | char(6) | YES | | NULL | |

| email | varchar(50) | YES | | NULL | |

+——–+————-+——+—–+———+——-+

4 rows in set (0.21 sec)

mysql> explain select name from s1 where id=1000; #没有任何索引

+—-+————-+——-+————+——+—————+——+———+——+———+———-+————-+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+—-+————-+——-+————+——+—————+——+———+——+———+———-+————-+

| 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 2688336 | 10.00 | Using where |

+—-+————-+——-+————+——+—————+——+———+——+———+———-+————-+

1 row in set, 1 warning (0.00 sec)

mysql> create index idx_id on s1(id); #创建索引

Query OK, 0 rows affected (4.16 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> explain select name from s1 where id=1000; #命中辅助索引,但是未覆盖索引,还需要从聚集索引中查找name

+—-+————-+——-+————+——+—————+——–+———+——-+——+———-+——-+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+—-+————-+——-+————+——+—————+——–+———+——-+——+———-+——-+

| 1 | SIMPLE | s1 | NULL | ref | idx_id | idx_id | 4 | const | 1 | 100.00 | NULL |

+—-+————-+——-+————+——+—————+——–+———+——-+——+———-+——-+

1 row in set, 1 warning (0.08 sec)

mysql> explain select id from s1 where id=1000; #在辅助索引中就找到了全部信息,Using index代表覆盖索引

+—-+————-+——-+————+——+—————+——–+———+——-+——+———-+————-+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+—-+————-+——-+————+——+—————+——–+———+——-+——+———-+————-+

| 1 | SIMPLE | s1 | NULL | ref | idx_id | idx_id | 4 | const | 1 | 100.00 | Using index |

+—-+————-+——-+————+——+—————+——–+———+——-+——+———-+————-+

1 row in set, 1 warning (0.03 sec)

  • 覆盖索引的另外一个好处是对某些统计问题而言的。基于上一小结创建的表buy_log,查询计划如下

mysql> explain select count(*) from buy_log;

+—-+————-+———+——-+—————+——–+———+——+——+————-+

| id | select_type | table   | type  | possible_keys | key    | key_len | ref  | rows | Extra       |

+—-+————-+———+——-+—————+——–+———+——+——+————-+

|  1 | SIMPLE      | buy_log | index | NULL          | userid | 4       | NULL |    7 | Using index |

+—-+————-+———+——-+—————+——–+———+——+——+————-+

1 row in set (0.00 sec)

  • innodb存储引擎并不会选择通过查询聚集索引来进行统计。由于buy_log表有辅助索引,而辅助索引远小于聚集索引,选择辅助索引可以减少IO操作,故优化器的选择如上key为userid辅助索引
  • 对于(a,b)形式的联合索引,一般是不可以选择b中所谓的查询条件。但如果是统计操作,并且是覆盖索引,则优化器还是会选择使用该索引,如下

#联合索引userid_2(userid,buy_date),一般情况,我们按照buy_date是无法使用该索引的,但特殊情况下:查询语句是统计操作,且是覆盖索引,则按照buy_date当做查询条件时,也可以使用该联合索引

mysql> explain select count(*) from buy_log where buy_date >= ‘2011-01-01’ and buy_date < ‘2011-02-01’;

+—-+————-+———+——-+—————+———-+———+——+——+————————–+

| id | select_type | table   | type  | possible_keys | key      | key_len | ref  | rows | Extra                    |

+—-+————-+———+——-+—————+———-+———+——+——+————————–+

|  1 | SIMPLE      | buy_log | index | NULL          | userid_2 | 8       | NULL |    7 | Using where; Using index |

+—-+————-+———+——-+—————+———-+———+——+——+————————–+

1 row in set (0.00 sec)

延伸阅读:

二、什么是Memory引擎

Memory引擎是Mysql的内存引擎,在实现上,Memory存储引擎不同于Innodb这种组织索引结构(索引即是数据,即数据存放在主键索引上),而是将索引和数据分开存储。索引采用Hash的形式,存放主键id和指向数据的指针,而数据则按插入顺序存放。我们称这种数据组织方式为堆组织方式。

相关文章