Light-electric.com

IT Журнал
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

Каковы наилучшие методы оптимизации производительности SQL Server?

Я всегда использовал подход к развертыванию базы данных с минимальным набором индексов, а затем добавление/изменение индексов по мере того, как производительность диктует.

Этот подход работает достаточно хорошо. Тем не менее, он все еще не говорит мне, где я мог бы улучшить производительность. Это только говорит мне, где производительность настолько плоха, что пользователи жалуются на это.

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

Итак, не стоит ли искать улучшения производительности, поскольку «преждевременная оптимизация — это корень всего зла»?

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

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

14 ответов

11 Решение Tom H [2008-09-19 19:33:00]

Мой подход заключается в сборе команд против сервера или базы данных в таблицу с использованием SQL Server Profiler. После этого вы можете запросить на основе времени выполнения max и avg, max и avg cpu, а также (также очень важно) количества раз, когда запрос выполнялся.

Так как я пытаюсь поместить весь код доступа к базе данных в хранимые процедуры, мне легко выполнить запросы. Если вы используете встроенный SQL, это может быть сложнее, поскольку изменение значения в запросе сделает его похожим на другой запрос. Вы можете попытаться обойти это, используя оператор LIKE, для ввода одинаковых типов запросов в одни и те же ведра для вычисления агрегатов (max, avg, count).

После того, как у вас есть список «лучших 10» потенциальных проблем, вы можете начать смотреть на них индивидуально, чтобы увидеть, можно ли переработать запрос, индекс может помочь или сделать небольшое изменение архитектуры в порядке. Чтобы придумать топ-10, попробуйте просмотреть данные по-разному: avg * count для общей стоимости в течение периода, max для худшего преступника, просто avg и т.д.

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

11 HLGEM [2008-09-19 21:17:00]

«преждевременная оптимизация — корень всего зла»

Что касается программирования баз данных, я думаю, что эта цитата — глупость. Очень дорого переписать все ваше приложение, потому что ваши разработчики не хотят писать эффективный код в первый раз. Весь код t-sql следует учитывать с точки зрения того, как это повлияет на производительность базы данных во-вторых (целостность данных, конечно, первая). Perfomance должен превзойти все, кроме целостности данных.

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

И нет причины использовать select * вместо указания имен полей. В MSSQL вы можете перетащить эти имена из объектного проводника, чтобы вы не могли сказать мне, что это слишком сложно сделать. Но, указав только нужные вам поля, вы сохраняете сетевые ресурсы и ресурсы сервера баз данных и ресурсы веб-сервера. Так зачем программисту когда-либо брать ленивый вариант select * и беспокоиться об оптимизации позже?

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

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

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

Методы оптимизации запросов к SQL Server — Советы для написания эффективных и быстрых запросов

Инновационный центр — Группа организаций Baba Farid

Автор перевода: Прищепа В.В.

Аннотация — SQL можно использовать для извлечения данных из любых баз данных. Чтобы получить один и тот же результат, можно написать различные SQL запросы. Для наилучшей производительности необходимо использовать лучшие, наиболее быстрее и эффективные запросы. Так что необходимо конфигурировать запросы на основании требований пользователей и решаемых задач. Эта статья раскрывает каким же образом SQL запросы могут быть оптимизированы для лучшей производительности. Оптимизация запросов тема очень глубокая, но мы будем стараться охватить наиболее важные моменты. В этой статье мы не будем сосредотачиваться на глубоком анализе базы данных, а обсудим простые советы по настройке запросов и приемы, которые могут быть применены, чтобы получить немедленный выигрыш в производительности.

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

Читать еще:  Оптимизация нвидиа для игр

II. ОБЗОР ВЫПОЛНЕНИЯ ЗАПРОСОВ С ИСПОЛЬЗОВАНИЕМ СТАТИСТИКИ ЧТЕНИЯ/ЗАПИСИ

Важным параметром является количество логических операций чтения производящихся по запросу. Возможность просматривать этот параметр предусмотрена в SQL Server Management Studio. Для определения числа логических операций чтения, вы можете включить или выключить отображение параметра STATISTICS IO с помощью такого запроса:

SET STATISTICS IO ON

Рассмотрим следующий запрос:

SELECT * FROM tablename

В окне результата SQL Server Management Studio вернулось следующее сообщение: «Table ‘tablename’. Scan count 1, logical reads 33, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0«.

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

