Alexander Abakumov
15e744da49
- Added comprehensive course structure with 11 modules - Included theoretical content, practical examples, and tasks - Multi-language code examples (Java, Rust, Node.js, Python) - Detailed analysis of architectural approaches and trade-offs - Complete caching strategies documentation - Architecture examples from real-world systems (Twitter, Netflix, Uber) - Structured navigation and learning path - Ready for educational use
146 lines
4.3 KiB
Markdown
146 lines
4.3 KiB
Markdown
# Оптимизация базы данных
|
|
|
|
## Навигация
|
|
- [🏠 Главная страница курса](../../../README.md)
|
|
- [📚 Модуль 10](../README.md)
|
|
|
|
---
|
|
|
|
## Оптимизация запросов
|
|
|
|
### 1. Query Optimization
|
|
```sql
|
|
-- Оптимизация запросов
|
|
-- Плохо: медленный запрос
|
|
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
|
|
```java
|
|
// Настройка пула соединений
|
|
@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
|
|
```java
|
|
// 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
|
|
```sql
|
|
-- Оптимизация индексов
|
|
-- Анализ использования индексов
|
|
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
|
|
```sql
|
|
-- Анализ планов выполнения
|
|
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';
|
|
```
|