新闻详情

新闻详情

首页 / 资讯中心 / 详情

【MySQL11】进阶篇 | 索引_#3使用规则

发布时间:2026/11/19 1:40:14来源:尧图网络
【MySQL11】进阶篇 | 索引_#3使用规则
目录验证索引执行效率联合索引最左前缀法则全部索引失效常见原因or、null、数据分布引发的索引失效SQL 索引提示use /ignore/force index覆盖索引、回表原理前缀索引使用教程单列索引 vs 联合索引对比索引创建全套设计原则一、验证索引效率无索引状态sqlselect *from tb_sku where sn 1000000003124没有索引会进行全表扫描查询速度很慢。创建 B 树单列索引sqlcreate index idx_sku_sn on tb_sku(sn);建立索引之后查询会走 B 树索引检索大幅提速。MySQL 普通索引底层结构为 B‑Tree二、联合索引核心最左前缀法则多字段联合索引查询必须匹配索引最左侧前列不能跳过索引字段跳过后面字段跳过位置之后索引失效示例联合索引顺序(profession,age,status)正常命中全部索引sqlexplain select *from tb_user where profession 软件工程 and age 31 and status 0;命中前两列索引sqlexplain select *from tb_user where profession 软件工程 and age 31;跳过最左首字段 → 索引直接失效typeALL 全表扫描sqlexplain select *from tb_user where age 31 and status 0;跳过中间 age 字段 → age 之后 status 索引失效仅 profession 生效sqlexplain select *from tb_user where profession 软件工程 and status 0;where 条件字段顺序不影响MySQL 优化器会自动调整顺序满足最左前缀即可生效sqlexplain select *from tb_user where age 31 and status 0 and profession 软件工程范围查询陷阱联合索引遇到 范围条件范围右侧所有字段索引直接失效sql-- age30 是范围查询status索引失效 select *from tb_user where profession 软件工程 and age 30 and status 0优化方案 优先使用 等值边界代替大于小于范围尽量把范围字段放在联合索引最后一位。三、高频索引失效场景1. 在索引列上做运算、函数、隐式类型转换sql-- phone为字符串索引字段不加单引号发生隐式转换索引失效 explain select *from tb_user where phone 17799999015;索引字段必须保持原生类型不要运算、不要调用函数、不要类型转换2. like 模糊查询like xxx%尾部通配索引生效like %xxx、like %xxx%头部带通配符索引失效全表扫描3. or 连接条件一侧无索引则整条 SQL 索引失效sql-- id带索引、age没有索引or导致索引失效 explain select *from tb_user where id 10 or age 23;解决办法给 or 后面 age 字段新建索引。4. 字段 is null /is not nullsql-- 有概率命中索引 select *from tb_user where profession is null -- 大概率索引失效全表扫描 typeall select *from tb_user where profession is not null5. MySQL 优化器判断全表扫描更快主动放弃索引当查询条件匹配表中绝大多数的数据索引查找成本高于全表扫描MySQL 自动放弃索引。sql-- 绝大多数phone满足该条件触发全表扫描 explain select *from tb_user where phone 1973892485四、SQL 提示手动控制 MySQL 选用索引适合一张表存在多条索引MySQL 优化器选错索引时手动干预sql-- 创建单列索引 create index idx_user_pro on tb_user(profession); -- 1.use index建议MySQL使用指定索引 select *from tb_user use index(idx_user_pro) where profession 软件工程; -- 2.ignore index忽略指定索引 select *from tb_user ignore index(idx_user_pro) where profession 软件工程; -- 3.force index强制使用该索引 select *from tb_user force index(idx_user_pro) where profession 软件工程;五、覆盖索引 回表查询基础原理聚集索引主键索引B 树叶子节点存放该行全部字段数据辅助索引普通索引叶子节点只存放主键 id回表通过普通索引拿到主键 id 之后再去主键索引读取整行数据多出一次 IO 查询。覆盖索引定义查询所需要的全部字段都存在于联合索引当中不需要回表读取主键索引执行计划 Extra 字段出现Using index 命中覆盖索引性能最高Using index condition 使用索引检索但是需要回表查询数据案例联合索引(profession,age,status)sql-- select * 需要回表 explain select *from tb_user where profession 软件工程 and age 31 and status 0 -- 查询字段全部包含在索引内触发覆盖索引Using‑index explain select id, profession from tb_user where profession 软件工程 and age 31 and status 0避坑准则禁止习惯性写select *极易触发回表拖慢查询速度SQL 优化例题现有字段id, username, password, statussqlselect id, username, password from tb_user where username itcast;优化方案建立联合索引idx_username_up(username,password)触发覆盖索引免去回表。六、前缀索引适用场景varchar、text 等超长字符串字段完整建立索引占用磁盘 IO 大、索引体积臃肿。 只存储字段前面一部分字符当作索引节省索引存储空间。创建语法sql-- 取该字段前n位作为索引 create index idx_xxxx on table_name(column(n));索引选择性核心参数选择性 不重复数值 ÷ 表总记录数数值越接近 1索引效果越好唯一索引选择性 1sql-- 完整字段选择性 select count(distinct email) / count(*) from tb_user; -- 截取前10位前缀选择性 select count(distinct substring(email, 1, 10))/count(*) from tb_user;调试不同截取长度在存储空间与选择性之间取平衡点。七、单列索引 vs 联合索引单列索引一个索引只包含单个字段sql-- phone单列索引查询phonename条件命中phone索引之后必须回表查找name select *from tb_user where phone 1779999991110 and name 韩信联合索引sqlcreate index idx_user_phone_name on tb_user(phone, name)查询条件phone name索引已经携带两个字段能够触发覆盖索引、跳过回表查询。开发规范优先建立合适顺序的联合索引减少大量单列索引遵循最左前缀规划索引字段顺序等值字段放前面、范围查询字段放在索引末尾。八、完整索引创建设计原则数据表数据量大的时候才建立索引小表没必要经常用于 where 查询、join 连接、order by、group by 的字段建立索引区分度高的字段建立索引优先创建唯一索引长字符串 varchar/text 字段采用前缀索引节约磁盘优先设计联合索引利用覆盖索引避免回表 IO一张表索引数量需要管控索引过多会拖慢 insert/update/delete 写入速度索引字段设置NOT NULLNULL 会造成索引失效、查询优化困难。
网站建设高端定制企业官网
RELATED

