52 стратегии оптимизации производительности операторов SQL, рекомендуемые для сбора

Java

В этой статье будут упомянуты 52 стратегии оптимизации производительности операторов SQL.

1. При оптимизации запросов следует по возможности избегать полного сканирования таблицы.Во-первых, следует учитывать индексы для столбцов, задействованных в WHERE и ORDER BY.

2. Старайтесь избегать определения значения NULL для полей в предложении WHERE.NULL является значением по умолчанию при создании таблицы, но в большинстве случаев следует использовать NOT NULL или использовать специальное значение, такое как 0, -1. как значение по умолчанию.

3. Старайтесь избегать использования оператора != или в предложении WHERE. MySQL использует индексы только для следующих операторов: , >=, BETWEEN, IN и иногда LIKE.

4. Старайтесь избегать использования ИЛИ для соединения условий в предложении WHERE, иначе движок откажется от использования индексов и выполнит полное сканирование таблицы.Вы можете использовать UNION для слияния запроса: select id from t where num=10 union все выбирают идентификатор из t, где num=20.

5. Используйте IN и NOT IN с осторожностью, иначе это приведет к полному сканированию таблицы. Для непрерывных значений используйте BETWEEN вместо IN: выберите id из t, где num от 1 до 3.

6. Следующий запрос также приведет к полному сканированию таблицы: выберите идентификатор из t, где имя похоже на '%abc%', или выберите id из t, где имя похоже на '%abc'. Для повышения эффективности можно рассмотреть возможность полнотекстового поиска. И выберите идентификатор из t, где имя типа «abc%» использует только индекс.

7. Если в предложении WHERE используется параметр, это также приведет к полному сканированию таблицы.

8. Старайтесь избегать выполнения операций с выражениями над полями в предложении WHERE и старайтесь избегать выполнения функциональных операций над полями в предложении WHERE.

9. Во многих случаях лучше использовать EXISTS вместо IN: выберите число из a, где число в (выберите число из b). Замените его следующим утверждением: выберите число из a, где существует (выберите 1 из b, где число = a.num).

10. Хотя индекс может повысить эффективность соответствующего SELECT, он также снижает эффективность INSERT и UPDATE. Поскольку индекс может быть перестроен во время INSERT или UPDATE, то, как построить индекс, требует тщательного рассмотрения в зависимости от конкретной ситуации. Количество индексов в таблице не должно превышать 6. Если индексов слишком много, следует подумать, не нужно ли строить индексы по некоторым редко используемым столбцам.

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

12. Максимально используйте числовые поля.Если поля содержат только числовую информацию, старайтесь не оформлять их как символьные поля, что снизит производительность запроса и соединения и увеличит нагрузку на хранилище.

13. Максимально используйте varchar, nvarchar вместо char, nchar. Поскольку место для хранения полей переменной длины небольшое, место для хранения можно сэкономить, а во-вторых, для запросов эффективность поиска в относительно небольшом поле явно выше.

14. Лучше не использовать возврат всех: выберите из t, замените «*» списком определенных полей и не возвращайте поля, которые не используются.

15. Старайтесь не возвращать клиенту большой объем данных.Если объем данных слишком велик, следует подумать, разумно ли соответствующее требование.

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

17. Используйте «временную таблицу» для временного хранения промежуточных результатов:

Важным способом упрощения операторов SQL является использование временных таблиц для временного хранения промежуточных результатов. Однако преимуществ временных таблиц гораздо больше: временные результаты временно хранятся во временной таблице, а последующие запросы — в базе данных tempdb, что позволяет избежать многократного сканирования основной таблицы в программе и значительно уменьшить блокировку «общих блокирует» во время выполнения программы. «Блокировка обновления» уменьшает блокировку и повышает производительность при одновременном выполнении.

18. Некоторые операторы запросов SQL должны быть добавлены без блокировки.Чтение и запись будут блокировать друг друга, чтобы улучшить параллельную производительность. Для некоторых запросов можно добавить nolock, чтобы разрешить запись при чтении, но недостатком является то, что незафиксированные грязные данные могут быть прочитаны.

