SQL索引

什么是索引？

索引就是帮助数据库管理系统高效获取数据的数据结构。数据库中的索引，就好比一本书的目录，它可以帮我们快速进行特定值的定位与查找，从而加快数据查询的效率。

索引的使用情况

索引不是万能的，在有些情况下使用索引反而会让效率变低：

当数据量少，是否使用索引对结果影响不大。

当数据重复度大，高于10%的时候，也不需要对这个字段使用索引。但有些时候也会使用，比如定位一个比例很少的字段。

索引的价值是快速定位。如果想要定位的数据有很多，那么索引就失去了它的使用价值，比如通常情况下的性别字段。不过有时候，我们还要考虑这个字段中的数值分布的情况，当性别字段的数值分布非常特殊，比如男性的比例非常少时索引就有用武之地。我们不仅要看字段中的数值个数，还要根据数值的分布情况来考虑是否需要创建索引。

索引的种类（功能逻辑和物理实现）

从功能逻辑上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引和全文索引。

普通索引是基础的索引，没有任何约束，主要用于提高查询效率。

唯一索引就是在普通索引的基础上增加了数据唯一性的约束,在一张数据表里可以有多个唯一索引。

主键索引在唯一索引的基础上增加了不为空的约束，也就是 NOT NULL+UNIQUE，一张表里最多只有一个主键索引。

全文索引用的不多，MySQL 自带的全文索引只支持英文。我们通常可以采用专门的全文搜索引擎，比如 ES(ElasticSearch) 和 Solr。

普通索引、唯一索引和主键索引都是一类索引，只不过对数据的约束性逐渐提升。

在一张数据表中只能有一个主键索引，这是由主键索引的物理实现方式决定的，因为数据存储在文件中只能按照一种顺序进行存储。但可以有多个普通索引或者多个唯一索引。

按照物理实现方式，索引可以分为 2 种：聚集索引和非聚集索引。我们也把非聚集索引称为二级索引或者辅助索引。

mysql的innodb表，就是索引组织表，表中的所有数据行都放在索引上，这就约定了数据是严格按照顺序存放的，不管数据插入的先后顺序，都会插入到固定的物理位置。

聚集索引（决定存储顺序，一定有）：

每个表都需要一个聚集索引，通常，聚集索引与主键同义。如果没有主键或是没有NOT NULL的唯一索引（作主键索引），存储引擎会自己设置隐含的聚集索引。

一个叶子节点存放一行的数据，直接通过这个聚集索引的键值找到某行；不需要回表查询

数据的物理存放顺序与索引顺序是一致的（聚集索引决定存储顺序，一张表只能有一个聚集索引），只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的，所以不管插入的先后顺序，数据都会存放到索引对应的位置。

数据行和相邻的键值紧凑地存储在一起，无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚集索引。

使用聚集索引的时候，数据的查询效率高，但如果对数据进行插入，删除，更新等操作（所有的记录就需要重新进行排序并重新写入到磁盘中，所以效率相比于非聚集索引可能会降低。 ），效率会比非聚集索引（非聚集索引只是存储索引，我们只需要更新这个索引即可，不需要对所有的记录重新排序。）低。

非聚集索引（不一定存在）：

非聚集索引的叶子节点存储的是数据位置。

叶子节点存放的是字段的值，通过这个非聚集索引的键值找到对应的聚集索引字段的值，再通过聚集索引键值找到表的某行。（回表查询）

通俗理解：

• 聚集索引 ：类似字典正文内容按照目录排列规则排序。
• 非聚集索引：只存放某些字在字典的位置
• 每个表只能有一个聚集索引，因为目录只能按照一种方法进行排序 。

聚集索引和非聚集索引的区别：

聚集索引决定存储顺序，非聚集索引不影响存储顺序。

聚集索引的叶子节点存储的就是我们的数据记录，非聚集索引的叶子节点存储的是数据位置。

一个表只能有一个聚集索引，因为只能有一种排序存储的方式，但可以有多个非聚集索引，也就是多个索引目录提供数据检索。

使用聚集索引的时候，数据的查询效率高，但如果对数据进行插入，删除，更新等操作，效率会比非聚集索引低。

聚集索引和主键索引的区别：

