当子查询具有组列时，MySQL 8 不使用 INDEX

2022-01-15 00:00:00 mariadb mysql

我们刚刚从 mariadb 5.5 迁移到 MySQL 8，一些更新查询突然变慢了.经过进一步调查，我们发现 MySQL 8 在子查询有组列时不使用索引.

We have just moved from mariadb 5.5 to MySQL 8 and some of the update queries have suddenly become slow. On more investigation, we found that MySQL 8 does not use index when the subquery has group column.

例如，下面是一个示例数据库.表 users 维护每种类型的用户的当前余额，表 'accounts' 维护每天的总余额历史记录.

For example, below is a sample database. Table users maintain the current balance of the users per type and table 'accounts' maintain the total balance history per day.

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;

以下是更新帐户查询的说明

Below is a explanation for the query to update accounts

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没有使用索引.

As you can see, mysql is not using index.

经过进一步调查，我发现如果我从子查询中删除 sum()，它就会开始使用 index.html.但是，mariadb 5.5 并非如此，它在所有情况下都正确使用了索引.

On more investigation, I found that if I remove sum() from the subquery, it starts using index. However, that's not the case with mariadb 5.5 which was correctly using the index in all the case.

下面是两个带和不带sum() 的选择查询.我使用 select 查询与 mariadb 5.5 进行交叉检查，因为 5.5 没有对更新查询的解释.

Below are two select queries with and without sum(). I've used select query to cross check with mariadb 5.5 since 5.5 does not have explanation for update queries.

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 的输出

Below is output from 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)

知道我们做错了什么吗?

Any idea what are we doing wrong?

推荐答案

正如其他人评论的那样，将您的更新查询分开...

As others have commented, break your update query apart...

更新帐户加入

然后你的查询

在加入的条件下.

你的内部选择查询

select uid, sum(balance) balance, day(current_date()) day from users

是唯一正在运行的东西，它获取一些 ID 和所有余额的总和以及当天的任何内容.您永远不知道哪个用户正在更新，更不用说正确的数量了.首先获取您的查询以查看每个用户 ID 的预期结果.虽然上下文没有意义，您的用户表有一个uid"，但没有主键，因此暗示同一个uid"有多个记录.帐户(对我而言)意味着例如:我是银行代表并注册了多个用户帐户.因此，我在给定日期的活跃客户余额组合是用户表的总和.

is the only thing that is running, getting some ID and the sum of all balances and whatever the current day. You never know which user is getting updated, let alone the correct amount. Start by getting your query to see your expected results per user ID. Although the context does not make sense that your users table has a "uid", but no primary key thus IMPLYING there is multiple records for the same "uid". The accounts (to me) implies ex: I am a bank representative and sign up multiple user accounts. Thus my active portfolio of client balances on a given day is the sum from users table.

话虽如此，让我们看看得到那个答案

Having said that, lets look at getting that answer

select
      u.uid,
      sum( u.balance ) allUserBalance
   from
      users u
   group by
      u.uid

这将向您显示每位用户目前的总余额.该组现在为您提供ID"键以绑定到帐户表.在 MySQL 中，这种情况下相关更新的语法是……(我使用上面的查询并为连接的 PreQuery 提供别名PQ")

This will show you per user what their total balance is as of right now. The group by now gives you the "ID" key to tie back to the accounts table. In MySQL, the syntax of a correlated update for this scenario would be... (I am using above query and giving alias "PQ" for PreQuery for the join)

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())

现在，如果您的帐户不再关联用户条目...例如所有用户都退出，则上述内容不会给出正确答案.因此，无论帐户没有用户，他们的余额和日记录都将是前一天的.要解决此问题，您可以使用 LEFT-JOIN，例如

Now, the above will not give a proper answer if you have accounts that no longer have user entries associated... such as all users get out. So, whatever accounts have no users, their balance and day record will be as of some prior day. To fix this, you could to a LEFT-JOIN such as

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()，如果user表中没有记录求和，则将账户余额设置为零.

With the left-join and COALESCE(), if there is no record summation in the user table, it will set the account balance to zero.

相关文章