Mysql索引详解

Mysql索引

概念

索引是帮助Mysql高效获取数据的排好序的数据结构。
数据库中的索引就相当于书籍中的目录一样，当我们想找到书中的某个知识点，我们可以直接去目录中找而不是在书中每页的找。

分类

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。
创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

1.普通索引：

仅加速查询 最基本的索引，没有任何限制，是我们大多数情况下使用到的索引。

2.唯一索引：
它与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。

3.主键索引：
它是一种特殊的唯一索引，不允许有空值。

4.全文索引：
全文索引（FULLTEXT）仅可以适用于MyISAM引擎的数据表；作用于CHAR、VARCHAR、TEXT数据类型的列。

5.组合索引：
将几个列作为一条索引进行检索，遵循”最左前缀“原则。创建复合索引时应该将最常用（频率）作限制条件的列放在最左边，依次递减。

优缺点

优点：索引可以大大提高MySQL的检索速度。
缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

什么样的字段适合创建索引

1、表的主键、外键必须有索引；外键是唯一的，而且经常会用来查询
2、数据量超过300的表应该有索引；
3、经常与其他表进行连接的表，在连接字段上应该建立索引；经常连接查询，需要有索引
4、经常出现在Where子句中的字段，加快判断速度，特别是大表的字段，应该建立索引，建立索引，一般用在select ……where f1 and f2 ，我们在f1或者f2上建立索引是没用的。只有两个使用联合索引才能有用
5、经常用到排序的列上，因为索引已经排序。
6、经常用在范围内搜索的列上创建索引，因为索引已经排序了，其指定的范围是连续的

索引结构

Mysql索引主要有两种结构：B+Tree索引和Hash索引.

Hash索引

Hash索引底层是哈希表，哈希表是一种以key-value存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的，是散列的方式分布，因此当查找某一条记录的时候,速度非常快。但是，对于区间查询是无法直接通过索引查询的，就需要全表扫描，也不支持排序。 哈希索引不支持多列联合索引的最左匹配规则 如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题。所以，哈希索引只适用于等值查询的场景。

B+树索引

B+tree是mysql使用最频繁的一个索引数据结构，是Inodb和Myisam存储引擎模式的索引类型。相对Hash索引，B+树在查找单条记录的速度比不上Hash索引，但是因为更适合排序等操作，所以他更受用户的欢迎。毕竟不可能只对数据库进行单条记录的操作。

下面分析一下各种数据结构存储索引的特点：

二叉树存储索引
如果以Clo2列作为索引，要查询23，只需要三次I/O就可以查到数据，如果没有索引的话，需要7次
I/O才能查到。但是二叉树存储索引有一个缺点，如果索引列是连续递增的整形，比如以Col1主键作为索引，就会出现下面这种情况：

要查询23所在那一行的数据也需要7次I/O，这样也相当于全表扫描了。所以Mysql数据库不用二叉树存储索引。

红黑树存储索引

还是以Col1作为索引，一次插入主键，数据结构如下所示：

如果数据量特别大的话达到百万级别，红黑树的层次会很深，树的高度是2的N次幂=100w，
N即为层数，N约等于20。如果查询的数据在树的底层，那么I/O次数也是比较多的，性能也不是很好，所以Mysql也不用红黑树存储索引。

有没有什么好的办法可以让树的高度低些？怎么改造？

可以让节点横向排开，增加横向节点的数量，这样就可以降低树的高度了，B Tree就满足了这样的数据结构。

B-Tree 存储索引

B -Tree特点：

1. 叶节点具有相同的深度
2. 叶节点的指针为空
3. 节点中的数据key从左到右递增排列
   

B-Tree所有节点上都存储了索引和数据。

B+Tree 存储索引

B+Tree特点：

1. 非叶子节点不存储data，只存储key，可以增大度
2. 叶子节点不存储指针
3. 顺序访问指针，提高区间访问的性能
   

