【20.0】MySQL进阶知识之索引

【一】索引的概念

• 索引（在MySQL中也叫做“键（key）”）是存储引擎用于快速找到记录的一种数据结构，这也是索引最基本的功能。
• 索引对于良好的性能非常关键。
  • 数据量越大时，索引对性能的影响也越重要，好的索引可以将查询性能提高几个数量级。
  • 在数据量较小且负载较低时，不恰当的索引对性能的影响可能还不明显，但是在数据量逐渐增大时，糟糕的索引会使MySQL的性能急剧的下降。
• 索引优化是查询性能优化最有效的手段。
• 如果想要在一本书中找到某个特定主题，一般会先看书的目录，找到对应的页码，然后直接翻到对应的页码即可查看。
  • 在MySQL中，存储引擎用类似的方法使用索引
    • 首先在索引中找到对应的值
    • 然后根据匹配的索引记录找到对应的数据行。
• 简单的说，数据库索引类似于书前面的目录，能加快数据库的查询速度。

【二】MySQL中索引的类型

• 创建主键约束（PRIMARY KEY）、唯一约束（UNIQUE）、外键约束（FOREIGN KEY）时，会自动创建 对应列的索引。

• 索引在MySQL中也叫键，是存储引擎用于快速查找记录的一种数据结构

  • 主键约束（PRIMARY KEY）
  • 唯一约束（UNIQUE）
  • index key
  • 外键约束（FOREIGN KEY）

• foreign key

  • 不是用来加速查询的

• primary key/unique key

  • 不仅可以加速查询速度，还具有对应的约束条件

• index key

  • 只有加速查询速度的功能

【三】索引的本质

• 通过不蹲的缩小想要的数据范围筛选出最终的结果
  • 同时将随机事件(一页一页的翻)变成顺序时间(先找目录再找数据)
• 也就是说我们有了索引机制，我们可以总是用一种固定的方式查询数据

【四】索引的缺点

• 当表中有大量数据存在的前提下，创建索引的速度回非常慢
• 在索引创建完毕后，对表的查询性能会大幅度的上升，但是写的性能也会大幅度下降

不要随意地创建索引

【五】索引的使用场景

• 要考虑对数据库表的某列或某几列创建索引，需要考虑以下几点：

  • 数据量较大，且经常对这些列进行条件查询。
  • 该数据库表的插入操作，及对这些列的修改操作频率较低。
  • 索引会占用额外的磁盘空间。

• 满足以上条件时，考虑对表中的这些字段创建索引，以提高查询效率。

• 反之，如果非条件查询列，或经常做插入、修改操作，或磁盘空间不足时，不考虑创建索引。

【六】索引操作

【1】查看索引

（1）语法

show index from 表名;

（2）示例

mysql> show index from student;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY    |            1 | id          | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
| student |          0 | sn         |            1 | sn          | A         |           8 |     NULL | NULL   | YES  | BTREE      |         |               |
| student |          1 | classes_id |            1 | classes_id  | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.01 sec)

mysql> desc student;-- 实现表结构 --
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| sn         | int(11)     | YES  | UNI | NULL    |                |
| name       | varchar(20) | YES  |     | unkown  |                |
| qq_mail    | varchar(20) | YES  |     | NULL    |                |
| classes_id | int(11)     | YES  | MUL | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

【2】创建索引

• 对于非主键、非唯一约束、非外键的字段，可以创建普通索引

（1）语法

create index  索引名 on 表名(字段名);

# 索引名的命名规则一般是：index_表名_列名

（2）示例

mysql> create index index_student_name on student(name);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

• 在实际开发中，如果项目的数据量非常大，创建索引的成本是非常高的，所以在实际开发时，在建表的时候索引就要规划好，如果表里有很多数据了，建议不要再额外添加索引了。

【3】查看索引

（1）语法

drop index 索引名 on 表名

（2）示例

mysql> drop index index_student_name on student;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

【七】索引的数据结构

• 对于索引的操作是非常简单的，但是关键在于我们要去学习支持索引的数据结构。
• 在数据结构那门课程里，我们可以使用二叉搜索树来加快查询，但是元素个数一多，对于输的高度就就会很高，而树高就代表着比较次数多，在实际项目中代表中I/O的访问次数多，因为数据库中的数据是存在硬盘里的。
• 或者我们还学过使用哈希表，他查询的时间复杂度为O(1)但是哈希表不支持范围查找，不支持模糊匹配。
• 实际上索引的背后使用的是B+树。
• 在了解B+树之前，先要了解B树，如果有考过408同学应该是对这个数据结构是非常熟悉的

【1】 B树和B+树

（1）B树

• B树本质上就是一个N叉的搜索树（查找树）。

（2）B+树

• B+树是在B树的基础上进行改进

（3）B+的特点

