Блог

Я запрашиваю тележки для покупок в системе магазинов, например:

 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 не использует никакого ключа в подзапросе:

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

Даже принудительное использование индекса не приводит к тому, что БД использует его:

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

Использование столбца «электронная почта» или столбца «пользователь» работает нормально, используется ключ:

 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 записями, в результате чего запрос занимает около 30 секунд только для запроса подмножества из 50 записей. Выполнение запроса только с совпадением для «электронной почты» или только с совпадением для «пользователя» занимает всего около 1 секунды для 50 записей.

Поэтому мне нужно оптимизировать запрос. Я попытался превратить ОПЕРАЦИОННУЮ в ПРОФСОЮЗ:

 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): Неизвестный столбец «c.электронная почта» в предложении «где»

Есть идеи, как сделать этот запрос с использованием индексов быстрее?

Это альтернативный подход, использующий два left join s:

 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) . Есть несколько худших сценариев, в которых может быть много совпадений по обоим измерениям. Однако во многих случаях это может оказаться весьма эффективным, поскольку оно может использовать индексы, а не сканировать всю таблицу для каждой строки.

(Я предполагаю, что «с» означает «тележка».)

(начинаем все сначала)

Так 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 неявно является частью каждого вторичного индекса, внутренние выборки имеют индексы «покрытия».