LucasDevLucasDev

MySQL索引详解

MySQL索引详解

概述

索引是数据库中用于快速查找数据的数据结构。MySQL中的索引主要基于B+树实现，能够显著提高查询性能，但也会增加存储空间和维护成本。

索引类型

按数据结构分类

B+树索引

  • 特点：MySQL默认索引类型，支持范围查询
  • 适用场景：大部分查询场景
  • 存储引擎：InnoDB、MyISAM
sql 
-- 创建B+树索引
CREATE INDEX idx_user_name ON users(name);

-- 查看索引信息
SHOW INDEX FROM users;
-- 创建B+树索引
CREATE INDEX idx_user_name ON users(name);

-- 查看索引信息
SHOW INDEX FROM users;

哈希索引

  • 特点：等值查询速度快，不支持范围查询
  • 适用场景：等值查询为主的场景
  • 存储引擎：Memory、NDB
sql 
-- Memory引擎默认使用哈希索引
CREATE TABLE hash_table (
    id INT,
    name VARCHAR(50),
    INDEX USING HASH (name)
) ENGINE=MEMORY;
-- Memory引擎默认使用哈希索引
CREATE TABLE hash_table (
    id INT,
    name VARCHAR(50),
    INDEX USING HASH (name)
) ENGINE=MEMORY;

全文索引

  • 特点：支持文本搜索
  • 适用场景：文本检索
  • 存储引擎：InnoDB、MyISAM
sql 
-- 创建全文索引
CREATE FULLTEXT INDEX idx_content ON articles(content);

-- 使用全文索引
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('MySQL 索引' IN NATURAL LANGUAGE MODE);
-- 创建全文索引
CREATE FULLTEXT INDEX idx_content ON articles(content);

-- 使用全文索引
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('MySQL 索引' IN NATURAL LANGUAGE MODE);

按字段数量分类

单列索引

sql 
-- 单列索引
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_order_date ON orders(order_date);
-- 单列索引
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_order_date ON orders(order_date);

复合索引（多列索引）

sql 
-- 复合索引
CREATE INDEX idx_user_age_city ON users(age, city);
CREATE INDEX idx_order_user_date ON orders(user_id, order_date, status);
-- 复合索引
CREATE INDEX idx_user_age_city ON users(age, city);
CREATE INDEX idx_order_user_date ON orders(user_id, order_date, status);

按功能分类

主键索引

sql 
-- 主键自动创建聚簇索引
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(100)
);
-- 主键自动创建聚簇索引
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(100)
);

唯一索引

sql 
-- 唯一索引
CREATE UNIQUE INDEX idx_user_email ON users(email);

-- 或者在创建表时定义
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);
-- 唯一索引
CREATE UNIQUE INDEX idx_user_email ON users(email);

-- 或者在创建表时定义
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

普通索引

sql 
-- 普通索引
CREATE INDEX idx_user_name ON users(name);
-- 普通索引
CREATE INDEX idx_user_name ON users(name);

索引原理

B+树结构

特点

  • 非叶子节点：只存储键值，不存储数据
  • 叶子节点：存储完整数据，通过指针连接
  • 有序性：叶子节点按键值有序排列
java 
// B+树节点结构示意
public class BPlusTreeNode {
    private boolean isLeaf;
    private List<Integer> keys;
    private List<BPlusTreeNode> children; // 非叶子节点
    private List<Record> records;         // 叶子节点
    private BPlusTreeNode next;           // 叶子节点链表
}
// B+树节点结构示意
public class BPlusTreeNode {
    private boolean isLeaf;
    private List<Integer> keys;
    private List<BPlusTreeNode> children; // 非叶子节点
    private List<Record> records;         // 叶子节点
    private BPlusTreeNode next;           // 叶子节点链表
}

查询过程

sql 
-- 查询示例
SELECT * FROM users WHERE id = 100;

-- B+树查询步骤：
-- 1. 从根节点开始，比较键值
-- 2. 根据比较结果选择子节点
-- 3. 重复步骤1-2，直到叶子节点
-- 4. 在叶子节点中找到目标记录
-- 查询示例
SELECT * FROM users WHERE id = 100;

-- B+树查询步骤：
-- 1. 从根节点开始，比较键值
-- 2. 根据比较结果选择子节点
-- 3. 重复步骤1-2，直到叶子节点
-- 4. 在叶子节点中找到目标记录

聚簇索引 vs 非聚簇索引

聚簇索引（主键索引）

sql 
-- InnoDB中主键就是聚簇索引
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

-- 数据按主键顺序物理存储
-- 叶子节点直接存储完整行数据
-- InnoDB中主键就是聚簇索引
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

-- 数据按主键顺序物理存储
-- 叶子节点直接存储完整行数据

非聚簇索引（二级索引）

