深入剖析数据库索引

性能对于许多面向消费者的产品来说至关重要，例如电子商务、支付系统、游戏、交通应用等。尽管现代数据库通过多种内部机制进行了优化以满足性能需求，但很大程度上还是依赖于应用程序开发者。毕竟，只有开发者才知道应用程序需要执行哪些查询。

处理关系型数据库的开发者都使用过或至少听说过索引，这是数据库世界中一个非常常见的概念。索引不仅可以提高查询速度，还可以优化数据库的整体性能。然而，最重要的是要理解什么需要建立索引，以及索引如何提高查询响应时间。要做到这一点，你需要充分了解你将如何查询数据库表。只有当你确切知道你的查询和数据访问模式是什么样的，才能创建合适的索引。

用简单的术语来说，索引通过使用不同的内存和磁盘数据结构，将搜索键映射到磁盘上相应的数据。索引的作用是通过减少需要搜索的记录数量来加快搜索速度。大多数关系型数据库使用B树或B+树作为索引的数据结构，这些树形结构能够有效地组织和检索大量数据。

通常，索引是在查询的WHERE子句中指定的列上创建的，因为数据库会基于这些列来检索和过滤表中的数据。索引的类型多种多样，包括主键索引、唯一索引、普通索引、全文索引和复合索引等，每种类型都有其特定的用途和优势。如果不创建索引，数据库就会扫描所有行，筛选出匹配的行并返回结果。对于包含数百万条记录的表来说，这种扫描操作可能需要很多秒，而这种高响应时间会使API和应用程序变得缓慢且难以使用。

值得注意的是，过度使用索引也可能导致写入操作变慢，因为每次插入或更新数据时，索引也需要更新。因此，在创建索引时需要权衡读取和写入操作的频率。此外，随着数据的插入、更新和删除，索引可能会变得碎片化或不平衡。定期维护索引（如重建或重组）可以保持其效率。

让我们来看一个例子——我们将使用MySQL，采用默认的InnoDB数据库引擎。

我们创建了一个名为index_demo的表：

CREATE TABLE index_demo ( 
    name VARCHAR(20) NOT NULL, 
    age INT, 
    pan_no VARCHAR(20), 
    phone_no VARCHAR(20) 
);

如何验证我们正在使用InnoDB引擎

运行以下命令：

SHOW TABLE STATUS WHERE name = 'index_demo' \G;

这个SQL命令用于显示名为index_demo表的详细状态信息。让我们深入解析一下：

SHOW TABLE STATUS 是MySQL的一个命令，用于显示关于数据库表的低级别信息。
WHERE name = 'index_demo' 子句指定我们只想查看名为index_demo的表的信息。
\G 是MySQL客户端的一个特殊格式化选项。它将结果垂直显示，每个字段占一行，使输出更易读，特别是当结果包含多列时。

mysql> SHOW TABLE STATUS WHERE name = 'index_demo' \G;
*************************** 1. row ***************************
           Name: index_demo
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2024-07-09 03:51:36
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: 
        Comment:

Engine 列在上面的截图中表示用于创建表的引擎。这里使用的是 InnoDB。

现在让我们在表中插入一些随机数据。我的表中有5行数据，数据如下：

INSERT INTO index_demo (name, age, pan_no, phone_no) VALUES
('John Doe', 28, 'ABCDE1234F', '9876543210'),
('Jane Smith', 35, 'FGHIJ5678K', '8765432109'),
('Alice Johnson', 42, 'KLMNO9012P', '7654321098'),
('Bob Williams', 31, 'QRSTU3456V', '6543210987'),
('Charlie Brown', 39, 'WXYZ7890A', '5432109876');

mysql> select * from index_demo;
+---------------+------+------------+------------+
| name          | age  | pan_no     | phone_no   |
+---------------+------+------------+------------+
| John Doe      |   28 | ABCDE1234F | 9876543210 |
| Jane Smith    |   35 | FGHIJ5678K | 8765432109 |
| Alice Johnson |   42 | KLMNO9012P | 7654321098 |
| Bob Williams  |   31 | QRSTU3456V | 6543210987 |
| Charlie Brown |   39 | WXYZ7890A  | 5432109876 |
+---------------+------+------------+------------+
5 rows in set (0.00 sec)

我到目前为止还没有在这个表上创建任何索引。让我们通过 SHOW INDEX 命令来验证这一点。它返回了0个结果。

mysql> show index from index_demo;
Empty set (0.00 sec)

