MySQL隐式转换造成索引失效

本次测试使用的 MySQL 版本是 ，随着 MySQL 版本的更新某些特性可能会发生改变，本文不代表所述观点和结论于 MySQL 所有版本均准确无误，版本差异请自行甄别。 原文：<https://www.guitu18.com/post/2019/11/24/61.html 前言 数据库优化是一个任重而道远的任务，想要做优化必须深入理解数据库的各种特性。在开发过程中我们经常会遇到一些原因很简单但造成的后果却很严重的疑难杂症，这类问题往往还不容易定位，排查费时费力最后发现是一个很小的疏忽造成的，又或者是因为不了解某个技术特性产生的。 于数据库层面，最常见的恐怕就是索引失效了，且一开始因为数据量小还不易被发现。但随着业务的拓展数据量的提升，性能问题慢慢的就体现出来了，处理不及时还很容易造成雪球效应，最终导致数据库卡死甚至瘫痪。造成索引失效的原因可能有很多种，相关技术博客已经有太多了，今天我要记录的是 隐式转换造成的索引失效 。 数据准备 首先使用存储过程生成 1000 万条测试数据， 测试表一共建立了 7 个字段（包括主键）， 和 保存的是和 一样的顺序数字，其中 是字符串类型。 和 保存的都是主键对 5 的取模，目的是模拟实际应用中常用类似 type 类型的数据，但是 是没有建立索引的。 和 都是保存了一个 20 位长度的随机字符串， 不能为 ， 允许为 ，相应的生成测试数据的时候我也会在 字段生产少量 值（每 100 条数据产生一个 值）。 数据量比较大，还涉及使用 生成随机字符串，所以速度有点慢，稍安勿躁，耐心等待即可。 1000 万条数据，我用了 33 分钟才跑完（实际时间跟你电脑硬件配置有关）。这里贴几条生成的数据，大致长这样。 SQL 测试 先来看这组 SQL，一共四条，我们的测试数据表 是 类型， 是 类型，但是存储的数据都是跟主键 一样的顺序数字，两个字段都建立有索引。 这四条 SQL 都是有针对性写的，12 查询的字段是 int 类型，34 查询的字段是 类型。12 或 34 查询的字段虽然都相同，但是一个条件是数字，一个条件是用引号引起来的字符串。这样做有什么区别呢？先不看下边的测试结果你能猜出这四条 SQL 的效率顺序吗？ 经测试这四条 SQL 最后的执行结果却相差很大，其中 124 三条 SQL 基本都是瞬间出结果，大概在 0.001 0.005 秒，在千万级的数据量下这样的结果可以判定这三条 SQL 性能基本没差别了。但是第三条 SQL，多次测试耗时基本在 4.5 4.8 秒之间。 为什么 34 两条 SQL 效率相差那么大，但是同样做对比的 12 两条 SQL 却没什么差别呢？查看一下执行计划，下边分别 1234 条 SQL 的执行计划数据： 可以看到，124 三条 SQL 都能使用到索引，连接类型都为 ，扫描行数都为 1，所以效率非常高。再看看第三条 SQL，没有用上索引，所以为全表扫描， 直接到达 1000 万了，所以性能差别才那么大。 仔细观察你会发现，34 两条 SQL 查询的字段 是 类型的，查询条件等号右边加引号的第 4 条 SQL 是用到索引的，那么是查询的数据类型和字段数据类型不一致造成的吗？如果是这样那 12 两条 SQL 查询的字段 是 类型，但是第 2 条 SQL 查询条件右边加了引号为什么还能用上索引呢。 查阅 MySQL 相关文档发现是隐式转换造成的，看一下官方的描述： 官方文档：12.2 Type Conversion in Expression Evaluation 当操作符与不同类型的操作数一起使用时，会发生类型转换以使操作数兼容。某些转换是隐式发生的。例如，MySQL 会根据需要自动将字符串转换为数字，反之亦然。以下规则描述了比较操作的转换方式： 1. 两个参数至少有一个是 时，比较的结果也是 ，特殊的情况是使用 对两个 做比较时会返回 ，这两种情况都不需要做类型转换 2. 两个参数都是字符串，会按照字符串来比较，不做类型转换 3. 两个参数都是整数，按照整数来比较，不做类型转换 4. 十六进制的值和非数字做比较时，会被当做二进制串 5. 有一个参数是 或 ，并且另外一个参数是常量，常量会被转换为 6. 有一个参数是 类型，如果另外一个参数是 或者整数，会将整数转换为 后进行比较，如果另外一个参数是浮点数，则会把 转换为浮点数进行比较 7. 所有其他情况下，两个参数都会被转换为浮点数再进行比较 根据官方文档的描述，我们的第 23 两条 SQL 都发生了隐式转换，第 2 条 SQL 的查询条件 ，左边是 类型右边是字符串，第 3 条 SQL 相反，那么根据官方转换规则第 7 条，左右两边都会转换为浮点数再进行比较。 先看第 2 条 SQL：` test1 10000 10000 '10000' 10000 test1 '10000' int 10000 '10000' 10000 10000 '10000a' '010000' '10000' 10000 num2='10000a' '010000' '10000' test1 num2='10000a' '010000' '10000' 0 'abc' 'a123bc' 'abc123' 0 '123abc' 123 '012abc' 012 12 '5.3a66b78c' 5.3 test1 0`，以数字开头的字符串会截取从第一个字符到第一个非数字内容为止的值为转化结果。 所以，我们在写 SQL 时一定要养成良好的习惯，查询的字段是什么类型，等号右边的条件就写成对应的类型。特别当查询的字段是字符串时，等号右边的条件一定要用引号引起来标明这是一个字符串，否则会造成索引失效触发全表扫描。 写在最后 如果内容对你有帮助的话，欢迎顺手给 JavaGuide 点一个免费的 Star 支持一下：GitHub | Gitee。 JavaGuide 已持续维护近七年，累计 6100+ 次提交，来自 620+ 位贡献者共同完善。你的 Star、反馈和 PR，都是这个项目继续更新的动力。 如果你正在准备后端/AI 应用开发面试，也可以了解一下我的知识星球，里面包括后端和 AI 实战项目、简历优化、一对一提问和高频考点资料，已经持续维护六年。