Компания SQL Нормы

Введение

Компания SQL Нормы

Пояснения по использованию

• Основные принципы1. Перед использованием любого запроса выполните EXPLAIN для проверки плана выполнения и использования индексов. 2. Не выполняйте статистические запросы из детализированных таблиц, регулярно выполняйте статистику и вставляйте результаты в сводные таблицы. 3. Запрещено использовать SELECT *, необходимо указывать конкретные имена полей, включая список полей при вставке данных в таблицу. 4. При детализированном статистическом анализе используйте только коды, не связывайте с ненужными полями, такими как наименования. 5. При объединении запросов используйте псевдонимы для каждого из таблиц, например SELECT T1.BM FROM GS T1 LEFT JOIN GSJJ T2 ON T1.BM = T2.BM. 6. Объем используемой памяти для каждого запроса не должен превышать 256 МБ, можно контролировать через диапазон времени, например RK BETWEEN A AND B, рекомендуется для больших таблиц использовать часовые диапазоны. 7. Запросы страницы должны возвращать результаты за 10 секунд, стандартное ограничение времени выполнения на сервере составляет 65 секунд. 8. Выполните SHOW VARIABLES LIKE '%cache%' для проверки размера и эффективности использования кэша запросов. 9. Выполните SHOW FULL PROCESSLIST для проверки ожидания кэша запросов, при значительных задержках отключите глобальное использование кэша.Избегайте использования подзапросов, так как они не могут использовать индексы (исключение: некоторые подзапросы могут использовать индексы; проверяйте план выполнения). 11. Избегайте использования GROUP BY; можно использовать периодические сводные отчеты через скрипты.• Проектирование полей

1. Используйте более компактные типы данных, например TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT (например, int(11) — это ширина отображения в клиенте, а не диапазон значений, tinyint — 2^7-2^7-1, smallint — 2^15-2^15-1, int — 2^31-2^31-1, bigint — 2^63-2^63-1).

2. Избегайте использования TEXT и BLOB, используйте ссылки вместо них.

3. Для полей одного типа используйте одинаковые типы данных, например не смешивайте числовые и текстовые поля, используйте преобразования впоследствии.

4. Типы и размеры полей должны быть одинаковыми для полей с одинаковыми именами в разных таблицах.

5. Имена полей не должны совпадать с ключевыми словами.

6. Не устанавливайте кодировку на уровне полей, рекомендуется использовать единый стандарт для всей базы данных.

7. Установите стандартные значения для полей, например, для дат не используйте 0000-00-00.

8. Не используйте автоинкрементные ID в качестве первичного ключа, так как они не могут быть синхронизированы и не имеют ограничений.

9. Не используйте внешние ключи и триггеры.

10. Для записей, связанных с транзакциями, сохраняйте метки времени, рекомендуется использовать только добавление, без изменения; при необходимости изменения записей, сохраняйте метки времени изменений.

11. Для полей с высокой точностью используйте decimal(number, numeric) вместо double и float(real).

12. Запрещено использование названий полей на языке, отличном от английского. • Использование индексов

13. Обычно один запрос использует только один индекс (в особых случаях возможна ситуация с объединением индексов, например, для выражений вида (a=1 or b=2) объединяются индексы a и b, или используется UNION ALL).

14. Чем меньше индексов у таблицы, тем лучше, рекомендуется иметь 1-3 индекса, максимум 5 (для OLTP 1-5, для OLAP более 5).

15. Каждый запрос должен использовать индекс (для маленьких таблиц полное сканирование может быть лучше, в зависимости от объема данных). Если индекс отсутствует, даже если добавлено rownum = 1, будет выполнено полное сканирование таблицы.

16. При создании составного индекса, поля, используемые в WHERE с оператором равенства, следует располагать в начале, а поля с диапазоном значений — в конце, например, DD=100000 AND SJ BETWEEN A AND B (см. выше).

17. Удаляйте индексы, содержащие повторяющиеся поля, чтобы уменьшить DML IO.

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

19. Чем меньше повторяющихся записей в индексе, тем выше его эффективность, наиболее эффективным является первичный ключ. Если одна и та же запись превышает 50%, следует регулярно выполнять полное сканирование таблицы и собирать статистическую информацию и гистограммы с помощью команды ANALYZE TABLE. Если возможно, добавьте NOT NULL или UNIQUE.

20. В индексах лучше избегать NULL значений, NULL можно заменить на 0. Рекомендуется устанавливать значение по умолчанию 0.myisam_stats_method и innodb_stats_method должны иметь значение nulls_equal. В случае, когда NULL значений намного больше, чем не-NULL, рекомендуется устанавливать значение по умолчанию 0 и запрещать использование nulls_equal при проектировании таблицы.

