本文已参与「新人创作礼」活动，一起开启掘金创作之路

存储引擎

不同存储引擎的特点

从MySQL5.5.5以后，InnoDB是默认引擎

这里主要介绍InnoDB和MyISAM的区别

存储文件：

- ibd中，既存储了索引，也存储了数据
- myi：索引文件
- myd：数据文件
- frm：表结构

这也就是为啥InnoDB引擎技能支持聚簇索引（数据和索引存在一起）和非聚簇索引（索引结构的叶子节点指向了数据对应的位置）的原因，因为ibd存储文件中既存储了索引也存储了数据

下面我们再谈具体的聚簇索引与非聚簇索引的区别

数据库的索引

数据库就类似一本字典，索引就类似于字典中的目录，当我们要查询数据库中的数据的时候，如果没有索引，只能一条一条的检索，直到找到需要的数据位置。建立了索引之后再查询数据呢，就相当于先查询字典中的目录，找到它在对应的第几页，这样就省去了我们一条条检索的时间，加快了检索效率

优点

(1)可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性.
(2)建立索引可以大大提高检索的数据,以及减少表的检索行数
(3)在表连接的连接条件可以加速表与表直接的相连
(4)在分组和排序字句进行数据检索,可以减少查询时间中分组和排序时所消耗的时间(数据库的记录会重新排序)
(5)建立索引,在查询中使用索引可以提高性能

缺点

(1)增加I/O成本。在创建索引和维护索引会耗费时间,随着数据量的增加而增加
(2)增加磁盘空间。索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间
(3)不合适的索引或索引过多，会降低增删改的效率。.当对表的数据进行 INSERT,UPDATE,DELETE 的时候,索引也要动态的维护,这样就会降低数据的维护速度,(建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快)。

索引的分类

按照存储结构来分

主要有B树索引、Hash索引两种

我们使用的是InnoDB引擎，默认的是B+树

几种树结构

二叉树

比根节点小的元素存放在根节点左边，比根节点大的树存放在根节点右边。如果数据是单边增长的情况那么出现的就是和链表一样的数据结构了，树高度大

红黑树

在二叉树的基础上多了树平衡，红黑树是一种自平衡二叉树，主要解决二叉搜索树在极端情况下退化成链表的情况，在数据插入的时候同时调整整个树，使其节点尽量均匀分布，保证平衡性，目的在于降低树的高度，提高查询效率。

什么是红黑树，具体的参考这篇博文

同样我们查找6，在二叉树中我们需要经过6个节点才能找到（1-2-3-4-5-6）,红黑树中我们只需要3个节点（2-4-6），但是mysql索引的数据结构并不是红黑树，因为如果数据量大了之后，树的高度就会很大。

B树

在红黑树的基础上，每个节点可以存放多个数据，降低了树的高度，减少了IO次数。并且每个节点除了存放键值外，还存放了数据

这个时候我们查找6 只需要2个节点就可以了，而且树的高度也比红黑树矮。

B+树

B树的变种，区别：

非叶子节点只存储键值信息
所有叶子节点之间都有链指针，方便进行范围查询
数据记录都存放在叶子节点中，减轻了非叶子结点的磁盘空间，能够存放更多的键值，树高更低

 叶子节点上保存了所有主键，并且是顺序排序的

B+ Tree索引和Hash索引区别

Hash索引

哈希索引基于哈希表实现。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。在mysql中，Memory引擎显式支持哈希索引。哈希索引的特点：索引的结构十分紧凑，因此查找速度非常快；哈希索引数据不是按照索引顺序存储的，无法用于排序；哈希索引不支持部分索引列匹配查找；哈希索引只支持等值比较查询(=,IN(),<=>)，不支持范围查询，如where price>100;

哈希索引适合等值查询，但是无法进行范围查询哈希索引没办法利用索引完成排序哈希索引不支持多列联合索引的最左匹配规则如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题

按照数据的存储方式分

