前言

MySQL的索引是存储引擎用于快速查找记录的一种数据结构。工作原理是先从索引上找到对应值，再根据匹配的索引记录找到对应的数据行。索引的目的在于提高查询效率，可类比字典、书籍的目录等这种形式。

数据结构介绍

目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构。实际上MySQL的很多存储引擎如最常见的innoDB使用的是B+Tree，即每个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。

B-Tree

mysql 坐标检索 mysql索引查找原理_MySQL

B+Tree

mysql 坐标检索 mysql索引查找原理_mysql 坐标检索_02

为什么要使用这种数据结构

从理论上讲，二叉树查找速度和比较次数都是最小的，为什么不用二叉树呢？

首先索引本质上是一种数据结构，占用空间也很大，不可能全部存储在内存中，一般以索引文件形式存储在磁盘上，所以查找索引时就涉及到从磁盘中读取数据，就会有I/O消耗。相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数，对于树来说，IO次数就是树的高度，而“矮胖”就是b树的特征之一。

磁盘读取数据时，并不是只读取所需要的数据就行，而是会从读取数据的起始处往后读取一定长度的数据。这就涉及到局部性原理和磁盘的预读机制的概念：

局部性原理

当一个数据被用到时，其附近的数据也通常会马上被使用。

磁盘预读机制

因为磁盘存取速度比内存慢很多，根据局部性原理，为了提高效率，需要尽量减少磁盘I/O，磁盘每次都要从读取的数据位置顺序向后读取一页数据的整数倍放入内存中。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页的大小通常为4k）。

B-Tree每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个节点只需一次I/O。

B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存，h为树的高度），渐进复杂度为$O(h)=O(log_dN)$。一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小（通常不超过3）。

综上所述，用B-Tree作为索引结构效率是非常高的。

比如红黑树这种结构，h明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，所以红黑树的I/O渐进复杂度也为$O(h)$，效率明显比B-Tree差很多。

MySQL索引实现

MyISAM索引实现

mysql 坐标检索 mysql索引查找原理_主键_03

MyISAM索引的数据结构是B+Tree结构，在叶子节点处保存的是数据记录的地址

mysql 坐标检索 mysql索引查找原理_数据_04

辅助索引与主键索引除了主键索引具有唯一性外，其他地方没有任何区别，在叶子节点处仅保存数据记录的地址。搜索数据记录时先使用B+Tree的搜索算法找出数据地址，再根据数据地址找出数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

InnoDB索引实现

mysql 坐标检索 mysql索引查找原理_数据_05

InnoDB引擎也是使用B+Tree作为索引结构。

与MyISAM引擎的索引实现方式不同在于InnoDB的数据文件本身就是主键的索引文件。

由上图可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

mysql 坐标检索 mysql索引查找原理_mysql 坐标检索_06

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。

所以聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

索引分类

主键索引

主键索引就是聚集索引，每张表中有且仅有一个主键，可以由表中一个或多个字段组成。

  • 主键值必须唯一
  • 不能包含null值

创建主键索引语法：

alter table table_name add primary key(column_list);

唯一索引

唯一索引是约束条件的一种，就是不允许有重复的值，但是可以允许有null值。表的主键只能有一个，但是唯一索引可以有多个。

创建唯一索引的语法：

create unique index index_name on table_name (column_list);
-- 或
alter table table_name add unique (column_list);

前缀索引

对于BLOB，TEXT或者很长的VARCHAR类型的列，为它们的前n个字符（具体几个由创建索引时指定）建立索引，这样的索引就叫前缀索引。这样建立的索引更小，查询更快。

创建前缀索引的语法：

create index index_name on table_name(column_name(prefix_length));
-- 或
alter table table_name add key(column_name(prefix_length));

联合索引

联合索引又叫复合索引，是在表中两个或两个以上的列上创建的索引。利用索引的附加列，可以缩小检索的范围，更快搜索到数据。

创建联合索引的语法：

create index index_name on table_name (column_list);
-- 或
alter table table_name add index index_name (column_list);

假设表t有c1，c2两个字段，为它们创建联合索引：

create index idx_c1_c2 on t (c1, c2);

联合索引的使用必须满足最左前缀原则。一般把选择性高的列放在前面。一条查询语句可以只使用索引中的一部分，但是必须从最左侧开始。联合idx_c1_c2可以用到c1索引和c1，c2索引，但用不到c2索引。

以下查询可以用到idx_c1_c2的索引：

select * from t where c1 = 'xxx';
select * from t where c1 = 'xxx' and c2 = 'xxxx';
select * from t where c1 = 'xxx' and c2 in ('xxxx', 'xxx');
select * from t where c1 = ('xxx', 'xxxx') and c2 = 'xxx';
select * from t order by c1, c2;
select * from t where c1 = 'xxx' order by c2;

