MySQL的一级索引和二级索引的区别及说明

目录

• 第一部分：mysql 的一级索引和二级索引的区别是什么？
• 一级索引和二级索引就像玩具工厂里的“清单”
• 第二部分：包含哪些部分？
• 主要组成部分
• 第三部分：背后到底做了哪些事情？
• 核心思想
• 底层实现
• 第四部分：示例代码与详细讲解
• 示例代码：模拟一级索引和二级索引的工作过程
• 背后发生了什么？
• 第五部分：使用场景
• 一级索引
• 二级索引
• 数据完整性需求
• 第六部分：底层原理
• B+ 树索引
• 回表操作
• 查询优化
• 第七部分：图表与示意图
• 思维导图
• 流程图
• 架构图
• 类图
• 序列图
• 数据流图
• 示意图
• 第八部分：总结
• 一级索引和二级索引的本质
• 生活中的类比
• 最后

第一部分：MySQL 的一级索引和二级索引的区别是什么？

一级索引和二级索引就像玩具工厂里的“清单”

生活中的例子：

• 想象一下，你在一家玩具工厂工作。工厂里有一个工人（相当于 MySQL）负责组装玩具：

一级索引：

• 工人有一份完整的零件清单（相当于主键索引），上面记录了每个零件的编号和具体位置。
• 这份清单是唯一的，每个零件编号只能对应一个位置。

二级索引：

• 工人还有一份分类清单（相当于非主键索引），上面记录了零件的类别（如颜色、形状）和对应的编号。
• 这份清单不是唯一的，一个类别可能对应多个零件编号。

在编程里：

• 一级索引（主键索引） 是基于主键构建的索引，直接指向主表中的完整数据。
• 二级索引（非主键索引） 是基于非主键列构建的索引，存储的是主键值，需要回表才能获取完整数据。

第二部分：包含哪些部分？

主要组成部分

一级索引（主键索引）

• 存储主键值和指向主表数据的指针。

二级索引（非主键索引）

• 存储非主键列的值和对应的主键值。

B+ 树结构

• 一级索引和二级索引都使用 B+ 树存储数据。

查询请求

• 用户通过 SQL 查询请求数据。

回表操作

• 当使用二级索引时，如果查询字段不在索引中，需要回表查找完整数据。

第三部分：背后到底做了哪些事情？

核心思想

一级索引：

• 直接定位到主表中的完整数据。

二级索引：

• 先通过索引找到主键值，再通过主键值回到主表查找完整数据。

优化策略：

• 尽量避免回表，例如使用覆盖索引。

底层实现

B+ 树索引：

• 一级索引和二级索引都使用 B+ 树存储数据。

叶子节点：

• 一级索引的叶子节点存储主键值和完整数据。
• 二级索引的叶子节点存储非主键列的值和主键值。

查询计划：

• MySQL 的查询优化器会决定使用哪种索引。

第四部分：示例代码与详细讲解

示例代码：模拟一级索引和二级索引的工作过程

<?php

// 第一步：定义一个表
class Table {
    private $primaryKeyIndex; // 一级索引（主键索引）
    private $secondaryIndex;  // 二级索引（非主键索引）
    private $mainTable;       // 主表

    public function __construct() {
        // 初始化一级索引（主键索引）
        $this->primaryKeyIndex = [
            1 => ['id' => 1, 'name' => 'Alice'javascript, 'age' => 25, 'city' => 'New York'],
            2 => ['id' => 2, 'name' => 'Bob', 'age' => 30, 'city' => 'San Francisco'],
            3 => ['id' => 3, 'name' => 'Charlie', 'age' => 35, 'city' => 'Los Angeles']
        ];

        // 初始化二级索引（非主键索引）
        $this->secondaryIndex = [
            'Alice' => ['id' => 1],
            'Bob' => ['id' => 2],
            'Charlie' => ['id' => 3]
        ];

        // 初始化主表
        $this->mainTable = [
            1 => ['id' => 1, 'name' => 'Alice', 'age' => 25, 'city' => 'New York'],
            2 => ['id' => 2, 'name' => 'Bob', 'age' =>js 30, 'city' => 'San Francisco'],
            3 => ['id' => 3, 'name' => 'Charlie', 'age' => 35, 'city' => 'Los Angeles']
        ];
    }

    // 使用一级索引查找数据
    public function queryWithPrimaryKey($id) {
        echo "正在通过一级索引查找记录...\n";

        // 查找一级索引
        if (isset($this->primaryKeyIndex[$id])) {
            $record = $this->primaryKeyIjavascriptndex[$id];
            echo "一级索引中找到完整记录：ID={$record['id']}, Name={$record['name']}, Age={$record['age']}, City={$record['city']}。\n";
        } else {
            echo "未找到记录。\n";
        }
    }