如果我们定义了主键（主键索引），那么InnoDB会选择其作为聚集索引；

如果没有显式定义主键，则InnoDB会选择第一个非空唯一索引作为主键索引；

如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增，这个ROWID不像ORACLE的ROWID那样可引用，是隐含的)。

每个表都需要有一个聚集索引。

回表查询：

当查询非聚集索引时，查询到的结果是索引字段的地址（主键），而不是一条行记录。如果查询的字段不完全在这个非聚集索引当中，就会通过主键再去查询聚集索引，得到整条行记录。第二次查询就是回表查询。

 

 什么是索引覆盖(Covering index)？

将被查询的字段，建立到联合索引里去，这样就可以避免回表查询，大大提高了查询效率。

 

单一索引和联合索引

联合索引的最左匹配原则：联合索引（a，b，c），查询a、ab、abc时可以用到索引，其他查询下联合索引就会失效。

SQL 条件语句中的字段顺序并不重要，因为在逻辑查询优化阶段会自动进行查询重写。

eg：查询“a=9 AND c=8 AND b=7” ，MySQL 的优化器可以自动帮我们调整为可以使用联合索引的形式。

如果遇到了范围条件查询，比如（<）（<=）（>）（>=）和 between 等，那么范围列后的列就无法使用到索引了

eg：当查询“x=9 AND y>8 AND z=7”的时候，如果建立了 (x,y,z) 顺序的索引，这时候 z 是用不上索引的。这是因为 MySQL 在匹配联合索引最左前缀的时候，如果遇到了范围查询，比如（<）（>）和 between 等，就会停止匹配。索引列最多作用于一个范围列，对于后面的 Z 来说，就没法使用到索引了。

索引的数据结构

索引其实就是一种数据结构，存放在硬盘上（永久保存）。

索引的优化概念

当在硬盘上进行查询时，会产生了硬盘的 I/O 操作。相比于内存的存取来说，硬盘的 I/O 存取消耗的时间要高很多。所以通过索引来查找某行数据的时候，需要计算产生的磁盘 I/O 次数，当磁盘 I/O 次数越多，所消耗的时间也就越大。如果能让索引的数据结构尽量减少硬盘的 I/O 操作，所消耗的时间也就越小。

二叉树做索引

树的深度决定了节点的比较次数，继而决定硬盘 I/O 操作的次数，最坏情况下二叉树的深度很大，因此不适合做索引。

 平衡二叉树

常见的平衡二叉树有很多种，包括了平衡二叉搜索树、红黑树、数堆、伸展树。平衡二叉搜索树是最早提出来的自平衡二叉搜索树，当我们提到平衡二叉树时一般指的就是平衡二叉搜索树。它在二分搜索树的基础上增加了约束，每个节点的左子树和右子树的高度差不能超过 1，也就是说节点的左子树和右子树仍然为平衡二叉树。 

当数据量 N 大的时候，以及树的分叉数 M 大的时候，M 叉树的高度会远小于二叉树的高度。

B树（平衡M叉树）

如果用二叉树作为索引的实现结构，会让树变得很高，增加硬盘的 I/O 次数，影响数据查询的时间。因此一个节点就不能只有 2 个子节点，而应该允许有 M 个子节点。当数据量 N 大的时候，以及树的分叉数 M 大的时候，M 叉树的高度会远小于二叉树的高度。

在文件系统和数据库系统中的索引结构经常采用 B 树来实现。

 磁盘块3处的38应为一个小于35大于26的数

B 树作为平衡的多路搜索树，它的每一个节点最多可以包括 M 个子节点，M 称为 B 树的阶。同时，每个磁盘块中包括了关键字和子节点的指针。如果一个磁盘块中包括了 x 个关键字，那么指针数就是 x+1。对于一个 100 阶的 B 树来说，如果有 3 层的话最多可以存储约 100 万的索引数据。对于大量的索引数据来说，采用 B 树的结构是非常适合的，因为树的高度要远小于二叉树的高度。