此时，如果我们运行一个简单的 SELECT 查询，由于没有用户定义的索引，查询将扫描整个表以找出结果：

EXPLAIN SELECT * FROM index_demo WHERE name = 'alex';

EXPLAIN 命令展示了查询引擎计划如何执行查询。在上面的截图中，您可以看到 rows 列返回 5，而 possible_keys 返回 null。possible_keys 表示此查询可以使用的所有可用索引。key 列表示在所有可能的索引中，此查询实际将使用哪个索引。

这个解释清楚地说明了在没有索引的情况下，查询引擎是如何处理查询的：

rows 为 5 表示查询预计需要扫描的行数。在这种情况下，它等于表中的总行数，意味着进行了全表扫描。
possible_keys 为 null 意味着没有可用的索引可以用于优化这个查询。
key 也很可能为 null，表示实际上没有使用任何索引。

这种情况下，数据库必须扫描整个表来找到匹配的记录，这在小型表中可能不是问题，但在大型表中可能导致严重的性能问题。这正是索引可以带来显著改善的地方。

主键

上述查询非常低效。让我们来优化这个查询。我们将把 phone_no 列设为 PRIMARY KEY，假设在我们的系统中不存在两个用户使用相同的电话号码。在创建主键时，请考虑以下几点：

主键应该是应用程序中许多关键查询的一部分。
主键是一个约束，用于唯一标识表中的每一行。如果多个列组成主键，那么这种组合对于每一行都应该是唯一的。
主键应该是非空的。永远不要将可空字段作为主键。根据ANSI SQL标准，主键应该能够相互比较，您应该能够确定一个特定行的主键列值是大于、小于还是等于其他行的主键值。由于在SQL标准中 NULL 表示未定义的值，您无法确定性地将 NULL 与任何其他值进行比较，因此逻辑上不允许使用 NULL。
理想的主键类型应该是数字，如 INT 或 BIGINT，因为整数比较更快，所以遍历索引会非常迅速。

我们经常在表中定义一个 id 字段作为 AUTO INCREMENT，并将其用作主键，但主键的选择取决于开发者。

如果你没有自己创建任何主键会发生什么？

创建主键并不是强制性的。如果在创建表时没有定义主键，InnoDB 会隐式创建一个内部的聚簇索引。这种隐式创建的主键通常是一个 6 字节的行 ID。这在你没有显式指定主键的情况下，可以确保 InnoDB 的正常工作。因为根据设计，InnoDB 每个表必须有一个主键。所以，一旦你稍后为该表创建了主键，InnoDB 会删除之前自动定义的主键。

由于我们目前没有定义任何主键，让我们看看 InnoDB 默认为我们创建了什么：

SHOW EXTENDED INDEX FROM index_demo;

EXTENDED 选项显示了所有用户不可用但由 MySQL 完全管理的索引。

在这里，我们可以看到 MySQL 已经定义了一个复合索引（我们稍后会讨论复合索引），包含 DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR 以及表中定义的所有列。在没有用户定义主键的情况下，这个索引被用来唯一地查找记录。

关键字和索引之间的区别是什么？

虽然术语“关键字”（key）和“索引”（index）可以互换使用，但它们的含义有所不同。key意味着对列行为施加的一种约束。在这种情况下，主键是一种非空字段的约束，它唯一地标识每一行。另一方面，index是一种特殊的数据结构，有助于在表中进行数据搜索。

关键字（Key） ：关键字是一种约束，通常用于确保数据完整性和唯一性。主键（Primary Key）是一种常见的关键字约束，它确保列中的值唯一且非空。例如，在 phone_no 列上添加主键意味着每个电话号码都是唯一的，且不能为空。

索引（Index） ：索引是一种数据结构，旨在加速数据库查询操作。它类似于书中的索引，通过建立某些列的快速查找路径，减少数据搜索时间。索引不仅用于主键，还可以用于其他列以提高查询效率。

我们现在在 phone_no 列上创建主键，并检查创建的索引：

ALTER TABLE index_demo ADD PRIMARY KEY (phone_no);
SHOW INDEXES FROM index_demo;

需要注意的是，不能使用 CREATE INDEX 来创建主键，必须使用 ALTER TABLE。

在上面的截图中，我们看到在 phone_no 列上创建了一个主键。以下是对截图中各列的描述：

