四时宝库

程序员的知识宝库

2024年07月17日

MySQL单列索引和联合索引的效率对比

关注我「程序猿集锦」，获取更多分享。

  • 背景
  • 实验环境
    • 表结构
    • 初始化数据
  • 实验过程
    • 多对多
    • 多对一
    • 一对一
  • 总结

背景

大家在工作中应该遇到过这样的一个问题：在一个表中有两个字段经常用作查询条件，为了提升查询效率我们经常在对应的列上面创建索引，以提升查询的效率。那么来了，是在这两个字段上分别创建一个索引好，还是在这两个列上创建一个联合索引(也称为组合索引、多列索引)好？

接下来，就让我们一起验证一下这个问题的答案到底是怎么样的。在开始实验之前，我们要有一个前提：就是我们的查询条件是两个列经常一起用来查询。

实验环境

表结构

我们创建两个innodb引擎的表，两个表的结构一样，只是表名称不一样而已。其结构如续所示：

CREATE TABLE `tab_a` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `n_vs_n_1` varchar(64) NOT NULL COMMENT '多对多关系的列1',
  `n_vs_one_1` varchar(64) NOT NULL COMMENT '多对一关系的列1',
  `one_vs_one_1` varchar(64) NOT NULL COMMENT '一对一关系的列1',
  `n_vs_n_2` varchar(64) NOT NULL COMMENT '多对多关系的列2',
  `n_vs_one_2` varchar(64) NOT NULL COMMENT '多对一关系的列2',
  `one_vs_one_2` varchar(64) NOT NULL COMMENT '一对一关系的列2',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14382992 DEFAULT CHARSET=utf8mb4 COMMENT='索引性能测试表';

CREATE TABLE `tab_b` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `n_vs_n_1` varchar(64) NOT NULL COMMENT '多对多关系的列1',
  `n_vs_one_1` varchar(64) NOT NULL COMMENT '多对一关系的列1',
  `one_vs_one_1` varchar(64) NOT NULL COMMENT '一对一关系的列1',
  `n_vs_n_2` varchar(64) NOT NULL COMMENT '多对多关系的列2',
  `n_vs_one_2` varchar(64) NOT NULL COMMENT '多对一关系的列2',
  `one_vs_one_2` varchar(64) NOT NULL COMMENT '一对一关系的列2',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14382992 DEFAULT CHARSET=utf8mb4 COMMENT='索引性能测试表';

这里针对上面的表结构简单说明一下，我们设计这个测试表的时候，设计了6个列，其中两两一对。用来测试每对是创建单列索引好还是创建联合索引好。

因为表中的列与列之间存在如下三种关系，我们就针对这三种情况分别来进行测试验证。

  • 多对多：列n_vs_n_1和列n_vs_n_2的关系就是多对多的关系。
  • 多对一：列n_vs_one_1和列n_vs_one_2的关系就是多对一的关系。
  • 一对一：列one_vs_one_1和列one_vs_one_2的关系就是一对一的关系。

初始化数据

这两个表中初始化数据内容、行数也完全相同，都是983W的数据量。如下所示：

mysql> select count(1) from tab_a;
+----------+
| count(1) |
+----------+
|  9831852 |
+----------+
1 row in set (1.70 sec)

mysql> select count(1) from tab_b;
+----------+
| count(1) |
+----------+
|  9831852 |
+----------+
1 row in set (1.77 sec)

数据内容示例如下，具体的数据内容是我根据现有的一个业务表改造出来的。里面的数据有的列是业务数据，有的列是随机填充的字符串。

两张表在磁盘上面占用空间的情况如下所示，大小都是1GB的磁盘空间。因为数据行数和内容都一样，只是表名称不一样。

sh-3.2# du -sh *
4.0K	db.opt
 12K	tab_a.frm
1.0G	tab_a.ibd
 12K	tab_b.frm
1.0G	tab_b.ibd
sh-3.2#

实验过程

多对多

我们首先针对多对多关系的两列来进行测试。

在表tab_a中，我们对列n_vs_n_1和列n_vs_n_2两列分别创建一个索引，而在另外一个表tab_b中，我们对列n_vs_n_1和列n_vs_n_2创建一个联合索引。然后我们去根据这两个多对多关系的列去查询两个表中的数据，看下他们的差效率哪个高哪个低。

在两个表中创建所以的语句如下所示：

