java面试必问14：MySQL 索引类型：从基础到优化，面试官给你点赞

MySQL 索引类型从基础到优化一篇讲透面试官“MySQL 有哪些索引类型”你“主键索引、唯一索引、普通索引、复合索引、全文索引。索引能大大加快查询速度但会降低增删改的性能。”面试官“那复合索引的最左前缀原则是什么为什么会有这个原则”你“……”很多人能列出索引类型但一追问“什么时候索引会失效”“如何设计高效索引”就含糊了。本文从索引的底层结构出发讲透各种索引的特点和使用场景。一、索引是什么索引是数据库中的一种数据结构类似于书的目录可以快速定位数据所在的位置避免全表扫描。MySQL 中常用的索引结构是BTreeInnoDB 存储引擎。索引虽然能加速查询但需要额外的存储空间并且在数据插入、更新、删除时需要同步维护因此会降低写入性能。二、五种索引类型详解1. 主键索引PRIMARY KEY特点唯一且非空一个表只能有一个主键索引。底层聚簇索引Clustered Index叶子节点直接存储整行数据。创建CREATETABLEuser(idINTPRIMARYKEY,-- 主键索引nameVARCHAR(50));-- 或单独添加ALTERTABLEuserADDPRIMARYKEY(id);注意InnoDB 中如果没有显式定义主键会自动选择一个唯一非空索引作为聚簇索引如果也没有则隐式生成一个 6 字节的 rowid 作为主键。建议每个表都显式指定主键。2. 唯一索引UNIQUE特点索引列的值必须唯一但允许 NULL可以有多个 NULL。作用保证数据唯一性同时加速查询。创建CREATETABLEuser(idINTPRIMARYKEY,emailVARCHAR(100)UNIQUE-- 唯一索引);-- 或CREATEUNIQUEINDEXidx_emailONuser(email);3. 普通索引INDEX / KEY特点最基本索引无唯一性约束允许重复值。作用加速查询是最常用的索引类型。创建CREATEINDEXidx_nameONuser(name);4. 复合索引Composite Index特点在多个列上建立一个索引遵循最左前缀原则。作用同时加速对多个列的查询尤其适合WHERE条件中包含多个字段的场景。创建CREATEINDEXidx_name_ageONuser(name,age);查询WHERE name 张三 AND age 25可以利用该索引但WHERE age 25不能使用该索引因为没有使用索引的最左列name。5. 全文索引FULLTEXT特点用于全文搜索类似搜索引擎仅支持MyISAM和InnoDBMySQL 5.6。作用对文本内容进行关键词匹配比LIKE %keyword%高效得多。创建CREATETABLEarticle(idINTPRIMARYKEY,contentTEXT,FULLTEXT(content));-- 查询SELECT*FROMarticleWHEREMATCH(content)AGAINST(数据库优化);注意全文索引有停用词、最小词长度等限制适合大文本字段的搜索场景。三、索引的优缺点优点缺点大大加快SELECT查询速度占用额外磁盘空间加速ORDER BY、GROUP BY降低INSERT、UPDATE、DELETE速度需维护索引唯一索引能保证数据唯一性索引设计不合理会导致查询优化器选错索引权衡索引不是越多越好。一般建议对查询频繁、区分度高的列建索引避免在低基数列如性别或频繁更新的列上建索引。四、复合索引的最左前缀原则重点复合索引(a, b, c)实际上相当于创建了三个索引(a)、(a, b)、(a, b, c)。查询时只有从索引的最左列开始匹配才能使用索引。能用索引的情况WHERE a 1WHERE a 1 AND b 2WHERE a 1 AND b 2 AND c 3WHERE a 1 AND c 3只用到 ac 无法使用但 a 可过滤一部分不能用索引的情况WHERE b 2没有最左列 aWHERE b 2 AND c 3范围查询的影响WHERE a 1 AND b 2 AND c 3a 和 b 能用到索引c 用不到因为 b 是范围查询后面的列停止匹配。设计建议将区分度高的列放在复合索引左侧等值查询的列优先于范围查询的列。五、索引失效的常见场景场景示例原因对索引列使用函数WHERE YEAR(create_time) 2024无法使用索引应改为范围查询隐式类型转换WHERE phone [phone]phone 是 varchar类型不匹配索引失效LIKE 以通配符开头WHERE name LIKE %张三无法使用索引OR 前后未全索引WHERE a 1 OR b 2只有 a 有索引需要全表扫描使用!或WHERE status ! 0非等值查询一般不用索引使用IS NULL或IS NOT NULL某些情况下失效取决于版本和数据分布-六、如何评估索引是否有效使用EXPLAIN命令查看执行计划EXPLAINSELECT*FROMuserWHEREname张三;关键列typeconstrefrangeindexALL好到差possible_keys可能使用的索引key实际使用的索引rows预估扫描行数ExtraUsing index表示覆盖索引不回表Using where表示需要过滤七、常见面试追问Q1主键索引和唯一索引的区别主键索引不允许 NULL唯一索引允许 NULL多个 NULL。一个表只能有一个主键可以有多个唯一索引。主键通常是聚簇索引InnoDB唯一索引是辅助索引。Q2为什么推荐使用自增整数做主键插入时顺序写入BTree 页分裂少性能高。UUID 或随机字符串作为主键会导致随机插入页分裂频繁且占用空间大。Q3什么是覆盖索引如果一个索引包含了查询所需的所有列即SELECT的列都在索引中那么不需要回表查询数据行称为覆盖索引。例如CREATEINDEXidx_nameONuser(name);SELECTnameFROMuserWHEREname张三;-- 覆盖索引不回表Q4索引下推Index Condition Pushdown是什么MySQL 5.6 引入的优化在索引遍历过程中对索引中包含的字段先做条件过滤减少回表次数。例如INDEX(a, b)查询WHERE a 1 AND b 2没有 ICP 时会先根据 a1 回表再过滤 b有 ICP 时直接在索引中判断 b2只回表符合的数据。Q5联合索引中字段顺序如何设计区分度高的列放在左侧。等值查询的列放在左侧范围查询的列放在右侧。经常用于排序的列可以考虑加入索引ORDER BY也能利用索引顺序。八、总结索引类型特点适用场景主键索引唯一、非空、聚簇每张表必备作为行标识唯一索引值唯一可 NULL保证字段唯一性如邮箱、手机号普通索引无约束加速查询最常用复合索引多列组合最左前缀多条件查询、排序全文索引关键词匹配大文本搜索如文章、评论一句话记住索引设计等值左前缀范围右靠后覆盖索引少回表函数运算全失效。索引是把双刃剑合理的索引能让查询飞起来滥用索引会让写入慢如牛。理解索引类型和原理是 MySQL 优化的第一步。希望这篇文章能帮你彻底掌握 MySQL 索引的相关知识从容应对面试和实际调优欢迎继续讨论。我的个人简介最后有一段内容感兴趣的朋友可以去找找看。那里有我日常分享的技术深度 解析和职场避坑指南期待与您继续交流。