EXPLAIN — Самая мощная команда MySQL / Habr
Самая мощная команда в MySQL – это EXPLAIN. EXPLAIN может в точности рассказать вам, что происходит, когда вы выполняете запрос. Эта информация позволит вам обнаружить медленные запросы и сократить время, затрачиваемое на обработку запроса, что впоследствии может значительно ускорить работу вашего приложения.Как использовать команду EXPLAIN
Вот очень простой пример использования этой команды:
Схема базы данных:
(таблица с пользователями users)
(таблица с адресами address)
В этом примере производится выборка данных пользователя на основе его идентификатора (userid).
Вот то, что мы имеем в результате выполнения запроса EXPLAIN:
| Переменная | Значение |
| Идентификатор (ID) таблицы в запросе. EXPLAIN создает по одной записи для каждой таблицы в запросе. | |
| Возможные значения: SIMPLE, PRIMARY, UNION, DEPENDENT UNION, SUBSELECT, и DERIVED. | |
| Имя таблицы, из которой MySQL читает данные | |
| Тип объединения, которое использует MySQL. Возможные значения: eq_ref, ref, range, index, или all. | |
| Список индексов (или NULL, если индексов нет), которые MySQL может использовать для выборки рядов в таблице. | |
| Название индекса, который использует MySQL (после проверки всех возможных индексов). | |
| Размер ключа в байтах. | |
| Колонки или значения, которые используются для сравнения с ключем. | |
| Количество рядов, которые MySQL необходимо проверить, для обработки запроса. | |
| Дополнительная информация о запросе. |
Этот пример достаточно прост. Мы производим поиск по первичному ключу (userid) и может быть только одна запись, которая подойдет нашим условиям (переменная rows равна 1).
Этот запрос более расширенный, чем первый. Он производит объединение таблиц users и address на основе userid. Поле userid – это первичный ключ таблицы users, но он не является индексом в таблице address. Результат выполнения команды EXPLAIN в этом случае будет следующий:
(таблица users)
Type: const
Possible_Keys: primary
Ref: const
(таблица address)
Type: all
Possible_Keys: (ничего)
Ref: (ничего)
Первая таблица является оптимизированной. Для выполнения запроса используется первичный ключ. Вторая таблица неоптимизирована. Значением параметра type является all, а Possible_keys пустой, что означает, что будет производиться полное сканирование таблицы. Добавление индекса к полю user второй таблицы сделает ее оптимизированной.
(таблица users)
Type: const
Possible_Keys: primary
Ref: const
(таблица address)
Type: const
Possible_Keys: primary
Ref: const
Дополнительную информацию о команде EXPLAIN вы можете найти в официальной документации MySQL: dev.mysql.com/doc/refman/5.0/en/explain.html
habr.com
Как выявить медленные SQL запросы? / Habr
Это случалось с каждым из нас при разработке веб-сайтов или приложений, использующих MySQL в качестве базы данных. Производительность внезапно сильно падала, и вы не имели понятия, почему это случилось. Этому могут быть причиной многие факторы (сильная загрузка CPU, нехватка дискового пространства, или слабая пропускная способность канала), но также это может быть и неоптимизированный запрос, выполняемый намного дольше, чем должен.Как узнать, какие из запросов выполняются дольше всего?
В MySQL есть встроенный функционал для ведения
Для включения этого функционала необходимо произвести одно из действий:
- добавить следующие строки в
/etc/my.cnf:log-slow-queries=/tmp/slow_queries.log
long_query_time=10 - вызвать
mysqldсо следующими параметрами:–log-slow-queries[=/tmp/slow_queries.log]
long_query_time — это максимальное количество секунд, которое может выполняться запрос, прежде чем он будет записан в лог медленных запросов.Другие связанные опции:
–log-slow-admin-statementsЗаписывать в лог медленные административные операторы такие, как
OPTIMIZE TABLE, ANALYZE TABLE, и ALTER TABLE.–log-queries-not-using-indexesЕсли вы используете эту опцию вместе с
–log-slow-queries, запросы, которые не используют индексы, будут записываться в лог медленных запросов.
Если ведение лога медленных запросов было успешно включено, вы увидите «ON» в столбце «Value» для строки «log_slow_queries» (как показано на рисунке выше).
Примечание: кэшированные запросы не будут записываться в лог. Также не будут записываться запросы, в которых индекс не приносит пользы из-за того, что в таблице ноль или одна запись.
Вы можете столкнуться с ситуацией, когда запрос выполняется медленно только при определенных условиях (когда вы записываете его в лог), но при ручном запуске запрос отрабатывает нормально:
- Таблица может быть заблокирована (locked), ставя, таким образом, запрос в очередь ожидания. В таком случае
lock_timeопределяет, когда таблица будет разблокирована, и как долго будет обрабатываться запрос. - Данные и индексы не были занесены в кэш памяти. Это обычно случается, когда MySQL запускается в первый раз, или когда таблицы не были оптимизированы.
- Был запущен сторонний процесс, замедляющий работу диска.
- Сервер перегружен другими запросами в это время, и не хватает ресурсов CPU для эффективной работы.
Анализ лога
В составе MySQL есть утилита mysqldumpslow — Perl-скрипт, который суммирует данные лога, и наглядно отображает, насколько часто исполняется каждый из медленных запросов.
——————————————————————————————
Справедливости ради, хочу добавить от себя пару слов.
Поскольку это все-таки перевод, а не своя статья, я старался максимально точно перевести то, что написал автор.
Но для тех, кто заинтересовался этой маленькой статьей, я хочу порекомендовать прочитать об этом функционале в официальном мануале MySQL.
dev.mysql.com/doc/refman/5.0/en/slow-query-log.html — версия на английском языке
www.mysql.ru/docs/man/Slow_query_log.html — версия на русском языке
PS: это не значит, что в статье описаны какие-то ложные действия. Просто как приятный бонус (:
habr.com
Как узнать, стоит ли оптимизировать MySQL запросы? / Habr
Мне часто задают один и тот же вопрос: как можно находить запросы, которые необходимо оптимизировать. Ведь, скажем, взглянув на отчет pt-query-digest, мы легко найдем медленные запросы или запросы, которые вызывают большую нагрузку на систему — но как мы поймем, существует или нет возможность сделать выполнение этого запроса быстрее? Полный ответ на этот вопрос определенно потребует комплексного анализа, так как существует много путей оптимизации запросов. Однако, существует одна очень полезная метрика, которую вы можете применить — соотношение между количество возвращенных запросом рядов и пройденными рядами.Допустим, у нас есть такой пример:
# Time: 120911 17:09:44 # User@Host: root[root] @ localhost [] # Thread_id: 64914 Schema: sbtest Last_errno: 0 Killed: 0 # Query_time: 9.031233 Lock_time: 0.000086 Rows_sent: 0 Rows_examined: 10000000 Rows_affected: 0 Rows_read: 0 # Bytes_sent: 213 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 # InnoDB_trx_id: 12F03 use sbtest; SET timestamp=1347397784; select * from sbtest where pad='abc';
Запрос в данном случае вернул 0 рядов (так как у нас нет совпадений), но для этого ему пришлось пройтись по 10 миллионам рядов. Какой сценарий был бы предпочтительнее? Если бы запрос прошелся по тому же числу рядов, которые в итоге он вернет. В таком случае, если я расставлю индексы в таблице, то я получу следующую запись в slow query log, куда падают все медленные запросы:
# Time: 120911 17:18:05 # User@Host: root[root] @ localhost [] # Thread_id: 65005 Schema: sbtest Last_errno: 0 Killed: 0 # Query_time: 0.000323 Lock_time: 0.000095 Rows_sent: 0 Rows_examined: 0 Rows_affected: 0 Rows_read: 0 # Bytes_sent: 213 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 # InnoDB_trx_id: 12F14 SET timestamp=1347398285; select * from sbtest where pad='abc';
Значение Rows_examined=0, совпадающее с Rows_sent означает, что запрос достаточно хорошо оптимизирован. Заметьте, если вы подумали, что в этом случае обращения к БД не происходит совсем — вы ошибаетесь. Проход по индексам выполняется, но, так как считаются только строки, которые были найдены и возвращены на верх для обработки MySQL-частью, то значение Rows_examined остается равным нулю.
Казалось бы, все очень просто, но это слишком поспешный вывод. Подобная математика сработает только с запросами без агрегирующих функций/group by, плюс только для запросов, которые проходят по ровно одной таблице. А как быть с запросами, которые затрагивают более одной таблицы?
# Time: 120911 17:25:22 # User@Host: root[root] @ localhost [] # Thread_id: 65098 Schema: sbtest Last_errno: 0 Killed: 0 # Query_time: 0.000234 Lock_time: 0.000063 Rows_sent: 1 Rows_examined: 1 Rows_affected: 0 Rows_read: 1 # Bytes_sent: 719 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 # InnoDB_trx_id: 12F1D SET timestamp=1347398722; select * from sbtest a,sbtest b where a.id=5 and b.id=a.k; mysql> explain select * from sbtest a,sbtest b where a.id=5 and b.id=a.k; +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | a | const | PRIMARY,k | PRIMARY | 4 | const | 1 | | | 1 | SIMPLE | b | const | PRIMARY | PRIMARY | 4 | const | 1 | | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ 2 rows in set (0.00 sec)
В этом случае мы на самом деле делаем объединение двух таблиц, но из-за того, что тип доступа к таблицам задан как «константа», MySQL не считает из за доступ к двум таблицам. В случае с «реальным» доступом, вывод будет таким:
# Time: 120911 17:28:12
# User@Host: root[root] @ localhost []
# Thread_id: 65099 Schema: sbtest Last_errno: 0 Killed: 0
# Query_time: 0.000273 Lock_time: 0.000052 Rows_sent: 1 Rows_examined: 2 Rows_affected: 0 Rows_read: 1
# Bytes_sent: 719 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# InnoDB_trx_id: 12F23
SET timestamp=1347398892;
select * from sbtest a,sbtest b where a.k=2 and b.id=a.id;
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+-------+
| 1 | SIMPLE | a | ref | PRIMARY,k | k | 4 | const | 1 | |
| 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | sbtest.a.id | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+-------+
2 rows in set (0.00 sec)
В этом случае мы имеем 2 проанализированные строки для каждого множества строк, чего и следовало ожидать, потому что у нас есть 2 (логических) таблицы используемых в этом запросе. Это правило также не будет работать в том случае, если у вас есть в запросе group:
# Time: 120911 17:31:48
# User@Host: root[root] @ localhost []
# Thread_id: 65144 Schema: sbtest Last_errno: 0 Killed: 0
# Query_time: 5.391612 Lock_time: 0.000121 Rows_sent: 2 Rows_examined: 10000000 Rows_affected: 0 Rows_read: 2
# Bytes_sent: 75 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# InnoDB_trx_id: 12F24
SET timestamp=1347399108;
select count(*) from sbtest group by k;
Этот запрос вернет только 2 строки, хотя пройдет по 10 миллионам, и мы не можем в действительности оптимизировать этот запрос по-простому, так как проход по всем строкам действительно необходим для группировки результатов.
В таком случае вы можете задуматься насчет удаления из запроса group by и агрегирующих функций. Тогда запрос превратится в
“select * from sbtest” , который вернет все 10 миллионов строк и, следовательно, здесь не будет простых способов оптимизации.Этот метод создан не для того, чтобы дать вам ясный ответ «да или нет», но он может порядком помочь, какой оптимизации вы можете достигнуть в итоге. Допустим, у меня есть запрос, который использует индекс по 1000 строк и возвращает 10… У меня все еще может быть возможность уменьшить число строк, по которым он проходит, в 100 раз, — при помощи, например, добавления комбинированных индексов.
Итак, кратко — как можно быстро узнать, стоит ли оптимизировать запрос или нет?
— посмотрите, сколько строк возвращает запрос после удаления group by, distinct и агрегирующих функций (A)
— возьмите число пройденных строк, деленное на число таблиц в объединении (B)
— если B меньше или равно A, ваш запрос «идеален»
— если B/A равен 10 или больше. то этот запрос один из самых достойных кандидатов на оптимизацию.
Это простой метод и его можно смело использовать вместе с pt-query-digest, так так последний сообщает не только средние значения, но и краевые.
Оригинал статьи: здесь.
habr.com
MySQL On air. Мониторим SQL запросы / Habr
Разбираясь как работает та или иная CMS приходится использовать различные инструменты, облегчающие работу.
Наиболее интересная тема — это работа с баз(ой|ами) данных. Естественно для изучения запросов и результатов запросов нужно использовать что-то универсальное. Что-то, что будет работать стабильно как с известным движком, так и с самописной системой.
Предположим у вас оказалась система управления контентом и вам необходимо посмотреть как реализовано добавление новых пользователей или смена паролей.
Большинство инструментов позволяющих мониторить работу с БД являются платными [раз, два]. Я хотел что-то более легкое и удобное, поэтому выбрал mysql-proxy. Хотя возможности утилиты гораздо шире чем мне требуется, я опишу лишь основное. Работает как под Windows, так и под Unix системами.
Скачать можно тут: dev.mysql.com/downloads/mysql-proxy
Первым делом нужно определиться с выбором дистрибутива. На данный момент новейшей версией является «MySQL Proxy 0.8.3 alpha», в качестве ОС у меня стоит Windows 7, поэтому все тесты будут на ней.
После нажатия на кнопку «Download» вас попросят авторизироваться либо зарегистрировать, но снизу есть ссылка для скачивания без лишних действий. (В репозиториях Ubuntu и Debian есть готовые пакеты, так что: sudo apt-get install mysql-proxy)
Хочу заметить, что при размере в 7.9Мб в дистрибутив входит Lua с поддержкой основых модулей.
После скачивания архива его нужно распаковать в удобную для вас директорию, возьмем для примера C:\mysql-proxy
Для запуска приложения нужно определиться с параметрами.
В данном контексте многое зависит от того, что у вас установлено. В качестве LAMP я использую Winginx, прочитать об этой связке можно тут: winginx.ru
По умолчанию MySQL работает на 3306 порту, его мы трогать не будем.
Нам нужно изменить порт для подключения php к базе. Для этого необходимо поправить php.ini
Найдем строку «mysql.default_port» и установим порт (по умолчанию 4040). Для более универсальной работы измените и «mysqli.default_port» на 4040
Подготовительная часть пройдена, перейдем к делу.
Вариант 1. Мониторинг запросов.
Для простого мониторинга необходимо использовать Lua скрипт. Как я уже говорил Lua идет в комплекте, так что ничего нового устанавливать не надо.
Создадим простой скрипт view.lua в директории C:\mysql-proxy\ с содержимым:
function read_query(packet)
if string.byte(packet) == proxy.COM_QUERY then
print(string.sub(packet, 2))
end
end
Теперь можно проверить результат.
Для удобства создадим в директории C:\mysql-proxy файл view.bat c содержимым:
C:\mysql-proxy\bin\mysql-proxy.exe --proxy-lua-script=C:\mysql-proxy\view.lua --log-file="C:\mysql-proxy\mysql-proxy-log.txt" --proxy-backend-addresses=localhost:3306
—proxy-backend-addresses — адрес MySQL сервера на который будем проксировать запрос.
Запустив вэб-сервер и выполнив какие либо запросы к базе можете увидеть такое:
Запросы отображаются, хорошо.
Вариант 2. Мониторинг запросов и запись в файл.
Для записи запросов в файл будем использовать штатные возможности Lua.
Создадим файл view-write.lua в директории C:\mysql-proxy\ с содержимым:
function read_query(packet)
if string.byte(packet) == proxy.COM_QUERY then
local file = io.open("C:\\mysql-proxy\\sql-log.txt", "a")
file:write(string.sub(packet, 2) .. "\n")
file:close()
print(string.sub(packet, 2))
end
end
и bat файл — «view-write.bat»
C:\mysql-proxy\bin\mysql-proxy.exe --proxy-lua-script="C:\mysql-proxy\view-write.lua" --log-file="C:\mysql-proxy\mysql-proxy-log.txt" --proxy-backend-addresses=localhost:3306
Результат после выполнения запросов (по адресу «C:\mysql-proxy\sql-log.txt»)
Помимо отображения самих запросов, нам может понадобиться вывод результатов этих запросов.
Вариант 3. Запросы и результат
По той-же схеме создаём скрипт «view-result.lua»:
function read_query( packet )
if packet:byte() == proxy.COM_QUERY then
print("Query: " .. string.sub(packet, 2))
local file = io.open("C:\\mysql-proxy\\sql-log.txt", "a")
file:write("Query: " .. string.sub(packet, 2) .. "\n")
file:close()
proxy.queries:append(2, string.char(proxy.COM_QUERY) .. string.sub(packet, 2), {resultset_is_needed = true} )
proxy.queries:append(1, packet, {resultset_is_needed = true})
return proxy.PROXY_SEND_QUERY
end
end
function read_query_result(inj)
if inj.id == 1 then
for row in inj.resultset.rows do
local i = 1
local fields = {}
while row[i] do
if row[i] == row then break end
local file = io.open("C:\\mysql-proxy\\sql-log.txt", "a")
file:write("Response field: " .. inj.resultset.fields[i].name .. " => " .. row[i] .. "\n")
file:close()
print("Response field: " .. inj.resultset.fields[i].name .. " => " .. row[i])
i = i + 1
end
end
return proxy.PROXY_IGNORE_RESULT
end
end
И view-result.bat
C:\mysql-proxy\bin\mysql-proxy.exe --proxy-lua-script="C:\mysql-proxy\view-result.lua" --log-file="C:\mysql-proxy\mysql-proxy-log.txt" --proxy-backend-addresses=localhost:3306
В результате получаем полное логирование запросов и ответов в читаемом виде
habr.com
Как убить проблемный MySQL запрос
Довольно много интернет проектов в качестве хранения базы данных используют СУБД MySQL. При всём при этом, существуют и другие варианты выбора, такие как: MS SQL, mSQL, PostrgreSQL, Oracle, и т.д., но практический каждый хостер предоставляет услуги, в которые с большой вероятностью включена возможность использования баз данных MySQL. Такой популярности могло послужить множество весомых причин, одной из которых является то, что продукт имеет открытый исходный код, иными словами Open Source, который может получить каждый желающий (для версий под Windows существуют некоторые оговорки). Так же бытует мнение, что связка PHP/MySQL, или Perl/MySQL может дать большой показатель быстродействия, которое в иных случаях достигается гораздо сложнее. К тому же, PHP в стандартной сборке имеет встроенную поддержку MySQL.
Для создания запросов на сервер MySQL, так же как и в большинстве других СУБД, используется язык SQL. SQL язык является доминирующим языком для работы с базами данных, однако каждая база данных так же может иметь свои «диалекты» языка SQL, которые присущи конкретной разновидности СУБД.
Так же как и в любой базе данных существуют некоторые нормы, при которых сервер базы данных функционирует должным образом. Стать одной из причин торможения системы может сложный SQL запрос, который занимает длительное время на выполнение. Тяжелые SQL запросы создают большую нагрузку на MySQL сервер, что в целом сказывается на производительности системы в целом. Парой это бывает крайне критично на «боевом» сервере, когда торможение системы может подарить кучу дополнительных проблем.
Такие запросы, как правило, следует оптимизировать, но что делать, если нужно убрать запрос из очереди, который в силу своей замудрённости завис.
Есть способ, именуемый в народе «топорным» решением проблемы, это перезапуск MySQL сервера.
Есть и другой способ, который зачастую является более рациональным, это “убить” проблемный MySQL запрос. Сделать это можно посредством SSH, либо какой либо утилитой, к примеру, phpMyAdmin.
Убираем тяжелый MySQL запрос по SSH:
1. Заходим на сервер по SSH
2. Выполняем команду:
$ mysql –u USER –p
Вместо USER указываем пользователя MySQL
3. Вводим пароль от учетной записи USER
4. Выполняем команду
show processlist;
(не забываем указать точку с запятой на конце)
5. Узнаем ID проблемного запроса
6. Выполняем команду
Kill query 11223
Вместо 11223 указываем ID запроса, который нужно убрать.
В phpMyAdmin это делается ещё проще.
Убираем тяжелый MySQL запрос в phpMyAdmin:
1. Авторизуемся, выбираем базу данных
2. Открываем поле ввода команд (вкладка «SQL»)
3. Вводим команду
show processlist
4. Появляется список запросов, на нужном запросе жмем по ссылке «завершить»
yournet.kz