这里有一个慢 SQL 查询等你来优化

关注
发布于: 2020 年 05 月 02 日
背景 
最近工作上遇到一个"神奇"的问题, 或许对大家有帮助, 因此形成本文.
 
问题大概是, 我有两个表 TableA, TableB, 其中 TableA 表大概百万行级别(存量业务数据), TableB 表几行(新业务场景, 数据还未膨胀起来), 语义上  TableA.columnA = TableB.columnA, 其中 columnA 上建立了索引, 但查询的时候确巨慢无比, 基本上到 5-6 秒, 明显跟预期不符合.  
﻿
下面我以一个具体的例子来说明吧, 模拟其中的 SQL 查询场景. 
﻿
场景重现﻿
user_info 表, 为了场景尽量简单, 我只 mock 了其中的三列数据. 
﻿
mysql> desc userinfo;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | autoincrement |
| uid   | varchar(64)  | NO   | MUL | NULL    |                |
| name  | varchar(255) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
﻿
userscore 表, 其中 uid 和 userinfo.uid 语义一致:
﻿
mysql> desc userinfo;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | autoincrement |
| uid   | varchar(64)  | NO   | MUL | NULL    |                |
| name  | varchar(255) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
﻿
其中数据情况如下, 都是很常见的场景. 
﻿
mysql> select  from userscore limit 2;
+----+--------------------------------------+-------+
| id | uid                                  | score |
+----+--------------------------------------+-------+
|  5 | 111111111                            |   100 |
|  6 | 55116d58-be26-4eb7-8f7e-bd2d49fbb968 |   100 |
+----+--------------------------------------+-------+
2 rows in set (0.00 sec)
mysql> select  from userinfo limit 2;
+----+--------------------------------------+-------------+
| id | uid                                  | name        |
+----+--------------------------------------+-------------+
|  1 | 111111111                            | tanglei     |
|  2 | 55116d58-be26-4eb7-8f7e-bd2d49fbb968 | hudsonemily |
+----+--------------------------------------+-------------+
2 rows in set (0.00 sec)
mysql> select count() from userscore
    -> union
    -> select count() from userinfo;
+----------+
| count(*) |
+----------+
|        4 |
|  3000003 |
+----------+
2 rows in set (1.39 sec)
﻿
索引情况是: 
﻿
mysql> show index from userscore;
+------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Nonunique | Keyname  | Seqinindex | Columnname | Collation | Cardinality | Subpart | Packed | Null | Indextype | Comment | Indexcomment |
+------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| userscore |          0 | PRIMARY   |            1 | id          | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| userscore |          1 | indexuid |            1 | uid         | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
mysql> show index from userinfo;
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Nonunique | Keyname  | Seqinindex | Columnname | Collation | Cardinality | Subpart | Packed | Null | Indextype | Comment | Indexcomment |
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| userinfo |          0 | PRIMARY   |            1 | id          | A         |     2989934 |     NULL | NULL   |      | BTREE      |         |               |
| userinfo |          1 | indexuid |            1 | uid         | A         |     2989934 |     NULL | NULL   |      | BTREE      |         |               |
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
﻿
查询业务场景: 已知 userscore.id, 需要关联查询对应userinfo的信息, (大家先忽略这个具体业务场景是否合理哈). 那么对应的 SQL 很自然的如下: 
﻿
mysql> select * from userscore us
    -> inner join userinfo 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 (1.18 sec)
﻿
请忽略其中的数据, 我刚开始 mock 了 100W, 然后又重复导入了两遍, 因此数据有一些重复.  300W 数据, 最后查询出来也是 1.18 秒. 按道理应该更快的. 老规矩 explain 看看啥情况?
﻿
mysql> explain
    -> select * from userscore us
    -> inner join userinfo ui on us.uid = ui.uid
    -> where us.id = 5;
