MySQL索引

1、MySQL中InnoDB存储引擎索引概述

在InnoDB存储引擎中支持以下几种常见的索引

• B+树索引

• 全文索引

• 哈希索引

InnoDB存储引擎支持的索引是自适应的，InnoDB会根据表的使用情况自动为表生成哈希索引。B+树中的B不是代表二叉（binary),而是代表平衡（balance）,因为B+树是从最早的平衡二叉树演化而来，但是B+树不是二叉树，B+树索引并不能找到一个给定键值得具体行，B+树索引能找到的只是被查找数据行所在的页。然后数据库通过把页读入到内存中，再在内存中进行查找，最后找到要查找的数据

2、索引分类

按照索引数据结构分类 : B+树索引、全文索引、哈希索引（Hash索引）

按照索引物理存储分类：聚簇索引（主键索引）、辅助索引（非聚集索引）

按照索引字段特性分类：主键索引、唯一索引、普通索引、前缀索引

按照索引字段个数分类：单列索引、联合索引

1、聚集索引（主键索引）

InnoDB存储引擎表是索引组织表，即表中数据按照主键顺序存放，而聚集索引（clustered index)就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。同B+树结构一样，每个数据页都通过一个双向链表来进行链接。

由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引，在多数情况下，查询优化器倾向于采用聚集索引，因为聚集索引能够在B+树索引的叶子结点上直接找到数据，此外，由于定义了数据的逻辑结构，聚集索引能够特别快地访问针对范围值的查询，查询优化器能够快速发现某一段范围的数据页需要扫描。

聚集索引对于主键的排序查找和范围查找速度非常快，叶子结点的数据就是用户所要查询的数据

聚集索引一个表只能有一个，在创建聚集索引时，InnoDB通过主键创建聚集索引，如果没有定义主键，InnoDB会选择一个非空的唯一索引来建立聚集索引，如果没有这样的索引，InnoDB会隐式的定义一个主键来作为聚集索引

聚集索引存储记录是物理上连续存在，物理存储按照索引排序，而非聚集索引是逻辑上的连续，物理存储并不连续，物理存储不按照索引排序。

聚集索引B+Tree存储如下图

2、辅助索引（非聚集索引）

对于辅助索引（非聚集索引），叶子节点并不包含行记录的全部数据，叶子节点除了包含键值之外，每个叶子节点中的索引行还包含了一个书签（bookmark）,该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据

3、联合索引

联合索引是指对表上的多个列进行索引，联合索引的创建方法与单个索引创建方法一样，不同之处在于有多个索引列，从本质上来说，联合索引也是一棵B+树,不同的是联合索引的键值的数量不是1，而是大于等于2

若是对表建立index(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值不是排序的，因此对于b列的查询用不到索引（a,b)的索引。

联合索引的第二个好处就在于已经对第二个键值进行了排序处理，以查询用户的购物情况为例，按照时间进行排序，最后取出最近三次的购买记录，这时使用联合索引可以避免多一次的排序 操作，因为索引本身在叶子节点已经排序了

创建表buy_log

CREATE TABLE buy_log ( userid INT NOT NULL, buy_date date ) ENGINE = INNODB

插入数据

insert into buy_log VALUES(1,'2009-01-01');
insert into buy_log VALUES(2,'2009-01-01');

insert into buy_log VALUES(3,'2009-01-01');
insert into buy_log VALUES(1,'2009-02-01');

insert into buy_log VALUES(3,'2009-02-01');
insert into buy_log VALUES(1,'2009-03-01');
insert into buy_log VALUES(1,'2009-04-01');

建立索引

alter table buy_log add key(userid);

alter table buy_log add key(userid,buy_date);

当只是对userd来进行查询时

select * from buy_log where userid=2

执行计划为

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 | NULL  |
+----+-------------+---------+------+-----------------+--------+---------+-------+------+-------+

可以发现，possible_keys在这里有两个索引可以使用，但是最终执行器选择的是索引userid,因为该索引上的叶子结点包含单个键值，所以理论上一个页能存放的记录更多

当执行语句

mysql> select * from buy_log where userid=2 order by buy_date desc limit 3；

执行计划为

mysql> explain select * from buy_log where userid=2 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 |    1 | Using where |
+----+-------------+---------+------+-----------------+----------+---------+-------+------+-------------+
1 row in set (0.00 sec)

SQL使用的是userid_2索引，因为在这个索引中buy_date已经排好序了，根据该索引取出数据，无需再对buy_date做一次额外的排序操作，若强制使用userid索引，则执行计划为

mysql> explain select * from buy_log force index (userid)  where userid=2 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 | 4       | const |    1 | Using where; Using filesort |
+----+-------------+---------+------+---------------+--------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)

在Extra中可以看到使用了Using fileSort,即需要额外的一次排序操作才能完成排序，在SQL中需要对buy_date排序，因为索引userid中的buy_date是未排序的

联合索引（a,b)是根据列a、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 

但是对于下面语句，联合索引不能直接得到结果，其中还需要执行一次filesort 排序，因为索引（a,c）并未排序

select ... from table where a=xxx order by c

4、覆盖索引（索引覆盖）

InnoDB存储引擎支持覆盖索引（covering index,也称索引覆盖），即可以直接从覆盖索引中就可以查询到记录，而不需要查询聚集索引中的记录，使用覆盖索引好处是覆盖索引不包含整行记录的所有信息，故其大小要远小于聚集索引，可以大大减少IO操作

比如上个查询语句

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)

可以看到彭possible_keys 列为NULL，但是执行器选择的是userid索引，而Extra的Using index表示使用了索引覆盖操作

5、倒排索引

全文索引通常使用倒排索引来实现（inverted index）来实现，倒排索引同B+树索引一样，也是一种索引结构，它在辅助表里面存储了单词与单词自身在一个或多个文档中所在位置之间的映射

3、B+树索引管理

1、索引管理

索引的创建和删除可以通过两张方法，一种是 alter table,另一种是create/drop index。

通过alter table 创建索引的语法是:

alter table tbl_name add [index_name] [index_type] (index_col_name,...)
alter table tbl_name drop index_name

通过create /drop index来创建索引

create [unique] index index_name [index_type] on tbl_name (index_col_name,...)
drop index index_name on tbl_name

若是想要查看表中索引的信息。通过命令 show index

mysql> show index from user;
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY         |            1 | Host        | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| user  |          0 | PRIMARY         |            2 | User        | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | index_user_Host |            1 | Host        | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

通过show index from 可以看到表中建立了3个索引，下面对每个字段进行解析

Table:索引所在的表名
Non_unique：非唯一的索引
Key_name：索引的名字
Seq_in_index：索引中该列的位置
Column_name：索引列的名称
Collation：列以什么方式存储在索引中，可以是A或者NULL,B+树索引总是A。即排序的
Cardinality：该值表示索引中唯一值的数目的估计值，
Sub_part：是否是列的部分被索引，如果表示100，则表示对前100个字符进行索引，如果索引整个列，则为NULL
Packed:关键字如何被压缩，如果没有被压缩，则为NULL
Null:是否索引的列含有NULL值，如果索引整个列，则为NULL
Index_type:索引的类型，InnoDB存储引擎值支持B+树索引，所以这里都是BTREE
Comment:注释
Index_comment：在创建索引时提供的注释

2、Multi-Range Read 优化

Multi-Range Read优化的目的就是为了减少磁盘的随机访问，并且将随机访问转化为顺序的数据访问，这对于IO-bound类型的SQL查询语句可带来性能的提升，Multi-Range Read优化可使用于range、ref、ef_ref的查询