대기열 테이블에서 사용자 위치를 가져오는 데 너무 느립니다.

대기열 테이블에서 사용자 위치를 가져오는 데 너무 느립니다.

다음과 같은 MariaDB 테이블이 있습니다.

+--------+--------+--------+---------------------+
| realm  |  key2  | userId |        date         |
+--------+--------+--------+---------------------+
|    AB3 |    123 |      1 | 2017-08-04 17:30:00 |
|    AB3 |    124 |      1 | 2017-08-04 17:30:00 |
|    AB3 |    125 |      1 | 2017-08-04 17:30:00 |
|    XY7 |     97 |      2 | 2017-08-04 17:35:00 |
|    XY7 |     98 |      2 | 2017-08-04 17:35:00 |
|    XY7 |     99 |      2 | 2017-08-04 17:35:00 |
|    AB3 |    110 |      3 | 2017-08-04 17:40:00 |
|    AB3 |    111 |      3 | 2017-08-04 17:40:00 |
+--------+--------+--------+---------------------+

PRIMARY_KEY (realm, key2)
INDEX (realm, userId)
INDEX (date)

이 테이블은 사용자 액션을 처리하기 위한 일종의 큐로 작동합니다.기본적으로 서버는 항상 이 테이블에서 가장 오래된 데이터를 가져와 처리하고 이 테이블에서 삭제합니다.각 레름에는 이 큐를 처리하는 자체 서버가 있습니다.

이제 해당 영역의 큐에서 사용자의 위치를 확인합니다.그래서 위의 예시를 사용하여 레름 'AB3'에서 userId 3의 위치를 요청했을 때 결과를 얻고 싶습니다.2렐름 AB3에서는 다른 1명의 사용자(userId 1)만이 조기에 처리되기 때문입니다.

(행)key2 이 예에서는 관계가 없을 수 있습니다.중요한 키의 일부이기 때문에 적절한 솔루션을 찾는 데 도움이 될 수 있기 때문에 포함시켰을 뿐입니다.)

SQL 스키마는 다음과 같습니다.

CREATE TABLE `queue` (
  `realm` varchar(5) NOT NULL,
  `key2` int(10) UNSIGNED NOT NULL,
  `userId` int(10) UNSIGNED NOT NULL,
  `date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `queue` (`realm`, `key2`, `userId`, `date`) VALUES
('AB3', 110, 3, '2017-08-04 17:40:00'),
('AB3', 111, 3, '2017-08-04 17:40:00'),
('AB3', 123, 1, '2017-08-04 17:30:00'),
('AB3', 124, 1, '2017-08-04 17:30:00'),
('AB3', 125, 1, '2017-08-04 17:30:00'),
('XY7', 97, 2, '2017-08-04 17:35:00'),
('XY7', 98, 2, '2017-08-04 17:35:00'),
('XY7', 99, 2, '2017-08-04 17:35:00');

ALTER TABLE `queue`
  ADD PRIMARY KEY (`realm`,`key2`),
  ADD KEY `ru` (`realm`,`userId`) USING BTREE,
  ADD KEY `date` (`date`);

1,000,000개의 엔트리가 있는 테이블에서 동작하는 것처럼 보이지만 매우 느린 쿼리(약 3초)가 떠올랐습니다.

SELECT (COUNT(DISTINCT `realm`, `userId`)+1) `position`
FROM `queue`
WHERE `realm` = 'AB3'
AND `date` < (
  SELECT `date`
  FROM `queue`
  WHERE `realm` = 'AB3' AND `userId` = 3
  GROUP BY `realm`, `userId`
)

SQL Fider: http://sqlfiddle.com/ #!9/fb04gl/9/0

EXPLAIN EXTENDED다음 쿼리의 경우:

+----+-------------+-------+-------------+-----------------+------------+---------+-------+---------+----------+------------------------------------------+--+
| id | select_type | table |    type     |  possible_keys  |    key     | key_len |  ref  |  rows   | filtered |                  Extra                   |  |
+----+-------------+-------+-------------+-----------------+------------+---------+-------+---------+----------+------------------------------------------+--+
|  1 | PRIMARY     | queue | ref         | PRIMARY,ru,date | PRIMARY    |     767 | const | 5266123 |   100.00 | Using where                              |  |
|  2 | SUBQUERY    | queue | index_merge | PRIMARY,ru      | ru,PRIMARY | 771,767 |       |     496 |    75.00 | Using intersect(ru,PRIMARY); Using where |  |
+----+-------------+-------+-------------+-----------------+------------+---------+-------+---------+----------+------------------------------------------+--+

1,000,000개 정도의 엔트리가 있는 테이블에서 이 쿼리를 더 빨리 실행할 수 있도록 최적화할 수 있는 방법을 알고 계십니까?

---

이 테이블에서 실행되는 기타 쿼리:

SELECT `m`.*
FROM `queue` `m`
JOIN (
    SELECT `m`.*
    FROM `queue` `m`
    WHERE `m`.`realm` = ?
    ORDER BY `date` ASC
    LIMIT 1
) `mm` ON `m`.`realm` = `mm`.`realm` AND `m`.`userId` = `mm`.`userId`;

그리고.

DELETE FROM `queue` WHERE `realm` = ? AND `userId` = ?;

인덱스를 최적화하려면 어떻게 해야 합니까?

테이블 DDL에 뭔가 문제가 있는 것 같아요.어쨌든, 저는 당신의 질문을 다음과 같이 다시 쓸 것입니다.

SELECT (COUNT(DISTINCT `userId`)+1) `position`
FROM `queue`
WHERE `realm` = 'AB3'
    AND `date` < (
        SELECT min(`date`)
        FROM `queue`
        WHERE `realm` = 'AB3' AND `userId` = 3
    )

그리고 이 쿼리에 대해 다음과 같은 매우 구체적인 인덱스를 가지고 있을 수 있습니다.

index (realm, date)

시티 인덱스를 시험해 보세요.

index (realm, date, userId)  

전작보다 더 빠를지 모르겠어요.

언급URL : https://stackoverflow.com/questions/45518778/getting-a-users-position-in-a-queue-table-is-too-slow