一、概念

索引是一个单独的、存储在磁盘上的数据库结构，包含着对数据表里所有记录的引用指针。使用索引可以快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。

索引是在存储引擎中实现的，因此，每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。MySQL中索引的存储类型有两种，即BTREE和HASH，具体和表的存储引擎相关。MyISAM和InnoDB存储引擎只支持BTREE索引；MEMORY/HEAP存储引擎可以支持HASH和BTREE索引。

索引的优点：

  1. 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
  2. 可以大大加快数据的查询速度，这也是创建索引的主要原因。
  3. 在实现数据的参考完整性方面，可以加速表和表之间的连接。
  4. 在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间。

索引的缺点：

  1. 创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
  2. 索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
  3. 当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

二、索引类型

  1. FULLTEXT
    即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。

    全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%”这类针对文本的模糊查询效率较低的问题。

  2. HASH
    由于HASH的唯一（几乎唯一）及类似键值对的形式，很适合作为索引。

    HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。

  3. BTREE
    BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。

  4. RTREE
    RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。

    相对于BTREE，RTREE的优势在于范围查找。

三、索引种类

  • **主键索引(PRIMARY KEY)**：唯一标识，主键不可重复

  • **唯一索引(UNIQUE KEY)**：避免重复的列，可以重复，多个列都可以标识为唯一索引

  • **常规索引(KEY/INDEX)**：默认的，index/key来设置

  • **全文索引(FULLTEXT)**：快速定位数据

  • 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

索引的最左匹配原则

当我们建立组合索引时，组合索引当然还是一颗B+树，比如我们建立一个联合索引(a, b),那么它的索引结构应该是这样的。

image-20220411235712976

a索引：1，1，2，2，3，3

b索引：1，2，1，4，1，2

通过观察我们可以发现，在组合索引中，对于a索引来说，索引是有序排列的，对于b索引显然是无序排列的。同时我们还可以发现对于a值相等的情况下，b值也是有序的。

这种有序是相对的，a>1 and b=4;遇到这种范围查询，就不会再去走索引，这种情况下a值可以走索引，而b值在这个范围内是无序的，所以最终也不会走索引。

那么我们就基本可以得出最左匹配原则的定义：最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

注意：

对于组合索引，在查询使用时，最好将条件顺序按找索引的顺序，这样效率最高，例如：select * from table1 where col1=A AND col2=B AND col3=D。但是如果使用 where col2=B AND col1=A ，这里还是会用到索引的最左匹配的吧，它会被编译器调整顺序的。

例如：

1.png

2.png

mysql创建组合索引的规则是首先会对组合索引的最左边的，也就是第一个name字段的数据进行排序，在第一个字段的排序基础上，然后再对后面第二个的age字段进行排序。其实就相当于实现了类似 order by name age这样一种排序规则。
所以：第一个name字段是绝对有序的，而第二字段就是无序的了。所以通常情况下，直接使用第二个age字段进行条件判断是用不到索引的， 这就是所谓的mysql为什么要强调最左前缀原则的原因。

3.png

那么什么时候才能用到age字段的索引呢? 前提当然是age字段的索引数据也是有序的情况下才能使用咯，什么时候才是有序的呢？观察可知，是在name字段是等值匹配的情况下，age才是有序的。
这也就是mysql索引规则中要求复合索引要想使用第二个索引，必须先使用第一个索引的原因，而且第一个索引必须是等值匹配。

四、基本语法

1
2
3
4
5
6
7
8
9
10
-- 显示所有的索引信息
SHOW INDEX FROM student

-- 增几个全文索引(索引名) 列名
ALTER TABLE school.student ADD FULLTEXT INDEX `studentName`(`studentName`);

-- EXPLAIN 分析sql的执行状况
EXPLAIN SELECT * FROM student; -- 非全文索引

EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('王');

使用

1
2
3
4
5
-- CREATE INDEX 索引名 ON 表(`字段`)
CREATE INDEX id_app_user_name ON app_user(`name`)

-- 使用
SELECT * FROM app_user WHERE `name` ='用户100000';

索引在数据非常多的时候，可以极大的提高查找速度

五、如何设计索引

  1. 避免对经常更新的表进行过多的索引，并且索引中的列要尽可能少。应该经常用于查询的字段创建索引，但要避免添加不必要的字段。
  2. 数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。
  3. 在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列上不要建立索引。比如在学生表的“性别”字段上只有“男”与“女”两个不同值，因此就无须建立索引，如果建立索引不但不会提高查询效率，反而会严重降低数据更新速度。
  4. 当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度。
  5. 在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

六、索引的实现原理

在MySQL中，索引是在存储引擎层实现的，不同存储引擎对索引的实现方式是不同的，下面我们探讨一下MyISAM和InnoDB两个存储引擎的索引实现方式。

1、MyISAM索引实现：

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址，MyISAM索引的原理图如下。这里假设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

img

如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示。同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

img

2、InnoDB索引实现：

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

下图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

img

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。下图为定义在Col3上的一个辅助索引。这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

img

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

3、MySQL的索引为什么用B+树

https://blog.csdn.net/zzti_erlie/article/details/82973742

B+树由B树和索引顺序访问方法演化而来，它是为磁盘或其他直接存取辅助设备设计的一种平衡查找树，在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点，各叶子节点通过指针进行链接。如下图：

img

B+树索引在数据库中的一个特点就是高扇出性，例如在InnoDB存储引擎中，每个页的大小为16KB。在数据库中，B+树的高度一般都在2～4层，这意味着查找某一键值最多只需要2到4次IO操作，这还不错。因为现在一般的磁盘每秒至少可以做100次IO操作，2～4次的IO操作意味着查询时间只需0.02～0.04秒。

4、聚簇索引和非聚簇索引有什么区别？

在InnoDB存储引擎中，可以将B+树索引分为聚簇索引和辅助索引（非聚簇索引）。无论是何种索引，每个页的大小都为16KB，且不能更改。

聚簇索引是根据主键创建的一棵B+树，聚簇索引的叶子节点存放了表中的所有记录。辅助索引是根据索引键创建的一棵B+树，与聚簇索引不同的是，其叶子节点仅存放索引键值，以及该索引键值指向的主键。也就是说，如果通过辅助索引来查找数据，那么当找到辅助索引的叶子节点后，很有可能还需要根据主键值查找聚簇索引来得到数据，这种查找方式又被称为书签查找。因为辅助索引不包含行记录的所有数据，这就意味着每页可以存放更多的键值，因此其高度一般都要小于聚簇索引。