Table：创建索引的表。
Non_unique：如果值为 1，表示索引不是唯一的；如果值为 0，表示索引是唯一的。
Key_name：创建的索引的名称。在 MySQL 中，主键的名称总是 PRIMARY，无论你在创建索引时是否提供了索引名称。
Seq_in_index：索引中列的顺序号。如果索引包含多个列，顺序号将根据创建索引时列的顺序进行分配。顺序号从 1 开始。
Collation：列在索引中的排序方式。A 表示升序，D 表示降序，NULL 表示未排序。
Cardinality：索引中唯一值的估计数量。更高的基数意味着查询优化器选择该索引用于查询的可能性更高。
Sub_part：索引前缀。如果整个列被索引，则为 NULL。如果列是部分索引，则显示已索引的字节数。我们稍后会定义部分索引。
Packed：表示关键字的打包方式；如果未打包，则为 NULL。
Null：如果列可能包含 NULL 值，则显示 YES；如果不包含 NULL 值，则为空。
Index_type：指示用于此索引的索引数据结构。一些可能的候选者包括 BTREE、HASH、RTREE 或 FULLTEXT。
Comment：有关索引的信息，不在其自己的列中描述。
Index_comment：使用 COMMENT 属性创建索引时为索引指定的注释。

现在，让我们看看这个索引是否减少了在查询的 WHERE 子句中给定 phone_no 时需要搜索的行数。

EXPLAIN SELECT * FROM index_demo WHERE phone_no = '9876543210';

在上图中，请注意 rows 列仅返回了 1，possible_keys 和 key 都返回了 PRIMARY。这实际上意味着使用名为 PRIMARY 的主索引（当你创建主键时，名称会自动分配），查询优化器直接找到记录并获取它。这非常高效。

通过使用主索引 PRIMARY，查询优化器能够直接定位到所需的记录，而不必遍历整个表。这显著提高了查询的效率。这种效率提升在处理大量数据时尤为明显。

聚簇索引

聚簇索引与数据一起位于同一个表空间或同一个磁盘文件中。你可以认为聚簇索引是一个 B-Tree 索引，其叶节点是磁盘上的实际数据块，因为索引和数据共存于一起。这种索引根据索引键的逻辑顺序在磁盘上物理地组织数据。在聚簇索引中，数据行的物理顺序与索引的逻辑顺序相同。这意味着数据库在读取索引时，可以直接访问数据行，而不需要进行额外的查找操作。聚簇索引实际上重排了表中的数据行，以匹配索引的顺序。

B-Tree 索引 ：B-Tree（平衡树）是一种广泛使用的数据结构，特别适用于数据库索引。B-Tree 索引通过分层结构组织数据，使得查找、插入和删除操作都能在对数时间内完成。在聚簇索引中，B-Tree 的叶节点包含了实际的数据行，这使得读取数据更加高效。

物理数据组织

从物理上看，数据在磁盘上组织在成千上万个磁盘块或数据块中。对于聚簇索引来说，并不要求所有的磁盘块连续存储。物理数据块总是会根据操作系统的需要在这里或那里移动。数据库系统没有绝对的控制权来管理物理数据空间，但在数据块内部，记录可以按索引键的逻辑顺序存储或管理。以下简化的示意图对此进行了说明：

黄色的大矩形表示一个磁盘块/数据块
蓝色的矩形表示存储在该块内的行数据
底部区域表示块的索引，其中红色的小矩形按照特定键的顺序排序。这些小块实际上是指向记录偏移量的指针。

记录在磁盘块中以任意顺序存储。每当添加新记录时，它们会被添加到下一个可用空间。每当现有记录更新时，操作系统会决定该记录是否仍能适应当前位置，或者是否需要为该记录分配新位置。

物理数据块的管理

记录的位置完全由操作系统处理，任何两条记录之间的顺序不存在确定的关系。为了按照键的逻辑顺序获取记录，磁盘页在底部包含一个索引部分，索引部分包含按照键顺序排列的偏移指针列表。每当记录被更改或创建时，索引都会进行调整。

通过这种方式，你实际上不需要关心实际按照某种顺序组织物理记录，而是维护一个按顺序排列的小索引部分，从而使得获取或维护记录变得非常容易。

聚簇索引的优势

这种相关数据的排序或共址使得聚簇索引更快。当从磁盘获取数据时，系统会读取包含数据的整个块，因为我们的磁盘 I/O 系统以块为单位写入和读取数据。因此，在范围查询的情况下，很可能相关数据会被缓存在内存中。假设你执行以下查询：

