sa-lessons/modules/module10/sections/03-database-optimization.md

Оптимизация базы данных

Навигация

• 🏠 Главная страница курса
• 📚 Модуль 10

---

Оптимизация запросов

1. Query Optimization

-- Оптимизация запросов
-- Плохо: медленный запрос
SELECT * FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE u.created_at > '2023-01-01' 
AND o.status = 'COMPLETED';

-- Хорошо: оптимизированный запрос
SELECT u.id, u.name, u.email, COUNT(o.id) as order_count
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE u.created_at > '2023-01-01' 
AND o.status = 'COMPLETED'
GROUP BY u.id, u.name, u.email;

-- Хорошо: использование индексов
CREATE INDEX idx_user_created_at ON users(created_at);
CREATE INDEX idx_order_status ON orders(status);
CREATE INDEX idx_order_user_status ON orders(user_id, status);

2. Connection Pooling

// Настройка пула соединений
@Configuration
public class DatabaseConfig {
    @Bean
    public DataSource dataSource() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:postgresql://localhost:5432/mydb");
        config.setUsername("user");
        config.setPassword("password");
        
        // Оптимизация пула соединений
        config.setMaximumPoolSize(20);
        config.setMinimumIdle(5);
        config.setConnectionTimeout(30000);
        config.setIdleTimeout(600000);
        config.setMaxLifetime(1800000);
        config.setLeakDetectionThreshold(60000);
        
        return new HikariDataSource(config);
    }
}

3. Batch Operations

// Batch операции
@Repository
public class OptimizedUserRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    // Batch вставка
    public void saveUsers(List<User> users) {
        String sql = "INSERT INTO users (name, email, created_at) VALUES (?, ?, ?)";
        
        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                User user = users.get(i);
                ps.setString(1, user.getName());
                ps.setString(2, user.getEmail());
                ps.setTimestamp(3, Timestamp.valueOf(user.getCreatedAt()));
            }
            
            @Override
            public int getBatchSize() {
                return users.size();
            }
        });
    }
    
    // Batch обновление
    public void updateUsers(List<User> users) {
        String sql = "UPDATE users SET name = ?, email = ? WHERE id = ?";
        
        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                User user = users.get(i);
                ps.setString(1, user.getName());
                ps.setString(2, user.getEmail());
                ps.setLong(3, user.getId());
            }
            
            @Override
            public int getBatchSize() {
                return users.size();
            }
        });
    }
}

Настройка базы данных

1. Index Optimization

-- Оптимизация индексов
-- Анализ использования индексов
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';

-- Создание составных индексов
CREATE INDEX idx_user_email_status ON users(email, status);

-- Создание частичных индексов
CREATE INDEX idx_active_users ON users(email) WHERE status = 'ACTIVE';

-- Создание функциональных индексов
CREATE INDEX idx_user_lower_email ON users(LOWER(email));

2. Query Plan Analysis

-- Анализ планов выполнения
EXPLAIN (ANALYZE, BUFFERS) 
SELECT u.name, COUNT(o.id) as order_count
FROM users u 
LEFT JOIN orders o ON u.id = o.user_id 
WHERE u.created_at > '2023-01-01'
GROUP BY u.id, u.name;

-- Анализ статистики
ANALYZE users;
ANALYZE orders;

-- Обновление статистики
UPDATE pg_stat_user_tables 
SET n_tup_ins = 0, n_tup_upd = 0, n_tup_del = 0 
WHERE relname = 'users';