大白话说说mysql索引
2023-09-09 11:29:56
MySQL索引就像书的目录，能帮数据库快速定位数据，避免全表翻找，提升查询速度。 以下是具体解释：
为什么需要索引？想象一下，一本没有目录的书，想找某个章节只能一页页翻。数据库也一样，如果没有索引，查询数据就得扫描整张表（全表扫描），数据量大时非常慢。索引的作用就是给数据“建目录”，让数据库能快速定位到目标数据。
索引的底层结构：B+树数据库的索引不是随便选的，它需要一种既能快速查找，又能高效支持区间查询的数据结构。
哈希表：虽然能快速定位等值查困桥询（如WHERE id=100），但区间查询（如WHERE id BETWEEN 1 AND 100）效率低，因为哈希是无序的。
数组：支持区间查询（通过下标遍历），但插入或删除数据时需要移动大量元素，性能差，适合静态数据（如历史数据）。
二叉搜索树：查找效率高，但区间查询仍需遍历整棵树。改造后（如B+树），数据只保存在叶子节点，叶子节点用链表连接，既能快速定位等值，又能高效区间查询。
B+树：MySQL最终选择的结构。它通过多级节点（非叶子节点只存索引，不存数据）减少磁盘I/O，叶子节点链表支持高效范围查询，是平衡树，查询效率稳定。
索引的“代价”索引不是越多越好，它有额外开销：
存储空间：索引本身需要占用磁盘空间。
写入性能：插入、更新、删除数据时，需要同步更新索引，可能降低写入速度。
优化器选择：MySQL的优化器会根据数据量、是否需要排序、是否需要回表等因素，决定是否使用索引。例如，小表查询可能直接全表扫描更快，或者索引选择性差（如性别字段只有“男/女”）时，优化器可能放弃索引。
索引的使用场景
高频查询字段：如用户表的手耐郑机号、订单表的订单号，经常用于WHERE条件或连接查询。
排序字段：如ORDER BY create_time，若create_time有索引，可避免排序操作。
组合索引：多个字段组合的索引（如(name, age)），能同时优化多个条件的查询。
覆盖索引：查询的字段全部在索引中，无需回表（如索引包含(id, name)，查询SELECT name FROM table WHERE id=1）。
索引的“坑”
索引失效：如对索引字段使用函数（WHERE YEAR(create_time)=2023）、模糊查询前缀通配符（WHERE name LIKE '%张'）、隐式类型转换（如字段是字符串类型，却用数字查询WHERE id='123'）。
索引选择性低：如性别汪亩猛、状态等字段，重复值多，索引效果差。
回表操作：非覆盖索引时，需根据索引找到主键，再回表查完整数据，可能影响性能。
总结：索引是MySQL的“加速神器”，但需合理设计。理解B+树结构、优化器逻辑和索引使用场景，能帮你避开性能陷阱，让查询飞起来！
热门标签
