32 совета для ускорения MySQL запросов

1. Используйте постоянное соединение с базой данных, чтобы избежать системных издержек.

2. Проверьте, чтобы на столбцах с высоким количеством уникальных элементов был PRIMARY KEY. Например, у столбца `gender` есть всего 2 варианта (male и female). Уникальный ID пользователя, напротив, содержит большое количество значений и подходит для того, чтобы стать первичным ключом.

3. Желательно, чтобы все связи между таблицами были с индексами (что подразумевает, что у них должны быть одинаковые типы данных, благодаря этому запросы будут быстрее). Также проверьте, чтобы поля, в которых необходимо делать поиск (часто появляются в выражениях WHERE, ORDER BY или GROUP BY) имели индексы. Но не добавляйте слишком много индексов: худшее, что вы можете сделать, это добавить индекс каждому столбцу в таблице (я не видел более 5 индексов даже в таблице с 20-30 столбцами). Если вы никогда не сравниваете столбец с другими данными и не проводите по нему поиск, незачем ставить на нём индекс.

4. Используйте как можно более простые привилегии, когда вы выполняете команду GRAND, чтобы уменьшить издержки из-за проверки привилегий во время подключения к базе.

5. Используйте меньше RAM на строку, точно определяя необходимую длину столбцов. (Например, для хранения пароля в md5 нужно отводить ровно 32 символа, больше не имеет смысла. Просто, но многие об этом забывают.)

6. В MySQL вы можете определить индекс сразу на нескольких колонках одновременно. При этом вы можете использовать крайний слева столбец как отдельный индекс, таким образом уменьшив количество отдельных индексов.

7. Если ваш индекс состоит из нескольких столбцов, почему бы не сделать хэш столбец с индексом, который будет коротким и достаточно уникальным? Тогда ваш запрос может быть похож на этот: SELECT * FROM table WHERE hash_column = MD5( CONCAT(col1, col2) ) AND col1="aaa" AND col2="bbb";

8. Предусмотрите запуск ANALYZE TABLE (или myisamchk --analyze из командной строки) на таблице после того, как вы заполнили её данными, чтобы помочь MySQL оптимизировать запросы.

9. Используйте тип CHAR, когда это возможно (вместо VARCHAR, BLOB или TEXT) — когда у значений столбца есть постоянная длина: хэш MD5, код аэропорта и подобные данные. Данные в столбцах CHAR могут быть найдены быстрее, чем в столбцах с другими типами данных.

10. Не стоит делить таблицу только из-за того, что в ней слишком много столбцов. При доступе к строке это не имеет значения.

11. Столбец должен быть объявлен как NOT NULL, если в нём действительно нет пустых ячеек — таким образом вы слегка ускорите проход по таблице.

12. Если Вы обычно получаете строки в одном и том же порядке, например, expr1, expr2..., сделайте запрос: ALTER TABLE... ORDER BY expr1, expr2... чтобы оптимизировать таблицу.

13. Не используйте цикл в PHP, создавая множество запросов. Вместо этого попробуйте такой запрос: SELECT * FROM `table` WHERE `id` IN (1,7,13,42);

14. Используйте значение столбца по умолчанию, и вставляйте только те значения, которые отличаются от обычного. Это уменьшает время разбора запроса.

15. Используйте INSERT DELAYED или INSERT LOW_PRIORITY (для MyISAM), когда MySQL используется для ведения журналов. Кроме того, если вы работаете с MyISAM, вы можете добавить опцию DELAY_KEY_WRITE=1 — это позволит быстрее обновлять индексы, так как они не будут записываться на диск, пока файл не закроется.

16. Подумайте о том, чтобы хранить данные пользовательских сессий (или любые другие не очень важные данные) в таблице типа MEMORY — это значительно сократит время доступа к базе.

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

18. Если вам необходимо сохранить большое количество текстовых данных, обратите внимание на тип BLOB, который подходит для хранения сжатых данных (COMPRESS() в MySQL выглядит медленным, можно посмотреть на gzipping в PHP). Конечно, предварительно нужно проверить производительность этого решения.

