索引 | 枫秀学习日志

概述

索引基础
索引主要类型
索引优化
InnoDB和MyISAM数据分布对比

1. 索引基础

索引，又叫key（键）
在mysql中，存储引擎先在索引中找到检索的内容，然后根据索引结果找到对应的数据行
索引可以包含一个或多个列的值，如果索引包含多个列，那么列的顺序十分重要，因为mysql只能高效的使用索引的最左前缀列，最左前缀列就是KEY(id, name, sex)，id在id、name、sex里面是写在左边的，这就叫最左前缀

2. 索引主要类型

索引是存储引擎用于快速找到记录的一种数据结构,这句话也侧面说明了mysql 索引是在存储引擎层而不是服务器层实现的。索引是对查询性能优化最有效的手段了，索引能够轻松将查询性能提升几个数量级。索引我们一般都是对某一列加索引。

存储引擎先在索引中找到对应值，然后根据匹配的索引记录上的rowid找到对应的数据行。比如运行如下查询语句：

1	SELECT first_name from actor where actor_id=5;

如果在actor_id列上建立有索引， MySQL将使用该索引找到actor_id 为5对应的行，也就是说，MySQL先在索引上按值查找，然后返回所有包含该值的数据行。

索引可以包含一个或则多个列的值，如果索引包含多个列(组合索引)，那么列的顺序也十分重要，因为MySQL只能高效的使用索引的最左前缀列。创建一个包含两个列的索引和创建两个包含包含一个列的索引是大不相同的。后面将会说道。

B树索引

一般情况下索引都是指B-Tree索引，它使用B-Tree数据结构来存储数据。实际上其实是基于B+Tree实现的，在每个叶子结点都包含一个指向下一个叶子结点的指针。

B-Tree意味着所有的值都是按照顺序存储的，比如对于name属性，就是按照从a-z的顺序存储的。使用B-Tree索引后，存储引擎不再需要进行全表扫描来获取需要的数据，而是从索引的根节点开始进行搜索，最终结果是要么找到对应的值，要么记录不存在。这样就能够加快访问数据的速度。

B-Tree对索引列是顺序组织存储的，所以很适合查找范围数据。（比如查找I-k开头的名字，这样效率会很高）

B-Tree索引适合的查询类型

全值匹配：和索引中的所有列进行匹配。
匹配最左前缀：对于一个索引包含多个列，只使用索引的第一列。
匹配列前缀：匹配某一列的值的开头部分。（比如匹配name字段的时候，只匹配以J开头的姓名）这里只用到了索引的第一列。
匹配范围值：匹配字段在某一个范围内的记录，这里只用到了索引的第一列。
精确匹配某一列并范围匹配另外一列：对于一个索引包含多个字段的情况，比如精确匹配第一列，第二列范围匹配。
只访问索引的查询：只访问索引行而不访问记录中其余字段的数据行。

上面的范围匹配，主要是因为索引的按顺序存储索引列，导致的范围匹配的高效性。

对于B-Tree的索引也有一些限制：

索引只能从最左列开始查找，如果查找使用的索引顺序和设置的索引顺序不同，则不能使用这个索引。比如索引(name,age，sex)如果查找使用的是age和sex，则这个索引不能使用。
不能跳过索引中的列，比如上面的，如果使用的是name和sex，跳过age，也是不能使用这个索引的。
如果查询中有某个列的范围查找，则其右边所有列都无法使用索引优化。还拿上面的例子，就是使用的查找条件name=dog and age >10 and sex=1，如果是这样查找的胡啊，只能使用索引的前俩列，而最后一列使用不了。

看到上面的限制应该就能明白关于索引中包含多个列的时候，索引列的顺序是很重要的。

Hash哈希索引

哈希索引是基于哈希表实现的，只有精确匹配索引所有列的查询才有效。对于每一行数据存储引擎都会为所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。

1）哈希索引只存储哈希值和行指针，并不存储具体的字段值，所以一定会存在读取行的过程。

2）哈希索引并不是按照索引值顺序存储的，所以就无法用于排序。

3）哈希索引只支持等值比较查询，不支持范围比较查询，这与哈希表的特性与有关。

4）哈希索引就存在哈希冲突的问题，对于哈希冲突的数据必须遍历链表中的所有行指针。

上面的这些限制，哈希索引只适合于特定的场合，但是一旦适合哈希索引，性能就会特别高。

使用hash索引时，一般情况下还要再查询条件中带上hash前的值，比如：

1	mysql>select * from words where crc=crc32(‘gnu’) and word=’gnu’;

这里crc字段就是word字段哈希之后的值，因为hash之后可能存在冲突，带上原本的值做上二次比较，就可以精确定位。