Есть 3 принципа использования nolock:

  • Результат запроса, используемый для «вставки, удаления и модификации», не может добавлять блокировку;
  • Запрашиваемая таблица подвержена частым разбиениям страниц, поэтому используйте nolock с осторожностью;
  • Использование временной таблицы может также сохранить "переднюю тень данных", функция которой аналогична табличному пространству отмены Oracle. Если временную таблицу можно использовать для повышения производительности параллельных операций, не используйте nolock.

19. Общие правила упрощения следующие:

Не используйте более 5 объединений таблиц (JOIN), рассмотрите возможность использования временных таблиц или табличных переменных для хранения промежуточных результатов. Используйте меньше подзапросов и не вкладывайте слишком глубокие представления. Как правило, рекомендуется не вкладывать более двух представлений.

20. Предварительно вычислите запрашиваемые результаты и поместите их в таблицу, а затем выберите при запросе. Это было самое важное средство до SQL7.0, например, расчет платы за госпитализацию в больницах.

21. Предложение OR можно разложить на несколько запросов, а несколько запросов можно соединить с помощью UNION. Их скорость зависит только от того, используется индекс или нет.Если в запросе необходимо использовать объединенный индекс, более эффективно использовать UNION all. Несколько предложений OR не используют индекс и переписываются в форме UNION, чтобы попытаться сопоставить индекс. Ключевой вопрос заключается в том, используются ли индексы.

22. В списке значений за IN поместите наиболее часто встречающееся значение вверху, а наименее встречающееся значение в конце, чтобы уменьшить количество суждений.

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

Хранимые процедуры — это операторы SQL, которые скомпилированы, оптимизированы, организованы в план выполнения и сохранены в базе данных.Они представляют собой набор языков потока управления и, конечно же, работают быстро. Для динамического SQL, который выполняется повторно, могут использоваться временные хранимые процедуры, а процедуры (временные таблицы) помещаются в базу данных Tempdb.

24. Когда на сервере будет достаточно памяти, настройтеКоличество потоков = максимальное количество соединений + 5, чтобы максимизировать эффективность; в противном случае используйтеКоличество потоков настройки , включите пул потоков SQL SERVER для решения, если все ещеКоличество = максимальное количество подключений + 5, серьезно снизить производительность сервера.

25. Ассоциация запроса такая же, как и порядок написания:

select a.personMemberID, * from chineseresume a,personmember b where personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' （A = B, B = '号码'） select a.personMemberID, * from chineseresume a,personmember b where a.personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' and b.referenceid = 'JCNPRH39681' （A = B, B = '号码', A = '号码'） select a.personMemberID, * from chineseresume a,personmember b where b.referenceid = 'JCNPRH39681' and a.personMemberID = 'JCNPRH39681' （B = '号码', A = '号码'）

26. Попробуйте использовать EXISTS вместо select count(1), чтобы определить, есть ли запись. Функция count используется только для подсчета всех строк в таблице, и count(1) более эффективна, чем count(*).

27. Попробуйте использовать ">=" вместо ">".

28. Спецификации по использованию индексов:

  • Создание индексов следует рассматривать совместно с приложением.Рекомендуется, чтобы большие OLTP-таблицы не имели более 6 индексов;
  • Максимально используйте поля индекса в качестве условий запроса, особенно кластеризованные индексы.При необходимости можно использовать index имя_индекса для форсирования указанного индекса;
  • Избегайте сканирования таблиц при запросе больших таблиц и при необходимости рассмотрите возможность создания новых индексов;
  • При использовании поля индекса в качестве условия, если индекс является совместным индексом, первое поле в индексе должно использоваться в качестве условия, чтобы гарантировать, что система использует индекс, иначе индекс не будет использоваться;
  • Обратите внимание на обслуживание индекса, периодически перестраивайте индекс и перекомпилируйте хранимую процедуру.​

29. Столбцы в следующих условных операторах SQL правильно проиндексированы, но скорость выполнения очень низкая:

SELECT * FROM record WHERE substrINg(card_no, 1, 4) = '5378' --13秒 SELECT * FROM record WHERE amount/30 < 1000 --11秒 SELECT * FROM record WHERE convert(char(10), date, 112) = '19991201' --10秒

анализировать:

Результат любой операции со столбцом в предложении WHERE вычисляется столбец за столбцом при выполнении SQL, поэтому он должен выполнять поиск в таблице без использования индекса для этого столбца.