用不到idx_c1_c2索引的查询：

select * from t where c2 = 'xxx';
select * from t where c2 = 'xxx' order by c1;

另外，使用or关键字也是不能使用到联合索引的：

select * from t where c1 = 'xxx' or c2 = 'xxxx';

这种情况就要在c1，c2各自建两个单列索引。

哈希索引

哈希索引采用哈希算法，将键值换算成哈希值。哈希值只能进行等值查询，不能进行排序、模糊查找、范围查询等。检索时不需要像B+Tree那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，查询速度非常快。

innodb引擎支持哈希索引，称为自适应哈希索引，但是需要通过innodb_adaptive_hash_index参数来启用，默认是启用的。hash索引的创建由InnoDB存储引擎引擎自动优化创建。哈希索引只能用来搜索等值的查询，对于其他查找类型，如范围查找，是不能使用哈希索引的

mysql 坐标检索 mysql索引查找原理_MySQL_07

全文索引

全文索引是在基于文本的列（char、varchar或text列）上创建的，有助于提高对这些列中文本的查询速度和DML操作效率。

InnoDB引擎对全文索引的支持是MySQL5.6新引入的特性，之前只有MyISAM引擎支持全文索引。对于全文索引的内容可以使用MATCH()…AGAINST语法进行查询。MySQL5.6不支持中文全文索引，因为与英文不同，中文的文字是连着一起写的，中间没有MySQL能找到分词的地方。MySQL 5.7开始，MySQL内置了ngram全文检索插件，用来支持中文分词，并且对MyISAM和InnoDB引擎有效。

添加全文索引:

alter table table_name add fulltext index index_name(column_list) with parser ngram;

查询语法：

MATCH(col1,col2,…) AGAINST (expr[search_modifier])

索引使用策略和优化技巧

创建一个员工表用于示例，并导入数据，数据来源：https://dev.mysql.com/doc/employee/en/

表结构如下：

CREATE TABLE employees (
    emp_no      INT             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)
);

全值匹配

全值匹配是指索引匹配查询条件的所有列

为字段birth_date, first_name, last_name创建联合索引：

alter table employees add index idx_bfl(`birth_date`, `first_name`, `last_name`);

使用如下语句查询：

select * from employees where birth_date = '1953-09-02' and first_name = 'Georgi' and last_name = 'Facello';

执行计划如下，可见该查询使用到了索引：

mysql 坐标检索 mysql索引查找原理_数据_08

联合索引匹配最左列原则

对于复合索引来说，不总是匹配所有字段列，但是可以匹配索引中靠左的列

如果在(a,b,c)三个字段上建立联合索引，那么它能够加快 a | (a,b) | (a,b,c) 三组查询速度。

查询如下

select * from employees where birth_date = '1953-09-02' and first_name = 'Georgi';

执行计划：

mysql 坐标检索 mysql索引查找原理_主键_09

将where中的first_name 改为last_name:

select * from employees where birth_date = '1953-09-02' and last_name = 'Facello';

执行计划：

mysql 坐标检索 mysql索引查找原理_mysql 坐标检索_10

可见该查询只用到了复合索引中的一个列。

联合索引优化技巧：

  • 建联合索引的时候，区分度最高的字段在最左边。

计算一个列的区分度可以使用

select count(distinct(column_name))/count(1) from table;

计算出来的结果约接近1区分度越好。

  • 如果建立了(a,b)联合索引，就不必再单独建立 a 索引。同理，如果建立了(a,b,c)联合索引，就不必再单独建立 a、(a,b) 索引。
  • 存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如 where a>? and b=?，那么即使 a 的区分度更高，也必须把 b 放在索引的最前列。
  • 最左侧查询需求，并不是指 SQL 语句的 where 顺序要和联合索引一致，where的条件顺序MySQL会进行优化为使用适合的索引。
  • 范围条件有：<、<=、>、>=、between等。范围列可以用到索引（联合索引必须是最左前缀），但是范围列后面的列无法用到索引，索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。

使用覆盖索引

MySQL可以使用索引来直接获取列的数据，这样就不需要再去读取数据行了。如果一个索引包含（或者说覆盖）所有需要查询的数据，就称为“覆盖索引”。

执行如下查询：

select birth_date, first_name, last_name from employees where birth_date = '1953-09-02' and first_name = 'Georgi';

此时查询只获取birth_date, first_name, last_name列，而索引idx_bfl已经包含了这三列字段的数据，所以MySQL无需再通过索引idx_bfl查询到的data域中的主键值去主键索引文件中查询数据记录。

