MySQL 索引
1、MySQL 中 InnoDB 存储引擎索引概述
在 InnoDB 存储引擎中支持以下几种常见的索引
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 存储如下图
<center>聚集索引 B+Tree 存储</center>
2、辅助索引（非聚集索引）
对于辅助索引（非聚集索引），叶子节点并不包含行记录的全部数据，叶子节点除了包含键值之外，每个叶子节点中的索引行还包含了一个书签（bookmark）,该书签用来告诉 InnoDB 存储引擎哪里可以找到与索引相对应的行数据
3、联合索引
联合索引是指对表上的多个列进行索引，联合索引的创建方法与单个索引创建方法一样，不同之处在于有多个索引列，从本质上来说，联合索引也是一棵 B+树,不同的是联合索引的键值的数量不是 1，而是大于等于 2
<center>多个键值的 B+树</center>
若是对表建立 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个字符进行索引，如果索引整个列，则为NULLPacked:关键字如何被压缩，如果没有被压缩，则为NULLNull:是否索引的列含有NULL值，如果索引整个列，则为NULLIndex_type:索引的类型，InnoDB存储引擎值支持B+树索引，所以这里都是BTREEComment:注释Index_comment：在创建索引时提供的注释
复制代码
2、Multi-Range Read 优化
Multi-Range Read 优化的目的就是为了减少磁盘的随机访问，并且将随机访问转化为顺序的数据访问，这对于 IO-bound 类型的 SQL 查询语句可带来性能的提升，Multi-Range Read 优化可使用于 range、ref、ef_ref 的查询
评论