文档教程后端【免费下载链接】JCSprout‍ Java Core Sprout : basic, concurrent, algorithm项目地址https://gitcode.com/gh_mirrors/jc/JCSprout点击查看免费下载本文是 JCSprout「数据库」知识模块的核心实战指南围绕 SQL 优化 文档展开系统讲解索引命中的十大边界条件并结合 MySQL 索引原理、数据库水平垂直拆分 与分表踩坑实践等仓库文档从 B Tree 底层原理到生产级分库分表场景帮助读者写出真正能命中索引、可稳定支撑亿级数据量的 SQL。一、为什么 SQL 优化先谈「索引命中」在互联网应用中数据库的读写比例通常能达到 10:1且查询时的磁盘 IO 消耗极大。正如仓库文档 MySQL 索引原理 所阐述的如果能把一次查询的 IO 次数控制在常量级数据库的性能提升将非常明显这正是基于 B Tree 的索引结构出现的原因。B Tree 中所有数据存放在叶子节点非叶子节点不存放数据一次查询经历的 IO 次数由树的高度决定而树的高度又由磁盘块与数据项的大小决定——磁盘块越大、数据项越小树的高度越低这就是索引字段要尽可能小的根本原因。理解了这一点就能明白 SQL 优化的核心命题让查询尽可能命中索引避免全表扫描。本文整理的十大原则全部围绕这一命题展开。二、负向查询不能使用索引-- 不推荐负向查询无法命中索引 select name from user where id not in (1,3,4);应改写为正向查询-- 推荐正向 in 查询可命中索引 select name from user where id in (2,5,6);原理说明B Tree 索引天然支持按范围与等值快速定位而not in、not like、等负向条件要求排除大量记录优化器通常放弃索引而选择全表扫描。同样的规则也适用于is not null、!等写法日常开发中应尽量将业务条件转化为正向表达。三、前导模糊查询不能使用索引-- 不推荐前导 % 导致索引失效全表扫描 select name from user where name like %zhangsan; -- 推荐非前导模糊查询可命中索引 select name from user where name like zhangsan%;原理说明B Tree 索引按数据项有序排列like zhangsan%等价于范围查询[zhangsan, zhangsan\uffff)可以通过索引定位起始位置后顺序扫描而%zhangsan无法确定起始边界只能全表扫描。延伸建议如果业务确实需要频繁的前导模糊查询仓库文档给出的方案是——考虑使用Lucene等全文索引工具来替代数据库层的模糊匹配而不是在 MySQL 里硬扛。四、数据区分不明显的不建议创建索引以user表的性别字段为例取值只有「男/女」两种区分度极低。优化器估算后发现通过索引反而要回表读取大量数据通常仍会选择全表扫描索引形同虚设。判断标准只有区分度明显的字段才建议创建索引如身份证号、手机号、设备唯一标识IMEI等。仓库佐证分表踩坑实践 中 IoT 场景选取 IMEI 作为 sharding 字段正是因为该字段天然保持唯一性、区分度极高绝大多数业务都围绕它展开。这也从另一个角度印证了「高区分度字段」在查询与数据路由中的价值。五、字段的默认值不要为 null字段默认值为 null 会带来和预期不一致的查询结果null在 SQL 语义中不等于「空串」也不等于任何值where name null永远查不到数据必须写成is null包含null的列在建索引与查询时行为复杂例如is not null无法走索引与 Java 侧的 Bean 映射、统计函数count、sum配合时也容易产生歧义。建议为字段设置明确的默认值例如字符串使用空串、数值使用0、时间使用合理的业务起始时间将「无值」语义显式化。六、在字段上进行计算不能命中索引-- 不推荐对索引列做函数计算索引失效 select name from user where FROM_UNIXTIME(create_time) CURDATE();应改为对常量进行计算保持索引列独立-- 推荐索引列保持原样计算移到常量侧 select name from user where create_time FROM_UNIXTIME(CURDATE());原理说明B Tree 索引中存储的是字段原始值。当create_time被FROM_UNIXTIME()包裹后索引中的原始值与计算后的值无法直接比较优化器只能对每一行做完函数计算再筛选索引自然失效。通用原则是索引列上不做任何运算函数、算术、类型转换把运算全部放到常量一侧。七、最左前缀问题复合索引假设为user表的username、pwd字段创建了复合索引(username, pwd)以下 SQL 都可以命中索引-- 命中完整使用复合索引的两个列 select username from user where usernamezhangsan and pwd axsedf1sd; -- 命中优化器会自动调整谓词顺序等价于上面一条 select username from user where pwd axsedf1sd and usernamezhangsan; -- 命中仅使用最左列 username select username from user where usernamezhangsan;而以下 SQL 不能命中索引-- 不命中跳过了最左列 username复合索引失效 select username from user where pwd axsedf1sd;原理说明复合索引本质上是按「第一列、第二列……依次有序」构建的 B Tree。只有从最左列开始、且各列连续使用的查询条件才能利用索引的有序性进行定位。上述三条可命中 SQL 中「只查username」实际命中的是复合索引的最左前缀部分。实战建议复合索引的列顺序按照区分度从高到低排列最常作为查询条件的列放最左不要在复合索引中间跳过列否则后续列无法命中如果业务中存在大量单独按pwd查询的场景则需要考虑为pwd单独建索引。八、明确只有一条记录返回时使用 limit 1select name from user where usernamezhangsan limit 1;效果当确认业务上username唯一或只需任意一条结果时limit 1可以让数据库找到第一条匹配记录后立即停止游标移动避免扫描后续索引项从而提升效率。注意该优化建立在「结果集确实只需要一条」的前提下若业务需要全部结果加limit 1反而会造成数据缺失。九、不要让数据库做强制类型转换-- 不推荐telno 是字符串类型与整型常量比较时发生隐式类型转换导致全表扫描 select name from user where telno18722222222;需要修改为与字段类型一致的写法-- 推荐字符串常量与字符串字段直接比较 select name from user where telno18722222222;原理说明当索引列参与隐式类型转换时等价于「在索引列上套了一层转换函数」与第六节的函数计算问题同理索引失效、退化为全表扫描。虽然结果可能「碰巧」正确但性能代价巨大。让 SQL 中的字面量类型与字段类型完全一致是避免此类问题的最简单手段。十、join 两表的关联字段类型必须一致-- 不推荐user.id 是 BIGINTorder.user_id 是 VARCHARjoin 时隐式转换导致索引失效 select * from user u join order o on u.id o.user_id;若进行 join 的字段两表类型不相同即使关联字段上有索引也不会命中。原理说明关联字段类型不一致时数据库需要对其中一侧做隐式类型转换转换后的值与另一侧索引中的原始值无法直接比较索引失效退化为嵌套循环全表扫描。实战建议两表关联字段的数据类型、长度、字符集collation必须保持一致join 的字段尽量使用数值类型INT/BIGINT而非字符串分库分表场景下尤其要注意——仓库文档 数据库水平垂直拆分 明确指出拆分后多表/多库的关联查询不建议使用 join一般的做法是做两次查询既规避了跨库 join 的类型与路由问题也避免了大表 join 的性能风险。十一、从理论到生产索引优化在分库分表场景的实践上述十大原则在单表场景下已足够指导日常开发而当数据量达到亿级、不得不分库分表时索引命中的问题会更加尖锐。仓库文档 分表踩坑实践 记录了一次真实的亿级数据分表经历其中多处印证了本篇文章的原则务必保留一个可排序的索引字段原表没有可用于排序的索引导致无法快速筛选数据加索引需要数小时整个数据迁移被严重拖延。这与「索引字段要尽可能小、区分度要高」的原则直接呼应分表后无法避免非 sharding 字段的全表扫描所有分片方案都会遇到这个问题文档给出的对策是引导业务尽量走分片字段查询避免无意义的全表/全分片遍历分表数量取 2 的 N 次方在取模分表方式下即便今后再次分表影响的数据也会尽量小分表后主键不能再依赖单表自增需要统一的主键生成组件时间戳随机数、UUID、雪花算法等相关实现可参考仓库 分布式 ID 生成器 文档。也就是说SQL 优化并不是孤立的一条条口诀而是与表结构设计、索引设计、数据路由策略强耦合的系统工程。十二、总结SQL 优化速查清单结合本文及仓库文档日常写 SQL 时可对照以下清单自查场景错误示范正确姿势负向查询id not in (...)改写为正向in模糊查询like %xx改为like xx%高频场景引入全文索引低区分度字段对性别建索引仅对高区分度字段建索引默认值字段允许 null设置明确的非 null 默认值列上计算FROM_UNIXTIME(col) ...运算移到常量侧复合索引跳过最左列查询遵循最左前缀原则单条返回不加 limit确认后加limit 1类型转换telno 18722222222telno 18722222222join 关联两表字段类型不一致类型/长度/字符集保持一致延伸阅读想深入理解这些规则背后的数据结构建议结合仓库文档 MySQL 索引原理B Tree 结构与查找过程一起阅读涉及大数据量拆分的场景可继续阅读 数据库水平垂直拆分 与 分表踩坑实践形成「索引原理 → SQL 优化 → 数据架构」的完整知识链。赞分享文档教程后端【免费下载链接】JCSprout‍ Java Core Sprout : basic, concurrent, algorithm项目地址https://gitcode.com/gh_mirrors/jc/JCSprout点击查看免费下载相关推荐rust-mysql-simple未来展望新特性路线图与社区贡献指南rust mysql simple未来展望新特性路线图与社区贡献指南 rust mysql simple是一个纯Rust实现的MySQL客户端库提供高效的数文档教程后端JCSprout 解读MySQL 索引原理 —— 从 B Tree 数据结构到索引使用原则JCSprout 解读MySQL 索引原理 —— 从 B Tree 数据结构到索引使用原则 导读 本篇文章基于 JCSprout 知识库中的 MySQL 索文档教程后端MySQL优化终极指南10个实用技巧让你的数据库性能提升10倍MySQL优化终极指南10个实用技巧让你的数据库性能提升10倍 在Java开发中数据库性能往往是系统瓶颈的关键所在。MySQL作为最流行的关系型数据库之一文档教程后端上一篇如何三步永久保存微信聊天记录WeChatMsg完整解决方案指南下一篇iNiR自动主题系统详解如何使用Material You从壁纸生成完美配色方案创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考