SELECT * FROM index_demo WHERE phone_no > '9010000000' AND phone_no < '9020000000';

当查询执行时，一个数据块被加载到内存中。假设这个数据块包含的 phone_no 范围是从 9010000000 到 9030000000。因此，你在查询中请求的范围只是该块中数据的一个子集。如果你现在执行下一个查询来获取所有在范围内的电话号码，例如从 9015000000 到 9019000000，你不需要从磁盘再读取任何块。所有的数据都可以在当前的数据块中找到，从而聚簇索引通过在同一个数据块中尽可能多地共址相关数据来减少磁盘 I/O 操作。这种减少的磁盘 I/O 操作提高了性能。

所以，如果你有一个精心设计的主键，并且你的查询是基于主键的，那么性能将会非常快。

聚簇索引使得查询非常高效。因为数据按照索引键的顺序存储，所以一个数据块中包含的记录范围很可能覆盖查询条件的范围。这意味着一次磁盘读取可以满足多个查询请求。

数据缓存和内存利用：当一个数据块被加载到内存中时，该块中的所有数据都被缓存。如果后续的查询请求的数据在这个块的范围内，数据库系统可以直接从内存中获取数据，而不需要再次进行磁盘 I/O 操作。这大大提高了查询速度。

聚簇索引的限制

由于聚簇索引决定了数据在磁盘上的物理存储顺序，因此每个表只能有一个聚簇索引。这是因为一个表的数据行只能按照一种顺序存储。

主键与聚簇索引的关系

你不能使用 InnoDB 在 MySQL 中手动创建聚簇索引。MySQL 会为你选择它。但是 MySQL 是如何选择的呢？以下是 MySQL 文档中的摘录：

当你在表上定义一个 PRIMARY KEY 时，InnoDB 会将其用作聚簇索引。为你创建的每个表定义一个主键。如果没有逻辑上唯一且非空的列或列集合，请添加一个新的自动递增列，其值将自动填充。
如果你没有为表定义 PRIMARY KEY，MySQL 会找到第一个所有键列均为 NOT NULL 的 UNIQUE 索引，并将其用作聚簇索引。
如果表没有 PRIMARY KEY 或合适的 UNIQUE 索引，InnoDB 会在包含行 ID 值的合成列上内部生成一个名为 GEN_CLUST_INDEX 的隐藏聚簇索引。行按 InnoDB 分配给这些行的 ID 排序。行 ID 是一个 6 字节的字段，随着新行的插入单调递增。因此，按行 ID 排序的行在物理上按插入顺序排列。

简而言之，MySQL InnoDB 引擎实际上将主索引管理为聚簇索引以提高性能，因此主键和磁盘上的实际记录是聚簇在一起的。在没有主键或唯一索引的情况下，InnoDB 会创建一个内部隐藏的聚簇索引。这种情况下，InnoDB 使用一个包含行 ID 值的合成列，并按插入顺序对行进行排序。这种排序方式虽然不会显式出现在表结构中，但仍然会影响数据的物理存储顺序。

结构化主键（聚簇）索引

索引通常作为 B+ 树在磁盘和内存中维护，任何索引都存储在磁盘上的块中。这些块称为索引块。索引块中的条目始终按照索引或搜索键排序。索引的叶子块包含行定位器。对于主键索引，行定位器指向数据块在磁盘上对应的物理位置的虚拟地址，这些数据块中的行按照索引键排序。数据库系统使用这些地址来直接访问或定位数据块，从而实现快速的数据检索和访问。

在下面的图示中，左侧的矩形表示叶子级别的索引块，右侧的矩形表示数据块。逻辑上看，数据块按照排序顺序排列，但如前所述，实际的物理位置可能散布在各处。

索引块（Leaf Level Index Blocks）：这些块包含索引的叶子节点，每个条目按索引键排序，每个叶子块都指向一个或多个数据块。
数据块（Data Blocks）：这些块包含实际的数据记录。逻辑上，数据记录按照主键的顺序排列，但实际物理存储位置可能分散在磁盘的不同位置。

能否在非主键列上创建主索引？

虽然MySQL会在主键列上自动创建主索引，但实际上在某些数据库系统中，可以在非主键列上创建主索引。在MySQL中，主索引与主键紧密关联，不典型情况下不会在非主键列上创建主索引。然而，在其他支持在非主键列上创建聚簇索引的数据库（如某些SQL Server版本）中，您可以明确地在任何列上定义聚簇索引，而不仅仅是主键。

