Что-то может поменяться в случае создания индекса с течением времени

Изменения при создании индекса с течением времени

Индекс в базе данных — это структура данных, которая обеспечивает быстрый поиск записей. Однако создание и поддержка индекса сопряжены с рядом динамических изменений и влияний на базу данных, которые развиваются во времени.

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

С течением времени при добавлении данных в таблицу производительность может измениться:

-- Таблица с индексом на поле email
CREATE INDEX idx_users_email ON users(email);

-- Изначально индекс очень быстро находит записи
SELECT * FROM users WHERE email = 'john@example.com';

-- Со временем, при росте количества записей,
-- производительность может деградировать, если индекс не оптимален

Деградация производительности

1. Фрагментация индекса — после множества INSERT/UPDATE/DELETE операций индекс может фрагментироваться

-- Проверка фрагментации (PostgreSQL)
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE indexname = 'idx_users_email';

-- Переиндексирование для оптимизации
REINDEX INDEX idx_users_email;

1. Изменение распределения данных — индекс может стать менее эффективным при изменении паттернов данных

# Пример: индекс хорошо работает для均匀распределения
# но может стать неэффективным для skewed данных

# Изначально — данные均匀распределены
users = [
    {'id': 1, 'status': 'active', 'score': 50},
    {'id': 2, 'status': 'active', 'score': 45},
    {'id': 3, 'status': 'inactive', 'score': 20},
]

# Со временем — 99% пользователей со статусом 'active'
# Индекс на status становится менее эффективным

Использование памяти и дискового пространства

# PostgreSQL отслеживает размер индекса
import psycopg2

conn = psycopg2.connect("dbname=mydb user=postgres")
cur = conn.cursor()

# Размер индекса растёт с течением времени
query = """
    SELECT 
        schemaname,
        tablename,
        indexname,
        pg_size_pretty(pg_relation_size(indexrelid)) as index_size
    FROM pg_indexes
    JOIN pg_class ON pg_class.relname = pg_indexes.indexname
    WHERE tablename = 'users';
"""

cur.execute(query)
for row in cur.fetchall():
    print(f"Индекс {row[2]}: {row[3]}")

conn.close()

Индекс занимает значительное дисковое пространство:

• 1 млн записей может потребовать 50-100 MB на индекс
• 1 млрд записей может потребовать 50-100 GB

Стоимость операций написания (INSERT, UPDATE, DELETE)

С течением времени индекс может замедлить операции написания:

-- Операция INSERT становится дороже из-за обновления индекса
INSERT INTO users (id, email, name) 
VALUES (1000001, 'new_user@example.com', 'New User');

-- База данных должна:
-- 1. Добавить запись в таблицу
-- 2. Обновить индекс на email
-- 3. Обновить индекс на id (PRIMARY KEY)
-- 4. Обновить другие индексы

# Пример замера времени операций с индексом
import time
import psycopg2

conn = psycopg2.connect("dbname=mydb user=postgres")
cur = conn.cursor()

# Без индекса
start = time.time()
for i in range(100000):
    cur.execute(
        "INSERT INTO users (email, name) VALUES (%s, %s)",
        (f"user{i}@example.com", f"User{i}")
    )
conn.commit()
print(f"Без индекса: {time.time() - start:.2f} сек")

# С индексом
start = time.time()
for i in range(100000):
    cur.execute(
        "INSERT INTO users (email, name) VALUES (%s, %s)",
        (f"user{i+100000}@example.com", f"User{i+100000}")
    )
conn.commit()
print(f"С индексом: {time.time() - start:.2f} сек")

conn.close()

Статистика индекса

Постгресол собирает статистику, которая влияет на планы запросов:

-- Статистика использования индекса
SELECT 
    schemaname,
    tablename, 
    indexname,
    idx_scan,           -- Сколько раз индекс сканировался
    idx_tup_read,       -- Сколько кортежей прочитано
    idx_tup_fetch       -- Сколько кортежей получено
FROM pg_stat_user_indexes
WHERE tablename = 'users';

-- Если idx_scan = 0, то индекс не используется совсем!

Нужно ли переиндексировать?

С течением времени индекс может потребовать обслуживания:

-- Проверка необходимости переиндексирования
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_blks_read,  -- Физические чтения блоков индекса
    idx_blks_hit    -- Попадания в кеш
FROM pg_statio_user_indexes;

-- Если много idx_blks_read — индекс плохо кэшируется
-- Вероятно, нужна переиндексация

-- Переиндексирование таблицы
REINDEX TABLE CONCURRENTLY users;  -- Не блокирует таблицу

Влияние на план запроса

-- Со временем оптимайзер БД может выбрать другой план
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'john@example.com';

-- Может быть:
-- 1. Index Scan (если индекс очень эффективен)
-- 2. Sequential Scan (если индекс малополезен)
-- 3. Bitmap Index Scan (компромисс)

Практические примеры изменений

Пример 1: Индекс становится менее полезным

# Изначально индекс на is_active хорошо работает
# 50% активных пользователей, 50% неактивных
SELECT COUNT(*) FROM users WHERE is_active = True;  # Быстро

# Со временем
# 99% активных пользователей, 1% неактивных
# Индекс становится менее полезным для поиска активных
# но очень полезен для поиска неактивных
SELECT COUNT(*) FROM users WHERE is_active = False;  # Быстро
SELECT COUNT(*) FROM users WHERE is_active = True;   # Может быть медленнее

Пример 2: Индекс никогда не используется

# Создали индекс
CREATE INDEX idx_users_country ON users(country);

# Со временем выяснили, что эту колонку никто не ищет
# Индекс только замедляет операции записи и занимает место

# Решение: удалить неиспользуемый индекс
DROP INDEX idx_users_country;

Best Practices

1. Мониторь использование индексов — удаляй неиспользуемые
2. Регулярная переиндексация — раз в месяц/год в зависимости от нагрузки
3. Анализ планов запросов — используй EXPLAIN для оптимизации
4. Не создавай индексы "на всякий случай" — каждый индекс имеет стоимость
5. Рассмотри индексы на колонках, которые часто используются в WHERE, JOIN, ORDER BY

Выводы

С течением времени при создании индекса могут измениться:

• Производительность запросов (улучшается или деградирует)
• Стоимость операций записи (возрастает)
• Использование памяти и дискового пространства (растёт)
• Эффективность индекса (зависит от данных)
• Необходимость обслуживания (переиндексация, удаление неиспользуемых)