当前位置：首页 > news >正文

【MySQL】前缀索引、索引下推、访问方法，自适应哈希索引

news 来源：原创 2025/8/24 13:15:11

最左前缀原则

对于INDEX(name, age)来说
最左前缀可以是联合索引的最左N个字段, 也可以是字符串索引的最左M个字符。

 SELECT * FROM t WHERE name LIKE '张%'

其效果和单独创建一个INDEX(name)的效果是一样的
若通过调整索引字段的顺序, 可以少维护一个索引树, 那么这个顺序就是需要优先考虑采用的
对字符串创建索引，要考虑如果字符串很长的情况下，那么维护和响应成本也会很高，这时，可以使用字符串最左边开始的部分字符建立索引
索引的选择性指的是 : 不重复的索引值和数据表的记录总数(#T)的比值, 范围为 1/#T 到 1 之间, 索引选择性越高则查询效率越高

索引选择性 = 不重复值的数量 / 总记录数，它反映了“这个索引字段能不能把数据查得很精”。

如果一个字段有 10 万条记录，但只有 3 个不同的值（比如性别:男、女，武装直升机），选择性 = 3 / 100000 = 0.00003 → 很低，不推荐建立单独索引。

如果字段有 10 万条记录，几乎每条都不同（比如身份证号），选择性接近 1 → 非常高，适合做索引。

为什么选择性越高，查询越快？
- 因为选择性越高，能过滤掉的数据就越多，减少回表，查询效率就越高。
对于BLOB, TEXT, VARCHAR等类型的列, 必须使用前缀索引, MySQL不允许索引这些列的完整长度
MySQL 不允许直接为这类大字段建完整索引（因为太大了）。所以要建前缀索引，也就是只索引字段前 N 个字符

如何选 N（前缀长度）？

先看整列的区分度（理想情况）：

SELECT COUNT(DISTINCT name)/COUNT(*) FROM t;

再试试不同前缀长度的选择性，比如前 3、5、10 个字符：

SELECT COUNT(DISTINCT LEFT(name, 3))/COUNT(*) FROM t;
SELECT COUNT(DISTINCT LEFT(name, 5))/COUNT(*) FROM t;
...

看哪个N更靠近1, 进行索引的创建，用它来建索引：
```
CREATE INDEX idx_name_prefix ON t(name(N));
```

-- 查看详细索引信息
show index from products;

什么是“索引下推”？

索引下推是 MySQL 从 5.6 开始引入的一种优化技术，它让更多的WHERE 条件在索引扫描阶段就被处理掉，减少了回表次数，从而提升查询效率。

举个例子说明：

SQL语句如下：

SELECT * FROM t WHERE name LIKE '陈%' AND age = 10;
-- 假设我们创建了联合索引：INDEX(name, age)

在 MySQL 5.6 之前（没有索引下推）：

使用索引找出 name LIKE '陈%' 匹配的主键 id。
对这些 id 一条条回表（到原始数据）。
然后在回表的数据中判断 age = 10。

问题：大量回表，浪费IO。

MySQL 5.6 之后（有索引下推 ICP）：

先用索引查出 name LIKE '陈%'。
再在索引中继续判断 age = 10（因为索引中也有 age 字段）。
只有两个条件都满足，才回表拿全部字段。

优势：回表次数少，速度快。

对比图解（简化描述）：

[无 ICP]       name → (回表) → 判断 age → 符合 → 返回结果
[有 ICP]       name → 判断 age (在索引内完成) → (回表) → 返回结果

使用条件：

使用了联合索引（如 INDEX(name, age)）。
查询中涉及多个字段条件。
被筛选的多个字段都在索引中存在。

想验证是否用了索引下推？

你可以使用 EXPLAIN 或 EXPLAIN FORMAT=JSON：

EXPLAIN SELECT name, age FROM t WHERE name LIKE '陈%' AND age = 10;

查看 Extra 中是否出现：

Using index condition

就说明用了索引下推。

索引下推使用条件逐条理解

条件	含义解释	是否关键	原因与说明
✅ 只能用于 `range`、`ref`、`eq_ref`、`ref_or_null` 访问方法	ICP 只在索引访问的情况下生效，这几种是索引访问方式中常见的	是	全表扫描 (`ALL`) 不会使用索引，自然也就无索引下推
✅ 只能用于 InnoDB 和 MyISAM 引擎（含分区表）	目前 ICP 仅支持这两个存储引擎	是	其他引擎如 MEMORY、CSV 不支持该特性
✅ 对 InnoDB 来说，ICP 只作用于二级索引（辅助索引）	InnoDB 的主键是聚簇索引，数据和索引一体	是	聚簇索引查询不需要回表，因此没有回表优化的必要
✅ ICP 的目的就是减少回表次数（减少 IO）	回表代价高，所以想办法让更多的过滤发生在索引层	是	回表越少，磁盘 IO 越少，性能越高
❌ 子查询中的条件不能下推	ICP 只作用于主查询中的 WHERE 条件	是	子查询优化路径不同，不能在索引层提前判断
❌ 使用存储函数（如 `IFNULL(col, '')`）的条件不能下推	存储引擎无法理解和执行存储函数	是	ICP 是存储引擎层做的过滤，函数是 SQL 层的，隔离了

什么是这些“访问方法”？（range、ref、eq_ref、ref_or_null）

这些是 MySQL 优化器 在执行 SELECT 时使用的 索引访问方式，用来决定“怎么查你这张表”。

你可以用 EXPLAIN 看见，比如：

EXPLAIN SELECT * FROM users WHERE id = 1;

type 那一列就可能会出现：ref、range、ALL 等。

访问方法	中文意思	举例	是否能用 ICP	说明
range	范围查找	`id > 5 AND id < 10`	✅ 支持	利用索引范围扫描（B+ 树区间）
ref	普通等值查找	`name = '张三'` 且 `name` 有索引	✅ 支持	单值等值匹配，常见联合索引匹配场景
eq_ref	唯一等值查找	`t1.id = t2.id` 且 t2.id 是主键或唯一索引	✅ 支持	用于连接，精确匹配唯一值
ref_or_null	等值 + null 查找	`name = '张三' OR name IS NULL`	✅ 支持	对空值的处理也是索引可识别的
ALL	全表扫描	没有用索引	❌ 不支持	没有用到索引，自然谈不上索引下推

尝试时的一个问题

create index idx_product_name_and_category_union on products(product_name, category);
show index from products;
-- 这里没触发索引下推，LIKE '笔%' 是可以使用索引的（前缀匹配），
-- 但匹配度低，MySQL 可能选择不下推 category 的判断，保留到回表阶段处理。
explain select product_id from products where product_name like '笔%' and category = 'Electronics';-- 加上 FORCE INDEX 强制使用联合索引，后就使用索引下推了
EXPLAIN SELECT * FROM products FORCE INDEX(idx_product_name_and_category_union)
WHERE product_name LIKE '笔%' AND category = 'Electronics';

在这里插入图片描述

自适应哈希索引

InnoDB 支持一种 自适应哈希索引（AHI） 的优化机制，它不是我们手动建的索引，而是 InnoDB 在运行过程中自动创建的哈希索引，

目的是加快查询速度，特别是对频繁访问的相同范围或相同条件的 B+树索引查询，会自动转化为哈希结构，提升效率。

具体来说：

InnoDB 会监控 B+ 树索引的使用频率
如果发现某段范围经常被查询，而且是“等值查找”（不是模糊、范围），就会自动为这段建立哈希索引
这样后续的查找可以从 O(log n) 变为 O(1)，提升性能
它是完全自动、由 InnoDB 维护的，不需要我们手动干预

点	内容
使用场景	频繁的等值查询，例如 `WHERE id = 123`，会被自动转换为哈希索引优化
控制开关	参数：`innodb_adaptive_hash_index=ON`（默认开启）
内存占用	哈希索引是存在 Buffer Pool 的内存中，不是磁盘上的
限制	只能用于等值匹配，不支持范围查询或模糊匹配（LIKE、BETWEEN 等）
风险	对热点表、高并发写可能带来锁冲突，可考虑关闭该功能

https://github.com/0voice

【MySQL】前缀索引、索引下推、访问方法，自适应哈希索引

最左前缀原则对于INDEX(name, age)来说最左前缀可以是联合索引的最左N个字段, 也可以是字符串索引的最左M个字符。 SELECT * FROM t WHERE name LIKE 张%其效果和单独创建一个INDEX(name)的效果是一样的若通过调整索引字段的顺序, 可以少维护一个索引树, 那么这个顺序就是需要…...

编程日记 2025/8/24 13:15:11

【C++游戏引擎开发】第16篇：ImGui指南

ImGui官方Github ImGUI（即时模式图形用户界面）是一种颠覆传统GUI开发范式的界面系统，由Casey Muratori于2005年提出概念，后经Omar Cornut开发为Dear ImGui开源库。其核心设计理念彻底改变了开发者构建交互式工具的方式。一、vcpkg安装与特点介绍 1.1 安装 vcpkg install…...

编程日记 2025/8/17 20:55:37

分享一下这几天在公司学到的东西

这几天我学到了很多东西 （1）我自己原来写项目，前后端联调用的都是postman，然后直接测试接口，然后连一下就完了。这几天我接触到了apifox的Mock这个东西！我知道了一个前端工程师进行前后端链条的时候&#…...

编程日记 2025/8/20 1:27:15

C# 将Excel格式文件导入到界面中，用datagridview显示

界面按钮不做介绍。主要代码： //用于获取从上一个页面传过来datagridview标题 public DataTable GetHeader { get; set; } private void UI_EXPINFO_Load(object sender, EventArgs e) { //页面加载显示listbox1中可…...

编程日记 2025/8/22 3:54:23

Shell编程之正则表达式与文本

目录一正则表达式 1 正则表达式的定义 2 正则表达式用途二正则表达式类型 1 基础正则表达式示例 （1）查找特定字符 （2）利用中括号” [] “来查找集合字符 （3）查找行首” ^ “与行尾字符”$“ &…...

编程日记 2025/8/22 11:25:10

Spring JDBC 与数据访问：从性能优化到事务协同

在高并发场景（如电商、金融等行业），数据库访问的性能和事务一致性是系统稳定性的关键。 Spring JDBC通过模板化操作和事务管理机制，大幅简化了传统 JDBC 繁琐的 API 处理，使数据库操作更加高效、安全、可维护。一、…...

编程日记 2025/8/23 5:03:54

Markdown 编辑器的使用

欢迎使用 Markdown 编辑器你好！ 这是你第一次使用 Markdown编辑器所展示的欢迎页。如果你想学习如何使用Markdown编辑器, 可以仔细阅读这篇文章，了解一下Markdown的基本语法知识。新的改变我们对Markdown编辑器进行了一些功能拓展与语法支持&…...

编程日记 2025/8/23 5:03:59

自动化浏览器环境与 Node.js 环境的逆向分析：完整教程

在当今的 Web 开发中，了解浏览器环境与 Node.js 环境之间的差异是非常重要的，特别是当你希望进行自动化操作、逆向工程或进行跨平台开发时。在这篇教程中，我们将探讨如何通过 JavaScript 在浏览器中自动化环境检测、分析并对比 Node.js 和浏览…...

编程日记 2025/8/24 1:45:37

脉冲编码调制（PCM）在三角形信号中的应用

基于MATLAB平台，详细阐述脉冲编码调制（PCM）在三角形信号处理中的全流程实现。我这里将变量名更改为具有辨识度的Dogness_、Lhuu_，包括信号生成、均匀量化、编码、解码及解量化过程，为数字信号处理提供实践参考。 Dogne…...

编程日记 2025/8/22 7:07:15

# 03_Elastic Stack 从入门到实践（三）-- 4

03_Elastic Stack 从入门到实践（三）-- 4 七、Elasticsearch之中文分词器（IK分词器） 1、什么是分词？ 分词：就是指将一个文本转化成一系列单词的过程，也叫文本分析，在Elasticsearch…...

编程日记 2025/8/24 13:15:11

前端VUE框架理论与应用（10）

1、记住全局注册的行为必须在根 Vue 实例 (通过 new Vue) 创建之前发生。 2、要注意，以 / 开头的嵌套路径会被当作根路径。这让你充分的使用嵌套组件而无须设置嵌套的路径。 3、注意：在 Vue 实例内部，你可以通过 $router 访问路由实例。因此你可以调用 this.$router.push…...

编程日记 2025/8/24 8:53:27

基础学习：（6）nanoGPT

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档文章目录前言1 nanoGPT 浅尝1.1 基础环境1.2 prepare.py1.2 train.py1.3 sample.py 2 再探gpt2.1 layer_norm2.2 KQV 和 self attention2.3 masked self-attention2.4 调用构…...

编程日记 2025/8/20 4:09:47

python支持自定义基准的相对误差计算

def is_within_tolerance_custom(a, b, tolerance0.1, reference“max”): “”" 支持自定义基准的相对误差计算。参数:reference (str): 基准类型，可选 "max"（默认）、"min"、"mean"、"a"&am…...

编程日记 2025/8/24 10:26:24

力扣DAY52-54 | 热100 | 图论：腐烂的橘子、课程表、前缀树

前言中等 √ 腐烂的橘子用层次遍历，课程表用俩哈希表，前缀树基本与题解一致。however不太规范。腐烂的橘子我的题解层次遍历，先找出所有腐烂的橘子进入队列并记录数量，接着内层遍历第一层腐烂的橘子，上下左右四…...

编程日记 2025/8/21 10:27:35

java CountDownLatch用法简介

CountDownLatch倒计数锁存器 CountDownLatch：用于协同控制一个或多个线程等待在其他线程中执行的一组操作完成，然后再继续执行 CountDownLatch用法构造方法：CountDownLatch(int count)，count指定等待的条件数（任务…...

编程日记 2025/8/19 14:30:03

科技项目验收测试报告有哪些作用?需要多长时间和费用?

在当今快速发展的科技环境中，科技项目的有效验收至关重要。对于公司、开发团队以及客户来说，科技项目验收测试报告更是一个不可缺少的一项重要环节。科技项目验收测试报告是对一个项目在开发完成后所进行的一系列测试结果的总结。这份报告不仅用于证明…...

编程日记 2025/8/17 21:19:45

网络原理面试题

1.如何理解 URI？ URI, 全称为(Uniform Resource Identifier), 也就是统一资源标识符，它的作用很简单，就是区分互联网上不同的资源。但是，它并不是我们常说的网址, 网址指的是URL, 实际上URI包含了URN和URL两个部分，由于 URL 过于普及，就默认将 URI 视为 URL 了。 URI 的…...

编程日记 2025/8/20 5:41:26

专为路由器和嵌入式设备设计的OpenWrt是什么？

OpenWrt是一款基于Linux内核的开源嵌入式操作系统，专为路由器和嵌入式设备设计。自2004年诞生以来，它已成为替代商业固件的首选方案，凭借其高度可定制性、模块化架构和活跃的开发者社区，广泛应用于家庭网络、企业级设备、物联网（IoT）及安全领域。以下从多个维度展开详细介…...

编程日记 2025/8/21 7:53:16

NVIDIA RTX™ GPU 低成本启动零售 AI 场景开发

零售行业正在探索应用 AI 升级客户体验，同时优化内部流程。面对多重应用场景以及成本优化压力，团队可采用成本相对可控的方案，来应对多重场景的前期项目预演和落地，避免短期内大规模投入造成的资源浪费。客户体验 AI 场景的研究…...

编程日记 2025/8/22 23:55:41

element-ui自定义主题

此处的element-ui为基于vue2.x的由于https://element.eleme.cn/#/zh-CN/theme/preview（element的主题）报错503， 所以使用https://element.eleme.cn/#/zh-CN/component/custom-theme 自定义主题文档中，在项目中改变scss变量的方…...

编程日记 2025/8/23 8:32:18

PhotoShop学习10

1.画板功能的使用使用画板功能可以轻松针对不同的设备和屏幕尺寸设计网页和 APP。画板是一种容器，类似于特殊图层组。画板中的图层在图层面板中，按画板进行分组。使用画板，一个文档中可以有多个设计版面，这样可以在画板之间轻…...

编程日记 2025/8/19 17:11:05

基于LLVM设计领域专用语言（DSL）的步骤——以激光微加工为例

1. 明确DSL的设计目标在激光微加工领域，DSL需解决以下问题： • 工艺参数抽象化：激光功率、频率、扫描路径等需用高阶语法描述，而非底层G代码。 • 实时性要求：控制指令需低延迟编译为机器码（如FPGA或运动控…...

编程日记 2025/8/20 21:40:31

【MAUI】IOS保活

文章目录概述sevice使用概述每种方法都是独立的，可以根据应用的需求单独使用。例如，如果应用的主要功能是跟踪用户的地理位置，则可以仅使用后台定位；若是为了保持应用在后台运行以完成特定任务（比如上传数据&#…...

编程日记 2025/8/21 17:30:53

shardingsphere-jdbc集成Seata分布式事务

1、导入相关依赖 <dependency><groupId>org.apache.shardingsphere</groupId><artifactId>shardingsphere-jdbc</artifactId><version>5.5.1</version></dependency><!-- shardingspher…...

编程日记 2025/8/24 3:55:37

基于区块链的技术应用探索

文章目录前言一、区块链技术的核心特性1.1 去中心化1.2 不可篡改性1.3 透明性与可追溯性1.4 智能合约机制二、区块链的典型应用场景2.1 金融与支付2.2 溯源与供应链管理2.3 数字身份与数据隐私2.4 数字资产与NFT2.5 公共服务与政务透明三、区块链的分类1.按权限管理方式分类…...

编程日记 2025/8/17 18:31:40

MQ(RabbitMQ)消息重复消费问题的全面解决方案

MQ消息重复消费是分布式系统中的常见问题，主要由网络问题、消费者故障、消息重试机制等引起。以下是针对RabbitMQ的完整解决方案体系： 一、消息生产端解决方案 1. 消息幂等设计全局唯一消息ID： MessageProperties props MessagePropert…...

编程日记 2025/8/20 1:35:14

windows Cursor 配置MCP的小坑

以高德地图MCP举例按需求配置好以后，会提示 Client closed 解决方案， windows 需要更改一下 commandargs 新增一个npx保存后Cursor设置MCP页面Refresh一下即可，打开的终端不要关闭最后贴一下文本代码，方便复制粘贴 {"m…...

编程日记 2025/8/17 15:08:01

探秘串口服务器厂家：背后的故事与应用

在科技飞速发展的今天，串口服务器作为连接串口设备与网络的桥梁，在工业自动化、智能交通、智能家居等众多领域发挥着关键作用。你是否好奇，那些生产串口服务器的厂家究竟有着怎样的故事？它们的产品背后又蕴含着怎样的原理呢&#…...

编程日记 2025/8/23 12:16:21

二叉树详细讲解（2/2）

4. 实现链式结构二叉树⽤链表来表⽰⼀棵⼆叉树，即⽤链来指⽰元素的逻辑关系。通常的⽅法是链表中每个结点由三个域组成，数据域和左右指针域，左右指针分别⽤来给出该结点左孩⼦和右孩⼦所在的链结点的存储地址，其结构如下&#…...

编程日记 2025/8/21 11:32:46

SpringBoot整合Redis限流

通过一个完整的Spring Boot项目演示如何用Redis实现简单的API限流功能。我们将从零开始搭建项目。一、环境准备 1.1 开发环境要求 JDK 1.8IntelliJ IDEA（推荐）Redis 5.0（本地安装）Postman（测试用） 1.2…...

编程日记 2025/8/20 18:23:47

（Matlab）自动驾驶仿真设计驾驶场景、配置传感器并生成合成数据

驾驶场景仿真平台核心功能一、场景搭建与编辑 ‌可视化场景构建‌ 使用拖放界面创建道路网络和角色模型（车辆、行人等）支持欧洲新车评估计划（Euro NCAP）测试协议及其他预置场景模板二、传感器配置 ‌车载传感器系统‌ 支持…...

编程日记 2025/8/19 22:30:52

接口测试（get请求方法）-----------实战演练

1.最简单的get请求方法 eg:请求一个王者荣耀语音包的接口接口文档如下： 2.把接口地址、请求方法、请求参数写到postman相应位置 3.填写请求参数的值，点击发送按钮，即可获得到返回参数...

编程日记 2025/8/23 4:55:41

【赵渝强老师】TiDB的列存引擎：TiFlash

TiDB的TiFlash提供列式存储，且拥有借助ClickHouse高效实现的协处理器层。除此以外，它与TiKV非常类似，依赖同样的Multi-Raft体系，以Region为单位进行数据复制和分散。TiFlash以低消耗不阻塞TiKV写入的方式，实时复制TiKV…...

编程日记 2025/8/22 2:03:52

《vue3学习手记3》

标签的ref属性 vue3和vue2中的ref属性： 用在普通DOM标签上，获取的是DOM节点 ref用在组件标签上，获取的是组件实例对象区别在于： 1.vue3中person子组件中的数据父组件App不能直接使用，需要引入并使用defineExpose才可…...

编程日记 2025/8/17 20:47:30

【Vue】从 MVC 到 MVVM：前端架构演变与 Vue 的实践之路

个人博客：haichenyi.com。感谢关注一. 目录一–目录二–架构模式的演变背景三–MVC：经典的分层起点四–MVP：面向接口的解耦尝试五–MVVM：数据驱动的终极形态六–Vue：MVVM 的现代化实践二. 架构模…...

编程日记 2025/8/19 7:10:35

Docker Compose 命令实现动态构建和部署一、编写支持动态版本号的 docker-compose.yml version: 3.8services:myapp:build: context: . # Dockerfile所在目录args:APP_VERSION: ${TAG:-latest} # 从环境变量获取版本号，默认latestimage: myapp:${TAG:-latest} …...

编程日记 2025/8/21 2:13:59

工厂模式实现案例

场景一：配置文件解析（工厂模式实现） 1. 定义解析器接口与具体实现 from abc import ABC, abstractmethod import json import yaml # 需要安装PyYAML库：pip install pyyamlclass ConfigParser(ABC):"""配置文件解…...

编程日记 2025/8/21 4:59:34

Vue3.5 企业级管理系统实战（十五）：其他全局设置项

在设置面板中，除了主题颜色的选择设置，还可以添加其他全局配置选项，如 tagsView 导航栏，Logo 的显示隐藏配置等。 1 Settings 的 Pinia 配置在 src/stores/settings.ts 中添加要持久存储的全局配置项，这里是 tagsVi…...

编程日记 2025/8/20 7:05:55

L2-052 吉利矩阵分

L2-052 吉利矩阵 - 团体程序设计天梯赛-练习集所有元素为非负整数，且各行各列的元素和都等于 7 的 33 方阵称为“吉利矩阵”，因为这样的矩阵一共有 666 种。本题就请你统计一下，把 7 换成任何一个 [2,9] 区间内的正整数 L，把矩…...

编程日记 2025/8/24 8:21:15

408 计算机网络知识点记忆（9）

前言本文基于王道考研课程与湖科大计算机网络课程教学内容，系统梳理核心知识记忆点和框架，既为个人复习沉淀思考，亦希望能与同行者互助共进。（PS：后续将持续迭代优化细节） 往期内容 408 计算机网络知识…...

编程日记 2025/8/20 22:09:52

矩阵基础+矩阵转置+矩阵乘法+行列式与逆矩阵

GPU渲染过程矩阵什么是矩阵（Matrix） 向量 （3，9，88） 点乘：计算向量夹角叉乘：计算两个向量构成平面的法向量。矩阵矩阵有3行，2列，所以表示为M32 获取固…...

编程日记 2025/8/22 13:38:09

如何在 .NET 环境中使用 Npgsql 驱动连接 KaiwuDB

在现代软件开发中，数据库连接和操作是任何应用程序的核心部分。本文将介绍如何在 .NET 环境下，使用 Npgsql 驱动连接 KaiwuDB，并执行基本的数据库操作，包括创建表、插入数据和查询操作。我们假设您已经安装并配置好了 KaiwuDB 数据…...

编程日记 2025/8/19 10:17:27

【代理错误 django】Request error: HTTPSConnectionPool(host=‘‘, port=443): 、

❗问题 ❶：仍然是代理错误（ProxyError） 错误日志： Request error: HTTPSConnectionPool(hostxueshu.baidu.com, port443): Max retries exceeded ... Caused by ProxyError(Unable to connect to proxy, FileNotFoundError(2, N…...

编程日记 2025/8/19 5:28:00

5.9 《GPT-4调试+测试金字塔：构建高可靠系统的5大实战策略》

5.4 测试与调试：构建企业级质量的保障体系关键词：测试金字塔模型、GPT-4调试助手、LangChain调试模式、异步任务验证测试策略设计（测试金字塔实践） #mermaid-svg-RblGbJVMnCIShiCW {font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill…...

编程日记 2025/8/17 20:36:09