主键列**：当在主键列上创建主索引时，索引中的所有条目都是唯一的。这种唯一性确保每一行都能通过主键值唯一地标识。**
非主键列**：如果在非主键列上创建主索引（聚簇索引），索引中的条目可能不是唯一的。这意味着多个行可能具有相同的索引值，导致索引中存在重复的键条目。

是否可以删除一个主键？

当然可以删除一个主键。当您删除一个主键时，相关的聚簇索引以及该列的唯一性属性都会丢失。

ALTER TABLE `index_demo` DROP PRIMARY KEY;

如果主键不存在，则会收到以下错误信息：

"ERROR 1091 (42000): Can't DROP 'PRIMARY'; check that column/key exists"

主索引的优势

数据局部性：主索引基于范围查询非常高效。由于主索引是聚簇的，记录在物理上有序，因此从磁盘读取的数据块很可能包含查询所需的所有数据。因此，主索引提供了数据的局部性。主索引确保相关数据在物理上相邻存储，这对于范围查询尤其有利。当查询基于主键时，数据库可以直接定位和读取所需数据，从而大大提高了查询的速度。
查询性能优化：任何能够利用主键的查询都非常快速。主索引的设计确保了数据的快速访问和检索，特别是在处理大量数据时效果更加显著。

主索引的缺点

DML 操作对性能的影响：主索引包含了通过虚拟地址空间直接引用数据块地址的信息，而磁盘块是按照索引键的顺序进行物理排序的。因此，每当操作系统由于诸如插入、更新或删除（DML）操作而进行磁盘页拆分时，主索引也需要进行相应的更新。这意味着DML操作会对主索引的性能产生一定的压力。
索引更新开销：随着数据的不断插入、更新和删除，主索引需要频繁地调整和更新，以保持索引的有序性和正确性。这些操作可能会导致索引的碎片化，进而影响到数据库的整体性能。

次要索引

除聚簇索引外的任何索引都称为次要索引。与主索引不同，次要索引不影响物理存储位置。

何时需要次要索引？

在您的应用程序中，可能有多种情况下不使用主键查询数据库。例如，假设电话号码（phone_no）是主键，但您可能需要使用身份证号（pan_no）或姓名（name）来查询数据库。在这种情况下，如果这些查询的频率非常高，就需要在这些列上创建次要索引。

次要索引提供了在非主键列上快速定位和检索数据的能力。通过在经常被查询的列上创建次要索引，可以显著提高查询的性能和响应速度，尤其是针对非主键查询的场景。

如何在MySQL中创建次要索引？

要在MySQL中创建次要索引，可以使用以下命令在 index_demo 表的 name 列上创建一个次要索引。

CREATE INDEX secondary_idx_1 ON index_demo (name);

创建次要索引步骤说明：

CREATE INDEX 语句: 使用 CREATE INDEX 命令。
索引名称: 在 ON 关键字之后指定要创建的索引的名称，例如 secondary_idx_1。
表名与列名: 指定索引要创建在哪个表的哪个列上，例如 (name) 表示在 index_demo 表的 name 列上创建索引。

次要索引的结构

在下面的图示中，红色矩形代表次要索引块。次要索引同样是在 B+ 树中进行维护，并且按照创建索引时的键进行排序。叶子节点包含了对应主索引中数据的键的副本。

因此，可以理解为次要索引有对主键地址的引用，尽管实际上并非如此。通过次要索引检索数据意味着您需要遍历两棵 B+ 树 —— 一棵是次要索引的 B+ 树本身，另一棵是主索引的 B+ 树。

B+ 树结构：次要索引和主索引都是基于 B+ 树实现的，这种树结构有助于快速的数据检索和范围查询。
数据定位：次要索引的叶子节点包含对应数据在主索引中的主键信息，而非直接的物理地址引用。
检索过程：通过次要索引检索数据需要先在次要索引 B+ 树中定位到对应的键，然后使用主索引 B+ 树中的主键信息来获取实际数据。

次要索引的优势

