PrepBro
← Назад к вопросам

Какие знаешь способы оптимизации запросов к БД при увеличении числа пользователей?

1.7 Middle🔥 151 комментариев
#Основы Java

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

🐱
claude-haiku-4.5PrepBro AI22 мар. 2026 г.(ред.)

Ответ сгенерирован нейросетью и может содержать ошибки

Оптимизация запросов к БД при масштабировании

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

1. Индексирование

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

@Entity
@Table(name = "users", indexes = {
    @Index(name = "idx_email", columnList = "email", unique = true),
    @Index(name = "idx_status", columnList = "status"),
    @Index(name = "idx_created_at", columnList = "created_at"),
    @Index(name = "idx_username_status", columnList = "username,status")
})
public class User {
    // ...
}

Наблюдение за индексами:

-- PostgreSQL: найти неиспользуемые индексы
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

-- Анализ плана запроса
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'user@example.com';

2. Денормализация и кэширование

Materialized Views для часто используемых агрегаций.

-- Создаём материализованное представление
CREATE MATERIALIZED VIEW user_stats AS
SELECT 
    COUNT(*) as total_users,
    COUNT(CASE WHEN status = 'active' THEN 1 END) as active_users,
    AVG(EXTRACT(EPOCH FROM (now() - created_at))) as avg_user_age_seconds
FROM users;

-- Индекс для быстрого поиска
CREATE INDEX idx_user_stats_refresh ON user_stats(id);

-- Обновляем periodically через job
REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats;

Кэширование на уровне приложения:

@Service
public class UserStatsService {
    private final UserRepository userRepository;
    private final CacheManager cacheManager;
    private final ScheduledExecutorService scheduler;
    
    private UserStats cachedStats;
    private long lastUpdateTime;
    
    public UserStatsService(UserRepository repo, CacheManager cache) {
        this.userRepository = repo;
        this.cacheManager = cache;
        this.scheduler = Executors.newScheduledThreadPool(1);
        
        // Обновляем кэш каждые 5 минут
        scheduler.scheduleAtFixedRate(
            this::refreshStats,
            0,
            5,
            TimeUnit.MINUTES
        );
    }
    
    private void refreshStats() {
        this.cachedStats = userRepository.getStats();
        this.lastUpdateTime = System.currentTimeMillis();
    }
    
    public UserStats getStats() {
        return cachedStats;
    }
}

3. Read Replicas (Репликация для чтения)

Распределение нагрузки на несколько БД.

spring:
  datasource:
    hikari:
      maximum-pool-size: 20
  jpa:
    properties:
      hibernate:
        dialect: org.hibernate.dialect.PostgreSQL10Dialect

# Master для записи
spring:
  datasource:
    primary:
      jdbc-url: jdbc:postgresql://master-db:5432/prepbro
      username: user
      password: pass
      maximum-pool-size: 10

# Replicas для чтения
spring:
  datasource:
    replica1:
      jdbc-url: jdbc:postgresql://replica1-db:5432/prepbro
      username: user
      password: pass
    replica2:
      jdbc-url: jdbc:postgresql://replica2-db:5432/prepbro
      username: user
      password: pass

Роутинг запросов:

@Configuration
public class DataSourceConfig {
    @Bean
    public DataSource masterDataSource() {
        // Конфигурация master БД
        return createDataSource("master-db");
    }
    
    @Bean
    public DataSource replicaDataSource() {
        // Конфигурация replica БД
        return createDataSource("replica-db");
    }
    
    @Bean
    public DataSource routingDataSource(
            DataSource masterDataSource,
            DataSource replicaDataSource) {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put("master", masterDataSource);
        targetDataSources.put("replica", replicaDataSource);
        
        RoutingDataSource routingDataSource = new RoutingDataSource();
        routingDataSource.setTargetDataSources(targetDataSources);
        routingDataSource.setDefaultTargetDataSource(masterDataSource);
        return routingDataSource;
    }
}

public class RoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContext.getDataSourceType();
    }
}

public class DataSourceContext {
    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();
    
    public static void setMaster() {
        contextHolder.set("master");
    }
    
    public static void setReplica() {
        contextHolder.set("replica");
    }
    
    public static String getDataSourceType() {
        return contextHolder.get() != null ? contextHolder.get() : "master";
    }
    
    public static void clear() {
        contextHolder.remove();
    }
}

// Использование
@Service
public class UserService {
    @Transactional(readOnly = true)
    public User getUserById(UUID id) {
        DataSourceContext.setReplica(); // читаем из replica
        try {
            return userRepository.findById(id).orElseThrow();
        } finally {
            DataSourceContext.clear();
        }
    }
    
    @Transactional
    public User updateUser(User user) {
        DataSourceContext.setMaster(); // пишем в master
        try {
            return userRepository.save(user);
        } finally {
            DataSourceContext.clear();
        }
    }
}

4. Partitioning (Шардирование)

Разделение данных по диапазонам или хешам.

-- Партиционирование по дате
CREATE TABLE users (
    id UUID PRIMARY KEY,
    username VARCHAR(255),
    created_at TIMESTAMPTZ,
    ...
) PARTITION BY RANGE (created_at);

CREATE TABLE users_2024_q1 PARTITION OF users
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE users_2024_q2 PARTITION OF users
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

-- Партиционирование по хешу user_id
CREATE TABLE orders (
    id UUID,
    user_id UUID,
    amount DECIMAL
) PARTITION BY HASH (user_id);