Если эти результаты доступны во время компиляции запроса, их можно оптимизировать с помощью оптимизатора SQL, используя индексы и избегая поиска по таблицам, поэтому перепишите SQL следующим образом:

SELECT * FROM record WHERE card_no like '5378%' -- < 1秒 SELECT * FROM record WHERE amount < 1000*30 -- < 1秒 SELECT * FROM record WHERE date = '1999/12/01' -- < 1秒

30. Когда есть пакет вставок или обновлений, использование пакетных вставок или пакетных обновлений никогда не будет обновлять записи одну за другой.

31. Во всех хранимых процедурах я никогда не буду использовать цикл для реализации операторов SQL.

Например: чтобы перечислить каждый день предыдущего месяца, я буду использовать connect by для рекурсивного запроса, и я никогда не буду использовать цикл от первого до последнего дня предыдущего месяца.

32. Выберите наиболее эффективный порядок имен таблиц (допустим только в оптимизаторе на основе правил):

Синтаксический анализатор Oracle обрабатывает имена таблиц в предложении FROM в порядке справа налево. Последняя таблица, записанная в предложении FROM (основная таблица управления таблицей), будет обработана первой. В предложение FROM включено несколько таблиц. В случае , вы должны выбрать таблицу с наименьшим количеством записей в качестве базовой таблицы.

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

33. Повысьте эффективность оператора Group By, можно отфильтровать нежелательную запись перед Group By. Следующие два запроса вернули один и тот же результат, но второй значительно быстрее.

Неэффективно:

SELECT JOB, AVG(SAL) FROM EMP GROUP BY JOB HAVING JOB = 'PRESIDENT' OR JOB = 'MANAGER'

Эффективный:

SELECT JOB, AVG(SAL) FROM EMPWHERE JOB = 'PRESIDENT' OR JOB = 'MANAGER' GROUP BY JOB

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

35. Использование алиасов, алиасов - навыки применения крупномасштабных баз данных, то есть имя таблицы и имя столбца в запросе алиасятся на букву, а скорость запроса в 1,5 раза выше, чем при построении таблицы подключения.

36. Избегайте взаимоблокировок, всегда обращайтесь к одной и той же таблице в ваших хранимых процедурах и триггерах в одном и том же порядке; транзакции должны быть максимально сокращены, а объем данных, задействованных в транзакции, должен быть сведен к минимуму; никогда не ждите ввода данных пользователем в сделка.

37. Избегайте использования временных таблиц.Если нет необходимости, вам следует стараться избегать использования временных таблиц.Вместо этого вы можете использовать табличные переменные. Большую часть времени (99%) табличные переменные находятся в памяти и поэтому работают быстрее, чем временные таблицы, которые находятся в базе данных TempDb, поэтому операции с временными таблицами требуют взаимодействия между базами данных, что, естественно, медленнее.

38. Лучше не использовать триггеры:

  • Инициирование триггера и выполнение события триггера само по себе является ресурсоемким процессом;
  • Если вы можете использовать ограничения для достижения цели, постарайтесь не использовать триггеры;
  • Не используйте один и тот же триггер для разных событий триггера (вставка, обновление и удаление);
  • Не используйте транзакционный код в триггерах.