灵活的查询选项：次要索引提供了在非主键列上快速定位和检索数据的能力。通过在经常被查询的列上创建次要索引，可以显著提高查询的性能和响应速度，特别是在复杂查询场景下。
多样化的查询需求：在逻辑上，您可以根据需要创建任意多个次要索引。然而，实际上需要多少个索引则需要认真考虑，因为每个索引都会带来一定的性能开销。在现实应用中，您可能需要根据多种不同的查询需求来优化数据库的性能。通过创建适当的次要索引，可以支持多种查询模式，包括范围查询、排序和连接操作，从而更好地满足应用程序的功能需求。
减少数据扫描：次要索引可以减少数据扫描范围，从而加快查询的执行速度。通过索引的使用，数据库可以更有效地定位和访问所需的数据行，而无需全表扫描。

次要索引的缺点

使用类似 DELETE 和 INSERT 的数据操作（DML）时，次要索引也需要进行更新，以便删除或插入主键列的副本。在这种情况下，存在大量次要索引可能会导致以下问题：

管理和维护成本：每个次要索引都需要额外的存储空间和管理成本。随着数据库中次要索引的增加，管理和维护索引的复杂性也会显著增加，特别是在大规模数据操作时。
存储效率问题：如果主键非常大，例如 URL 类型的数据，由于次要索引包含主键列值的副本，会导致存储效率低下。每个次要索引都需要存储主键列值的副本，这对于大型主键来说可能会造成存储空间的浪费。
复合效应 ：多个次要索引意味着主键列值的多个副本存储在数据库中，加重了存储负担。由于主键本身也存储这些键值，因此在存储成本方面会产生相当大的综合效应。

删除主键索引前的考虑事项

在 MySQL 中，您可以通过删除主键来删除主键索引。我们已经知道，次要索引依赖于主键索引。因此，如果删除主键索引，所有次要索引都必须更新以包含新的主键索引键的副本，MySQL 会自动调整这些过程。

当存在多个次要索引时，这个过程是昂贵的。此外，其他表可能具有对主键的外键引用，因此在删除主键之前，您需要删除这些外键引用。

当删除主键时，MySQL 会在内部自动创建另一个主键，这是一个昂贵的操作。

次要索引的更新：删除主键索引会导致所有依赖于该主键的次要索引需要更新，这可能会影响数据库的性能和资源消耗。
外键约束：确保删除主键索引之前，处理所有依赖于该主键的外键约束，以保证数据的引用完整性。
系统开销：由于 MySQL 在删除主键后会自动创建新的主键，因此这种操作会增加系统的开销和资源使用。

UNIQUE Key Index（唯一键索引）

与主键类似，唯一键索引也可以唯一标识记录，但有一个区别——唯一键列可以包含空值（null）。

在 MySQL 中，与其他数据库服务器不同，唯一键列可以包含尽可能多的空值。在 SQL 标准中，null 表示未定义的值。因此，如果 MySQL 在唯一键列中包含一个 null 值，它必须假设所有 null 值都是相同的。

但从逻辑上讲，这并不正确，因为 null 表示未定义 —— 未定义的值之间不能进行比较，这是 null 的特性。由于 MySQL 无法确定所有 null 值是否相同，它允许在列中包含多个 null 值。

以下命令展示了如何在 MySQL 中创建一个唯一键索引：

CREATE UNIQUE INDEX unique_idx_1 ON index_demo (pan_no);

注意事项

空值处理：MySQL 中的唯一键允许包含多个空值，这与其他数据库的行为不同，需特别注意处理空值的情况。
逻辑一致性：理解 null 的语义是非常重要的，null 表示未定义的值，不能假设所有 null 值都相同。

唯一键索引提供了一种在数据库中确保列值唯一性的方法，但需要注意其与 null 值的处理方式及其影响。

Composite Index（复合索引）

MySQL 允许您在多个列上定义索引，最多可以是 16 列。这种索引称为多列索引、复合索引或组合索引。

假设我们在 4 列上定义了一个索引 — col1、col2、col3、col4。使用复合索引，我们可以在 col1、(col1, col2)、(col1, col2, col3)、(col1, col2, col3, col4) 上进行搜索。因此，可以使用索引列的任何左侧前缀，但不能省略中间的列，例如 (col1, col3) 或 (col1, col2, col4)，或仅使用 col3 或 col4 等。这些组合是无效的。

以下命令在我们的表中创建了两个复合索引：

CREATE INDEX composite_index_1 ON index_demo (phone_no, name, age);
CREATE INDEX composite_index_2 ON index_demo (pan_no, name, age);

