阿里的程序员也不过如此,竟被一个简单的 SQL 查询难住

栏目: IT技术 · 发布时间: 4年前

内容简介:最近工作上遇到一个”神奇”的问题, 或许对大家有帮助, 因此形成本文.下面我以一个具体的例子来说明吧, 模拟其中的 SQL 查询场景.

(请原谅我, 标题党一回, 花几分钟看看, 或许对你有帮助)

背景

最近工作上遇到一个”神奇”的问题, 或许对大家有帮助, 因此形成本文.

问题大概是, 我有两个表 TableA, TableB, 其中 TableA 表大概百万行级别(存量业务数据), TableB 表几行(新业务场景, 数据还未膨胀起来), 语义上   TableA.columnA = TableB.columnA , 其中 columnA 上建立了索引, 但查询的时候确巨慢无比, 基本上到 5-6 秒 , 明显跟预期不符合.

下面我以一个具体的例子来说明吧, 模拟其中的 SQL 查询场景.

场景重现

  • user_info 表, 为了场景尽量简单, 我只 mock 了其中的三列数据.

  • user_score 表, 其中  uid 和  user_info.uid 语义一致.

阿里的 <a href='https://www.codercto.com'>程序员</a> 也不过如此,竟被一个简单的 SQL 查询难住

  • 其中数据情况如下, 都是很常见的场景.

阿里的程序员也不过如此,竟被一个简单的 SQL 查询难住

  • 索引情况是

阿里的程序员也不过如此,竟被一个简单的 SQL 查询难住

  • 查询业务场景: 已知 user_score.id , 需要关联查询对应 user_info 的信息, (大家先忽略这个具体业务场景是否合理哈). 那么对应的 SQL 很自然的如下:

阿里的程序员也不过如此,竟被一个简单的 SQL 查询难住

请忽略其中的数据, 我刚开始 mock 了 100W, 然后又重复导入了两遍, 因此数据有一些重复.  300W 数据, 最后查询出来也是 1.18 秒. 按道理应该更快的. 老规矩 explain 看看啥情况?

阿里的程序员也不过如此,竟被一个简单的 SQL 查询难住

发现 user_info 表没用上索引, 全表扫描近 300W 数据? 现象是这样, 为什么呢?

你不妨思考一下, 如果你遇到这种场景, 应该怎么去排查?

阿里的程序员也不过如此,竟被一个简单的 SQL 查询难住

(分割线, 花 10 秒想想? )

我当时也是”一顿操作猛如虎”, 然并卵? 尝试了什么多种 sql 写法来完成这个操作. 比如更换Join表的顺序(驱动表/被驱动表),  再比 如用子查询.  最终, 还是没有结果. 但直接单表查询写 SQL 确能用上索引.

阿里的程序员也不过如此,竟被一个简单的 SQL 查询难住

问题解决

尝试更换检索条件, 比如更换 uid 直接关联查询, 索引仍然用不上, 差点放弃了都. 在准备求助 DBA 前, 看了下表的建表语句.

阿里的程序员也不过如此,竟被一个简单的 SQL 查询难住

完全有理由怀疑因为字符集不一致的问题导致索引失效的问题了.

于是修改了小表(真实线上环境可别乱操作)的字符集与大表一致, 再测试下.

mysql> select * from user_score us
-> inner join user_info ui on us.uid = ui.uid
-> where us.id = 5;
+----+-----------+-------+---------+-----------+---------+
| id | uid | score | id | uid | name |
+----+-----------+-------+---------+-----------+---------+
| 5 | 111111111 | 100 | 1 | 111111111 | tanglei |
| 5 | 111111111 | 100 | 3685399 | 111111111 | tanglei |
| 5 | 111111111 | 100 | 3685400 | 111111111 | tanglei |
| 5 | 111111111 | 100 | 3685401 | 111111111 | tanglei |
| 5 | 111111111 | 100 | 3685402 | 111111111 | tanglei |
| 5 | 111111111 | 100 | 3685403 | 111111111 | tanglei |
+----+-----------+-------+---------+-----------+---------+
6 rows in set (0.00 sec)

mysql> explain
-> select * from user_score us
-> inner join user_info ui on us.uid = ui.uid
-> where us.id = 5;
+----+-------------+-------+-------+-------------------+-----------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-------------------+-----------+---------+-------+------+-------+
| 1 | SIMPLE | us | const | PRIMARY,index_uid | PRIMARY | 4 | const | 1 | NULL |
| 1 | SIMPLE | ui | ref | index_uid | index_uid | 194 | const | 6 | NULL |
+----+-------------+-------+-------+-------------------+-----------+---------+-------+------+-------+
2 rows in set (0.00 sec)

果然 work 了.

挖掘根因

其实深究原因, 就是网上各种 MySQL 军规/规约所提到的, “索引列不要参与计算”.  这次这个 case, 如果知道 explain extended + show warnings 这个 工具 的话, (以前都不知道 explain 后面还能加 extended 参数), 可能就尽早”恍然大悟”了. (最新的 MySQL 8.0版本貌似不需要另外加这个关键字).

看下效果. (啊, 我还得把字符集改回去!!!)

