Верные слова не изящны, изящные слова не верны (Лао-Цзы).

MySQL не использует мой индекс – объединение с условием диапазона

5 мин для чтения
FavoriteLoadingДобавить в избранное
1 Звезда2 Звезды3 Звезды4 Звезды5 Звезд (1 оценок, среднее: 5,00 из 5)
Загрузка...
10 апреля 2019
Помощь и синтаксис sql joins
Итак, давайте углубимся в то, как мы использовали некоторые «плохие методы» для преодоления потенциальной ошибки в оптимизаторе MySQL.
В этом тематическом исследовании будет описан сценарий, в котором оптимизатор MySQL не решит использовать существующий и предположительно хороший индекс для оптимизации поискового запроса, что в итоге привело к 80-секундному отклику простой страницы в веб-приложении.

 

Суть проблемы

Сценарий с точки зрения бизнеса. Команда разработчиков пыталась реализовать веб-страницу, на которой представлены данные о действиях пользователей за последний год.
Сценарий с точки зрения технической базы данных. Команда пыталась объединить две (одна и та же проблема возникнет с более чем двумя) таблицами и использовать условие диапазона, которое не применяется к первой таблице (в плане выполнения оптимизатора). Если это не очень понятно, изучите запрос и план объяснения ниже и вернитесь к последнему предложению снова.

Таблицы, включенные в сценарий, – это пользователи и user_actions , которые говорят сами за себя по своему имени и структуре.
Таблица users содержит 209 пользователей, а таблица user_actions содержит 2 200 000 000 (да, 2,2 миллиарда) записей.

CREATE TABLE users (
id INT(10) NOT NULL,
user_name VARCHAR(40) NOT NULL,
status VARCHAR(10) NOT NULL,
PRIMARY KEY(id),
KEY status (status)
)
CREATE TABLE user_actions(
    Id INT(10) NOT NULL AUTO_INCREMENT,
    user_id INT(10) NOT NULL,
    date DATE NOT NULL,
    PRIMARY KEY (id),
    KEY user_date (user_id, date)
)

 

Используя этот упрощенный SQL-запрос, мы попытались получить с 2017 года данные об активных пользователях и их действиях.

SELECT *
FROM users u
JOIN user_actions ua ON u.id=ua.user_id
WHERE ua.date>'2019-04-10' AND  u.status='Active';

 

Продолжительность выполнения этого SQL-запроса составляла 80 секунд, что, разумеется, в наши дни неприемлемо для любого приложения для любого действия, инициируемого пользователем (возможно, кроме генерации отчета, что здесь не так).

Поэтому мы обратились к плану EXPLAIN, чтобы найти там спасение:

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: u
type: ref
possible_keys: PRIMARY,status
key: status
key_len: 32
ref: const
rows: 203
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: ua
type: ref
possible_keys: user_date
key: user_date
key_len: 4
ref: u.id
rows: 3837000
Extra: Using where; Using index

 

Как видите, MySQL решил использовать индекс user_date , но key_len составляет всего 4 байта! Полная длина ключа должна составлять 7 байтов (4 байта для user_id и 3 байта для столбца даты).

Как упоминалось выше, мы наблюдали такое поведение в случаях, когда вы объединяете две или более таблиц и используете условие диапазона, которое не применяется к первой таблице (первая таблица, как в EXPLAIN). В нашем примере – мы присоединились к `users` и ` user_actions`, а используя условие диапазона в колонке `date` из ` user_actions` (вторая таблица в плане выполнения).

Как вы можете себе представить, такое поведение может сильно повлиять на производительность.

 

Возможные обходные пути

Обратите внимание, что некоторые из этих обходных путей не идеальны с точки зрения дизайна и «удивительности». Некоторые даже считают их «плохой практикой», когда используют без уважительной причины.

Единственная причина, по которой мы предлагаем использовать их в этом случае, – обойти потенциальную ошибку в оптимизаторе MySQL.

 

