Старые базы данных могут содержать очень сложные и непонятные запросы, скорость выполнения которых необходимо увеличить. Ручная оптимизации займёт много времени и напрямую зависит от опыта работы сотрудника с данной даталогической моделью.
- Минимизировать время на оптимизацию БД пользователем
- Максимально увеличить скорость выполнения запросов к БД
- Проектирование и создание тестовой базы данных
- СОЗДАНИЕ ПРИЛОЖЕНИЯ АВТОИНДЕКСИРОВАНИЯ БАЗЫ ДАННЫХ
- Тестирование приложения
- Анализ полученных результатов
8 из 10 самых популярных (по версии db-engines.com) СУБД подобной функциональности не имеют.
| Рейтинг | СУБД | Модель данных |
|---|---|---|
| 1 | Oracle | Реляционная |
| 2 | MySQL | Реляционная |
| 3 | Microsoft SQLServer | Реляционная |
| 4 | PostgreSQL | Реляционная |
| 5 | MongoDB | Документная |
| 6 | Redis | Ключ-значение |
| 7 | IBM Db2 | Реляционная |
| 8 | Elasticsearch | Поисковой движок |
| 9 | Microsoft Access | Реляционная |
| 10 | SQLite | Реляционная |
Только Oracle Database и Microsoft SQLServer имеют функцию автоиндексирования, но это дорогие коммерческие продукты,
а сам процесс представляет собой «черный ящик».
Нельзя официально приобрести в России.
Преимущества моего решения:
- Поддержка любой БД с JDBC-драйвером
- Контролируемые процесс (формирование отчётов)
- Открытый исходный код
Ключевым требованием, предъявляемым к разрабатываемой программе, сформулированным в задании на выпускную квалификационную работу, является поддержка широкого спектра оборудования, а именно операционных систем Windows, Linux, macOS и процессорных архитектур arm, x86-64. Необходимо было выбрать язык программирования, который бы позволял запускать программу на всех перечисленных конфигурациях. Чтобы разработка проходила легче, стоило выбрать тот язык, который обладает развитым сообществом и подробной документацией. Я обратился к веб-сайту TIOBE, который занимается оценкой и аудитом программного обеспечения. Специалисты сайта поддерживают ежемесячный рейтинг популярности язык программирования, опираясь на данные которого, я решил начать своё исследование. Из представленного списка, наиболее подходящими кандидатами является Java. Тем не менее за свою более чем тридцатилетнюю историю, язык успел накопить проблем, в первую очередь связанных со слишком строгим следованием правилам ООП и громоздким синтаксисом. Со временем, появились другие языки, которые исправляли эти проблемы, а их код мог выполняться в JVM (Java Virtual Machine – среда выполнения Java-кода). Одним из таких языков является Kotlin, созданный в Санкт-Петербурге в 2011 году фирмой JetBrains. Имея богатый опыт разработки на Java и контакт с сообществом, они создали язык, который решал проблемы Java и был полностью совместим с ним. Другой причиной, почему была выбрана именно технология JVM, является протокол JDBC: он обеспечивает интерфейс для взаимодействия с любой SQL СУБД, для которой разработчики СУБД написали JDBC-драйвер, что они обычно и делают, ввиду широкого распространения Java/JVM в корпоративной разработке. Это позволит мне удобно добавлять поддержку новых СУБД, так как интерфейс выполнения SQL выражений будет общий, сложная задача будет состоять в том, чтобы научить программу формировать выражения создания индексов, специфичные для каждой СУБД.
Приложению также нужен интерфейс взаимодействия с пользователем, графический или консольный. Стоит отметить, что, если реализовать взаимодействие с приложением через HTTP-протокол, появится возможность независимо от ядра приложения (той части, которая будет отвечать за разбор истории запросов и формирование индексов), обновлять графический интерфейс. Или создать консольный интерфейс ввода. Или создать другой клиент на любой вкус. Было решено так и поступить. Клиент я решил реализовать в виде веб-приложения, как это часто делают другие авторы ПО, используемого в качестве инфраструктурного, к примеру, Jenkins, WildFly или pgAdmin. Выбор фронтенд-фреймворка по большей части дело вкуса: каждый из трёх самых популярный фрейворков (Vue, React, Angular) имеет подробную документацию, активное сообщество, библиотеки компонентов. С первыми двумя у меня был опыт, в обоих случаях положительный, а потому я решил использовать React: мне он нравится своим декларативным подходом.
Так как ядро приложения будет является сервером, доступ к которому могут получить несколько клиентов в сети, или может быть развернуто несколько экземпляров приложения параллельно, нам нужно где-то хранить информацию, какая из баз данных сейчас индексируется приложением, чтобы не допустить параллельного построения одних и тех же индексов несколькими экземплярами приложения. Хранить достаточно лишь строку – url занятой базы данных. Для таких целей отлично подойдут хранилища типа «ключ-значение», самые известные из которых Redis и Memcached. Оба продукта представляют одинаковую функциональность, но Redis имеет чуть более удобную в использовании библиотеку для JVM, поэтому был выбран он.
Невозможно будет проверить работу программы без существования сложной базы данных. Модель данных будет описана в следующей части, здесь стоит определиться с СУБД, в который эта база будет создана. Для тестирования стоит выбрать ту, которая имеет несколько разных индексов, позволит создать сложную модель данных, а опыт работы в ней достаточен, чтобы в случае проблем быть уверенным, что проблемы именно в разработанном приложении, а не в неправильно сконфигурированной СУБД. Для меня такой является PostgreSQL.
Определившись с используемыми технологиями, нужно понять, когда и как эти технологии будут между собой взаимодействовать. При проектировании стоит учитывать, что процесс автоиндексирования базы данных может занимать длительное время, поэтому передача клиенту итогового отчёта будет асинхронным процессом. В таком случае, наилучшим вариантов будет дать возможность клиенту самому выбрать, куда получить отчёт, а не дожидаться HTTP-ответа от серверной части приложения.
- Клиент создаёт задание на автоиндексирование, предоставляя историю запросов, данные для подключения к БД и данные для получения отчёта.
- Сервер выполняет необходимые проверки, к примеру, разбор SQL-выражений на корректность, не занята ли БД индексацией в данный момент.
- Клиент получит ответное сообщение, в случае успеха: что его задание принято в работу, и отчёт он получит, как только работа будет завершена; в случае проблем сообщение об ошибке. Этот процесс представлен на схемах.
Чтобы удостоверить в работе приложения, необходимо проверить его на какой-либо базе данных. Эта база должна быть большой по количеству записей и обладать сложной моделью данных. Попробуем спроектировать такую БД. Определим предметную область как Частная военная компания. Новые отношения (таблицы) и их атрибуты можно придумывать бесконечно, а потому, я усложнял модель, пока на это хватало фантазии. Описывать каждую сущность смысла нет, так как процесс исключительно творческий, а не технический, а потому сразу приведу полную схему и выдержку из скрипта создания этой модели.
Главным требованием, предъявляемым к архитектуре серверной части приложения, является простота расширения, что позволит просто и быстро добавлять поддержку новый СУБД. Для выполнения SQL-запросов к СУБД используется интерфейс, предоставляемый JDBC: он не обладает информацией о том, какие индексы существует в каждой конкретной СУБД, а также о синтаксисе выражения добавления индекса, который может отличаться от стандартного SQL. И использовал средство языка программирования, которое называется перечислением (enum). Новый элемент перечисления принимает на вход названия индексов СУБД, выражение для создания индексов на основе стандарта SQL, переопределяет метод формирования запросов и возвращает множество этих запросов. Таким образом, можно выполнять запросы с помощью JDBC независимо от их (запросов) генерации, как бы сильно синтаксис конкретной СУБД не расходился с общим SQL-синтаксисом.
Алгоритм формирования индексов: программа создаёт для каждого уникального SQL-запроса из истории все возможные комбинации:
Таблица-поле-тип индекса
- Java11
- PostgreSQL
- Redis
Клиент и сервер разворачиваются отдельно.
Установите указанные переменные окружения:
| Название | Описание | Пример |
|---|---|---|
EMAIL_SENDER |
Сервер исходящих сообщений | test@gmail.com;test; smtp.googlemail.com:465 |
REDIS_CACHE_CREDS |
Адрес экземпляра Redis | localhost:6379 |
SERVICE_PORT |
Порт сервиса данного приложения | 80 |
Далее используйте команду:
java -jar back/target/server.jarВ файле front/.env укажите адрес сервера в переменную REACT_APP_BACK_URL. Пример:
REACT_APP_BACK_URL="http://localhost:80"
Далее используйте команду:
cd front
npm startГрафический интерфейс клиента
Спецификация OpenAPI3 сервера
openapi: 3.0.3
info:
title: SQL-OPTIMIZER
version: 1.1.0
components:
schemas:
FORMATS:
type: string
enum:
- CSV
- JSON
CONSUMERS:
type: string
enum:
- EMAIL
- SMB
- SFTP
- FS
INSTANCES:
type: string
enum:
- POSTGRESQL
- SQLSERVER
CommonMessage:
type: object
properties:
details:
type: string
required: true
BenchTask:
type: object
properties:
connectionUrl:
type: string
required: true
queries:
type: array
items:
type: string
required: true
consumer:
type: string
required: false
default: FS
format:
type: string
required: false
default: CSV
consumerParams:
type: string
required: false
default: ''
saveBetter:
type: boolean
required: false
default: false
paths:
/bench:
post:
requestBody:
required: true
content:
application/json:
schema:
$ref: 'components/schemas/BenchTask'
responses:
200:
description: Info about test request
content:
application/json:
schema:
$ref: 'components/schemas/CommonMessage'
400:
description: This database not supported yet
404:
description: Provided connectionUrl doesn't not match pattern
504:
description: Can't ping database. Please check it's availability and creds or try again later.
500:
description: Error while creating database server specific index for query.
5XX:
description: Something goes wrong
/support/formats:
get:
responses:
200:
description: Enum with supported formats
content:
application/json:
schema:
$ref: '#components/schemas/formats'
/support/consumers:
get:
responses:
200:
description: Enum with supported methods for saving reports
content:
application/json:
schema:
$ref: '#components/schemas/consumers'
/support/instances:
get:
responses:
200:
description: Enum with DBMS which the program can build indexes
content:
application/json:
schema:
$ref: '#components/schemas/instances'Примеры отчёта, сформированного программой для выражения
SELECT emp_id FROM employee
JOIN position USING (pos_id)
JOIN missions_emp USING (emp_id)
JOIN mission USING (miss_id)
WHERE rank !~~ ''
ORDER BY is_married DESC, end_date_and_time DESC, hiring_date DESC
LIMIT 20;
| indexStatement | diff | timeTaken |
|---|---|---|
| CREATE INDEX Is_marriedEmployeeHASH ON employee USING HASH(is_married); | 173166 | 4956042 |
| CREATE INDEX Is_marriedHiring_dateEmployeeBTREE ON employee USING BTREE(is_married, hiring_date); | 123791 | 5005417 |
| CREATE INDEX End_date_and_timeMissionBTREE ON mission USING BTREE(end_date_and_time); | 78250 | 5050958 |
| CREATE INDEX Emp_idIs_marriedHiring_dateEmployeeBTREE ON employee USING BTREE(emp_id, is_married, hiring_date); | 71375 | 5057833 |
| CREATE INDEX RankPositionHASH ON position USING HASH(rank); | 45833 | 5083375 |
| CREATE INDEX Emp_idMissions_empHASH ON missions_emp USING HASH(emp_id); | 7375 | 5121833 |
| CREATE INDEX End_date_and_timeMissionHASH ON mission USING HASH(end_date_and_time); | -37834 | 5167042 |
| CREATE INDEX Emp_idMissions_empBTREE ON missions_emp USING BTREE(emp_id); | -86209 | 5215417 |
| CREATE INDEX Is_marriedEmployeeBTREE ON employee USING BTREE(is_married); | -109667 | 5238875 |
| CREATE INDEX Emp_idHiring_dateEmployeeBTREE ON employee USING BTREE(emp_id, hiring_date); | -173792 | 5303000 |
| CREATE INDEX RankPositionBTREE ON position USING BTREE(rank); | -181834 | 5311042 |
| CREATE INDEX Hiring_dateEmployeeBTREE ON employee USING BTREE(hiring_date); | -209209 | 5338417 |
| CREATE INDEX Hiring_dateEmployeeHASH ON employee USING HASH(hiring_date); | -414625 | 5543833 |
| CREATE INDEX Emp_idEmployeeBTREE ON employee USING BTREE(emp_id); | -665376 | 5794584 |
| CREATE INDEX Emp_idIs_marriedEmployeeBTREE ON employee USING BTREE(emp_id, is_married); | -674542 | 5803750 |
| CREATE INDEX Emp_idEmployeeHASH ON employee USING HASH(emp_id); | -3633667 | 8762875 |
Больше отчётов можно найти в директории docs/reports.