mysql> explain extended select * from user_score us  inner join user_info ui on us.uid = ui.uid where us.id = 5;
+----+-------------+-------+-------+-------------------+---------+---------+-------+---------+----------+-------------+
| id | select_type | table | type  | possible_keys     | key     | key_len | ref   | rows    | filtered | Extra       |
+----+-------------+-------+-------+-------------------+---------+---------+-------+---------+----------+-------------+
|  1 | SIMPLE      | us    | const | PRIMARY,index_uid | PRIMARY | 4       | const |       1 |   100.00 | NULL        |
|  1 | SIMPLE      | ui    | ALL   | NULL              | NULL    | NULL    | NULL  | 2989934 |   100.00 | Using where |
+----+-------------+-------+-------+-------------------+---------+---------+-------+---------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                              |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select '5' AS `id`,'111111111' AS `uid`,'100' AS `score`,`test`.`ui`.`id` AS `id`,`test`.`ui`.`uid` AS `uid`,`test`.`ui`.`name` AS `name` from `test`.`user_score` `us` join `test`.`user_info` `ui` where (('111111111' = convert(`test`.`ui`.`uid` using utf8mb4))) |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

(滑动看右边)

索引列参与计算了, 每次都要根据字符集去转换, 全表扫描, 你说能快得起来么?

至于这个问题为什么会发生? 综合来看, 就是因为历史原因, 老业务场景中的原表是假 utf8 , 新业务新表采用了真 utf8mb4 .

  1. 考虑新表的时候, 忽略和原库字符集的比较. 其实, 发现库里面的不同表可能都有不同的字符集, 不同人建的时候可能都依据个人喜好去选择了不同的字符集. 由此可见, 开发规范有多重要 .

  2. 虽然知道索引列不能参与计算, 但这个场景下都是相同的类型, varchar(64) 最终查询过程中仍然发生了类型转换. 因此需要把字段字符集不一致等同于字段类型不一致.

  3. 如果这个 case, 利用 fail-fast 的理念的话, 发现不一致, 直接不让 join 会不会更好? (就像 char v.s varchar 不能 join 一样).

说明: 本文测试场景基于 MySQL 5.6, 另外, 本文案例只是为了说明问题, 其中的 SQL 并不规范(例如尽量别用 select * 之类的), 请勿模仿(模仿了我也不负责 阿里的程序员也不过如此,竟被一个简单的 SQL 查询难住 ).  为了写本文, 可花了不少时间, 建 DB, mock数据, 包括排版公众号(啊,公众号后台对代码格式还是不友好, markdown 转来代码格式还是有问题)等等, 如果觉得有用, 还望你帮忙"在看", "转发". 最后留一个思考题供讨论, 欢迎留言说出你的看法. 

留一道思考题

你能解释如下情况吗? 查询结果表现为何不一致?  注意一下 SQL 的执行顺序, 查询优化器工作流程, 以及其中的 Using join buffer (Block Nested Loop) , 可以多看看 [MySQL 官方手册](https://dev.mysql.com/doc/refman/5.6/en/) 深入了解背后的过程和原理.

阿里的程序员也不过如此,竟被一个简单的 SQL 查询难住

打个广告

阿里云ECS弹性计算服务是阿里云的最重要的云服务产品之一。弹性计算服务是一种简单高效,处理能力可弹性伸缩的计算服务。我们始终致力于利用和创造业界最新的前沿技术,让更多的客户轻松享受这些技术红利,在云上快速构建更稳定、安全的应用,提升运维效率,降低IT成本,使客户更专注于自己的核心业务创新。弹性计算重新定义了人们使用计算资源的方式,这一新的方式正在并且将一直影响着关于计算资源的生态和经济圈。我们正在创造历史,我们真诚地邀请您加入我们的队伍。

最近团队释放不少 HC, 诚招 P6/P7/P8 的同学, 本组同学主要招聘后端研发同学(JD在此), 感兴趣的同学可扫描下面二维码加我联系.

另外, 2021 届校招/实习生岗位也正在进行中(详情请戳), 如果你是 2020-11 — 2021-07 月之间毕业, 同时对阿里巴巴感兴趣, 也欢迎联系我帮忙内推.

阿里的程序员也不过如此,竟被一个简单的 SQL 查询难住

参考资料

  • explain-extended 文档

  • mock数据生成器

  • Block Nested-Loop and Batched Key Access Joins

点阅读原文, 有相关链接, 若觉得有用, 请右下角点击"在看", 帮忙转发, 感谢.


以上所述就是小编给大家介绍的《阿里的程序员也不过如此,竟被一个简单的 SQL 查询难住》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

算法与数据结构

算法与数据结构

张乃孝 / 高等教育出版社 / 2006-1 / 31.00元

《算法与数据结构:C语言描述(第2版)》以数据结构为主线,算法为辅线组织教学内容。全书共分10章:绪论、线性表、字符串、栈与队列、二叉树与树、集合与字典、高级字典结构、排序、图和算法分析与设计。《算法与数据结构:C语言描述(第2版)》体系完整,概念清楚,内容充实,取材适当。第一版被列入“面向21世纪课程教材”,2004年被评为“北京市高等教育精品教材”,第二版被列入普通高等教育“十一五”国家级规划......一起来看看 《算法与数据结构》 这本书的介绍吧!

在线进制转换器
在线进制转换器

各进制数互转换器

随机密码生成器
随机密码生成器

多种字符组合密码

SHA 加密
SHA 加密

SHA 加密工具