MySQL / Оптимизация доступа к базе данных в веб-приложениях
В продолжение дискуссии о преимуществах той или иной базы данных - перевод статьи "Оптимизация доступа к базе данных" (eng) с сайта http://www.mysqlperformanceblog.com
Оптимизация доступа к базе данных в веб-приложениях
Здесь рассмотрен достаточно простой подход, который я часто использую для оптимизации работы веб-приложений, если проблемой охвачено несколько страниц. Если проблема описывается такими словами, как "все тормозит", то, возможно лучшим началом ее решения будет просмотр журнала медленных запросов.
Итак, что можно было бы сделать...
Изучите страницу, которая приходит из базы данных.
Тем или иным способом вы должны будете получить информацию из базы данных, и, возможно, сохранить внешний вид страницы такой, какой она была до вашего вмешательства. Ваша задача - найти наиболее эффективный способ получения информации из базы данных.
Исследуйте степень динамичности информации
Если информация статическая или редко изменяется, то лучшей техникой может стать пред-создание или кэширование. Существует множество техник для кэширования и пред-создания, которые вы можете использовать. Главное, помните - лучшим способом оптимизации доступа к базе является тот, который позволяет избегать доступа к базе данных. Это может относиться к чему угодно - если вы можете избежать динамического формирования страницы и иметь для ее обслуживания кэш на сервера, то следует использовать эту возможность.
Проверьте, соответствует ли информация, полученная из базы данных тому, что вы видите на экране
Часто мы получаем из базы данных гораздо больше информации, чем необходимо для формирования страницы. В лучшем случае, используется SELECT * FROM tbl вместо перечисления списка столбцов, которые нам действительно необходимы, а в худшем, это использование SELECT * FROM tbl для подсчета строк в таблице (без шуток). Иногда можно видеть случаи фильтрации на прикладном уровне, когда, например, из 100 выбранных записей случайным образом выбирается только одна. Некоторые вещи, действительно, выполняются на прикладном уровне более эффективно, но в общем случае, следует так составлять свои запросы, чтобы они возращали только необходимую информацию.
Проанализируйте количество строк, которые используются для формирования результирующего набора данных
Это очень важный и часто забываемый шаг. Некоторые считают, что запрос прост, если он возвращает небольшое количество строк, в то время как действительное значение имеет количество строк, которое было проанализировано в связи с запросом. Например, запрос SELECT COUNT(*) FROM links WHERE domain = ‘mysql.com’; возвратит только один ряд, в то время как может просмотреть сотни тысяч строк (или элементов индекса) для получения результата. Другие наиболее общие "убийственные" запросы, это запросы GROUP BY и запросы с сортировкой - SELECT name,descr FROM titles ORDER BY rank DESC LIMIT 10 - если не определено индекса, соответствующего требованию сортировки, то это может стать частью проблемы. Также следует упомянуть использование объединений JOIN - объединения дорого обходятся с точки зрения производительности (относительно конечно) и наверняка увеличивают количество строк, которые используются для формирования результирующего набора - если вы должны объединить 10 таблиц, для составления объекта, то это будет намного медленнее, чем получение тех же самых данных чтением одной строки.
Проанализируйте количество строк, которые в действительности необходимы для формирования результирующего набора
Простой случай - запросу требуется анализировать большее количество строк для формирования результата, чем нужно только из-за не оптимальной индексации схемы. Типичный пример - запрос ORDER BY rank - добавление индекса на столбец rank упрощает работу запроса и для возврата 10 строк, он обрабатывает только 10 строк - ровно столько, сколько нам нужно. Другое дело - запрос COUNT(*) - даже если вы задали индекс по домену, может потребоваться просмотреть большое количество строк для получения результирующего набора. Такие запросы предпочтительнее спроектировать как-то иначе, т.е. найти другой способ получения результата - например, в этом случае, имела бы смысл итоговая таблица, которая содежала бы количество связей на домен.
Проанализируйте количество запросов
Если вы можете получить такое же количество данных одним запросом, то это предпочтительнее, чем выполнение нескольких запросов, в предположении, что этому запросу не требуется анализировать намного больше строк вследствии его иной оптимизации. Одним из типичных примеров здесь был бы SELECT * FROM tbl WHERE id=5 выполняемый много раз с различными константами - имеет смысл заменить такие запросы, запросом, с использованием IN(5,7,4,56). Однако не слишком этим увлекайтесь. Я был свидетелем того, как люди пытались объединить все запросы в один UNION (с дополнением, чтобы соотнести различные типы и количество столбцов) - это не лучшая практика. Однако, если Вы можете уменьшить количество запросов без серьезных осложнений прикладной архитектуры, то имеет смысл это сделать.
Оптимизация доступа к базе данных в веб-приложениях
Здесь рассмотрен достаточно простой подход, который я часто использую для оптимизации работы веб-приложений, если проблемой охвачено несколько страниц. Если проблема описывается такими словами, как "все тормозит", то, возможно лучшим началом ее решения будет просмотр журнала медленных запросов.
Итак, что можно было бы сделать...
Изучите страницу, которая приходит из базы данных.
Тем или иным способом вы должны будете получить информацию из базы данных, и, возможно, сохранить внешний вид страницы такой, какой она была до вашего вмешательства. Ваша задача - найти наиболее эффективный способ получения информации из базы данных.
Исследуйте степень динамичности информации
Если информация статическая или редко изменяется, то лучшей техникой может стать пред-создание или кэширование. Существует множество техник для кэширования и пред-создания, которые вы можете использовать. Главное, помните - лучшим способом оптимизации доступа к базе является тот, который позволяет избегать доступа к базе данных. Это может относиться к чему угодно - если вы можете избежать динамического формирования страницы и иметь для ее обслуживания кэш на сервера, то следует использовать эту возможность.
Проверьте, соответствует ли информация, полученная из базы данных тому, что вы видите на экране
Часто мы получаем из базы данных гораздо больше информации, чем необходимо для формирования страницы. В лучшем случае, используется SELECT * FROM tbl вместо перечисления списка столбцов, которые нам действительно необходимы, а в худшем, это использование SELECT * FROM tbl для подсчета строк в таблице (без шуток). Иногда можно видеть случаи фильтрации на прикладном уровне, когда, например, из 100 выбранных записей случайным образом выбирается только одна. Некоторые вещи, действительно, выполняются на прикладном уровне более эффективно, но в общем случае, следует так составлять свои запросы, чтобы они возращали только необходимую информацию.
Проанализируйте количество строк, которые используются для формирования результирующего набора данных
Это очень важный и часто забываемый шаг. Некоторые считают, что запрос прост, если он возвращает небольшое количество строк, в то время как действительное значение имеет количество строк, которое было проанализировано в связи с запросом. Например, запрос SELECT COUNT(*) FROM links WHERE domain = ‘mysql.com’; возвратит только один ряд, в то время как может просмотреть сотни тысяч строк (или элементов индекса) для получения результата. Другие наиболее общие "убийственные" запросы, это запросы GROUP BY и запросы с сортировкой - SELECT name,descr FROM titles ORDER BY rank DESC LIMIT 10 - если не определено индекса, соответствующего требованию сортировки, то это может стать частью проблемы. Также следует упомянуть использование объединений JOIN - объединения дорого обходятся с точки зрения производительности (относительно конечно) и наверняка увеличивают количество строк, которые используются для формирования результирующего набора - если вы должны объединить 10 таблиц, для составления объекта, то это будет намного медленнее, чем получение тех же самых данных чтением одной строки.
Проанализируйте количество строк, которые в действительности необходимы для формирования результирующего набора
Простой случай - запросу требуется анализировать большее количество строк для формирования результата, чем нужно только из-за не оптимальной индексации схемы. Типичный пример - запрос ORDER BY rank - добавление индекса на столбец rank упрощает работу запроса и для возврата 10 строк, он обрабатывает только 10 строк - ровно столько, сколько нам нужно. Другое дело - запрос COUNT(*) - даже если вы задали индекс по домену, может потребоваться просмотреть большое количество строк для получения результирующего набора. Такие запросы предпочтительнее спроектировать как-то иначе, т.е. найти другой способ получения результата - например, в этом случае, имела бы смысл итоговая таблица, которая содежала бы количество связей на домен.
Проанализируйте количество запросов
Если вы можете получить такое же количество данных одним запросом, то это предпочтительнее, чем выполнение нескольких запросов, в предположении, что этому запросу не требуется анализировать намного больше строк вследствии его иной оптимизации. Одним из типичных примеров здесь был бы SELECT * FROM tbl WHERE id=5 выполняемый много раз с различными константами - имеет смысл заменить такие запросы, запросом, с использованием IN(5,7,4,56). Однако не слишком этим увлекайтесь. Я был свидетелем того, как люди пытались объединить все запросы в один UNION (с дополнением, чтобы соотнести различные типы и количество столбцов) - это не лучшая практика. Однако, если Вы можете уменьшить количество запросов без серьезных осложнений прикладной архитектуры, то имеет смысл это сделать.