• 一个结点，可以存储N个key,N个Key划分出N个区间（B树是N+1个）；
• 每个结点的Key值，都会在子节点中存在（并且是子节点的最大值）；
• B+树的叶子结点首位相连，类似链表；
• 整个树的所有数据都包含在叶子结点中。所以非叶子结点的Key最终都会出现在叶子结点中。
• B+树还有一个显著特点，他的每一个叶子结点都关联这一个记录，这个记录就是我们实际数据库里每一个表里的每一行记录。

（4）B+树的优势

• 当前一个结点保存更多的key,最终树的高度是相对更矮的（B树也有这个优点），查询的时候可以减少IO的访问次数。
• 所有的查询最终都会落在叶子结点上（查询任何一个数据，经过的IO访问次数，是一样的。）稳定是很重要的，稳定可以让程序员对程序的运行效率有更准确的评估。
• B+树的所有叶子结点都用链表进行了链接(并且是一个双向链表)，这样就支持更直接的范围查询了。同时代码也更好写了。
• 由于数据都在叶子结点上，非叶子结点只存了key，所以我们就可以将叶子结点的一部分进行缓存（B树非叶子结点是存记录的），这样可以进一步减少IO次数。

（5）示例

mysql> desc student;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| sn         | int(11)     | YES  | UNI | NULL    |                |
| name       | varchar(20) | YES  |     | unkown  |                |
| qq_mail    | varchar(20) | YES  |     | NULL    |                |
| classes_id | int(11)     | YES  | MUL | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

• 表的数据还是按照id为主键，构建出B+树，通过叶子结点组织所有的数据行。其次针对name如果我又创建了一个索引，那么此时在底层是又构建了一个B+树，这个B+树的叶子结点不再存储这一行的完整数据，而是存主键id。
• 此时，如果根据name来查询，查到叶子结点得到的只是主键Id,还需要再次通过主键id主键的B+树里再查一次。
• 上述过程称之为回表，是mysql自己完成的，用户感知不到。

【2】B+树

（1）什么是B+树

• B+树是一种平衡查找树的数据结构，被广泛应用于数据库系统和文件系统中的索引结构。
• 它相比于其他树状结构（如二叉搜索树）具有更高的查询性能和更好的存储利用率。

（2）B+树的特点

• 平衡性：B+树是一棵平衡树，即每个节点的左子树和右子树的高度差不超过1。这样可以保持树的高度相对较小，使得查找操作的复杂度保持在O(log n)级别。
• 多路搜索：B+树的每个内部节点可以拥有多个子节点，这使得B+树能够在每个节点上存储更多的关键字，提高了存储的效率。
• 顺序访问性：B+树的所有叶子节点按照关键字的大小顺序形成一个有序链表，可以支持范围查询和排序等操作。同时，由于内部节点只包含关键字而不包含具体数据，可以在内存中存放更多的节点，从而提高了缓存的命中率。
• 存储利用率高：B+树的内部节点不存储数据，只存储关键字，因此相比于其他树结构（如B树），B+树可以存储更多的关键字。这使得B+树在磁盘或内存中的存储利用率更高。

（3）B+树的应用

• B+树的应用主要包括数据库系统和文件系统中的索引结构。
• 在数据库系统中，B+树常被用作数据库表的索引结构，能够高效地支持查找、插入、删除等操作。
• 在文件系统中，B+树常被用来维护文件的目录结构，可以快速定位和访问文件。

（4）小结

• 总之，B+树是一种高效的数据结构，具有平衡性、多路搜索、顺序访问性和存储利用率高等特点，适用于需要高效查询和排序的场景。
• 只有叶子结点存放真实数据，根和树枝节点存的仅仅是虚拟数据
• 查询次数由树的层级决定，层级越低次数越少

【八】聚集索引(主键索引)

【1】什么是聚集索引

• 聚集索引（Clustered Index）是关系型数据库中的一种索引类型，它决定了表中数据的物理存储顺序。
• 在一个表中，只能有一个聚集索引。
• 聚集索引对表进行了重新组织，使得数据按照聚集索引的键值顺序存储在磁盘上。
• 由于聚集索引决定了数据的物理存储顺序，因此通过聚集索引可以快速地找到特定范围内的数据
• MySQL的聚簇索引是基于B+树的数据结构实现的，它会把数据存储在索引的叶子节点上，叶子节点之间按顺序链接，使得按主键进行搜索时速度最快。
• 如果没有主键，如果按主键搜索，速度是最快的。

【2】聚集索引的特点

