MySQL 一级索引和二级索引

原创

mob649e8165596b 2024-10-13 05:14:49 ©著作权

©著作权归作者所有：来自51CTO博客作者mob649e8165596b的原创作品，请联系作者获取转载授权，否则将追究法律责任

完全理解 MySQL 的一级索引与二级索引

在数据库设计中，索引的建立是优化查询性能的关键一步。MySQL 数据库中，一般提到的索引通常分为一级索引和二级索引。今天，我将以一名经验丰富的开发者的角色，逐步引导你理解如何在 MySQL 中创建一级索引和二级索引。

一、整体流程

我们将通过以下步骤来完成索引的创建：

步骤	操作内容
步骤1	创建数据库和数据表
步骤2	创建一级索引
步骤3	创建二级索引
步骤4	插入数据
步骤5	查询数据

二、详细步骤

步骤1：创建数据库和数据表

首先，我们需要创建一个数据库，以及一张包含示例数据的表。在这个表中，我们将设定一些字段来后续建立索引。

-- 创建数据库
CREATE DATABASE SchoolDB;

-- 使用创建的数据库
USE SchoolDB;

-- 创建学生表
CREATE TABLE Students (
    id INT AUTO_INCREMENT PRIMARY KEY, -- 主键，自动增长
    name VARCHAR(100),                 -- 学生姓名
    age INT,                           -- 学生年龄
    class VARCHAR(20),                 -- 班级
    INDEX idx_class (class)            -- 二级索引
);

解释：首先创建了一个名为 SchoolDB 的数据库，并在此数据库中创建一个名为 Students 的表。其中 id 字段是我们的主键，class 字段上建立了一个二级索引 idx_class。

步骤2：创建一级索引

在 MySQL 中，主键默认就有一个唯一的一级索引。在我们前面的 CREATE TABLE 语句中，id 字段已经是主键，因此已经自动创建了一个一级索引。

-- 查看索引
SHOW INDEX FROM Students;

解释：这一命令将展示 Students 表中的索引信息。可以看到，id 字段的索引类型为 PRIMARY，即一级索引。

步骤3：创建二级索引

我们在创建表时为 class 字段添加了一个二级索引。此时我们需要反复确认该索引是否正确建立。若后期想要修改可使用 ALTER TABLE 语句。

-- 方法一：展示表中的所有索引
SHOW INDEX FROM Students;

-- 方法二：手动添加二级索引
ALTER TABLE Students ADD INDEX idx_age (age); -- 在 age 字段上创建一个新的二级索引

解释：此命令会先展示所有索引，然后我们又为 age 字段添加了一个新的二级索引。

步骤4：插入数据

接下来我们向表中插入一些数据，以便后续测试索引的查询性能。

-- 插入数据
INSERT INTO Students (name, age, class) VALUES
('Alice', 12, '6A'),
('Bob', 13, '6B'),
('Charlie', 12, '6A'),
('David', 14, '6C');

解释：这段代码插入了四条模拟学生数据。

步骤5：查询数据

最后，我们可以使用 SQL 查询来验证索引的作用。我们将进行两个查询，一个是基于一级索引的查询，一个是基于二级索引的查询。

-- 基于一级索引的查询（查找 ID=1 的学生信息）
SELECT * FROM Students WHERE id = 1;

-- 基于二级索引的查询（查找班级为 '6A' 的学生信息）
SELECT * FROM Students WHERE class = '6A';

解释：第一个 SQL 查询利用了 id 的一级索引，第二个查询则利用了 class 的二级索引。在这两个查询中，二级索引能有效提升检索速度。

三、类图展示

以下是一个简化的类图，展示了数据库表与索引的关系。

classDiagram
    class Students {
        +int id
        +string name
        +int age
        +string class
    }
    class Index {
        +id : int
        +class_idx : class
        +age_idx : age
    }
    
    Students --|> Index : has