하위 쿼리에 그룹 열이 있을 때 MySQL 8이 INDEX를 사용하지 않습니다.

하위 쿼리에 그룹 열이 있을 때 MySQL 8이 INDEX를 사용하지 않습니다.

방금 mariadb 5.5에서 MySQL 8로 이동했는데 업데이트 문의가 갑자기 느려졌습니다.더 조사해 보니 하위 쿼리에 그룹 열이 있는 경우 MySQL 8은 인덱스를 사용하지 않는 것으로 나타났습니다.

예를 들어, 다음은 샘플 데이터베이스입니다.테이블users유형별 사용자의 현재 잔액을 유지하고 테이블 '잔액'은 일별 총 잔액 기록을 유지합니다.

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()사용한 적이 있습니다.select5.5에는 업데이트 쿼리에 대한 설명이 없기 때문에 mariadb 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)

우리가 뭘 잘못하고 있는지 알아?

다른 사용자가 코멘트한 것처럼 업데이트 쿼리를 분할합니다.

계정 갱신 가입

그럼 당신의 질문

가입 조건으로

내부 선택 쿼리:

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

신분증과 모든 잔액의 합계를 따는 것, 그리고 오늘 하루의 모든 것을 실행하는 것 뿐입니다.정확한 양은 말할 것도 없고 어떤 사용자가 업데이트되고 있는지도 알 수 없습니다.먼저 사용자 ID별로 예상되는 결과를 보기 위한 쿼리를 가져옵니다.사용자 테이블에는 "uid"가 있지만 기본 키가 없으므로 동일한 "uid"에 대해 여러 개의 레코드가 있음을 암시합니다.(나에게) 계좌는 ex를 암시합니다.저는 은행 대표이며,따라서 특정 날짜에 나의 액티브한 고객 잔액 포트폴리오는 사용자 테이블의 합계입니다.

이왕이면 그 답을 찾아보자.

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

사용자별로 현재 총 잔액이 표시됩니다.이제 그룹이 계정 테이블에 다시 연결할 수 있는 "ID" 키를 제공합니다.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())

사용자 엔트리가 관련지어져 있지 않은 계정이 있는 경우 위의 답변은 적절하지 않습니다.예를 들어 모든 사용자가 빠져나가는 경우.따라서 계정에 사용자가 없는 경우 해당 계정의 잔액과 일수는 전날과 같습니다.이 문제를 해결하려면 다음과 같은 LEFT-JOIN을 사용할 수 있습니다.

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

왼쪽 조인 및 COALESCE()를 사용하면 사용자 테이블에 레코드 합계가 없는 경우 계정 잔액이 0으로 설정됩니다.

언급URL : https://stackoverflow.com/questions/59044056/mysql-8-is-not-using-index-when-subquery-has-a-group-column