MySQL索引解析：B+树机制及典型认知偏差
2026西湖龙井茶官网DTC发售：茶农直供，政府溯源防伪到农户家
一、索引是什么
索引是存储引擎用于快速查找记录的一种数据结构。没有索引时，MySQL只能从第一行开始逐行扫描全表，直到找到匹配的行。这种全表扫描在数据量小的时候没问题，但百万级数据下，一次查询可能需要几秒钟甚至更久。
索引的核心价值是将线性查找转化为可预测的树查找，把时间复杂度从O(n)降到O(log n)。MySQL的InnoDB引擎使用B+树作为索引的底层数据结构，B+树的所有数据记录都存储在叶子节点上，并且叶子节点之间用指针连接，形成一个有序链表，这让范围查询变得高效。
二、B+树如何工作
B+树是一种平衡多路搜索树，它的特点体现在几个方面：
叶子节点存储全部数据：非叶子节点只存储键值和指针，不存储实际数据行，因此一个页可以容纳更多键值，树的高度更低。
叶子节点有序连接：所有叶子节点按键值大小顺序串连，这意味着对索引列进行范围查询或排序时，只需遍历叶子链表即可，不需要回溯上层节点。
磁盘I/O次数少：InnoDB的页大小默认为16KB，一个3层的B+树可以存储约2000万条记录，查找一条数据只需要3次磁盘I/O。
用个例子理解：对user表的age列建立索引KEY idx_age(age)，当执行SELECT * FROM user WHERE age BETWEEN 20 AND 30时，InnoDB从根节点开始二分查找，定位到20所在的叶子节点，然后沿着叶子节点的链表向后遍历，直到年龄超过30为止。整个过程不需要读取不相关的数据页。
三、索引失效的典型场景
索引不是建了就一定能用上。以下情况MySQL优化器会放弃使用索引：
在索引列上使用函数或表达式：
WHERE DATE(create_time) = '2025-01-01'会导致索引失效，正确写法是WHERE create_time >= '2025-01-01' AND create_time < '2025-01-02'。类型隐式转换：如果
phone字段是varchar类型，但查询写成WHERE phone = 13800000000，MySQL会将phone转换为数字再比较，索引失效。应该用字符串形式：WHERE phone = '13800000000'。LIKE以通配符开头：
WHERE name LIKE '%张三'无法使用索引，而WHERE name LIKE '张三%'可以使用索引的前缀匹配。联合索引不满足最左前缀：索引
(a, b, c)支持a、a,b、a,b,c的查找，但查询条件只包含b和c时无法使用该索引。
四、如何分析索引使用情况
MySQL提供了EXPLAIN命令用于查看查询的执行计划。重点关注几个字段：
type：连接类型，从好到差依次是
system>const>eq_ref>ref>range>index>ALL。出现ALL意味着全表扫描，需要优化。possible_keys：可能用到的索引，但不代表实际使用。
key：实际使用的索引。如果为NULL，说明没用到索引。
rows：预估需要扫描的行数，这个值越小越好。
Extra：额外信息，出现
Using filesort或Using temporary通常意味着排序或分组没有利用索引，性能较差。
分析慢查询时，可以先开启慢查询日志，用mysqldumpslow工具汇总分析，找出最耗时的几条SQL，再用EXPLAIN逐个优化。
五、索引设计的几条建议
为高频查询条件建索引：经常出现在
WHERE、ORDER BY、GROUP BY、JOIN中的列优先考虑。选择区分度高的列：索引列的重复值越少越好。性别列只有两个值，区分度极低，建索引意义不大。
避免索引过多：每增加一个索引，
INSERT、UPDATE、DELETE操作都需要额外维护索引树，会降低写性能。使用覆盖索引：如果查询只需要索引列中的值，可以把这些列建成联合索引，避免回表操作。
定期清理冗余索引：通过
sys.schema_redundant_indexes视图或pt-duplicate-key-checker工具找出重复或冗余的索引并删除。
请先 登录后发表评论 ~