初探MySQL索引

MySQL在项目开发中是最常用的了，但是对其还不是那么的了解，针对于索引展开学习，详细的学习一下索引到底是什么，有什么作用？为什么要建立索引？等一系列的问题要搞搞清楚。不能用的这么不明不白的。

什么是索引

有关于索引官方是这样介绍的，索引是帮助MySQL高效获取数据的结构。其实更通俗的来说，MySQL的索引就好比是一本书前面的目录，能够加快数据的查询速度。

大家应该都用过字典，字典前面有按照拼音查找和按照笔画查找文字，其实这个行为就有些类似于MySQL的索引了。

一般的来说索引本身的也很大，不可能全部都存储在内存中，因此索引往往也被存储在磁盘的文件中，可能存储在单独的索引文件中，也可能和数据存放在同一个文件中，这种情况依据情况而定。我们通常所说的索引包括聚集索引，覆盖索引，组合索引，前缀索引，唯一索引等，默认都是使用B+树结构组织(多路搜索树，并不一定是二叉树)的索引。

索引分类

关于索引的类型，上面也说了一些，也是蛮多的，但是如何正确的使用索引是个问题，下面一一说一下常用索引的类型有哪些。

单列索引

单列索引其实是一个统称，我是这样理解的，单列索引都包括什么呢？单列索引包括普通索引、唯一索引、主键索引。单列索引是一个索引只包含单个列，但是一个表中可以有多个单列索引。

普通索引

MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹时为了查询数据更快一点。

普通索引创建方式：

//  方式一
//  在原有表中添加普通索引
ALTER TABLE users ADD INDEX index_users (id)
//  方式二
//  在表中创建普通索引
CREATE INDEX index_name ON table_name (column_name)

唯一索引

索引列中的值必须是唯一的，但是允许为空值。

主键索引创建方式：

//  方式一
//  在原有表中添加唯一索引
ALTER TABLE users ADD UNIQUE (id)
//  方式二
//  创建唯一索引，数据不能有重复值
CREATE UNIQUE INDEX index_users ON users (id)

主键索引

这是一种比较特殊的唯一索引，不允许有空值，一般是在建表的时候指定了主键，就会创建主键索引（CREATE INDEX不能用来创建主键索引，使用 ALTER TABLE来代替）

主键索引创建方式：

//  在原有表中添加列时标注为主键索引
ALTER TABLE users ADD PRIMARY KEY (id)

组合索引

在表中的多个字段组合上创建索引，组合索引的使用，需要遵循最左前缀原则，一般情况下，建议使用组合索引代替单列索引。

最左前缀原则即最左优先，在检索数据时从联合索引的最左边开始匹配，组合索引的第一个字段必须出现在查询组句中，这个索引才会被用到。

组合索引创建方式:

//  创建组合索引
ALTER TABLE article Add INDEX inde_time (title(50),time(10))

单列索引(全文索引)

全文索引只有在 myisam 引擎上才能使用，只能在CHAR、VARCHAR、TEXT类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行。

单列索引(全文索引)创建方式：

//  创建全文索引
CREATE FULLTEXT INDEX index_name ON table(column(length))
alter table table_name add fulltext index_name(column)

索引的优缺点

索引优点：

1. 可以提高数据检索效率，减低数据库的IO成本，类似书的目录

2. 通过索引列对数据进行排序，降低数据的排序成本，降低了CPU的消耗

   1. 被索引的列会自动进行排序，包括单列索引和组合索引，只是组合索引的排序要复杂一些
   2. 如果按照索引列的顺序进行排序，对应的order by语句，效率就会提高很多
   3. where索引列在存储引擎层进行处理
   4. 覆盖索引，不需要回表查询

索引缺点：

1. 索引会占据内存空间
2. 索引虽然会提高查询效率，但是会降低更新表格数据的效率。例如每次对表数据进行增删操作，MySQL不仅要保存数据，还要保存和更新对应的索引文件。

索引原理

上文中已经说到过，使用索引的目的是为了提高查询效率，减少IO的操作成本，其实其原理和使用字典是相类似的。通过不断的缩小想要获取的数据范围来筛选出最终想要的数据结果，同时把随机的事件变成顺序事件，也就是说有了这种索引机制，总是可以使用同一种方法来查找想要的数据。

