Какие знаешь способы ускорения выбора по таблице?

Способы ускорения выбора по таблице (SELECT запросы)

Производительность запросов — критичная часть оптимизации приложений. Рассмотрим все основные способы ускорения SELECT операций.

1. Индексы (Indexes)

Это основной инструмент ускорения:

-- Создание простого индекса
CREATE INDEX idx_users_email ON users(email);

-- Теперь этот запрос использует индекс (быстро)
SELECT * FROM users WHERE email = 'user@example.com';

-- Без индекса это был бы полный скан таблицы

Типы индексов:

-- B-tree индекс (default, для сравнений)
CREATE INDEX idx_users_created ON users(created_at);

-- Hash индекс (только для равенства)
CREATE INDEX idx_hash ON users USING HASH(email);

-- BRIN индекс (для больших таблиц с отсортированными данными)
CREATE INDEX idx_brin ON large_table USING BRIN(timestamp);

-- GiST индекс (для геоданных, полнотекстового поиска)
CREATE INDEX idx_location ON places USING GIST(location);

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

Составные индексы:

-- Индекс по нескольким колонкам
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);

-- Теперь эти запросы используют индекс
SELECT * FROM orders WHERE user_id = 123;
SELECT * FROM orders WHERE user_id = 123 AND created_at > '2024-01-01';

-- Но этот запрос НЕ использует индекс (неправильный порядок)
SELECT * FROM orders WHERE created_at > '2024-01-01';

2. EXPLAIN ANALYZE (анализ плана запроса)

Понимаем, почему запрос медленный:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 123;

-- Результат:
-- Index Scan using idx_orders_user_id on orders
-- Index Cond: (user_id = 123)
-- Rows: 45
-- Planning time: 0.142 ms
-- Execution time: 2.156 ms

-- Если видим "Seq Scan" (полный скан), нужен индекс!
EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'pending';
-- Seq Scan on orders
-- Filter: (status = 'pending')
-- Rows: 5000
-- Execution time: 1234.567 ms

3. Выбор нужных колонок (Projection)

// ❌ Плохо: выбираем всё
String sql = "SELECT * FROM orders WHERE user_id = ?";
List<Order> orders = jdbcTemplate.query(sql, mapper);

// ✅ Хорошо: только нужные колонки
String sql = "SELECT id, status, amount FROM orders WHERE user_id = ?";
List<OrderDTO> orders = jdbcTemplate.query(sql, mapper);

// Особенно для больших текстовых полей
// ❌ Плохо
SELECT id, description FROM products WHERE id = 1;
-- description может быть 1MB текста

// ✅ Хорошо
SELECT id FROM products WHERE id = 1;
SELECT description FROM products WHERE id = 1;  // Только если нужно

4. Limit и Pagination

// ❌ Плохо: загруженя все 1 млн строк
String sql = "SELECT * FROM users ORDER BY created_at DESC";
List<User> allUsers = jdbcTemplate.query(sql, mapper);
// Потом берём первые 20
List<User> first20 = allUsers.subList(0, 20);

// ✅ Хорошо: пaginация на уровне БД
String sql = "SELECT * FROM users ORDER BY created_at DESC LIMIT 20 OFFSET 0";
List<User> first20 = jdbcTemplate.query(sql, mapper);

// В Spring Data
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    Page<User> findAll(Pageable pageable);
}

// Использование
PageRequest pageRequest = PageRequest.of(0, 20, Sort.by("created_at").descending());
Page<User> page = userRepository.findAll(pageRequest);

5. JOIN оптимизация

-- ❌ Плохо: несколько отдельных запросов
SELECT * FROM orders WHERE user_id = 123;
-- Для каждого заказа
SELECT * FROM order_items WHERE order_id = ?;
-- (N+1 problem)

-- ✅ Хорошо: один запрос с JOIN
SELECT o.*, oi.* FROM orders o
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE o.user_id = 123;

В Java с Spring Data:

// ❌ N+1 проблема
@Entity
public class Order {
    @OneToMany(fetch = FetchType.LAZY)  // Lazy загрузка
    private List<OrderItem> items;
}

// Когда получаем заказы, items загружаются отдельно для каждого
List<Order> orders = orderRepository.findAll();
for (Order order : orders) {
    order.getItems();  // Отдельный запрос для каждого заказа!
}

// ✅ Решение 1: JOIN FETCH
@Query("SELECT o FROM Order o LEFT JOIN FETCH o.items WHERE o.userId = :userId")
List<Order> findByUserIdWithItems(@Param("userId") Long userId);

// ✅ Решение 2: Eager загрузка (осторожно!)
@OneToMany(fetch = FetchType.EAGER)
private List<OrderItem> items;

// ✅ Решение 3: DTO projection
@Query("SELECT new com.example.OrderDTO(o.id, o.status) FROM Order o WHERE o.userId = :userId")
List<OrderDTO> findDTOByUserId(@Param("userId") Long userId);

6. WHERE условия оптимизация

-- ❌ Плохо: функция в WHERE (индекс не используется)
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- ✅ Хорошо: индекс как есть
SELECT * FROM users WHERE email = 'user@example.com';