III. ОБЩИЕ РЕКОМЕНДАЦИИ ПО ОПТИМИЗАЦИИ ЗАПРОСОВ

Используйте конкретные имена столбцов вместо * в запросе SELECT

Запрос SQL, становится быстрее, если использовать имена столбцов в SELECT вместо ‘*’. Так что нам нужно ограничить запросы с выборкой всех столбцов, выбрав только определенные столбцы из таблицы. Это приводит к выигрышу общей производительности, уменьшению сетевого трафика.

SELECT col_1, col_2, col_3, col_4, subject FROM table_name;

SELECT * FROM table_name.

Используйте альтернативные методы для возврата общего количества строк таблицы вместо COUNT (*)

SELECT COUNT (*) делает полное сканирование таблицы, это может занять много времени для больших таблиц. Если нам нужно узнать количество строк таблицы, мы можем использовать альтернативный способ – системную таблицу sysindexes. В ней присутствует столбец ROWS, содержащий общее количество строк для каждой таблицы в системе. Таким образом, мы можем использовать следующий оператор выбора:

SELECT rows FROM sysindexes
WHERE id = OBJECT_ID (‘table_name’) AND indid 10;

SELECT id, col1, col2 FROM table
WHERE col2 != 10.

Для сравнения строк:

SELECT id, col1, col2 FROM table
WHERE col1 LIKE ‘Nav%’;

SELECT id, col1, col2 FROM table
WHERE SUBSTR(col1,1,3) = ‘Nav’.

Для сравнения чисел в диапазоне:

SELECT Col1, Col2 FROM table
WHERE Col3 BETWEEN MAX (Col3) and MIN (Col3);

SELECT Col1, Col2 FROM table
WHERE Col3 >= MAX (Col3) and Col3 <= MIN (Col3).

Применяйте модифицированное значение колонки в инструкции WHERE только при необходимости.

SELECT id, Col1, Col2 FROM table
WHERE Col2 < 25000;

SELECT id, Col1, Col2 FROM table
WHERE Col2 + 10000 < 35000.

IV. ЕЩЕ НЕСКОЛЬКО СОВЕТОВ ПО ОПТИМИЗАЦИИ ЗАПРОСОВ/ТАБЛИЦ/ХРАНИМЫХ ПРОЦЕДУР

  • Таблица должна иметь минимум один кластеризованный индекс и соответствующее число не кластеризованных индексов;
  • Избегайте использования триггеров, если это возможно. Включите логику триггера в хранимую процедуру;
  • Таблица должна иметь ключевое поле;
  • Старайтесь использовать переменные таблицы вместо временных. Переменные занимают меньше системных ресурсов и ресурсов логов;
  • Избегайте использования VIEW, постарайтесь заменить их таблицами;
  • Избегайте инструкции DISTINCT, используйте ее только если это действительно необходимо;
  • Используйте TOP в иснтрукции SELECT, если необходимо выбрать некоторое количество строк в начале таблицы;
  • Оформите повторяющийся код в пользовательскую процедуру. Это поможет улучшить производительность, ускорить вашу работу, уменьшить сетевой трафик;
  • Использование TRUNCATE вместо DELETE позволит ускорить удаление строк из таблицы, потому что удаление происходит без записи информации в лог-файл;
  • Избегайте использования курсоров, если это возможно, они сильно замедляют производительность;
  • Когда разрабатывается запрос с подзапросами:
    • Используйте коррелированный подзапрос только тогда, когда возвращаемый результат будет относительно небольшим и/или другие критерии быстродействия подзапроса будут эффективными;
    • Используйте не коррелированные подзапросы при работе с большими таблицами, из которых ожидается большой результат и/или подзапрос имеет низкие показатели эффективности;
    • Убедитесь в том, что несколько подзапросов расположены в наиболее эффективном порядке;
    • Переписывание подзапроса с JOIN иногда может повысить эффективность;
  • Для хранения символьных и строковых данных используйте char/varchar вместо nchar/nvarchar, если нет необходимости в использовании UNICODE. В первом случае для хранения символов используется один байт, во втором – два;
  • Можно попытаться использовать инструкцию RETURN для возвращения целочисленного значения вместо того, чтобы это значение было частью результирующего набора данных;
  • Очистите систему от неиспользуемых индексов, они занимают место на диске и замедляют операции DML;
  • Создавайте индексы для целочисленных полей, это способствует меньшему объему индекса на диске, меньшему количеству операций чтения при использовании индекса;
  • Если часто используется объединение одних и тех же таблиц, то стоит создать индекс для объединяемых столбцов.

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

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

