Как создать индекс для колонки с типом int32?

Ответ

Создание индекса для колонки типа INT (в Java соответствует int32) выполняется через SQL команду CREATE INDEX. Есть несколько подходов в зависимости от контекста.

Способ 1: Прямо через SQL (самый простой)

-- Создать обычный индекс на колонке INT
CREATE INDEX idx_user_age ON users(age);

-- Или более полная синтаксис
CREATE INDEX idx_user_age ON users (age ASC);

-- Уникальный индекс (если значения должны быть уникальны)
CREATE UNIQUE INDEX idx_user_phone ON users(phone_number);

-- Multicolumn индекс (поиск по двум колонкам)
CREATE INDEX idx_user_status_age ON users(status, age);

-- Partial индекс (индексируем только активных пользователей)
CREATE INDEX idx_active_users_age ON users(age) WHERE status = 'ACTIVE';

Способ 2: Через Goose Migration (рекомендуется для production)

-- migrations/0001_initial_schema.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    age INTEGER NOT NULL,
    status VARCHAR(50),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- migrations/0002_add_age_index.sql
CREATE INDEX idx_users_age ON users(age);

-- migrations/0003_add_composite_index.sql
CREATE INDEX idx_users_status_age ON users(status, age) WHERE status = 'ACTIVE';

Выполнение миграций:

# Накатить миграции
goose -dir migrations postgres "$DATABASE_URL" up

# Откатить последнюю
goose -dir migrations postgres "$DATABASE_URL" down

# Статус миграций
goose -dir migrations postgres "$DATABASE_URL" status

Способ 3: Через JPA Аннотации (для приложения на Java)

@Entity
@Table(
    name = "users",
    indexes = {
        @Index(name = "idx_user_age", columnList = "age"),
        @Index(name = "idx_user_email", columnList = "email", unique = true),
        @Index(name = "idx_user_status_age", columnList = "status,age")
    }
)
public class User {
    @Id
    private Integer id;
    
    @Column(name = "age")
    private Integer age;  // int32 в Java
    
    @Column(name = "status")
    private String status;
    
    @Column(name = "email")
    private String email;
}

Это автоматически создаст индексы при инициализации схемы (если используешь JPA)

Способ 4: Через JpaRepository Query

@Repository
public interface UserRepository extends JpaRepository<User, Integer> {
    // Spring Data автоматически создаст индекс для поиска по age
    @Query("SELECT u FROM User u WHERE u.age > :age ORDER BY u.age")
    List<User> findUsersByAgeGreaterThan(@Param("age") Integer age);
    
    // Поиск по нескольким колонкам — нужен multicolumn индекс
    @Query("SELECT u FROM User u WHERE u.status = :status AND u.age > :age")
    List<User> findActiveUsersOlderThan(
        @Param("status") String status,
        @Param("age") Integer age
    );
}

Способ 5: Динамическое создание индекса через EntityManager

@Service
public class IndexManagementService {
    
    @PersistenceContext
    private EntityManager entityManager;
    
    public void createAgeIndex() {
        String sql = "CREATE INDEX idx_users_age ON users(age)";
        
        try {
            Session session = entityManager.unwrap(Session.class);
            session.doWork(connection -> {
                try (Statement stmt = connection.createStatement()) {
                    stmt.execute(sql);
                    System.out.println("Index created successfully");
                }
            });
        } catch (Exception e) {
            log.error("Failed to create index", e);
        }
    }
}

Практический пример: Полный проект

// Entity
@Entity
@Table(
    name = "users",
    indexes = {
        @Index(name = "idx_age", columnList = "age"),
        @Index(name = "idx_email", columnList = "email", unique = true),
        @Index(name = "idx_status_age", columnList = "status,age")
    }
)
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    
    @Column(nullable = false)
    private String name;
    
    @Column(nullable = false)
    private Integer age;  // int32
    
    @Column(nullable = false, unique = true)
    private String email;
    
    @Column
    private String status;
    
    @Column(name = "created_at")
    private LocalDateTime createdAt;
}

// Repository
@Repository
public interface UserRepository extends JpaRepository<User, Integer> {
    // Используется idx_age
    List<User> findByAgeGreaterThan(Integer age);
    
    // Используется idx_email
    Optional<User> findByEmail(String email);
    
