Приветствую!

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


Входные данные


  • Бизнес сильно ограничен в ресурсах и не готов тратить большие деньги на NoSql специалистов
  • В команде есть сильный front-end разработчик, который не боится кешировать данные на фронте и их обрабатывать. Умеет эффективно работать с большим списком
  • Есть back-end разработчик, который уважает себя, пишет код для не очень мощного железа
  • Задача связана с отображением большого массива данных на фронте (будь то геоданные на карте или картотека в интернет-магазине)

Что будем делать?


  1. Взять большой массив данных из базы. В примере загружена база ФИАС
  2. Выполнить запрос и отдать клиенту

План работ


  1. Написать SQL-запрос на выборку данных
  2. Написать код, который выполнял запрос и после чего отдавать результат клиенту
  3. Написать код, который бы сформировал запрос на выборку данных и такой запрос, чтобы при первом найденном результате поиска данные бы отдавались клиенту для скорейшей обработки
  4. Сравнить результат

Технологический стек


  • 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 использует значительно меньше ресурсов приложения.

Комментарии (3)


  1. awfun
    19.11.2021 23:11

    Не в тему, но как вы организуете сборку jooq проектов на локальной машине? Жуку нужна база, по которой он сгенерирует код. Разработчик поднимает базу локально, или во время билда поднимается контейнер, или другой способ?


    1. minpor Автор
      20.11.2021 00:24

      1. При помощи pgdbf создаём таблицы в PosgreSQL.

      2. Запуск таски generateJooq генерирует нужные классы. В плагнине nu.studer.jooq:6.0.1 срабатывает правило запуска данной таски во время сборки проекта


    1. minpor Автор
      20.11.2021 00:28

      СУБД должна быть рабочая у разработчика, который запускает приложение. Это может быть независмый экземпляр от контейнера или контейнер c экземпляром СУБД. Кому как удобно. У меня без контейнера!