MySQL高级篇之索引的数据结构详解

索引的作用

索引是一种数据结构，用于快速地定位和访问数据表中的指定行。MySQL中索引通常以B-tree（B树）或哈希表的形式来实现，通过将索引存储在内存中，可以提高系统的查询效率。

常用的索引分为主键索引、唯一索引和普通索引。其作用分别为：

• 主键索引：保证表中每一行数据的唯一性，便于快速查询和修改数据。
• 唯一索引：保证某一列的值唯一，可以加速对该列值的查找。
• 普通索引：加速对某一列的查找，但不保证值的唯一性。

B-tree（B树）

B-tree是一种多路搜索树，它为数据库系统提供了一种高效的索引结构。MySQL的B-tree是一种平衡树，它的每个节点中都包含多个键值和子节点指针。

B-tree的搜索过程，首先需要找到根节点，然后根据键值大小关系逐级向下搜索，直到找到目标节点或搜索失败为止。

以下是一个B-tree的示例：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '',
  `age` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

在此表中，id列是主键索引，name列是唯一索引。在创建索引时，MySQL会自动根据列名创建一个索引名，如上述代码中的PRIMARY KEY和UNIQUE KEY。

哈希索引

哈希索引是一种基于哈希表的索引结构，它将数据存储在一张哈希表中。哈希索引适用于等值查询，但不适于范围查询、模糊查询等。

以下是一个哈希索引的示例：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '',
  `age` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `name` (`name`) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

在此表中，name列是哈希索引。创建哈希索引时，需要在索引名后添加USING HASH，告诉MySQL使用哈希索引。

总结

索引是提高系统查询效率的重要手段，其中B-tree和哈希索引是最常用的索引。在实际应用中，需要根据不同情况选择合适的索引类型，同时需要注意索引的创建和维护，以保证索引的有效性。

示例1：如何查看表的索引信息？

我们可以通过以下SQL语句查看某个表的索引信息：

SHOW INDEX FROM user;

该语句可以显示user表的全部索引信息，包括索引名、索引类型、索引包含的列等。

示例2：如何通过ALTER TABLE添加索引？

我们可以通过以下SQL语句添加一个普通索引：

ALTER TABLE user ADD INDEX age_index (age);

该语句会在user表中添加一个名为age_index的普通索引，索引列为age。