    // Используется idx_status_age (composite)
    @Query("SELECT u FROM User u WHERE u.status = :status AND u.age > :age")
    List<User> findActiveOlderThan(
        @Param("status") String status,
        @Param("age") Integer age
    );
}

// Service
@Service
public class UserService {
    @Autowired
    private UserRepository repository;
    
    public List<User> getAdultUsers() {
        // Быстро благодаря idx_age
        return repository.findByAgeGreaterThan(18);
    }
    
    public User getUserByEmail(String email) {
        // Очень быстро благодаря idx_email (unique)
        return repository.findByEmail(email).orElseThrow();
    }
    
    public List<User> getActiveAdultUsers() {
        // Быстро благодаря idx_status_age
        return repository.findActiveOlderThan("ACTIVE", 18);
    }
}

// Migration (Goose)
-- migrations/0001_create_users_table.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    age INTEGER NOT NULL,           -- int32
    email VARCHAR(255) NOT NULL UNIQUE,
    status VARCHAR(50),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Создаём индексы
CREATE INDEX idx_users_age ON users(age);
CREATE INDEX idx_users_email ON users(email);  -- Уникальный индекс
CREATE INDEX idx_users_status_age ON users(status, age) WHERE status = 'ACTIVE';

Анализ производительности индексов

Без индекса:

EXPLAIN SELECT * FROM users WHERE age > 18;
-- Результат: Seq Scan (просматривает все 1 миллион строк)
-- Время: 500ms

С индексом:

EXPLAIN SELECT * FROM users WHERE age > 18;
-- Результат: Index Range Scan (использует idx_users_age)
-- Время: 5ms
-- Ускорение: в 100 раз!

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

-- B-Tree (по умолчанию, для числовых типов идеален)
CREATE INDEX idx_age ON users(age);

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

-- BRIN (Block Range Index, для очень больших таблиц)
CREATE INDEX idx_large_age ON users USING BRIN (age);

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

Для INT (int32) используй B-Tree (по умолчанию).

Оптимизация composite индекса

-- ❌ Неправильный порядок
CREATE INDEX idx_bad ON users(age, status);
-- Использоваться будет только для WHERE age = X AND status = Y
-- Для WHERE status = Y AND age > 18 индекс не поможет

-- ✅ Правильный порядок (equality, then range)
CREATE INDEX idx_good ON users(status, age);
-- Теперь WHERE status = 'ACTIVE' AND age > 18 использует индекс
-- Потому что status первый (точное совпадение)

-- ✅ Для частых диапазонных запросов
CREATE INDEX idx_range ON users(age DESC);  -- DESC для ORDER BY age DESC

Мониторинг индексов

-- Показать все индексы таблицы
SELECT * FROM pg_indexes WHERE tablename = 'users';

-- Размер индекса
SELECT 
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_indexes i
JOIN pg_stat_user_indexes s ON i.indexname = s.indexrelname
WHERE tablename = 'users'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Какие индексы НЕ используются
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan  -- Количество использований
FROM pg_stat_user_indexes
WHERE idx_scan = 0  -- Не используется вообще
ORDER BY pg_relation_size(indexrelid) DESC;

-- Удалить неиспользуемый индекс
DROP INDEX idx_unused_age;

Лучшие практики

✅ Создавай индексы для колонок в WHERE
✅ Для JOINов индексируй foreign keys
✅ Для ORDER BY и GROUP BY индексируй колонки
✅ Multicolumn индексы: сначала equality, потом range
✅ Уникальные индексы для уникальных данных (email, phone)
✅ Partial индексы для условных запросов
✅ Периодически удаляй неиспользуемые индексы
❌ Не создавай индекс на каждую колонку
❌ Не используй индексы на маленьких таблицах (< 1000 строк)
❌ Каждый индекс замедляет INSERT/UPDATE/DELETE

Итог

Для создания индекса на INT (int32) колонку:

1. В production → используй Goose migration:

   CREATE INDEX idx_users_age ON users(age);
   

2. В разработке → используй JPA аннотации:

   @Table(indexes = {@Index(name = "idx_age", columnList = "age")})
   

3. Проверяй효율 через EXPLAIN и pg_stat_user_indexes

4. Помни: индекс ускоряет SELECT, но замедляет INSERT/UPDATE