Статистика

Участников проекта 105
Опубликовано статей 78
Отчет по карме. Топ 20

Новости блога

1 29.11.2013  Сегодня самым активным участникам newblog'а был выплачен доход с sape.
7 02.11.2012  Ура! Свешилось, нашему сайту дали тИЦ 10. Спасибо всем кто принимает участие в развитии нашего блога.
8 21.08.2012  Интеграция с sape.ru. Теперь каждый автор статей на newblog автоматически зарабатывает на рекламе.
Все новости

Топ 5 категорий

Программирование 46
Операционные системы 9
Базы данных 4
Туризм 2
Заметки 2

Последние 5 заметок (90)

gullyar - Закладки gullyar
gullyar - Ваша первая закладка
osadchaya - Закладки osadchaya
Ira0231188 - Закладки Ira0231188
Ira0231188 - Закладки Ira0231188

Ссылки

www.freedev.asia

Методы оптимизации запросов в MySQL

23.05.2012 21:18 | Просмотров: 1718 | Доход: 32.76 руб. | Комментариев: 2
[Базы данных] 
Рейтинг: 5/1

В крупных проекта при увеличении числа пользователей даже самый незначительный sql запрос может повлиять на производительность системы в целом. Для таких целей у  MySQL есть штатные средства выявления и оптимизации таких запросов. Причем данные средства позволяют проводить такие опыты прямо на "боевом" сервере. Это позволяет оценить реальную причину нагрузки. Первое на что нужно обратить внимание это на команду show processlist. Полезность данной команды сложно переоценить. Данная команда показывает все текущие sql запросы. А именно какой пользователь совершил запрос, host с которого произошло подключение, базу данных к которой обратились. Время выполнения запроса, статус и собственно сам sql запрос.  "n rows" - показывает количество активных подключений в текущий момент. Если он будет превышать параметр max_connections, то MySQL наченет откидывать запросы, даже если есть свободные ресурсы сервера.

mysql> show processlist;
+-----+------+----------+---+-------------+------+----------------------------+------------------+
| Id  | User | Host     | db| Command     | Time | State                      | Info             |
+-----+-----------------+-----------------+------+----------------------------+------------------+
| n | user1  | localhost| db| Query       |  10  | Copying to tmp table       |    sql запрос    |
...
+-----+------+----------+---+-------------+------+----------------------------+------------------+
n rows in set (0.00 sec)

Поэтому полезно следить за параметром max_connections:

show variables like "max_connections";

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 200   |
+-----------------+-------+

 

Увеличить это значение можно командой set global max_connections = n;

Вернемся к show processlist. Саммый ценный параметр для оптимизации это Time - время выполнения sql запроса. Определяем запрос, который висит дольше всего. Чтобы посмотреть этот запрос полностью, выполняем show full processlist; Теперь мы знаем какой именно запрос тормозит систему. Но если одновременных запросов очень много либо используются blob поля, то таким способом может быть проблематично отловить запрос на сервере. Есть еще один способ show profiles:

set profiling = 1;

 

Через какое-то время вводим:

set profiling = 0;

mysql> show profiles;
+----------+------------+--------------------------------+
| Query_ID | Duration   | Query                          |
+----------+------------+--------------------------------+
|        1 | 0.00009500 | запрос 1|
|        2 | 0.00014200 | запрос 2|

...
+----------+------------+--------------------------------+
n rows in set (0.00 sec)

 

Итак тяжелый запрос отловили. В MySQL есть еще одна очень полезная команда, которая называется explain.

mysql> explain select count(*) from table1; 
;(вместо этого запроса указываем длинный и страшный запрос из show processlist)
+----+-------------+-------+-------+---------------+--------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key    | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+--------+---------+------+------+-------------+
|  1 | SIMPLE      | table1  | index | NULL          | actual | 1       | NULL | 9323905 | Using index |
+----+-------------+-------+-------+---------------+--------+---------+------+------+-------------+
1 row in set (0.10 sec)

 

Здесь rows - показывает сколько строк пришлось просмотреть MySQL серверу чтобы определить результат запроса. И соответственно чем меньше rows тем быстрее отработает запрос. Как правило это выявляет все проблемные запросы с join и т.д. Оптимизируем и проверяем количество задетых строк пока не получим приемлемый результат. Допустим мы упростили запрос. Как правило разработчик точно знает где этот запрос, но если приклад разрабатывался до него, то это можно определить простым поиском в linux: grep -rl "часть sql запроса" [директория приклада]. Меняем запрос. Вот собственно и все.


© GM
| Комментировать статью |
  • saratan +16 (24.05.2012 21:02)
    ы! палишся... explain select count(*) from table1; -> 1 | SIMPLE      | [вырезано администратором]. А что оптимизировал, [вырезано администратором]?
    | Ответить |
    • GM +2587 (24.05.2012 23:15)
      упсс.. )))) не заметил :)
      | Ответить |