Оптимизация запросов данных MySQL на уровне миллионов

---

Методы обработки миллионов данных и повышения скорости запросов:

1. Вы должны стараться избегать использования оператора != или в предложении where, иначе движок откажется от использования индекса и выполнит полное сканирование таблицы.

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

3. Вы должны стараться избегать определения нулевого значения поля в предложении where, иначе движок откажется от использования индекса и выполнит полное сканирование таблицы, например: выберите идентификатор из t, где число равно нулю Вы можете установить значение по умолчанию 0 для num, убедиться, что в столбце num в таблице нет нулевых значений, а затем выполнить запрос следующим образом: выберите идентификатор из t, где num=0

4. Вы должны стараться избегать использования или объединения условий в предложении where, иначе движок откажется от использования индекса и выполнит полное сканирование таблицы, например: выберите идентификатор из t, где num=10 или num=20 Вы можете запросить так: выберите идентификатор из t, где num=10 союз всех выберите идентификатор из t, где num=20

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

6. In и not in также следует использовать с осторожностью, иначе это приведет к полному сканированию таблицы, например: выберите идентификатор из t, где число в (1,2,3) Для последовательных чисел используйте «между» вместо «внутри»: выберите идентификатор из t, где число от 1 до 3

7. Следует максимально избегать операций с выражениями над полями в предложениях where, что заставит движок отказаться от использования индексов и выполнить полное сканирование таблицы. как: выберите идентификатор из t, где число/2 = 100 Следует изменить на: выберите идентификатор из t, где num=100*2

8. Вы должны стараться избегать функциональных операций над полями в предложении where, что заставит движок отказаться от использования индексов и выполнить полное сканирование таблицы. как: выберите идентификатор из t, где substring(name,1,3)='abc' – идентификатор, имя которого начинается с abc выберите идентификатор из t, где datediff(day,createddate,'2005-11-30')=0–'2005-11-30' сгенерированный идентификатор Следует изменить на: выберите идентификатор из t, где имя типа «abc%» выберите идентификатор из t, где createdate>='2005-11-30' и createdate

9. Не выполняйте функции, арифметические операции или другие операции с выражениями слева от «=» в предложении where, иначе индекс может использоваться неправильно.

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

11. Не пишите бессмысленные запросы, такие как создание пустой структуры таблицы: выберите col1, col2 в #t из t, где 1=0 Этот вид кода не будет возвращать какой-либо набор результатов, но будет потреблять системные ресурсы, его следует изменить на это: создать таблицу #t(…)

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

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

14. Чем больше индексов, тем лучше, индекс, безусловно, может повысить эффективность соответствующего выбора, но он также снижает эффективность вставки и обновления, поскольку индекс может быть перестроен во время вставки или обновления, поэтому необходимо тщательно продумать, как строить индекс. считается, в зависимости от конкретных Зависит. Желательно, чтобы количество индексов в таблице не превышало 6. Если индексов слишком много, следует подумать, не нужно ли строить индексы по некоторым редко используемым столбцам.

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

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

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

18. Не используйте select * from t где угодно, вместо этого используйте конкретный список полей*, не возвращайте поля, которые не используются.

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

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

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

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

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

25. Старайтесь избегать использования курсора, потому что эффективность курсора низкая, если данные, обрабатываемые курсором, превышают 10 000 строк, вам следует подумать о перезаписи.

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

27. Как и временные таблицы, курсоры можно использовать. Использование курсоров FAST_FORWARD для небольших наборов данных часто предпочтительнее других методов построчной обработки, особенно если для получения требуемых данных необходимо обратиться к нескольким таблицам. Подпрограммы, включающие в результирующий набор «итогов», обычно выполняются быстрее, чем с использованием курсоров. Если позволяет время разработки, попробуйте методы на основе курсора и на основе набора, чтобы увидеть, какой из них работает лучше.

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

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

30. Старайтесь избегать операций с крупными транзакциями, чтобы улучшить параллелизм системы.

---

Причины низкой скорости запросов:

1. Индекс отсутствует или не используется (это самая распространенная проблема медленных запросов, и это недостаток программирования)

2. Пропускная способность ввода-вывода мала, что создает эффект узкого места.

3. Отсутствие создания вычисляемого столбца приводит к неоптимизированному запросу.

4. Недостаточно места

5. медленная сеть

6. Объем запрашиваемых данных слишком велик (можно использовать несколько запросов, а другие методы могут уменьшить объем данных)