М55144А: Настройка производительности и оптимизация SQL Server 2014

SQL Server 2014

Этот курс в нашем Центре
успешно закончили
6352 человек!

М55144А: SQL Server 2014 Performance Tuning and Optimization

SQL Server – комплексная платформа управления данными и бизнес-анализа, предлагающая разработчикам и пользователям широкий набор возможностей по созданию решений с высоким уровнем производительности, надежности и безопасности. Возможности SQL Server 2014 позволяют анализировать большие объемы информации, моделируя и отслеживая результаты принятия тех или иных решений.

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

Курс предназначен для разработчиков и администраторов баз данных.

По окончании курса Вы будете уметь:

  • Настраивать механизмы хранения и доступа к данным для максимальной производительности
  • Использовать инструменты мониторинга и оптимизации производительности баз данных
  • Ускорять работу запросов и операций над данными

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

Продолжительность курса — 40 ак. ч.

Преподаватели курса

Отзывы о курсе

Cлушатель: Чирков Алексей Викторович

Cлушатель: Иванов Андрей Николаевич

Предварительная подготовка

Требуемая подготовка: Успешное окончание курса Курс 20461D: Создание запросов в Microsoft SQL Server 2014 или эквивалентная подготовка.

Требуемая подготовка: Успешное окончание курса Курс 20464D: Разработка баз данных на базе Microsoft SQL Server 2014 или эквивалентная подготовка.

Требуемая подготовка: «Английский язык. Уровень 2. Elementary, часть 2», или эквивалентная подготовка. Определить уровень владения языком Вам поможет тест: http://www.specialist.ru/test/599

Получить консультацию о необходимой предварительной подготовке по курсу Вы можете у наших менеджеров: +7 (495) 232-32-16.

Наличие предварительной подготовки является залогом Вашего успешного обучения. Предварительная подготовка указывается в виде названия других курсов Центра (Обязательная предварительная подготовка). Вам следует прочитать программу указанного курса и самостоятельно оценить, есть ли у Вас знания и опыт, эквивалентные данной программе. Если Вы обладаете знаниями менее 85-90% рекомендуемого курса, то Вы обязательно должны получить предварительную подготовку. Только после этого Вы сможете качественно обучиться на выбранном курсе.

Читать еще:  Как оптимизировать интернет

Рекомендуемые курсы по специальности

Чтобы стать профессионалом, мы рекомендуем Вам вместе с этим курсом изучить:

Настройка производительности SQL – Советы по оптимизации запросов MySQL

Главное меню » Базы данных » База данных MySQL » Настройка производительности SQL – Советы по оптимизации запросов MySQL

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

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

На самом деле не имеет значения, используете ли вы уровень абстракции базы данных (Hibernate, JOOQ, Entity Framework, Sqlalchemy, Django или другие) или пишете нативные SQL-запросы, вы в конечном итоге столкнетесь с проблемой настройки отправляемых запросов. в вашу базу данных.

Создавайте индексы, но делайте это с умом

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

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

Кроме того, основными узкими местами в поиске данных могут быть части GROUP BY и ORDER BY. Потенциальный сбой будет в том, что вы не сможете индексировать их в некоторых случаях, как мы объяснили здесь. Поэтому вам может потребоваться переосмыслить структуру вашего запроса перед созданием индексов, чтобы убедиться, что вы пишете отличные запросы, а также пишете запросы с индексированием.

Как только вы выяснили индексирование для одного запроса, не останавливайтесь на этом. Расширьте свой взгляд и посмотрите на другие важные вопросы в вашем приложении. Чем больше запросов вы просматриваете, тем больше вы будете думать о лучших индексах для создания. Убедитесь, что вы объединяете индексы, когда это возможно, и удаляете индексы, которые больше не нужны. Просмотр области всего приложения всегда будет лучше, чем просмотр области одного запроса.

Тем не менее, наличие большего количества индексов, чем вам нужно, также может иметь неприятные последствия, поскольку они могут замедлить операции записи (такие как операторы INSERT/UPDATE). Поэтому создайте индексы для оптимизации ваших запросов SQL, но делайте это с умом.