此外InnoDB存储引擎有一个特殊的功能叫做自适应hash索引。当InnoDB注意到某些索引值被使用的非常频繁时，就会在内存中基于B-Tree索引上在创建一个哈希索引，这样就让B-Tree索引也具有哈希索引的一些优点，比如快速的哈希查找。这是一个完全自动的、内部的行为，用户无法控制或者配置，不过如果没有必要，完全可以关闭该功能。

使用索引的优点

索引可以让服务器快速定位到表的指定位置。但是这不是唯一的作用，还有以下功能：

对于B-Tree索引，由于B-Tree是按照顺序存储数据的，所以用来做order by 操作或则是 group by操作的效率很高。
因为索引中存储了实际的列值，所以某些查询只需要索引就可以完成全部查询。

总结来说索引的有三个优点：
（1）索引大大减少服务器需要扫描的数据量；

（2）索引可以帮助服务器避免排序和临时表；

（3）索引可以将随机IO变为排序IO。

索引的缺点

（1）对于insert、update、delete操作，需要同步更新索引，导致速度变慢。

（2）索引会占用很大的存储。

3. 索引优化

先概括一下索引的策略：
1）单列索引
2）多列索引
3）前缀索引
4）聚簇索引
5）覆盖索引

单列索引

所谓单列索引是指：使用数据表字段中的某一列作为索引。

但是在查询的时，索引列不能是表达式的一部分，也不能是函数的参数。

比如对于下面的一个例子：

1	select actor_id from actor where actor_id+1=5;

对于这样的一个SQL，where语句后面是一个表达式，其实很明显是actor_id=4的条件，但是MySQL却无法解析，索引无法正却使用索引。

还有一种是函数参数：也是无法正常的使用索引的

1	select ... where TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col)<=10;

多列索引（组合索引）以及选择合适的索引顺序

注意这里要区分：为每个列创建独立的索引和为多个列创建一个索引的区别。

比如下面这种情况：

CREATE TABLE t{
c1 int,
c2 int,
c3 int,
key(c1),
key(c2),
key(c3)
}

这一种就是为表中的3个列都创建了索引。

但是多个列创建索引就是：创建了一个索引，包含customer_id,和staff_id

1	alter table payment add KEY(customer_id, staff_id);

上面这个索引其实是包含了两个索引，一个是customer_id这个索引，还有一个是(customer_id，staff_id)。注意: staff_id并不能作为单独的索引使用，这里有一个最左值匹配问题，也就是只能先从最左边的customer_id开始匹配，如果从staff_id匹配，则和此索引不匹配，也就不能使用此索引，但是可以单独匹配customer_id这个索引，因为他是最左边的索引

对于多列索引，最重要的就是怎么选择索引列的顺序，其实这一点与实际的查询需求有关。主要是为了满足排序和分组。

先从数据结构层次来分析，我们知道索引是以B-Tree的形式存储的，在一个多列索引列中，索引的顺序意味着索引首先按照最左列进行排序，其次是第二列。所以对于一个多列索引，如果以第二列或则第三列直接作为索引，基本是没有用到索引。由于索引的有序性很好的满足了order by、group by和distinct等子句的查询需求。

从上面的分析我们就能认识到多列索引中列的顺序是多么重要。关于多列索引中有一点经验法则：

在不需要考虑排序和分组时，通常情况下将选择性最高的列放在索引最前列。(这时候索引只需要优化where查询条件，能够很快过滤出需要的行)

索引列的选择性定义：不重复的索引值和数据表的记录总数的比值。索引的选择性越高也就是查询效率越高。比如对于人员信息表，phone这一字段的选择性是很高的，几乎为1，但是对于sex性别这一字段的选择性是非常低的，因为只有两个选择男或则是女，几乎为0。

实际情况下也与数据的分布有很大关系。
以下面的查询为例：
1
SELECT * FROM item WHERE staff_id=2 AND customer_id=584;

这时候应该创建（staff_id, customer_id）的索引还是应该创建（customer_id，staff_id）的索引呢？这时候就应该确认一下那个字段的选择性更高，先查询一下staff_id和customer_id的总数，哪个小就将哪个放在前面。

前缀索引和索引的选择性

有时候需要索引很长的字符列，这会让索引变得大且慢。这种情况下可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。索引的选择性是指，不重复的索引值和数据表的记录总数的比值。索引的选择性越高则查询效率越高，因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的行。前缀索引是一种能使索引更小、更快的有效办法，但也有缺点：MySQL 无法使用前缀索引做 ORDER BY 和 GROUP BY，也无法使用前缀索引做覆盖扫描。

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。这里主要以InnoDB为例来说明聚簇索引。

InnoDB中聚族索引实际上是在同一个结构中保存额B-tree索引和数据行，非聚族索引就是索引和数据不放在一起。当表中有聚族索引时，它的数据行实际上是存放在索引的叶子节点中。聚簇的含义实际上就是数据行和相邻的B-Tree中键值紧凑的存储在一起。数据行只能存放在一个地方，所以聚簇索引只能有一个。