• 数据的逻辑顺序和物理顺序是一致的，通过聚集索引可以直接访问特定行，因此聚集索引的查询速度很快。
• 聚集索引的键值必须是唯一的，不允许重复值存在。
• 当表中的数据发生插入、删除或更新操作时，聚集索引需要进行相应的调整以保持数据的有序性，这可能会对性能产生一定影响。
• 如果表中没有定义聚集索引，那么表的数据存储顺序将按照物理地址来存储。
• 表不建立主键，也会有个隐藏字段是主键，是主键索引
• 主键索引对于按照主键进行查询的性能非常高。

【3】语法

• 数据列不允许重复，不允许为NULL，一个表只能有一个主键。

ALTER TABLE table_name ADD PRIMARY KEY (column);

【4】小结

• 聚集索引适用于经常需要按照某个特定的列或列组进行查询的情况。
  • 例如，在一个订单表中，如果根据订单号频繁地进行查询，那么可以将订单号作为聚集索引，这样可以提高订单查询的效率。
• 需要注意的是，聚集索引的选择需要根据具体的业务需求和数据访问模式进行权衡。
  • 在一些特定情况下，聚集索引可能并不适合或者不符合最佳实践，此时可以考虑使用非聚集索引等其他索引类型。

【九】辅助索引(普通索引)

【1】什么是辅助索引

• 辅助索引（Non-clustered Index）是关系型数据库中的一种索引类型，与聚集索引相对应。
• 辅助索引的键值并不决定数据在磁盘上的物理存储顺序，而是创建一个额外的数据结构来帮助加快查询速度。一个表可以有多个辅助索引。

【2】辅助索引的特点

• 辅助索引的建立并不直接影响数据的物理存储顺序，它是基于聚集索引（如果存在）或表本身的物理存储结构进行构建的。
• 辅助索引的键值可以重复，允许在同一个键值上有多行数据。
• 辅助索引存储着键值和指向实际数据行的指针，通过辅助索引可以快速定位到具体的数据行。
• 辅助索引的维护对数据的插入、删除、更新操作的性能有一定影响，因为每次操作都需要更新辅助索引。

【3】语法

• MySQL中的基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和NULL值。
• 一个表允许多个列创建普通索引。

ALTER TABLE table_name ADD INDEX index_name (column);

【4】小结

• 辅助索引适用于类似于搜索或排序等需要频繁进行的查询操作。
  • 当查询涉及到的列不是聚集索引的键值时，辅助索引可以提供更高效的查询性能。
  • 例如，在一个学生信息表中，如果经常按照学生的姓名进行查询，那么可以在姓名列上创建一个辅助索引，这样可以加速姓名查询的速度。
• 需要注意的是，在设计数据库时，应根据实际情况综合考虑聚集索引和辅助索引的选择。
  • 聚集索引适用于经常以特定顺序扫描整个表或者频繁按照某个列进行范围查找的场景，而辅助索引则适用于单列或多列的等值或范围查找场景。
  • 理想情况下，辅助索引应当能够满足大部分查询需求，从而避免全表扫描。

【十】唯一索引（unique）

【1】什么是唯一索引

• 唯一索引是指该索引的所有值都是唯一的，不允许出现重复值。

【2】语法

• MySQL中可以通过以下语法创建唯一索引

ALTER TABLE table_name ADD UNIQUE KEY index_name (column);

【3】注意事项

• 与普通索引不同的是，如果尝试向包含唯一索引的列中插入重复的值，则会引发错误。
• 唯一索引可以用于确保数据的一致性和完整性，并且可以帮助提高查询性能。

【十一】组合索引(联合索引)

【1】什么是组合索引

• 组合索引是一种由两个或更多列组成的索引。
• 当查询涉及多个列时，组合索引可以大大提高查询性能。

【2】语法

• MySQL中可以通过以下语法创建组合索引

ALTER TABLE table_name ADD INDEX index_name (column1, column2, ...);

• 需要注意的是，组合索引中的列顺序很重要。
• 在执行查询时，MySQL会首先使用最左的列进行匹配，然后逐步向右扩展。
• 因此，应该将最常用的查询条件放在组合索引的前面。

【3】缺点

• 虽然组合索引可以提高查询性能，但是也存在一些缺点。
• 例如，如果添加了不需要的列，或者删除了不需要的列，可能会导致组合索引变得无效。
• 此外，如果创建了太多的组合索引，也可能会增加索引维护的成本。
• 因此，在创建组合索引时需要谨慎考虑。

【十二】全文索引

【1】什么是全文索引

• 全文索引是一种特殊的索引，它可以用来存储和检索文本数据。
• 全文索引可以包含单词、短语和其他类型的文本内容，并支持模糊匹配和近似匹配。

【2】语法

• MySQL中可以通过以下语法创建全文索引

CREATE FULLTEXT INDEX index_name ON table_name (column);

【3】注意事项

• 需要注意的是，只有MyISAM和InnoDB存储引擎支持全文索引。
• 此外，创建全文索引可能会增加索引维护的成本，并且可能会降低其他类型的查询性能。
• 因此，在创建全文索引时需要权衡其利弊。

