MySQL ускорение SQL запросов

Со временем информации на сайте становится больше так как сайт постоянно развивается и увеличивается в объемах, особенно это касается интернет магазинов где постоянно добавляются новые товары с описанием и характеристиками. Раз растет сайт - увеличивается база данных, ну и как следствие время выполнения SELECT запросов. При этом ощущается заметное торможение скорости работы сайта или интернет магазина.

По опыту скажу что довольно часто при разработке интернет магазина не учитывались методы оптимизации базы данных, не проставлены индексы, не оптимизированы SQL запросы, большая избыточность.

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

• Используйте постоянное соединение с базой данных, чтобы избежать затрат времени на пере соединение.
• Проверьте, чтобы на столбцах (в основном уникальных идентификаторах ID) был указан первичный ключ PRIMARY KEY.
• Все связи между таблицами LEFT (RIGHT) JOIN, INNER JOIN были с индексами + у них должны быть одинаковые типы данных. Также индексы необходимо поставить на поля по которым производится отбор или поиск, в выражениях WHERE, ORDER BY или GROUP BY. На поля которые не учувствуют в связях или поиске - индекс ставить НЕ НУЖНО!
• Используйте меньше выделяемой памяти на поле. Например, для хранения фамилии достаточно 70 символов varchar(70), не нужно ставить 255
• В MySQL вы можете определить составной индекс сразу на нескольких полях одновременно. Это делается при одновременном поиске по 2-му и более полям.
• Также можно воспользоваться хэш столбцом с индексом, например если ваш индекс состоит из нескольких полей. Пример такого запроса: SELECT * FROM mytable WHERE hash_col = MD5( CONCAT(colum1, colum2) ) AND colum1='aaa' AND colum2='bbb';
• Оптимизировать запросы поможет запуск ANALYZE TABLE (или myisamchk --analyze из командной строки) на таблице после того, как вы заполнили её данными.
• Если в поле пустых ячеек оно должно быть объявлено как NOT NULL — таким образом вы немного ускорите обход таблицы.
• Для отбора сразу нескольких значений с массива например используйте такой запрос: SELECT * FROM `mytable` WHERE `id` IN (1,3,7,22);
• При частом использовании функции COUNT или SUM в таблицах с большим количеством строк, можно создать отдельную таблицу и обновлять счётчик в ней.
• Настройте кэширование MySQL: выделите достаточно памяти для буфера (например, SET GLOBAL query_cache_size = 31М), и определите query_cache_min_res_unit в зависимости от среднего размера возвращаемых данных в запросе. Не следует ставить большой значение query_cache_size так как будет быстро расходоваться оперативная память.
• Разделите сложные вопросы на несколько более простых, часть обработки можно перенести на уровень PHP и закешировать результаты.
• Группируйте несколько подобных INSERT'ов в одном длинном со списком VALUES, чтобы вставить несколько строк за один раз: запрос выполнится быстрее из-за того, что время соединения, посылки и разбора запроса примерно в 5-7 раз больше, чем фактическая вставка данных (в зависимости от длины строки). Если это не возможно, используйте START TRANSACTION и COMMIT, при условии, что вы работаете с InnoDB. Иначе пользуйтесь LOCK TABLES — это сокращает время, так как буфер индекса сбрасывается на диск только один раз, после того, как все операторы INSERT были выполнены. При этом не забывайте разблокировать таблицы примерно через 1000 вставленных строк, чтобы дать другим потокам доступ к таблице.
• Находите узкие места в приложении и исследуйте их. Так вы сможете найти запросы с высоким временем выполнения, не использующие индексы, а также медленные выражения, такие как OPTIMIZE TABLE и ANALYZE TABLE.