Оптимизация баз данных Принципы, методики, техники Денис Бесков-Доронин Архитектор ПО, Системный аналитик

Обзор презентации Что оптимизировать? Что такое производительность? Когда выполняется оптимизация? Принципы оптимизации Методы оптимизации Техники оптимизации

Что оптимизировать?

Внешние свойства Производительность Масштабируемость Надёжность

Внутренние свойства Модифицируемость

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

Для IT-специалистов Время отклика –Response Пропускная способность –Throughput –Bandwidth

Для бизнес-пользователей Субъективное время реакции системы

Для владельца Количество обрабатываемых бизнес- операций в единицу времени Количество одновременно работающих пользователей Степень удовлетворённости пользователей

Когда выполняется оптимизация?

Проактивно При проектировании, построении системы –Make It Run, Make It Right, Make It Fast –Classic Engineering Performance Planning Capacity Planning Reliability Planning –Кто: Системный архитектор Плановая –Профилактика –Предупреждение Мониторинг –Кто: Администрор БД / Системы

Реактивно В экстренном порядке –"Найти и обезвредить" –Кто: Аналитик по производительности

Почему именно БД?

Архитектура запроса в системе

Обзор типовой архитектуры многослойной системы Интерфейс, Внешние системы Глобальная сеть Сервисный слой (логика приложения) Локальная сеть Бизнес-логика Логика доступа к данным –Структура запросов Интерфейс доступа к БД –ODBC –Custom API Модель данных –Логическая модель данных –Физическая модель данных СУБД –Выделяемый пул –Режим работы ОС –Процессы Оборудование –Память –Диски –Процессор –Шина

Путь выполнения запроса

Принципы оптимизации

Сокращение пути выполнения запросов Dont Repeat Yourself –Кэширование

Сокращение времени выполнения отдельной операции Распараллеливание (Увеличение числа обработчиков)

Методики оптимизации

Наивный экстенсив Процессор Память Диски

"Метод научного тыка" Содержание Проблема эффективности –Ищем где светло –Эффект от неважного до отрицательного

Метод R Aligning IT and business Цель Голдратта –"Слабое звено" –Теория ограничений –Сценарий оптимизации Identify Profile ROI-based choosing Tune

Техники оптимизации

Логика приложения Кэширование –HTTP-кэширование –Кэширование шаблонов –Кэш объектов –Сетевое кэширование "Ленивое" чтение –Минимум датасетов (.NET) Плоские файлы Prefetching

Уровень доступа к данным (DAL) Стратегия доступа Настройка SQL

Стратегия доступа Индексированный доступ ORM Хранимые процедуры Последовательный доступ Представления Применение образцов проектирования доступа Временные таблицы

Настройка SQL Кэширование запроса Кэширование результатов запроса Правильное использование SQL Минимизация объёма данных Политика блокировок Использование специфических свойств СУБД Использование оптимизатора Пакетная обработка Операторы

Кэширование запроса Связанные переменные (Параметризация запросов) –+ Безопасность! –Порядок выполнения SQL-запроса Синтаксический разбор Лексический разбор Проверка прав доступа Построение план выполнения Доступ к данным Извлечение Минимизация динамического SQL

Правильное использование SQL Операции над множествами VS циклы

Минимизация объёма данных Минимизация проекции: Явное перечисление столбцов VS квантор * Минимизация выборки (WHERE) Кэширование результатов запроса

Политика блокировок Ослабление политики Частые фиксации транзакций

Использование специфических свойств СУБД Oracle Hierarchical Query TOP command LIMIT GROUP_CONCAT

Использование оптимизатора Читаемая структура (ANSI JOINs) Подсказки Сбор статистики

Подсказки Порядок обхода таблиц –Кардинальность и селективность каждой таблицы Исключение индексов Метод соединения –Вложенные циклы Есть индексы Результат необъёмен –Сортировка слиянием

Пакетная обработка Отключение индексов, ключей и ограничений Загрузка и удаление данных кусками, а не целиком

Операторы UNION VS UNION ALL

Модель БД Логическая модель БД Физическая модель БД

Логическая модель БД Денормализация Истинный ERM VS ORM Логическое секционирование Политика удаления

Денормализация Хранимые представления только для чтения –Materialized View –Агрегаты –Соединения –Производные атрибуты Обновления –Автоматически Триггер Систематическая задача MV Refresh –Явно Замена кода типа булевыми признаками

Логическое секционирование Вертикальное Горизонтальное –По таблицам –По разным хостам (хэш)

Политика удаления Замена мягкого удаления жёстким

Физическая модель БД Индексы –Минимально необходимые –На внешние ключи –С высокой селективностью –Правильный порядок столбцов в составных индексах –Исключение дублей Секционирование –По времени –По региону –По имени Минимизация накладных расходов –Удаление индексов и ограничений –Отключение / Выключение индексов и ограничений Кластеризация Распределение файлов данных и индексов Первичные ключи –Замена составных ключей суррогатными DB Engine –In-memory Типы данных –Минимум длины –NOT NULL

Настройка СУБД Распределение файлов БД и журналов Буферы памяти Ограничение пула соединений Репликация Просмотр системых представлений

Файловая система RAID SAN Несколько дисков

Интерфейс доступа Native interfaces against ODBC to be continued…

Вопросы? Контакты! Приходите общаться! Сообщество IT-аналитиков, архитекторов и консультантов UML2.ru: «Системный анализ и требования» «Управление проектами» «Проектирование систем» Денис Бесков-Доронин ICQ LJ user beskov