MySQL索引

对于MySQL的索引相信大家都很熟悉，它是存储引擎用于快速找到记录的一种数据结构

索引类型

我们常说的索引有一下几种：
- 单列索引
  
  一个索引只包含单个列，但一个表中可以有多个单列索引。
  
  单列索引又可以细分为以下几种
  1. 普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。
  2. 唯一索引：索引列中的值必须是唯一的，但是允许为空值
  3. 主键索引：是一种特殊的唯一索引，不允许为空值
  这几种单列索引我们就不介绍创建和修改了，作为我们常用的索引这些操作大家都很熟悉。
- 联合索引
  
  多个列组合创建的索引，遵循最左前缀原则，如（c1,c2,c3,c4....cN）的联合索引，where 条件按照索引建立的字段顺序来使用（条件可以不按顺序写），如果中间某列没有在条件中使用，或使用like会导致后面的列不能使用索引。
  
  索引也能用于分组和排序，分组要先排序，在计算平均值等等。所以在分组和排序中，如果字段顺序可以按照索引的字段顺序，即可利用索引的有序特性
- 全文索引
  
  MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
  
  MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
  
  使用的字段为char、varchar、text
  
  创建全文索引
```
create table fulltext_test (
    id int(11) NOT NULL,
    content text NOT NULL,
    PRIMARY KEY (id),
    FULLTEXT KEY content_tag_fulltext(content)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
```
  创建联合全文索引
```
create table fulltext_test (
    id int(11) NOT NULL,
    content text NOT NULL,
    content1 varchar(255),
    PRIMARY KEY (id),
    FULLTEXT KEY content_tag_fulltext(content,content1)  // 创建联合全文索引列
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
```
  在已存在的表上创建全文索引
```
// 通过create创建
create fulltext index content_tag_fulltext
    on fulltext_test(content);
// 通过alter创建
alter table fulltext_test
    add fulltext index content_tag_fulltext(content);
```
  删除全文索引
```
// 通过drop删除
drop index content_tag_fulltext
    on fulltext_test;
// 通过alter删除    
alter table fulltext_test
    drop index content_tag_fulltext;
```
  使用全文索引
```
select * from fulltext_test 
    where match(content) against('xxx');
```
- 空间索引
  
  MySQL的空间数据类型是5.7之后新加入的功能，所以MySQL的空间数据类型使用的人并不多，如果是GIS开发一般不使用MySQL作为数据存储，所以暂时不说这些，后面会找时间专门介绍
  
  MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用SPATIAL关键字，引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL。
但是其实从MySQL的索引整体上来说这些只是索引的类型，所谓类型就是我们直接使用的，MySQL对外展示的形式。而这些类型下又存在着不同的算法方式来支撑这些索引的实现。支撑这些类型的方式大概可以分为四种：BTREE，RTREE， HASH ，FULLTEXT （全文索引我们在上面已经说明，后面不再进行解释）。
索引方法

