索引是数据库调优核心利器空间换时间读加速、写损耗。本文整合底层硬件原理、B树演进、InnoDB/MyISAM索引差异、全套索引语法、面试高频考点、开发避坑点逻辑连贯。目录索引价值与无索引性能灾难磁盘硬件底层IO性能瓶颈根源InnoDB核心IO单元Page与Buffer Pool缓存机制索引底层演进从链表→单页目录→多页目录→B树4.1 单页有序存储页目录优化4.2 多页链表的性能缺陷4.3 多级目录诞生B树4.4 为什么数据库只选用B树摒弃其他数据结构聚簇索引 vs 非聚簇索引InnoDB与MyISAM深度对比5.1 InnoDB聚簇索引主键索引5.2 InnoDB二级索引、回表查询、覆盖索引5.3 MyISAM非聚簇索引结构5.4 两大引擎索引对比表四类索引完整实战语法创建/查询/删除/易错点6.1 主键索引 PRIMARY KEY6.2 唯一索引 UNIQUE6.3 普通索引 INDEX业务最常用6.4 全文索引 FULLTEXT文本检索专用6.5 索引查看、删除通用语法索引高级核心知识点开发避坑指南7.1 复合索引最左匹配原则7.2 索引失效高频场景7.3 索引创建黄金准则哪些字段该建、哪些禁止建7.4 页分裂、自适应哈希索引补充知识点SQL执行计划EXPLAIN验证索引使用全文总结1. 索引价值与无索引性能灾难1.1 索引优缺点✅优势海量数据查询速度提升数百上千倍大幅减少磁盘随机IO主键/唯一索引自动约束字段唯一性省去业务代码校验优化ORDER BY、GROUP BY、JOIN关联查询避免文件排序。❌缺陷双刃剑核心INSERT/UPDATE/DELETE写操作性能下降修改数据需要同步维护B树索引产生额外IO索引占用磁盘存储空间单表索引不宜过多数据量极小的表索引反而会增加开销全表扫描更快。1.2 800万数据实测对比步骤1生成测试数据函数存储过程DELIMITER$$-- 生成指定长度随机字符串CREATEFUNCTIONrand_string(nINT)RETURNSVARCHAR(255)BEGINDECLAREchars_strVARCHAR(100)DEFAULTabcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ;DECLAREreturn_strVARCHAR(255)DEFAULT;DECLAREiINTDEFAULT0;WHILEinDOSETreturn_strCONCAT(return_str,SUBSTRING(chars_str,FLOOR(1RAND()*52),1));SETii1;ENDWHILE;RETURNreturn_str;END$$-- 生成10~510随机数字CREATEFUNCTIONrand_num()RETURNSINTBEGINRETURNFLOOR(10RAND()*500);END$$-- 批量插入海量数据存储过程CREATEPROCEDUREinsert_emp(INstartINT,INmax_numINT)BEGINDECLAREiINTDEFAULT0;SETautocommit0;-- 关闭自动提交批量插入减少事务日志IOREPEATSETii1;INSERTINTOemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES((starti),rand_string(6),SALESMAN,0001,CURDATE(),2000,400,rand_num());UNTIL imax_numENDREPEAT;COMMIT;END$$DELIMITER;-- 插入800万测试数据CALLinsert_emp(100001,8000000);步骤2无索引全表扫描SELECT*FROMempWHEREempno998877;单机单线程耗时4~5秒线上高并发场景会出现连接堆积、数据库阻塞宕机。步骤3建立索引优化查询ALTERTABLEempADDINDEXidx_empno(empno);SELECT*FROMempWHEREempno123456;查询耗时降至0.001秒以内性能差距巨大。2. 磁盘硬件底层IO性能瓶颈根源数据库所有持久化数据存储在磁盘磁盘属于机械设备读写速度远低于内存随机IO是MySQL性能最大瓶颈。2.1 磁盘基础概念扇区Sector磁盘硬件最小读写单元默认512字节新型硬盘4KB磁道Track盘面上同心圆同一半径所有盘面磁道组成柱面Cylinder磁头Head每张盘片双面各1个磁头负责读写盘面CHS寻址硬件定位扇区标准方式磁头柱面扇区号系统上层使用LBA线性地址映射底层自动转换CHS。2.2 两种磁盘访问模式连续访问顺序IO读写扇区地址连续磁头无需大幅移动速度极快随机访问读写扇区分散磁头机械移位寻址性能极差。索引核心设计目标尽可能减少随机磁盘IO次数。3. InnoDB核心IO单元Page与Buffer Pool缓存机制3.1 三层IO单位区分层级单位大小作用磁盘硬件512字节扇区硬件最小读写单元操作系统4KB块BlockOS屏蔽硬件差异统一读写标准InnoDB引擎16KB页PageMySQL与磁盘交互最小单元验证Page大小SQLSHOWGLOBALSTATUSLIKEinnodb_page_size;-- 输出结果16384代表16KB3.2 为什么IO单位设计为16KB而非单行读取核心依据局部性原理访问某条数据后短时间内大概率访问相邻数据。单行读取查询多条数据需要多次磁盘IOPage读取一次性加载整页16KB数据到内存同页内后续查询全部走内存无磁盘IO。3.3 Buffer Pool缓冲池MySQL内存中开辟的超大缓存区域专门缓存磁盘Page查询逻辑优先查询Buffer Pool缓存无缓存才发起磁盘IO加载Page写逻辑增删改先修改内存缓存数据后台线程按策略异步刷盘调优规范服务器物理内存50%~70%分配给Buffer Pool。3.4 Page内部结构页头存储上一页、下一页双向指针所有数据页串联成双向链表页目录页内稀疏索引快速定位行数据避免线性遍历数据行按主键有序存储InnoDB强制主键排序。4. 索引底层演进从链表→单页目录→多页目录→B树4.1 单页有序存储页目录优化建表测试CREATETABLEuser(idINTPRIMARYKEY,ageINTNOTNULL,nameVARCHAR(16)NOTNULL);-- 乱序插入数据INSERTINTOuserVALUES(3,18,杨过),(4,16,小龙女),(2,26,黄蓉),(5,36,郭靖),(1,56,欧阳锋);查询结果自动按主键升序排列核心目的有序数据才能二分查找搭配页目录快速定位。无目录逐行遍历O(n)页目录二分目录快速定位数据行大幅降低查找开销。4.2 多页链表的性能缺陷数据量超过单Page容量时MySQL新建Page存储数据所有Page通过双向链表串联。缺陷跨页查询需要依次加载多个Page到内存产生大量随机IO性能极差。4.3 多级目录诞生B树解决方案为所有数据页统一建立上层目录页目录页存储「最小主键数据页指针」数据量持续增长后再给目录页建立顶层目录最终形成树形结构——B树。B树核心特性非叶子目录页仅存储主键key子页指针不存储业务数据单页可存放海量索引key树高度极低千万级数据仅3~4层叶子节点存储完整业务数据聚簇索引或主键二级索引所有叶子节点通过双向链表串联范围查询、排序无需回溯节点性能极强查找自上而下IO次数 树高度大幅减少磁盘访问。4.4 为什么数据库只选用B树摒弃其他数据结构数据结构致命缺陷单向链表线性全量遍历IO次数爆炸二叉搜索树有序插入退化为链表树高不可控AVL/红黑树二叉结构节点子节点最多2个树高过大IO多范围查询繁琐Hash表仅支持等值查询无法排序、范围匹配存在哈希冲突InnoDB不支持手动Hash索引B树非叶子节点存储完整数据单页key数量少树更高叶子无链表范围查询需要多次回溯B树核心优势总结树矮IO少、范围查询友好、内存利用率高完美适配磁盘IO场景。5. 聚簇索引 vs 非聚簇索引InnoDB与MyISAM深度对比5.1 InnoDB企业主流聚簇索引文件结构*.ibd独立表空间文件索引与业务数据存储在同一个文件。主键索引聚簇索引一张表只能有1个聚簇索引即主键无主键时自动选用唯一非空列无则生成6字节隐藏rowidB树叶子节点直接存储完整行数据数据天然按主键有序。二级辅助索引普通/唯一索引叶子节点不存储完整数据仅存储对应行的主键值回表查询通过二级索引拿到主键再走聚簇索引查询完整行两次B树检索增加IO覆盖索引优化查询字段全部包含在二级索引内无需回表执行计划显示Using index。5.2 MyISAM非聚簇索引老旧业务使用文件拆分*.frm表结构、*.MYD纯数据文件、*.MYI索引文件索引与数据完全分离。主键索引、普通索引底层结构完全一致仅唯一性约束不同索引叶子节点存储数据行物理磁盘地址查询流程索引拿到地址直接读取MYD数据无回表概念不支持事务、MVCC、行锁仅支持表锁。5.3 两大引擎索引对比总表对比维度InnoDB聚簇索引MyISAM非聚簇索引数据索引存储合并存放于.ibd分离.MYI索引、.MYD数据主键叶子节点完整行数据数据物理地址指针二级索引叶子主键值需要回表数据地址无需回表事务支持支持事务、行锁、MVCC不支持仅表锁范围查询性能优秀叶子链表一般适用场景线上业务、读写均衡、事务需求静态只读、离线统计、无事务场景6. 四类索引完整实战语法创建/查询/删除/易错点6.1 主键索引 PRIMARY KEY创建三种方式-- 方式1建表字段后直接声明CREATETABLEuser1(idINTPRIMARYKEY,nameVARCHAR(30));-- 方式2表末尾统一指定支持复合主键CREATETABLEuser2(idINT,nameVARCHAR(30),PRIMARYKEY(id));-- 方式3建表后追加主键CREATETABLEuser3(idINT,nameVARCHAR(30));ALTERTABLEuser3ADDPRIMARYKEY(id);删除主键ALTERTABLEuser3DROPPRIMARYKEY;核心特性单表仅1个主键支持多列复合主键字段值不可NULL、全局唯一InnoDB主键即聚簇索引查询性能最优。6.2 唯一索引 UNIQUE-- 建表创建CREATETABLEuser4(idINTPRIMARYKEY,phoneVARCHAR(11)UNIQUE);-- 后期新增ALTERTABLEuser4ADDUNIQUEuk_phone(phone);CREATEUNIQUEINDEXuk_phoneONuser4(phone);-- 删除ALTERTABLEuser4DROPINDEXuk_phone;DROPINDEXuk_phoneONuser4;特性单表可创建多个唯一索引字段不可重复允许单个NULL多个NULL互不冲突唯一索引NOT NULL 等价主键索引。6.3 普通索引 INDEX业务最常用-- 建表内定义CREATETABLEuser8(idINTPRIMARYKEY,nameVARCHAR(20),INDEXidx_name(name));-- 追加索引两种写法ALTERTABLEuser8ADDINDEXidx_name(name);CREATEINDEXidx_nameONuser8(name);-- 删除索引ALTERTABLEuser8DROPINDEXidx_name;DROPINDEXidx_nameONuser8;特性字段允许重复、允许NULL频繁查询、无唯一性约束字段使用。6.4 全文索引 FULLTEXT适用场景VARCHAR/TEXT大文本模糊检索MySQL5.6后InnoDB支持默认仅英文中文需ngram分词插件。易错点LIKE %关键词%不走全文索引必须使用MATCH() AGAINST()。CREATETABLEarticles(idINTUNSIGNEDAUTO_INCREMENTPRIMARYKEY,titleVARCHAR(200),bodyTEXT,FULLTEXT ft_title_body(title,body))ENGINEInnoDB;-- 错误全表扫描SELECT*FROMarticlesWHEREbodyLIKE%database%;-- 正确命中全文索引SELECT*FROMarticlesWHEREMATCH(title,body)AGAINST(databaseINNATURALLANGUAGEMODE);-- 验证索引是否生效EXPLAINSELECT*FROMarticlesWHEREMATCH(title,body)AGAINST(database);6.5 索引查看通用语法-- 完整索引信息推荐SHOWKEYSFROMuser;SHOWINDEXFROMuser;-- 简略字段信息DESCuser;关键字段解读Non_unique0主键/唯一索引1普通索引Key_name索引名称删除索引时需要Index_typeBTREE/B树、FULLTEXT、HASH。7. 索引高级核心知识点开发避坑指南7.1 复合索引最左匹配原则复合索引idx(a,b,c)查询条件必须匹配最左前列不能跳过中间字段否则索引失效。有效where a1、where a1 and b2、where a1 and b2 and c3失效where b2、where b2 and c3补充MySQL优化器会自动调整where条件顺序只要包含最左前列即可命中索引。7.2 索引失效高频易错场景面试高频索引字段参与运算、函数WHERE id 1 100、WHERE DATE(create_time) 2026-06-24隐式类型转换字符串索引传入数字WHERE phone 13800138000模糊查询前置通配符LIKE %张三OR两侧字段只有一侧建立索引复合索引跳过最左前列。7.3 索引创建黄金准则✅ 适合建立索引频繁出现在WHERE、JOIN ON条件的字段ORDER BY、GROUP BY、DISTINCT排序分组字段区分度高字段手机号、身份证、用户ID联合字段构建覆盖索引规避回表。❌ 不建议单独建索引区分度极低性别、状态仅0/1索引无优化效果更新极其频繁点赞数、实时状态频繁维护索引树不会出现在查询条件的字段单表数据不足百条全表扫描效率更高单表索引总数控制在5个以内过多拖慢写操作。7.4 补充冷门知识点面试加分页分裂无序主键插入导致Page空间不足拆分新Page产生大量IO推荐自增INT主键避免页分裂页合并大量删除数据后页面闲置后台自动合并空闲Page自适应哈希索引InnoDB内置自动为Buffer Pool热点数据生成哈希缓存无需手动创建冗余索引已有复合索引(a,b)无需单独创建(a)节约磁盘空间。8. SQL执行计划EXPLAIN验证索引使用开发中使用EXPLAIN分析SQL执行计划判断是否命中索引、是否全表扫描。EXPLAINSELECT*FROMempWHEREempno998877;核心字段优先级性能从优到劣system const eq_ref ref range index ALL生产环境禁止出现typeALL全表扫描。关键字段key实际使用的索引名NULL代表未命中索引rows扫描行数数值越小性能越好ExtraUsing index代表覆盖索引无回表Using where过滤数据Using filesort出现文件排序需优化索引。9. 全文总结索引本质是空间换时间核心目标减少磁盘随机IO读场景提速、写场景损耗性能磁盘随机IO是数据库性能瓶颈InnoDB以16KB Page为IO最小单位搭配Buffer Pool缓存减少磁盘访问B树是数据库最优索引结构非叶子只存索引键、叶子有序链表兼顾等值查询与范围查询InnoDB采用聚簇索引数据与索引一体二级索引存在回表可通过覆盖索引优化MyISAM非聚簇索引索引数据完全分离索引分为主键、唯一、普通、全文四类根据业务场景选择遵循最左匹配原则开发规范控制索引数量、避开索引失效场景、使用EXPLAIN校验SQL、高区分度字段建索引。索引是数据库调优基础合理设计索引可以大幅提升系统并发承载能力切忌盲目加索引、滥用复合索引。觉得文章干货满满欢迎点赞收藏评论区交流MySQL调优问题