【十三】前缀索引

【1】什么是前缀索引

• 前缀索引是一种特殊的索引，它只存储索引列的一部分，而不是完整的值。
• 前缀索引通常用于处理非常大的列，例如IP地址或邮政编码。
• 在这种情况下，全列索引可能会消耗大量的存储空间，并且可能会导致查询性能下降。
• 前缀索引可以大大减少索引大小，并且可以更快地执行范围查询。

【2】语法

• MySQL中可以通过以下语法创建前缀索引

ALTER TABLE table_name ADD INDEX index_name (column(length));

• 其中，length参数表示要保留的字符数。

【3】示例

• 例如，如果我们有一个邮政编码列，并且我们只需要后两位数字进行索引，我们可以创建以下前缀索引：

ALTER TABLE table_name ADD INDEX idx_postcode(postcode(2));

• 在这个例子中，索引只会包含邮政编码的最后两位数字。
• 当我们执行范围查询时，MySQL可以直接使用前缀索引来缩小搜索范围，从而提高查询性能。

【】覆盖索引

【1】什么是覆盖索引

• 覆盖索引（Covering Index）是指在关系型数据库中，创建一个辅助索引包含了查询所需要的所有列，从而避免了对主表进行额外的数据检索操作。
• 只在辅助索引的叶子节点中就已经找到了所有我们想要的数据

select name from user where name='dream';

【2】覆盖索引的特点

• 覆盖索引包含了查询所需要的所有列，无需再通过辅助索引定位到实际的数据行。这样，数据库可以直接从索引中获取到查询所需的数据，而不需要再访问主表，提高了查询性能。
• 覆盖索引减少了磁盘I/O操作的次数。相比于使用辅助索引定位到数据行后再读取数据，覆盖索引可以直接从索引中读取所需数据，减少了磁盘读取的次数，从而加快了查询速度。
• 覆盖索引在一些特定的查询场景下非常有效，特别是当查询只需要返回索引包含的列时。例如，如果需要查询一个学生表中的学生姓名和年龄，而这两个列在一个名为"student_idx"的辅助索引上都有覆盖，那么查询时就可以直接使用该覆盖索引，而无需再去读取主表中的其他列，从而提高查询效率。

【3】小结

• 根据具体的查询需求，我们可以通过创建适当的覆盖索引来提高查询性能。
  • 需要考虑的因素包括查询的列、查询的条件、表的大小以及对数据插入和更新操作的影响。
  • 创建过多的覆盖索引可能增加了存储空间的占用，并对数据的插入、删除和更新操作性能产生负面影响。
• 因此，在创建覆盖索引时需要权衡索引的选择，避免过度索引化的情况。

【】非覆盖索引

【1】什么是非覆盖索引

• 非覆盖索引是指在数据库中的索引结构中，存储了对应的键值（例如：主键、唯一键、普通索引）以及相应的行的定位信息（如物理存储位置或行标识），但没有包含查询所需的其他列数据。
• 当执行一个查询时，使用非覆盖索引需要通过索引定位到对应的行，并进一步访问主表来获取所需的列数据。
• 与覆盖索引相比，非覆盖索引需要进行额外的查询操作来检索主表中的其他列数据，因此在某些情况下可能会导致性能下降。
• 虽然查询的时候命中了索引字段name，但是要查的是age字段，所以还需要利用主键才去查找

select age from user where name='dream';

【2】非覆盖索引的适用场景和优势

• 提供高效的筛选能力：非覆盖索引可以根据所建索引的键值快速定位到满足查询条件的行，这可以减少需要扫描的数据量，提供高效的筛选能力。
• 减少磁盘I/O操作：虽然非覆盖索引需要额外的访问主表来获取数据，但是相对于全表扫描或者需要访问大量数据页的情况，非覆盖索引仍然可以减少磁盘的I/O操作次数，从而提升查询性能。
• 降低内存消耗：非覆盖索引通常比较小，占用的内存空间相对较少。这对于有限的内存资源来说，可以更好地利用内存空间。

【3】注意事项

• 列选择性：索引的列选择性是指该列上不同值的数量与总行数之间的比率。当列具有较高的选择性时，非覆盖索引的效果通常会更好。因为高选择性的列能够更快地筛选出满足查询条件的行。
• 查询性能评估：在设计索引时，需要仔细评估查询的频率和性能需求。如果某个查询经常执行，而且对性能要求很高，那么建立合适的非覆盖索引可以提升查询效率。

【4】小结

• 总结而言，非覆盖索引是一种常见的索引类型，在特定场景下可以提供高效的筛选能力和降低磁盘I/O操作的优势。
• 但在选择索引类型时，需要综合考虑查询需求、列选择性以及数据表大小等因素，以选择最合适的索引优化方案。