Системы управления базами данных предоставляют возможность не только хранить и управлять данными, но и исполнять пользовательский код на сервере. Примером могут выступать хранимые процедуры и триггеры. Но при том, что это достаточно популярная практика, одна операция изменения данных может привести к вызову нескольких хранимых процедур по цепочке и приведет к нежелательным блокировкам [1].
Примером может служить каскадное удаление данных из таблиц реляционных баз данных, когда исключение одной строки приводит к удалению связанных записей в таблицах (рис. 1).
Рис. 1. Добавление каскадного удаления строк
В результате расширенная функциональность позволяет пользователю упростить его работу с базами данных, но при неоптимальном использовании может привести к снижению производительности.
Ключевые моменты оптимизации баз данных:
1) Грамотно использовать индексы.
Индексы – это эффективный способ управления получением информации из базы данных. Они ускоряют запросы, помогая искать данные в базе более оптимально, и предоставляют доступ к строкам данных в таблице без полного перебора. Аналогией может служить предметный указатель в книге, который хранит адреса терминов для их поиска [1].
Например, если создать кластерный индекс по первичному ключу, то в базе данных создастся иерархическое хранилище указателей на данные в этой таблице. И при попытке поиска данных будет выполняться поиск по заданному индексу, а не выполняться полный перебор всех строк таблицы (рис. 2).
Рис. 2. Добавление первичного ключа с кластерным индексом
Кроме того, стоит учесть, что использовать индексы в таблицах, где частые INSERT, UPDATE и DELETE, плохая практика. При обновлении таблицы так же приходится и обновлять индексы, что может привести к их фрагментации. Поэтому, если данные требуют частых обновлений, то индексы могут стать плохой практикой, замедляющей работу базы данных [2].
2) Избегать коррелирующих подзапросов.
Основная проблема коррелирующих запросов в том, что они выполняются для каждой строки (рис. 3). Для использования в родительском запросе базе данных приходится построчно выполнять код в подзапросе, что замедляет работу SELECT [3].
Рис. 3. Пример коррелирующего запроса
3) Не использовать SELECT всех столбцов через «*».
Для получения данных лучше использовать конечные имена столбцов, чтобы ограничить выборку и не выгружать ненужные строки. Для грамотного SELECT запроса необходимо указывать названия столбцов явно, а также лучше выбирать то количество строк, которое необходимо в данный момент времени (рис. 4). Даже если требуется выбрать все столбцы таблицы, лучше указать их явно, чтобы при повторном использовании кода не возникало ошибок и проблем с производительностью [1].
Рис. 4. Пример запросов с полным именованием колонок
4) Пользоваться временными таблицами грамотно.
Временные таблицы в основном могут усложнять структуру запроса и требовать большее количество ресурсов. Поэтому, если есть возможность создать запрос без временных таблиц, то лучше их избегать. Временные таблицы являются хорошим решением в хранимых процедурах, если операции с обрабатываемыми данными невозможно выполнить в одном запросе [2].
Например, если требуется сделать JOIN большой таблицы, то более оптимизированным решением будет вынести выборку данных во временную таблицу, и после этого сделать JOIN, так как это будет быстрее и выгоднее по памяти.
Также после завершения работы с временной таблицей ее лучше удалить и освободить ресурсы (рис. 5). Если не удалить ее, то таблица будет существовать до автоматической очистки (после закрытия соединения с сервером БД) [3].
Рис. 5. Пример использования временных таблиц
Таким образом, при использовании данных рекомендаций можно повысить производительность базы данных, не прибегая к изменениям ее структуры.
Список литературы
- 1. Кэмпбелл Лейн, Мейджорс Черити. Базы данных. Инжиниринг надежности. СПб.: Питер, 2020. 304 с.
- 2. Грофф, Джеймс Р., Пол Н., Оппель, Эндрю Дж. SQL: Полное руководство. 3-е изд.: Пер. с англ. – М.: ООО «И.Д. Вильямс», 2015. 960 с.
- 3. John L. Viescas, Douglas J. Steele, Ben G. Clothier Effective SQL: 61 Specific Ways to Write Better SQL. Addison-Wesley Professional, 2016. Т. 352.
Набродова Ирина Николаевна, канд. техн. наук, доцент, ira1978@tsu.tula.ru, Россия, Тула, Тульский государственный университет,
Кузнецов Григорий Алексеевич, магистрант, gregory.kuznetsov1998@gmail.com, Россия, Тула, Тульский государственный университет
IMPROVING DATABASE PERFORMANCE
The purpose of the study is to learn and determine the key points for optimizing their per formance on loaded projects
Key words: database, index, correlation query, stored procedure, trigger, temporary table.
Nabrodova Irina Nikolaevna, candidate of technical sciences, docent, ira1978@tsu.tula.ru, Russia, Tula, Tula State University,
Kuznetsov Grigory Alekseevich, undergraduate, gregory.kuznetsov1998@gmail.com, Russia, Tula, Tula State University