MySQL索引总结_mysql 索引

转载：MySQL 三万字精华总结 + 面试100 问，和面试官扯皮绰绰有余（收藏系列） (juejin.cn)

1 MySQL索引简介

索引的本质是 数据结构，索引的目的在于 提高查询效率，可以类比字典、 火车站的车次表、图书的目录等 。

可以简单的理解为“排好序的快速查找数据结构”，数据本身之外，数据库还维护者一个满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图是一种可能的索引方式示例。

• 索引本身也很大，不可能全部存储在内存中，一般以索引文件的形式存储在磁盘上。

• 平常说的索引，没有特别指明的话，就是B+树（多路搜索树，不一定是二叉树）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。此外还有哈希索引等。

索引的优势和劣势：

优势：

1. 提高数据检索效率，降低数据库IO成本；
2. 降低数据排序的成本，降低CPU的消耗；

劣势：

1. 索引也是一张表，保存了主键和索引字段，并指向实体表的记录，所以也需要占用内存；
2. 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。 因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段， 都会调整因为更新所带来的键值变化后的索引信息。

2 创建、删除、查看索引的命令

创建

• 新建索引：

  CREATE [UNIQUE] INDEX indexName ON mytable(username(length));
1

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

• 修改表结构时候添加索引：

  ALTER table tableName ADD [UNIQUE] INDEX indexName(columnName);
1

删除

DROP INDEX [indexName] ON mytable;
1

查看

SHOW INDEX FROM table_name\G
1

–可以通过添加 \G 来格式化输出信息。

使用ALERT命令

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):
1

该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list);
1

这条语句创建唯一索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。

ALTER TABLE tbl_name ADD INDEX index_name (column_list);
1

添加普通索引，索引值可出现多次。

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list);
1

该语句指定了索引为 FULLTEXT ，用于全文索引。

3 索引分类

（1）从数据结构角度分类：

• B+Tree 索引（重要）
• Hash 索引
• Full-Text 全文索引
• R-Tree 索引

（2）从物理存储角度分类：

• 聚集索引（clustered index）

• 非聚集索引（non-clustered index），也叫辅助索引（secondary index）

聚集索引和非聚集索引都是B+树结构。

（3）从逻辑角度分类：

• 主键索引：主键索引是一种特殊的唯一索引，不允许有空值。
• 唯一索引或者非唯一索引：值是不是唯一的。
• 单列索引（普通索引）：每个索引只包含单个列，一个表可以有多个单列索引。
• 多列索引（复合索引、联合索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合。
• 空间索引：空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。 MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建。

4 索引数据结构

索引（index）是在存储引擎（storage engine）层面实现的，而不是server层面。不是所有的存储引擎都支持所有的索引类型。即使多个存储引擎支持某一索引类型，它们的实现和行为也可能有所差别。

4.1 B+Tree 索引（重点）

MyISAM 和 InnoDB 存储引擎，都使用 B+Tree的数据结构，它相对与 B-Tree结构，所有的数据都存放在叶子节点上，且把叶子节点通过指针连接到一起，形成了一条数据链表，以加快相邻数据的检索效率。

详细讲解见：MySQL 三万字精华总结 + 面试100 问，和面试官扯皮绰绰有余（收藏系列） (juejin.cn)

B-Tree（读作：B树）、B+Tree（读作：B+树）

---

B-Tree结构：

模拟查找关键字29的过程：

1. 根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
2. 比较关键字29在区间（17,35），找到磁盘块1的指针P2。
3. 根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
4. 比较关键字29在区间（26,30），找到磁盘块3的指针P2。
5. 根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
6. 在磁盘块8中的关键字列表中找到关键字29。

B-Tree结构的缺陷：如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。

---

B+Tree 是在 B-Tree 基础上的一种优化。

B+Tree相对于B-Tree有几点不同：

1. 非叶子节点只存储键值信息；
2. 所有叶子节点之间都有一个链指针；
3. 数据记录都存放在叶子节点中。

MyISAM主键索引与辅助索引的结构

MyISAM引擎的索引文件和数据文件是分离的。MyISAM引擎索引结构的叶子节点的数据域（data），存放的并不是实际的数据记录，而是数据记录的地址。索引文件与数据文件分离，这样的索引称为"非聚簇索引"。MyISAM的主索引与辅助索引区别并不大，只是主键索引不能有重复的关键字。

