MySQL进阶学习--day03

1. 介绍

索引是一种对数据库表中的一列或多列进行排序的数据结构，它们可以大大加快数据的检索速度。索引类似于书中的目录，它使数据库系统能够更快地找到需要访问的数据行。

索引可以通过在表的一列或多列上创建，以加快对这些列的查询和排序操作。它们是数据库性能优化的重要手段之一。

索引有多种类型，包括：

1. 主键索引：用于唯一标识表中的每一行数据，通常会自动创建在主键列上，保证数据的唯一性和快速查找。

2. 唯一索引：要求列中的数据是唯一的，但允许有空值。它可以加快对该列的查找操作。

3. 聚集索引：在表中数据物理存储上进行排列，通常是根据主键进行排序。

4. 非聚集索引：不影响表中数据的物理存储顺序，通过指向数据行指针的方式来加速查询。

5. 复合索引：创建在多个列上，可以优化多个列的查询操作。

索引的使用可以大大提高数据库的查询性能，加快数据的检索速度。但是，索引也会占用额外的存储空间，并在插入、更新和删除操作时增加额外的开销。因此，在创建索引时需要权衡考虑，避免过度索引导致性能下降。同时，索引需要定期维护和优化，以确保数据库系统的高效运行。

2. 演示

我们以下表为例子来进行说明：

假如我们要执行的SQL语句为 ： select * from user where age = 45;

2.1 无索引

在无索引情况下，就需要从第一行开始扫描，一直扫描到最后一行，我们称之为全表扫描，性能很低。以下为无索引的情况：

全表扫描是数据库执行查询时的一种操作，它指的是当数据库系统没有使用索引或其他优化方式进行数据检索时，而是循环遍历整个表的每一行来满足查询条件。

当数据库查询语句中的条件无法利用索引或者没有合适的索引可用时，系统就会执行全表扫描。这意味着数据库系统会逐行读取整张表的数据，对每一行应用查询条件，并返回满足条件的数据行。全表扫描通常发生在数据量较大、没有适当索引、或者查询条件无法被索引覆盖的情况下。

全表扫描的主要特点包括：

• 性能较低：全表扫描可能需要大量的磁盘I/O和CPU资源，因为它需要读取整个表的数据进行比对，所以通常会比较慢。

• 不利于大型数据表：在大型数据表上执行全表扫描可能会导致性能问题，因为遍历整张表的数据需要大量的时间和资源。

• 适用于小型数据表或特定情况：在某些情况下，全表扫描可能是合适的。比如，当查询返回的数据量很小，或者表中的数据量比较少时，全表扫描的性能损耗可能不是很明显。

尽管全表扫描可能会影响数据库性能，但在一些情况下，它是无法避免的。因此，合理的索引设计和查询优化是降低全表扫描影响的重要手段。

2.2 有索引

如果我们针对于这张表建立了索引，假设索引结构就是二叉树，那么也就意味着，会对age这个字段建立一个二叉树的索引结构。以下为有索引的情况：

此时我们在进行查询时，只需要扫描三次就可以找到数据了，极大的提高的查询的效率。

备注： 这里我们只是假设索引的结构是二叉树，介绍一下索引的大概原理，只是一个示意图，并不是索引的真实结构，索引的真实结构，后面会详细介绍。

3. 特点

索引在数据库中起着至关重要的作用，但它也有一些优缺点。以下是对索引的优点和缺点的详细介绍：

优点：

• 提高检索速度：索引可以加快数据库系统的数据检索速度，因为它允许数据库直接定位到所需数据的位置，而不需要扫描整个表。

• 支持唯一性和完整性约束：索引可以通过唯一性约束来确保数据的唯一性，避免出现重复的数据。它还可以用于加速对表中数据的更新和删除操作。

• 支持排序：索引可以按照指定的顺序对数据进行排序，从而提高按照特定顺序进行检索的效率。

• 提高连接性能：对于连接操作（如JOIN），索引可以大大提高查询的性能。

• 优化查询性能：通过合理的索引设计，可以优化查询语句的性能，减少数据库的负载。

缺点：

• 占用额外的存储空间：索引通常需要占用额外的存储空间，因为索引本身也是一种数据结构，需要存储在数据库中。

• 对更新操作的影响：当对表中的数据进行更新或删除操作时，索引也需要相应地进行更新，这可能会导致额外的开销。

• 对插入操作的影响：索引的存在可能会对插入操作的性能产生一定的影响，因为插入数据时需要更新索引。

• 维护成本高：创建、维护和删除索引需要消耗额外的计算资源和时间。

• 可能导致性能下降：过度使用索引或者使用不当可能会导致性能下降，因为索引也需要维护和更新。

综上所述，索引在提高数据检索效率的同时，也会带来一些额外的成本和影响。在设计和使用索引时，需要权衡各种因素，合理地使用索引可以提高数据库的性能，但过度使用索引或者使用不当可能会导致性能下降。