MySQL. 8.0.19. Лучший индекс для соответствия заданному запросу

ProgramBox

MySQL. 8.0.19. Лучший индекс для соответствия заданному запросу

Post author:admin
Запись опубликована:24 февраля, 2023
Post category:Вопросы по программированию

#mysql

Вопрос:

заранее благодарю.

Я пытаюсь улучшить производительность моего запроса, но застрял в такой ситуации:

Дана таблица:

 CREATE TABLE `user_purchases`
(
    `id`        BIGINT      NOT NULL AUTO_INCREMENT,
    `user_id`   INT         NOT NULL,
    `date`      DATETIME    NOT NULL,
    `status`    VARCHAR(64) NOT NULL,
    `purchase`  BIGINT      NOT NULL,
    `cashback`  BIGINT      NULL DEFAULT -1,
    `coupon_id` INT         NULL,
    PRIMARY KEY (`id`),
    KEY `user_status_key` (`user_id`, `status`),
    KEY `user_key` (`user_id`),
    KEY `status_key` (`status`)
);

Таблица содержит много исторических данных о покупках пользователей

Чтобы получить все, что мне нужно, я выполняю такой запрос:

 SELECT id FROM user_purchases p
WHERE p.user_id = 1
  AND p.status IN ('PROCESSED', 'FAILED', 'CANCELLED')
  AND p.id < 400
  AND p.coupon_id IS NULL
ORDER BY p.id DESC
LIMIT 10;

Но в ситуациях, когда у пользователя есть тысячи покупок в этом статусе, это занимает слишком много времени.

Выбираю ли я лучшие индексы или я могу изменить их на другие, чтобы ускорить выполнение этого запроса?

Результат для объяснения запроса:

 select_type - SIMPLE
type - range
select_type - simple
POSSIBLE_KEYS - PRIMARY,user_status_key,status_key,user_key
key - PRIMARY
key_len - 8
ref - null
rows - 8347239
filtered - 0.01
Extra - Using where; Backward index scan

Если я использую принудительный индекс (user_status_key), результат будет лучше

 select_type - SIMPLE
type - range
select_type - simple
POSSIBLE_KEYS - user_status_key
key - user_status_key
key_len - 270
ref - null
rows - 55324
filtered - 10
Extra - Using index condition; Using where; Using filesort

Смогу ли я ускорить запрос больше, используя другие индексы?

1. p.id < 400 условие кажется странным; можете ли вы объяснить, для чего оно? кроме того, key_len - 270 это кажется невозможным; пожалуйста, покажите вашу реальную таблицу (в идеале полный вывод show create table user_purchases ) или ваше реальное объяснение

2. помогает ли user_key вместо этого принудительное индексирование?

3. вывод из show create (2 части для соответствия ограничению символов комментариев)

4. СОЗДАТЬ ТАБЛИЦУ user_purchases ( id АВТО_ИНКРЕМЕНТ bigint NOT NULL, user_id int NOT NULL, date datetime NOT NULL, status НАБОР СИМВОЛОВ varchar(64) utf8mb4 СОПОСТАВЛЯЕТ utf8mb4_unicode_ci НЕ NULL, purchase bigint NOT NULL, cashback bigint ПО УМОЛЧАНИЮ ‘-1’, coupon_id int ПО УМОЛЧАНИЮ NULL,

5. ПЕРВИЧНЫЙ КЛЮЧ ( id ), КЛЮЧ user_key ( user_id ), КЛЮЧ status_key ( status ), КЛЮЧ user_status_key ( user_id , status ) ) ENGINE = InnoDB AUTO_INCREMENT = 17203377 КОДИРОВКА ПО УМОЛЧАНИЮ = utf8mb4 СОПОСТАВЛЕНИЕ = utf8mb4_unicode_ci;

Ответ №1:

Хотя KEY на самом деле создается уникальный индекс, проблема с вашим дизайном заключается в том, что у вас есть отдельные индексы. Два индекса для A и B не совпадают с одним индексом для (A, B) . Как следствие, база данных по-прежнему должна выполнять отдельные запросы / проверки.

Чтобы ускорить ваш запрос, вы хотите иметь индекс, содержащий все ваши WHERE условия, по крайней мере, до того момента, когда остальное будет содержать всего несколько записей. Кроме того, вы можете захотеть воспользоваться тем, что индекс упорядочен так же, как и ваш ORDER BY . Так что это было бы что-то вроде этого:

 CREATE INDEX my_index ON user_purchases (
    user_id, id DESC, status, coupon_id
)

Обратите внимание, что индексы являются левоассоциативными, поэтому, если столбцы индекса A начинаются со всех столбцов индекса B, то B является избыточным. Но чтобы оптимизировать это, вам нужно будет объяснить причины, по которым вы разработали свою схему таким образом.

Ответ №2:

Индекс user_status_key содержит user_id, status и id; вы выбираете один user_id, три разных статуса и диапазон идентификаторов. Иногда, когда у вас есть несколько разных значений для поиска в средней части индекса, помогает указать их с помощью соединения, а не in условия. Попробуйте:

 SELECT straight_join id
FROM (SELECT 'PROCESSED' status UNION ALL SELECT 'FAILED' UNION ALL SELECT 'CANCELLED') statuses
JOIN user_purchases p USE INDEX (user_status_key) ON p.user_id = 1 AND p.status = statuses.status AND p.id < 400
WHERE p.coupon_id IS NULL
ORDER BY p.id DESC
LIMIT 10;