相关资讯

更多精彩内容,欢迎继续阅读

较早相关资讯

最新相关资讯

【外设】之大彩串口显示屏 2026/11/18 21:21:17

【外设】之大彩串口显示屏

大彩串口屏初步使用 1 .官网下载 STM32 屏幕 GUI 设计资料 http://www.gz-dc.com/category/typeid/4112 找到 STM32 Keil 工程,移植相关代码因项目而异进行移植,由于项目简单,本人只对用到的指令接口进行修改。 比如:注意事项&…

阅读更多 →
无法下载Windows系统iso文件 2026/11/18 14:20:46

无法下载Windows系统iso文件

当我遇到这个问题的时候,我打开了一个网站: 登录 然后我打算下载的时候: 突然那个官方的连接就可以下载了:

阅读更多 →
【清华代码熊】DeepSeek V4.1 Flash 后训练详解 2026/11/18 20:01:42

【清华代码熊】DeepSeek V4.1 Flash 后训练详解

📌 上期解析了 DeepSeek V4.1 Flash 模型架构改进,本期解析 DeepSeek V4.1 Flash 预训练/后训练技术: 🌟 预训练:45T 文本 多模态混合语料、直接训练 sparse attention(取消 DeepSeek V4 的 dense 冷启动&…

阅读更多 →
Shuffle-R1: Efficient RL framework for Multimodal Large Language Models via Data-centric Dynamic ... 2026/11/18 11:04:06

Shuffle-R1: Efficient RL framework for Multimodal Large Language Models via Data-centric Dynamic ...

文章主要内容和创新点 主要内容 本文聚焦于多模态大语言模型(MLLM)强化学习(RL)训练中的效率问题,提出了一个名为Shuffle-R1的框架。研究发现,当前RL训练存在两个关键缺陷: 优势值坍缩(Advantage Collapsing):批次中大多数优势值集中在零附近,导致有效梯度信号被淹…

阅读更多 →
PRvL: Quantifying the Capabilities and Risks of Large Language Models for PII Redaction 2026/11/18 15:55:22

PRvL: Quantifying the Capabilities and Risks of Large Language Models for PII Redaction

一、文章主要内容总结 本文聚焦于利用大型语言模型(LLMs)实现个人身份信息(PII)脱敏的研究,旨在解决传统脱敏方法(如基于规则的系统、领域特定命名实体识别(NER)模型)泛化能力差、跨格式/跨语境适应性弱的问题。 研究通过全面评估多种LLM架构(包括密集型LLM(D-LLM…

阅读更多 →
LLaVA-RE: Binary Image-Text Relevancy Evaluation with Multimodal Large Language Model 2026/11/18 21:21:16

LLaVA-RE: Binary Image-Text Relevancy Evaluation with Multimodal Large Language Model

文章主要内容和创新点 主要内容 本文聚焦于二进制图像-文本相关性评估任务(判断图像与文本“相关”或“不相关”),针对该任务中文本格式多样、相关性定义随场景变化等挑战,提出了基于多模态大语言模型(MLLM)的解决方案LLaVA-RE。 模型设计:LLaVA-RE基于LLaVA 1.5架构,…

阅读更多 →

今日资讯

本周资讯

本月资讯

看完文章仍有疑问?

联系尧图顾问,获取一对一建站咨询

立即免费咨询 📞 400-888-8888
📞 ✉