Способы оптимизации производительности баз данных

Повышение производительности баз данных: Практические советы

Мы в 1cloud много рассказываем о собственном опыте работы над провайдером виртуальной инфраструктуры и тонкостях организации внутренних процессов. Сегодня мы решили немного поговорить об оптимизации БД.

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

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

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

Взгляните на график ниже. На нем изображены результаты выполнения нагрузочного тестирования приложения, когда число пользователей (синий график), работающих с БД, постепенно увеличивается до 50. Количество запросов (оранжевый), с которыми система может справиться, быстро достигает своего максимума и перестаёт расти, тогда как время ответа (желтый) постепенно увеличивается.

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

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

Используйте индексы

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

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

Однако стоит обратить внимание, что, если ваши таблицы «бомбардируются» методами INSERT, UPDATE и DELETE, к индексации нужно отнестись осторожно – она может привести к ухудшению производительности, так как после проведения указанных выше операций все индексы должны быть изменены.

Более того, когда нужно добавить в таблицу большое количество строк (например более миллиона) сразу, администраторы БД часто сбрасывают индексы для ускорения процесса вставки (после вставки индексы создаются заново). Индексация – это обширная и интересная тема, для ознакомления с которой недостаточно столь краткого описания. Больше информации по этой теме вы найдете здесь.

Не используйте циклы с большим количеством итераций

Представьте ситуацию, когда на вашу БД последовательно приходит 1000 запросов:

