MySQL 数据库索引详细教程

五道书童

于 2025-03-25 09:00:00 发布

阅读量3.4k

点赞数 30

分类专栏：数据库文章标签：数据库 mysql

本文链接：https://blog.csdn.net/z17826856787/article/details/146490277

版权

数据库专栏收录该内容

17 篇文章

订阅专栏

MySQL 数据库索引详细教程

一、索引基础概念

1. 什么是索引

索引是数据库中用于提高查询性能的数据结构，类似于书籍的目录。它可以帮助数据库引擎快速定位到表中的特定数据，而不需要扫描整个表。

2. 索引的优点

大大加快数据的检索速度
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
加速表与表之间的连接
在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间

3. 索引的缺点

创建和维护索引需要耗费时间
索引需要占用物理空间
当对表中的数据进行增删改时，索引也要动态维护，降低了数据的维护速度

二、MySQL索引类型

1. B-Tree索引（默认索引类型）

-- 创建B-Tree索引
CREATE INDEX idx_name ON table_name(column_name);

-- 多列索引
CREATE INDEX idx_name ON table_name(col1, col2, col3);

2. 唯一索引

CREATE UNIQUE INDEX idx_name ON table_name(column_name);

3. 主键索引

-- 创建表时指定主键
CREATE TABLE table_name (
    id INT NOT NULL,
    PRIMARY KEY (id)
);

-- 修改表添加主键
ALTER TABLE table_name ADD PRIMARY KEY (column_name);

4. 全文索引（FULLTEXT）

-- 创建全文索引
CREATE FULLTEXT INDEX idx_name ON table_name(column_name);

-- 使用全文索引查询
SELECT * FROM table_name WHERE MATCH(column_name) AGAINST('search_term');

5. 空间索引（SPATIAL）

CREATE SPATIAL INDEX idx_name ON table_name(column_name);

6. 哈希索引（MEMORY引擎支持）

CREATE INDEX idx_name USING HASH ON table_name(column_name);

三、索引的创建与管理

1. 创建索引

-- 基本语法
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
ON table_name (column_name [(length)] [ASC|DESC], ...);

-- 示例
CREATE INDEX idx_customer_name ON customers(last_name, first_name);

2. 查看索引

-- 查看表的索引
SHOW INDEX FROM table_name;

-- 或使用
SHOW KEYS FROM table_name;

3. 删除索引

DROP INDEX index_name ON table_name;

4. 修改索引

MySQL不支持直接修改索引，需要先删除再重建。

四、索引设计原则

1. 选择合适的列建立索引

经常作为查询条件的列（WHERE子句）
经常用于表连接的列
经常需要排序的列（ORDER BY子句）
经常需要分组统计的列（GROUP BY子句）

2. 避免过度索引

索引并非越多越好，每个额外的索引都会占用存储空间并降低写操作性能
一般建议单表索引不超过5-6个

3. 考虑索引的选择性

选择性高的列更适合建立索引，选择性计算：

选择性 = 不重复的索引值数量 / 表中记录总数

4. 复合索引设计原则

最左前缀原则：索引(a,b,c)可以用于查询条件a、a,b或a,b,c，但不能用于b,c
将选择性高的列放在前面
将经常用于查询条件的列放在前面
将需要排序的列放在后面

五、索引优化技巧

1. 使用EXPLAIN分析查询

EXPLAIN SELECT * FROM customers WHERE last_name = 'Smith';

2. 覆盖索引

当查询的列都包含在索引中时，MySQL可以直接从索引获取数据而不需要回表。

-- 假设有索引(last_name, first_name)
SELECT first_name FROM customers WHERE last_name = 'Smith';

3. 索引条件下推(ICP)

MySQL 5.6+支持，将WHERE条件推到存储引擎层进行过滤。

4. 避免索引失效的场景

使用不等于操作（!= 或 <>）
使用函数操作索引列（如WHERE YEAR(date_column) = 2023）
使用OR连接条件（除非所有OR条件都有索引）
使用LIKE以通配符开头（如’%name’）
类型转换（如字符串列与数字比较）

六、高级索引策略

1. 前缀索引

对于长字符串列，可以只索引前几个字符。

CREATE INDEX idx_name ON table_name(column_name(10));

2. 使用索引进行排序

-- 如果索引是(a,b), 以下查询可以使用索引排序
SELECT * FROM table_name ORDER BY a, b;

-- 以下查询不能使用索引排序
SELECT * FROM table_name ORDER BY b;

3. 冗余和重复索引

避免创建功能相同的索引：

-- 已有索引(a,b), 再创建索引(a)就是冗余的
-- 已有主键id, 再创建唯一索引id就是重复的

4. 索引合并

MySQL有时会使用多个索引并将结果合并：

-- 假设有索引a和b
SELECT * FROM table_name WHERE a = 1 OR b = 2;

七、实践案例

案例1：电商平台用户表

CREATE TABLE users (
    user_id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id),
    UNIQUE KEY idx_username (username),
    UNIQUE KEY idx_email (email),
    INDEX idx_phone (phone),
    INDEX idx_created (created_at)
);

案例2：订单查询优化

-- 原始查询
SELECT * FROM orders WHERE customer_id = 100 AND status = 'shipped' ORDER BY order_date DESC;

-- 优化方案
CREATE INDEX idx_customer_status_date ON orders(customer_id, status, order_date DESC);