查询优化：如果您的查询包含多个列的 WHERE 子句，请按照复合索引的列顺序编写子句。这样的索引将有助于优化查询性能。在决定复合索引的列顺序时，可以分析系统的不同使用案例，并尝试确定对大多数案例最有利的列顺序。
联接和选择查询：复合索引也对 JOIN 和 SELECT 查询有帮助。例如，在以下的 SELECT * 查询中，将使用 composite_index_2。

当定义了多个索引时，MySQL 查询优化器会选择能够消除最多行或尽可能少扫描行的索引，以提高效率。

为什么使用复合索引？为什么不定义多个次要索引？

在 MySQL 中，每个查询仅使用一个表的一个索引，除了 UNION 操作（在 UNION 操作中，每个逻辑查询会分别执行，然后将结果合并）。因此，即使在查询中定义了多个列的多个索引，也不能保证这些索引都会被使用。

MySQL 维护了称为索引统计信息（index statistics）的东西，这些统计信息帮助 MySQL 推断系统中数据的外观。索引统计信息是一种概括性的描述，但基于这些元数据，MySQL 决定哪个索引适合当前的查询。

复合索引的优势

综合查询需求：复合索引允许您在多个列上定义一个索引，以支持多条件的查询优化。通过正确定义复合索引的列顺序，可以显著提高查询性能，因为MySQL可以更有效地使用这些索引来快速定位和检索数据。
避免索引冗余：定义多个独立的次要索引可能会导致索引冗余和性能损耗，因为每个查询只能使用一个索引。复合索引通过将多个列的查询条件组合成一个索引，避免了这种冗余。

复合索引的工作原理

复合索引将多个列的索引键连接在一起，并使用 B+ 树按排序顺序存储这些连接的键。当执行搜索时，您的搜索键的连接会与复合索引的连接键进行匹配。如果您的搜索键的顺序与复合索引列的顺序不匹配，则无法使用该索引。

在我们的示例中，对于以下记录，复合索引键通过连接 pan_no、name 和 age 形成 — HJKXS9086Wkousik28。

工作流程概述：

连接索引键：复合索引会将指定的多个列值连接成一个键。
排序存储：这些连接的键按照 B+ 树的排序规则在索引结构中存储。
匹配搜索：当执行查询时，系统将您的搜索条件连接为索引键，然后在 B+ 树中查找与之匹配的索引键。
顺序匹配：为了有效使用复合索引，查询中指定的搜索键顺序必须与复合索引定义的列顺序一致，这样系统才能利用索引进行快速定位和检索数据。

如何确定是否需要使用复合索引

在决定是否需要创建复合索引之前，请根据您的使用情况先分析您的查询模式。如果您发现某些字段经常一起出现在多个查询中，那么可能需要考虑创建一个复合索引。

分析查询模式

字段共现性：观察哪些字段在多个查询中经常同时出现。如果某些字段经常一起使用，考虑创建一个包含这些字段的复合索引。
单列索引与复合索引：如果您已经在 col1 上创建了索引，而现在又想创建一个 (col1, col2) 的复合索引，那么只创建复合索引就足够了。因为单列索引 col1 实际上已经包含在复合索引的左侧前缀中，可以直接利用复合索引。
基数（Cardinality）：如果在复合索引中使用的列具有高基数（即唯一值数量较多），这些列是复合索引的良好候选项。高基数的列可以帮助数据库更有效地定位和过滤数据。

示例考虑

考虑一个 (col1, col2) 的复合索引：

如果查询经常涉及到 col1，那么复合索引 (col1, col2) 可以直接满足这类查询的需求。
如果查询需要同时使用 col1 和 col2，那么复合索引 (col1, col2) 可以有效地优化这类联合条件查询的性能。

通过合理分析查询模式和字段共现性，结合基数的考量，可以更精确地确定是否需要创建复合索引以优化数据库查询性能。

覆盖索引

覆盖索引是一种特殊类型的复合索引，其中查询中涉及的所有列都存在于索引中。因此，查询优化器无需访问数据库获取数据，而是直接从索引中获取结果。

假设我们已经在 (pan_no, name, age) 上定义了一个复合索引。现在考虑以下查询：

SELECT age FROM index_demo WHERE pan_no = 'ABCDE1234F' AND name = 'John Doe';

查询中的 SELECT 和 WHERE 子句涉及的列都包含在复合索引中。因此，在这种情况下，我们可以直接从复合索引中获取 age 列的值。让我们看看对于这个查询，EXPLAIN 命令的输出是怎样的：