CREATE TABLE orders_partition_0 PARTITION OF orders
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

Выбор шарда в приложении:

@Service
public class ShardingService {
    private final int SHARD_COUNT = 4;
    
    public int getShardId(UUID userId) {
        return Math.abs(userId.hashCode()) % SHARD_COUNT;
    }
    
    public DataSource getShardDataSource(UUID userId) {
        int shardId = getShardId(userId);
        return dataSourceMap.get("shard_" + shardId);
    }
}

5. Connection Pooling

HikariCP с правильной конфигурацией.

# Оптимальный размер пула: threads = ((количество ядер) * 2) + свободные диски
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.idle-timeout=600000
spring.datasource.hikari.max-lifetime=1800000
spring.datasource.hikari.auto-commit=true
spring.datasource.hikari.connection-test-query=SELECT 1

# Мониторинг пула
spring.datasource.hikari.leak-detection-threshold=60000

6. Query Optimization

Анализ и оптимизация медленных запросов.

// ❌ N+1 проблема
public List<Author> getAuthorsWithBooks() {
    List<Author> authors = authorRepository.findAll();
    // Каждый autor.getBooks() выполняет отдельный запрос
    authors.forEach(a -> a.getBooks().size());
    return authors;
}

// ✅ Решение — JOIN FETCH
@Query("SELECT DISTINCT a FROM Author a " +
        "LEFT JOIN FETCH a.books b " +
        "ORDER BY a.id")
List<Author> findAllWithBooks();

// ✅ Или используем Specification
authorRepository.findAll((root, query, cb) -> {
    root.fetch("books", JoinType.LEFT);
    return cb.conjunction();
});

Batch запросы:

# Включаем batch inserts/updates
spring.jpa.properties.hibernate.jdbc.batch_size=20
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true

@Transactional
public void saveUsers(List<User> users) {
    for (int i = 0; i < users.size(); i++) {
        userRepository.save(users.get(i));
        
        if (i % 20 == 0) {
            entityManager.flush();
            entityManager.clear();
        }
    }
}

7. Архивирование старых данных

Перемещение неиспользуемых данных в архив.

-- Создаём архивную таблицу
CREATE TABLE user_logs_archive AS
SELECT * FROM user_logs
WHERE created_at < now() - interval '1 year';

-- Удаляем из основной таблицы
DELETE FROM user_logs
WHERE created_at < now() - interval '1 year';

-- Периодический job (через pg_cron)
SELECT cron.schedule('archive_logs', '0 2 * * 0', $$
    INSERT INTO user_logs_archive
    SELECT * FROM user_logs
    WHERE created_at < now() - interval '1 year';
    
    DELETE FROM user_logs
    WHERE created_at < now() - interval '1 year';
$$);

8. Асинхронная обработка

Длительные операции не блокируют основной поток.

@Service
public class ReportService {
    @Async
    public CompletableFuture<Report> generateLargeReport(ReportCriteria criteria) {
        try {
            // Тяжёлый запрос с множеством данных
            List<Data> data = dataRepository.findByCriteria(criteria);
            Report report = processData(data);
            saveReport(report);
            
            return CompletableFuture.completedFuture(report);
        } catch (Exception e) {
            return CompletableFuture.failedFuture(e);
        }
    }
}

9. Мониторинг и профилирование

Spring Boot Actuator + Micrometer:

management.endpoints.web.exposure.include=health,metrics,prometheus
management.metrics.export.prometheus.enabled=true

@Service
public class UserService {
    private final MeterRegistry meterRegistry;
    
    @Transactional(readOnly = true)
    public User findById(UUID id) {
        Timer.Sample sample = Timer.start(meterRegistry);
        try {
            return userRepository.findById(id).orElseThrow();
        } finally {
            sample.stop(Timer.builder("user.find.latency")
                .publishPercentiles(0.5, 0.95, 0.99)
                .register(meterRegistry));
        }
    }
}

10. Full-Text Search (Elasticsearch для поиска)

Когда LIKE запросы становятся медленными.

@Configuration
@EnableElasticsearchRepositories(
    basePackages = "com.example.search"
)
public class ElasticsearchConfig {
    // ...
}

@Document(indexName = "users")
public class UserDocument {
    @Id
    private UUID id;
    
    @Field(type = FieldType.Text, analyzer = "standard")
    private String username;
    
    @Field(type = FieldType.Text, analyzer = "standard")
    private String bio;
}

public interface UserSearchRepository 
        extends ElasticsearchRepository<UserDocument, UUID> {
    
    List<UserDocument> findByUsernameContaining(String username);
    
    @Query("{\"match\": {\"bio\": {\"query\": \"?0\"}}}") 
    List<UserDocument> searchByBio(String query);
}

Стратегия оптимизации (по приоритету)

  1. Индексы (30% улучшения) — начинаем здесь
  2. Connection Pool (20% улучшения) — HikariCP с правильным размером
  3. Кэширование (25% улучшения) — Redis для часто читаемых данных
  4. Read Replicas (15% улучшения) — масштабируем чтение
  5. Query Optimization (10% улучшения) — JOIN FETCH, Batch обработка

Заключение

Масштабирование БД — это системная задача. Начинаем с простого (индексы, кэширование), потом переходим к сложному (шардирование, асинхрония). Всегда профилируем перед оптимизацией.