7. Блокировка или взаимоблокировка (это также самая распространенная проблема медленных запросов и недостаток дизайна программы)

8. sp_lock, sp_who, активное представление пользователя, причина в том, что чтение и запись конкурируют за ресурсы.

9. Были возвращены ненужные строки и столбцы

10. Оператор запроса не является хорошим и не оптимизированным

---

Запрос можно оптимизировать следующими методами

1. Разместите данные, журналы и индексы на разных устройствах ввода-вывода, чтобы увеличить скорость чтения.В прошлом Tempdb следовало размещать на RAID0, но SQL2000 не поддерживает это. Чем больше объем данных (размер), тем важнее улучшить ввод-вывод.

2. Разделить таблицу по вертикали и по горизонтали, чтобы уменьшить размер таблицы (sp_spaceuse)

3. обновить оборудование

4. В соответствии с условиями запроса создавайте индексы, оптимизируйте индексы, оптимизируйте методы доступа и ограничьте объем данных в результирующем наборе. Обратите внимание, что коэффициент заполнения подходит (предпочтительно использовать значение по умолчанию, равное 0). Индекс должен быть как можно меньше. Используйте столбец с небольшим количеством байтов для построения индекса (см. создание индекса). Не создавайте один индекс для поля с ограниченным числом значений, например гендерное поле.

5. увеличить скорость интернета;

6. Расширьте память сервера, Windows 2000 и SQL Server 2000 могут поддерживать 4-8G памяти. Настройка виртуальной памяти. Размер виртуальной памяти следует настраивать в зависимости от служб, одновременно работающих на компьютере. При работе с Microsoft SQL Server® 2000 рассмотрите возможность установки размера виртуальной памяти в 1,5 раза больше физической памяти, установленной на компьютере. Если вы дополнительно установили функцию полнотекстового поиска и планируете запустить службу поиска Microsoft для полнотекстового индексирования и запросов, рассмотрите следующие варианты: Настройте размер виртуальной памяти так, чтобы он как минимум в 3 раза превышал размер физической памяти, установленной на компьютере. Настройте параметр конфигурации сервера SQL Server max server memory так, чтобы он в 1,5 раза превышал объем физической памяти (половина параметра размера виртуальной памяти).

7. Увеличьте количество ЦП сервера, но следует понимать, что параллельная обработка последовательной обработки требует больше ресурсов, таких как память. Использование параллельных или последовательных процессов определяется автоматической оценкой MsSQL. Одна задача разбивается на несколько задач, которые могут выполняться на процессоре. Например, предложения сортировки, объединения, сканирования и GROUP BY отложенных запросов выполняются одновременно.SQL Server определяет оптимальный уровень параллелизма в соответствии с нагрузкой на систему.Сложные запросы, потребляющие много ресурсов ЦП, больше всего подходят для параллельной обработки . Но операции обновления UPDATE, INSERT и DELETE не могут выполняться параллельно.

8. Если вы используете like для запроса, простого использования индекса недостаточно, но полнотекстовое индексирование занимает место. как 'a%' использует индекс как '%a' не использует индекс ВАРЧАР. Создайте полнотекстовый индекс для полей с длинными значениями.

9. Разделение серверов БД и серверов приложений, разделение OLTP и OLAP

10. Распределенные секционированные представления можно использовать для реализации федераций серверов баз данных. Федерация — это группа отдельно управляемых серверов, но они взаимодействуют друг с другом, чтобы разделить вычислительную нагрузку системы. Этот механизм разделения данных для формирования комплекса серверов баз данных позволяет расширять группу серверов для поддержки потребностей в обработке больших многоуровневых веб-сайтов. Дополнительные сведения см. в разделе Проектирование серверов объединенных баз данных. (Обратитесь к файлу справки SQL «Разделенные представления»):

    1. Перед реализацией секционированного представления таблица должна быть секционирована по горизонтали.

    2. После создания таблицы элементов определите распределенное секционированное представление на каждом рядовом сервере, и каждое представление будет иметь одно и то же имя. Таким образом, запросы, ссылающиеся на имя распределенного секционированного представления, могут выполняться на любом рядовом сервере. Система работает так, как если бы на каждом сервере-члене была копия исходной таблицы, но на самом деле на каждом сервере есть только одна таблица-член и одно распределенное секционированное представление. Расположение данных прозрачно для приложения.

