前言

实习过程中经常碰到自己写的查询sql语句跑了好几秒甚至十几秒都没结果的情况，自己也仔细检查了查询的逻辑没发现问题，那么基本可以确定是索引的问题了。Mysql中的索引本质上就和字典当中的索引一样，如果不借助索引，在一本字典当中查找一个单词，就必须从第一页开始找，速度可想而知会非常的慢。然而建立了索引之后，就可以很快速的找到首字母开始出现的位置，大大减少了查询的时间。可见索引在数据库中扮演着一个十分重要的角色，深入了解索引的内部原理就显得很有必要了。

索引原理

要想深入地了解索引，那就必须先要了解索引的本质了。因为InnoDB引擎在Mysql中已经占据着主导地位，所以本篇都是基于InnoDB存储引擎之上的。不同于MyISAM，InnoDB最大的特点就是它的存储文件同样也是索引文件，这个索引被称为聚簇索引，表示数据行和索引紧凑地存储在一起。InnoDB索引的结构是一颗B+树，数据库表中的所有数据都是存放在叶子结点之上的，内部节点存储的是索引值。结构如下图所示：

image

</center>

图中我们可以看到，每个数据行都是通过聚簇索引查询或是数据行间的链接得到，可见每个InnoDB表都必须有这么一个聚簇索引，那么什么样的索引会作为聚簇索引呢？定义了主键了的话，那么主键就是聚簇索引，如果没有，InnoDB会选取一个唯一的非空索引来代替，如果还是没有，那么InnoDB会隐式地创建一个主键作为聚簇索引。

接下来看看InnoDB中的二级索引(聚簇索引之外的所有其他索引都被称为二级索引 )，二级索引存储结构中数据域存储的其实是主键值，也就是说当使用二级索引进行查询时，实际上是进行了两次查询，第一次根据二级索引获取到主键值，第二次根据查询到的主键值找到所需要的数据。结构如下图所示：

image

</center>

优化实践

测试使用的数据库为Mysql官方提供的事例数据库，下载地址：https://github.com/datacharmer/test_db。主要使用的是其中的employees表，表结构如下：

mysql> show create table employees\G;
*************************** 1. row ***************************
       Table: employees
Create Table: CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

最左前缀索引

首先创建一个组合索引idx_test：

mysql> create index idx_test on employees(birth_date,first_name,last_name);

然后我们根据idx_test索引的第一列进行查询：

mysql> explain select * from employees where birth_date='1953-11-7'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: ref
possible_keys: idx_test
          key: idx_test
      key_len: 3
          ref: const
         rows: 61
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.04 sec)

从key中可以看到，查询确实使用到了我们创建的组合索引，并且只是用了组合索引当中的第一列，key_len为3。接着我们用组合索引的第一列和第三列作为条件进行查询：

mysql> explain select * from employees where birth_date='1952-11-7' and last_nam
e='Sluis'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: ref
possible_keys: idx_test
          key: idx_test
      key_len: 3
          ref: const
         rows: 65
     filtered: 10.00
        Extra: Using index condition
1 row in set, 1 warning (0.04 sec)

可以看到数据库依旧是使用到了我们创建的索引，但是key_len=3，也就是只用到了组和索引的第一列，第三列并没有使用到，符合最左前缀思想。
当组合索引中第x列存在范围条件，那么第x列后面的列索引将不会使用到，看下面的例子：

mysql> explain select * from employees where birth_date>'1965-01-31' and first_n
ame='Steve' and last_name='Denji'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: range
possible_keys: idx_test
          key: idx_test
      key_len: 3
          ref: NULL
         rows: 49
     filtered: 1.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

key_len=3,只有第一列索引被使用到了，就是因为birth_date是比较操作。

覆盖索引

前面就已经说到InnoDB二级索引存储的数据其实是主键值，每次使用二级索引都会进行两次查询。其实有一种情况，数据库只需要查询二级索引就行了，那就是select查询的列在二级索引存储结构中都可以被查询到，那么数据库就不需要再回主键查询。

mysql> explain select * from employees where birth_date='1956-02-12' and first_n
ame='Berni' and last_name='Sluis'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: ref
possible_keys: idx_test
          key: idx_test
      key_len: 97
          ref: const,const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.04 sec)

mysql> explain select birth_date,first_name,last_name from employees where birth
_date='1956-02-12' and first_name='Berni' and last_name='Sluis'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: ref
possible_keys: idx_test
          key: idx_test
      key_len: 97
          ref: const,const,const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.02 sec)

可以看到，第一次使用select *条件进行查询，其中包括了二级索引存储结构中不存在的列，所以数据库需要回主键查询。而第二次，select查询的列都是在二级索引存储结构中存在的，数据库只需要一次查询便可得到结果，所以速度更快。

前缀索引

有时候需要索引很长的字符列，这会让索引变得大且慢，那么就可以索引该列的前一部分字符，这样就可以大大节约索引空间，从而提高索引效率，但是这样也会降低索引的选择性。选择性是指不重复的索引值和数据表记录总数的比值。范围会在0-1之间，选择性越高查询效率就会越高。比如性别列，选择性=2/count(*),当表记录越来越大时，选择性趋向于0，所以性别列的查询效率会很低。需要注意的是前缀索引也有缺点，那就是Mysql无法使用前缀索引做order by和group by操作，也无法使用前缀索引做覆盖扫描。

PS

查询容易，优化不易，且写且珍惜！

参考

1. MySQL索引背后的数据结构及算法原理
2. <高性能MySQL>