InnoDB主键索引与辅助索引的结构

InnoDB引擎索引结构的叶子节点的数据域（data），存放的就是实际的数据记录（对于主键索引，此处会存放表中所有的数据记录；对于辅助索引此处会引用主键，检索的时候通过主键到主键索引中找到对应数据行），或者说，InnoDB的数据文件本身就是主键索引文件，这样的索引被称为"“聚簇索引”，一个表只能有一个聚簇索引。

主键索引

它的索引结构是在同一个树节点中同时存放索引和数据。

在Innodb中，索引分叶子节点和非叶子节点，非叶子节点就像新华字典的目录，单独存放在索引段中，叶子节点则是顺序排列的，在数据段中。

辅助索引

它的索引结构跟主键索引的结构有很大差别，在最底层的叶子结点有两行数据，第一行的字符串是辅助索引，按照ASCII码进行排序，第二行的整数是主键的值。

这就意味着，对列进行条件搜索，需要两个步骤：

① 在辅助索引上检索目标的值，到达其叶子节点获取对应的主键；

② 使用主键在主索引上再进行对应的检索操作。

这也就是所谓的 “回表查询”。

几个问题

问题:1：为什么Mysql索引要用B+树不是B树？

用B+树不用B树考虑的是IO对性能的影响，B树的每个节点都存储数据，而B+树只有叶子节点才存储数据，所以查找相同数据量的情况下，B树的高度更高，IO更频繁。数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页（对应索引树的节点）。其中在MySQL底层对B+树进行进一步优化：在叶子节点中是双向链表，且在链表的头结点和尾节点也是循环指向的。

问题2：为何不采用Hash索引？

因为Hash索引底层是哈希表，哈希表是一种以key-value存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的，所以，对于区间查询是无法直接通过索引查询的，就需要全表扫描。

哈希索引只适用于等值查询的场景。

而B+ Tree是一种多路平衡查询树，所以他的节点是天然有序的（左子节点小于父节点、父节点小于右子节点），所以对于范围查询的时候不需要做全表扫描。

哈希索引不支持多列联合索引的最左匹配规则，如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题。

4.2 Hash索引

主要就是通过 Hash算法（常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储。

检索算法：在检索查询时，就再次对待查关键字再次执行相同的Hash算法，得到Hash值，到对应Hash表对应位置取出数据即可，如果发生Hash碰撞，则需要在取值时进行筛选。

目前使用Hash索引的数据库并不多，主要有Memory等。

MySQL目前有Memory引擎和NDB引擎支持Hash索引。

4.3 Full-Text 全文索引

全文索引也是MyISAM的一种特殊索引类型，主要用于全文索引，InnoDB从MYSQL5.6版本提供对全文索引的支持。

它用于替代效率较低的LIKE模糊匹配操作，而且可以通过多字段组合的全文索引一次性全模糊匹配多个字段。

同样使用B-Tree存放索引数据，但使用的是特定的算法，将字段数据分割后再进行索引（一般每4个字节一次分割），索引文件存储的是分割前的索引字符串集合，与分割后的索引信息，对应B-Tree结构的节点存储的是分割后的词信息以及它在分割前的索引字符串集合中的位置。

4.4 R-Tree 空间索引

空间索引是MyISAM的一种特殊索引类型，主要用于地理空间数据类型。

5 哪些情况需要创建索引，哪些情况不需要？

哪些情况需要创建索引：

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段
3. 查询中与其他表关联的字段，外键关系建立索引
4. 单键/组合索引的选择问题，高并发下倾向创建组合索引
5. 查询中排序的字段，排序字段通过索引访问大幅提高排序速度
6. 查询中统计或分组字段

哪些情况不要创建索引：

1. 表记录太少
2. 经常增删改的表
3. 数据重复且分布均匀的表字段，只应该为最经常查询和最经常排序的数据列建立索引（如果某个数据类包含太多的重复数据，建立索引没有太大意义）
4. 频繁更新的字段不适合创建索引（会加重IO负担）
5. where条件里用不到的条件字段不创建索引