+----+-------------+-------+-------+-------------------+---------+---------+-------+---------+-------------+
| id | selecttype | table | type  | possiblekeys     | key     | keylen | ref   | rows    | Extra       |
+----+-------------+-------+-------+-------------------+---------+---------+-------+---------+-------------+
|  1 | SIMPLE      | us    | const | PRIMARY,indexuid | PRIMARY | 4       | const |       1 | NULL        |
|  1 | SIMPLE      | ui    | ALL   | NULL              | NULL    | NULL    | NULL  | 2989934 | Using where |
+----+-------------+-------+-------+-------------------+---------+---------+-------+---------+-------------+
2 rows in set (0.00 sec)
﻿
发现 user_info表没用上索引, 全表扫描近 300W 数据? 现象是这样, 为什么呢? 
﻿
你不妨思考一下, 如果你遇到这种场景, 应该怎么去排查? 
﻿
-----------
﻿
我当时也是"一顿操作猛如虎", 然并卵? 尝试了什么多种 sql 写法来完成这个操作.
﻿
比如更换Join表的顺序(驱动表/被驱动表)
﻿
mysql> explain select * from userinfo ui inner join userscore us on us.uid = ui.uid where us.id = 5;
+----+-------------+-------+-------+-------------------+---------+---------+-------+---------+-------------+
| id | selecttype | table | type  | possiblekeys     | key     | keylen | ref   | rows    | Extra       |
+----+-------------+-------+-------+-------------------+---------+---------+-------+---------+-------------+
|  1 | SIMPLE      | us    | const | PRIMARY,indexuid | PRIMARY | 4       | const |       1 | NULL        |
|  1 | SIMPLE      | ui    | ALL   | NULL              | NULL    | NULL    | NULL  | 2989934 | Using where |
+----+-------------+-------+-------+-------------------+---------+---------+-------+---------+-------------+
2 rows in set (0.00 sec)
﻿
再比如用子查询: 
﻿
mysql> explain select * from userinfo where uid in  (select uid from userscore where id = 5);
+----+-------------+------------+-------+-------------------+---------+---------+-------+---------+-------------+
| id | selecttype | table      | type  | possiblekeys     | key     | keylen | ref   | rows    | Extra       |
+----+-------------+------------+-------+-------------------+---------+---------+-------+---------+-------------+
|  1 | SIMPLE      | userscore | const | PRIMARY,indexuid | PRIMARY | 4       | const |       1 | NULL        |
|  1 | SIMPLE      | userinfo  | ALL   | NULL              | NULL    | NULL    | NULL  | 2989934 | Using where |
+----+-------------+------------+-------+-------------------+---------+---------+-------+---------+-------------+
2 rows in set (0.00 sec)
﻿
最终, 还是没有结果. 但直接单表查询写 SQL 确能用上索引. 
﻿
mysql> select  from userinfo where uid = '111111111';
+---------+-----------+---------+
| id      | uid       | name    |
+---------+-----------+---------+
|       1 | 111111111 | tanglei |
| 3685399 | 111111111 | tanglei |
| 3685400 | 111111111 | tanglei |
| 3685401 | 111111111 | tanglei |
| 3685402 | 111111111 | tanglei |
| 3685403 | 111111111 | tanglei |
+---------+-----------+---------+
6 rows in set (0.01 sec)
mysql> explain select  from userinfo where uid = '111111111';
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-----------------------+
| id | selecttype | table     | type | possiblekeys | key       | keylen | ref   | rows | Extra                 |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | userinfo | ref  | indexuid     | indexuid | 194     | const |    6 | Using index condition |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-----------------------+
1 row in set (0.01 sec)
﻿
问题解决 
尝试更换检索条件, 比如更换 uid 直接关联查询, 索引仍然用不上, 差点放弃了都. 在准备求助 DBA 前, 看了下表的建表语句. 
﻿
mysql> show create table userinfo;
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                 |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| userinfo | CREATE TABLE userinfo (
  id int(11) NOT NULL AUTOINCREMENT,
  uid varchar(64) NOT NULL,
  name varchar(255) DEFAULT NULL,
  PRIMARY KEY (id),
  KEY indexuid (uid) USING BTREE
) ENGINE=InnoDB AUTOINCREMENT=3685404 DEFAULT CHARSET=utf8 |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table userscore;
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                             |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| userscore | CREATE TABLE userscore (
  id int(11) NOT NULL AUTOINCREMENT,
  uid varchar(64) NOT NULL,
  score float DEFAULT NULL,
  PRIMARY KEY (id),
  KEY indexuid (uid)
) ENGINE=InnoDB AUTOINCREMENT=9 DEFAULT CHARSET=utf8mb4 |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
﻿
完全有理由怀疑因为字符集不一致的问题导致索引失效的问题了. 
于是修改了小表(真实线上环境可别乱操作)的字符集与大表一致, 再测试下. 
﻿
mysql> select  from userscore us
    -> inner join userinfo 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 userscore us
    -> inner join userinfo ui on us.uid = ui.uid
    -> where us.id = 5;