mysql> alter table tab_a add index idx_tab_a_n_vs_n_1(n_vs_n_1) using btree;
Query OK, 0 rows affected (26.67 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table tab_a add index idx_tab_a_n_vs_n_2(n_vs_n_2) using btree;
Query OK, 0 rows affected (19.87 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table tab_b add index idx_tab_b_n_vs_n_1_and_n_vs_n_2(n_vs_n_1,n_vs_n_2) using btree;
Query OK, 0 rows affected (35.37 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

两个表的索引在创建完成之后，它们的表结构如下：

mysql> show create table tab_a\G
*************************** 1. row ***************************
       Table: tab_a
Create Table: CREATE TABLE `tab_a` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `n_vs_n_1` varchar(64) NOT NULL COMMENT '多对多关系的列1',
  `n_vs_one_1` varchar(64) NOT NULL COMMENT '多对一关系的列1',
  `one_vs_one_1` varchar(64) NOT NULL COMMENT '一对一关系的列1',
  `n_vs_n_2` varchar(64) NOT NULL COMMENT '多对多关系的列2',
  `n_vs_one_2` varchar(64) NOT NULL COMMENT '多对一关系的列2',
  `one_vs_one_2` varchar(64) NOT NULL COMMENT '一对一关系的列2',
  PRIMARY KEY (`id`),
  KEY `idx_tab_a_n_vs_n_1` (`n_vs_n_1`) USING BTREE,
  KEY `idx_tab_a_n_vs_n_2` (`n_vs_n_2`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=14382992 DEFAULT CHARSET=utf8mb4 COMMENT='索引性能测试表'
1 row in set (0.00 sec)

mysql> show create table tab_b\G
*************************** 1. row ***************************
       Table: tab_b
Create Table: CREATE TABLE `tab_b` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `n_vs_n_1` varchar(64) NOT NULL COMMENT '多对多关系的列1',
  `n_vs_one_1` varchar(64) NOT NULL COMMENT '多对一关系的列1',
  `one_vs_one_1` varchar(64) NOT NULL COMMENT '一对一关系的列1',
  `n_vs_n_2` varchar(64) NOT NULL COMMENT '多对多关系的列2',
  `n_vs_one_2` varchar(64) NOT NULL COMMENT '多对一关系的列2',
  `one_vs_one_2` varchar(64) NOT NULL COMMENT '一对一关系的列2',
  PRIMARY KEY (`id`),
  KEY `idx_tab_b_n_vs_n_1_and_n_vs_n_2` (`n_vs_n_1`,`n_vs_n_2`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=14382992 DEFAULT CHARSET=utf8mb4 COMMENT='索引性能测试表'
1 row in set (0.00 sec)

mysql>

这里需要注意一点，在表tab_b创建联合索引的时候，选择引导列的时候，需要选择数据离散度(区分度)高的列作为引导列。我们来看一下表tab_b中的两个列的数据的离散度，根据下面的统计结果可以看出列n_vs_n_1的数据区分度高于列n_vs_n_2的区分度。所以，我们在表tab_b中创建联合索引的时候，把数据区分度高的列放在第一列的位置上。

mysql> select count(1) as n_vs_n_1_distinct_number from (
    -> select n_vs_n_1 from tab_b group by n_vs_n_1
    -> ) as x;
+--------------------------+
| n_vs_n_1_distinct_number |
+--------------------------+
|                      689 |
+--------------------------+
1 row in set (5.02 sec)

mysql> select count(1) as n_vs_n_2_distinct_number from (
    -> select n_vs_n_2 from tab_b group by n_vs_n_2
    -> ) as y;
+--------------------------+
| n_vs_n_2_distinct_number |
+--------------------------+
|                       33 |
+--------------------------+
1 row in set (4.42 sec)

mysql>

引导列：排在联合索引中第一个位置的列称之为引导列，一般选择数据区分度高、离散度高的列作为引导列。

在两个表都创建完成索引之后，现在我们看下两个表在磁盘上上占用的空间大小。

发现创建组合索引的表tab_b占用的磁盘空间比tab_a两个单列索引占用的表空间要小一些。这是因为表tab_b上只要一个索引树，而表tab_a上面有两个索引树。所以表tab_b所占用的空间相对要小一些。这也是联合索引的一个小小的优势，就是在组合索引和单列索引性能一致，并且都能满足业务需求的时候，可以优先考虑组合索引，因为这样可以节省一些空间。

sh-3.2# du -sh *
4.0K	db.opt
 12K	tab_a.frm
1.4G	tab_a.ibd
 12K	tab_b.frm
1.3G	tab_b.ibd
sh-3.2#

下面我们分别执行下面的两个SQL语句，这两个SQL语句分别查询两个表，但是查询条件都是相同的，条件都是通过两列多对多关系的列来查询两个表中的数据：

select * from tab_a where n_vs_n_1='A0032000' and n_vs_n_2='2170';
select * from tab_b where n_vs_n_1='A0032000' and n_vs_n_2='2170';

上述两个SQL的执行计划如下所示：

mysql> explain select * from tab_a where n_vs_n_1='A0032000' and n_vs_n_2='2170'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tab_a
   partitions: NULL
         type: index_merge
possible_keys: idx_tab_a_n_vs_n_1,idx_tab_a_n_vs_n_2
          key: idx_tab_a_n_vs_n_1,idx_tab_a_n_vs_n_2
      key_len: 258,258
          ref: NULL
         rows: 10444
     filtered: 100.00
        Extra: Using intersect(idx_tab_a_n_vs_n_1,idx_tab_a_n_vs_n_2); Using where
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from tab_b where n_vs_n_1='A0032000' and n_vs_n_2='2170'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tab_b
   partitions: NULL
         type: ref
possible_keys: idx_tab_b_n_vs_n_1_and_n_vs_n_2
          key: idx_tab_b_n_vs_n_1_and_n_vs_n_2
      key_len: 516
          ref: const,const
         rows: 1974
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql>

执行的结果如下，从使用的时间上来看第二个SQL语句的效率要高于第一个SQL的效率，两者差23倍。不要小看这个，如果数据量大的情况下，这种差距可能会直线上升。我尝试重启MySQL数据库实例，让所有的缓存都清除，经过我多次试验发现每次都是查询表tab_a用的时间要大于查询表tab_b用的时间。

mysql> pager less -S;
PAGER set to 'less -S'

mysql> select * from tab_a where n_vs_n_1='A0032000' and n_vs_n_2='2170';
1974 rows in set (0.23 sec)

mysql> select * from tab_b where n_vs_n_1='A0032000' and n_vs_n_2='2170';
1974 rows in set (0.01 sec)

mysql>

通过上面执行计划和执行结果我们知道，在多对多的列上，创建联合索引比创建单列索引的效率要高，并且联合索引锁占用的空间比两个单列索引占用的空间要小一些。

这是因为通过两个多对多的关系列查询的时候，在单列索引上，需要扫描两个索引树，根据一个索引的条件是不能定义到具体的数据行的，需要根据另外一个条件去扫描另外一个索引树。而联合索引在查询的时候，两个条件只需要扫描一个索引树即可。

**注意：**这里有一个前提条件，那就是在查询的时候，多对多的这两个列是一起作为查询条件来使用的。如果只要一个列作为经常使用的查询条件，那么就不存在上面我们讨论的联合索引的问题了。直接在那个经常使用的一个列上面创建一个单列索引就可以了。

多对一

针对多对一的两个列n_vs_one_1和n_vs_one_2我们创建的索引如下所示：

mysql> alter table tab_a add index idx_tab_a_n_vs_one_1(n_vs_one_1) using btree;
Query OK, 0 rows affected (16.33 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table tab_a add index idx_tab_a_n_vs_one_2(n_vs_one_2) using btree;
Query OK, 0 rows affected (26.90 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table tab_b add index idx_tab_b_n_vs_one_1_and_n_vs_one_2(n_vs_one_1,n_vs_one_2) using btree;
Query OK, 0 rows affected (16.86 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

注意：这里在创建联合索引的时候，在选择引导列的时候，我们选择了多对一中的一那一列作为引导列，这才符合在创建联合索引的优化规则。也就是n_vs_one_1这一列，它的数据离散度如下所示，和表中的记录数是一样的，它的离散度是100%=去重后的数据行数/表中总的数据行数。

mysql> select count(1) as n_vs_one_1_distinct_number from (
    -> select n_vs_one_1 from tab_b group by n_vs_one_1
    -> ) as y;
+----------------------------+
| n_vs_one_1_distinct_number |
+----------------------------+
|                    9831852 |
+----------------------------+
1 row in set (7.03 sec)

mysql>

两个表的索引在创建完成之后，它们的表结构如下：

mysql> show create table tab_a\G
*************************** 1. row ***************************
       Table: tab_a
Create Table: CREATE TABLE `tab_a` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `n_vs_n_1` varchar(64) NOT NULL COMMENT '多对多关系的列1',
  `n_vs_one_1` varchar(64) NOT NULL COMMENT '多对一关系的列1',
  `one_vs_one_1` varchar(64) NOT NULL COMMENT '一对一关系的列1',
  `n_vs_n_2` varchar(64) NOT NULL COMMENT '多对多关系的列2',
  `n_vs_one_2` varchar(64) NOT NULL COMMENT '多对一关系的列2',
  `one_vs_one_2` varchar(64) NOT NULL COMMENT '一对一关系的列2',
  PRIMARY KEY (`id`),
  KEY `idx_tab_a_n_vs_n_1` (`n_vs_n_1`) USING BTREE,
  KEY `idx_tab_a_n_vs_n_2` (`n_vs_n_2`) USING BTREE,
  KEY `idx_tab_a_n_vs_one_1` (`n_vs_one_1`) USING BTREE,
  KEY `idx_tab_a_n_vs_one_2` (`n_vs_one_2`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=14382992 DEFAULT CHARSET=utf8mb4 COMMENT='索引性能测试表'
1 row in set (0.00 sec)

mysql> show create table tab_b\G
*************************** 1. row ***************************
       Table: tab_b
Create Table: CREATE TABLE `tab_b` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `n_vs_n_1` varchar(64) NOT NULL COMMENT '多对多关系的列1',
  `n_vs_one_1` varchar(64) NOT NULL COMMENT '多对一关系的列1',
  `one_vs_one_1` varchar(64) NOT NULL COMMENT '一对一关系的列1',
  `n_vs_n_2` varchar(64) NOT NULL COMMENT '多对多关系的列2',
  `n_vs_one_2` varchar(64) NOT NULL COMMENT '多对一关系的列2',
  `one_vs_one_2` varchar(64) NOT NULL COMMENT '一对一关系的列2',
  PRIMARY KEY (`id`),
  KEY `idx_tab_b_n_vs_n_1_and_n_vs_n_2` (`n_vs_n_1`,`n_vs_n_2`) USING BTREE,
  KEY `idx_tab_b_n_vs_one_1_and_n_vs_one_2` (`n_vs_one_1`,`n_vs_one_2`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=14382992 DEFAULT CHARSET=utf8mb4 COMMENT='索引性能测试表'
1 row in set (0.00 sec)

mysql>

下面我们分别执行下面的两个SQL语句：

select * from tab_a where n_vs_one_1='14482987' and n_vs_one_2='A0001800';
select * from tab_b where n_vs_one_1='14482987' and n_vs_one_2='A0001800';

两个SQL的执行计划如下所示：

mysql> explain select * from tab_a where n_vs_one_1='14482987' and n_vs_one_2='A0001800'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tab_a
   partitions: NULL
         type: ref
possible_keys: idx_tab_a_n_vs_one_1,idx_tab_a_n_vs_one_2
          key: idx_tab_a_n_vs_one_1 /*这里执行计划选择了idx_tab_a_n_vs_one_1索引列，而没有选择idx_tab_a_n_vs_one_2，是因为前面的索引是多对一中一，可以直接定位到具体的唯一一行。*/
      key_len: 258
          ref: const
         rows: 1
     filtered: 5.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from tab_b where n_vs_one_1='14482987' and n_vs_one_2='A0001800'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tab_b
   partitions: NULL
         type: ref
possible_keys: idx_tab_b_n_vs_one_1_and_n_vs_one_2
          key: idx_tab_b_n_vs_one_1_and_n_vs_one_2
      key_len: 516
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql>

执行结果如下所示，从下面的SQL执行的时间可以看出两个SQL基本没有区别。

mysql> pager less -S;
PAGER set to 'less -S'
mysql> select * from tab_a where n_vs_one_1='14482987' and n_vs_one_2='A0001800';
1 row in set (0.00 sec)

mysql> select * from tab_b where n_vs_one_1='14482987' and n_vs_one_2='A0001800';
1 row in set (0.00 sec)

mysql>

综上，我们可以知道在两个列的关系是多对一的情况下，创建联合索引或者组合索引的区别不大。因为MySQL在选择索引的时候，会选项那个多对一中的一这个列对应的索引，因为通过这个索引可以直接定位到唯一的一行数据。

换言之，多对一的两个列，没有必要创建联合索引，直接在数据唯一的那个列上创建一个单列索引即可，因为查询的时候，通过这个数据唯一性的列就可以直接定位到查询的数据行，没有必要在多对一中的两个列中的多对应的列上面创建索引，基本用不上那个索引。

注意：这里有一个前提条件，就是查询的时候是使用了多对一中的那个一所对应的列，不能是只用多对一中的多所对应的列来查询。一这个列必须要有，多这个列，可以有也可以没有。

一对一

现在我们来针对一对一的两个列来做一下测试。

分别在两个表上，创建索引如下：

mysql> alter table tab_a add index idx_tab_a_one_vs_one_1(one_vs_one_1) using btree;
Query OK, 0 rows affected (19.42 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table tab_a add index idx_tab_a_one_vs_one_2(one_vs_one_2) using btree;
Query OK, 0 rows affected (17.74 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table tab_b add index idx_tab_b_one_vs_one_1_and_one_vs_one_2(one_vs_one_1,one_vs_one_2) using btree;
Query OK, 0 rows affected (22.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

因为两个列是一对一的关系，所以在数据的区分度和离散度上，两个列的数据都是一样的。所以在表tab_b上面创建联合索引的时候，就不用区分哪个列放在前面作为引导列了。

在创建完索引之后，两个表的结构如下所示：

mysql> show create table tab_a\G
*************************** 1. row ***************************
       Table: tab_a
Create Table: CREATE TABLE `tab_a` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `n_vs_n_1` varchar(64) NOT NULL COMMENT '多对多关系的列1',
  `n_vs_one_1` varchar(64) NOT NULL COMMENT '多对一关系的列1',
  `one_vs_one_1` varchar(64) NOT NULL COMMENT '一对一关系的列1',
  `n_vs_n_2` varchar(64) NOT NULL COMMENT '多对多关系的列2',
  `n_vs_one_2` varchar(64) NOT NULL COMMENT '多对一关系的列2',
  `one_vs_one_2` varchar(64) NOT NULL COMMENT '一对一关系的列2',
  PRIMARY KEY (`id`),
  KEY `idx_tab_a_n_vs_n_1` (`n_vs_n_1`) USING BTREE,
  KEY `idx_tab_a_n_vs_n_2` (`n_vs_n_2`) USING BTREE,
  KEY `idx_tab_a_n_vs_one_1` (`n_vs_one_1`) USING BTREE,
  KEY `idx_tab_a_n_vs_one_2` (`n_vs_one_2`) USING BTREE,
  KEY `idx_tab_a_one_vs_one_1` (`one_vs_one_1`) USING BTREE,
  KEY `idx_tab_a_one_vs_one_2` (`one_vs_one_2`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=14382992 DEFAULT CHARSET=utf8mb4 COMMENT='索引性能测试表'
1 row in set (0.00 sec)

mysql> show create table tab_b\G
*************************** 1. row ***************************
       Table: tab_b
Create Table: CREATE TABLE `tab_b` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `n_vs_n_1` varchar(64) NOT NULL COMMENT '多对多关系的列1',
  `n_vs_one_1` varchar(64) NOT NULL COMMENT '多对一关系的列1',
  `one_vs_one_1` varchar(64) NOT NULL COMMENT '一对一关系的列1',
  `n_vs_n_2` varchar(64) NOT NULL COMMENT '多对多关系的列2',
  `n_vs_one_2` varchar(64) NOT NULL COMMENT '多对一关系的列2',
  `one_vs_one_2` varchar(64) NOT NULL COMMENT '一对一关系的列2',
  PRIMARY KEY (`id`),
  KEY `idx_tab_b_n_vs_n_1_and_n_vs_n_2` (`n_vs_n_1`,`n_vs_n_2`) USING BTREE,
  KEY `idx_tab_b_n_vs_one_1_and_n_vs_one_2` (`n_vs_one_1`,`n_vs_one_2`) USING BTREE,
  KEY `idx_tab_b_one_vs_one_1_and_one_vs_one_2` (`one_vs_one_1`,`one_vs_one_2`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=14382992 DEFAULT CHARSET=utf8mb4 COMMENT='索引性能测试表'
1 row in set (0.00 sec)

mysql>

对两个表的查询语句如下，通过两个一对一的列进行:

select * from tab_a where one_vs_one_1='1a3b144a-e54' and one_vs_one_2='1a3b144b';
select * from tab_b where one_vs_one_1='1a3b144a-e54' and one_vs_one_2='1a3b144b';

上述两个SQL语句的执行计划如下所示：

mysql> explain select * from tab_a where one_vs_one_1='1a3b144a-e54' and one_vs_one_2='1a3b144b'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tab_a
   partitions: NULL
         type: ref
possible_keys: idx_tab_a_one_vs_one_1,idx_tab_a_one_vs_one_2
          key: idx_tab_a_one_vs_one_1
      key_len: 258
          ref: const
         rows: 1
     filtered: 5.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from tab_b where one_vs_one_1='1a3b144a-e54' and one_vs_one_2='1a3b144b'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tab_b
   partitions: NULL
         type: ref
possible_keys: idx_tab_b_one_vs_one_1_and_one_vs_one_2
          key: idx_tab_b_one_vs_one_1_and_one_vs_one_2
      key_len: 516
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql>

执行结果如下，他们的执行时间基本没有什么差别。但是从上面的执行计划上看，使用单列索引是好于使用组合索引的。因为单列索引扫描的索引页要少，因为数据具有唯一性，所以只要扫描一个单列的索引树就可以定位到数据行了。从执行计划中的key_len属性值可以看出。

mysql> pager less -S;
PAGER set to 'less -S'
mysql> select * from tab_a where one_vs_one_1='1a3b144a-e54' and one_vs_one_2='1a3b144b';
1 row in set (0.00 sec)

mysql> select * from tab_b where one_vs_one_1='1a3b144a-e54' and one_vs_one_2='1a3b144b';
1 row in set (0.00 sec)

mysql>

在理解了多对一的两个列上，没有必要在多对应的两个列上创建索引之后，那么对于一对一的两个列上，也是同样的道理。只需要在其中一个一所对应的列上创建索引就可以了。没有表在两个数据都具有唯一性的列上分别创建两个单列索引。

但是从扫描的索引树的大小上来看，扫描一个单列索引树需要扫描的page页，要小于扫描一个联合索引树的page页。所以，此时建议使用两个单列索引，这样不仅满足两个列都作为查询条件的时候可以使用到索引，并且可以满足只使用两个唯一列的任何一列作为查询条件的是也可以使用到索引。

总结

结合上面所有的实验，我们可以得出这样的结论，当我们将从使用两个列一起作为查询条件来查询表中的数据的时候。

  • 如果两个列中的数据内容是多对多的关系，那么建议在这两个列上使用联合索引，而不是使用两个单列索引。因为数据是多对多的关系，通过一个列，不能定位到具体的数据行，还需要根据另外一个列的条件才可以定位到数据行，所以需要扫描两个单列索引，此时不如扫描一个联合索引树速度了，因为多扫描的另一个索引树。并且联合索引的磁盘占用空间，比两个单列索引的磁盘占用空间要小。
  • 当两个列中的数据内容是多对一的关系，建议使用两个单列索引，而不是一个组合索引。因为有一个列的数据具有唯一性，通过这个唯一性的列就可以直接定位到数据行，此时只要一个数据具有唯一性的列上面的一个单列索引即可，没有必要去扫描一个联合索引的索引树。因为那样会多扫描很多索引的page页面。一个单列索引树占用的磁盘空间，比一个联合索引树占用的磁盘空间要小。
  • 当两个列中的数据内容是一对一的关系，也建议使用两个单列索引，而不是一个组合索引。因为两个列的数据都具有唯一性，所以，通过任何一个列作为查询条件都可以直接定位到数据行，只需要使用一个单列索引就可以定位到数据，此时扫描一个单列索引树所扫描的page页要小于扫描一个联合索引树的page页，效率要高一些。另外，两个单列索引还有另外一个好处就是，可以只用两个列中的任何一列作为查询条件去筛选数据，而联合索引则没有那么灵活，它必须要带上引导列才可以使用到对应的联合索引树。

标签：mysql创建联合索引 

作者:sishi88 , 分类:技术教程 , 浏览:17 , 评论:0

发表评论:

控制面板
您好，欢迎到访网站！
  查看权限
网站分类
最新留言
    友情链接