Mysql验证联合索引的最左原则

前言

如果你接触过数据库,应该听说过在数据库表的某些列上建立索引能够加快查找速度,如果研究更深入一点的人,可能还听说过联合索引,那么索引为什么能够加快查找速度呢?联合索引究竟又是什么呢?下面说说我的简单理解。

索引

试想一下,把1~10000这1万个数字打乱顺序存储在数组中,如果要找到5000这个数字在哪,那就得从数组第0个元素开始,依次遍历找到5000这个数字所在的位置,运气好了1次就能找到,运气不好需要查询1万个数,可是如果把这1万个数作为map的key,每个数存在数组中的位置作为value,存储在map结构中很快就能找到,通常情况下要比直接遍历快的多。

其实这里的map充当的是一个索引的作用,我们知道map存储数据时使用树形结构,会根据要查找的值和当前节点比较,来确定继续查找左分支还是右分支,而数据库中的索引充当的也是这样的作用,mysql中的索引是BTree结构(多路搜索树),就是利用建立索引的列中的所有值建立了一棵树,通过有序的树形查找一般要比全局搜索快多了吧!

联合索引

简单了解了一下索引的含义,那么什么是联合索引呢?其实mysql数据库中的索引不止可以建立在一个列上,它可以将一个索引同时建立在说多个列上,也就是我们所说的联合索引,联合索引的作用特别大,有时会超过单列索引,至于什么时候建立单列索引,什么时候建立联合索引同样是个很复杂的问题,在此不做描述。有兴趣的读者可以自行搜索一下。

最左原则

当你在多个列上建立一个索引时,怎样的查找才能利用索引加快速度呢?说到这我们先建立一个带有索引的表格,具体的分析一下什么叫做索引的最左原则。