Не мешайте указателям

К нам часто обращаются клиенты, которые спрашивают нас «почему база данных не использует мой индекс?». Ну, это отличный вопрос с бесконечными возможными ответами. Но в этой статье мы попытаемся предоставить несколько распространенных вариантов, которые мы часто видим, поэтому, надеюсь, вы найдете их полезными для собственного варианта использования.

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

Рассмотрим этот запрос, который подсчитывает количество хот-догов, купленных в России в 2019 году. На всякий случай, если вам интересно, в 2019 году в России было продано много хот-догов.

Как видите, мы используем функцию YEAR, чтобы получить часть года из столбца purchase_time. Этот вызов функции не позволит базе данных использовать индекс для поиска по столбцу purchase_time, потому что мы проиндексировали значение purchase_time, но не возвращаемое значение YEAR (purchase_time).

Чтобы преодолеть эту проблему и настроить этот запрос SQL, вы можете проиндексировать результат функции, используя Generated Columns, которые доступны начиная с MySQL 5.7.5.

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

Пример №2 – избегать условия OR

Рассмотрим этот запрос, который выбирает количество постов на Facebook, опубликованных после канун Нового года, или опубликованных пользователем по имени Alex.

Индекс для столбцов username и post_time может показаться полезным, но в большинстве случаев база данных не будет его использовать, по крайней мере, не полностью. Причиной будет связь между двумя условиями – оператор OR, который заставляет базу данных извлекать результаты каждой части условия в отдельности.

Альтернативный способ взглянуть на этот запрос может состоять в том, чтобы «разделить» условие OR и «объединить» его с помощью предложения UNION. Эта альтернатива позволит вам индексировать каждое из условий отдельно, поэтому база данных будет использовать индексы для поиска результатов, а затем объединять результаты с предложением UNION.

Обратите внимание, что если вы не возражаете против дублирования записей в наборе результатов, вы также можете использовать UNION ALL (который будет работать лучше, чем UNION DISTINCT по умолчанию).

Пример № 3 – Избегайте сортировки со смешанным порядком

Рассмотрим этот запрос, который выбирает все сообщения из Facebook и сортирует их по имени пользователя в порядке возрастания, а затем по дате публикации в порядке убывания.

MySQL (и многие другие реляционные базы данных) не могут использовать индексы при сортировке со смешанным порядком (как ASC, так и DESC в одном и том же предложении ORDER BY). Это изменилось с выпуском функциональности обращенных индексов и MySQL 8.x.

Так что вы можете сделать, если вы еще не обновились до последней версии MySQL? Во-первых, мы рекомендуем пересмотреть сортировку смешанного порядка. Вам это действительно нужно? Если нет, избегайте этого.

Итак, вы решили, что вам это нужно, или ваш менеджер по продукту сказал: «Мы никак не можем обойтись без него»? Другим вариантом будет использование сгенерированных столбцов (доступно в MySQL 5.7.5+) для создания обращенного столбца и сортировки по этому столбцу вместо исходного. Например, предположим, что вы сортируете по числовому столбцу, вы можете создать сгенерированный столбец с отрицательным числовым значением, соответствующим исходному номеру, и отсортировать по этому новому столбцу в обратном порядке. Таким образом, все столбцы будут иметь одинаковый порядок сортировки в предложении ORDER BY, но сортировка будет происходить так, как это было первоначально определено требованиями вашего продукта.

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

Пример № 4 – Избегайте условий с разными типами столбцов

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

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

Итак, как вы можете настроить этот запрос SQL? У вас есть два варианта для оптимизации этого запроса. Первый – сравнить столбец с константным значением, соответствующим типу столбца, поэтому, если это столбец VARCHAR, сравните его с «5» (с одинарными кавычками), а не с 5 (это числовое сравнение, которое приведет к в скрытом исполнении).

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

Читать еще:  Какие методы оптимизации предлагает программа defrag

Избегайте поиск по LIKE с подстановочными знаками

Рассмотрим этот запрос, который ищет все сообщения в Facebook по имени пользователя, которое содержит строку «Mar», поэтому мы ищем все сообщения, написанные пользователями с именами Mark, Marcus, Almar и т. l.