MySQL规定，在使用InnoDB存储引擎的时候，必须且仅有一个聚集索引，非聚集索引也就是普通索引就看自己设置的有多少个了
InnoDB 主键使用的是聚簇索引，MyISAM 不管是主键索引，还是二级索引使用的都是非聚簇索引。

聚簇索引

聚集索引中的非叶子节点存储的是表的主键
聚集索引的叶子节点，存储着当前表中每条记录的所有信息

非聚簇索引

非聚集索引的非叶子节点存储的是自己设置的索引字段对应的值(如果是联合索引，那就是联合索引的几个字段对应的值)
非聚集索引的叶子节点，存储当前索引字段值及对应的主键ID(也就是聚集索引的非叶子节点存储的值)

什么是回表查询

如果是通过非主键索引进行查询，select所要获取的字段不能通过非主键索引获取到，需要通过非主键索引获取到的主键，从聚集索引再次查询一遍，获取到所要查询的记录，这个查询的过程就是回表

非主键索引一定会回表查询吗

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

聚蔟索引和非聚簇索引的区别

聚簇索引也好，非聚簇索引也好，他们只是索引的一个基本分类。他们最主要的点在存储引擎的不同。如果说我们使用的是innodb引擎，它的存储文件只有frm和idb两种，这就意味着，innodb里面，存放存储数据的文件和存放索引的文件是同一个文件，他们是放一起存储的，叫idb文件，一个表中可能会有多种索引，就意味着会有多个B+tree，但是不可能在多个B+tree上都存放完整的数据，这样会导致数据的冗余，所以，数据和索引放在一起的叫做聚簇索引，而其他的索引字段放的是聚簇索引的key值，通过回表的方式来查询对应的数据。在myisam引擎中的，有myi和myd文件，分别用来存放索引和数据，所以在myisam中只支持非聚簇索引。

更新主键的代价很高，因为将会导致被更新的行移动

因此，对于InnoDB表，我们一般定义主键为不可更新。

采用聚簇索引插入新值比采用非聚簇索引插入新值的速度要慢很多

因为插入要保证主键不能重复，判断主键不能重复，采用的方式在不同的索引下面会有很大的性能差距，聚簇索引遍历所有的叶子节点，非聚簇索引也判断所有的叶子节点，但是聚簇索引的叶子节点除了带有主键还有记录值，记录的大小往往比主键要大的多。这样就会导致聚簇索引在判定新记录携带的主键是否重复时进行昂贵的I/O代价。

按应用层次分

（1）普通索引

最基本的索引，它没有任何限制，用于加速查询。

（2）唯一索引

索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

（3）主键索引

是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。

（4）组合索引/联合索引

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。

（5）全文索引

主要用来查找文本中的关键字，而不是直接与索引中的值相比较。
fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。
fulltext索引配合match against操作使用，而不是一般的where语句加like。
它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。

联合索引中的最左原则

在检索数据时从联合索引的最左边开始匹配。所以当我们创建一个联合索引的时候，如(key1,key2,key3)，相当于创建了（key1）、(key1,key2)和(key1,key2,key3)三个索引，这就是最左匹配原则。
(key2,key1)也行，引擎会对顺序进行优化，也会走索引

主键与唯一索引的区别？

主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。唯一性索引列允许空值，而主键列不允许为空值。主键列在创建时，已经默认为空值 + 唯一索引了。
主键可以被其他表引用为外键，而唯一索引不能。一个表最多只能创建一个主键，但可以创建多个唯一索引。主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。在 RBO 模式下，主键的执行计划优先级要高于唯一索引。两者可以提高查询的速度。

什么情况下该创建索引？

频繁作为查询条件的字段应该创建索引；
查询中与其他表有关联的字段，例如外键关系；
在经常需要排序(order by),分组(group by)和的distinct 列上加索引，可以加快排序查询的时间

什么情况下不创建索引？

(1)查询中很少使用到的字段不应该创建索引,如果建立了索引然而还会降低mysql的性能和增大了空间需求.
(2)值重复率高的字段不适合建索引（比如性别、百万级数据时比如26个字母）
数据很少的字段也不应该建立索引,比如一个性别字段 0或者1,在查询中,结果集的数据占了表中数据行的比例比较大,mysql需要扫描的行数很多,增加索引,并不能提高效率
(3)定义为text和image和bit数据类型的列不应该增加索引,
(4)当表的修改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创建索引,这两个操作是互斥的关系