下面以一个示例图来说明：索引列是整数值，叶子页包含了行的全部数据，但是结点页只包含了索引列(下图中的整型值)。

在目前为止的MySQL版本中，InnoDB的聚簇索引还只支持使用主键来聚簇数据。如果没有定义主键，InnoDB会选择一个唯一的非空索引来替代。

聚簇的数据的优点：

可以把相关数据保存在一起。比如查询电子邮箱邮件为例，以用户ID为主键，通过用户ID聚簇数据，这样只需要从磁盘读取少量数据页就能获取某个用户全部邮件。
数据访问更快。聚簇索引将索引和数据保存在一个B-Tree中，因此从聚簇索引中获取数据通常比非聚簇索引查找的快。（当然存在查找列就是索引列的情况）
使用覆盖索引扫描的查询可以直接使用页结点中的主键。

利用查询和设计表时上面的优点能够极大的提升性能，但是也有一些缺点：

聚簇数据极大提升了IO密集型应用的性能，但是数据全部放在内存中，访问的顺序就不重要了，聚簇索引也就失去了优势。
插入速度严重依赖于插入顺序。按照主键的顺序插入是速度最快的方式。如果是随机的会造成很多次的夜分裂，这样导致插入的速度变慢。这个后面会举例子。
更新聚簇索引列的代价很高，需要调整树的结构。
在插入新行或主键被更新导致需要移动行的时候，可能面临页分裂的问题。
可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续。
二级索引可能占用了更多空间，因为二级索引的叶子节点包含了引用行的主键。
二级索引访问需要两次索引查找。

覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。

覆盖索引的好处：

使用索引扫描来做排序

MySQL有两种方式可以生成有序的结果：通过排序操作或者按照索引顺序扫描。如果explain出来的type值是index，则说明MySQL使用了索引扫描来做排序。

索引扫描本身很快，只需要从一条记录往下一条记录移动即可，但是如果索引列不能覆盖所有查询字段，那么每次扫描一条索引记录都要回表查询一次，这基本上就是随机IO，因此按索引顺序读取数据的速度通常比顺序的全表扫描要慢。

只有当索引的列顺序和Order By子句的顺序完全一致，并且所有列的排序方向都一致时，Mysql才能使用索引对结果排序。

尽可能设计同一索引即满足排序又可用于查找。

4. InnoDB和MyISAM数据分布对比

聚簇索引和非聚簇索引的数据分布有区别，以及对应的主键索引和二级索引的数据分布也有区别，使用下面这个表来看看InnoDB和MyISAM是如何存储的

create table layout_test{
	col1 int not null,
	col2 int not null,
	primary key(col1)
	key(col2)
}

MyISAM的数据分布很简单，按照数据插入的顺序进行存储。如下5-4所示，在行的旁边加了行号，（并不一定总是行号，这里是定长的所以是行号，如果是变长则可能使用另外的策略，这里就不仔细研究）

上面图5-5显示了索引的创建的结构，这里省略了页的分布。索引节点的每个叶子节点中国包含行号，也就是实际存储的位置，另外MyISAM的其他索引的结构和上图是一样的，都是在叶子节点存储的行号，也就是存储的位置的指针（定长可以直接根据行号定位）。

InnoDB的数据分布如下图，使用的是聚簇索引，索引和数据存储在一起。如下图

上面数据和索引存储在一起，这是和MyISAM第一点不同。还有一点和MyISAM不同的是，InnoDB的二级索引和聚簇索引很不相同。InnoDB二级索引的叶子节点存储的是主键值而不是像MyISAM那样存储的是行指针。这样在进行移动或者数据页分裂时索引的维护工作变少。使用主键来当二级索引的的指针会占用更多的空间。不过好处是，在移动时不用更新这个二级索引中的这个指针。具体结构如下图

下图描述了InnoDB和MyISAM如何存放表的抽象图，也可以看出是聚簇和非聚簇表的对比图

下面我们来看一下为什么往聚簇索引中插入数据，最好按照顺序比较好，也就是按照主键自增长的顺序比较好.如下图所示，再插入的时候，是一条接一条的写，也就是顺序插入，相对随机插入会快，另外页的分裂也相对较少，产生的数据碎片也比较少，就是一页就存储很少的数据。

从上面可以看出，因为主键的值是顺序插入的，所以InnoDB把每一条记录都存储子啊上一条记录的后面，当达到页的最大填充因子时(InnoDB的默认填充因子是页大小的15/16，留出部分空间用于以后修改)。下一条记录就会写入新的页中。一旦数据按照这种顺序方式加载，主键页都是近似于顺序的被填满，这也正是所期望的结果。

相对于顺序，随机主键值的缺点如下:

随机主键值写入的过程如下：