Решение проблемы с использованием плохой практики # 1 (Выполнить запрос в цикле)

Добавьте еще одно условие, которое использует столбец user_id (который мы использовали в предложении ON) к запросу:

SELECT *
FROM users u
JOIN user_actions ua ON u.id=ua.user_id
WHERE ua.date>'2019-04-10' AND  u.status='Active' and u.id=10;

 

Теперь давайте снова посмотрим на план объяснения:

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: u
type: const
possible_keys: PRIMARY,status
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: ua
type: ref
possible_keys: user_date
key: user_date
key_len: 7
ref: const
rows: 404090
Extra: Using where; Using index

 

В нашем случае этого решения было недостаточно, поскольку нам нужны были данные всех user_id, а не только одного из них. Поэтому, опираясь на этот обходной путь, мы выполнили запрос для каждого user_id в цикле из кода приложения. Это может показаться дорогим и плохой практикой, но на самом деле, в нашем конкретном случае, общая производительность была значительно выше, чем при использовании исходного запроса (который неправильно использует индекс user_date ). Обратите внимание, что если количество пользователей было значительно больше, это может быть не результат.

 

Решение проблемы с использованием плохой практики № 2 (декартово произведение)

Создайте новую таблицу дат и заполните ее всеми действительными и релевантными датами, начиная с минимальной даты, которая подходит для вашего приложения, вплоть до максимально возможной даты.

CREATE TABLE `dates` (
  `date` date NOT NULL,
  PRIMARY KEY (`date`)
)

 

Затем присоединитесь к нему в запросе:

SELECT STRAIGHT_JOIN *
FROM users u
JOIN dates d
JOIN user_actions ua ON u.id=ua.user_id AND d.date=ua.date
WHERE d.date>'2019-04-10' AND  u.status='Active';

 

Давайте посмотрим на план объяснения обновленного запроса:

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: u
type: ref
possible_keys: PRIMARY,status
key: status
key_len: 32
ref: const
rows: 203
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: d
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 3
ref: NULL
rows: 12305
Extra: Using where; Using index; Using join buffer
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: ua
type: ref
possible_keys: user_date
key: user_date
key_len: 7
ref:  u.id,d.date
rows: 30420
Extra: Using index

 

Вы, наверное, заметили, что этот запрос будет использовать декартово произведение `users` и ` dates`. Мы знаем. Это не выглядит хорошо на всех. НО, в нашем случае (и других подобных мы видели), этот обходной путь значительно увеличил время выполнения. В этом конкретном примере время отклика запроса значительно улучшилось, снизившись с 80 до 0,04 секунды!

* Straight_join используется, чтобы помочь MySQL использовать правильный план выполнения. В конце концов, использование декартового произведения обычно не является первым выбором оптимизатора.

Обратите внимание, что по этому поводу уже есть несколько сообщений об ошибках (например, ошибка 8569 , ошибка 19548 ), но, насколько мы знаем, исправление не было запланировано/выпущено.

 

Заключение

Помните, что мы не рекомендуем подходы выше для любого запроса. Используйте их с умом и только для конкретного случая использования, который мы описали выше: при объединении нескольких таблиц и использовании сканирования диапазона. И снова, тестируйте, тестируйте и тестируйте снова, прежде чем решить, какой подход выбрать.

Вы столкнулись с подобной проблемой и не можете обойти ее? Расскажите нам о вашем конкретном случае использования в комментариях.

Нашли ли вы другие допустимые обходные пути? Мы будем более чем рады услышать о них!

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

Просмотров: 19

Если статья понравилась, то поделитесь ей в социальных сетях:

Добавить комментарий

Войти с помощью: 

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Сообщить об опечатке

Текст, который будет отправлен нашим редакторам:

Заполните форму и наш менеджер перезвонит Вам в самое ближайшее время!

badge
Обратный звонок 1
Отправить
galka

Спасибо! Ваша заявка принята

close
galka

Спасибо! Ваша заявка принята

close