sql 
-- 二级索引
CREATE INDEX idx_user_name ON users(name);

-- 叶子节点存储索引键值 + 主键值
-- 需要回表查询获取完整数据
-- 二级索引
CREATE INDEX idx_user_name ON users(name);

-- 叶子节点存储索引键值 + 主键值
-- 需要回表查询获取完整数据

回表查询

sql 
-- 会发生回表的查询
SELECT * FROM users WHERE name = 'John';

-- 查询过程：
-- 1. 在name索引中找到'John'对应的主键id
-- 2. 根据主键id在聚簇索引中查找完整记录（回表）

-- 避免回表的查询（覆盖索引）
SELECT id, name FROM users WHERE name = 'John';
-- 会发生回表的查询
SELECT * FROM users WHERE name = 'John';

-- 查询过程：
-- 1. 在name索引中找到'John'对应的主键id
-- 2. 根据主键id在聚簇索引中查找完整记录（回表）

-- 避免回表的查询（覆盖索引）
SELECT id, name FROM users WHERE name = 'John';

索引优化

覆盖索引

sql 
-- 创建覆盖索引
CREATE INDEX idx_user_name_email ON users(name, email);

-- 使用覆盖索引（无需回表）
SELECT name, email FROM users WHERE name = 'John';

-- 查看执行计划
EXPLAIN SELECT name, email FROM users WHERE name = 'John';
-- Extra: Using index
-- 创建覆盖索引
CREATE INDEX idx_user_name_email ON users(name, email);

-- 使用覆盖索引（无需回表）
SELECT name, email FROM users WHERE name = 'John';

-- 查看执行计划
EXPLAIN SELECT name, email FROM users WHERE name = 'John';
-- Extra: Using index

最左前缀原则

sql 
-- 复合索引
CREATE INDEX idx_abc ON table_name(a, b, c);

-- 能使用索引的查询
SELECT * FROM table_name WHERE a = 1;                    -- 使用索引
SELECT * FROM table_name WHERE a = 1 AND b = 2;          -- 使用索引
SELECT * FROM table_name WHERE a = 1 AND b = 2 AND c = 3; -- 使用索引
SELECT * FROM table_name WHERE a = 1 AND c = 3;          -- 部分使用索引

-- 不能使用索引的查询
SELECT * FROM table_name WHERE b = 2;                    -- 不使用索引
SELECT * FROM table_name WHERE c = 3;                    -- 不使用索引
SELECT * FROM table_name WHERE b = 2 AND c = 3;          -- 不使用索引
-- 复合索引
CREATE INDEX idx_abc ON table_name(a, b, c);

-- 能使用索引的查询
SELECT * FROM table_name WHERE a = 1;                    -- 使用索引
SELECT * FROM table_name WHERE a = 1 AND b = 2;          -- 使用索引
SELECT * FROM table_name WHERE a = 1 AND b = 2 AND c = 3; -- 使用索引
SELECT * FROM table_name WHERE a = 1 AND c = 3;          -- 部分使用索引

-- 不能使用索引的查询
SELECT * FROM table_name WHERE b = 2;                    -- 不使用索引
SELECT * FROM table_name WHERE c = 3;                    -- 不使用索引
SELECT * FROM table_name WHERE b = 2 AND c = 3;          -- 不使用索引

索引下推

sql 
-- 索引下推优化
CREATE INDEX idx_name_age ON users(name, age);

-- 查询
SELECT * FROM users WHERE name LIKE 'J%' AND age > 25;

-- 优化前：在存储引擎层只能使用name条件，age条件在Server层过滤
-- 优化后：age条件也在存储引擎层过滤（索引下推）
-- 索引下推优化
CREATE INDEX idx_name_age ON users(name, age);

-- 查询
SELECT * FROM users WHERE name LIKE 'J%' AND age > 25;

-- 优化前：在存储引擎层只能使用name条件，age条件在Server层过滤
-- 优化后：age条件也在存储引擎层过滤（索引下推）

前缀索引

sql 
-- 对于长字符串字段，使用前缀索引
CREATE INDEX idx_email_prefix ON users(email(10));

-- 选择合适的前缀长度
SELECT 
    COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS selectivity_5,
    COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS selectivity_10,
    COUNT(DISTINCT LEFT(email, 15)) / COUNT(*) AS selectivity_15
FROM users;
-- 对于长字符串字段，使用前缀索引
CREATE INDEX idx_email_prefix ON users(email(10));

-- 选择合适的前缀长度
SELECT 
    COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS selectivity_5,
    COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS selectivity_10,
    COUNT(DISTINCT LEFT(email, 15)) / COUNT(*) AS selectivity_15
FROM users;

索引设计原则

1. 选择性原则

