like也是bTree索引吗一、直接回答是的当 LIKE 查询能用到索引时用的就是 BTree 索引因为 MySQL 中默认的索引结构就是 BTree。但关键在于BTree 的有序性决定了什么样的 LIKE 查询能用索引。二、BTree 为什么支持 LIKE ‘abc%’1. BTree 的有序特性-- 假设 name 列的值有张三、李四、王五、张伟、张强-- 在 BTree 中存储顺序是李四,王五,张三,张伟,张强-- 按字符串排序-- BTree 结构示意[王五]/\[李四][张...]/\[张三][张伟,张强]2. 前缀匹配的执行过程-- 查询WHERE name LIKE 张%EXPLAINSELECT*FROMusersWHEREnameLIKE张%;-- BTree 执行步骤-- 1. 在索引树中找到第一个以张开头的值定位到张三-- 2. 顺序扫描直到遇到不是以张开头的值-- 3. 这个过程利用了 BTree 叶子节点的链表结构-- 相当于执行了SELECT*FROMusersWHEREname张ANDname郑-- 郑是张的下一个首字三、BTree 为什么不支持 LIKE ‘%abc’1. 有序性的限制-- 查询WHERE name LIKE %三-- 原因BTree 索引是基于完整字符串排序的-- 无法通过索引直接找到以三结尾的记录-- 索引中的值李四, 王五, 张三, 张伟, 张强-- ↑ ↑-- 找不到以三结尾的起点 也不知道终点-- 只能全表扫描2. 可以这样理解-- BTree 索引就像电话簿按姓氏排序-- ✅ 快速找到所有姓张的人前缀匹配-- ❌ 无法快速找到名字叫三的人后缀匹配四、BTree 索引在 LIKE 中的实际工作方式1. 索引条件下推ICP-- MySQL 5.6 引入的优化CREATEINDEXidx_name_ageONusers(name,age);-- 查询SELECT*FROMusersWHEREnameLIKE张%ANDage20;-- 执行过程Without ICP-- 1. 用索引找到所有以张开头的记录10万条-- 2. 回表读取完整数据-- 3. 在 server 层过滤 age 20-- 执行过程With ICP-- 1. 用索引找到以张开头的记录-- 2. 在存储引擎层直接用索引中的 age 字段过滤-- 3. 只对过滤后的少量记录回表2. 索引覆盖扫描-- 创建覆盖索引CREATEINDEXidx_nameONusers(name,email);-- 查询只返回索引中的字段SELECTname,emailFROMusersWHEREnameLIKE张%;-- Extra: Using index不需要回表-- 因为查询的所有字段都在索引中五、BTree 索引在 LIKE 中的性能分析1. 不同情况下的扫描范围-- 数据分布100万条记录-- 假设姓张的有 1万条-- 情况1LIKE 张%-- 索引扫描范围从第一个张到最后一个张-- 扫描记录数约 1万条-- 性能很快-- 情况2LIKE 张三%-- 索引扫描范围从第一个张三到最后一个张三-- 扫描记录数可能只有几百条-- 性能更快-- 情况3LIKE %三-- 索引扫描范围整个索引100万条-- 但 BTree 扫描整个索引也很慢还不如全表2. 范围估算-- 查看索引统计信息SHOWINDEXFROMusers;-- Cardinality 字段表示区分度-- 估算匹配行数EXPLAINSELECT*FROMusersWHEREnameLIKE张%;-- rows 字段会显示预估的扫描行数六、BTree 索引的局限性和弥补方案1. 局限性-- BTree 无法优化的场景SELECT*FROMarticlesWHEREcontentLIKE%数据库%;SELECT*FROMproductsWHEREdescriptionLIKE%手机%;SELECT*FROMlogsWHEREmessageLIKE%ERROR%;2. 弥补方案-- 方案1全文索引内部用倒排索引不是 BTreeALTERTABLEarticlesADDFULLTEXT(content);SELECT*FROMarticlesWHEREMATCH(content)AGAINST(数据库);-- 方案2搜索引擎ElasticSearch-- 使用倒排索引专门优化全文搜索-- 方案3反向字段索引用 BTree 间接实现ALTERTABLEusersADDreverse_emailVARCHAR(255)GENERATED ALWAYSAS(REVERSE(email))STORED;CREATEINDEXidx_reverse_emailONusers(reverse_email);-- 现在可以查后缀WHERE reverse_email LIKE REVERSE()七、BTree 索引在不同 LIKE 模式下的表现LIKE 模式BTree 使用情况扫描方式效率‘abc%’✅ 使用索引范围扫描高‘a%c’✅ 使用索引范围扫描 过滤中高‘a_c’✅ 使用索引范围扫描 过滤中‘%abc’❌ 不使用全表扫描低‘%abc%’❌ 不使用全表扫描极低‘_abc’❌ 不使用全表扫描低八、总结BTree 索引在 LIKE 中的工作原理利用有序性通过 BTree 的树状结构快速定位起点利用链表通过叶子节点的链表顺序扫描利用 ICP在索引层面过滤减少回表利用覆盖索引避免回表关键结论LIKE ‘abc%’ 能用 BTree 索引利用前缀有序性LIKE ‘%abc’ 不能用 BTree 索引后缀无序BTree 不是万能的需要其他技术全文索引、ES弥补一句话理解BTree 索引就像一本按拼音排序的字典可以快速找到以某个拼音开头的所有字但没法找到以某个拼音结尾的字。
like也是bTree索引吗一、直接回答是的当 LIKE 查询能用到索引时用的就是 BTree 索引因为 MySQL 中默认的索引结构就是 BTree。但关键在于BTree 的有序性决定了什么样的 LIKE 查询能用索引。二、BTree 为什么支持 LIKE ‘abc%’1. BTree 的有序特性-- 假设 name 列的值有张三、李四、王五、张伟、张强-- 在 BTree 中存储顺序是李四,王五,张三,张伟,张强-- 按字符串排序-- BTree 结构示意[王五]/\[李四][张...]/\[张三][张伟,张强]2. 前缀匹配的执行过程-- 查询WHERE name LIKE 张%EXPLAINSELECT*FROMusersWHEREnameLIKE张%;-- BTree 执行步骤-- 1. 在索引树中找到第一个以张开头的值定位到张三-- 2. 顺序扫描直到遇到不是以张开头的值-- 3. 这个过程利用了 BTree 叶子节点的链表结构-- 相当于执行了SELECT*FROMusersWHEREname张ANDname郑-- 郑是张的下一个首字三、BTree 为什么不支持 LIKE ‘%abc’1. 有序性的限制-- 查询WHERE name LIKE %三-- 原因BTree 索引是基于完整字符串排序的-- 无法通过索引直接找到以三结尾的记录-- 索引中的值李四, 王五, 张三, 张伟, 张强-- ↑ ↑-- 找不到以三结尾的起点 也不知道终点-- 只能全表扫描2. 可以这样理解-- BTree 索引就像电话簿按姓氏排序-- ✅ 快速找到所有姓张的人前缀匹配-- ❌ 无法快速找到名字叫三的人后缀匹配四、BTree 索引在 LIKE 中的实际工作方式1. 索引条件下推ICP-- MySQL 5.6 引入的优化CREATEINDEXidx_name_ageONusers(name,age);-- 查询SELECT*FROMusersWHEREnameLIKE张%ANDage20;-- 执行过程Without ICP-- 1. 用索引找到所有以张开头的记录10万条-- 2. 回表读取完整数据-- 3. 在 server 层过滤 age 20-- 执行过程With ICP-- 1. 用索引找到以张开头的记录-- 2. 在存储引擎层直接用索引中的 age 字段过滤-- 3. 只对过滤后的少量记录回表2. 索引覆盖扫描-- 创建覆盖索引CREATEINDEXidx_nameONusers(name,email);-- 查询只返回索引中的字段SELECTname,emailFROMusersWHEREnameLIKE张%;-- Extra: Using index不需要回表-- 因为查询的所有字段都在索引中五、BTree 索引在 LIKE 中的性能分析1. 不同情况下的扫描范围-- 数据分布100万条记录-- 假设姓张的有 1万条-- 情况1LIKE 张%-- 索引扫描范围从第一个张到最后一个张-- 扫描记录数约 1万条-- 性能很快-- 情况2LIKE 张三%-- 索引扫描范围从第一个张三到最后一个张三-- 扫描记录数可能只有几百条-- 性能更快-- 情况3LIKE %三-- 索引扫描范围整个索引100万条-- 但 BTree 扫描整个索引也很慢还不如全表2. 范围估算-- 查看索引统计信息SHOWINDEXFROMusers;-- Cardinality 字段表示区分度-- 估算匹配行数EXPLAINSELECT*FROMusersWHEREnameLIKE张%;-- rows 字段会显示预估的扫描行数六、BTree 索引的局限性和弥补方案1. 局限性-- BTree 无法优化的场景SELECT*FROMarticlesWHEREcontentLIKE%数据库%;SELECT*FROMproductsWHEREdescriptionLIKE%手机%;SELECT*FROMlogsWHEREmessageLIKE%ERROR%;2. 弥补方案-- 方案1全文索引内部用倒排索引不是 BTreeALTERTABLEarticlesADDFULLTEXT(content);SELECT*FROMarticlesWHEREMATCH(content)AGAINST(数据库);-- 方案2搜索引擎ElasticSearch-- 使用倒排索引专门优化全文搜索-- 方案3反向字段索引用 BTree 间接实现ALTERTABLEusersADDreverse_emailVARCHAR(255)GENERATED ALWAYSAS(REVERSE(email))STORED;CREATEINDEXidx_reverse_emailONusers(reverse_email);-- 现在可以查后缀WHERE reverse_email LIKE REVERSE()七、BTree 索引在不同 LIKE 模式下的表现LIKE 模式BTree 使用情况扫描方式效率‘abc%’✅ 使用索引范围扫描高‘a%c’✅ 使用索引范围扫描 过滤中高‘a_c’✅ 使用索引范围扫描 过滤中‘%abc’❌ 不使用全表扫描低‘%abc%’❌ 不使用全表扫描极低‘_abc’❌ 不使用全表扫描低八、总结BTree 索引在 LIKE 中的工作原理利用有序性通过 BTree 的树状结构快速定位起点利用链表通过叶子节点的链表顺序扫描利用 ICP在索引层面过滤减少回表利用覆盖索引避免回表关键结论LIKE ‘abc%’ 能用 BTree 索引利用前缀有序性LIKE ‘%abc’ 不能用 BTree 索引后缀无序BTree 不是万能的需要其他技术全文索引、ES弥补一句话理解BTree 索引就像一本按拼音排序的字典可以快速找到以某个拼音开头的所有字但没法找到以某个拼音结尾的字。