21. Составной индекс можно использовать только для первого поля, или для первых двух полей, или для первых нескольких полей, но не начинать с второго поля и не пропускать поля при использовании индекса. Использование индекса должно начинаться с префикса, для многофакторного индекса между полями BETWEEN или <, > и т. д. Следующие поля не будут использоваться индексом. Лучше всего выполнять сортировку внутри индекса.

22. Кодировка индексов, используемых в соединениях, должна быть одинаковой, рекомендуется использовать UTF-8.

23. Если кодировки различаются, индекс будет недействителен. • Параметры запроса1. Условия WHERE в SQL-запросах должны быть корректными, без лишних условий и скобок, например, SELECT BM FROM GS WHERE 1=1, ORDER BY

24. В SQL-запросах не следует добавлять ненужные сортировки

25. Управлять временными результатами, включая промежуточные результаты и сортировки, можно с помощью запросов SELECT * FROM v$sort_usage, SHOW STATUS LIKE '%temp%'

26. В WHERE-условиях лучше избегать использования IN и LIKE, заменяя IN на EXISTS, а LIKE на более конкретные условия

27. В WHERE-условиях не следует использовать NOW() и подобные функции, так как они могут влиять на план выполнения

28. Запрещено использование неавторизованных подсказок (hints), например, SELECT sql_no_cache * FROM ?

29. Для полей, используемых в индексах, не следует применять функции или выполнять вычисления, например, field1 + 1 = field2, adddate(field1, ...), CAST

30. Запрещено преобразование формата полей, например, SELECT x FROM GS WHERE BM=200000, числовые значения не должны быть заключены в кавычки

31. Большинство функций, применяемых к полям, не используют индексы, за исключением случаев, когда функция применяется к индексированному полю, например, left(BM)='200000'

32. Функциональные индексы могут быть использованы только для определенных функций, которые гарантируют уникальность

• Хранимые процедуры1. В хранимых процедурах, если количество обрабатываемых записей превышает 1000, следует избегать использования курсоров (курсоры запрещены, следует использовать временные таблицы, триггеры также запрещены). 2. В ключевых шагах хранимых процедур следует записывать информацию в журнал для мониторинга и отладки. 3. Для символьных переменных следует использовать одинарные кавычки, а не двойные, например, ['2012-09-23 00:00:00'] можно заменить на ['2012-09-23 00:00:00']. 4. Хранимые процедуры должны быть способны к повторному выполнению, при этом следует очищать конфликтные записи. 5. Использование автономных транзакций для управления записью в журнал, отдельные операции с журналом в хранимых процедурах.• Отдаленные таблицы

1. Структура отдалённых таблиц должна соответствовать структуре исходных таблиц, особенно индексам.
2. Объём данных в отдалённых таблицах не должен превышать 256 МБ, WHERE-условия для отдалённых таблиц могут быть некорректными.
3. Отдалённые таблицы обычно используются для полной синхронизации небольших таблиц.
4. После выполнения операций с отдалёнными таблицами следует подтверждать изменения.

• Техники запросов

1. SQL-запросы не должны быть слишком длинными, если список значений в IN слишком велик, следует заменить его на LEFT JOIN, и связывать с ключевым индексом.
2. Избегайте использования LIKE, например, lrsj like "2012-09-23%" можно заменить на LRSJ BETWEEN '2012-09-23 00:00:00' AND '2012-09-23 23:59:59' или использование функций left, right.
3. Для нескольких условий OR в WHERE-условии, если они не используют один индекс, следует использовать UNION, например, bm1=953016 or bm2=953016 можно заменить на SELECT ... WHERE BM1=953016 UNION ALL SELECT ... WHERE BM2=953016, объединение индексов, результат explain будет using union(idx_name, idx_name).

• Оптимизация производительности

1. Переключение на файловую систему XFS может повысить производительность на 5%, увеличение объёма диска на 1/6 может повысить производительность на 1/6, оптимизация индексов и структуры может повысить производительность на 100-1000 раз.
2. Использование временных таблиц типа heap.
3. Использование движков.1. Для движка InnoDB в конце процесса производится коммит, чтобы избежать избыточных коммитов.
4. Избегайте операций, пересекающих разные движки, например, если одна таблица использует InnoDB, а другая — MyISAM.

• Управление правами доступа

1. Пользователь, используемый PHP для подключения к MySQL, должен иметь только права SELECT, INSERT, UPDATE и DELETE.
2. Все передаваемые переменные проходят через mysql_real_escape_string для экранирования, чтобы предотвратить инъекции. Все можно проверить с помощью инструмента для унифицированного тестирования.