Наличие подстановочного знака «%» в начале шаблона не позволит базе данных использовать индекс для поиска в этом столбце. Такие поиски могут занять некоторое время ..

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

Другим вариантом будет использование полнотекстовых индексов. Обратите внимание, что эти индексы и синтаксис MATCH… AGAINST не свободны от проблем и имеют некоторые различия по сравнению со знакомыми выражениями LIKE в MySQL.

Заключение

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

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

Дата поста: 01-10-2012

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

1. Оптимизация таблиц.

Необходима, когда было произведено много изменений в таблице: либо удалена большая часть данных, либо много изменений со строками переменной длины — text, varchar, blob. Дело в том, что удалённые записи продолжают поддерживаться в индексном файле, и при последующей вставке новых записей используются позиции старых записей. Чтобы дефрагментировать файл с данными, используюется команда OPTIMIZE.

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

2. Перестройка данных в таблице.

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

3. Тип данных.

Лучше не индексировать поля, имеющие строковый тип, особенно поля типа TEXT. Для таблиц, данные которых часто изменяются, желательно избегать использования полей типа VARCHAR и BLOB, так как данный тип создаёт динамическую длину строки, тем самым увеличивая время доступа к данным. При этом советуют использовать поле VARCHAR вместо TEXT, так как с ним работа происходит быстрее.

4. NOT NULL и поле по умолчанию.

Лучше всего помечать поля как NOT NULL, так как они немного экономят место и исключают лишние проверки. При этом стоит задавать значение полей по умолчанию и новые данные вставлять только в том случае, если они от него отличаются. Это ускорит добавление данных и снизит время на анализ таблиц. И стоит помнить, что типы полей BLOB и TEXT не могут содержать значения по умолчанию.

5. Постоянное соединение с сервером БД.

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

6. Разделение данных.

Длинные не ключевые поля советуют выделить в отдельную таблицу в том случае, если по исходной таблице происходит постоянная выборка данных и которая часто изменяется. Данный метод позволит сократить размер изменяемой части таблицы, что приведёт к сокращению поиска информации.

Особенно это актуально в тех случаях, когда часть информации в таблице предназначена только для чтения, а другая часть — не только для чтения, но и для модификации (не забываем, что при записи информации блокируется вся таблица). Яркий пример — счётчик посещений.

Есть таблица (имя first) с полями id, content, shows. Первое ключевое с auto_increment, второе — текстовое, а третье числовое — считает количество показов. Каждый раз загружая страницу, к последнему полю прибавляется +1. Отделим последнее поле во вторую таблицу. Итак, первая таблица (first) будет с полями id, content, а вторая (second) с полями shows и first_id. Первое поле понятно, второе думаю тоже — отсыл к ключевому полю id из первой таблицы.

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

А выборка будет происходить усложнённым запросом, но одним, двух не нужно:

Стоит помнить, что это не очень актуально для сайтов с малой посещаемостью и малым количеством информации.

7. Имена полей,

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

8. Требовать меньше данных.

При возможности избегать запросов типа:

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

Тут же сделаю добавление о желательности использования LIMIT. Данная команда ограничивает количество строк, возвращаемых запросом. То есть запрос становится «легче»; и производительнее.

Если стоит LIMIT 10, то после получения десяти строк запрос прерывается.

Если в запросе применяется сортировка ORDER BY, то она происходит не по всей таблице, а только по выборке.

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

Если использовать LIMIT 0, то возвращено будет пустое значение (иногда нужно для определения типа поля или просто проверки работы запроса).

9. Ограничить использование DISTINCT.

Эта команда исключает повторяющиеся строки в результате. Команда требует повышенного времени обработки. Лучше всего комбинировать с LIMIT.

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

10. Ограничить использование SELECT для постоянно изменяющихся таблиц.

11. Не забывайте про временные таблицы типа HEAP.

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

12. Поиск по шаблону.

Зависит от размера поля и если уменьшить размер с 400 байтов до 300, то время поиска сократиться на 25%

13. Команда LOAD DATA INFILE

позволяет быстро загружать большой объём данных из текстового файла

14. Хранение изображений в БД нежелательно.

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

15. Максимально число запросов при генерации страницы,

как мне думается, должно быть не более 20 (+- 5 запросов). При этом оно не должно зависеть от переменных параметров.

Ссылка на основную публикацию
Adblock
detector