    // 使用二级索引查找数据
    public function queryWithSecondaryIndex($name) {
        echo "正在通过二级索引查找记录...\n";

        // 查找二级索引
        if (isset($this->secondaryIndex[$name])) {
            $indexRecord = $this->secondaryIndex[$name];
            echo "二级索引中找到主键值：ID={$indexRecord['id']}。\n";

            // 回表查找完整数据
            $mainRecord = $this->mainTable[$indexRecord['id']];
            echo "回表后找到完整记录：ID={$mainRecord['id']}, Name={$mainRecord['name']}, Age={$mainRecord['age']}, City={$mainRecord['city']}。\n";
        } else {
            echo "未找到记录。\n";
        }
    }
}

// 第二步：模拟一级索引和二级索引的工作过程
$table = new Table();

echo "\n=== 模拟一级索引查询 ===\n";

// 使用一级索引查询 ID=2 的记录
$table->queryWithPrimaryKey(2);

echo "\n=== 模拟二级索引查询 ===\n";

// 使用二级索引查询 Name='Alice' 的记录
$table->queryWithSecondaryIndex('Alice');

为什么要这样写？

• 第一步：定义一个 Table 类，模拟一级索引和二级索引的功能。
• 第二步：总结一级索引和二级索引的核心区别，展示其本质。

背后发生了什么？

一级索引查询：

• 直接通过主键定位到完整数据。

二级索引查询：

• 先通过索引找到主键值，再回到主表查找完整数据。

结果返回：

• 返回完整的查询结果。

总结核心作用：

• 展示一级索引和二级索引如何协作完成查询。

第五部分：使用场景

一级索引

• 当查询条件是主键时，使用一级索引。
• 例如：

SELECT * FROM users WHERE id=2;

二级索引

• 当查询条件是非主键列时，使用二级索引。
• 例如：

SELECT * FROM users WHERE name='Alice';

数据完整性需求

• 当需要返回完整数据时，编程客栈二级索引可能需要回表。
• 例如：查询用户的所有信息。

第六部分：底层原理

B+ 树索引

一级索引：

• 叶子节点存储主键值和完整数据。

二级索引：

• 叶子节点存储非主键列的值和主键值。

回表操作

二级索引：

• 如果查询字段不在索引中，需要回到主表查找完整数据。

查询优化

覆盖索引：

• 如果查询字段都在索引中，可以避免回表。

索引设计：

• 合理设计索引，减少回表次数。

第七部分：图表与示意图

思维导图

MySQL 索引
├── 一级索引
│   ├── 主键索引
│   └── 完整数据
├── 二级索引
│   ├── 非主键索引
│   └── 回表操作
└── 查询优化
    ├── 覆盖索引
    └── 索引设计

流程图

[查询请求] --> [一级索引] --> [完整数据]
[查询请求] --> [二级索引] --> [回表操作] --> [完整数据]

架构图

[一级索引] -----> [主表] -----> [完整数据]
[二级索引] -----> [主表] -----> [完整数据]

类图

+-------------------+
|       Table       |
+-------------------+
| - primaryKeyIndex: array|
| - secondaryIndex: array |
| - mainTable: array |
+-------------------+
| + queryWithPrimaryKey(): void|
| + queryWithSecondaryIndex(): void|
+-------------------+

序列图

主程序 -> 表: 查询请求
表 -> 一级索引: 查找记录
一级索引 -> 表: 返回完整记录
表 -> 主程序: 返回查询结果

主程序 -> 表: 查询请求
表 -> 二级索引: 查找记录
二级索引 -> 表: 返回主键值
表 -> 主表: 回表查找
主表 -> 表: 返回完整记录
表 -> 主程序: 返回查询结果

数据流图

[查询请求] -----> [一级索引] -----> [完整数据]
[查询请求] -----> [二级索引] -----> [主表] -----> [完整数据]

示意图

[一级索引] -----> [主表] -----> [完整数据]
[二级索引] -----> [主表] -----> [完整数据]

第八部分：总结

一级索引和二级索引的本质

一级索引：

• 基于主键构建，直接指向主表中的完整数据。

二级索引：

• 基于非主键列构建，存储主键值，需要回表查找完整数据。

优化策略：

• 尽量避免回表，例如使用覆盖索引。

生活中的类比

一级索引和二级索引就像玩具工厂里的“清单”：

• 一级索引是完整的零件清单，直接找到零件。
• 二级索引是分类清单，先找到零件编号，再根据编号找到零件。

最后

以上为个人经验，编程客栈希望能给大家一个参考，也希望大家多多支持编程客栈(www.devze.com)。