索引是什么？

  MySQL中的索引也是一种为了便于查找数据而在数据之外创建的一种数据结构，通过这种数据结构我们可以方便得根据条件查找我们想要得数据，索引的目的是加快数据的查找速度，从而提升查询的效率。索引的作用就类似于新画字典中的拼音目录，我们通过拼音可以快速的查找到指定的字。换句话说，如果给你一部没有拼音目录的字典，那么我们只能在字典中一页一页的去翻（当然，如果字典中的每个字都是按拼音排序，咱也可以通过二分查找来实现），总而言之就是查找效率会非常低下了。因此我们总结一下，索引有以下几个特点：

优点
  1.加快数据查询的速度和效率。（相当重要！！！）

缺点
  1.建立索引需要消耗额外的存储空间。因为索引也是一种按照某种数据结构组织的数据，通过这些数据我们可以快速地定位到我们想要查找的数据。
  2.建立和维护索引需要消耗额外的时间。虽然索引可以提升数据查询的效率，但是大量的索引的创建和维护的时间成本并不低。
  3.当对表中的数据进行增加、删除、修改时，除了维护原始数据，索引也需要同时进行动态的维护，降低了数据的维护速度

创建索引的建议

  上面我们对索引的优缺点进行了简单的分析，那么什么情况下需要创建索引？什么情况下又不适合创建索引呢？我们给出以下几点建议：
  1. 对于数据体量较大且查询频率远高于变更频率的列，建议创建索引。
  2. 对于数据区分度不大的列不建议创建索引。比如用户的性别，枚举范围较小的列。
  3. 对于需要频繁变更（修改和删除）的列，不建议创建索引。因为在频繁变更的列上创建索引，会导致变更操作的效率变低。
  4. 数据体量不大或者查询需求不高的的情况下，不建议创建索引。
  5. 索引需要根据查询需求来确定，并且尽可能以最小范围的方式进行创建，不建议对所有列创建索引。对于业务已经变更，需求不大的索引，需要及时drop。

MySQL存储引擎概览

  在详细介绍MySQL索引原理之前，我们需要先简单介绍一下MySQL的存储引擎。存储引擎是处理不同表类型的SQL操作的MySQL组件，目前InnoDB已经成为MySQL默认的存储引擎，也是目前使用范围最广的存储引擎，除此之外，我们可以使用SHOW ENGINES命令来查看当前版本的MySQL所支持的存储引擎，示例如下：

mysql> SHOW ENGINES
*************************** 1. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 5. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 8. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO

  由于很多存储引擎只针对特定的场景，很多人可能终其一生也不会用到，下面就从官网扒拉来一份几种常见的MySQL特性对比，如下表所示：

备注：
  1.在服务器中实现，而不是在存储引擎中。
  2.只有在使用压缩行格式时，才支持压缩的MyISAM表。使用MyISAM压缩行格式的表是只读  的。
  3.在服务器端通过加密功能来实现。
  4.在服务器端通过加密功能来实现；MySQL 5.7及以上版本支持静态数据加密
  5.在MySQL集群NDB 7.3及更高版本中支持使用外键。
  6.在MySQL 5.6及更高版本中支持FULLTEXT索引可用。
  7.在MySQL 5.7及更高版本中支持geospatial 索引可用。
  8.InnoDB内部利用哈希索引实现自适应哈希索引特性。

MySQL索引的分类

  MySQL中的索引，从作用范围上可以将其分成分单列索引和组合索引。单列索引即一个索引只包含单个列，一个表可以有多个单列索引。组合索引即一个索引包含多个列。这里我们不对单列索引和组合索引做过多介绍。我们主要从不同的用途出发归纳MySQL的索引分类，MySQL中的索引根据其具体的用途在逻辑上主要分成分为以下 3 类：

1.常规索引

  常规索引是 MySQL 中最基本的索引类型，它没有任何特殊限制限制，其主要目的就是提升数据的查询效率。常规索引又可以根据其约束和限制的严苛程度分为3种，分别是：普通索引、唯一索引和主键索引。

普通索引：普通索引允许在索引的列中插入重复值和空值。
唯一索引：唯一索引与普通索引的不同的是创建唯一性索引的目的不仅仅是为了提高访问速度，同时还要避免数据出现重复。唯一索引的列值必须唯一，但是允许有空值。如果是组合索引，则这些列值的组合的值必须唯一（组合索引不要某个列的值必须唯一）。
主键索引：主键索引是一种特殊的唯一索引，该索引是专门为主键字段创建的索引，不允许值重复或者为空值。

2. 全文索引

  首先全文检索并不是MySQL最擅长的任务之一，如果有巨大的全文检索需求，建议使用专门的全文检索引擎，如Solr和ES等。但是MySQL使用全文索引提供了对全文检索的支持，全文索引主要用来查找文本中的关键字，只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。全文索引允许在索引列中插入重复值和空值。

