Как вспомогательная таблица может помочь найти данные

# Вспомогательные таблицы и индексы для оптимизации поиска данных

Вспомогательные таблицы и индексы — это критически важные инструменты для оптимизации поиска данных в больших объёмах информации.

Индексы как основной вспомогательный механизм

Индексы — это отдельные структуры данных, которые хранят отсортированные значения колонок и указатели на соответствующие строки.

Базовый пример

-- Таблица пользователей
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    created_at TIMESTAMP WITH TIME ZONE
);

-- Без индекса: FULL TABLE SCAN (медленно)
SELECT * FROM users WHERE email = 'user@example.com';
-- Время: может быть O(n) для миллионов строк

-- С индексом: быстрый поиск
CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE email = 'user@example.com';
-- Время: O(log n) с B-tree индексом

Java примеры использования индексированных таблиц

1. Поиск по простому индексу

@Entity
@Table(name = "users", indexes = {
    @Index(name = "idx_email", columnList = "email", unique = true),
    @Index(name = "idx_created_at", columnList = "created_at")
})
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(nullable = false, unique = true)
    private String email;
    
    private String firstName;
    private String lastName;
    
    @CreationTimestamp
    private LocalDateTime createdAt;
    
    // Getters and Setters
}

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    // Использует индекс по email
    User findByEmail(String email);
    
    // Использует индекс по createdAt
    List<User> findByCreatedAtAfter(LocalDateTime date);
}

2. Составной индекс

-- Составной индекс по нескольким колонкам
CREATE INDEX idx_users_lastname_firstname 
ON users(last_name, first_name);

-- Эффективно используется для:
SELECT * FROM users 
WHERE last_name = 'Doe' AND first_name = 'John';

-- Может использоваться и для:
SELECT * FROM users WHERE last_name = 'Doe';

-- Но НЕ оптимален для:
SELECT * FROM users WHERE first_name = 'John';
-- (не использует индекс, нужна перестановка колонок)

@Entity
@Table(name = "users", indexes = {
    @Index(name = "idx_lastname_firstname", 
            columnList = "last_name, first_name")
})
public class User {
    // Fields
}

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    List<User> findByLastNameAndFirstName(String lastName, String firstName);
    List<User> findByLastName(String lastName);
}

Вспомогательные таблицы для денормализации

1. Таблица с количеством (для быстрого подсчёта)

-- Основная таблица
CREATE TABLE posts (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    title VARCHAR(255),
    created_at TIMESTAMP WITH TIME ZONE
);

-- Вспомогательная таблица с предсчитанными данными
CREATE TABLE user_post_stats (
    user_id BIGINT PRIMARY KEY REFERENCES users(id),
    total_posts INT DEFAULT 0,
    last_post_date TIMESTAMP WITH TIME ZONE,
    updated_at TIMESTAMP WITH TIME ZONE
);

-- Индекс для быстрого поиска по количеству постов
CREATE INDEX idx_user_stats_total_posts 
ON user_post_stats(total_posts DESC);

-- Быстрый поиск активных пользователей
SELECT u.* FROM users u
JOIN user_post_stats s ON u.id = s.user_id
WHERE s.total_posts > 100
ORDER BY s.total_posts DESC;

@Entity
@Table(name = "user_post_stats")
public class UserPostStats {
    @Id
    private Long userId;
    private Integer totalPosts;
    private LocalDateTime lastPostDate;
    private LocalDateTime updatedAt;
}

@Repository
public interface UserPostStatsRepository extends JpaRepository<UserPostStats, Long> {
    List<UserPostStats> findByTotalPostsGreaterThanOrderByTotalPostsDesc(Integer minPosts);
}

// Trigger для автоматического обновления статистики
public class PostListener {
    @PostPersist
    @PostUpdate
    public void updateUserStats(Post post) {
        // Обновляем user_post_stats
    }
}

2. Search таблица для полнотекстового поиска

-- Основная таблица статей
CREATE TABLE articles (
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(255),
    content TEXT,
    created_at TIMESTAMP
);

-- Вспомогательная таблица для поиска
CREATE TABLE article_search_index (
    article_id BIGINT PRIMARY KEY REFERENCES articles(id),
    search_text TEXT,
    created_at TIMESTAMP
);

-- Индекс для полнотекстового поиска
CREATE INDEX idx_article_search_text 
ON article_search_index USING GIN(to_tsvector('russian', search_text));

-- Быстрый поиск
SELECT a.* FROM articles a
JOIN article_search_index s ON a.id = s.article_id
WHERE to_tsvector('russian', s.search_text) @@ plainto_tsquery('russian', 'Java');

3. Временной индекс для диапазонных запросов

@Entity
@Table(name = "transactions", indexes = {
    @Index(name = "idx_user_date", columnList = "user_id, created_at DESC"),
    @Index(name = "idx_created_at", columnList = "created_at")
})
public class Transaction {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    private Long userId;
    private BigDecimal amount;
    
    @CreationTimestamp
    private LocalDateTime createdAt;
}

@Repository
public interface TransactionRepository extends JpaRepository<Transaction, Long> {
    // Использует индекс idx_user_date
    @Query("SELECT t FROM Transaction t WHERE t.userId = ?1 AND t.createdAt >= ?2 ORDER BY t.createdAt DESC")
    List<Transaction> findUserTransactionsAfter(Long userId, LocalDateTime date);
    
    // Использует индекс для диапазона дат
    @Query("SELECT t FROM Transaction t WHERE t.createdAt BETWEEN ?1 AND ?2")
    List<Transaction> findTransactionsByDateRange(LocalDateTime start, LocalDateTime end);
}

Примеры типов индексов

-- B-Tree индекс (по умолчанию, хорош для большинства случаев)
CREATE INDEX idx_email ON users(email);

-- Hash индекс (для точного совпадения, не для диапазонов)
CREATE INDEX idx_email_hash ON users USING HASH(email);

-- GIN индекс (для массивов и полнотекстового поиска)
CREATE INDEX idx_tags ON articles USING GIN(tags);

-- BRIN индекс (для больших таблиц с упорядоченными данными)
CREATE INDEX idx_timestamp ON events USING BRIN(created_at);

-- Partial индекс (только активные пользователи)
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

Анализ плана выполнения запроса

-- Без индекса: FULL TABLE SCAN
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';
-- Seq Scan on users  (cost=0.00..35.50 rows=1 width=100)

-- С индексом: INDEX SCAN
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';
-- Index Scan using idx_email on users  (cost=0.29..8.31 rows=1 width=100)

Java для анализа производительности

@Service
public class PerformanceAnalysisService {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    public void analyzeQueryPerformance() {
        String query = "EXPLAIN ANALYZE SELECT * FROM users WHERE email = ?";
        List<Map<String, Object>> results = jdbcTemplate.queryForList(
            query, "user@example.com"
        );
        
        results.forEach(row -> {
            System.out.println("Execution Plan: " + row);
        });
    }
}

Стратегии использования вспомогательных таблиц

1. Денормализация данных — для быстрого доступа к часто используемым данным
2. Кэширование предсчитанных значений — статистика, агрегаты
3. Индексирование часто используемых полей — email, uuid, дат
4. Партиционирование больших таблиц — по дате, по географии
5. Материализованные представления — pre-computed результаты запросов

Когда использовать

• Таблица содержит > 100k строк
• Часто выполняются медленные запросы
• Нужна быстрая агрегация больших объёмов
• Требуется полнотекстовый поиск
• Критична скорость поиска для пользовательского интерфейса

Вспомогательные таблицы и индексы — это не просто оптимизация, а необходимость при работе с большими объёмами данных.