-- ❌ Плохо: вычисления
SELECT * FROM orders WHERE amount * tax > 1000;

-- ✅ Хорошо: фиксированное значение
SELECT * FROM orders WHERE amount > 1000;

-- ❌ Плохо: OR с разными колонками
SELECT * FROM orders WHERE status = 'pending' OR user_id = 123;

-- ✅ Хорошо: если часто нужны оба условия, составной индекс
CREATE INDEX idx_orders_status_user ON orders(status, user_id);

-- ❌ Плохо: LIKE с % в начале (полный скан)
SELECT * FROM products WHERE name LIKE '%phone';

-- ✅ Хорошо: LIKE в конце (может использовать индекс)
SELECT * FROM products WHERE name LIKE 'phone%';

7. Кэширование результатов

@Service
public class UserService {
    
    @Autowired
    private UserRepository userRepository;
    
    // ✅ Результаты кэшируются (первый вызов идёт в БД)
    @Cacheable("users")  // Кэш по названию
    public User getUserById(Long id) {
        return userRepository.findById(id).orElseThrow();
    }
    
    // При обновлении очищаем кэш
    @CacheEvict("users", key = "#id")
    public void updateUser(Long id, UserDTO dto) {
        User user = userRepository.findById(id).orElseThrow();
        user.update(dto);
        userRepository.save(user);
    }
    
    // Кэш для списков
    @Cacheable("user_list")
    public List<User> getAllUsers() {
        return userRepository.findAll();
    }
}

// Конфигурация
@Configuration
@EnableCaching
public class CacheConfig {
    @Bean
    public CacheManager cacheManager() {
        return new ConcurrentMapCacheManager("users", "user_list");
    }
}

8. Materialized Views (Материализованные представления)

Для сложных агрегатов:

-- Создаём материализованное представление (как таблица)
CREATE MATERIALIZED VIEW user_stats AS
SELECT 
    u.id,
    u.email,
    COUNT(o.id) as order_count,
    SUM(o.amount) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.email;

-- Создаём индекс
CREATE INDEX idx_user_stats_id ON user_stats(id);

-- Обновляем view (когда нужно)
REFRESH MATERIALIZED VIEW user_stats;

-- Быстрый запрос
SELECT * FROM user_stats WHERE id = 123;

9. Partitioning (Разделение таблиц)

Для очень больших таблиц (миллиарды строк):

-- Разделение по дате
CREATE TABLE orders (
    id BIGINT,
    user_id BIGINT,
    created_at TIMESTAMPTZ,
    amount DECIMAL
) PARTITION BY RANGE (created_at);

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

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

-- БД автоматически выбирает нужную партицию
SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2024-04-01';

10. Database normalization vs Denormalization

-- ❌ Плохо: денормализованные данные
CREATE TABLE orders_denorm (
    id BIGINT,
    user_email VARCHAR,        -- Копия из users
    user_name VARCHAR,         -- Копия из users
    user_phone VARCHAR,        -- Копия из users
    total_amount DECIMAL       -- Можно посчитать из order_items
);
-- Проблема: дублирование, несогласованность

-- ✅ Хорошо: нормализованные таблицы
CREATE TABLE orders (
    id BIGINT,
    user_id BIGINT REFERENCES users(id),
    created_at TIMESTAMPTZ
);

CREATE TABLE order_items (
    id BIGINT,
    order_id BIGINT REFERENCES orders(id),
    amount DECIMAL
);

-- Если нужна быстрая сумма, используем материализованное представление
CREATE MATERIALIZED VIEW order_totals AS
SELECT order_id, SUM(amount) as total FROM order_items GROUP BY order_id;

11. Connection Pooling и пакетные операции

// ❌ Плохо: отдельный запрос для каждого
for (User user : users) {
    userRepository.save(user);  // 1000 запросов!
}

// ✅ Хорошо: пакетная вставка
userRepository.saveAll(users);  // 1 или несколько пакетов

// Или SQL
INSERT INTO users (name, email) VALUES
('user1', 'user1@example.com'),
('user2', 'user2@example.com'),
('user3', 'user3@example.com');

12. Monitoring и профилирование

// Spring Boot Actuator показывает медленные запросы
spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.use_sql_comments=true

// Логирование медленных запросов
spring.jpa.properties.hibernate.dialect.Oracle11gDialect.log_slow_queries=true
spring.jpa.properties.hibernate.dialect.Oracle11gDialect.slow_query_time_ms=1000

// p6spy для перехвата SQL
@Configuration
public class P6SpyConfiguration {
    @PostConstruct
    public void setLogMessageFormat() {
        P6SpyOptions.getActiveInstance().setLogMessageFormat(
            "com.p6spy.engine.spy.appender.MultiLineFormat"
        );
    }
}

Чеклист оптимизации SELECT

1. Создал индексы? ✓
2. Проверил EXPLAIN ANALYZE? ✓
3. Выбираю только нужные колонки? ✓
4. Использую pagination? ✓
5. Избежал N+1? ✓
6. Функции в WHERE? ✓
7. Кэширование? ✓
8. Партиционирование больших таблиц? ✓
9. Мониторю медленные запросы? ✓