39. Правила создания индекса:

  • Первичный ключ и внешний ключ таблицы должны иметь индекс;
  • Таблицы с более чем 300 данными должны иметь индексы;
  • Таблицы, которые часто объединяются с другими таблицами, должны быть проиндексированы в поле соединения;
  • Поля, которые часто появляются в предложении WHERE, особенно в больших таблицах, должны быть проиндексированы;
  • Индексы должны быть построены на высокоселективных полях;
  • Индексы должны быть построены на небольших полях.Для больших текстовых полей или даже очень длинных полей не создавайте индексы;
  • Создание составного индекса требует тщательного анализа, и попробуйте рассмотреть возможность замены его индексом с одним полем;
  • Правильно выбрать поле основного столбца в составном индексе, которое обычно является полем с лучшей избирательностью;
  • Часто ли несколько полей составного индекса объединяются вместе в предложении WHERE? Мало ли запросов с одним полем или нет? Если это так, вы можете построить составной индекс, в противном случае рассмотрите индекс с одним полем;
  • Если поля, содержащиеся в составном индексе, часто появляются по отдельности в предложении WHERE, он разбивается на несколько индексов с одним полем;
  • Если составной индекс содержит более 3 полей, то тщательно обдумайте его необходимость и рассмотрите возможность сокращения составных полей;
  • Если для этих полей есть как индекс с одним полем, так и составной индекс, составной индекс обычно можно удалить;
  • Таблицы с частыми операциями с данными не создают слишком много индексов;
  • Удалите бесполезные индексы, чтобы избежать негативного влияния на план выполнения;
  • Каждый индекс, установленный для таблицы, увеличивает нагрузку на хранилище, а индекс также увеличивает нагрузку на обработку операций вставки, удаления и обновления. Кроме того, слишком много составных индексов, как правило, не имеют значения в случае индекса с одним полем; наоборот, это снизит производительность добавления и удаления данных, особенно для часто обновляемых таблиц, негативное влияние более серьезное. .
  • Старайтесь не индексировать поле в базе данных, содержащее много повторяющихся значений.

40. Резюме по оптимизации запросов MySQL:

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

Производительность всегда будет меняться с течением времени, избегайте использования count(*) для всей таблицы, это может заблокировать всю таблицу, сохраняйте согласованность запроса, чтобы последующие аналогичные запросы могли использовать кеш запросов, используйте GROUP BY вместо DISTINCT, где это уместно, используйте индексированные столбцы в предложениях WHERE, GROUP BY и ORDER BY, сохраняйте простоту индексов и не включайте один и тот же столбец в несколько индексов.

Иногда MySQL будет использовать неправильный индекс, в этом случае используйте USE INDEX, проверьте проблему использования SQL_MODE=STRICT, для полей индекса с менее чем 5 записями используйте LIMIT вместо OR, когда UNION.

Чтобы избежать SELECT перед обновлением, используйте INSERT ON DUPLICATE KEY или INSERT IGNORE; не используйте UPDATE для достижения, не используйте MAX; используйте индексные поля и предложение ORDER BY LIMIT M, N может фактически замедлить запрос. Используйте экономно, используйте UNION вместо подзапросов в предложении WHERE, перезапустите MySQL, не забудьте прогреть базу данных, чтобы данные находились в памяти, и выполняйте запросы быстро, рассмотрите постоянные соединения вместо нескольких соединений, чтобы уменьшить накладные расходы.

Сравнительные запросы, в том числе с использованием нагрузки на сервер, иногда простой запрос может влиять на другие запросы, при увеличении нагрузки на сервер используйте SHOW PROCESSLIST, чтобы увидеть медленные и проблемные запросы, проверено на зеркальных данных, созданных в среде разработки Все подозрительные запросы .

41. Процесс резервного копирования MySQL:

  • Бэкап со вторичного сервера репликации;
  • Остановить репликацию во время резервного копирования, чтобы избежать несоответствий в зависимостях данных и ограничений внешнего ключа;
  • Полностью остановить MySQL и выполнить резервное копирование из файла базы данных;
  • Если для резервного копирования используется дамп MySQL, также сделайте резервную копию двоичных файлов журнала — убедитесь, что репликация не прерывается;
  • Не доверяйте моментальным снимкам LVM, это может привести к несогласованности данных, что вызовет у вас проблемы в будущем;
  • Чтобы упростить восстановление одной таблицы, экспортируйте данные по таблицам, если данные изолированы от других таблиц.
  • Используйте –opt при использовании mysqldump;
  • Проверяйте и оптимизируйте таблицы перед резервным копированием;
  • Для более быстрого импорта временно отключите ограничения внешнего ключа при импорте. ;
  • Для более быстрого импорта временно отключите определение уникальности при импорте;
  • Рассчитывайте размеры базы данных, таблиц и индексов после каждого резервного копирования, чтобы лучше отслеживать рост размера данных;
  • Отслеживайте ошибки и задержки экземпляра репликации с помощью сценариев автоматического планирования;
  • Регулярно делайте резервные копии.