sql 
-- 计算字段选择性
SELECT 
    COUNT(DISTINCT column_name) / COUNT(*) AS selectivity
FROM table_name;

-- 选择性越高，索引效果越好
-- 选择性 = 不重复值数量 / 总记录数
-- 计算字段选择性
SELECT 
    COUNT(DISTINCT column_name) / COUNT(*) AS selectivity
FROM table_name;

-- 选择性越高，索引效果越好
-- 选择性 = 不重复值数量 / 总记录数

2. 频率原则

sql 
-- 为经常出现在WHERE、ORDER BY、GROUP BY中的字段创建索引
CREATE INDEX idx_user_status ON users(status);        -- WHERE条件
CREATE INDEX idx_order_date ON orders(order_date);    -- ORDER BY
CREATE INDEX idx_sales_region ON sales(region);       -- GROUP BY
-- 为经常出现在WHERE、ORDER BY、GROUP BY中的字段创建索引
CREATE INDEX idx_user_status ON users(status);        -- WHERE条件
CREATE INDEX idx_order_date ON orders(order_date);    -- ORDER BY
CREATE INDEX idx_sales_region ON sales(region);       -- GROUP BY

3. 复合索引设计

sql 
-- 复合索引字段顺序：选择性高的字段在前
CREATE INDEX idx_user_status_city_age ON users(status, city, age);

-- 考虑查询模式
-- 如果经常查询 WHERE status = ? AND city = ?
-- 那么 (status, city) 的顺序比 (city, status) 更好
-- 复合索引字段顺序：选择性高的字段在前
CREATE INDEX idx_user_status_city_age ON users(status, city, age);

-- 考虑查询模式
-- 如果经常查询 WHERE status = ? AND city = ?
-- 那么 (status, city) 的顺序比 (city, status) 更好

索引监控和分析

查看索引使用情况

sql 
-- 查看索引统计信息
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    INDEX_NAME,
    CARDINALITY,
    SUB_PART,
    NULLABLE
FROM information_schema.STATISTICS 
WHERE TABLE_SCHEMA = 'your_database';

-- 查看索引大小
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    ROUND(STAT_VALUE * @@innodb_page_size / 1024 / 1024, 2) AS 'Size(MB)'
FROM mysql.innodb_index_stats 
WHERE STAT_NAME = 'size' 
AND DATABASE_NAME = 'your_database';
-- 查看索引统计信息
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    INDEX_NAME,
    CARDINALITY,
    SUB_PART,
    NULLABLE
FROM information_schema.STATISTICS 
WHERE TABLE_SCHEMA = 'your_database';

-- 查看索引大小
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    ROUND(STAT_VALUE * @@innodb_page_size / 1024 / 1024, 2) AS 'Size(MB)'
FROM mysql.innodb_index_stats 
WHERE STAT_NAME = 'size' 
AND DATABASE_NAME = 'your_database';

分析慢查询

sql 
-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

-- 分析执行计划
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE name = 'John' AND age > 25;

-- 关键指标：
-- type: 连接类型（const > eq_ref > ref > range > index > ALL）
-- key: 使用的索引
-- rows: 扫描的行数
-- Extra: 额外信息
-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

-- 分析执行计划
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE name = 'John' AND age > 25;

-- 关键指标：
-- type: 连接类型（const > eq_ref > ref > range > index > ALL）
-- key: 使用的索引
-- rows: 扫描的行数
-- Extra: 额外信息

索引优化工具

sql 
-- 使用pt-index-usage分析索引使用情况
-- pt-index-usage --host=localhost --user=root --password=xxx database_name

-- 使用pt-duplicate-key-checker检查重复索引
-- pt-duplicate-key-checker --host=localhost --user=root --password=xxx
-- 使用pt-index-usage分析索引使用情况
-- pt-index-usage --host=localhost --user=root --password=xxx database_name

-- 使用pt-duplicate-key-checker检查重复索引
-- pt-duplicate-key-checker --host=localhost --user=root --password=xxx

索引维护

重建索引

sql 
-- 重建索引（删除并重新创建）
DROP INDEX idx_user_name ON users;
CREATE INDEX idx_user_name ON users(name);

-- 或者使用ALTER TABLE
ALTER TABLE users DROP INDEX idx_user_name, ADD INDEX idx_user_name(name);
-- 重建索引（删除并重新创建）
DROP INDEX idx_user_name ON users;
CREATE INDEX idx_user_name ON users(name);

-- 或者使用ALTER TABLE
ALTER TABLE users DROP INDEX idx_user_name, ADD INDEX idx_user_name(name);

在线DDL

sql 
-- MySQL 5.6+支持在线DDL
ALTER TABLE users ADD INDEX idx_user_email(email), ALGORITHM=INPLACE, LOCK=NONE;