11. Перестроить индексы DBCC REINDEX, DBCC INDEXDEFRAG, сжать данные и журнал DBCC SHRINKDB, DBCC SHRINKFILE Настроить автоматическое сжатие журнала Для больших баз данных не настраивайте автоматический рост базы данных, это снизит производительность сервера. Большое внимание уделяется написанию T-sql.Общие моменты перечислены ниже: Во-первых, процесс обработки планов запросов СУБД выглядит следующим образом:

    1. Лексическая и грамматическая проверка предложений запроса www.2cto.com

    2. Отправьте оператор оптимизатору запросов СУБД.

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

    4. Создание планов запросов из предварительно скомпилированных модулей

    5. Затем отправьте его в систему для обработки в нужное время.

    6. Наконец, верните результат выполнения пользователю.Далее взгляните на структуру хранения данных SQL SERVER: размер страницы 8K (8060) байт, а 8 страниц - это экстент, который хранится в соответствии с B -дерево.

12. Разница между фиксацией и откатом Откат: Откат всего. Зафиксировать: зафиксировать текущую вещь. Нет необходимости писать что-то в динамическом SQL. Если вы хотите написать что-то, пожалуйста, напишите их снаружи, например: begin tran exec(@s) commit trans или напишите динамический SQL как функции или сохраненный процедуры.

13. Используйте предложение Where в операторе запроса Select, чтобы ограничить количество возвращаемых строк, чтобы избежать сканирования таблицы.Если возвращаются ненужные данные, ресурсы ввода-вывода сервера тратятся впустую, что увеличивает нагрузку на сеть и снижает производительность. Если таблица очень большая, заблокируйте таблицу во время сканирования таблицы, запретив другим объединениям доступ к таблице, последствия будут серьезными.

14. Оператор комментария SQL не влияет на выполнение

15. Не используйте курсоры как можно больше, это потребляет много ресурсов. Если требуется выполнение построчно, попробуйте использовать методы без курсора, такие как цикл на стороне клиента, использование временных таблиц, табличных переменных, использование подзапросов, использование операторов Case и т. д. Курсор можно классифицировать по поддерживаемым параметрам выборки: «Только вперед» Строки должны извлекаться в порядке от первой до последней строки. FETCH NEXT — единственная разрешенная операция выборки по умолчанию. Возможность прокрутки Любая строка может быть выбрана случайным образом в любом месте курсора. Технология Cursor стала очень мощной в SQL2000, и ее целью является поддержка циклов. Существует четыре варианта параллелизма READ_ONLY: обновления с помощью позиционирования курсора не допускаются, а строки, составляющие результирующий набор, не блокируются. ОПТИМИСТИКА В ЦЕННОСТЯХ: Оптимистичный контроль параллелизма — стандартная часть теории управления транзакциями. Оптимистический контроль параллелизма используется, когда существует лишь небольшая вероятность того, что второй пользователь обновит строку в интервале между открытием курсора и обновлением строки. Когда курсор открывается с этой опцией, никакие блокировки не контролируют строки в нем, что помогает максимизировать его вычислительную мощность. Если пользователь пытается изменить строку, текущее значение строки сравнивается со значением, полученным при последней выборке строки. Если какое-либо значение изменяется, сервер знает, что кто-то другой обновил строку, и возвращает ошибку. Если значения совпадают, сервер выполняет модификацию. Выберите этот параметр параллелизма ОПТИМИСТИЧЕСКИЙ С ВЕРСИЯМИ СТРОКИ: этот параметр управления оптимистичным параллелизмом основан на управлении версиями строк. При управлении версиями строк у таблицы должен быть некоторый идентификатор версии, который сервер может использовать, чтобы определить, изменилась ли строка с тех пор, как она была прочитана в курсор. В SQL Server эта возможность обеспечивается типом данных timestamp, который представляет собой двоичное число, представляющее относительный порядок изменений в базе данных. Каждая база данных имеет глобальную текущую временную метку: @@DBTS. Каждый раз, когда строка со столбцом метки времени каким-либо образом изменяется, SQL Server сначала сохраняет текущее значение @@DBTS в столбце метки времени, а затем увеличивает значение @@DBTS. Если в таблице есть столбец метки времени, метка времени записывается на уровне строки. Затем сервер может сравнить текущее значение метки времени строки со значением метки времени, сохраненным при последней выборке, чтобы определить, была ли обновлена ​​строка. Серверу не нужно сравнивать значения всех столбцов, только столбец timestamp. Если приложению требуется оптимистичный параллелизм на основе управления версиями строк для таблиц без столбцов с метками времени, курсоры по умолчанию используют оптимистичный параллелизм на основе числовых значений. SCROLL LOCKS Этот параметр реализует пессимистичный контроль параллелизма. При пессимистическом управлении параллелизмом приложение попытается заблокировать строку базы данных, пока она считывается в результирующий набор курсора. При использовании серверных курсоров на курсор устанавливается блокировка обновления, когда в него считываются строки. Если курсор открывается внутри транзакции, блокировка обновления транзакции удерживается до тех пор, пока транзакция не будет зафиксирована или не будет отменена; блокировка курсора снимается при выборке следующей строки. Если курсор открывается вне транзакции, блокировка снимается при выборке следующей строки. Следовательно, всякий раз, когда пользователю требуется полный пессимистичный контроль параллелизма, курсор следует открывать в транзакции. Блокировка обновления предотвратит получение любой другой задачей блокировки обновления или монопольной блокировки, не позволяя другим задачам обновлять строку. Однако блокировка обновления не блокирует общую блокировку, поэтому она не препятствует чтению строки другими задачами, если вторая задача также не запрашивает чтение с блокировкой обновления. Блокировки прокрутки Эти параметры параллелизма курсора могут генерировать блокировки прокрутки на основе подсказок блокировки, указанных в операторе SELECT определения курсора. Блокировки прокрутки устанавливаются для каждой строки при выборке и удерживаются до следующей выборки или до закрытия курсора, в зависимости от того, что произойдет раньше. При следующей выборке сервер получает блокировки прокрутки для строк в новой выборке и освобождает блокировки прокрутки для строк из предыдущей выборки. Блокировки прокрутки не зависят от блокировок транзакций и могут удерживаться до завершения операции фиксации или отката. Если опция закрытия курсоров при фиксации отключена, оператор COMMIT не закрывает открытые курсоры, а блокировки прокрутки удерживаются после фиксации для обеспечения изоляции извлеченных данных. Тип полученной блокировки прокрутки зависит от параметров параллелизма курсора и подсказки блокировки в операторе SELECT курсора. Подсказка блокировки Только для чтения Оптимистичный Числовой Оптимистичный Управление версиями строк Блокировать Без блокировки Не заблокировано Не заблокировано Не заблокировано Обновление NOLOCK Не заблокировано Не заблокировано Не заблокировано Не заблокировано обновления *Указание подсказки NOLOCK сделает таблицу, для которой указана подсказка, доступной только для чтения внутри курсора.

