文章目录一、什么是索引为什么要用它1. 通俗理解2. 索引的好处3. 索引的代价新手必知二、索引底层为什么 MySQL 选择 B树1. B树的核心特点InnoDB版2. InnoDB 两大索引类型聚簇索引 vs 二级索引1聚簇索引主键索引2二级索引非聚簇索引 / 普通索引3覆盖索引优化神器三、MySQL 索引的常见分类1. 按物理存储分2. 按字段数量分3. 按功能特性分 看着直观四、索引基础实操可直接运行1. 给已有表添加索引2. 查看表中的所有索引3. 删除索引五、索引生效核心规则面试工作重点1. 联合索引最左前缀匹配原则2. 常见的索引失效场景1左模糊匹配 %xxx2索引列上使用函数、运算3隐式类型转换4OR 连接无索引列5使用 !、、NOT IN、IS NOT NULL6索引区分度太低六、索引优化神器EXPLAIN 执行计划1. 用法2. 核心字段解读只看最重要的4个七、索引设计与优化原则八、新手常见误区一、什么是索引为什么要用它1. 通俗理解你可以把数据库的表想象成一本厚字典没有索引想找一个字必须从第一页翻到最后一页挨个比对这叫全表扫描数据越多越慢。有索引先查拼音/部首目录直接定位到目标页码一步找到内容。MySQL 索引的本质就是一种排好序的、用于快速定位数据的数据结构相当于给数据库表建了「目录」。2. 索引的好处大幅提升数据查询速度核心作用加速ORDER BY排序和GROUP BY分组操作让随机IO变成顺序IO降低磁盘读写压力3. 索引的代价新手必知索引不是越多越好它有明显成本空间成本索引本身也要占用磁盘存储空间大表的索引体积可能超过数据本身。时间成本新增、修改、删除数据时需要同步维护所有相关索引会降低写操作的速度。二、索引底层为什么 MySQL 选择 B树MySQL 最常用的存储引擎是InnoDBMySQL 5.5 之后默认它的索引底层统一使用B树结构。1. B树的核心特点InnoDB版矮胖结构IO次数少非叶子节点只存「索引键指针」不存真实数据一页默认16KB能存上千个索引键百万级数据树高也只有3-4层查询最多3次磁盘IO。叶子节点存完整数据所有数据都存在叶子节点非叶子节点只做导航。叶子节点有序且相连叶子节点之间用双向链表串联天然支持范围查询、排序。2. InnoDB 两大索引类型聚簇索引 vs 二级索引这是理解索引优化的核心基础。1聚簇索引主键索引一张表有且只有一个聚簇索引默认就是主键索引。叶子节点直接存储整行完整数据找到索引就找到了数据不需要二次查找。整张表的数据就是按主键排序的B树也叫「索引组织表」。类比字典的正文本身就是按拼音排序的拼音目录直接对应正文页码翻到页码就是完整内容。2二级索引非聚簇索引 / 普通索引普通索引、唯一索引、联合索引都属于二级索引可以建多个。叶子节点只存「索引列的值 主键值」不存完整行数据。回表通过二级索引找到主键后还需要拿着主键去聚簇索引里查完整数据这个二次查找的过程就叫回表会额外消耗性能。类比用部首查字法先查到字的拼音页码再按拼音页码去翻正文多了一步。3覆盖索引优化神器如果查询的字段在二级索引里已经全部包含了比如索引是name你只查name和id就不需要回表了直接从索引里拿数据这就是覆盖索引是性能极高的查询方式。三、MySQL 索引的常见分类我们从不同维度给索引起名字注意不要混淆维度。1. 按物理存储分聚簇索引主键索引数据和索引在一起非聚簇索引二级索引数据和索引分开2. 按字段数量分单列索引一个索引只包含一个字段联合索引复合索引一个索引包含多个字段是业务中最常用的优化手段3. 按功能特性分 看着直观索引类型作用约束主键索引加速查询唯一标识一行数据非空、唯一一张表只能一个唯一索引加速查询保证字段值不重复值唯一允许null值可以多个普通索引最基础的索引只用于加速查询无约束最常用前缀索引对 字符串 的前N个字符建索引节省空间适合长字符串全文索引用于文本关键词搜索替代 like 模糊查询适合大文本四、索引基础实操可直接运行我们以一张用户表为例演示所有索引操作。先建测试表CREATETABLEuser(idBIGINTNOTNULLAUTO_INCREMENTCOMMENT主键ID,nameVARCHAR(50)NOTNULLCOMMENT姓名,ageINTDEFAULTNULLCOMMENT年龄,phoneVARCHAR(20)DEFAULTNULLCOMMENT手机号,emailVARCHAR(100)DEFAULTNULLCOMMENT邮箱,create_timeDATETIMEDEFAULTCURRENT_TIMESTAMPCOMMENT创建时间,PRIMARYKEY(id)-- 建表时自动创建主键索引)ENGINEInnoDBDEFAULTCHARSETutf8mb4;1. 给已有表添加索引1. 添加普通索引idx_ 是命名惯例方便识别ALTERTABLEuserADDINDEXidx_name(name);-- 另一种写法CREATEINDEXidx_ageONuser(age);2. 添加唯一索引uk_ 命名惯例ALTERTABLEuserADDUNIQUEINDEXuk_phone(phone);3. 添加联合索引最常用字段顺序很重要ALTERTABLEuserADDINDEXidx_name_age_create(name,age,create_time);4. 添加前缀索引长字符串用节省空间ALTERTABLEuserADDINDEXidx_email_prefix(email(10));2. 查看表中的所有索引SHOWINDEXFROMuser;关键字段说明Key_name索引名称Seq_in_index字段在索引中的顺序从1开始Column_name索引包含的字段Non_unique是否允许重复0表示唯一索引3. 删除索引DROPINDEXidx_nameONuser;-- 另一种写法ALTERTABLEuserDROPINDEXidx_age;五、索引生效核心规则面试工作重点1. 联合索引最左前缀匹配原则这是联合索引最核心的规则90%的索引失效问题都和它有关。联合索引必须从最左边的字段开始连续匹配缺了最左列后面所有列都没法用来快速查询。联合索引中遇到范围查询、、、、between、右模糊like时范围列本身可以用到索引但范围列右侧的所有字段都无法利用索引的有序性做快速定位。规则联合索引会按照字段顺序从最左边的列开始向右匹配查询中间跳过某一列后面的列就无法用到索引。以上面的idx_name_age_create(name, age, create_time)为例✅完整生效1、WHEREname张三ANDage20ANDcreate_time2026-01-01;三个条件从左到右完整连续匹配索引的 name、age、create_time 三个字段全部都能用上 是这个索引能发挥的最强形态。2、WHEREname张三ANDage20;连续匹配到了 name 和 age用到了索引的前两列 第三个字段 create_time 没用到 但是不影响前面的部分正常走索引。3、WHEREname张三;只匹配到了最左边的 name 字段只能用到索引的第一列但依然是走这个联合索引而不是全表扫描。 这条SQL虽然走了 idx_name_age_create 这个索引但只用到了 name 这一列的能力 它的效果和单独给 name 建一个单列索引查询性能基本是一样的⚠️部分生效只用到name列跳过了agecreate_time用不到索引WHEREname张三ANDcreate_time2026-01-01;❌完全失效WHEREage20ANDcreate_time2026-01-01;WHEREcreate_time2026-01-01;补充范围查询右边的列会失效age 是范围查询后面的 create_time 用不到索引WHEREname张三ANDage20ANDcreate_time2026-01-01;这条SQLname 和 age 字段可以走索引create_time 字段索引失效。2. 常见的索引失效场景建了索引不代表一定会用以下场景会导致索引失效退化成全表扫描。1左模糊匹配%xxx-- ❌ 失效左边有%无法利用有序索引定位WHEREnameLIKE%三;-- ✅ 生效右模糊可以WHEREnameLIKE张%;2索引列上使用函数、运算-- ❌ 失效对索引列做函数操作WHEREYEAR(create_time)2026;-- ✅ 生效写成范围查询WHEREcreate_time2026-01-01ANDcreate_time2027-01-01;3隐式类型转换-- phone 是 varchar 类型传数字会触发隐式转换索引失效-- ❌ 失效WHEREphone13800138000;-- ✅ 生效WHEREphone13800138000;4OR连接无索引列只要 OR 两边有一个字段没有索引整个索引都会失效。-- 如果 age 没有索引整条SQL索引失效WHEREname张三ORage20;5使用!、、NOT IN、IS NOT NULL这些负向查询通常会导致索引失效数据量大时优化器会选择全表扫描。6索引区分度太低如果字段值重复率极高比如性别只有男 / 女优化器会觉得走索引还不如全表扫快直接放弃索引。六、索引优化神器EXPLAIN 执行计划判断一条SQL有没有走索引、性能如何用EXPLAIN关键字这是日常优化的必备工具。1. 用法EXPLAINSELECT*FROMuserWHEREname张三;2. 核心字段解读只看最重要的4个字段含义重点关注type访问类型代表查询的性能等级性能从好到差system const eq_ref ref range index ALL见到ALL就是全表扫描必须优化key实际使用的索引名称为 NULL 表示没走索引rows预估需要扫描的行数数值越小越好Extra额外执行信息Using index覆盖索引性能极好Using where需要回表过滤Using filesort文件排序性能差需优化Using temporary用了临时表性能很差七、索引设计与优化原则优先给 WHERE、JOIN、ORDER BY、GROUP BY 涉及的字段建索引不要给无关字段乱建。联合索引字段顺序区分度高的放左边同时遵循最左前缀原则。尽量使用覆盖索引查询只取需要的字段避免SELECT *减少回表。长字符串用前缀索引比如邮箱、地址只对前10-20个字符建索引大幅节省空间。控制索引数量单表索引建议不超过5-6个写多的表要更少。主键用自增整数BIGINT不要用UUID自增主键插入时不会频繁分裂B树写入性能好。避免冗余索引比如已经有了(a,b)联合索引就不需要再单独给a建索引了。八、新手常见误区❌ 索引越多越好索引会拖慢增删改还占空间按需建立才合理。❌ 建了索引就一定会用优化器会根据数据量、区分度自动选择最优方案可能放弃索引走全表。❌ORDER BY一定会走索引排序不符合索引顺序时会触发文件排序Using filesort。❌ 所有字段都加索引低区分度字段如性别、状态建索引收益极低反而浪费空间。