为什么性别不适合创建索引？

因为你访问索引需要付出额外的IO开销，你从索引中拿到的只是地址，要想真正访问到数据还是要对表进行一次IO。假如你要从表的100万行数据中取几个数据，那么利用索引迅速定位，访问索引的这IO开销就非常值了。但如果你是从100万行数据中取50万行数据，就比如性别字段，那你相对需要访问50万次索引，再访问50万次表，加起来的开销并不会比直接对表进行一次完整扫描小。

最后来总结下什么是索引

索引是用来加快数据访问，提高检索效率的，它是一种key-value的数据格式，而存储这种数据格式的数据结构有很多：比如hash、二叉树、B树、B+数等，不管使用哪种数据结构，最终都会涉及到内存和磁盘的交互，而在进行交互的时候会涉及到操作系统的两个基本的概念：局部性原理和磁盘预读，局部性原理又表现为：时间局部性和空间局部性，大概是指程序在执行时呈现出局部性规律，即在一段时间内，整个程序的执行仅限于程序中的某一部分。相应地，执行所访问的存储空间也局限于某个内存区域。磁盘预读说的是磁盘在和内存进行交互的时候有一个最基本的逻辑单位称为页，每次在进行数据读取的时候一般是读页的整数倍（页的大小的操作系统相关，一般为4k或者8K），在mysql默认的存储引擎innodb中，默认是16K，根据这两个概念就有个结论，因为内存的大小是有限的不可能一股脑的全读到内存里面，所以每次在进行查询的时候，数据是分块读的，每次读取一部分数据，再根据指针读取下一块的数据，在读数据过程中，要尽可能少的减少IO次数，IO次数越多，访问的速度也就越慢
mysql5.5版本后默认的存储引擎是B+树，为什么使用B+树呢，首先我来说说hash结构，它的优点毋庸置疑，那就是查询效率非常的高，但是它也有很显著的缺点，首先就是必须要设计一个优秀的hash算法，来保障数据足够的散列，如果存在大量的hash冲突，就会导致某些查询效率非常低，而且hash适合做等值查询，当要进行范围查询的时候，需要挨个的进行比较，效率就非常低，而我们实际环境中，大部分场景需要的都是范围查询，所以hash结构不适合
然后再来看看二叉树，二叉树有很多种，比如二叉树、二叉平衡树，红黑树等等，它们有个共同的特点就是只有两个叉，二叉树呢因为不平衡，如果数据都倾斜到某一边，那就相当于一条长链表了，查询效率就大打折扣，AVL树和红黑树倒是解决了这个问题，会让左右子树相对平衡，但是因为他们的共同特点，如果数据量非常大，树的高度就会非常高，IO次数就会提高，所以也不适合，这时候如果有一个矮胖的树，就能解决这个问题了
B树就是这样的数据结构，它每个数据节点可以放多个数据值，这样就可以分出多个叉，降低了树的高度，减少了IO次数，但是它依然有问题，它的数据和主键是存放在一起的，也就是每个节点除了存放主键以外还存放对应主键的数据，每个节点又是一个磁盘块，它的大小是固定的也就是16KB，你把数据也存在里面自然占用了很多内存空间，如果把数据和主键维护到叶子节点当中，非叶子节点值存放主键用来将请求路由到具体的叶子节点上，那么非叶子节点因为只存放主键，，就可以存储更多的主键，分出更多的叉，更近一步的降低了树高，并且由于相邻磁盘块之间并没有指针，所以如果要进行范围查询，还需要重新路由一次到隔壁的磁盘块
所以B+树就来了，它将主键和数据按顺序的维护在了叶子节点上，非叶子节点只用来路由，并且叶子节点之间的磁盘块还有前后指针，方便进行范围查询，这样就减少了IO次数，一次提高了范围查询的效率

MySQL索引的理解