16. Используйте Profiler для отслеживания запросов, получения времени, необходимого для запроса, выяснения, где SQL неверен; используйте Index Optimizer для оптимизации индексов

17. Обратите внимание на разницу между UNion и UNion all. СОЮЗ все хорошо

18. Будьте осторожны при использовании DISTINCT, не используйте его без необходимости, это сделает запрос медленнее, как UNION. Повторяющиеся записи не проблема в запросе

19. Не возвращайте ненужные строки и столбцы при запросе

20. Используйте хранимую процедуру sp_configure «ограничение стоимости регулятора запросов» или SET QUERY_GOVERNOR_COST_LIMIT, чтобы ограничить ресурсы, потребляемые запросами. Когда ресурсы, потребляемые оценочным запросом, превышают лимит, сервер автоматически отменяет запрос, уничтожая его до запроса. SET LOCKTIME устанавливает время блокировки

21. Используйте select top 100 / 10 Percent, чтобы ограничить количество строк, возвращаемых пользователем, или SET ROWCOUNT, чтобы ограничить количество строк операций.

22. До SQL 2000 вообще не используйте следующие слова "IS NULL", "", "!=", "!>", "!" по-прежнему нельзя оптимизировать, индекс не используется.

23. Используя Query Analyzer, просмотрите план запроса оператора SQL и оцените, является ли анализ оптимизированным SQL. Как правило, 20% кода занимают 80% ресурсов, и наша оптимизация сосредоточена на этих медленных местах.

24. Если вы обнаружите, что запрос не проходит через индекс при использовании IN или OR и т. д., используйте оператор display для указания индекса: SELECT * FROM PersonMember (INDEX = IX_Title) WHERE processid IN ('мужской', 'женский' )

25. Результаты, которые необходимо запросить, предварительно вычисляются и помещаются в таблицу, а затем при запросе выполняется SELECT. Это наиболее важное средство перед SQL7.0. Например, расчет стационарных расходов больницы.

26. MIN() и MAX() могут использовать соответствующий индекс