假设中间节点节点的关键字为：Key[1], Key[2], …, Key[k-1]，且关键字按照升序排序，即 Key[i]。此时 k-1 个关键字相当于划分了 k 个范围，也就是对应着 k 个指针，即为：P[1], P[2], …, P[k]，其中 P[1]指向关键字小于 Key[1]的子树，P[i]指向关键字属于 (Key[i-1], Key[i]) 的子树，P[k]指向关键字大于 Key[k-1]的子树。

查找过程：

假设想要查找的关键字是 9，那么步骤可以分为以下几步：

与根节点的关键字 (17，35）进行比较，9 小于 17 那么得到指针 P1；

按照指针 P1 找到磁盘块 2，关键字为（8，12），因为 9 在 8 和 12 之间，所以得到指针 P2；

按照指针 P2 找到磁盘块 6，关键字为（9，10），然后找到了关键字 9。

在 B 树的搜索过程中，比较的次数并不少，但如果把数据读取出来然后在内存中进行比较，这个时间就是可以忽略不计的。而读取磁盘块本身需要进行 I/O 操作，消耗的时间比在内存中进行比较所需要的时间要多，是数据查找用时的重要因素，B 树相比于平衡二叉树来说磁盘 I/O 操作要少，在数据查询中比平衡二叉树效率要高。

B+树

B+ 树基于 B 树做出了改进，主流的 DBMS 都支持 B+ 树的索引方式，比如 MySQL。

B+ 树和 B 树的差异在于以下几点：

• 有 k 个孩子的节点就有 k 个关键字。也就是孩子数量 = 关键字数，而 B 树中，孩子数量 = 关键字数 +1。
• 非叶子节点的关键字也会同时存在在子节点中，并且是在子节点中所有关键字的最大（或最小）。
• 非叶子节点仅用于索引，不保存数据记录，跟记录有关的信息都放在叶子节点中。而 B 树中，非叶子节点既保存索引，也保存数据记录。
• 所有关键字都在叶子节点出现，叶子节点构成一个有序链表，而且叶子节点本身按照关键字的大小从小到大顺序链接。

 每一层父节点的关键字都会出现在下一层的子节点的关键字中，因此在叶子节点中包括了所有的关键字信息，并且每一个叶子节点都有一个指向下一个节点的指针，这样就形成了一个链表。

 看起来 B+ 树和 B 树的查询过程差不多，但是 B+ 树和 B 树有个根本的差异在于，B+ 树的中间节点并不直接存储数据。这样的好处都有什么呢？

树的深度更矮

 树的阶数M最好要控制到使得每读取一个磁盘块不要超过 磁盘页 的大小，而B+树因为每个结点关键字占用空间更小（因为不用存储真正的数据记录（的指针）），M就可以设置的更大一些，这样还是B+树整体上不仅偏稳定，而且树的高度也要相对更低一些。

范围查询io操作更少

其次，关系数据库中还会大量使用范围查询、有序查询等，比如某时间范围内的用户交易数据。范围查询，这种查询的特点是会大量使用排序，比较，返回结果也往往是多条。 如果使用b树的话，需要使用中序遍历，因为数据节点不在同一层上，会频繁引起io，从而导致整体速度下降。而在b+树中，所有的数据节点都在叶子节点，相近的叶子节点之间也存在着链接，因此会节约io时间。

Hash索引

采用 Hash 进行检索效率非常高，基本上一次检索就可以找到数据，而 B+ 树需要自顶向下依次查找，多次访问节点才能找到数据，中间需要多次 I/O 操作，从效率来说 Hash 比 B+ 树更快。

Hash 索引与 B+ 树索引的区别

• Hash 索引不能进行范围查询，而 B+ 树可以。这是因为 Hash 索引指向的数据是无序的，而 B+ 树的叶子节点是个有序的链表。
• Hash 索引不支持联合索引的最左侧原则（即联合索引的部分索引无法使用，只能对联合索引全体使用），而 B+ 树可以。对于联合索引来说，Hash 索引在计算 Hash 值的时候是将索引键合并后再一起计算 Hash 值，所以不会针对每个索引单独计算 Hash 值。因此如果用到联合索引的一个或者几个索引时，联合索引无法被利用。
• Hash 索引不支持 ORDER BY 排序，因为 Hash 索引指向的数据是无序的，因此无法起到排序优化的作用，而 B+ 树索引数据是有序的，可以起到对该字段 ORDER BY 排序优化的作用。同理，我们也无法用 Hash 索引进行模糊查询，而 B+ 树使用 LIKE 进行模糊查询的时候，LIKE 后面前模糊查询（比如 % 开头）的话就可以起到优化作用。

总结

只有对于等值查询来说，Hash 索引的效率更高，不过也存在一种情况，就是索引列的重复值如果很多，效率就会降低。这是因为遇到 Hash 冲突时，需要遍历桶中的行指针来进行比较，找到查询的关键字，非常耗时。所以，Hash 索引通常不会用到重复值多的列上，比如列为性别、年龄的情况等。

通过索引提高SQL查询效率

哪些情况下创建索引？

1. 当字段的数值具有唯一性的限制

索引本身可以起到约束的作用，比如唯一索引、主键索引都是可以起到唯一性约束的，因此在我们的数据表中，如果某个字段是唯一性的，就可以直接创建唯一性索引，或者主键索引。

2. 频繁作为WHERE查询的字段特别是数据量大的情况下

在数据量大的情况下，某个字段在 SQL 查询的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。创建普通索引就可以大幅提升数据查询的效率。

3. 经常使用GROUP BY 或者 ORDER BY的情况下

索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序的时候，就需要对分组或者排序的字段进行索引。

实际上多个单列索引在多条件查询时只会生效一个索引（MySQL 会选择其中一个限制最严格的作为索引），所以在多条件联合查询的时候最好创建联合索引。

4..UPDATE、DELETE 的 WHERE 条件列，一般也需要创建索引

当对数据update、delete时，对where的字段加索引，也可以提高操作效率（要先查询到位置，再执行update或delete操作）。

5.DISTINCT 字段需要创建索引

注意：做多表 JOIN 连接操作时，创建索引需要注意以下的原则：

1. 连接表的数量不能超过3张

2. 其次，对 WHERE 条件创建索引，因为 WHERE 才是对数据条件的过滤。

3. 对用于连接外表的字段创建索引，并且该字段在多张表中的类型必须一致。

不需要加索引的情况

1.where、group by 、order by里用不到的字段不需要创建索引

2.如果表记录太少，比如少于 1000 个，那么是不需要创建索引的

3. 字段中如果有大量重复数据，也不用创建索引。（但是当比例特别悬殊时，也可以创建。）

4.最后一种情况是，频繁更新的字段不一定要创建索引。因为更新数据的时候，也需要更新索引，如果索引太多，在更新索引的时候也会造成负担，从而影响效率。

索引失效的情况

1. 索引进行了表达式计算，则会失效

如果对索引进行了表达式计算，索引就失效了。这是因为我们需要把索引字段的取值都取出来，然后依次进行表达式的计算来进行条件判断，因此采用的就是全表扫描的方式，运行时间也会慢很多

eg：

SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_id+1 = 900001

2. 对索引使用函数，也会造成失效

EXPLAIN SELECT comment_id, user_id, comment_text FROM product_comment WHERE SUBSTRING(comment_text, 1,3)='abc'

EXPLAIN会分析select语句的运行性能状态。

3.在 WHERE 子句中，如果在 OR 前的条件列进行了索引，而在 OR 后的条件列没有进行索引，那么索引会失效

因为 OR 的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引是没有意义的，只要有条件列没有进行索引，就会进行全表扫描，因此索引的条件列也会失效

4. 当我们使用 LIKE 进行模糊查询的时候，前面不能是 %

如果一本字典按照字母顺序进行排序，我们会从首位开始进行匹配，而不会对中间位置进行匹配，否则索引就失效了。

5. 联合索引的最左原则
不按照最左原则查询字段，也会使得索引失效

注意：索引列尽量设置为 NOT NULL 约束。

尽量将数据表的字段设置为 NOT NULL 约束，这样做的好处是可以更好地使用索引，节省空间，甚至加速 SQL 的运行。

判断索引列是否为 NOT NULL，往往需要走全表扫描，因此我们最好在设计数据表的时候就将字段设置为 NOT NULL 约束

比如你可以将 INT 类型的字段，默认值设置为 0。将字符类型的默认值设置为空字符串 ('')。