MySQL索引优化快速入门

这里需要知道什么是B树从数据结构角度简单分析二叉树和B树可以简单理解为通过二分法减少查询的次数但是仍存在严重的性能问题1插入顺序不对时会退化为链表时间复杂度由O(logn)变成O(n)。2. 大数据情况下查询性能低为了解决以上问题选择B树如何解决自动平衡机制B-tree与B树在插入和删除时会执行自动平衡算法。删除导致节点键值对数不足时会触发合并或重新分配操作页合并和借键。度数为n的b树每个节点每个页最大容纳n-1的键值对在子节点达到n-1对时中间的键值对会复制一份到父节点该子节点分裂为两个子节点。阶数为5[25] ← 父节点提升的键/ \[10, 20] [25,30, 40] ← 两个叶子这些算法保证整棵树的平衡性防止出现性能退化情况。平衡操作的时间复杂度同样维持在O(log n)级别。。仍存在以下问题范围查询性能低大数据情况树的高度过高查询次数过多为了解决以上问题MySQL InnoDB选择B树如何解决叶子节点双向链表连接所有叶子节点通过双向链表连接形成有序序列。这种结构保证范围查询的高效性无论插入顺序如何只需遍历链表即可获取有序数据。链表连接方式使得B树在保持平衡的同时支持高效的范围操作。非叶节点仅做key的区分B树的非叶子节点不存储实际数据因此单个节点单个页可以容纳更多键。该特性显著降低树的高度例如在千万级数据量下B树可能仅需3-4层。这种特性确保大数据量下的查询性能稳定。一、索引索引结构索引的核心定义索引是MySQL在存储引擎层为字段构建的B树数据结构用于快速定位符合条件的数据行减少磁盘IO。2.基础索引类型索引类型核心特点适用场景主键索引唯一、非空一张表仅1个聚簇索引主键查询WHERE id1唯一索引字段值唯一允许NULL一张表可多个唯一字段查询如手机号phone普通索引无唯一性约束最常用普通查询字段如age、name联合索引多个字段组合的索引多字段查询如agename覆盖索引索引包含查询所需所有字段避免回表如SELECT id,age FROM user WHERE age20(age,id)索引覆盖3.什么是回表聚簇索引是实际存储表数据的结构每个主键下存储该row的数据非聚簇索引以字段为key,主键为value在查询时先查询出主键再到聚簇索引中查询出该主键下的row数据,这个过程叫做回表回表select * from tb_user where age20;不回表select id,age from tb_user where age20;4.索引底层为什么用B树基础理解B树是“平衡多路查找树”所有数据存在叶子节点且叶子节点按顺序链表连接对比其他结构优势比二叉树层级少百万数据仅3-4层查询更快比哈希索引支持范围查询如age20哈希仅支持等值查询。5.索引失效核心场景索引列做运算/函数age120、YEAR(create_time)2024select id,age from tb_user where age120;存在隐式类型转换WHERE phone[phone]phone是var类型select id,phone from tb_user where phone[phone];联合索引不满足“最左前缀”(age,name)索引仅查name张三select id,name from tb_user where name张三;LIKE后置模糊匹配name LIKE %张前缀匹配张%生效select id,name from tb_user where name like %张;OR条件含非索引列age20 OR address北京address无索引。select id,age,address from tb_user where age20 OR address北京;6.索引设计基础原则首先通过该命令查看当前数据库各语句的访问频次SHOW GLOBAL STATUS LIKE Com_______;一般只为select语句进行优化select语句要占比高select/selectinsertupdatedelete只为“高频查询字段”建索引WHERE/ORDER BY/GROUP BY字段联合索引高频字段放左、区分度高的字段放左如phoneage避免冗余索引如已有(age,name)无需再建age索引。索引下推ICP基础语句select * from tb_user where age20 and reward200;范围查询让联合索引(age,reward)中的reward部分失效从“B树二分查找”退化成“叶子双向链表顺序扫描 ICP过滤”。非叶子节点的快速定位能力只用到age定位起点后面的reward完全靠过滤不再参与树遍历。核心定义索引下推全称Index Condition PushdownICP是索引优化机制在使用联合索引查询时将原本由MySQL服务器层执行的「索引字段过滤逻辑」下推到存储引擎层完成减少回表次数和数据传输量提升查询效率。核心作用减少回表IO存储引擎层先过滤不符合条件的索引记录仅将符合条件的记录主键返回给服务器层再回表查询完整数据降低服务器层开销避免服务器层处理大量无效的索引记录减少 CPU 计算成本对联合索引的长尾条件过滤效果显著。总结适用场景仅针对非主键的联合索引且过滤条件是索引中包含的字段如上面的reward是联合索引的一部分识别方式EXPLAIN的Extra字段显示Using index condition表示触发了索引下推与覆盖索引的区别覆盖索引是「完全不回表」索引包含所有查询字段索引下推是「减少回表行数」仍需回表只是回表数据更少有任何相关问题欢迎评论区讨论