27. В базе данных действует принцип, что чем ближе код к данным, тем лучше, поэтому приоритетом является выбор Default, за которым следуют Rules, Triggers, Constraint (ограничения, такие как внешнее здоровье, main Health CheckUNIQUE..., максимальное длина типов данных и т. д. все ограничения), Процедура, Таким образом, работа по техническому обслуживанию не только небольшая, но и качество программы высокое, а скорость выполнения высокая.

28. Если вы хотите вставить большое двоичное значение в столбец Image, используйте хранимую процедуру, никогда не используйте встроенный INsert для вставки (я не знаю, есть ли JAVA). Потому что таким образом приложение сначала преобразует двоичное значение в строку (в два раза больше его размера), а сервер преобразует его в двоичное значение после получения символа.В хранимой процедуре нет таких действий: Метод: Создать процедуру p_insert как вставку в table( Fimage) values ​​(@image), вызывайте эту хранимую процедуру на переднем плане для передачи двоичных параметров, поэтому скорость обработки значительно повышается.

29. В какой-то момент Between быстрее, чем IN, Between быстрее находит диапазоны на основе индекса. Разницу можно увидеть с помощью оптимизатора запросов. выберите * из китайского резюме, где заголовок ('мужской', 'женский') Выберите * из китайского резюме, где между «мужской» и «женский» одинаковые. Поскольку in будет сравниваться много раз, иногда это будет медленнее.

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

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

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

33. Используйте как можно меньше просмотров, это неэффективно. Операция над представлением медленнее, чем прямая операция над таблицей, вы можете использовать хранимую процедуру для ее замены. В частности, не используйте вложенные представления. Вложенные представления усложняют поиск исходного материала. Давайте посмотрим на сущность представления: это оптимизированный SQL, хранящийся на сервере, который создал план запроса. При извлечении данных для одной таблицы не используйте представления, указывающие на несколько таблиц, извлекайте непосредственно из таблицы или читайте только представление, содержащее эту таблицу, иначе будут добавлены ненужные накладные расходы и запрос будет нарушен. запрос представления, MsSQL увеличивает функцию индекса представления.

34. Не используйте DISTINCT и ORDER BY без необходимости, вместо этого эти действия можно выполнить на стороне клиента. Они добавляют дополнительные накладные расходы. Это то же самое, что и UNION и UNION ALL.

SELECT   top   20   ad.companyname,comid,position,ad.referenceid,worklocation,   convert(varchar(10),ad.postDate,120)   as   postDate1,workyear,degreedescription   FROM   jobcn_query.dbo.COMPANYAD_query   ad   where   referenceID   in('JCNAD00329667','JCNAD132168','JCNAD00337748','JCNAD00338345','JCNAD00333138','JCNAD00303570',   'JCNAD00303569','JCNAD00303568','JCNAD00306698','JCNAD00231935','JCNAD00231933','JCNAD00254567',   'JCNAD00254585','JCNAD00254608','JCNAD00254607','JCNAD00258524','JCNAD00332133','JCNAD00268618',   'JCNAD00279196','JCNAD00268613')   order   by   postdate   desc

35. В списке значений после IN самое частое значение поместите вверху, а наименее частое значение в конце, уменьшив количество суждений

36. При использовании SELECT INTO он блокирует системные таблицы (sysobjects, sysindexes и т. д.), блокируя доступ к другим соединениям. Используйте явный оператор при создании временной таблицы вместо

select   INTO.   drop   table   t_lxh   begin   tran   select   *   into   t_lxh   from   chineseresume   where   name   =   'XYZ'  

--commit в другом соединении

SELECT   *   from   sysobjects

Вы можете видеть, что SELECT INTO заблокирует системную таблицу, а Create table также заблокирует системную таблицу (будь то временная таблица или системная таблица). Поэтому никогда не используйте его внутри вещей! ! ! В этом случае, если часто используется временная таблица, используйте реальную таблицу или переменную временной таблицы.

37. Как правило, лишние строки можно удалить перед предложениями GROUP BY HAVING, поэтому старайтесь не использовать их для удаления строк. Их порядок выполнения должен быть следующим: предложение Where команды select выбирает все подходящие строки, Group By используется для группировки статистических строк, а предложение Have используется для устранения избыточных групп. Таким образом, группировка по наличию невелика, а запрос выполняется быстро. Группировка и наличие больших строк данных потребляют ресурсы. Если целью Group BY не является вычисление, а только группировка, то использование Distinct быстрее.

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

