mysql- 索引讲解分析以及基本操作-05

数据库索引全面解析

简介

索引是创建在表上的一种数据结构，用于对数据库表中一列或多列的值进行排序，以提高查询效率。索引本质上是一种"目录"，它通过特定的数据结构组织数据，使得数据库系统能够快速定位到所需的数据行，而不必扫描整个表。

索引的优缺点

优点：

加快查询速度：索引可以大幅减少数据库需要扫描的数据量
降低排序时间：如果查询需要排序，而排序字段已经建立了索引，可以避免实际的排序操作
加快表间连接速度：在表连接操作中，索引可以显著提高连接效率

缺点：

占用磁盘空间：索引需要额外的存储空间
降低写入性能：插入、更新和删除操作需要同时维护索引，会导致这些操作变慢
维护成本：随着数据变化，索引需要不断更新，增加了数据库的维护开销

索引类型

聚集索引（Clustered Index）

特点：

按照主键值进行排序和存储
叶子节点直接包含完整的行数据
在InnoDB中，主键索引就是聚集索引
一个表只能有一个聚集索引

优点：

主键查询效率极高，不需要"回表"操作
范围查询效率高，因为数据物理上是按顺序存储的

缺点：

占用存储空间较大
插入速度受主键顺序影响较大

包含：

主键索引：不能重复、不能为空的唯一索引

CREATE TABLE user(
  id int PRIMARY KEY,  -- 自动创建主键索引
  name CHAR(20)
);

非聚集索引（辅助索引）

特点：

叶子节点不包含行数据，而是存储主键值和索引列值
一个表可以有多个非聚集索引

优点：

占用存储空间相对较小
可以创建多个，满足不同查询需求

缺点：

如果查询的列不在索引中，需要"回表"查询主键索引，增加IO操作

包含：

唯一索引：索引列值必须唯一，但允许NULL值

CREATE TABLE user(
  id int UNIQUE,      -- 自动创建唯一索引
  name CHAR(20) UNIQUE
);

普通索引（单列索引）：最基本的索引类型，无任何限制
- 可以创建在任何类型的字段上
- 一个表可以创建多个单列索引

普通索引（单列索引）创建示例

1. 建表时创建普通索引

-- 在tname字段上创建名为idx_name的普通索引
CREATE TABLE student (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    email VARCHAR(100),
    INDEX idx_name (name)  -- 普通索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 在已有表上创建普通索引

-- 为student表的age字段创建名为idx_age的普通索引
CREATE INDEX idx_age ON student(age);

-- 或者使用ALTER TABLE语法
ALTER TABLE student ADD INDEX idx_email (email);

3. 创建前缀索引（只索引字段的前N个字符

-- 为email字段的前10个字符创建索引
CREATE INDEX idx_email_prefix ON student(email(10));

、、、

组合索引（多列索引/复合索引/联合索引）：在多列上同时创建的索引
- 遵循"最左前缀"原则，如索引(id,name,job)可以支持：
  - WHERE id = ?
  - WHERE id = ? AND name = ?
  - WHERE id = ? AND name = ? AND job = ?

组合索引（多列索引/联合索引）创建示例

、、、

1. 建表时创建组合索引

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    status VARCHAR(20),
    amount DECIMAL(10,2),
    INDEX idx_customer_status (customer_id, status)  -- 组合索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 在已有表上创建组合索引

-- 为orders表创建customer_id和order_date的组合索引
CREATE INDEX idx_customer_date ON orders(customer_id, order_date);

-- 或者使用ALTER TABLE语法
ALTER TABLE orders ADD INDEX idx_status_amount (status, amount);

3. 包含3个列的组合索引

-- 创建包含customer_id、status和order_date的组合索引
CREATE INDEX idx_customer_status_date ON orders(customer_id, status, order_date)

索引数据结构：B+树

示意图：

        [ 根节点]
       /    |    \
  [支节点] [支节点] [支节点]
   / | \   / | \   / | \
[叶子][叶子][叶子]...[叶子] ← 双向链表

特点：

非叶子节点只存储索引：可以容纳更多关键字，降低树的高度，减少IO次数
叶子节点存储数据：
- 聚集索引：存储索引列值和完整行数据
- 非聚集索引：存储索引列值和主键值
叶子节点形成双向链表：天然有序，支持高效的范围查询
查询效率稳定：所有查询都要访问到叶子节点，查询路径长度相同

索引操作

查看索引

SHOW INDEX FROM test;
SHOW CREATE TABLE test;  -- 查看索引类型

创建索引

普通索引：

-- 建表时创建
CREATE TABLE test(
  tid int,
  tname char(20),
  INDEX index_tname(tname)  -- 创建普通索引
) ENGINE=INNODB CHARSET=utf8;

-- 已存在表上创建
CREATE INDEX index_tid ON test(tid);
ALTER TABLE test ADD INDEX index_tid(tid);

唯一索引：

-- 建表时创建
CREATE TABLE test2(
  tid int,
  tname char(20),
  UNIQUE INDEX index_tname(tname)  -- 创建唯一索引
) ENGINE=INNODB CHARSET=utf8;

-- 已存在表上创建
CREATE UNIQUE INDEX index_tid ON test2(tid);
ALTER TABLE test ADD UNIQUE INDEX index_tid(tid);

删除索引

DROP INDEX index_tid ON test;
ALTER TABLE test DROP INDEX index_tid;

应用场景

适合创建索引的情况：
- 主键自动创建索引
- 频繁作为查询条件的字段
- 经常需要排序、分组和联合操作的字段
- 作为外键的字段
- 高选择性的字段（字段值重复率低）
不适合创建索引的情况：
- 频繁更新的字段
- 数据量小的表
- 数据重复且分布均匀的字段
- 很少被查询的字段

实际应用示例

场景1：用户查询优化

-- 用户表常用查询：按城市和年龄筛选
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    age INT,
    city VARCHAR(50),
    registration_date DATE,
    INDEX idx_city_age (city, age)  -- 支持 WHERE city=? AND age>? 这类查询
);

场景2：订单查询优化

-- 订单表常用查询：按用户ID和订单状态筛选
ALTER TABLE orders ADD INDEX idx_user_status (customer_id, status);

-- 支持以下查询高效执行：
-- SELECT * FROM orders WHERE customer_id = 1001 AND status = 'completed';
-- SELECT * FROM orders WHERE customer_id = 1001;

场景3：时间范围查询优化

-- 日志表按日期和类型查询
CREATE TABLE system_logs (
    log_id BIGINT PRIMARY KEY,
    log_type VARCHAR(30),
    log_date DATETIME,
    message TEXT,
    INDEX idx_type_date (log_type, log_date)  -- 支持按类型和时间范围查询
);

-- 高效查询示例：
-- SELECT * FROM system_logs 
-- WHERE log_type = 'error' AND log_date BETWEEN '2023-01-01' AND '2023-01-31';

索引优化建议

遵循最左前缀原则：组合索引的查询条件应从最左列开始
避免过度索引：索引不是越多越好，每个索引都会增加维护成本
考虑索引选择性：选择性高的字段更适合建索引
使用覆盖索引：尽量让查询只需要通过索引就能完成，避免回表
定期分析索引使用情况：删除不常用的索引

通过合理设计和使用索引，可以显著提高数据库查询性能，但需要权衡查询性能和写入性能的关系，根据实际业务需求进行优化。

posted @ 2025-06-11 11:51 Shafir莎菲尔阅读(38) 评论(0) 收藏举报

刷新页面返回顶部

寻找-永无止境