1
2
3
4
5
6
7
8
9
CREATE TABLE IF NOT EXISTS `test_index`(
`id` int(4) NOT NULL AUTO_INCREMENT,
`a` int(4) NOT NULL DEFAULT '0',
`b` int(4) NOT NULL DEFAULT '0',
`c` int(4) NOT NULL DEFAULT '0',
`data` int(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `union_index` (`a`,`b`,`c`)
)ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=binary;

分析上述建表语句,创建了一个名为test_index 的数据库表格,然后在a、b、c三列上建立了联合索引,索引名字为union_index,而最左原则指的就是当你建立了这样一个索引的时候,等于建立了(a)、 (a,b)、 (a,b,c)三个索引,通过条件 (a), (a,b), (a,b,c) 这三种条件查询的时候都可以利用索引加快速度,所以在建立索引的时候要把最常用的条件列放到联合索引的最左边,接下来我们来验证一下,工具就是mysql自带的explain命令。

测试版本

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

验证过程

  1. 首先以列a作为条件查询数据,我们看到 type: ref 表示引用查找, key_len: 4 表示索引长度为4,也就说明利用了索引来进行查找

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    mysql> explain select data from test_index where a = 1\G
    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: test_index
    partitions: NULL
    type: ref
    possible_keys: union_index
    key: union_index
    key_len: 4
    ref: const
    rows: 70
    filtered: 100.00
    Extra: NULL
    1 row in set, 1 warning (0.01 sec)
  2. 然后以列b作为条件查询数据,可以看到type: ALL表示全表查找, key_len: NULL 表示没有索引,也就说明如果只使用b作为查询条件,不能利用索引来加快查找速度

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    mysql> explain select data from test_index where b = 1\G
    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: test_index
    partitions: NULL
    type: ALL
    possible_keys: NULL
    key: NULL
    key_len: NULL
    ref: NULL
    rows: 716173
    filtered: 10.00
    Extra: Using where
    1 row in set, 1 warning (0.00 sec)
  3. 接着以列c作为条件查询数据,可以看到type: ALL表示全表查找, key_len: NULL 表示没有索引,情况与用b作为条件一样,只使用c作为查询条件也不能利用索引来加快查找速度

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    mysql> explain select data from test_index where c = 1\G
    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: test_index
    partitions: NULL
    type: ALL
    possible_keys: NULL
    key: NULL
    key_len: NULL
    ref: NULL
    rows: 716173
    filtered: 10.00
    Extra: Using where
    1 row in set, 1 warning (0.00 sec)
  4. 现在来测一下使用a、b作为条件的情况,我们看到 type: ref 表示引用查找, key_len: 8 表示索引长度为8,也就是说我们利用上了a、b联合索引来进行查找

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    mysql> explain select data from test_index where a = 1 and b = 1\G
    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: test_index
    partitions: NULL
    type: ref
    possible_keys: union_index
    key: union_index
    key_len: 8
    ref: const,const
    rows: 1
    filtered: 100.00
    Extra: NULL
    1 row in set, 1 warning (0.00 sec)
  5. 紧接着来测一下使用a、c作为条件的情况,我们看到 type: ref 表示引用查找, key_len: 4 表示索引长度为4,这就奇怪了,按照最左原则来说,a、c上是不会建立索引的,为什么会有索引长度呢?其实与a、b上的索引一比较我们就能发现,a、c上的索引长度只有4,而且单独的c上是没有索引的,所以4字节长度的索引只能是a上的,也就是说这种情况我们只使用了a列上的索引来进行查找

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    mysql> explain select data from test_index where a = 1 and c = 1\G
    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: test_index
    partitions: NULL
    type: ref
    possible_keys: union_index
    key: union_index
    key_len: 4
    ref: const
    rows: 70
    filtered: 10.00
    Extra: Using index condition
    1 row in set, 1 warning (0.00 sec)
  6. 为了进一步验证上面的想法,这一次测一下使用b、c作为条件的情况,我们看到 type: ALL 表示全表查找, key_len: NULL 表示没有索引可以使用,按照最左原则来说,b列上没有索引,c列上也没有索引,同时b、c的上也不存在联合索引,所以使用b、c作为查询条件时无法利用联合索引

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    mysql> explain select data from test_index where b = 1 and c = 1\G
    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: test_index
    partitions: NULL
    type: ALL
    possible_keys: NULL
    key: NULL
    key_len: NULL
    ref: NULL
    rows: 716173
    filtered: 1.00
    Extra: Using where
    1 row in set, 1 warning (0.00 sec)
  7. 测试完两个条件的情况,接下来测试一下使用a、b、c作为条件的情况,我们看到 type: ref 表示引用查找, key_len: 12 表示索引长度为12,这完全符合联合索引的最左原则,同时使用3个条件查询可以利用联合索引

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    mysql> explain select data from test_index where a = 1 and b = 1 and c = 1\G
    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: test_index
    partitions: NULL
    type: ref
    possible_keys: union_index
    key: union_index
    key_len: 12
    ref: const,const,const
    rows: 1
    filtered: 100.00
    Extra: NULL
    1 row in set, 1 warning (0.00 sec)
  8. 测试进行到现在,我测试了所有的情况吗?不是的!还可以颠倒顺序啊,我原来一直以为联合索引是有顺序的,结果测试后才发现,利用索引的条件符合“交换律”,也就是下面这种情况也能利用a、b上的联合索引,索引长度为8

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    mysql> explain select data from test_index where b = 1 and a = 1\G
    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: test_index
    partitions: NULL
    type: ref
    possible_keys: union_index
    key: union_index
    key_len: 8
    ref: const,const
    rows: 1
    filtered: 100.00
    Extra: NULL
    1 row in set, 1 warning (0.00 sec)
  9. 再来试试这种情况,按照最左原则,c上没有建立索引,a上有索引,c、a没有建立联合索引,所以只能使用a上的索引进行查找,结果索引长度只有4,验证了我们的想法,联合查询条件使用索引时满足“交换律”

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    mysql> explain select data from test_index where c = 1 and a = 1\G
    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: test_index
    partitions: NULL
    type: ref
    possible_keys: union_index
    key: union_index
    key_len: 4
    ref: const
    rows: 70
    filtered: 10.00
    Extra: Using index condition
    1 row in set, 1 warning (0.00 sec)
  10. 接下来几种交换顺序的情况(c,b)、(a,c,b)、(c,b,a)等,大家可以自己进行验证,到此为止,mysql联合索引的最左原则也就验证结束了!

总结

  1. 联合索引的最左原则就是建立索引KEY union_index (a,b,c)时,等于建立了(a)、(a,b)、(a,b,c)三个索引,从形式上看就是索引向左侧聚集,所以叫做最左原则,因此最常用的条件应该放到联合索引的组左侧。

  2. 利用联合索引加速查询时,联合查询条件符合“交换律”,也就是where a = 1 and b = 1 等价于 where b = 1 and a = 1,这两种写法都能利用索引KEY union_index (a,b,c)

  3. 遇到这种不确定的问题还是需要实际测试一下,简单的调整一下索引顺序可能会极大的提升效率哦!

Albert Shi wechat
欢迎您扫一扫上面的微信公众号,订阅我的博客