39. Используйте меньше временных таблиц и старайтесь заменить их наборами результатов и переменными табличного типа. Табличные переменные лучше, чем временные таблицы.

40. В SQL2000 вычисляемые поля могут быть проиндексированы, при этом должны быть соблюдены следующие условия:

    1. Выражение вычисляемого поля является детерминированным
    2. Нельзя использовать в типах данных TEXT, Ntext, Image.
    3. Должны быть настроены следующие параметры: ANSI_NULLS = ON, ANSI_PADDINGS = ON, …….

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

42. Не используйте одну и ту же функцию снова и снова в одном предложении, тратьте ресурсы, поместите результат в переменную и вызовите ее быстрее

43. Эффективность SELECT COUNT(*) низкая, постарайтесь максимально изменить его метод записи, в то время как EXISTS работает быстро.В то же время обратите внимание на разницу: возвращаемое значение select count(Поле null) из таблицы и выберите количество (поле NOT null) из таблицы отличается от.

44. Когда на сервере достаточно памяти, количество потоков конфигурации = максимальное количество подключений + 5, что может максимизировать эффективность; в противном случае используйте количество потоков конфигурации

45. Доступ к вашим столам в определенном порядке. Если вы сначала заблокируете таблицу A, затем таблицу B, затем заблокируйте их в этом порядке во всех хранимых процедурах. Если вы (непреднамеренно) заблокируете таблицу B, а затем таблицу A в хранимой процедуре, это может привести к взаимоблокировке. Взаимоблокировки трудно обнаружить, если последовательность блокировок не разработана заранее.

46. Отслеживайте нагрузку на соответствующее оборудование с помощью монитора производительности SQL Server и счетчика «Память: сбои страниц / сек».Если значение иногда становится высоким, это указывает на то, что в это время существуют потоки, конкурирующие за память. Если он постоянно высокий, узким местом может быть память. Процесс:

    1. % DPC Time относится к проценту времени, в течение которого процессор тратит отложенный вызов процедуры (DPC) на получение и обслуживание в течение интервала выборки. (DPC выполняется с интервалом более низкого приоритета, чем стандартный интервал). Поскольку DPC выполняется в привилегированном режиме, процент времени DPC составляет долю процента привилегированного времени. Это время считается отдельно и не является частью общего количества интервалов. Это общее количество показывает средний час занятости в процентах от времени экземпляра.

    2. %Processor Time Counter: если значение этого параметра продолжает превышать 95%, это указывает на то, что узким местом является ЦП. Рассмотрите возможность добавления процессора или перехода на более быстрый процессор.

    3. % Привилегированного времени относится к проценту времени не простоя процессора, проведенного в привилегированном режиме. (Привилегированный режим — это режим обработки, предназначенный для компонентов операционной системы и управления аппаратными драйверами. Он обеспечивает прямой доступ к оборудованию и всей памяти. Другой режим — пользовательский режим, который представляет собой ограниченный режим обработки целочисленных разделов. Операционная система преобразует потоки приложений в привилегированный режим для доступа к службам операционной системы). % привилегированного времени включает время обслуживания прерываний и DPC. Высокий коэффициент времени привилегий может быть вызван большим количеством интервалов, создаваемых неисправным устройством. Этот счетчик отображает средний час занятости как часть времени расчета.

    4. % пользовательского времени представляет операции базы данных с интенсивным использованием ЦП, такие как сортировка, выполнение агрегатных функций и т. д. Если значение высокое, рассмотрите возможность увеличения индекса, попробуйте использовать простые соединения таблиц, разделить большие таблицы по горизонтали и другие методы, чтобы уменьшить значение. Физический диск: Текущий счетчик длины очереди диска Значение не должно превышать число дисков в 1,5~2 раза. Для повышения производительности добавьте диски. Счетчик SQLServer:Cache Hit Ratio Чем выше значение, тем лучше. Если он остается ниже 80%, рассмотрите возможность увеличения объема памяти. Обратите внимание, что значение этого параметра было накоплено с момента запуска SQL Server, поэтому по прошествии некоторого времени значение не будет отражать текущее значение системы.

47. Анализ выберите форму emp_name сотрудника, где зарплата > 3000. В этом операторе, если зарплата имеет тип Float, оптимизатор оптимизирует ее как Convert(float, 3000), поскольку 3000 является целым числом, мы должны использовать 3000,0 в программировании вместо ожидания. Пусть СУБД сделать преобразование во время выполнения. Преобразование одних и тех же символьных и целочисленных данных

В этой статье используетсяmdniceнабор текста