其实回头想想，数据库其实也是这个样子的，但是其内部实现原理要远远比这个复杂的多，因为在查询数据的时候不光是查询准确的值，有的时候我们可能需要进行范围性的查询。

其实实质上索引也是会存储到磁盘上，这个在上文中已经提到过了，其实索引的存储结构是在存储引擎里面来实现的，换句话来说依据不同的存储引擎会使用不同的索引。

myisam 和 innoDB 的存储引擎只支持B+TREE，也就是说默认使用的BTREE，不能够更换。innoDB和Memory存储则是是使用的HASH和BTREE引擎，HASH索引通常来说要比BTREE快。

说到这里才说到正点上，主要介绍一下B+TREE和BTREE两者之间的区别以及如何作业的。

BTREE和B+TREE

在上文中也提到过B+TREE是一种数据存储引擎，实质上B+TREE在数据库中的实现是最常见的一种索引形式。接下来就说一下BTREE和B+TREE是个什么玩意。假设我们的数据结构如下图所示。

看到图中不相同的数分为0012-0017，如果我们只看最下面这一层实际上可以看出我们得到的是一个类似于链表结构的数据结构图，通常都会把这部分叫做叶子节点。

其实BTREE和B+TREE最大的区别就是在于，B+TREE只会在叶子节点中产生所有的数据，BTREE则会在所有的节点上都会存有数据。如果我们细心观察叶子节点就会发现其内部的数据结构是从右向左从小到大有序排列的。B+TREE存储的数据是在一行中，并且这些数据都是有指针指向的，也就是有顺序的索引列，这个在上图中可以明显的看出来。

BTREE的高度一般会在2-4之间，树的高度越高则直接影响到其IO速度，因为每次IO都需要进行频繁的查找。若三层结构的支撑数据结构可以达到20G的话，那么4层树结构则会可以高达几十个T。

聚集索引(InnoDB)

聚集索引:聚集索引是指数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同。一个表只能有一个聚集索引，因为一个表的物理顺序只有一种情况，所以，对应的聚集索引只能有一个。如果某索引不是聚集索引，则表中的行物理顺序与索引顺序不匹配，与非聚集索引相比，聚集索引有着更快的检索速度。 ——节选自百度百科

主键索引的叶子节点会存储数据行，也就是说数据和索引是在一起的，这就是聚集索引。辅助索引只会存储主键的值。如果没有主键，则使用唯一索引简历聚集索引，如果没有唯一索引，Mysql会按照一定规则创建聚集索引。

主键索引

InnoDB要求表必须有主键，如果没有显式指定主键，则Mysql系统会自动选择一个可以做唯一表示的数据记录的列作为主键，如果不存在这种列，则Mysql自动为InnoDB表生成一个隐含字段作为主键，其类型为长整形。

从上图明显的可以看出聚集索引的主键下面直接挂着数据块，这也就印证了上文所说的内容。同时也证明了在叶子节点下面保存着当前数据块的完整数据，这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集。

这也就印证了，InnoDB必须存有主键，若没有主键，则数据就没有可以存放的地方，因为所有的数据都是挂载在叶子节点主键的下面的。

上文也说过如果没有主键Mysql会自动生成主键，但是不建议这样做，还是必须生成主键，否则会给Mysql带来一些额外的任务。

辅助索引

叶子节点保存的是主键，那么这里就涉及到一个新知识点，敲黑板！哒~哒~哒~冒蓝火的加特林，错了重来。哒~哒~哒~这个地方考试(面试)的时候会考，一定要注意。

那么我们先看下数据结构图如下：

如果我们的查询语句使用的是select * from user where age='19'，在查询过程中首先会找到age='19'下面的id也就是11，但是这里使用的是select *所以，找到对应id以后会去主键数据树上去查询所需要的数据，那么这个过程就叫做回表查询

说白了就是语句通过辅助索引找到了主键，又通过主键去找到所对应的数据块之后才能拿到所需要的数据。整个这个过程中想要拿到所需要的数据检索了两次，才拿到了数据，太累了，徽标查询太慢了。

