Топ-100 советов по производительности MySQL

Заметка была опубликована на хабре (habrahabr.ru) ИМХО есть что подчерпнуть для разработчика БД. Перевод сделан мной, так что если будут какие то ошибки или неточность перевода, просьба указывать в комментах или написать мне через форму обратной связи.

1. Используйте EXPLAIN чтобы представить схему выполнения запроса.
2. Включайте Slow Query Log .
3. Не используйте DISTINCT когда у вас присутвтвует GROUP BY.
4. Выполнение INSERT
   • Batch INSERT and REPLACE
   • Используйте LOAD DATA вместо INSERT
5. LIMIT m,n может быть не таким как кажется
6. Не используйте ORDER BY RAND() если у вас больше 2к записей.
7. Используйте SQL_NO_CACHE если вы часто SELECTing (выбираете) данные или обновляете их.
8. Избегайте символы в начале LIKE запроса
9. Избегайте сопоставлять подзапросы и IN выборе и где положение (стараейтесь избегать IN) 
10. Не рассчитывайте сравнения — изолированых индексированных столбцов.
11. ORDER BY и LIMIT работают наилучшим образом с равенствами и крытыми индексами.
12. Отделяйте text/blobs от метаданных, не включайте в запрос text/blobs если не используете их.
13. Производные таблицы (подзапросы в ИЗ положение) может быть полезна для поиска BLOB-сортировка без них.(Присоединение на половину могут ускорить запросы, если 1-ая часть находит и использует идентификаторы затем извлечь из остатка).ALTER TABLE… ORDER BY может принимать данные отсортированы в хронологическом порядке, и изменить порядок его разных местах — это может делать запросы о том, что выборка из поля может работать быстрее (возможно, это выходит на индексацию?).
14. Подумайте, когда можно разбить сложные запросы и разбить на мелкие.
15. Производите удаление записей понемногу, если это возмжно.
16. Делая однотиптые запросы, убедитесь что запросы кешируются.
17. Придерживайтесь стандарта написания SQL запроса.
18. Не используйте запросы с возражением.
19. Включая OR на несколько индексных полей (<5.0) в UNION могут величить производительность (с LIMIT) после 5.0 index_merge должны получить прирост в производительности.
20. Не используйте COUNT * в Innodb таблицах, для каждого поиска, делайте это для несколько раз для нескольких таблиц, или если вам нужно для конретного числа таблиц, используйте SQL_CALC_FOUND_ROWS и SELECT FOUND_ROWS(). 
21. Используйте INSERT ON DUPLICATE KEY обновление (INSERT IGNORE) чтобы избежать необходимости выборки.
22. Используйте groupwise maximum вместо подзапросов.

Общие советы

 1. Используйте benchmarking (тесты)

 2. Изолируйте рабочее место не допускайте административный интерфейс вместе с пользовательским (например, резервное копирование).

 3. Debugging sucks, testing rocks!

 4. Если ваши данные увеличиваются, то индексирование может измениться (мощности и селективности изменения). Может быть потребуется изменить структуру. Старайтесь «делать» ваш код структурированным. Сделайте свой код масштабным. План и принять изменения, а также получить разработчиков сделать то же самое.

 

Сетевые улучшения 

 1. Выбирайте только то что вам нужно, экономьте трафик.

    1. Пейджинг / фрагментированное поиска данных по ограничению

    2. Не используйте SELECT *

    3. Не используйте, старайтесь избежать, множества мелких запросов в замен одному побольше.

 2. Использование multi_query в случае необходимости может сократить запросы.

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

 

Настройки ОС

 1. Использование надлежащих данных разделов

 2. Для кластерных систем. Начать думать о кластере * до * Вы в них нуждается 

 3. Хранить базу данных хоста чистую, насколько это возможно. Вы действительно нуждаетесь в Windows для сервера?

 4. Используйте преимущество OS.

 5. Создайте среду для тестирования.

 6. Настраивайте исходные коды и конфигурационные файлы.

 7. Для LVM innodb backups, восстановить различные уровни MySQL ла  так InnoDB может восстанавливаться наперед.

 8. Настройки для раздела на диске. 

 

