文章目录MySQL索引核心分类系统性知识体系一、索引分类整体框架二、物理存储维度聚簇索引 vs 非聚簇索引2.1 聚簇索引Clustered Index2.2 非聚簇索引Non-Clustered Index2.3 聚簇索引 vs 非聚簇索引对比三、功能层级维度主键索引 vs 二级索引3.1 主键索引Primary Key Index3.2 二级索引Secondary Index四、字段数量维度单列索引 vs 联合索引4.1 单列索引Single-Column Index4.2 联合索引Composite Index五、优化用途维度覆盖索引 vs 前缀索引5.1 覆盖索引Covering Index5.2 前缀索引Prefix Index六、索引分类关系总结6.1 核心索引类型对比6.2 交叉关系图6.3 索引选型决策树6.4 常见误区澄清七、索引设计最佳实践7.1 索引设计原则7.2 索引使用注意事项7.3 常见索引失效场景八、总结MySQL索引核心分类系统性知识体系一、索引分类整体框架MySQL索引可以从四个核心维度进行分类不同维度之间存在交叉关系分类维度核心类型本质区别物理存储维度聚簇索引、非聚簇索引索引与数据是否存储在一起功能层级维度主键索引、二级索引是否作为数据组织的主键字段数量维度单列索引、联合索引索引包含的字段数量优化用途维度覆盖索引、前缀索引针对特定场景的优化手段关键关系InnoDB中主键索引就是聚簇索引所有非聚簇索引都属于二级索引单列索引和联合索引既可以是主键索引也可以是二级索引覆盖索引和前缀索引是特殊的二级索引二、物理存储维度聚簇索引 vs 非聚簇索引这是理解InnoDB和MyISAM性能差异的最核心分类决定了数据的组织方式和查询流程。2.1 聚簇索引Clustered Index定义聚簇索引不是一种特殊的索引类型而是数据本身按某个索引键值的顺序物理存储的方式。B树的叶子节点直接存储完整的数据行。核心特点索引即数据找到索引就找到了数据一张表有且仅有一个聚簇索引数据按索引键值有序物理存储叶子节点包含主键值、事务ID、回滚指针、所有用户列InnoDB聚簇索引选取规则优先级从高到低显式定义的PRIMARY KEY主键第一个所有列都NOT NULL的UNIQUE索引自动生成的6字节隐藏row_idGEN_CLUST_INDEX优点主键查询极快只需一次B树查找范围查询效率极高数据物理有序缓存命中率高数据与索引一起缓存缺点插入速度严重依赖插入顺序随机主键会导致页分裂更新主键列代价极高会导致数据行移动二级索引查询需要回表两次B树查找2.2 非聚簇索引Non-Clustered Index定义索引与数据行分开存储B树的叶子节点不存储完整数据只存储指向数据的指针。InnoDB中的非聚簇索引叶子节点存储索引列值 对应记录的主键值所有非聚簇索引都称为二级索引/辅助索引查询流程二级索引 → 主键值 → 聚簇索引回表MyISAM中的非聚簇索引所有索引包括主键索引都是非聚簇索引叶子节点存储数据行的物理地址索引文件.MYI与数据文件.MYD分离优点维护成本低于聚簇索引占用空间通常更小可以创建多个满足不同查询需求缺点非覆盖查询需要额外的I/O操作回表范围查询效率低于聚簇索引2.3 聚簇索引 vs 非聚簇索引对比特性InnoDB聚簇索引InnoDB非聚簇索引MyISAM所有索引叶子节点内容完整数据行索引列主键值数据物理地址数量1个多个多个主键查询1次I/O2次I/O回表2次I/O数据存储与索引一体与索引分离与索引分离范围查询极快较快一般插入性能依赖主键顺序较好好三、功能层级维度主键索引 vs 二级索引3.1 主键索引Primary Key Index定义基于表的主键创建的索引是一种约束索引的组合。核心特点唯一且非空主键列不允许重复值和NULL值一张表只能有一个主键索引InnoDB中主键索引自动成为聚簇索引隐式创建NOT NULL约束和UNIQUE约束最佳实践优先使用自增整数作为主键避免页分裂提升插入性能避免使用UUID、业务字段作为主键随机写性能差更新代价高保持主键尽可能短减少二级索引的存储空间3.2 二级索引Secondary Index定义除了主键索引之外的所有索引都称为二级索引也叫辅助索引。常见的二级索引类型普通索引INDEX/KEY最基本的索引类型无唯一性限制唯一索引UNIQUE索引列值必须唯一但允许有多个NULL值联合索引由多个列组合而成的索引前缀索引只对字符串的前n个字符建立索引全文索引FULLTEXT用于全文搜索的特殊索引核心特点一张表可以有多个二级索引InnoDB中二级索引叶子节点存储主键值查询时通常需要回表到聚簇索引获取完整数据唯一索引 vs 普通索引唯一索引额外保证了数据的唯一性插入/更新时唯一索引需要检查唯一性性能略低唯一索引的查询性能与普通索引基本相同四、字段数量维度单列索引 vs 联合索引4.1 单列索引Single-Column Index定义只包含一个字段的索引。适用场景查询条件中经常单独使用该字段该字段的选择性很高不重复值多简单的等值查询或范围查询示例CREATEINDEXidx_user_nameONusers(name);4.2 联合索引Composite Index定义由两个或多个字段组合而成的索引。核心原理B树的键值由多个字段按定义顺序拼接构成排序规则先按第一列升序第一列相同时再按第二列升序依此类推类似于电话簿的编排方式先按姓氏再按名字最左前缀匹配原则查询条件必须从联合索引定义的最左侧字段开始连续匹配才能有效触发索引。对于索引(a,b,c)有效的查询组合a ?a ? AND b ?a ? AND b ? AND c ?a ?或a BETWEEN ? AND ?a ? AND b ?或a ? AND b BETWEEN ? AND ?失效场景b ?缺少第一列ab ? AND c ?缺少第一列aa ? AND c ?缺少中间列bc无法使用索引范围查找MySQL 8.0.13新特性索引跳跃扫描Index Skip Scan在某些条件下即使查询条件不包含最左列优化器也能使用联合索引适用于第一列基数较低的情况联合索引设计原则最左前缀优先将查询中最常用的字段放在最左边选择性高优先当不需要考虑排序和分组时将选择性最高的列放在前面字段数控制联合索引的字段数不超过3-4个覆盖查询优先尽量让索引包含查询所需的所有字段覆盖索引示例CREATEINDEXidx_user_age_statusONusers(age,status);-- 有效查询SELECT*FROMusersWHEREage25;SELECT*FROMusersWHEREage25ANDstatusactive;-- 无效查询SELECT*FROMusersWHEREstatusactive;五、优化用途维度覆盖索引 vs 前缀索引5.1 覆盖索引Covering Index定义如果一条SQL查询需要的所有列SELECT、WHERE、ORDER BY、GROUP BY涉及到的列都存在于索引中MySQL可以直接从索引返回结果完全不用回表这样的索引称为覆盖索引。核心优势避免回表减少B树查找次数从2次变为1次减少I/O操作索引体积远小于全表数据提升缓存命中率索引页比数据页更容易留在内存中在EXPLAIN输出的Extra列中标志是Using index设计原则优先覆盖高频查询避免过度索引不要为了覆盖而添加过多字段利用联合索引实现覆盖将查询需要的字段都加入联合索引示例-- 创建联合索引CREATEINDEXidx_orders_user_status_amountONorders(user_id,status,amount);-- 覆盖索引查询无需回表SELECTamountFROMordersWHEREuser_id100ANDstatuspaid;-- EXPLAIN Extra: Using index-- 非覆盖索引查询需要回表SELECT*FROMordersWHEREuser_id100ANDstatuspaid;-- EXPLAIN Extra: 无Using index5.2 前缀索引Prefix Index定义只对字符串类型字段的前n个字符建立索引而不是对整个字符串建立索引。核心优势显著减少索引占用的磁盘空间提升索引查询速度索引页可以存放更多索引项降低索引维护成本适用场景字符串字段较长如VARCHAR(255)字符串的前n个字符已经具有足够的选择性不需要对整个字符串进行排序或分组设计原则选择合适的前缀长度在选择性和空间之间取得平衡计算选择性SELECT COUNT(DISTINCT LEFT(column, n)) / COUNT(*) FROM table;目标是让前缀的选择性尽可能接近完整列的选择性限制无法用于ORDER BY和GROUP BY无法用于覆盖索引不支持**LIKE ‘%xxx’**查询只能用于LIKE ‘xxx%’示例-- 对email字段的前10个字符建立前缀索引CREATEINDEXidx_user_email_prefixONusers(email(10));-- 有效查询SELECT*FROMusersWHEREemailLIKEjohn%;-- 无效查询SELECT*FROMusersWHEREemailLIKE%example.com;六、索引分类关系总结6.1 核心索引类型对比索引类型存储特点查询性能维护成本适用场景主键索引聚簇存储叶子节点是完整数据最高高主键更新代价大主键查询、范围查询、排序普通二级索引非聚簇叶子节点是主键中等需要回表中等单条件查询联合索引非聚簇多列组合键高可覆盖较高多条件查询、覆盖查询唯一索引非聚簇保证唯一性中等需要回表较高唯一性检查需要保证数据唯一性的列覆盖索引包含查询所有列极高无需回表较高高频查询前缀索引只索引字符串前缀中等低长字符串列6.2 交叉关系图索引 ├── 按物理存储 │ ├── 聚簇索引InnoDB主键索引 │ └── 非聚簇索引所有二级索引 ├── 按功能层级 │ ├── 主键索引通常是聚簇索引 │ └── 二级索引非聚簇索引 │ ├── 普通索引 │ ├── 唯一索引 │ ├── 联合索引 │ ├── 前缀索引 │ └── 全文索引 ├── 按字段数量 │ ├── 单列索引 │ └── 联合索引 └── 按优化用途 ├── 覆盖索引特殊的二级索引 └── 前缀索引特殊的二级索引6.3 索引选型决策树是否是主键查询 ├─ 是 → 使用主键索引 └─ 否 → 是否是多条件查询 ├─ 是 → 是否经常一起查询 │ ├─ 是 → 创建联合索引 │ └─ 否 → 分别创建单列索引 └─ 否 → 列是否是长字符串 ├─ 是 → 前缀选择性是否足够 │ ├─ 是 → 创建前缀索引 │ └─ 否 → 考虑全文索引或其他方案 └─ 否 → 是否需要保证唯一性 ├─ 是 → 创建唯一索引 └─ 否 → 创建普通索引 → 最后是否可以设计成覆盖索引 ├─ 是 → 优化为覆盖索引 └─ 否 → 保持原索引6.4 常见误区澄清误区主键索引就是聚簇索引正确在InnoDB中是对的在MyISAM中主键索引也是非聚簇索引误区一张表可以有多个聚簇索引正确一张表只能有一个聚簇索引因为数据只能按一种方式物理排序误区联合索引的字段顺序不影响性能正确字段顺序至关重要必须遵循最左前缀匹配原则误区覆盖索引是一种特殊的索引类型正确覆盖索引不是一种独立的索引类型而是索引的一种使用方式误区前缀索引越长越好正确前缀长度应在选择性和空间之间取得平衡过长会失去前缀索引的优势七、索引设计最佳实践选择性原则优先为选择性高的字段建立索引最小化原则索引列的长度尽可能小平衡原则平衡查询性能与写入性能避免过度索引联合索引优先对于多列查询优先使用联合索引而非多个单列索引覆盖索引优先尽量让高频查询使用覆盖索引避免回表避免索引失效不要在索引列上使用函数、运算、隐式类型转换主键设计使用自增整数作为主键避免UUID和业务主键7.1 索引设计原则优先考虑联合索引一个联合索引可以替代多个单列索引减少索引数量覆盖索引优先尽量让索引包含查询所需的所有列避免回表操作选择性高的列优先选择性越高索引的过滤效果越好避免创建过多索引过多的索引会降低插入、更新和删除的性能避免在低选择性列上创建索引如性别、状态等只有少数几个值的列避免在频繁更新的列上创建索引更新索引的代价很高使用自增整数作为主键避免页分裂提高插入性能7.2 索引使用注意事项避免在索引列上使用函数或表达式会导致索引失效避免使用!或操作符会导致索引失效避免使用IS NULL或IS NOT NULL会导致索引失效避免使用LIKE %xxx’查询会导致索引失效避免使用OR连接多个条件如果OR两边的条件有一个没有索引会导致全表扫描注意联合索引的最左前缀原则查询条件必须从最左列开始定期分析和优化索引删除无用的索引优化低效的索引7.3 常见索引失效场景在索引列上使用函数WHERE YEAR(create_time) 2023在索引列上进行计算WHERE age 1 20使用!或操作符WHERE status! 1使用IS NULL或IS NOT NULLWHERE name IS NULL使用LIKE %xxx’查询WHERE name LIKE %张三字符串不加引号WHERE phone 13800138000phone是VARCHAR类型联合索引不满足最左前缀原则WHERE b2 AND c3索引是idx_a_b_c使用OR连接多个条件WHERE a1 OR b2b列没有索引八、总结索引是数据库性能优化的核心理解各类索引的特点和适用场景是设计高效数据库的关键。聚簇索引决定了数据的物理存储顺序查询速度最快但插入和更新代价高二级索引是除主键索引之外的所有索引查询时通常需要回表操作联合索引基于多个列创建遵循最左前缀原则可以替代多个单列索引覆盖索引包含了查询所需的所有列避免了回表操作性能极高前缀索引只对字符串的前N个字符创建索引可以减少索引大小在实际应用中应该根据业务查询特点合理设计和使用索引避免创建过多或无用的索引定期分析和优化索引以达到最佳的数据库性能。