在InnoDB中，所有的数据都是以页的形式为基本单位存贮的。而每个数据页通过双向链表连接起来。在页内的记录会按照主键的大小从小到大顺序组成一个单向链表。如果你想要在某个页内找到你需要的数据，那么根据你查找的依据InnoDB会有两种方式来查找它：
1. 以主键为搜索条件以主键为搜索条件很简单，InnoDB会先在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。（如果没有主键，InnoDB会建立一个虚拟的主键，这个主键我们看不到）
2. 以其他列作为搜索条件对非主键列的查找的过程可就不这么幸运了，因为在数据页中并没有对非主键列建立所谓的页目录，所以我们无法通过二分法快速定位相应的槽。这种情况下只能从最小记录开始依次遍历单链表中的每条记录，然后对比每条记录是不是符合搜索条件。很显然，这种查找的效率是非常低的。
但是这个只是在一个页内查找，如果要在多页内查找记录，那么我们并没有依据能快速的定位到记录，所以只能从第一个页沿着双向链表一直往下找，遍历每一个页的数据直到结束。如果数据量很少查找的速度我们不会感觉到很慢，但是如果数据量达到了一亿条呢？几十亿条呢？所以为了解决这个问题，我们文章开头那句话他就诞生了。
- B-TREE索引
  
  InnoDB中设计了一种索引方案，提出了目录项记录这个概念，其实这个目录项记录和我们的普通数据页没有什么区别。从物理上类比如果一个表中的数据我们看作是一本书，那么每条数据记录就是一节，数据库中的页对应着书中的一章，而目录项记录就是书前面的目录，他清清楚楚的记录了每个章节的页码。
  
  如图目录项记录中存放的是每个数据页的主键最小值，和每个数据页的页号。当我们再通过主键查找数据的时候我们就可以先通过目录项记录页确定我们查找的记录在那个页中，然后再到具体的数据页中取出数据。比如我们查找id为4的记录。4大于1小于5那么这条记录就存在页码为10的数据页中。
  
  当一个数据页存放不下这些目录项记录的时候那么就需要重新分配一个新的存储目录项记录的页，且新的页和之前的页构成一个双向链表
  
  但是这个时候如果目录项记录页非常的多，我们查找的时候又会非常的吃力。其实这个时候InnoDB将原目录项记录页当作数据页在其上再次生成一个目录项记录页。这样我们经过层层查找就能很快的定位到我们需要的数据。
  
  通过这个图我们可以看出这个设计形成的数据结构非常像我们常说的树，不错他就是InnoDB中经常被提及的B+树。不论是存放用户记录的数据页，还是存放目录项记录的数据页，我们都把它们存放到B+树这个数据结构中了，所以我们也称这些数据页为节点。从图中可以看出来，我们的实际用户记录其实都存放在B+树的最底层的节点上，这些节点也被称为叶子节点或叶节点，其余用来存放目录项的节点称为非叶子节点或者内节点，其中B+树最上边的那个节点也称为根节点。
  
  有人会问，如果存放的数据量非常大，B+树的层数是不是也会非常的高，导致索引的效率变慢，甚至还不如没有索引。
  
  其实这就有点想多了，你能想到的问题，设计这个数据结构的人会想不到嘛。假设我们每个用户记录页可以存储100条数据，而目录记录页可以存放1000条数据（为什么目录记录页会比用户记录也记录的数据条数要多呢？那是因为目录记录页的每条数据只保存最小主键和页码数）
  
  那么如果有一层目录记录页就是1000×100=100000条记录
  
  如果有两层目录记录页就是1000×1000×100=100000000条记录
  
  如果有三层目录记录页就是1000×1000×1000×100=100000000000条记录
  
  你看只要有三层的目录记录页，也就是树的高度为4的时候就可以记录100000000000这么多的数据，真实的场景中你会在一张表中记录这么多数据嘛。
  - 聚簇索引
    
    所谓聚簇索引是指有以下特点的B+树：
    1. 使用记录主键值的大小进行记录和页的排序：
      - 页内的记录是按照主键的大小顺序排成一个单向链表。
      - 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。
      - 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。
    2. B+树的叶子节点存储的是完整的用户记录，所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。
    聚簇索引并不需要我们在MySQL语句中显式的使用INDEX语句去创建（后边会介绍索引相关的语句），InnoDB存储引擎会自动的为我们创建聚簇索引。
  - 二级索引
    
    所谓的二级索引是指我们在创建了主键索引后又想为别的列创建索引，这个时候InnoDB会再生成一棵B+树，但是这棵B+树的叶子节点并不是用户记录。比如我们有一张表，表中有三个属性列：c1,c2,c3。其中c1为主键。我们为主键创建了索引，然后又为c2创建了索引。那么此时c2所形成的B+树如图所示：
    
    为什么形成的树中会保存c1列的值呢。那是因为当我们通过这个索引找到具体对应的c2的时候，会通过主键即c1的值到真实存储用户记录的页中定位到具体的记录（此处会使用主键索引查找，并不是一一遍历）。这部操作叫做回表
  - 联合索引
    
    联合索引从本质上来说他也是二级索引，只不过他的索引列不再是单个列，而是多个列组合而成。比如联合索引使用c2，c3两个列作为索引列，那么他会先把各个记录和页按照c2列进行排序。在记录的c2列相同的情况下，采用c3列进行排序。除此之外，他不再采用和单列的二级索引一样在每个记录中都带着主键值，而是把主键值放到叶子节点中。
- Hash索引
  
  和B-Tree索引比起来，Hash索引能够效率更高的定位到数据，因为其key-value的方式，索引的检索只需要一次，不像B-Tree索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问。
  
  那么为什么Hash索引没有代替B-Tree索引呢？那是因为Hash索引的特殊性也带来了很多限制和弊端。
  1. 哈希索引只支持等值比较查询，包括＝、 IN 、<=> (注意<>和＜＝＞是不同的操作）。不支持任何范围查询，例如WHERE price > 100。　　由于Hash索引比较的是进行Hash运算之后的Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的Hash算法处理之后的Hash值的大小关系，并不能保证和Hash运算前完全一样。
  2. Hash索引无法被用来避免数据的排序操作。　　由于Hash索引中存放的是经过Hash计算之后的Hash值，而且Hash值的大小关系并不一定和Hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算;
  3. Hash索引不能利用部分索引键查询。　　对于组合索引，Hash索引在计算Hash值的时候是组合索引键合并后再一起计算Hash值，而不是单独计算Hash值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash索引也无法被利用。
  4. Hash索引在任何时候都不能避免表扫描。　　前面已经知道，Hash索引是将索引键通过Hash运算之后，将 Hash运算结果的Hash值和所对应的行指针信息存放于一个Hash表中，由于不同索引键存在相同Hash值，所以即使取满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。
  5. Hash索引遇到大量Hash值相等的情况后性能并不一定就会比BTree索引高。　对于选择性比较低的索引键，如果创建Hash索引，那么将会存在大量记录指针信息存于同一个Hash值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。
  除此之外，我们常用的存储引擎InnoDB并不支持Hash索引，只有Memory和ndb支持他，这也是我们平常开发中很少使用Hash索引最主要的原因。
- R-Tree索引
  
  目前R-Tree索引主要用于空间数据的索引，但是MySQL的空间数据类型直到5.7之后才发布，而且应用的并不是很多，所以目前也没有太了解这个索引方法，等学习之后再来补上这块内容。
  
  文章参考：MySQL 是怎样运行的：从根儿上理解 MySQL

浅谈MySQL的索引

MySQL索引

索引类型

单列索引

联合索引

全文索引

空间索引

索引方法

B-TREE索引

聚簇索引

二级索引

联合索引

Hash索引

R-Tree索引