mysql 局部索引

原创

mob64ca12e86bd4 2024-09-12 04:35:44 ©著作权

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

深入了解 MySQL 局部索引的实现

在数据库的日常操作中，索引是提高查询效率的利器。MySQL 支持多种类型的索引，其中“局部索引”指的是仅对某些列的部分内容建立索引，而不是完整的列。当我们只需要常用的部分数据时，使用局部索引能显著提升查询性能，节省存储空间。本篇文章将引导你完成 MySQL 局部索引的实现过程。

实现流程

以下是实现 MySQL 局部索引的步骤：

步骤	描述
1	创建测试数据库
2	创建包含需要索引的表
3	插入测试数据
4	创建局部索引
5	查询数据并观察索引效果

具体步骤详解

第一步：创建测试数据库

我们首先需要创建一个数据库，以便于后续进行表的创建和数据的插入。

-- 创建一个名为 'test_db' 的数据库
CREATE DATABASE test_db;

-- 选择使用该数据库
USE test_db;

第二步：创建包含需要索引的表

在创建表时，我们可以定义一些字段，例如我们创建一个“用户”表，包含用户的 ID 和名字。

-- 创建用户表，包含 id 和 name 字段
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY, -- 用户ID，自增主键
    name VARCHAR(255) NOT NULL          -- 用户名，非空
);

第三步：插入测试数据

为了能够测试索引功能，我们需要在表中插入一些数据。

-- 向 users 表中插入测试数据
INSERT INTO users (name) VALUES ('Alice'), ('Bob'), ('Alice'), ('Charlie'), ('Bob'), ('David');

第四步：创建局部索引

此时我们可以基于 name 列创建一个局部索引。局部索引指的是只索引部分数据，我们可以针对 name 列的一部分来加速查询。

-- 创建局部索引，仅索引名字的前部分
CREATE INDEX idx_name_partial ON users (name(10));

这条 SQL 语句会在 name 列的前10个字符上创建一个局部索引，如果 name 列中的字符串较长，局部索引可以显著节省空间，同时提高查询效率。

第五步：查询数据并观察索引效果

创建了索引后，我们可以运行几个查询来观察索引是否正常工作。

-- 查询名字为 'Alice' 的用户
SELECT * FROM users WHERE name = 'Alice';

-- 查询名字以 'B' 开头的用户
SELECT * FROM users WHERE name LIKE 'B%';

通过 EXPLAIN 关键词，我们可以分析查询是否使用了索引，从而验证我们的局部索引是否有效。

-- 分析 SQL 查询，查看查询计划
EXPLAIN SELECT * FROM users WHERE name = 'Alice';

旅行图

以下是实现局部索引过程中的经历旅程图，以帮助你理解每一步的重要性和目的：

journey
    title MySQL 局部索引实现过程
    section 创建测试数据库
      创建数据库: 5: 用户
      选择数据库: 4: 用户
    section 创建用户表
      定义数据库结构: 3: 用户
    section 插入测试数据
      加入实际数据: 4: 用户
    section 创建局部索引
      提升查询性能: 5: 用户
    section 查询数据
      验证索引效果: 3: 用户