42. Буфер запросов не обрабатывает пробелы автоматически, поэтому при написании операторов SQL следует свести к минимуму использование пробелов, особенно пробелов в начале и конце SQL (поскольку буфер запросов не перехватывает автоматически пробелы в начале и конце) .

43. Легко ли участнику использовать mid в качестве стандарта для запросов к подтаблицам? В общих бизнес-требованиях имя пользователя в основном является основой запроса.Обычно имя пользователя должно быть хэшем по модулю для разделения таблиц.

Для секционирования таблицы это делает функция секционирования MySQL, которая является прозрачной для кода; кажется неразумным реализовывать ее на уровне кода.

44. Мы должны установить идентификатор в качестве первичного ключа для каждой таблицы в базе данных, и лучше всего использовать тип INT (рекомендуется UNSIGNED), и установить автоматически добавляемый флаг AUTO_INCREMENT.

45. Установите SET NOCOUNT ON в начале всех хранимых процедур и триггеров и установите SET NOCOUNT OFF в конце. Нет необходимости отправлять клиенту сообщение DONE_IN_PROC после выполнения каждого оператора хранимых процедур и триггеров.

46. ​​Запросы MySQL могут включать высокоскоростное кэширование запросов. Это один из эффективных методов оптимизации MySQL для повышения производительности базы данных. Когда один и тот же запрос выполняется несколько раз, гораздо быстрее извлечь данные из кэша и вернуть данные непосредственно из базы данных.

47. Запрос EXPLAIN SELECT используется для отслеживания эффекта просмотра:

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

48. Используйте LIMIT 1, когда есть только одна строка данных:

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

В этом случае добавление LIMIT 1 может повысить производительность. Таким образом, механизм базы данных MySQL прекратит поиск после того, как часть данных будет найдена, вместо того, чтобы продолжать поиск следующей соответствующей записи.

49. Выберите подходящий механизм хранения для таблицы:

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

  • **InnoDB:** Обработка транзакций и согласованность данных требуются в параллельных условиях. В дополнение к вставкам и запросам включены многие обновления и удаления. (InnoDB эффективно уменьшает блокировку, вызванную удалением и обновлением).

    Для таблиц типа InnoDB, которые поддерживают транзакции, основная причина влияния на скорость заключается в том, что параметр AUTOCOMMIT по умолчанию включен, и программа явно не вызывает BEGIN для запуска транзакции, что приводит к автоматической отправке каждой вставленной записи, что серьезно влияет на скорость. Вы можете вызвать begin перед выполнением SQL, и несколько SQL образуют одно целое (даже если включена автоматическая фиксация), что значительно повысит производительность.

50. Оптимизируйте тип данных таблицы и выберите подходящий тип данных:

**Принцип:** Чем меньше, тем лучше, простота — это хорошо, все поля должны иметь значения по умолчанию, старайтесь избегать NULL.

Например, при проектировании таблиц базы данных используйте меньшие целочисленные типы, насколько это возможно, чтобы занимать меньше места на диске. (mediumint более подходит, чем int)

Например, поля времени: datetime и timestamp. datetime занимает 8 байт, timestamp занимает 4 байта, то есть только половину. Диапазон, представленный отметкой времени, составляет 1970-2037, что подходит для времени обновления.

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

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

Например: при определении поля почтового индекса, если для него задано значение CHAR(255), это, очевидно, добавляет ненужное пространство в базу данных. Даже использование типа VARCHAR является излишним, поскольку CHAR(6) прекрасно справляется со своей задачей.

Также, если возможно, мы должны использовать MEDIUMINT вместо BIGIN для определения целочисленных полей, и мы должны попытаться установить для поля значение NOT NULL, чтобы базе данных не приходилось сравнивать значения NULL при выполнении запросов в будущем.

Для некоторых текстовых полей, таких как «провинция» или «пол», мы можем определить их как тип ENUM. Потому что в MySQL типы ENUM обрабатываются как числовые данные, а числовые данные обрабатываются намного быстрее, чем текстовые типы. Таким образом, мы можем снова улучшить производительность базы данных.

51. Строковый тип данных: char, varchar, text Выберите разницу.

52. Любая операция над столбцом приведет к сканированию таблицы, включая функции базы данных, расчетные выражения и т. д. При запросе переместите операцию как можно правее знака равенства.

Категории