+----+-------------+-------+-------+-------------------+-----------+---------+-------+------+-------+
| id | selecttype | table | type  | possiblekeys     | key       | keylen | ref   | rows | Extra |
+----+-------------+-------+-------+-------------------+-----------+---------+-------+------+-------+
|  1 | SIMPLE      | us    | const | PRIMARY,indexuid | PRIMARY   | 4       | const |    1 | NULL  |
|  1 | SIMPLE      | ui    | ref   | indexuid         | indexuid | 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 userscore us  inner join userinfo ui on us.uid = ui.uid where us.id = 5;
+----+-------------+-------+-------+-------------------+---------+---------+-------+---------+----------+-------------+
| id | selecttype | table | type  | possiblekeys     | key     | keylen | ref   | rows    | filtered | Extra       |
+----+-------------+-------+-------+-------------------+---------+---------+-------+---------+----------+-------------+
|  1 | SIMPLE      | us    | const | PRIMARY,indexuid | 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.userscore us join test.userinfo ui where (('111111111' = convert(test.ui.uid using utf8mb4))) |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
﻿
索引列参与计算了, 每次都要根据字符集去转换, 全表扫描, 你说能快得起来么? 
﻿
至于这个问题为什么会发生? 综合来看, 就是因为历史原因, 老业务场景中的原表是假 utf8, 新业务新表采用了真 utf8mb4. 
﻿
考虑新表的时候, 忽略和原库字符集的比较. 其实, 发现库里面的不同表可能都有不同的字符集, 不同人建的时候可能都依据个人喜好去选择了不同的字符集. 由此可见, 开发规范有多重要. 
虽然知道索引列不能参与计算, 但这个场景下都是相同的类型,  varchar(64) 最终查询过程中仍然发生了类型转换. 因此需要把字段字符集不一致等同于字段类型不一致.
如果这个 case, 利用 fail-fast 的理念的话, 发现不一致, 直接不让 join 会不会更好? (就像 char v.s varchar 不能 join 一样). 
﻿
留一道思考题 ﻿
你能解释如下情况吗? 查询结果表现为何不一致? 注意一下 SQL 的执行顺序, 查询优化器工作流程, 以及其中的 Using join buffer (Block Nested Loop), 建议多看看 [MySQL 官方手册](https://dev.mysql.com/doc/refman/5.6/en/) 深入背后原理.  
﻿
mysql> select  from userinfo ui
    -> inner join userscore us on us.uid = ui.uid
    -> where us.uid = '111111111';
+---------+-----------+---------+----+-----------+-------+
| id      | uid       | name    | id | uid       | score |
+---------+-----------+---------+----+-----------+-------+
|       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 |  5 | 111111111 |   100 |
+---------+-----------+---------+----+-----------+-------+
6 rows in set (1.14 sec)
mysql> select  from userinfo ui
    -> inner join userscore us on us.uid = ui.uid
    -> where ui.uid = '111111111';
+---------+-----------+---------+----+-----------+-------+
| id      | uid       | name    | id | uid       | score |
+---------+-----------+---------+----+-----------+-------+
|       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 |  5 | 111111111 |   100 |
+---------+-----------+---------+----+-----------+-------+
6 rows in set (0.00 sec)
﻿
mysql> explain
    -> select  from userinfo ui
    -> inner join userscore us on us.uid = ui.uid
    -> where us.uid = '111111111';
+----+-------------+-------+------+---------------+-----------+---------+-------+---------+----------------------------------------------------+
| id | selecttype | table | type | possiblekeys | key       | keylen | ref   | rows    | Extra                                              |
+----+-------------+-------+------+---------------+-----------+---------+-------+---------+----------------------------------------------------+
|  1 | SIMPLE      | us    | ref  | indexuid     | indexuid | 258     | const |       1 | Using index condition                              |
|  1 | SIMPLE      | ui    | ALL  | NULL          | NULL      | NULL    | NULL  | 2989934 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+-----------+---------+-------+---------+----------------------------------------------------+
2 rows in set (0.00 sec)
mysql> explain
    -> select  from userinfo ui
    -> inner join userscore us on us.uid = ui.uid
    -> where ui.uid = '111111111';
+----+-------------+-------+------+---------------+-----------+---------+-------+------+----------------------------------------------------+
| id | selecttype | table | type | possiblekeys | key       | keylen | ref   | rows | Extra                                              |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+----------------------------------------------------+
|  1 | SIMPLE      | ui    | ref  | indexuid     | indexuid | 194     | const |    6 | Using index condition                              |
|  1 | SIMPLE      | us    | ALL  | index_uid     | NULL      | NULL    | NULL  |    4 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+----------------------------------------------------+
2 rows in set (0.01 sec)
﻿
说明: 本文测试场景基于 MySQL 5.6, 另外, 本文案例只是为了说明问题, 其中的 SQL 并不规范(例如尽量别用 select * 之类的), 请勿模仿(模仿了我也不负责)​.  为了写本文, 可花了不少时间, 建 DB, 灌mock数据等等, 如果觉得有用, 还望你帮忙"在看", "转发". 最后留一个思考题供讨论, 欢迎留言说出你的看法. 
﻿
打个广告﻿
阿里云ECS弹性计算服务是阿里云的最重要的云服务产品之一。弹性计算服务是一种简单高效，处理能力可弹性伸缩的计算服务。我们始终致力于利用和创造业界最新的前沿技术，让更多的客户轻松享受这些技术红利，在云上快速构建更稳定、安全的应用，提升运维效率，降低IT成本，使客户更专注于自己的核心业务创新。弹性计算重新定义了人们使用计算资源的方式，这一新的方式正在并且将一直影响着关于计算资源的生态和经济圈。我们正在创造历史，我们真诚地邀请您加入我们的队伍。
﻿
最近团队释放不少 HC, 诚招 P6/P7/P8 的同学, 本组同学主要招聘后端研发同学([JD在此]()), 感兴趣的同学可扫描下面二维码加我联系. 
﻿
另外, 2021 届校招/实习生岗位也正在进行中([详情请戳]), 如果你是 2020-11 -- 2021-07 月之间毕业, 同时对阿里巴巴感兴趣, 也欢迎联系我帮忙内推. 
﻿
参考资料﻿
[explain-extended 文档](https://dev.mysql.com/doc/refman/5.6/en/explain-extended.html)
[mock数据生成器](https://faker.readthedocs.io/en/master/index.html#)
[Block Nested-Loop and Batched Key Access Joins](https://dev.mysql.com/doc/refman/5.6/en/bnl-bka-optimization.html)
﻿
发布于: 2020 年 05 月 02 日阅读数: 78
原文链接:【http://xie.infoq.cn/article/e94ed741004a233104225a599】。文章转载请联系作者。
石头

关注
码农@阿里云，公众号：程序猿石头 2018.06.13 加入
程序员@阿里云，曾工作于大疆|宜信|腾讯，欢迎关注交流。
评论 (1 条评论)

发布
石头
1. markdown 链接识别有点问题；
2. 代码语法渲染有点问题；
2020 年 05 月 02 日 23:04
 0 回复
没有更多了
创作场景

这里有一个慢 SQL 查询等你来优化

背景

场景重现

问题解决

挖掘根因

留一道思考题

打个广告

参考资料

石头

评论 (1 条评论)