3. 空间索引

  与全文索引类似，MySQL中的空间索引也是一种针对特定类型的数据和查询需求创建的索引， 空间索引主要是对空间数据类型的字段建立的索引，如GIS中常用的坐标数据（经度、维度），创建空间索引的列必须将其声明为 NOT NULL，空间索引主要用于地理空间数据类型 GEOMETRY，对于没有这种空间数据处理任务的人来说，这类索引很少会用到。我们当初在一个时空网络的项目中使用到了空间索引，在针对GPS数据查询和坐标点距离排序这块，空间索引有着常规索引无法比拟的优势。

MySQL索引的实现原理

  上文中我们提供过，MySQL中的索引根据用途和场景不同，主要可以分成三种：常规索引、全文索引和空间索引。不同索引的实现原理不同，且在不同的存储引擎上的实现方式也不尽相同。比如常规索引的在InnoDB上可以使用B-Tree索引的方式实现，也可以使用Hash索引的方式实现；全文索引可以使用倒排索引的方式来实现；空间索引可以基于R-Tree的方式来实现。这里我们主要针对MySQL中Hash和B-Tree索引的实现原理进行详细的介绍。

Hash索引原理

  Hash索引也称为哈希索引或散列索引。MySQL 目前在 MEMORY和NDB 存储引擎中都支持支持该种类型的索引，其中，MEMORY 存储引擎将 Hash当成默认索引。Hash索引不是基于树形的数据结构查找数据，而是根据索引列对应的哈希值的方法获取记录所在的位置。Hash索引的原理很简单，大致如下图所示，主要是维护一个Hash函数，插入数据的时候对索引列的值进行Hash，得到HashCode后将该行数据存储在指定位置；查询数据的时候，也是同样的操作，先对索引列的值进行Hash，得到HashCode后去到指定的位置读取当前行的数据。Hash索引的最大优点是访问速度快，但也存在下面的一些缺点：

  1. 使用Hash索引需要在查询和建立索引的过程中进行Hash计算，Hash计算是一个比较耗时的操作，相对于 B-树索引来说，建立哈希索引会耗费更多的时间。
  2. 由于Hash的无序性，无法使用 Hash索引进行排序和范围查找。
  3. Hash索引只支持等值比较。
  4. Hash索引不支持键的部分匹配，因为Hash值是通过整个索引值计算得到的。
  5.随着数据量的增大，Hash冲突的现象会越来越明显，此时会出现明显的性能下降现象。

Hash索引

B+Tree索引原理

B+Tree索引是目前MySQL中使用最广的索引，MySQL中的B+Tree索引根据其实现方式的不同又可以分为聚集索引和非聚集索引。其中InnoDB存储引擎中的B+Tree的实现即为聚集索引，聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。而非聚集索引与聚集索引的主要区别在于非聚集索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的主键，MySQL中的MyISAM存储引擎中的B+树的实现就是非聚集索引。这里主要针对InnoDB存储引擎中的B+Tree实现原理进行展示，不同的存储引擎中，B+树的实现方式也不一样。B+树索引是一个典型的数据结构，其包含的组件主要有以下几个：

根节点：一个 B+树索引只有一个根节点，根节点位于树的最顶端。
分支节点：包含的条目指向索引里其他的分支节点。
叶子节点：包含的条目直接指向表里的数据行。B+树中叶子节点之间通过指针彼此相连。

  基于这种树形数据结构，表中的每一行都会在索引上有一个对应值。因此，在表中进行数据查询时，可以根据索引值一步一步定位到数据所在的行。

image.png

  B+树索引可以进行全键值、键值范围和键值前缀查询，也可以对查询结果进行 ORDER BY 排序。但 B+树索引必须遵循左边前缀原则，要考虑以下几点约束：

查询必须从索引的最左边的列开始。
查询不能跳过某一索引列，必须按照从左到右的顺序进行匹配。
存储引擎不能使用索引中范围条件右边的列。

MySQL索引优化建议

  这里给出一些MySQL的索引优化建议，仅供在使用MySQL中作为参考。

1.优先选择唯一性索引，尽量选择区分度高的列作为索引，可以更快速的通过该索引来确定某条记录。
2.为经常作为查询条件的字段建立索引，为经常需要排序、分组和联合操作的字段建立索引。
3.限制索引的数目： 越多的索引，会使更新表变得很浪费时间；尽量在当前索引的基础上扩展索引，尽量不要新建索引；删除不再使用或者很少使用的索引。
4.尽量使用字段长度较短的列做索引，如果索引的值很长，那么查询的速度会受到影响。
5.如果索引的值很长，尽量使用前缀来索引，比如like只有在‘%’不在最左边的时候索引才会生效，也就是最左匹配原则。
6.最左前缀匹配原则，非常重要的原则。
7.索引列不能参与计算，保持列“干净”：带函数的查询不参与索引。