当子查询具有组列时，MySQL 8不使用索引
当子查询具有组列时，MySQL 8不使用索引
提问于 2019-11-26 13:02:52
我们刚刚从mariadb5.5迁移到MySQL 8，一些更新查询突然变慢了。经过进一步研究，我们发现当子查询包含group列时，MySQL 8不使用索引。
例如，下面是一个示例数据库。表users维护每个类型的用户的当前余额，表'accounts‘维护每天的总余额历史记录。
CREATE DATABASE 'test';
CREATE TABLE `users` (
`uid` int(10) unsigned NOT NULL DEFAULT '0',
`balance` int(10) unsigned NOT NULL DEFAULT '0',
`type` int(10) unsigned NOT NULL DEFAULT '0',
KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `accounts` (
`uid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`balance` int(10) unsigned NOT NULL DEFAULT '0',
`day` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`uid`),
KEY `day` (`day`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;以下是更新帐号查询的说明
mysql> explain update accounts a inner join (
select uid, sum(balance) balance, day(current_date()) day from users) r
on r.uid=a.uid and r.day=a.day set a.balance=r.balance;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| 1 | UPDATE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table |
| 2 | DERIVED | users | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
2 rows in set, 1 warning (0.00 sec)如您所见，mysql没有使用索引。
经过进一步的研究，我发现如果从子查询中删除sum()，它就会开始使用索引。然而，mariadb 5.5并非如此，它在所有情况下都正确地使用了索引。
下面是两个带有和不带有sum()的select查询。我已经使用select query与MariaDB5.5进行了交叉检查，因为5.5没有提供更新查询的解释。
mysql> explain select * from accounts a inner join (
select uid, balance, day(current_date()) day from users
) r on r.uid=a.uid and r.day=a.day ;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+
| 1 | SIMPLE | a | NULL | ref | PRIMARY,day | day | 4 | const | 1 | 100.00 | NULL |
| 1 | SIMPLE | users | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.a.uid | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)并使用sum()
mysql> explain select * from accounts a inner join (
select uid, sum(balance) balance, day(current_date()) day from users
) r on r.uid=a.uid and r.day=a.day ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table |
| 2 | DERIVED | users | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
2 rows in set, 1 warning (0.00 sec)以下是mariadb 5.5的输出
MariaDB [test]> explain select * from accounts a inner join (
select uid, sum(balance) balance, day(current_date()) day from users
) r on r.uid=a.uid and r.day=a.day ;
+------+-------------+------------+------+---------------+------+---------+-----------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+------------+------+---------------+------+---------+-----------------------+------+-------------+
| 1 | PRIMARY | a | ALL | PRIMARY,day | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 10 | test.a.uid,test.a.day | 2 | Using where |
| 2 | DERIVED | users | ALL | NULL | NULL | NULL | NULL | 1 | |
+------+-------------+------------+------+---------------+------+---------+-----------------------+------+-------------+
3 rows in set (0.00 sec)知道我们做错了什么吗？
回答 1
Stack Overflow用户
发布于 2019-12-02 06:24:22
正如其他人所评论的那样，分解您的更新查询...
更新帐户联接
然后你的查询
在联接的条件下。
的内部select查询
select uid, sum(balance) balance, day(current_date()) day from users是唯一正在运行的东西，获取一些ID和所有余额的总和以及任何当天的数据。你永远不会知道哪个用户正在更新，更不用说正确的数量了。首先让您的查询查看每个用户ID的预期结果。虽然上下文没有意义，您的users表有一个"uid"，但没有主键，因此意味着同一个"uid“有多个记录。帐户(对我来说)意味着例如:我是一名银行代表，并且注册了多个用户帐户。因此，我在给定日期的活跃客户余额组合是users表中的总和。
话虽如此，让我们看看如何得到答案
select
u.uid,
sum( u.balance ) allUserBalance
from
users u
group by
u.uid这将显示每个用户目前的总余额是多少。group by现在为您提供了"ID“键，用于绑定回accounts表。在MySQL中，此场景的相关更新的语法为...(我使用上面的查询，并为连接的PreQuery指定别名"PQ“)
update accounts a
JOIN
( select
u.uid,
sum( u.balance ) allUserBalance
from
users u
group by
u.uid ) PQ
-- NOW, the JOIN ON clause ties the Accounts ID to the SUM TOTALS per UID balance
on a.uid = PQ.uid
-- NOW you can SET the values
set Balance = PQ.allUserBalance,
Day = day( current_date())现在，如果您的帐户不再有关联的用户条目，则以上将不会给出正确的答案...例如所有用户都可以退出。因此，任何帐户没有用户，他们的余额和一天的记录将截至前一天。要解决这个问题，您可以使用左连接，例如
update accounts a
LEFT JOIN
( select
u.uid,
sum( u.balance ) allUserBalance
from
users u
group by
u.uid ) PQ
-- NOW, the JOIN ON clause ties the Accounts ID to the SUM TOTALS per UID balance
on a.uid = PQ.uid
-- NOW you can SET the values
set Balance = coalesce( PQ.allUserBalance, 0 ),
Day = day( current_date())使用left-join和COALESCE()，如果用户表中没有记录总和，它会将帐户余额设置为零。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/59044056
复制相关文章
相似问题
腾讯云开发者