B+Tree所有索引数据都在叶子结点上，并且增加了顺序访问指针,每个叶子节点都有指向相邻叶子节点的指针。
这样做是为了提高区间查询效率，例如查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。

Mysql数据库为什么要用B+Tree而不用B-Tree?

Mysql数据库中，一个节点的大小为16k，可以通过下面的sql语句查询
show global status like ‘Innodb_page_size’;
B树的高度越低I/O的次数越少，B-Tree的非叶子节点存储的是key和data，而B+Tree只存储key，这样可以存储更多的索引值。一个节点存储的索引越多，相应的树的高度就越低了。
B+Tree比B-Tree多了一个顺序指针，这样在查询区间数据的时候会节省很多I/O。比如查询索引在15-30区间的数据的时候，B+Tree查到15的时候会顺着指针依次查下去，如果是B-Tree的话，查到15的时候，会再次返回从根节点开始往下查，这样I/O次数会多很多。

MyISAM索引实现(非聚集)

MYISAM ：磁盘上一张表对应三个文件

1. 表名.frm ：存储表结构
2. 表名.MYD ： 存储表数据
3. 表名.MYI : 存储表索引

MyISAM索引文件和数据文件是分离的。
存储索引的数据结构如下图所示：
MyISAM存储索引，data存储的是数据行的地址。

以主键作为索引：

辅助索引（非主键）

主键索引和辅助索引没有太多区别。

InonoDB索引实现(聚集)

InoDB ： 磁盘上一张表对应两个文件

1. 表名.frm ：存储表结构
2. 表名.idb ：存储索引和数据

表数据文件本身就是B+Tree组织的一个索引结构文件
聚集索引-叶节点包含了完整的数据记录，即整行数据。主键索引就是聚集索引

ID	name	code
100	tom	1
300	jack	2
400	rose	3
600	joy	4

以ID作为索引(主键索引)：其中R代表一整行的值。

以code作为索引（非主键索引）

从图中不难看出，主键索引和非主键索引的区别是：非主键索引的叶子节点存放的是主键的值，而主键索引的叶子节点存放的是整行数据，其中非主键索引也被称为二级索引，而主键索引也被称为聚簇索引。

根据这两种结构我们来进行下查询，看看他们在查询上有什么区别。

1、如果查询语句是 select * from table where ID = 100,即主键查询的方式，则只需要搜索 ID 这棵 B+树。

2、如果查询语句是 select * from table where code = 1，即非主键的查询方式，则先搜索code索引树，得到ID=100,再到ID索引树搜索一次，这个过程也被称为回表。

主键索引查询只会查一次，而非主键索引需要回表查询多次，那么所有情况都是这样的吗？非主键索引一定会查询多次吗？

覆盖索引也可以只查询一次。

覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。 当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O提高效率。 如，表table中有一个普通索引 idx_key1_key2(key1,key2)。
当我们通过SQL句：select key2 from table where key1 = ‘keytest’;的时候，就可以通过覆盖索引查询，无需回表。

为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？

为什么要有主键？

表数据文件本身就是B+Tree组织的一个索引结构文件，所以必须指定主键，在Mysql建表的时候，如果不指定主键，Mysql在建表的时候，后台会找一列可以作为唯一标识的建立索引，如果找不到，后台会默认加一列数据作为索引。

为什么要用整型？

Mysql根据条件查找数据的时候，是通过比较索引的大小来查找的。很多情况我们使用UUID作为主键，UUID是一个字符串，要先转换成ASCII码在比较大下，而Int类型的主键更容易做大小的比较。并且用UUID（32个字节，int类型8个字节）作为主键，一个节点能够存储的索引就会少很多，这样B+树的高度就会变高，增加I/O。

为什么要自增?

如果我们插入 ID = 650 的一行数据，那么直接在最右边插入就可以了

但是如果插入的是 ID = 350 的一行数据，由于 B+ 树是有序的，那么需要将下面的叶子节点进行移动，腾出位置来插入 ID = 350 的数据，这样就会比较消耗时间，如果刚好 R4 所在的数据页已经满了，需要进行页分裂操作，这样会更加糟糕。

