企业官网建设流程全解析

• 文档
• 教程
• 后端

本文是 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 username='zhangsan' and pwd ='axsedf1sd'; -- 命中：优化器会自动调整谓词顺序，等价于上面一条 select username from user where pwd ='axsedf1sd' and username='zhangsan'; -- 命中：仅使用最左列 username select username from user where username='zhangsan';

而以下 SQL 不能命中索引：

-- 不命中：跳过了最左列 username，复合索引失效 select username from user where pwd ='axsedf1sd';

原理说明：复合索引本质上是按「第一列、第二列……依次有序」构建的 B+ Tree。只有从最左列开始、且各列连续使用的查询条件才能利用索引的有序性进行定位。上述三条可命中 SQL 中，「只查username」实际命中的是复合索引的最左前缀部分。

实战建议：

1. 复合索引的列顺序按照区分度从高到低排列，最常作为查询条件的列放最左；
2. 不要在复合索引中间跳过列，否则后续列无法命中；
3. 如果业务中存在大量单独按pwd查询的场景，则需要考虑为pwd单独建索引。

八、明确只有一条记录返回时使用 limit 1

select name from user where username='zhangsan' limit 1;

效果：当确认业务上username唯一（或只需任意一条结果）时，limit 1可以让数据库找到第一条匹配记录后立即停止游标移动，避免扫描后续索引项，从而提升效率。

注意：该优化建立在「结果集确实只需要一条」的前提下，若业务需要全部结果，加limit 1反而会造成数据缺失。

九、不要让数据库做强制类型转换

-- 不推荐：telno 是字符串类型，与整型常量比较时发生隐式类型转换，导致全表扫描 select name from user where telno=18722222222;

需要修改为与字段类型一致的写法：

-- 推荐：字符串常量与字符串字段直接比较 select name from user where telno='18722222222';

原理说明：当索引列参与隐式类型转换时，等价于「在索引列上套了一层转换函数」，与第六节的函数计算问题同理，索引失效、退化为全表扫描。虽然结果可能「碰巧」正确，但性能代价巨大。让 SQL 中的字面量类型与字段类型完全一致，是避免此类问题的最简单手段。

十、join 两表的关联字段类型必须一致

-- 不推荐：user.id 是 BIGINT，order.user_id 是 VARCHAR，join 时隐式转换导致索引失效 select * from user u join `order` o on u.id = o.user_id;

若进行 join 的字段两表类型不相同，即使关联字段上有索引也不会命中。

原理说明：关联字段类型不一致时，数据库需要对其中一侧做隐式类型转换，转换后的值与另一侧索引中的原始值无法直接比较，索引失效，退化为嵌套循环全表扫描。

实战建议：

1. 两表关联字段的数据类型、长度、字符集（collation）必须保持一致；
2. join 的字段尽量使用数值类型（INT/BIGINT）而非字符串；
3. 分库分表场景下尤其要注意——仓库文档 数据库水平垂直拆分 明确指出：拆分后多表/多库的关联查询不建议使用 join，一般的做法是做两次查询，既规避了跨库 join 的类型与路由问题，也避免了大表 join 的性能风险。

十一、从理论到生产：索引优化在分库分表场景的实践

上述十大原则在单表场景下已足够指导日常开发，而当数据量达到亿级、不得不分库分表时，索引命中的问题会更加尖锐。仓库文档 分表踩坑实践 记录了一次真实的亿级数据分表经历，其中多处印证了本篇文章的原则：

• 务必保留一个可排序的索引字段：原表没有可用于排序的索引，导致无法快速筛选数据，加索引需要数小时，整个数据迁移被严重拖延。这与「索引字段要尽可能小、区分度要高」的原则直接呼应；
• 分表后无法避免非 sharding 字段的全表扫描：所有分片方案都会遇到这个问题，文档给出的对策是引导业务尽量走分片字段查询，避免无意义的全表/全分片遍历；
• 分表数量取 2 的 N 次方：在取模分表方式下，即便今后再次分表，影响的数据也会尽量小；
• 分表后主键不能再依赖单表自增：需要统一的主键生成组件（时间戳+随机数、UUID、雪花算法等），相关实现可参考仓库 分布式 ID 生成器 文档。

也就是说：SQL 优化并不是孤立的一条条口诀，而是与表结构设计、索引设计、数据路由策略强耦合的系统工程。

十二、总结：SQL 优化速查清单

结合本文及仓库文档，日常写 SQL 时可对照以下清单自查：

延伸阅读：想深入理解这些规则背后的数据结构，建议结合仓库文档 MySQL 索引原理（B+ Tree 结构与查找过程）一起阅读；涉及大数据量拆分的场景，可继续阅读 数据库水平垂直拆分 与 分表踩坑实践，形成「索引原理 → SQL 优化 → 数据架构」的完整知识链。

• 文档
• 教程
• 后端