一文搞懂mysql索引，让你的系统飞起来

一、索引的基本概念

• 定义：索引是一种数据结构，用于快速查找表中的记录，类似于书籍的目录
• 作用：加速查询速度，降低数据库 IO 成本
• 代价：占用额外存储空间，降低写入（INSERT/UPDATE/DELETE）性能

二、索引的类型

1. 按数据结构划分

• B + 树索引：MySQL 默认索引类型，适用于范围查询
  • 所有数据存储在叶子节点，形成有序链表
  • 支持等值查询、范围查询、排序操作
• 哈希索引：仅 Memory 引擎支持，适用于精确匹配
  • 查找速度快，但不支持范围查询和排序
• Full-text 索引：全文索引，适用于文本内容的关键词搜索
  sql

   

   

   

   

   

  CREATE FULLTEXT INDEX idx_content ON articles(content);
  SELECT * FROM articles WHERE MATCH(content) AGAINST('database');
  

   
• R-tree 索引：用于地理空间数据类型

2. 按功能划分

• 主键索引（PRIMARY KEY）：唯一标识表中的记录
  sql

   

   

   

   

   

  ALTER TABLE users ADD PRIMARY KEY (id);
  

   
• 唯一索引（UNIQUE）：确保列值唯一，但允许 NULL 值
  sql

   

   

   

   

   

  CREATE UNIQUE INDEX idx_email ON users(email);
  

   
• 普通索引（INDEX）：最基本的索引，无唯一性约束
  sql

   

   

   

   

   

  CREATE INDEX idx_username ON users(username);
  

   
• 组合索引：多个列组合创建的索引，遵循 "最左前缀原则"
  sql

   

   

   

   

   

  CREATE INDEX idx_name_age ON users(name, age);
  

   
• 前缀索引：对字符列的前 n 个字符创建索引，节省空间
  sql

   

   

   

   

   

  CREATE INDEX idx_title ON articles(title(20));
  

   

三、索引的使用场景

适合创建索引的情况：

• 频繁出现在 WHERE 子句中的列
• 用于 JOIN 的列（关联查询）
• 用于排序（ORDER BY）和分组（GROUP BY）的列
• 区分度高的列（如身份证号，不建议为性别等区分度低的列建索引）

不适合创建索引的情况：

• 表数据量小（通常小于 1 万行）
• 频繁更新的列
• 很少查询的列
• 数据重复率高的列

四、索引失效的常见情况

1. 使用函数或表达式操作索引列
   sql

    

    

    

    

    

   SELECT * FROM users WHERE YEAR(birthday) = 1990; -- 索引失效
   

    
2. 使用不等于（!=, <>）、NOT IN、NOT EXISTS
3. 模糊查询以 % 开头
   sql

    

    

    

    

    

   SELECT * FROM users WHERE username LIKE '%john'; -- 索引失效
   

    
4. 组合索引不满足最左前缀原则
   sql

    

    

    

    

    

   -- 对(name, age)的组合索引
   SELECT * FROM users WHERE age = 25; -- 索引失效
   

    
5. 使用 OR 连接包含非索引列的条件
6. 隐式类型转换导致索引失效
   sql

    

    

    

    

    

   SELECT * FROM users WHERE phone = 13800138000; -- 如果phone是字符串类型，索引失效
   

    

五、索引优化技巧

1. 覆盖索引：查询的列都包含在索引中，避免回表查询
   sql

    

    

    

    

    

   -- 对(name, age)建立组合索引
   SELECT name, age FROM users WHERE name = 'john'; -- 只需查询索引，无需访问表数据
   

    
2. 合理设计组合索引顺序：将区分度高的列放在前面
3. 定期分析和优化索引：
   sql

    

    

    

    

    

   -- 分析索引使用情况
   SHOW INDEX FROM table_name;
   
   -- 优化表（重建索引）
   OPTIMIZE TABLE table_name;
   

    

    

    

    

    

    

    

    

    

    

    

    

    

    

    

    

    

    

    

    

    

    

    

    

   

    
4. 使用索引提示：当 MySQL 优化器选择不佳时
   sql

    

    

    

    

    

   SELECT * FROM users USE INDEX (idx_name) WHERE name = 'john';
   

    

    

    

    

    

    

    

    

    

    

    

    

    

    

    

    

   

    
5. 避免过度索引：每个额外索引都会影响写入性能

六、索引的底层原理

• InnoDB 的 B + 树索引分为聚簇索引和非聚簇索引
• 聚簇索引：叶子节点存储整行数据，一个表只有一个
• 非聚簇索引：叶子节点存储主键值，需要通过主键查找完整数据（回表）