Настройки MySQL Server

 1. innodb_flush_commit=0 может помочь slave изоляции 

 2. Оптимизируйте тип данных использоемый тип данных. Используйте PROCEDURE ANALYSE() чтобы определить наименьший тип используемых типов данных.

 3. use optimistic locking, not pessimistic locking. try to use shared lock, not exclusive lock. share mode vs. FOR UPDATE

 4. Если возможно, используейте компрессию text/blobs. 

 5. Используйте сжатие для статиских данных. 

 6. Не делайте часто backup статических данных.

 7. Увеличение и увеличение запросов и buffer caches в случае необходимости.

 8. Конфигурационные параметры — http://docs.cellblue.nl/2007/03/17/easy-mysql-performance-tweaks  -рекомендованне как стандарт.

 9. Параметры конфигурации: 

     1. Использование одного из поставляемых конфигурационных файлов

     2. key_buffer, unix cache (оставят RAM свободной), per-connection variables, innodb memory variables 

     3. be aware of global vs. per-connection variables

     4. Проверьте SHOW STATUS and SHOW VARIABLES (GLOBAL|SESSION в 5.0 и выше) 

     5. be aware of swapping esp. with Linux, «swappiness» (bypass OS filecache for innodb data files, innodb_flush_method=O_DIRECT if possible (this is also OS specific))

     6. Дефрагментируйте таблицы, перестраивайте индексы, когда обслуживаете таблицу. 

     7. Если вы используете innodb_flush_txn_commit=1, используйте battery-backed hardware cache write controller

     8. Больше памяти ОЗУ лучше чем быстрее диск.

     9. Используйте 64-bit архитектуру.

 10. --skip-name-resolve 

 11. Увеличивайте myisam_sort_buffer_size для больших INSERT'в

 12. look up memory tuning parameter for on-insert caching

 13. increase temp table size in a data warehousing environment (default is 32Mb) so it doesn't write to disk (also constrained by max_heap_table_size, default 16Mb)

 14. Запуск к SQL_MODE=STRICT поможет выявить предупреждения.

 15. /tmp dir on battery-backed write cache

 16. consider battery-backed RAM for innodb logfiles

 17. Используйте --safe-updates для клиентов.

 18. Redundant данных является излишним 

 

 Storage Engine Performance Tips: 

1. InnoDB ALWAYS keeps the primary key as part of each index, so do not make the primary key very large
2. Utilize different storage engines on master/slave ie, if you need fulltext indexing on a table.
3. BLACKHOLE engine and replication is much faster than FEDERATED tables for things like logs.
4. Know your storage engines and what performs best for your needs, know that different ones exist. 
5. use row-level instead of table-level locking for OLTP workloads
6. try out a few schemas and storage engines in your test environment before picking one.

1. ie, use MERGE tables ARCHIVE tables for logs
2. Archive old data — don't be a pack-rat! 2 common engines for this are ARCHIVE tables and MERGE tables

Оптимизация разработки базы данных

1. Разрабатывая разумную схему запросов не бойтесь использовать join, часто они быстрее чем деморализованные запросы.
2. Не используйте булевые флаги.
3. Используйте индексы.
4. Не индексируйте все подряд.
5. Не используйте повторяющиеся индексы.
6. Не используйте индексы большого размера.
7. Будьте осторожны от избыточных столбцов в индексе или через индексы.
8. Используйте «умный ключ» и ORDER BY вместо MAX
9. Нормализируйте сначала, и денормализируйте, где присваивают.
10. Databases are not spreadsheets, even though Access really really looks like one. Then again, Access isn't a real database
11. Используйте INET_ATON и INET_NTOA для IP адресов, не char и не varchar. 
12. make it a habit to REVERSE() email addresses, so you can easily search domains (this will help avoid wildcards at the start of LIKE queries if you want to find everyone whose e-mail is in a certain domain)
13. NULL тип данных может занять больше места для хранения, чем NOT NULL.
14. Выберите соответствующие наборы символов & collations — UTF16 будет хранить каждый символ в 2 байта, нуждается ли он в нем или нет, latin1 быстрее чем UTF8.
15. Использование триггеров оправданно.
16. use min_rows and max_rows to specify approximate data size so space can be pre-allocated and reference points can be calculated.
17. Использование хэш-индексирования для индексации всей колонны с аналогичными данными префиксами
18. Используйте myisam_pack_keys числового типа данные.
19. Будьте готовы изменить вашу схему без изменения кода.
20. segregate tables/databases that benefit from different configuration variables

 

Авторы: Jay Pipes, Sheeri Kritzer, Bill Karwin, Ronald Bradford, Farhan «Frank Mash» Mashraqi, Taso Du Val, Ron Hu, Klinton Lee, Rick James, Alan Kasindorf, Eric Bergen, Kaj Arno, Joel

Оригинал статьи forge.mysql.com/wiki/Top10SQLPerformanceTips