for (int i = 0; i @VALUE – VALIDATION

Избегайте коррелирующих подзапросов

Коррелирующим подзапросом называют такой подзапрос, который использует значения родительского запроса. Он выполняется построчно, один раз для каждой строки, возвращённой внешним (родительским) запросом, что снижает скорость работы БД. Вот простой пример коррелирующего подзапроса:

(SELECT CompanyName FROM Company WHERE AS CompanyName

Здесь проблема в том, что внутренний запрос (SELECT CompanyName…) выполняется для каждой строки, которую возвращает внешний запрос (SELECT c.Name…). Чтобы повысить производительность, можно переписать подзапрос через JOIN:

FROM Customer c

LEFT JOIN Company co

ON c.CompanyID = co.CompanyID

Старайтесь не использовать SELECT *

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

Например, что лучше: SELECT * FROM Employees или SELECT FirstName, City, Country FROM Employees?

Если вам действительно нужны все столбцы, укажите каждый в явном виде. Это поможет избежать ошибок и дополнительной настройки БД в будущем. Например, если вы используете INSERT… SELECT. а в исходной таблице появился новый столбец, могут возникнуть ошибки, даже если этот столбец не нужен в конечной таблице:

INSERT INTO Employees SELECT * FROM OldEmployees

Msg 213, Level 16, State 1, Line 1

Insert Error: Column name or number of supplied values does not match table definition.

Во избежание таких ошибок, нужно прописывать каждый столбец:

INSERT INTO Employees (FirstName, City, Country)

SELECT Name, CityName, CountryName

Однако стоит заметить, что есть ситуации, в которых использование SELECT * допустимо. Примером могут служить временные таблицы.

Пользуйтесь временными таблицами с умом

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

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

Допустим, вам нужно сделать выборку с условиями из большой таблицы. Чтобы увеличить производительность БД, стоит перевести свои данные во временную таблицу и выполнить JOIN уже с ней. Временная таблица будет меньше исходной, поэтому объединение произойдёт быстрее.

Не всегда понятно, в чем разница между временными таблицами и подзапросами. Потому приведем пример: представьте таблицу покупателей с миллионами записей, из которой нужно сделать выборку по региону. Один из вариантов реализации – использовать SELECT INTO с последующим объединением во временную таблицу:

SELECT * INTO #Temp FROM Customer WHERE RegionID = 5

SELECT r.RegionName, t.Name FROM Region r JOIN #Temp t ON t.RegionID = r.RegionID

Но вместо временных таблиц можно использовать подзапрос:

SELECT r.RegionName, t.Name FROM Region r

JOIN (SELECT * FROM Customer WHERE RegionID = 5) AS t

ON t.RegionID = r.RegionID

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

SELECT r.RegionName, t.Name FROM Region r

JOIN (SELECT Name, RegionID FROM Customer WHERE RegionID = 5) AS t

ON t.RegionID = r.RegionID

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

Когда работа с временной таблицей закончена, лучше удалить её и освободить ресурсы tempdb, чем ждать, пока произойдет автоматическое удаление (когда ваше соединение с сервером БД закроется):

Используйте EXISTS()

Если необходимо проверить существование записи, лучше использовать оператор EXISTS() вместо COUNT(). Тогда как COUNT() проходит по всей таблице, EXISTS() прекращает работу после нахождения первого совпадения. Этот подход повышает производительность и улучшает читаемость кода:

IF (SELECT COUNT(1) FROM EMPLOYEES WHERE FIRSTNAME LIKE ‘%JOHN%’) > 0

IF EXISTS(SELECT FIRSTNAME FROM EMPLOYEES WHERE FIRSTNAME LIKE ‘%JOHN%’)

Вместо заключения

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

Хотелось бы подвести небольшой итог и повторить ключевые моменты, описанные в статье:

1. Используйте индексы, чтобы ускорить проведение поиска и сортировки.
2. Не используйте циклы с большим количеством итераций для вставки данных – используйте INSERT или UPDATE.
3. Обходите стороной коррелирующие подзапросы.
4. Ограничивайте количество параметров оператора SELECT – указывайте только нужные таблицы.
5. Используйте временные таблицы только как «посредников» для объединения крупных таблиц.
6. Для проверки на наличие записи пользуйтесь оператором EXISTS(), который заканчивает работу после определения первого совпадения.

Если вам интересна тема производительности баз данных, то на Stack Exchange есть обсуждение, в котором собрано большое количество полезных ресурсов, – вам стоит обратить на него внимание.

Еще можно почитать материал, который подготовили специалисты 1cloud, о том, как работают с данными крупные мировые компании.

Источник

7 простых способов улучшить производительность вашей базы данных

Главное меню » Базы данных » 7 простых способов улучшить производительность вашей базы данных

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

Оптимизация запросов

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

Создать оптимальные индексы

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

Приобрести более мощный процессор

Чем лучше ваш процессор, тем быстрее и эффективнее будет ваша база данных. Поэтому, если ваша база данных работает не так, вы должны рассмотреть возможность обновления процессорного модуля более высокого класса; чем мощнее ваш процессор, тем меньше нагрузка при работе с несколькими приложениями и запросами. Кроме того, при оценке производительности ЦП важно отслеживать все аспекты производительности ЦП, в том числе время готовности ЦП (которое может рассказать вам о случаях, когда ваша система пыталась использовать ЦП, но не смогла, поскольку все ресурсы ЦП были слишком заняты или иным образом заняты).

Выделите больше памяти

Подобно тому, как недостаточно мощный процессор может повлиять на эффективность базы данных, так же как и нехватка памяти. В конце концов, когда в базе данных недостаточно памяти для выполнения запрашиваемой работы, производительность базы данных по понятным причинам пойдет на спад. По сути, наличие большего объема памяти поможет повысить эффективность системы и общую производительность. Хороший способ проверить, нужно ли вам больше памяти, – посмотреть, сколько ошибок в вашей системе имеет страница; Если количество сбоев велико (например, в тысячах), это означает, что вашим хостам не хватает (или, возможно, полностью не хватает) доступного пространства памяти. Следовательно,

Кроме того, вы можете рассмотреть вопрос об увеличении объема памяти, используемой MySQL. Мы рекомендуем разрешить ему выделять 70% общей памяти (при условии, что база данных является единственным приложением на этом сервере). Вы можете изменить объем памяти, выделенной для базы данных, используя ключ innodb_buffer_pool_size в файле конфигурации MySQL, my.cnf.

Дефрагментация данных

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

Типы дисков

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

Версия базы данных

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

Резюме

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

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

Среда MySql для Linux включает моделирование данных, разработку, инструменты администрирования параметров SQL, администрирование пользователей, резервное копирование/восстановление и другие функции. Простота использования и все эти функции делают его отличным выбором для управления базами данных MySql. В Linux вы можете установить MySql Workbench различными способами, в том числе через официальные пакеты. Установка официальных репозиториев Oracle, использование репозиториев операционной системы по умолчанию или компиляция пакетов из исходного кода – все это варианты. Итак, давайте кратко рассмотрим метод установки рабочей среды MySQLЧитать далее… >

MySQL – одна из самых популярных баз данных в мире. Независимо от отрасли, MySQL широко используется благодаря своим функциям. Это СУБД с открытым исходным кодом(система управления реляционными базами данных). Данные организованы в таблицы, которые могут быть связаны друг с другом. Он включает SQL для выполнения различных действий с базой данных. В этой статье мы рассмотрим одну из таких функций. Он продемонстрирует, как использовать функцию DATEDIFF в MySQL. DATEDIFF() в SQL Функция DATEDIFF доступна как часть языка запросов SQL. ВЧитать далее… >

MySQL – это СУБД с концепцией нулевых значений. В MySQL используется функция COALESCE, поскольку она возвращает первое ненулевое значение из указанной серии выражений. Если все значения столбца равны нулю, COALESCE также вернет ноль. Он работает аналогично запросу «IS NULL», оба имеют дело с значениями NULL, но разница в них обоих заключается в их поведении, запрос «IS NULL» извлекает значения NULL из таблицы, тогда как функция COALESCE дает первое ненулевое значение, если в таблице присутствует какое-либоЧитать далее… >

Данные в MySQL управляются в базах данных в виде таблиц, которые также состоят из строк и столбцов. Что такое схема? Схема отличается от баз данных, поскольку она не имеет всех привилегий, которые имеет база данных, она имеет только привилегии таблиц, строк и столбцов, которые определяют структуру базы данных. В этой статье мы объясним, что такое схема, как она работает и каковы различия между схемой и базой данных. Что такое схема Схема базы данных – это архитектура базыЧитать далее… >

Mysqli_real_escape_string – это встроенная функция PHP, которая используется для управления нежелательными и опасными символами. В этой статье мы обсудим с помощью примера, что такое функция mysqli_real_escape_string и как ее можно использовать для защиты базы данных. Что такое mysqli_real_escape_string По определению mysqli_real_escape_string() позволяет специальным символам в строке перемещаться в базу данных с помощью запроса SQL, принимая во внимание текущий набор символов установленного соединения. Проще говоря, эта функция позволяет рассматривать специальные символы как часть строки и сохранятьЧитать далее… >

Mysql имеет множество встроенных функций для разных целей, одна из них – функция Substring. Функция substring имеет дело с типом данных «строка» и используется в управлении базой данных для извлечения строки из таблиц. Что это за строка? Строка – это совокупная комбинация символов. Например, «Hello andreyEx» и «KHG1234» – это строки. В этой статье мы поймем, что такое функция Substring и как ее можно использовать в MySQL для управления данными в базах данных. ЧтоЧитать далее… >

Запрос IS NULL в базе данных MySQL показывает данные, которые либо отсутствуют, либо неизвестны DMS. Значение NULL отличается, поскольку оно не имеет значения, оно не равно нулю целому числу или пустому набору. NULL – это состояние, а не значение, если мы сравним значение NULL с любым другим значением NULL, результатом всегда будет NULL, потому что оно само неизвестно. Запрос «IS NULL» используется, когда данные отсутствуют или неизвестны, например, мы составляем список телефонных справочников, если номер телефона какого-либо человекаЧитать далее… >

В этой статье основное внимание уделяется типу данных BIGINT в MySQL и рассматривается, как его можно использовать для хранения целочисленных значений. Мы также узнаем его диапазон, размер хранилища и различные атрибуты, включая подписанный, беззнаковый и нулевое заполнение. BIGINT в MySQL BIGINT – это ab-байтовое или 64-битное целочисленное значение, которое очень полезно для хранения огромных целочисленных значений. BIGINT, как и любой другой целочисленный тип данных MySQL, может быть подписанным или беззнаковым. Тип данных со знакомЧитать далее… >

В этой статье показано, как использовать предложение WHERE в MySQL для фильтрации строк по определенному условию. Используя предложение where, мы можем указать условие поиска для строк, которые возвращают значение true для условия, и выполнять с ними действия. Основное использование Синтаксис предложения WHERE: SELECT column_list FROM TABLE_NAME WHERE condition; Мы начинаем с определения начального действия, которое нужно выполнить; это может быть SELECT, DELETE, UPDATE или любой другой поддерживаемый оператор. Затем мы указываем столбцы и таблицу, над которыми нужно выполнить указанное действие.Читать далее… >

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

В этой статье будет обсуждаться использование запроса IN в MySQL для проверки того, входит ли конкретное значение в набор значений, что полезно при замене набора операторов OR. Базовый синтаксис Оператор IN достаточно прост. Он работает, проверяя, соответствует ли набор указанному значению. Общий синтаксис запроса: SELECT col_names FROM table_name WHERE expression IN (value_sets); В приведенном выше синтаксисе мы указываем выполняемую функцию. Это может быть SELECT, INSERT, DELETE, UPDATE и т. д. Затем мы указываем имя таблицы, из которой нужно выполнить указанное выше действие. Следующая часть –Читать далее… >

Базы данных – это специальное программное обеспечение, которое позволяет нам сохранять данные, которые нам нужно хранить, и обеспечивает легкий доступ с помощью эффективной системы отчетности. В этой статье мы расскажем о MySQL, здесь можно изучить подробнее MySQL. одной из самых популярных сегодня баз данных. MySQL – это СУБД с открытым исходным кодом на базе Oracle (система управления реляционными базами данных), основанная на SQL (языке структурированных запросов). Она работает практически на всех основных платформах, включая Linux,Читать далее… >

Источник