MySQL索引
MySQL索引详解
一、索引是什么？
索引是数据库中的一种数据结构，类似于书籍的目录，它可以帮助数据库快速定位和访问表中的数据，而不需要扫描整个表。
二、MySQL索引分类
1.按数据结构分类
| 索引类型 | 存储引擎 | 特点 | 适用场景 |
|---|---|---|---|
| B+Tree索引 | InnoDB、MyISAM | 默认索引，平衡树结构 | 范围查询、排序、等值查询 |
| Hash索引 | Memory/Heap | 哈希表结构，O(1)查找 | 等值查询（不支持范围查询） |
| Full-Text索引 | InnoDB、MyISAM | 全文搜索 | 文本字段的模糊搜索 |
| R-Tree索引 | MyISAM | 空间数据索引 | GIS地理数据 |
2.按逻辑功能分类
主键索引 (Primary Key)
CREATETABLEusers(idINTPRIMARYKEY,-- 主键索引nameVARCHAR(50));- 每张表只能有一个
- 不允许NULL值
- 自动创建聚簇索引（InnoDB）
唯一索引 (Unique Index)
CREATEUNIQUEINDEXidx_emailONusers(email);- 确保列值唯一
- 允许NULL值（但只能有一个NULL）
- 可以有多个
普通索引 (Normal Index)
CREATEINDEXidx_nameONusers(name);- 最基本的索引类型
- 仅用于加速查询
全文索引 (Full-Text Index)
CREATEFULLTEXTINDEXidx_contentONarticles(content);- 用于全文搜索
- 支持MATCH AGAINST语法
组合索引 (Composite Index)
CREATEINDEXidx_name_ageONusers(name,age);- 多列组合的索引
- 最左前缀原则
空间索引 (Spatial Index)
CREATESPATIALINDEXidx_locationONplaces(location);- 用于地理空间数据
3.按物理存储分类
聚簇索引 (Clustered Index)
- InnoDB中主键就是聚簇索引
- 数据行和索引存储在一起
- 一个表只有一个
非聚簇索引 (Non-Clustered Index)
- 索引和数据分开存储
- MyISAM默认都是非聚簇索引
- InnoDB的二级索引
三、常用索引类型
最常用的索引：
- B+Tree索引（95%以上场景）
- 组合索引（优化多条件查询）
- 唯一索引（保证数据唯一性）
- 主键索引（每张表必须有）
四、索引生效场景（什么时候有效）
1. 全值匹配
-- 索引 idx_name_age(name, age)SELECT*FROMusersWHEREname='张三'ANDage=25;-- ✅ 生效2. 最左前缀匹配
-- 索引 idx_name_age_city(name, age, city)SELECT*FROMusersWHEREname='张三';-- ✅ 生效SELECT*FROMusersWHEREname='张三'ANDage=25;-- ✅ 生效SELECT*FROMusersWHEREage=25;-- ❌ 不生效（跳过了name）3. 范围查询（部分生效）
-- 索引 idx_name_age(name, age)SELECT*FROMusersWHEREname='张三'ANDage>20;-- ✅ 生效SELECT*FROMusersWHEREname>'张三';-- ✅ 生效4. 覆盖索引
-- 索引 idx_name_age(name, age)SELECTname,ageFROMusersWHEREname='张三';-- ✅ 生效（不需要回表）5. 排序操作
-- 索引 idx_name_age(name, age)SELECT*FROMusersORDERBYname,age;-- ✅ 生效SELECT*FROMusersORDERBYnameASC,ageDESC;-- ❌ 不生效（混合排序）6. 分组操作
-- 索引 idx_name_age(name, age)SELECTname,COUNT(*)FROMusersGROUPBYname;-- ✅ 生效五、索引失效场景（什么时候无效）
1. 违反最左前缀原则
-- 索引 idx_name_age(name, age)SELECT*FROMusersWHEREage=25;-- ❌ 不生效SELECT*FROMusersWHEREage=25ANDname='张三';-- ✅ 生效（优化器会调整顺序）2. 在索引列上运算或函数
-- 索引 idx_name(name)SELECT*FROMusersWHERELEFT(name,1)='张';-- ❌ 不生效SELECT*FROMusersWHEREYEAR(create_time)=2024;-- ❌ 不生效3. 使用不等于(!=, <>)
SELECT*FROMusersWHEREname!='张三';-- ❌ 不生效（全表扫描更优）4. 使用IS NULL/IS NOT NULL（特殊情况）
SELECT*FROMusersWHEREnameISNULL;-- ✅ 可能生效SELECT*FROMusersWHEREnameISNOTNULL;-- ❌ 通常不生效5. LIKE以通配符开头
SELECT*FROMusersWHEREnameLIKE'%张三%';-- ❌ 不生效SELECT*FROMusersWHEREnameLIKE'张三%';-- ✅ 生效6. 类型转换
-- 假设phone是varchar类型，索引 idx_phone(phone)SELECT*FROMusersWHEREphone=13800138000;-- ❌ 不生效（隐式类型转换）SELECT*FROMusersWHEREphone='13800138000';-- ✅ 生效7. OR条件部分无索引
-- name有索引，age无索引SELECT*FROMusersWHEREname='张三'ORage=25;-- ❌ 不生效8. 数据量小时
-- 表只有100行数据SELECT*FROMsmall_tableWHEREname='test';-- ❌ 可能不生效（全表扫描更快）9. 统计信息不准确
-- 当索引统计信息过时，优化器可能选择全表扫描ANALYZETABLEusers;-- 更新统计信息六、索引设计最佳实践
创建索引的黄金法则：
-- 1. 选择性高的列建索引CREATEINDEXidx_emailONusers(email);-- email唯一性高-- 2. 常用查询条件组合建索引CREATEINDEXidx_queryONorders(user_id,status,create_time);-- 3. 覆盖索引设计CREATEINDEXidx_coveringONusers(name,age,email);-- 查询：SELECT name, age, email FROM users WHERE name = '张三';索引使用建议：
- 不要过度索引：每个索引都有维护成本
- 更新频繁的列：谨慎建索引
- 小表不建议建索引
- 避免冗余索引
- 定期分析索引使用情况
-- 查看索引使用情况SHOWINDEXFROMtable_name;-- 查看未使用的索引SELECT*FROMsys.schema_unused_indexes;七、性能诊断工具
1. EXPLAIN分析
EXPLAINSELECT*FROMusersWHEREname='张三';2. 查看索引统计
-- 查看索引区分度SELECTINDEX_NAME,CARDINALITY,TABLE_ROWS,ROUND(CARDINALITY/TABLE_ROWS*100,2)asselectivityFROMinformation_schema.STATISTICSWHERETABLE_NAME='users';3. 慢查询日志
-- 开启慢查询SETGLOBALslow_query_log=ON;SETGLOBALlong_query_time=1;八、常见问题排查
索引失效排查步骤：
- 使用EXPLAIN分析执行计划
- 检查WHERE条件是否符合最左前缀
- 检查是否有类型转换
- 检查是否使用函数或计算
- 检查统计信息是否准确
- 检查数据量是否太小
索引优化示例：
-- 问题查询SELECT*FROMordersWHEREDATE(create_time)='2024-01-01'-- ❌ 索引失效ANDstatus=1;-- 优化后SELECT*FROMordersWHEREcreate_time>='2024-01-01'ANDcreate_time<'2024-01-02'-- ✅ 索引生效ANDstatus=1;-- 创建合适索引CREATEINDEXidx_time_statusONorders(create_time,status);记住原则：索引不是越多越好，合适的索引才是最好的。定期审查和优化索引是DBA的重要工作。