-- ALGORITHM选项：
-- COPY: 创建临时表，复制数据（阻塞）
-- INPLACE: 原地修改（较少阻塞）

-- LOCK选项：
-- NONE: 允许并发读写
-- SHARED: 允许并发读，阻塞写
-- EXCLUSIVE: 阻塞所有操作
-- MySQL 5.6+支持在线DDL
ALTER TABLE users ADD INDEX idx_user_email(email), ALGORITHM=INPLACE, LOCK=NONE;

-- ALGORITHM选项：
-- COPY: 创建临时表，复制数据（阻塞）
-- INPLACE: 原地修改（较少阻塞）

-- LOCK选项：
-- NONE: 允许并发读写
-- SHARED: 允许并发读，阻塞写
-- EXCLUSIVE: 阻塞所有操作

索引统计信息更新

sql 
-- 手动更新统计信息
ANALYZE TABLE users;

-- 自动更新配置
SET GLOBAL innodb_stats_auto_recalc = ON;
SET GLOBAL innodb_stats_persistent = ON;
-- 手动更新统计信息
ANALYZE TABLE users;

-- 自动更新配置
SET GLOBAL innodb_stats_auto_recalc = ON;
SET GLOBAL innodb_stats_persistent = ON;

常见问题和解决方案

1. 索引失效场景

sql 
-- 函数操作导致索引失效
SELECT * FROM users WHERE UPPER(name) = 'JOHN';  -- 索引失效
SELECT * FROM users WHERE name = 'John';         -- 使用索引

-- 类型转换导致索引失效
SELECT * FROM users WHERE id = '123';  -- 可能导致索引失效
SELECT * FROM users WHERE id = 123;    -- 使用索引

-- 前导模糊查询
SELECT * FROM users WHERE name LIKE '%John';  -- 索引失效
SELECT * FROM users WHERE name LIKE 'John%';  -- 使用索引
-- 函数操作导致索引失效
SELECT * FROM users WHERE UPPER(name) = 'JOHN';  -- 索引失效
SELECT * FROM users WHERE name = 'John';         -- 使用索引

-- 类型转换导致索引失效
SELECT * FROM users WHERE id = '123';  -- 可能导致索引失效
SELECT * FROM users WHERE id = 123;    -- 使用索引

-- 前导模糊查询
SELECT * FROM users WHERE name LIKE '%John';  -- 索引失效
SELECT * FROM users WHERE name LIKE 'John%';  -- 使用索引

2. 索引选择错误

sql 
-- 查看MySQL选择的索引
EXPLAIN SELECT * FROM users WHERE name = 'John' AND age = 25;

-- 强制使用特定索引
SELECT * FROM users FORCE INDEX(idx_user_name) WHERE name = 'John' AND age = 25;

-- 忽略特定索引
SELECT * FROM users IGNORE INDEX(idx_user_age) WHERE name = 'John' AND age = 25;
-- 查看MySQL选择的索引
EXPLAIN SELECT * FROM users WHERE name = 'John' AND age = 25;

-- 强制使用特定索引
SELECT * FROM users FORCE INDEX(idx_user_name) WHERE name = 'John' AND age = 25;

-- 忽略特定索引
SELECT * FROM users IGNORE INDEX(idx_user_age) WHERE name = 'John' AND age = 25;

3. 过多索引的问题

sql 
-- 检查表的索引数量
SELECT 
    TABLE_NAME,
    COUNT(*) AS index_count
FROM information_schema.STATISTICS 
WHERE TABLE_SCHEMA = 'your_database'
GROUP BY TABLE_NAME
ORDER BY index_count DESC;

-- 删除不必要的索引
DROP INDEX idx_unused ON table_name;
-- 检查表的索引数量
SELECT 
    TABLE_NAME,
    COUNT(*) AS index_count
FROM information_schema.STATISTICS 
WHERE TABLE_SCHEMA = 'your_database'
GROUP BY TABLE_NAME
ORDER BY index_count DESC;

-- 删除不必要的索引
DROP INDEX idx_unused ON table_name;

最佳实践

  1. 合理设计索引：根据查询模式设计索引，避免过度索引
  2. 定期监控：监控索引使用情况，删除无用索引
  3. 复合索引优化：遵循最左前缀原则，合理安排字段顺序
  4. 避免索引失效：注意函数、类型转换等导致的索引失效
  5. 使用覆盖索引：减少回表查询，提高查询性能
  6. 前缀索引：对于长字符串字段，考虑使用前缀索引
  7. 在线DDL：使用在线DDL减少对业务的影响

总结

索引是MySQL性能优化的重要手段，正确使用索引能够显著提高查询性能。但索引也有成本，需要在查询性能和维护成本之间找到平衡。通过理解索引原理、掌握优化技巧、定期监控分析，可以设计出高效的索引方案。