Приветствую!
На протяжении нескольких лет моего опыта в качестве разработчика ПО, не раз сталкивался с ситуацией, когда нужно было выполнить не самый простой SQL-запрос по производительности. Несмотря на то, что не всегда здорово большие аналитические запросы генерить и отдавать на фронт, я напишу статью именно на эту тему. Для примера воспользуемся двумя технологиями, чтобы реализовать простой пример: R2DBC и JDBC
Лежат тут.
Для примера возьмём какую-нибудь большую базу данных из открытого источника. Так как я проживаю в России, то на ум пришла база данных ФИАСа (Федеральная информационная адресная система).
Для начала создадим базу данных jooq_test в PostgreSQL.
Перейдём по ссылке ФИАСа и скачаем нужный архив
Благодаря Linux утилитки pgdbf загрузим данные в базу:
Дождёмся когда все данные в базу прогрузятся и перейдем к следующему этапу.
Обратите внимание, что индексы в таблицах мы не создаём. Это сделано намеренно, чтобы усложнить выборку данных. Цель наша вовсе не в том, чтобы написать оптимальный запрос, а именно в технологическом применении извлечения данных.
Чтобы чуть усложнить запрос сделаем JOIN трёх таблиц, при этом данные возьмём из одной (doma):
И так, с запросом мы определились. Теперь можно приступить к написанию кода.
Как уже было описано выше, стек у нас Spring Boot + MVC + PostgreSQL Driver. Собирать проект мы будем с помощью Gradle
Подробно останавливаться на описании зависимостей мы не будем. Об этом много статей.
Опишем файл настроек в ресурсах проекта
Мы не просто так описали проперти подключения к базе конкретно по пути spring.datasource.hikari. У нас в проекта будет несколько источников данных. Один из которых будет Хикари, а второй R2dbc
В нашем примере весь код лежит в одном пакете: ru.jooq.test.jooqtest. Классов будет немного и для наглядного представления создавать их в бизнесовые пакеты мы не будем.
Как и любое приложение Spring Boot'a всё начинается с аннотации @SpringBootApplication
Здесь тоже ничего нового мы для себя не находим. Продолжим…
Настройки подключения к базе данных у нас лежат в классе JooqConfiguration
Как уже ранее было сказано, у нас два dataSource и по этой причине настройки не по умолчанию spring boot'а.
Обращаться к данным мы будем через протокол HTTP, выходная модель которого представлена в ResultDto-классе
Теперь надо реализовать слой взаимодействия с БД для запроса, который мы описывали ранее.
Для нашего примера мы ограничимся условием лишь на количество строк. Этого достаточно для понимания всех процессов описанных в данной статье. Qualifier(«jdbcDSLContext») мы используем для того, чтобы однозначно определить тот bean, который нам нужен.
Метод getBlockingJooq должен вызываться в сервисе SampleService
Непосредственно сам end-point опишем в SampleController
Здесь мы видим простой RestController и метод jooqBlocking с одним лишь параметром запроса: limit. В сервис мы передаём текущую дату и пришедший параметр от клиента. Ресурс запроса состоит из строки /sample/jooq-blocking, а полный запрос выглядит так:
Чтобы наглядно продемонстрировать работоспособность урла, было записано видео и лежит оно тут
О нём много полезной информации можно найти на Хабре. Первоисточник: r2dbc.io
Технологический стек для данного примера: Spring Boot + Web Flux + R2dbc PostgreSQL Driver
Зависимости в build.gradle.kt нужно подкорректировать:
spring-boot-starter-webflux — это стартер spring WebFlux, который является самодостаточным и несёт в себе практически все необходимые зависимости для «реактивного» взаимодействия.
r2dbc-postgresql — r2dbc драйвер для postgreSQL
Подключение к базе данных нужно немного донастроить, так как драйвер теперь другой, да и сам механизм формирования DSLContext иной:
aplication.yml
Метод в Repository на получение данных реализован через Spring Reactor библиотеку:
Flux — это stream который может отправлять от 0 до N элементов в поток. Он идеально подходит для нашего кейса. Flux наследуется от Publisher, который и публикует сообщение в поток. Более подробно можно почитать тут
Метод очень похож на то, что мы видели в Jdbc-подходе, с одним изменением: вместо списка — stream.
Слой сервиса по аналогии c JDBC тоже следует обновить:
Здесь просто вызываем наш метод из репозитория.
Слой контроллера в WebFlux имеет отличительную особенность в сравнении с MVC. Дело в том, что привычный нам Content-Type: application/json не подходит, так как это блокирующий вызов. Вместо этого нужно использовать Content-Type: application/x-ndjson для стриминга индивидуальных элементов, разделённых новой строкой. Он идеально подходит для нашей концепции.
Curl запрос выглядит так:
Видео, которое демонстрирует работу с R2dbc выложено тут
Для начала запустим наши запросы и посмотрим, какая «картина». Будем запускать 1 млн. строк как для jooq-blocking, так и для jooq-reactive
На первом скриншоте изображён Jdbc запуск, на втором R2dbc
На первом скриншоте изображён Jdbc запуск, на втором R2dbc
Jdbc
R2dbc
В данной статье продемонстрированы самые простые примеры применения реактивного подхода к разработке. Кто-то считает такой способ — будущем, кто-то настоящим, а кто-то и вовсе ненужной фичей. Сравнительный анализ показал, что в целом Spring MVC справляется быстрее с задачей, однако Spring WebFlux + r2dbc использует значительно меньше ресурсов приложения.
На протяжении нескольких лет моего опыта в качестве разработчика ПО, не раз сталкивался с ситуацией, когда нужно было выполнить не самый простой SQL-запрос по производительности. Несмотря на то, что не всегда здорово большие аналитические запросы генерить и отдавать на фронт, я напишу статью именно на эту тему. Для примера воспользуемся двумя технологиями, чтобы реализовать простой пример: R2DBC и JDBC
Входные данные
- Бизнес сильно ограничен в ресурсах и не готов тратить большие деньги на NoSql специалистов
- В команде есть сильный front-end разработчик, который не боится кешировать данные на фронте и их обрабатывать. Умеет эффективно работать с большим списком
- Есть back-end разработчик, который уважает себя, пишет код для не очень мощного железа
- Задача связана с отображением большого массива данных на фронте (будь то геоданные на карте или картотека в интернет-магазине)
Что будем делать?
- Взять большой массив данных из базы. В примере загружена база ФИАС
- Выполнить запрос и отдать клиенту
План работ
- Написать SQL-запрос на выборку данных
- Написать код, который выполнял запрос и после чего отдавать результат клиенту
- Написать код, который бы сформировал запрос на выборку данных и такой запрос, чтобы при первом найденном результате поиска данные бы отдавались клиенту для скорейшей обработки
- Сравнить результат
Технологический стек
- Spring boot 2.5+
- PostgreSQL
- Jooq Framework
- Spring Webflux
- Srping Web MVC
- R2dbc driver для PostgreSQL
- Jdbc driver для PostgreSQL
Исходники
Лежат тут.
Подготовка данных и запрос на выборку данных
Для примера возьмём какую-нибудь большую базу данных из открытого источника. Так как я проживаю в России, то на ум пришла база данных ФИАСа (Федеральная информационная адресная система).
Для начала создадим базу данных jooq_test в PostgreSQL.
CREATE DATABASE jooq_test;
Перейдём по ссылке ФИАСа и скачаем нужный архив
Благодаря Linux утилитки pgdbf загрузим данные в базу:
pgdbf DOMA.DBF | iconv -c -f cp866 -t UTF-8| psql jooq_test
pgdbf FLAT.DBF | iconv -c -f cp866 -t UTF-8| psql jooq_test
pgdbf STREET.DBF | iconv -c -f cp866 -t UTF-8| psql jooq_test
Дождёмся когда все данные в базу прогрузятся и перейдем к следующему этапу.
Обратите внимание, что индексы в таблицах мы не создаём. Это сделано намеренно, чтобы усложнить выборку данных. Цель наша вовсе не в том, чтобы написать оптимальный запрос, а именно в технологическом применении извлечения данных.
Чтобы чуть усложнить запрос сделаем JOIN трёх таблиц, при этом данные возьмём из одной (doma):
select d.*
from doma d
join street s on d.ocatd = s.ocatd
join flat f on f.gninmb = s.gninmb
И так, с запросом мы определились. Теперь можно приступить к написанию кода.
Jdbc-подход
Как уже было описано выше, стек у нас Spring Boot + MVC + PostgreSQL Driver. Собирать проект мы будем с помощью Gradle
build.gradle.kts
import nu.studer.gradle.jooq.JooqEdition
import org.jetbrains.kotlin.gradle.tasks.KotlinCompile
// Необходимы для работы плагины
plugins {
id("org.springframework.boot") version "2.5.6"
id("io.spring.dependency-management") version "1.0.11.RELEASE"
id("nu.studer.jooq") version ("6.0.1")
kotlin("jvm") version "1.5.31"
kotlin("plugin.spring") version "1.5.31"
}
group = "ru.jooq.test"
version = "0.0.1-SNAPSHOT"
java.sourceCompatibility = JavaVersion.VERSION_11
repositories {
mavenCentral()
}
val postgresVersion = "42.3.1"
dependencies {
// Зависимость для jooq
jooqGenerator("org.postgresql:postgresql:$postgresVersion")
// Spring стартеры
implementation("org.springframework.boot:spring-boot-starter-jooq")
implementation("org.springframework.boot:spring-boot-starter-jdbc")
implementation("com.fasterxml.jackson.module:jackson-module-kotlin")
implementation("org.jetbrains.kotlin:kotlin-reflect")
implementation("org.jetbrains.kotlin:kotlin-stdlib-jdk8")
runtimeOnly("org.postgresql:postgresql")
testImplementation("org.springframework.boot:spring-boot-starter-test")
}
tasks.withType<KotlinCompile> {
kotlinOptions {
freeCompilerArgs = listOf("-Xjsr305=strict")
jvmTarget = "11"
}
}
tasks.withType<Test> {
useJUnitPlatform()
}
// генерация классов для Jooq Framework
jooq {
edition.set(JooqEdition.OSS)
configurations {
create("main") {
jooqConfiguration.apply {
jdbc.apply {
driver = "org.postgresql.Driver"
url = "jdbc:postgresql://localhost:5432/jooq_test"
user = "postgres"
password = "postgres"
}
generator.apply {
name = "org.jooq.codegen.DefaultGenerator"
generate.apply {
isDeprecated = false
isRecords = true
isImmutablePojos = false
isFluentSetters = false
isJavaBeansGettersAndSetters = false
}
database.apply {
name = "org.jooq.meta.postgres.PostgresDatabase"
inputSchema = "public"
}
target.apply {
packageName = "ru.jooq.test.jooqtest.domain"
}
strategy.name = "org.jooq.codegen.DefaultGeneratorStrategy"
}
}
}
}
}
Подробно останавливаться на описании зависимостей мы не будем. Об этом много статей.
Опишем файл настроек в ресурсах проекта
application.yml
spring:
datasource:
driverClassName: org.postgresql.Driver
hikari:
jdbc-url: jdbc:postgresql://localhost:5432/jooq_test
username: postgres
password: postgres
jooq:
sql-dialect: postgres
Мы не просто так описали проперти подключения к базе конкретно по пути spring.datasource.hikari. У нас в проекта будет несколько источников данных. Один из которых будет Хикари, а второй R2dbc
В нашем примере весь код лежит в одном пакете: ru.jooq.test.jooqtest. Классов будет немного и для наглядного представления создавать их в бизнесовые пакеты мы не будем.
Как и любое приложение Spring Boot'a всё начинается с аннотации @SpringBootApplication
JooqTestApplication.kt
package ru.jooq.test.jooqtest
import org.springframework.boot.autoconfigure.SpringBootApplication
import org.springframework.boot.runApplication
@SpringBootApplication
class JooqTestApplication
fun main(args: Array<String>) {
runApplication<JooqTestApplication>(*args)
}
Здесь тоже ничего нового мы для себя не находим. Продолжим…
Настройки подключения к базе данных у нас лежат в классе JooqConfiguration
JooqConfiguration.kt
package ru.jooq.test.jooqtest
import javax.sql.DataSource
import org.jooq.DSLContext
import org.jooq.impl.DSL
import org.jooq.impl.DataSourceConnectionProvider
import org.jooq.impl.DefaultConfiguration
import org.jooq.impl.DefaultDSLContext
import org.springframework.boot.context.properties.ConfigurationProperties
import org.springframework.boot.jdbc.DataSourceBuilder
import org.springframework.context.annotation.Bean
import org.springframework.context.annotation.Configuration
import org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy
@Configuration
class JooqConfiguration {
// конфигурируем dslConext для запросов к СУБД
@Bean(value = ["jdbcDSLContext"])
fun jdbcDSLContext(): DSLContext {
return DefaultDSLContext(configuration())
}
// DataSource из настроек application.yml
@Bean
@ConfigurationProperties(prefix = "spring.datasource.hikari")
fun dataSource(): DataSource {
return DataSourceBuilder.create().build()
}
// Открываем подключение
@Bean
fun lazyConnectionDataSource(): LazyConnectionDataSourceProxy {
return LazyConnectionDataSourceProxy(dataSource())
}
@Bean
fun connectionProvider(): DataSourceConnectionProvider {
return DataSourceConnectionProvider(lazyConnectionDataSource())
}
@Bean
fun configuration(): DefaultConfiguration {
val jooqConfiguration = DefaultConfiguration()
jooqConfiguration.set(connectionProvider())
return jooqConfiguration
}
}
Как уже ранее было сказано, у нас два dataSource и по этой причине настройки не по умолчанию spring boot'а.
Обращаться к данным мы будем через протокол HTTP, выходная модель которого представлена в ResultDto-классе
ResultDto.kt
class ResultDto(
val queryTime: LocalDateTime, // Время запроса
val dataTime: LocalDateTime, // Время маппинга данных
timeToResult: Long? = 0,
val dataDto: DataDto // Набор данных из таблицы
) {
val timeToResult: Long = Duration.between(queryTime, dataTime).toMillis()
}
data class DataDto(
val name: String,
val korp: String,
val socr: String,
val code: String,
val index: String,
val gninmb: String,
val uno: String,
val ocatd: String
)
Теперь надо реализовать слой взаимодействия с БД для запроса, который мы описывали ранее.
SampleRepository
package ru.jooq.test.jooqtest
import java.time.LocalDateTime
import org.jooq.DSLContext
import org.jooq.Record8
import org.jooq.SelectLimitPercentStep
import org.springframework.beans.factory.annotation.Qualifier
import org.springframework.stereotype.Repository
import ru.jooq.test.jooqtest.domain.Tables
@Repository
class SampleRepository(
@Qualifier("jdbcDSLContext")
private val jdbcDSLContext: DSLContext
) {
private val d = Tables.DOMA
private val s = Tables.STREET
private val f = Tables.FLAT
// Маппинг данных на модель ResultDto
fun getBlockingJooq(queryTime: LocalDateTime, limit: Long): List<ResultDto> {
return getQuery(jdbcDSLContext, limit)
.map { r ->
ResultDto(
queryTime = queryTime,
dataTime = LocalDateTime.now(),
dataDto = r.into(d).into(DataDto::class.java)
)
}
}
// SQL-запрос
private fun getQuery(dslContext: DSLContext, limit: Long): SelectLimitPercentStep<Record8<String, String, String, String, String, String, String, String>> {
return dslContext
.select(d.NAME, d.KORP, d.SOCR, d.CODE, d.INDEX, d.GNINMB, d.UNO, d.OCATD)
.from(d)
.join(s).on(s.OCATD.eq(d.OCATD))
.join(f).on(f.GNINMB.eq(s.GNINMB))
.limit(limit)
}
}
Для нашего примера мы ограничимся условием лишь на количество строк. Этого достаточно для понимания всех процессов описанных в данной статье. Qualifier(«jdbcDSLContext») мы используем для того, чтобы однозначно определить тот bean, который нам нужен.
Метод getBlockingJooq должен вызываться в сервисе SampleService
SampleService.kt
package ru.jooq.test.jooqtest
import java.time.LocalDateTime
import org.springframework.stereotype.Service
@Service
class SampleService(private val sampleRepository: SampleRepository) {
// метод, который вызывает репозиторий с SQL-запросом
fun getBlockingJooq(queryTime: LocalDateTime, limit: Long): List<ResultDto> {
return sampleRepository.getBlockingJooq(queryTime, limit)
}
}
Непосредственно сам end-point опишем в SampleController
SampleController.kt
package ru.jooq.test.jooqtest
import java.time.LocalDateTime
import org.springframework.http.MediaType
import org.springframework.web.bind.annotation.GetMapping
import org.springframework.web.bind.annotation.RequestParam
import org.springframework.web.bind.annotation.RestController
@RestController
class SampleController(private val sampleService: SampleService) {
@GetMapping("/sample/jooq-blocking")
fun jooqBlocking(@RequestParam limit: Long): List<ResultDto> {
return sampleService.getBlockingJooq(LocalDateTime.now(), limit)
}
}
Здесь мы видим простой RestController и метод jooqBlocking с одним лишь параметром запроса: limit. В сервис мы передаём текущую дату и пришедший параметр от клиента. Ресурс запроса состоит из строки /sample/jooq-blocking, а полный запрос выглядит так:
curl http://localhost:8080/sample/jooq-blocking?limit=1000000
Чтобы наглядно продемонстрировать работоспособность урла, было записано видео и лежит оно тут
R2dbc-подход
The Reactive Relational Database Connectivity (R2DBC) принёс реактивное API для реляционных баз данных
О нём много полезной информации можно найти на Хабре. Первоисточник: r2dbc.io
Технологический стек для данного примера: Spring Boot + Web Flux + R2dbc PostgreSQL Driver
Зависимости в build.gradle.kt нужно подкорректировать:
dependencies {
...
implementation("org.springframework.boot:spring-boot-starter-webflux")
runtimeOnly("io.r2dbc:r2dbc-postgresql")
}
spring-boot-starter-webflux — это стартер spring WebFlux, который является самодостаточным и несёт в себе практически все необходимые зависимости для «реактивного» взаимодействия.
r2dbc-postgresql — r2dbc драйвер для postgreSQL
Подключение к базе данных нужно немного донастроить, так как драйвер теперь другой, да и сам механизм формирования DSLContext иной:
aplication.yml
spring:
r2dbc:
url: r2dbc:postgresql://localhost:5432/jooq_test
password: postgres
username: postgres
pool:
initial-size: 3
max-size: 10
max-idle-time: 30m
jooq:
sql-dialect: postgres
@Configuration
class JooqConfiguration(
private val connectionFactory: ConnectionFactory
) {
@Bean(value = ["r2dbcDSLContext"])
fun createContext(): DSLContext {
return DSL.using(connectionFactory)
}
...
}
Метод в Repository на получение данных реализован через Spring Reactor библиотеку:
@Repository
class SampleRepository(
@Qualifier("r2dbcDSLContext")
private val r2dbcDSLContext: DSLContext,
@Qualifier("jdbcDSLContext")
private val jdbcDSLContext: DSLContext
) {
...
fun getReactiveJooq(queryTime: LocalDateTime, limit: Long): Flux<ResultDto> {
return Flux.from(getQuery(r2dbcDSLContext, limit))
.map { r ->
ResultDto(
queryTime = queryTime,
dataTime = LocalDateTime.now(),
dataDto = r.into(d).into(DataDto::class.java)
)
}
}
...
}
Flux — это stream который может отправлять от 0 до N элементов в поток. Он идеально подходит для нашего кейса. Flux наследуется от Publisher, который и публикует сообщение в поток. Более подробно можно почитать тут
Метод очень похож на то, что мы видели в Jdbc-подходе, с одним изменением: вместо списка — stream.
Слой сервиса по аналогии c JDBC тоже следует обновить:
@Service
class SampleService(private val sampleRepository: SampleRepository) {
fun getReactiveJooq(queryTime: LocalDateTime, limit: Long): Flux<ResultDto> {
return sampleRepository.getReactiveJooq(queryTime, limit)
}
...
}
Здесь просто вызываем наш метод из репозитория.
Слой контроллера в WebFlux имеет отличительную особенность в сравнении с MVC. Дело в том, что привычный нам Content-Type: application/json не подходит, так как это блокирующий вызов. Вместо этого нужно использовать Content-Type: application/x-ndjson для стриминга индивидуальных элементов, разделённых новой строкой. Он идеально подходит для нашей концепции.
@RestController
class SampleController(private val sampleService: SampleService) {
@GetMapping("/sample/jooq-reactive", produces = [MediaType.APPLICATION_NDJSON_VALUE])
fun jooqReactive(@RequestParam limit: Long): Flux<ResultDto> {
return sampleService.getReactiveJooq(LocalDateTime.now(), limit)
}
...
}
Curl запрос выглядит так:
curl http://localhost:8080/sample/jooq-reactive?limit=1000000
Видео, которое демонстрирует работу с R2dbc выложено тут
Сравнительный анализ
Для начала запустим наши запросы и посмотрим, какая «картина». Будем запускать 1 млн. строк как для jooq-blocking, так и для jooq-reactive
Потребление памяти
На первом скриншоте изображён Jdbc запуск, на втором R2dbc
Загрузка CPU
На первом скриншоте изображён Jdbc запуск, на втором R2dbc
Время выполнения
Jdbc
GET http://localhost:8080/sample/jooq-blocking?limit=1000000
HTTP/1.1 200 OK
Content-Type: application/json
Content-Length: 279170577
> 2021-11-18T181435.200.json
Response code: 200 (OK); Time: 25541ms; Content length: 273258295 bytes
R2dbc
GET http://localhost:8080/sample/jooq-reactive?limit=1000000
HTTP/1.1 200 OK
transfer-encoding: chunked
Content-Type: application/x-ndjson
> 2021-11-18T180754.200.txt
Response code: 200 (OK); Time: 47203ms; Content length: 279975556 bytes
Заключение
В данной статье продемонстрированы самые простые примеры применения реактивного подхода к разработке. Кто-то считает такой способ — будущем, кто-то настоящим, а кто-то и вовсе ненужной фичей. Сравнительный анализ показал, что в целом Spring MVC справляется быстрее с задачей, однако Spring WebFlux + r2dbc использует значительно меньше ресурсов приложения.
awfun
Не в тему, но как вы организуете сборку jooq проектов на локальной машине? Жуку нужна база, по которой он сгенерирует код. Разработчик поднимает базу локально, или во время билда поднимается контейнер, или другой способ?
minpor Автор
При помощи pgdbf создаём таблицы в PosgreSQL.
Запуск таски generateJooq генерирует нужные классы. В плагнине nu.studer.jooq:6.0.1 срабатывает правило запуска данной таски во время сборки проекта
minpor Автор
СУБД должна быть рабочая у разработчика, который запускает приложение. Это может быть независмый экземпляр от контейнера или контейнер c экземпляром СУБД. Кому как удобно. У меня без контейнера!