该查询的执行计划如下：

mysql 坐标检索 mysql索引查找原理_数据_11

其中Extra为Using index，说明使用了覆盖索引。

避免强制类型转换

强制类型转换的查询会导致全表扫描。

如果 salary字段是 varchar 类型，则下面的 SQL 不能命中索引。

select * from salaries where salary = 62102;

mysql 坐标检索 mysql索引查找原理_数据_12

可以优化为

select * from salaries where salary = '62102';

mysql 坐标检索 mysql索引查找原理_主键_13

InnoDB的主键选择与插入优化

在使用InnoDB存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键。

上文讨论过InnoDB的索引实现，InnoDB使用聚集索引，数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）。

如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。如下图所示：

mysql 坐标检索 mysql索引查找原理_mysql 坐标检索_14

这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。

如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置：

mysql 坐标检索 mysql索引查找原理_主键_15

此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

因此，只要可以，请尽量在InnoDB上采用自增字段做主键。

问题

只要是查询语句需要，就建上索引？

不是，因为索引虽然加快了查询速度，但索引也是有代价的。索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担。另外，MySQL 在运行时也要消耗资源维护索引，因此索引并不是越多越好。一般两种情况下不建议建索引。第一种情况是表记录比较少，例如一两千条甚至只有几百条记录的表，没必要建索引，另一种是数据的区分度比较低，可以使用 count(distinct(列名))/count(*) 来计算区分度。

假如建立联合索引(a,b,c)，下列语句是否可以使用索引，如果可以，使用了那几列？

where a= 3

答：是，使用了 a 列。

where a= 3 and b = 5

答：是，使用了 a，b 列。

where a = 3 and c = 4 and b = 5

答：是，使用了 a，b，c 列。

where b= 3

答：否。

where a= 3 and c = 4

答：是，使用了 a 列。

where a = 3 and b > 10 and c = 7

答：是，使用了 a，b 列。

where a = 3 and b like 'xx%' and c = 7

答：是，使用了 a，b 列。

答疑

关于执行计划的相关列含义

  • Extra列

Extra列是不适合在其他列显示的额外信息。

常用值如下：

Using index：表示使用覆盖索引

Using where：需要回表查询，使用where来过滤条件

Using index condition：查找使用了索引，但是需要回表查询数据

  • filtered：显示的是针对表里符合where子句或联接条件的记录数的百分比
  • type：访问类型

ALL：全表扫描

index：也是全表扫描，但是按照索引的次序进行

range：范围扫描

ref：索引查找

  • possible_keys：查询可以使用哪些索引
  • key：MySQL决定用哪个索引来优化查询
  • key_len：索引使用的字节数。如果MySQL正在使用的只是索引里的某些列，那么就可以用这个值来算出具体是哪些列
  • rows：MySQL为了找到所需的行而要读取的行数

聚集（簇）索引的优势在哪？

看上去聚簇索引的效率明显要低于非聚簇索引，因为每次使用辅助索引检索都要经过两次B+树查找，那么聚簇索引的优势在哪？

  • 由于行数据和叶子节点存储在一起，这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。
  • 辅助索引使用主键作为"指针" 而不是使用行地址值作为指针的好处是，减少了当出现行移动或者数据页分裂时辅助索引的维护工作，使用主键值当作指针会让辅助索引占用更多的空间，换来的好处是InnoDB在移动行时无须更新辅助索引中的这个"指针"，使用聚簇索引可以保证不管这个主键B+树的节点如何变化，辅助索引树都不受影响。
    原文链接：

使用in关键字时联合索引使用情况

本地测试的MySQL版本为5.7.19。

select * from employees where birth_date in ('1953-09-02', '1953-09-05')

mysql 坐标检索 mysql索引查找原理_主键_16

由执行计划得知用到了联合索引中的最左列。

select * from employees where birth_date in ('1953-09-02', '1953-09-05') and first_name = 'Georgi';

mysql 坐标检索 mysql索引查找原理_数据_17

通过key_len可以知道用到了联合索引中的前两个字段（当where条件只有birth_date条件时key_len为3，key_len是指MySQL在索引里使用的字节数）

select * from employees where birth_date = '1953-09-02' and first_name in ('Georgi', 'xxx') and last_name = 'Facello'

mysql 坐标检索 mysql索引查找原理_数据_18

通过key_len可以知道用到了联合索引中的所有字段

使用范围查询时联合索引使用情况

select * from employees where birth_date = '1953-09-02' and first_name > 'Georgi' and last_name = 'Facello'

mysql 坐标检索 mysql索引查找原理_mysql 坐标检索_19

由执行计划得知，该查询使用到了联合索引的前两列。