19. Если вам часто приходится вычислять функцию COUNT или SUM, основанную на большом количестве строк (оценки статей, количество голосов в опросе, количество зарегистрированных пользователей, и тому подобное), имеет смысл создать отдельную таблицу и обновлять счётчик в режиме реального времени, что будет намного быстрее. Если вам нужно собрать статистику из огромных таблиц регистрации, используйте сводную таблицу вместо того, чтобы каждый раз просматривать таблицу целиком.

20. Не используйте REPLACE (который на деле является DELETE + INSERT и расходует ID"ы): используйте вместо этого INSERT … ON DUPLICATE KEY UPDATE (то есть INSERT + UPDATE, если произошел конфликт). Эта же техника может использоваться, когда вам сначала нужно сделать SELECT, чтобы узнать, есть ли уже данные в базе, и затем выбрать INSERT или UPDATE. Зачем решать самому - положитесь на базу данных!

21. Настройте кэширование MySQL: выделите достаточно памяти для буфера (например, SET GLOBAL query_cache_size = 1000000), и определите query_cache_min_res_unit в зависимости от среднего размера возвращаемых данных в запросе.

22. Разделите сложные вопросы на несколько более простых — у них больше шансов быть закешированными, соответственно - более быстрыми.

23. Группируйте несколько подобных INSERT"ов в одном длинном со списком VALUES, чтобы вставить несколько строк за один раз: запрос выполнится быстрее из-за того, что время соединения, посылки и разбора запроса примерно в 5-7 раз больше, чем фактическая вставка данных (в зависимости от длины строки). Если это не возможно, используйте START TRANSACTION и COMMIT, при условии, что вы работаете с InnoDB. Иначе пользуйтесь LOCK TABLES — это сокращает время, так как буфер индекса сбрасывается на диск только один раз, после того, как все операторы INSERT были выполнены. При этом не забывайте разблокировать таблицы примерно через 1000 вставленных строк, чтобы дать другим потокам доступ к таблице.

24. Загружая таблицу из текстового файла, используйте LOAD DATA INFILE, это в 20-100 раз быстрее.

25. Находите узкие места в приложении и исследуйте их. Так вы сможете найти запросы с высоким временем выполнения, не использующие индексы, а также медленные выражения, такие как OPTIMIZE TABLE и ANALYZE TABLE.

26. Настройте параметры сервера базы данных. Например, увеличив размер буфера.

27. Если в вашем приложении много DELETE"ов или обновлений динамических форматов строк (если в строке есть столбец типа VARCHAR, BLOB или TEXT, у строки есть динамический формат), запускайте каждую неделю по крону OPTIMIZE TABLE. Дефрагментация способствует повышению скорости запросов. Если вы не используете репликацию, добавьте ключевое слово LOCAL, чтобы дефрагментация занимала меньше времени.

28. Не используйте ORDER BY RAND(), чтобы получить несколько случайных строк. Получите 10-20 записей (последние по времени добавления или ID) и сделайте array_random() на стороне PHP. Есть и другие решения.

29. Постарайтесь избегать выражения HAVING — оно слегка тормозит.

30. В большинстве случаев выражение DISTINCT можно рассмотреть как особый случай GROUP BY; таким образом, оптимизация, применимая к запросам GROUP BY, может быть также применена к запросам с выражением DISTINCT. Кроме того, если вы используете DISTINCT, постарайтесь использовать LIMIT (MySQL останавливается, как только находит row_count уникальных строк), и избегайте ORDER BY (во многих случаях он требует временной таблицы).

31. Когда я прочитал "Building scalable web sites", я понял, что иногда необходимо де-нормализовать некоторые таблицы (кстати, так делает Flickr), то есть дублировать некоторые данные в нескольких таблицах, чтобы избежать JOIN"ов, которые могут дорого обойтись.

32. Если вы хотите протестировать какую-то функцию или выражение в MySQL, используйте для этого BENCHMARK.

найдено тут: http://www.recens.ru/