但是，如果我们的主键是自增的，每次插入的 ID 都会比前面的大，那么我们每次只需要在后面插入就行， 不需要移动位置、分裂等操作，这样可以提高性能。也就是为什么建议使用主键自增的索引。

Mysql索引是如何支持千万级的快速查找的？

首先索引的结构是B+Tree的结构，又因为MySQL每个节点的大小是16kb，非叶子节点存储的只是索引数据，假设一行数据大小为1K，那么一页就能存储16条数据，也就是一个叶子节点能存16条数据，再看非叶子节点，假设主键ID为int类型，那么长度为8B,指针大小在Inonodb源码中为6B，一共就是14B,那么一页里就可以存储16K/14B=1170个（主键+指针）。
那么一个高度为2的B+树就能存储的数据为：1170 x 16=18720条，一个高度为3的B+树能存储的数据为：1170 x 1170 x 16=21902400（千万级别）。

关于上面提到的页的补充

CPU从磁盘取数据有一个页的概念，CPU从磁盘加载数据到内存，每次取的都是页的整数倍，1页=4K，一次I/O最多加载1-5页的数据。
B+Tree节点的大小设为等于一个页，每次新建节点直接申请一个页的空间，这样就保证一个节点物理上也存储一个页里，就实现了一个节点的载入只需要一次I/O。
B+Tree的高度h非常小，一般为1到3。

为什么非主键索引结构叶子节点存储的是主键值？

为了数据的一致性，存储数据的时候，只需保证主键索引存储成功即可，而无需等待非主键索引数据存储成功，才能提交数据；还有节省存储空间，如果非主键索引也存储数据，那么有几个索引则数据就会存储几份，这样造成空间的浪费和数据冗余。

索引的最左前缀原则（针对联合索引）

MySQL索引查询会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。所以当我们创建一个联合索引的时候，如(key1,key2,key3)，相当于创建了（key1）、(key1,key2)和(key1,key2,key3)三个索引，这就是最左匹配原则。
比如，联合索引的字段为 (key1,key2,key3),那么索引的结构是先按照key1的顺序，向B+Tree结构添加数据，然后，如果key1的值相同再按照key2的顺序向B+Tree结构添加数据，最后如果key1和key2的值相同再根据key3的顺序向B+Tree结构添加数据。

索引下推

MySQL 5.6引入了索引下推优化，默认开启，使用SET optimizer_switch = ‘index_condition_pushdown=off’;可以将其关闭。官方文档中给的例子和解释如下： people表中（zipcode，lastname，firstname）构成一个索引

SELECT * FROM people WHERE zipcode=‘123’ AND lastname LIKE ‘%tom%’;

如果没有使用索引下推技术，则MySQL会通过zipcode='123’从存储引擎中查询对应的数据，返回到MySQL服务端，然后MySQL服务端基于lastname LIKE '%tom%'来判断数据是否符合条件。 如果使用了索引下推技术，则MYSQL首先会返回符合zipcode='123’的索引，然后根据lastname LIKE '%tom%来判断索引是否符合条件。如果符合条件，则根据该索引来定位对应的数据，如果不符合，则直接reject掉。 有了索引下推优化，可以在有like条件查询的情况下，减少回表次数。

查询优化器

什么情况下会发生明明创建了索引，但是执行的时候并没有通过索引呢？

查询优化器 一条SQL语句的查询，可以有不同的执行方案，至于最终选择哪种方案，需要通过优化器进行选择，选择执行成本最低的方案。 在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案。这个成本最低的方案就是所谓的执行计划。 优化过程大致如下：
1、根据搜索条件，找出所有可能使用的索引
2、计算全表扫描的代价
3、计算使用不同索引执行查询的代价
4、对比各种执行方案的代价，找出成本最低的那一个

最后给大家推荐一个学习数据结构的网址（国外的）很实用。数据结构网址
页面如下：
对于学习二叉树、红黑树、B-Tree、B+Tree数据结构很好理解