ИТ Блог. Администрирование серверов на основе Linux (Ubuntu, Debian, CentOS, openSUSE)
Итак, давайте углубимся в то, как мы использовали некоторые «плохие методы» для преодоления потенциальной ошибки в оптимизаторе 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.
Добавьте еще одно условие, которое использует столбец 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 ). Обратите внимание, что если количество пользователей было значительно больше, это может быть не результат.
Создайте новую таблицу дат и заполните ее всеми действительными и релевантными датами, начиная с минимальной даты, которая подходит для вашего приложения, вплоть до максимально возможной даты.
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 ), но, насколько мы знаем, исправление не было запланировано/выпущено.
Помните, что мы не рекомендуем подходы выше для любого запроса. Используйте их с умом и только для конкретного случая использования, который мы описали выше: при объединении нескольких таблиц и использовании сканирования диапазона. И снова, тестируйте, тестируйте и тестируйте снова, прежде чем решить, какой подход выбрать.
Вы столкнулись с подобной проблемой и не можете обойти ее? Расскажите нам о вашем конкретном случае использования в комментариях.
Нашли ли вы другие допустимые обходные пути? Мы будем более чем рады услышать о них!
Тэги: , MySQL, база данных, оптимизация БД Категории: MySQL
