Версия MySQL у меня 8. Не могу сказать, что я опытный пользователь, но у меня возникают определенные трудности. Вот что я делаю: работаю с таблицей, содержащей около 10 миллионов строк. У меня есть следующий запрос: <pre><code class="sql">SELECT id, time, ROW_NUMBER() OVER (PARTITION BY phone ORDER BY time DESC) AS n FROM sms WHERE time > NOW() - INTERVAL 6 MONTH AND time < NOW() - INTERVAL 6 HOUR;</code></pre> Этот запрос выполняется нормально, в пределах 5-10 секунд. Далее я добавил к запросу следующее: <pre><code class="sql">SELECT * FROM (SELECT id, time, ROW_NUMBER() OVER (PARTITION BY phone ORDER BY time DESC) AS n FROM sms WHERE time > NOW() - INTERVAL 6 MONTH AND time < NOW() - INTERVAL 6 HOUR) t WHERE n <=4;</code></pre> Он также работает без проблем. Однако, когда я пишу следующий запрос, MySQL зависает, и меня не спасает перезапуск через systemctl или физический перезагруз сервер. Это происходит даже на двух разных серверах: <pre><code class="sql">SELECT * FROM (SELECT id, time, ROW_NUMBER() OVER (PARTITION BY phone ORDER BY time DESC) AS n FROM sms WHERE time > NOW() - INTERVAL 6 MONTH AND time < NOW() - INTERVAL 6 HOUR) t WHERE n=4;</code></pre> Кажется, что я что-то делаю неправильно, но логически мне не кажется, что запросы отличаются чем-то особенным.
Ваше описание проблемы действительно указывает на то, что запрос, который вы привели в последнем примере, может иметь некоторые проблемы, которые вызывают зависание MySQL. Вот несколько возможных причин: 1. **Отсутствие индексов**: Запрос, в котором используется `PARTITION BY` и `ORDER BY`, может быть очень затратным по ресурсам, если соответствующие поля не индексированы. Убедитесь, что у вас есть индекс на столбце `phone` и возможно на столбце `time`. Это может значительно ускорить выполнение запроса. 2. **Большие данные после фильтрации**: При использовании `ROW_NUMBER()` MySQL должен сначала обработать все строки, удовлетворяющие критериям, а затем применять `WHERE n=4`. Если есть много строк, которые соответствуют условиям выборки (в данном случае за последние 6 месяцев), вы можете столкнуться с проблемами производительности. 3. **Прерывание и блокировки**: Если предыдущий запрос забирает много ресурсов (например, из-за больших объемов данных), это может заблокировать или замедлить выполнение последующего запроса, даже если их логика выглядит похожей. 4. **План выполнения запроса**: В некоторых случаях MySQL может неправильно оптимизировать определенные запросы. Используйте команду `EXPLAIN` перед вашим запросом, чтобы посмотреть, как MySQL планирует его выполнить, и выяснить, где могут возникнуть узкие места. 5. **Неправильная обработка NULL**: Если в ваших данных есть значения NULL в столбце `phone`, это может привести к проблемам в узких местах при обработке. Убедитесь, что все значения корректны. Для устранения проблемы вы можете попробовать следующее: - **Индексы**: Убедитесь, что на поля, которые вы используете в условиях `WHERE`, установлены индексы. - **Улучшите запрос**: Рассмотрите возможность выполнить запрос в несколько этапов, чтобы избежать чрезмерной нагрузки на сервер в одном запросе. Например, сначала получите все строки с `n <= 4`, а затем отфильтруйте только те, которые вам нужны. - **Серверные ресурсы**: Убедитесь, что сервер имеет достаточное количество ресурсов (CPU, памяти и дискового пространства) для работы с этими запросами. Проверьте настройки MySQL, такие как размер буфера. Попробуйте использовать `EXPLAIN` для диагностики и оптимизации вашего запроса. Это даст более полное представление о том, как MySQL обрабатывает ваш запрос и где могут возникнуть проблемы.
Первый уровень оптимизации запросов - это, разумеется, индексы.
Но иногда даже если все нужные индексы есть, база тупит неимоверно. В этом случае надо сразу смотреть значение innodb_buffer_pool_size. По умолчанию значение этого параметра всего 128 мегабайт. А это, грубо говоря, максимальное значение памяти, доступное MySQL для работы.
Собственно, суть работы индекса в том, что это упорядоченные данные, лежащие в оперативной памяти , за счёт чего процессор находит нужные данные очень быстро. Но вот если памяти тупо не хватает чтобы индекс в ней поместился, то его эффективность тут же превращается в тыкву.
При этом Mysql дисциплинированно не забирает всю доступную на компьютере память, а берет столько, сколько ей скажут. И если это значение не увеличить, то на большой базе индексы не поместятся в память и всё встанет колом, поскольку индекс будет читаться частями с диска, потом еще надо будет выбранные результаты свопить на диск, потом ещё сортировать полученный результат опять же на диске...
Разумеется, у mysql есть просто миллион разных настроек, которые тоже влияют на производительность. У Перконы, кажется, даже есть калькулятор, в который ты заливаешь информацию о своей базе, а он тебе выдаёт значения параметров конфигурации. Но это уже такая тонкая настройка, третий уровень. Ничего из этого не будет работать, если innodb_buffer_pool_size недостаточного размера. И его размер рекомендуется выставлять на максимально допустимое значение. Если это выделенный mysql сервер, то 80-90% от физической памяти. Если не выделенный - то столько, сколько не жалко, чтобы не мешать остальным приложениям.