如果使用select id,age from user where age='19'现在sql所查询的id、age字段在叶子节点中都是存在的。所以在一个辅助索引树上查询即可，完全不需要进行回表查询，在辅助索引树上就可以查询出所需要的数据，这种查询情况叫做覆盖索引。

其实就是我可以拿到我想要的东西，那么不需要再去找其他人要了。

非聚集索引(MyISAM)

非聚集索引:非聚集索引是一种索引，该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。 ——节选自百度百科

其实说白了就是数据和索引不在同一个文件中存储。分别是MYD和MYI。非聚集索引包含主键索引和辅助索引都会存储指针的值。MYD和MYI是myisam引擎表的结构文件，通过上述结论可以得出myisam就是使用的是非聚集索引，太机智了，哈哈哈。

主键索引

下图中有一个数据结构图假如我们的数据表中的主键为id，图中使用的就是myisam表的主索引，可以看出myisam的索引文件仅仅是保存的数据地址。

其实单单来看上图的话还是蛮抽象的，非聚集索引叶子节点仍然是索引节点，只是有一个指针指向对应的数据块，如果使用非聚集索引查询，而查询列中包含了其他该索引没有覆盖的列，那么他还要进行第二次的查询，查询节点上对应的数据行的数据。

需要知道的一点是白色部分和彩色部分是单独存放在两个不同的文件中，当我们通过主键进行查询的时候，是通过主键所存储的地址找到了数据表中所对应的数据块。

比如11对应的是数据存储地址是0x1那么在数据表中所对应的地址也必须同样是0x1，这样就能找到对应的相应的数据块。

辅助索引

在MySAM中，主要索引和次要索引在结构上没有任何区别，只是主要索引要求key是唯一的，而辅助索引的key可以重复。如果我们在age上建立一个辅助索引，则此索引的结构如下图所示。

在辅助索引中，叶子节点同样也是存储的是对应数据块的地址，与主键索引相同都是通过地址去获取到对应的区块。

索引使用场景

上面已经讲述的索引的用法和简单的原理，那么在使用索引的时候我们该如何正确的使用索引。

需要创建索引：

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该创建辅助索引
3. 夺标关联查询中，关联字段应该创建索引on两边都要创建索引
4. 查询中排序字段，应该创建索引
5. 频繁查找字段，覆盖索引
6. 查询中统计或者分组字段，应该创建索引`group by

不需要创建索引：

1. 表记录太少
2. 经常进行增删改操作的表
3. 频繁更新的字段
4. where条件里使用频率不高的字段

组合索引

MySQL创建组合索引的规则事首先会对组合索引的最左边的，也就是第一个name字段的数据进行排序，在第一个字段的排序基础上，然后再对后面的第二个cid字段进行排序。其实就相当于实现了order by name cid这样的一种规则。为了节省MySQL索引存储空间以及提升搜索性能，可建立组合索引，能使用组合索引就不要使用单列索引，为什么说组合索引比较节省空间？因为在一颗索引树上有3个索引，和三棵索引树分别对应一个索引，相比之下前者占用资源较少。多使用组合索引更容易实现索引覆盖，提升查询性能。

我们举例说明，a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。就是说组合索引从最左匹配到最右，直到遇到范围查询，就停止。如果没有范围查询，理论上，里面的索引顺序随便调整。不过，实际中，查询需求频繁或者字段选择性高的列放在前面，以提高索引使用的效率。

总结

以上讲了很多关于聚集索引和非聚集索引的查询区别，对于非聚集索引来说其内部存储的是地址，无论是主键索引还是辅助索引都需要通过地址去获取数据块。然而聚集索引在查询过程中取决与当前查询情况，针对不同情况会做出相应的处理，主键索引查询数据是最快的，如果是辅助索引的情况下，尽量多使用覆盖索引，如何多用覆盖索引呢？那么就是多用组合索引，就可以达到覆盖索引。

在聚集索引情况下，如果是辅助索引，辅助索引树中的数据字段越多，造成回表查询的几率就越小。

在使用索引过程中一定要主辅索引的使用，最后感谢大家用这么长时间来阅读这篇文章，若文章中有问题请在下方评论指出，我会尽快做出改正。