写这篇东西的起因是最近帮一个朋友排查线上订单查询慢的问题。他用的就是BETWEEN AND加时间条件乍一看很普通结果慢得离谱数据还经常对不上。排查到最后问题出在边界条件和隐式类型转换上。那一刻我意识到这个看起来简单到不能再简单的MySQL语法坑其实远比想象中多。BETWEEN AND做范围查询几乎是每个写SQL的人都会碰到的操作。但绝大多数人只用了它最浅的一层很少有人认真去琢磨边界值到底怎么算、日期时间怎么配合、怎么和索引打交道、什么时候该放弃它改用别的写法。这篇就把我这些年实际踩过的坑和验证过的经验拆开讲讲从基础语法到索引优化从日期陷阱到性能对比一次说透。1. 范围查询的基础BETWEEN AND的语法与边界语义1.1 基本语法与一个典型的错误认知BETWEEN AND的基本语法非常简单SELECT 列名 FROM 表名 WHERE 列名 BETWEEN 值1 AND 值2;它表达的意思是筛选出目标列的值大于等于值1且小于等于值2的所有记录。注意这里的关键词——大于等于和小于等于。两端的边界值是包含在内的。我见过特别多刚入门的朋友会把BETWEEN AND和、的关系搞混或者误以为它是开区间。这里可以直接记结论BETWEEN AND等价于加的组合它是闭区间两端都包含。1.2 数值型范围查询的实操演示用最经典的订单金额场景来演示一下订单ID金额状态100150.00已完成100299.90已完成1003100.00已完成1004150.50退款1005200.00已完成要查金额在100到200之间的订单写法和结果如下SELECT 订单ID, 金额 FROM 订单表 WHERE 金额 BETWEEN 100 AND 200;查询结果会包含1003、1004、1005三行金额分别为100.00、150.50、200.00。注意1003和1005正好踩在边界上这两条也是会被查出来的很多人测试的时候容易忽略这一点。1.3 字符串与日期类型使用BETWEEN AND的差异数值类型之外BETWEEN AND也常被用在字符串和日期字段上。但这两种场景一定要注意底层比较规则。字符串类型比较字符串是按字典序字符集排序规则比较的不是按长度或数值大小。什么意思呢举个例子查询名字在A和C之间的用户SELECT 用户名 FROM 用户表 WHERE 用户名 BETWEEN A AND C;这里B、Bobby、Cathy会被查出来C语言高手也可能被查出来因为中文字符在排序规则下的编码位置不同。但Chris就查不出来因为Chris的首字母虽然是C但它比字符串C长在字典序中Chris大于C所以超出范围了。这一点不加注意很容易出现莫名其妙的漏数据。日期类型比较日期本质上在MySQL内部也是按数值序列来存储和比较的。下面这种写法是最常见的日期范围查询SELECT * FROM 订单表 WHERE 下单日期 BETWEEN 2024-01-01 AND 2024-01-31;这个写法看着没毛病但里面藏着一个非常经典的坑我单独开一节详细讲这里先记住一句话BETWEEN AND在日期上的边界问题是重灾区。2. 日期时间范围查询最容易被边界坑杀的场景2.1 为什么BETWEEN 2024-01-01 AND 2024-01-31会漏数据直接上案例。假设有一张订单表下单日期字段是DATETIME类型里面有一条记录的下单日期是2024-01-31 23:59:59还有一条是2024-01-31 08:30:00。用下面的SQL去查一月的订单SELECT * FROM 订单表 WHERE 下单日期 BETWEEN 2024-01-01 AND 2024-01-31;结果会让你意外2024-01-31 23:59:59这条查询不到而2024-01-31 08:30:00这条能查到。原因在于当下单日期是DATETIME类型时字符串2024-01-31会被自动转换成2024-01-31 00:00:00。也就是说边界实际上是从2024-01-01 00:00:00到2024-01-31 00:00:00。1月31日零点之后的所有记录全部被排除在外了。这个坑之所以隐蔽是因为大多数测试数据不会精确到秒级别大家在1月31日这一天的数据量少、不容易引起注意。一旦上了生产环境月底最后一天的数据全丢问题就大了。2.2 正确的日期范围写法左闭右开区间要规避这个问题最稳妥、最推荐的做法是使用左闭右开区间再配合运算符SELECT * FROM 订单表 WHERE 下单日期 2024-01-01 AND 下单日期 2024-02-01;这个写法的逻辑是查所有大于等于1月1日零点、且小于2月1日零点的记录。这样1月31日全天包括23:59:59的数据都在范围内一整天一块不多一块不少。这也是为什么很多大厂SQL规范里明确规定禁止在两个日期之间使用BETWEEN AND特指日期时间类型字段而全部改成和的组合。2.3 DATE类型和DATETIME类型的使用差异如果字段是DATE类型只存储2024-01-31这种纯日期没有时分秒那BETWEEN 2024-01-01 AND 2024-01-31是没问题的因为它只有到天这一个精度。但问题来了如果字段里既有DATE类型又有DATETIME类型或者你写SQL时不确定字段类型怎么办在不确定的情况下最安全的选择永远是用和的写法因为无论字段是DATE还是DATETIME这种写法都不会出错。而BETWEEN AND在DATETIME类型上就是高风险操作。2.4 时间戳字段的边界陷阱除了DATE和DATETIME还有一种常见类型是TIMESTAMP。它的逻辑和DATETIME类似也是包含时分秒的。但也有个微妙区别TIMESTAMP受时区影响而DATETIME不受。这意味着如果数据库会话时区设置不同同一时间点在TIMESTAMP字段里显示的字符串可能不一样。如果表里用的是TIMESTAMP做范围查询时同样要采用左闭右开的写法并且注意不要让MySQL隐式地把字符串转成当前时区的时间值否则查出来的边界可能和预期差好几个小时。注意日期时间字段做范围查询时养成一个习惯——永远用的组合而不是BETWEEN AND。这个习惯能帮你避免掉90%以上的日期边界bug。3. 类型转换与查询条件中的隐藏坑3.1 当字段是字符串、值却是数字时发生了什么表结构设计不规范时会遇到一个经典问题某个字段在表里是VARCHAR类型但存储的内容是100、200这样的数字字符串。这时候写SELECT * FROM 订单表 WHERE 金额字段 BETWEEN 100 AND 200;MySQL没办法直接拿数字和字符串比较于是会触发隐式类型转换。规则是这样的如果比较的双方一个是数值类型一个是字符串类型MySQL会把字符串转换成数值再比较。所以上面的SQL执行时实际是SELECT * FROM 订单表 WHERE CAST(金额字段 AS SIGNED) BETWEEN 100 AND 200;问题随之而来CAST操作会导致该字段上的索引失效。如果表数据量一上来这就是典型的全表扫描查询慢到怀疑人生。更烦的是如果字符串里混了非数字字符比如100元、200AMySQL转换时会把开头的数字部分截出来后面的字符忽略转成100、200。如果开头连数字都没有就变成0。这种数据做范围查询时结果可能完全超出你的认知范围。3.2 避免在查询条件中对字段做函数或运算无论什么情况都尽量不要在WHERE条件里对字段本身做处理。例如-- 错误示例对字段做了操作索引失效 SELECT * FROM 订单表 WHERE DATE(下单时间) BETWEEN 2024-01-01 AND 2024-01-31; -- 正确示例不对字段做操作边界用左闭右开 SELECT * FROM 订单表 WHERE 下单时间 2024-01-01 AND 下单时间 2024-02-01;这个原则也不限于BETWEEN AND是所有SQL优化的通用规则。只要你让字段参与函数计算或隐式类型转换MySQL基本就放弃索引了。换成对条件的常量值做处理索引就能正常走。3.3 浮点数的边界精度问题BETWEEN AND用于浮点数字段时也有一个隐藏的精度陷阱。看这个例子SELECT * FROM 商品表 WHERE 价格 BETWEEN 0.99 AND 9.99;由于浮点数在计算机内部的二进制表示并不精确0.99在存储时可能是一个接近0.99但略有偏差的值。如果某条记录的价格是9.99但实际存储为9.990000000000002它会被范围条件包含这是好事但反过来如果边界值本身精度不对就会出现该包含的没包含、不该包含的被包含的情况。解决浮点比较的方法通常是不要直接用浮点存储金额改用DECIMAL类型精确小数或者在查询时把范围扩大一个极小值或者把值先乘以100转成整数再比较。最推荐的还是改表结构用DECIMAL这是根治方案。4. 索引利用与性能优化BETWEEN AND能走索引吗4.1 索引在BETWEEN AND下的工作方式先说结论在字段类型正确、没有隐式转换的前提下BETWEEN AND是可以正常走索引的。因为在上文讲过BETWEEN AND本质就是和的组合MySQL优化器会把它解析成范围条件使用索引进行范围扫描Range Scan。-- 优化器实际执行的等价条件 SELECT * FROM 订单表 WHERE 金额 100 AND 金额 200;范围扫描的效率介于点查等值查找和全表扫描之间。数据量越大走索引和全表扫描的差距越明显。特别是在千万级数据表上一条走索引的范围查询可能几十毫秒返回同样条件全表扫描可能要几十秒。4.2 联合索引与BETWEEN AND的位置关系这是很多人会忽略的优化点。如果表上有一个联合索引(status, created_at)那么查询条件应该怎么写才最高效-- 高效status走等值定位created_at走范围扫描 SELECT * FROM 订单表 WHERE status 已完成 AND created_at BETWEEN 2024-01-01 AND 2024-01-31; -- 低效created_at走了范围后面的user_id无法继续用索引 SELECT * FROM 订单表 WHERE created_at BETWEEN 2024-01-01 AND 2024-01-31 AND user_id 12345;联合索引遵循最左前缀原则。在(status, created_at, user_id)这种索引上如果查询条件里先用到范围条件created_at BETWEEN...那user_id就没法再走索引了只能是额外的过滤。所以在设计索引时要把等值条件放在前面范围条件放在最后。这也是为什么建议在写SQL之前先想清楚字段组合而不是等SQL写完了再回头考虑索引。4.3 如何确认BETWEEN AND是否有效利用索引确认方式很简单用EXPLAIN看一下执行计划EXPLAIN SELECT * FROM 订单表 WHERE 金额 BETWEEN 100 AND 200;关键看type列和key列type如果是range说明BETWEEN AND正常走了范围扫描这是健康的。type如果是ALL说明是全表扫描赶紧检查是否有隐式类型转换或索引缺失。key列显示了实际用到的索引名如果为NULL说明没有使用索引。举一个我实际排查过的例子一个订单明细表数据量在500万行左右原先查询某时间段的记录要4秒钟。用EXPLAIN一看type是ALL原因就是created_at是字符串类型而查询条件里传的是日期时间格式触发了隐式转换。后来把表结构改成DATETIME类型并把BETWEEN AND改成和的左闭右开写法扫描行数从500万降到2万查询时间从4秒降到80毫秒。这个优化前后对比足够说明问题。5. 性能对比BETWEEN AND与OR、IN的取舍5.1 为什么OR条件会让查询突然变慢初学者经常在范围查询上犯的一个错误是把多个等值条件用OR连接起来-- 不推荐的写法 SELECT * FROM 订单表 WHERE 金额 50 OR 金额 100 OR 金额 200;这种写法的问题在于OR会破坏索引的连续扫描特性。哪怕每条单值查询都能走索引但优化器可能把它拆分成多次访问或者干脆放弃索引直接扫全表。MySQL对OR的优化能力比较有限尤其是在多个字段之间做OR时索引利用率往往很低。5.2 IN在等值范围中的优势如果需求只是查某几个离散的值用IN替代OR会更好-- 推荐写法 SELECT * FROM 订单表 WHERE 金额 IN (50, 100, 200);IN本质上会被优化成多次等值访问如果字段有索引引擎可以高效地多次查找。相比ORIN的语义更清晰执行计划也更可控。在很多版本MySQL中IN大量值时会自动做排序和合并效率有保障。5.3 连续范围到底选BETWEEN还是IN这里把所有实际情况做个汇总对比场景推荐写法原因连续范围数值型字段BETWEEN AND等价于和走范围索引性能最佳连续范围日期时间字段避免包含边界导致的漏数据且语义更安全离散值集合IN多次等值访问比OR高效可走索引不连续但数量少的范围UNION ALL多个等值查询避免优化器对OR处理不当从执行效率角度看对于连续范围BETWEEN AND并不比加慢两者执行计划基本一致。但是从语义安全和可维护性看日期时间字段我强烈建议一律用加。有一个特别适合用IN的经典场景状态字段。比如订单表的status字段只有固定几个值待支付、已支付、已发货、已完成、已取消查询时要同时取已支付和已发货两种状态的订单这时候IN (已支付, 已发货)比写两个OR优雅得多索引利用率也更高。5.4 大范围查询中的排序和分页配合范围查询经常要和排序ORDER BY、分页LIMIT一起出现。这里也有一个容易被忽略的性能细节SELECT * FROM 订单表 WHERE 金额 BETWEEN 100 AND 200 ORDER BY 下单时间 LIMIT 20;如果金额和下单时间分别在两个索引上MySQL无法同时利用两个索引完成过滤和排序。它通常有两种选择走金额索引查出所有满足范围的记录再对结果做filesort排序最后取20条。走下单时间索引顺序扫描再过滤金额条件直到凑够20条。对于大表第二种方案往往更快因为不需要把所有满足范围条件的记录全部捞出来排序。但优化器不一定总能做出最优选择这时候可以试试用FORCE INDEX指定索引或者把排序字段加入联合索引中就像前面提到的(status, created_at)这种结构。6. 面试场景中的BETWEEN AND高频考点与易错问答6.1 考点一BETWEEN AND边界值是否包含这个问题在面试中出现率极高。直接答出三要素即可BETWEEN 1 AND 10两端都包含即 1 AND 10。边界是闭区间1和10都会被选中。如果不想包含某一端要手动改成、、的组合。6.2 考点二日期范围查询如何避免边界风险面试官如果问怎么查某个月的数据标准答案就是左闭右开结合下月第一天SELECT * FROM 订单表 WHERE 下单时间 2024-01-01 AND 下单时间 2024-02-01;同时要能答出BETWEEN 2024-01-01 AND 2024-01-31在DATETIME类型上会漏掉1月31日00:00:00之后的数据。6.3 考点三BETWEEN AND遇到NULL会怎样这是一个比较容易让人卡壳的问题。如果字段某一行是NULL那么无论BETWEEN的范围是什么这一行都是不会被查询出来的。因为NULL参与的判断结果既不是TRUE也不是FALSE而是未知UNKNOWN状态WHERE条件只保留结果为TRUE的记录。如果要查NULL值需要单独写IS NULL。6.4 考点四索引失效与性能排查思路面试官会给一个慢SQL问怎么排除性能问题。排查路径通常是先看EXPLAIN执行计划确认type是不是ALL全表扫描。检查WHERE条件里的字段有没有被函数包裹有没有隐式类型转换。检查字段是否缺索引联合索引是否遵守最左前缀原则。如果范围查询和排序同时存在看有没有产生filesort。确认数据量大小如果只有几千行走全表扫描也不是什么问题不要过度优化。6.5 考点五BETWEEN AND与IN的性能差异这个考点考察的是对离散值范围和连续值范围的理解。简洁回答BETWEEN AND适合连续范围走范围扫描。IN适合离散值集合走多次等值访问。它们不能互相替代的场景是连续范围用IN列举不现实以及离散值用BETWEEN会误包含中间值。在MySQL较新版本中IN列表太长时也会有限制注意控制长度。7. 高频问题的排查实录与个人经验总结7.1 问题一BETWEEN AND查出来的结果比预期少这类问题最容易让我联想到日期类型字段。如果发现少数据先跑一条SQL去验证边界数据SELECT * FROM 订单表 WHERE 下单时间 2024-01-31 00:00:00 AND 下单时间 2024-02-01 00:00:00;如果这条能查到数据而BETWEEN 2024-01-01 AND 2024-01-31查不到那就是边界问题无疑了。7.2 问题二BETWEEN AND查询速度突然变慢经验是两个方向排查之前快、现在慢大概率是数据量暴增导致全表扫描变大或者统计信息不准、优化器选错了执行计划。一直慢大概率是隐式类型转换或索引缺失。用EXPLAIN看执行计划是最快的诊断手段。实际案例里我还遇到过collation排序规则不一致导致的索引失效。表和查询条件的字符集排序规则不匹配MySQL不得不做隐式转换索引照样废掉。7.3 问题三查询结果包含预期外的边界记录这种情况一般是闭区间语义引起的。比如程序员想查大于100小于200的数据但写成了BETWEEN 100 AND 200100和200就都进来了。解法是改成SELECT * FROM 订单表 WHERE 金额 100 AND 金额 200;所以写SQL之前先想清楚边界到底要不要要哪端不要哪端这个习惯能省去后续一堆返工时间。7.4 问题四负数和零值在BETWEEN AND中的表现负数和零值在BETWEEN AND里的逻辑和正数完全一致只要字段类型是数值型就没问题。但如果字段是无符号整型UNSIGNED而范围的下界写成了负数比较时会触发隐式转换结果可能不符合预期。这一点在排查数据异常时要留心。7.5 个人踩坑实录一次线上事故的复盘最后分享一个我印象特别深的真实事故。某次处理一个报表需求需要把上个月产生的所有订单做汇总。当时的SQL写的是SELECT SUM(金额) FROM 订单表 WHERE 下单日期 BETWEEN 2024-03-01 AND 2024-03-31;上线后总金额和业务系统的对账差了一截查了半天才发现下单日期虽然是DATE类型但是表里有一条2024-03-31 23:59:59的记录。等等DATE类型明明不存时分秒为什么会这样后来一查表结构发现字段定义成了DATETIME但因为历史原因应用程序写入的数据绝大多数是零点整的日期值导致一直没暴露问题。而那一条异常数据恰恰有非零时分秒3月31日当天的数据整个被漏掉了。那次之后我给自己定了一条铁律所有涉及日期时间的范围查询一律用左闭右开区间绝不使用BETWEEN AND写日期条件。这个习惯后来帮我避开了不知道多少潜在事故。其实回归根本BETWEEN AND本身并不复杂复杂的是它和各数据类型、索引机制、边界语义组合之后产生的各种意外。记住闭区间、左闭右开、隐式转换这十二个字的精髓再配合EXPLAIN和边界检查这个知识点基本就不会再给你挖坑了。