MySQL 索引特性与性能优化全解

草莓熊Lotso个人主页❄️个人专栏:《C知识分享》 《Linux 入门到实践零基础也能懂》✨生活是默默的坚持毅力是永久的享受 博主简介文章目录前言一. 索引是什么1.1 初步认识索引1.2 建立共识知识补充感兴趣的可以仔细看看二. 索引底层数据结构B 树2.1 B 树结构的特点2.2 为何选择 B 树三. InnoDB 与 MyISAM 索引实现聚簇索引和非聚簇索引3.1 InnoDB聚簇索引3.2 MyISAM非聚簇索引3.3 回表查询辅助索引补充四. 索引分类与创建4.1 主键索引primary key4.2 唯一索引unique4.3 普通索引index4.4 复合索引常用4.5 全文索引fulltext五. 索引的删除与查看操作5.1 删除索引5.2 查看索引六. 索引知识点小结6.1 索引失效场景6.2 索引设计最佳实践6.3 总结结尾前言大家好今天这篇文章将从索引本质、数据结构、物理存储、索引分类、创建删除、性能压测、最左前缀、索引优化全覆盖、可直接复制运行适合学习、笔记、复习、面试使用。在 MySQL 性能优化中索引是最关键、最有效、最常用的手段。没有索引 全表扫描有了索引 快速定位。但索引不是越多越好理解原理、结构、适用场景才能写出真正高效的 SQL。声明一下由于本章概念性的知识比较多有些地方会直接放上资料的截图大家自行进行查看理解一下。一. 索引是什么1.1 初步认识索引索引是帮助 MySQL高效获取数据的数据结构B 树。可以理解为书的目录。优点大幅提高查询速度降低磁盘 IO 次数优化排序、分组操作缺点占用磁盘空间降低增删改效率需要维护索引树过多索引会导致优化器选择困难大家感兴趣的话可以做一下下面这个实验✅️ 其实我们这里是还要聊一下磁盘的但是关于磁盘的话题之前博主在Linux系列博客中写的很详细了大家可以去看一下进行一个了解Linux磁盘基础从物理结构到 CHS/LBA寻址吃透数据存储底层逻辑1.2 建立共识知识补充感兴趣的可以仔细看看二. 索引底层数据结构B 树2.1 B 树结构的特点叶子节点保存有数据非叶子节点即路上节点不存储数据只存储目录项。因为非叶子节点不存数据所以可以存储更多的目录项从而管理更多的叶子节点。这样形成的树结构矮胖查找时经过的节点少每次读取的 page 更少可以大大提升搜索效率。在十层左右的树中整体效率会有显著提高。叶子节点全部用链表级联起来。这是 B 树的一个重要特点主要目的是为了高效地进行范围查找。2.2 为何选择 B 树重点为什么不用二叉树、红黑树、B 树二叉树数据倾斜时高度极高IO 多红黑树层数仍然高B 树叶子节点不连续范围查询慢B 树MySQL 最终选择三. InnoDB 与 MyISAM 索引实现聚簇索引和非聚簇索引3.1 InnoDB聚簇索引索引即数据数据即索引.ibd文件 索引 数据主键索引叶子节点存储完整记录辅助索引叶子节点存储主键值上面的那种B树就是InnoDB的结构3.2 MyISAM非聚簇索引索引和数据分开存储.MYD数据文件.MYI索引文件索引叶子节点存储数据地址指针3.3 回表查询辅助索引补充通过辅助索引找到主键 → 再通过主键查数据 → 叫回表。避免回表的方法覆盖索引后面会讲到的。四. 索引分类与创建4.1 主键索引primary key非空、唯一、一个表只能一个默认为聚簇索引createtableuser(idintprimarykeyauto_increment,namevarchar(20));4.2 唯一索引unique列值唯一允许null适合身份证、手机号、邮箱createuniqueindexidx_nameonuser(name);4.3 普通索引index仅加速查询无唯一性限制createindexidx_nameonuser(name);4.4 复合索引常用多个字段组合成索引遵循最左前缀原则最左前缀必须从左到右依次匹配不能跳过。createindexidx_name_ageonuser(name,age);举个例子假设有一张用户表CREATETABLEuser(idINTPRIMARYKEY,-- 主键索引nameVARCHAR(20),ageINT,cityVARCHAR(20),INDEXidx_name_age(name,age)-- 复合索引 (name, age));查询1使用覆盖索引避免回表SELECTname,ageFROMuserWHEREname张三;条件用了复合索引的最左列name满足最左前缀原则。索引idx_name_age中已经包含了name和age查询需要的字段都在索引里直接从索引返回数据不用回表。查询2缺少最左列索引失效SELECTname,ageFROMuserWHEREage25;条件没有name不满足最左前缀原则idx_name_age无法高效使用。查询3索引未覆盖所有字段仍需回表SELECTname,age,cityFROMuserWHEREname张三;虽然满足最左前缀但city不在idx_name_age中索引只能找到name和age还得拿着id回主键索引取city。总结复合索引遵循最左前缀原则只有从第一列开始连续匹配才能被有效利用如果索引包含了查询所需的所有列覆盖索引就能避免回表直接返回结果。4.5 全文索引fulltext用于大文本检索不支持 like “% xxx%”createfulltextindexidx_contentonarticle(content);五. 索引的删除与查看操作5.1 删除索引dropindexidx_nameonuser;5.2 查看索引showindexfromuser;showindexfromuser\G六. 索引知识点小结6.1 索引失效场景违反最左前缀使用函数where upper(name)A隐式类型转换where id123like ‘% xxx’ 以通配符开头使用or但有字段无索引order by 违反最左前缀使用 、 not in 、 not exists优化器判断全表扫描更快数据量少6.2 索引设计最佳实践优先创建复合索引而不是单列索引索引字段要高选择性如性别不适合不要索引频繁更新的字段不要索引无用字段单表索引建议不超过 5 个复合索引字段顺序等值在前范围在后尽量使用覆盖索引避免 select *6.3 总结索引是 B 树结构目的是减少 IO、加速查询InnoDB 是聚簇索引MyISAM 是非聚簇索引辅助索引存主键查询可能回表复合索引必须遵循最左前缀覆盖索引能避免回表大幅提升速度索引不是越多越好要按业务设计掌握索引失效场景SQL 才能真正高效结尾 我是草莓熊 Lotso若这篇技术干货帮你打通了学习中的卡点 【关注】跟我一起深耕技术领域从基础到进阶见证每一次成长 ❤️ 【点赞】让优质内容被更多人看见让知识传递更有力量 ⭐ 【收藏】把核心知识点、实战技巧存好需要时直接查、随时用 【评论】分享你的经验或疑问比如曾踩过的技术坑一起交流避坑 ️ 【投票】用你的选择助力社区内容方向告诉大家哪个技术点最该重点拆解 技术之路难免有困惑但同行的人会让前进更有方向愿我们都能在自己专注的领域里一步步靠近心中的技术目标结语索引是 MySQL 性能优化的核心吃透 B 树原理、索引类型与使用规则就能避开 80% 的性能坑。合理设计索引、避开失效场景让数据库查询效率倍增 觉得有用欢迎点赞收藏后续将持续分享更多 MySQL 进阶技巧一起深耕数据库开发✨把这些内容吃透超牛的放松下吧✨ʕ˘ᴥ˘ʔづきらど