Если мы говорим об источнике проблем, значит, для начала было бы хорошо выяснить в MySQL ли дело вообще, потому что достаточно часто сталкиваешься с ситуацией, когда у людей проблемы с приложением, а в реальности подозревают базу данных, но подозревают её достаточно безосновательно. Может быть, среди тех 10 секунд, когда грузится страничка, MySQL съедает не так много времени. В таком случае полезно иметь инструментацию приложения
Если вам интересно, как создавать по-настоящему быстрые запросы к MySQL, эта статья для вас.
- Используйте постоянное соединение с базой данных, чтобы избежать системных издержек.
- Проверьте, чтобы на столбцах с высоким количеством уникальных элементов был PRIMARY KEY. Например, у столбца `gender` есть всего 2 варианта (male и female). Уникальный ID пользователя, напротив, содержит большое количество значений и подходит для того, чтобы стать первичным ключом.
- Желательно, чтобы все связи между таблицами были с индексами (что подразумевает, что у них должны быть одинаковые типы данных, благодаря этому запросы будут быстрее). Также проверьте, чтобы поля, в которых необходимо делать поиск (часто появляются в выражениях WHERE, ORDER BY или GROUP BY) имели индексы. Но не добавляйте слишком много индексов: худшее, что вы можете сделать, это добавить индекс каждому столбцу в таблице (я не видел более 5 индексов даже в таблице с 20-30 столбцами). Если вы никогда не сравниваете столбец с другими данными и не проводите по нему поиск, незачем ставить на нём индекс.
- Используйте меньше RAM на строку, точно определяя необходимую длину столбцов. (Например, для хранения пароля в md5 нужно отводить ровно 32 символа, больше не имеет смысла. Просто, но многие об этом забывают.)
- В MySQL вы можете определить индекс сразу на нескольких колонках одновременно. При этом вы можете использовать крайний слева столбец как отдельный индекс, таким образом уменьшив количество отдельных индексов.
- Если ваш индекс состоит из нескольких столбцов, почему бы не сделать хэш столбец с индексом, который будет коротким и достаточно уникальным? Тогда ваш запрос может быть похож на этот: SELECT * FROM table WHERE hash_column = MD5( CONCAT(col1, col2) ) AND col1='aaa' AND col2='bbb';
- Предусмотрите запуск ANALYZE TABLE (или myisamchk --analyze из командной строки) на таблице после того, как вы заполнили её данными, чтобы помочь MySQL оптимизировать запросы.
- Не стоит делить таблицу только из-за того, что в ней слишком много столбцов. При доступе к строке это не имеет значения.
- Столбец должен быть объявлен как NOT NULL, если в нём действительно нет пустых ячеек — таким образом вы слегка ускорите проход по таблице.
- Не используйте цикл в PHP, создавая множество запросов. Вместо этого попробуйте такой запрос: SELECT * FROM `table` WHERE `id` IN (1,7,13,42);
- Используйте значение столбца по умолчанию, и вставляйте только те значения, которые отличаются от обычного. Это уменьшает время разбора запроса.
- Используйте INSERT DELAYED или INSERT LOW_PRIORITY (для MyISAM), когда MySQL используется для ведения журналов. Кроме того, если вы работаете с MyISAM, вы можете добавить опцию DELAY_KEY_WRITE=1 — это позволит быстрее обновлять индексы, так как они не будут записываться на диск, пока файл не закроется.
- Если вам часто приходится вычислять функцию COUNT или SUM, основанную на большом количестве строк (оценки статей, количество голосов в опросе, количество зарегистрированных пользователей, и тому подобное), имеет смысл создать отдельную таблицу и обновлять счётчик в режиме реального времени, что будет намного быстрее. Если вам нужно собрать статистику из огромных таблиц регистрации, используйте сводную таблицу вместо того, чтобы каждый раз просматривать таблицу целиком.
- Настройте кэширование MySQL: выделите достаточно памяти для буфера (например, SET GLOBAL query_cache_size = 1000000), и определите query_cache_min_res_unit в зависимости от среднего размера возвращаемых данных в запросе
- Разделите сложные вопросы на несколько более простых — у них больше шансов быть закешированными, соответственно - более быстрыми.
- Группируйте несколько подобных INSERT'ов в одном длинном со списком VALUES, чтобы вставить несколько строк за один раз: запрос выполнится быстрее из-за того, что время соединения, посылки и разбора запроса примерно в 5-7 раз больше, чем фактическая вставка данных (в зависимости от длины строки). Если это не возможно, используйте START TRANSACTION и COMMIT, при условии, что вы работаете с InnoDB. Иначе пользуйтесь LOCK TABLES — это сокращает время, так как буфер индекса сбрасывается на диск только один раз, после того, как все операторы INSERT были выполнены. При этом не забывайте разблокировать таблицы примерно через 1000 вставленных строк, чтобы дать другим потокам доступ к таблице.
- Находите узкие места в приложении и исследуйте их. Так вы сможете найти запросы с высоким временем выполнения, не использующие индексы, а также медленные выражения, такие как OPTIMIZE TABLE и ANALYZE TABLE.