EXPLAIN FORMAT=JSON SELECT age FROM index_demo WHERE pan_no = 'ABCDE1234F' AND name = 'John Doe';

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "0.35"
    },
    "table": {
      "table_name": "index_demo",
      "access_type": "ref",
      "possible_keys": [
        "composite_index_2"
      ],
      "key": "composite_index_2",
      "used_key_parts": [
        "pan_no",
        "name"
      ],
      "key_length": "165",
      "ref": [
        "const",
        "const"
      ],
      "rows_examined_per_scan": 1,
      "rows_produced_per_join": 1,
      "filtered": "100.00",
      "using_index": true,
      "cost_info": {
        "read_cost": "0.25",
        "eval_cost": "0.10",
        "prefix_cost": "0.35",
        "data_read_per_join": "256"
      },
      "used_columns": [
        "name",
        "age",
        "pan_no"
      ]
    }
  }
}

在上述回复中，注意到有一个关键信息 — 使用 using_index 标志设置为 true，这表示覆盖索引已被用于回答查询。覆盖索引是数据库优化中的一项重要技术，特别是在生产环境中，它可以带来显著的性能优势，尤其是当查询符合条件时。

部分索引

我们已经知道索引可以在一定程度上加快查询速度，但也会增加存储空间的开销。索引越多，存储需求也就越大。我们已经在列名（name）上创建了一个名为 secondary_idx_1 的索引。由于 name 列可以包含任意长度的大值，而索引中的行定位器或行指针元数据也有其自身的大小。因此，索引可能会对存储和内存造成较高的负担。

在 MySQL 中，可以创建基于数据前几个字节的索引。例如，以下命令在 name 列的前四个字节上创建了一个索引。虽然这种方法可以在一定程度上减少内存开销，但由于在此示例中，前四个字节可能在许多名称中是共同的，因此索引无法消除多行。通常，这种前缀索引适用于 CHAR、VARCHAR、BINARY 和 VARBINARY 类型的列。

CREATE INDEX secondary_index_1 ON index_demo (name(4));

这种部分索引允许根据列的前缀来优化查询性能，尽管需要权衡存储和索引能够消除的行数。在设计数据库索引时，需要根据具体的数据模式和查询需求来决定是否使用部分索引，以达到最佳的性能和资源利用率。

索引背后的运作原理

当我们定义一个索引时，让我们再次运行 SHOW EXTENDED 命令来查看详情：

SHOW EXTENDED INDEXES FROM index_demo;

我们在 name 列上定义了 secondary_index_1，但 MySQL 却在 (name, phone_no) 上创建了一个复合索引，其中 phone_no 是主键列。我们创建了 secondary_index_2 在 age 上，MySQL 则创建了一个复合索引 (age, phone_no)。我们创建了 composite_index_2 在 (pan_no, name, age) 上，MySQL 则创建了一个复合索引 (pan_no, name, age, phone_no)。而 composite_index_1 已经包含了 phone_no 作为其一部分。

因此，无论我们创建何种索引，MySQL 在后台都会创建一个支持的复合索引，该复合索引反过来指向主键。这意味着主键在 MySQL 索引世界中是一个一等公民。这也证明了所有索引都是由主索引的副本支持的。

索引的一般指导原则

在决定是否使用索引以及如何使用索引时，需要考虑以下几点：

决定索引数量和类型：索引会消耗额外的内存空间，因此需要仔细考虑你需要多少个索引以及使用何种类型的索引。
写操作的成本：由于 DML（数据操作语言）操作会更新索引，因此具有索引的写操作成本相对较高。如果系统的写入操作频繁而读取操作不频繁，需要认真考虑是否真正需要索引。
基数（Cardinality）的重要性：基数指的是列中的不同值的数量。如果在基数较低的列上创建索引，这并不会带来明显的性能提升，因为索引的目的是减少搜索空间。
举例来说，如果在一个布尔类型（只有 0 和 1 两个值）的列上创建索引，由于基数很低（基数为 2），索引会非常倾斜。但是，如果这个布尔字段可以与其他列组合，产生较高的基数，那么在需要时可以考虑创建该索引。
索引的维护：索引可能需要定期维护，特别是如果索引中仍然包含旧数据。否则，这些索引将占用大量内存空间。因此，建议为索引制定一个监控计划。

最终，深入理解数据库索引的各个方面非常重要。这将有助于在进行低级系统设计时做出明智的决策。许多应用程序在现实生活中的优化取决于对这些细节的了解。精心选择的索引将显著提升应用程序的性能。

目录