2개의 인덱스의 "OR"을 더 빠른 솔루션으로 대체(UNION)?

2개의 인덱스의 "OR"을 더 빠른 솔루션으로 대체(UNION)?

다음과 같은 상점 시스템에서 쇼핑 카트를 조회합니다.

DROP TABLE IF EXISTS c;
CREATE TABLE c (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user` int(10) unsigned DEFAULT NULL,
  `email` VARCHAR(255) NOT NULL DEFAULT '', 
  `number` VARCHAR(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `user`(`user`),
  KEY `email`(`email`),
  UNIQUE KEY `number`(`number`)
) ENGINE=InnoDB;

INSERT INTO c SET user=1, email="test1@example.com", number="00001";
INSERT INTO c SET user=2, email="test2@example.com", number="00002";
INSERT INTO c SET user=3, email="test3@example.com", number="00003";
INSERT INTO c SET user=4, email="test1@example.com", number="00004";
INSERT INTO c SET user=1, email="test1@example.com", number="00005";

동일한 사용자 또는 동일한 이메일을 가진 카트 수를 나타내는 컬럼으로 c의 기록을 조회해야 합니다.그래서 나는 한다:

SELECT c.number, 
       (SELECT COUNT(DISTINCT (id)) FROM c AS c2
                  WHERE c2.email = c.email OR c2.user = c.user
       ) AS ordercount
FROM c;
   

+--------+------------+
| number | ordercount |
+--------+------------+
| 00001  |          3 |
| 00002  |          1 |
| 00003  |          1 |
| 00004  |          3 |
| 00005  |          3 |
+--------+------------+

이 방법은 작동하지만 MySQL/MariaDB는 하위 쿼리에서 키를 사용하지 않기 때문에 OR이 매우 느립니다.

EXPLAIN SELECT c.number, 
               (SELECT COUNT(DISTINCT (id)) FROM c AS c2
                   WHERE c2.email = c.email OR c2.user = c.user
               ) AS ordercount
        FROM c;

+----+--------------------+-------+------------+------+---------------------------+--    ----+---------+------+------+----------+-------------+
| id | select_type        | table | partitions | type | possible_keys             | key  | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+-------+------------+------+---------------------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY            | c     | NULL       | ALL  | NULL                      | NULL | NULL    | NULL |    5 |   100.00 | NULL        |
|  2 | DEPENDENT SUBQUERY | c2    | NULL       | ALL  | PRIMARY,number,user,email | NULL | NULL    | NULL |    5 |    36.00 | Using where |
+----+--------------------+-------+------------+------+---------------------------+------+---------+------+------+----------+-------------+

인덱스를 강제로 사용하더라도 DB는 인덱스를 사용하지 않습니다.

EXPLAIN SELECT c.number, 
               (SELECT COUNT(DISTINCT (id)) FROM c AS c2 FORCE INDEX(email, user)
                  WHERE c2.email = c.email OR c2.user = c.user
               ) AS ordercount
        FROM c;

+----+--------------------+-------+------------+------+---------------------------+--    ----+---------+------+------+----------+-------------+
| id | select_type        | table | partitions | type | possible_keys             | key  | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+-------+------------+------+---------------------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY            | c     | NULL       | ALL  | NULL                      | NULL | NULL    | NULL |    5 |   100.00 | NULL        |
|  2 | DEPENDENT SUBQUERY | c2    | NULL       | ALL  | PRIMARY,number,user,email | NULL | NULL    | NULL |    5 |    36.00 | Using where |
+----+--------------------+-------+------------+------+---------------------------+------+---------+------+------+----------+-------------+

"email" 열 또는 "user" 열 중 하나를 사용하면 올바르게 작동합니다. 키는 다음과 같이 사용됩니다.

EXPLAIN SELECT c.number, 
               (SELECT COUNT(DISTINCT (id)) FROM c AS c2 WHERE c2.email = c.email) AS ordercount
        FROM c;

+----+--------------------+-------+------------+------+---------------------------+-------+---------+--------------+------+----------+-------------+
| id | select_type        | table | partitions | type | possible_keys             | key   | key_len | ref          | rows | filtered | Extra       |
+----+--------------------+-------+------------+------+---------------------------+-------+---------+--------------+------+----------+-------------+
|  1 | PRIMARY            | c     | NULL       | ALL  | NULL                      | NULL  | NULL    | NULL         |    5 |   100.00 | NULL        |
|  2 | DEPENDENT SUBQUERY | c2    | NULL       | ref  | PRIMARY,number,user,email | email | 767     | test.c.email |    3 |   100.00 | Using index |
+----+--------------------+-------+------------+------+---------------------------+-------+---------+--------------+------+----------+-------------+

문제는 쿼리가 약 500.000개의 엔트리를 가진 큰 테이블에서 실행되기 때문에 쿼리는 50개의 레코드의 서브셋을 쿼리하는 데만 약 30초가 걸린다는 것입니다."이메일"과 일치하는 경우에만 쿼리를 실행하거나 "사용자"와 일치하는 경우에만 쿼리를 실행하면 50개의 레코드에 대해 약 1초밖에 걸리지 않습니다.

그래서 쿼리를 최적화해야 합니다.수술실을 UNION으로 바꾸려고 했습니다.

SELECT c.number, 
(SELECT COUNT(DISTINCT (id)) FROM 
    ((SELECT u1.id FROM c AS u1 WHERE
     u1.email = c.email
    )
    UNION DISTINCT
    (SELECT u2.id FROM c AS u2 WHERE
    u2.user = c.user
    )) AS u2
) AS ordercount
FROM c;

에러 1054(42S22):'where 절'의 알 수 없는 열 'c.email'

인덱스를 사용하여 이 쿼리를 더 빠르게 만드는 방법을 알고 계십니까?

이것은 두 가지 방법을 사용하는 대체 접근법입니다.left joins:

select c.*,
       count(distinct coalesce(ce.id, cu.id))
from c left join
     c ce
     on c.email = ce.email left join
     c cu
     on c.user = cu.user and not cu.email <=> ce.email
group by c.id;

이것은 다른 인덱스를 사용할 수 있습니다.c(user)그리고.c(email).

기본적으로, 이것은 두 개의 분리된 차원을 따라 결합되고, 그 다음 두 차원을 합쳐서count(distinct). 양쪽 차원에 일치하는 항목이 많을 수 있는 최악의 시나리오가 있습니다.그러나 대부분의 경우 각 행에 대해 전체 테이블을 검색하는 대신 인덱스를 사용할 수 있기 때문에 이 방법은 매우 잘 작동합니다.

("c"는 "카트"를 의미한다고 생각합니다.)

(다시 시작)

부터number이UNIQUE, 그것은 더 나을지도 모른다.PRIMARY KEY. 또한 제거한다.id.

CREATE FUNCTION Ct(_user INT, _email VARCHAR(255))
    RETURNS VARCHAR(20)
RETURN
    SELECT COUNT(DISTINCT number)
        FROM
            ( SELECT number
                FROM c
                WHERE user = _user
            ) UNION ALL
            ( SELECT number
                FROM c
                WHERE email = _email
            );

그럼 하세요

SELECT number, Ct(user, email)
    FROM c;

참고로 이중 구분은 피했습니다.또한 PK는 암묵적으로 각 보조 인덱스의 일부이므로 내부 Selects에는 "커버링" 인덱스가 있습니다.

언급URL : https://stackoverflow.com/questions/69284955/replace-or-on-2-indexes-with-a-faster-solution-union