Оптимизация баз данныхОптимизация баз данныхПринципы, методики, техникиПринципы, методики, техники

Денис Бесков-ДоронинДенис Бесков-ДоронинАрхитектор ПО, Системный аналитикАрхитектор ПО, Системный аналитикUML2.ruUML2.ru

rit2007@beskov.rurit2007@beskov.ru

www.rit2007.ru

Обзор презентацииОбзор презентации

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

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

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

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

• Методы оптимизации

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

www.rit2007.ru

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

www.rit2007.ru

Внешние свойстваВнешние свойства

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

• Масштабируемость

• Надёжность

www.rit2007.ru

Внутренние свойстваВнутренние свойства

• Модифицируемость

www.rit2007.ru

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

www.rit2007.ru

Для IT-специалистовДля IT-специалистов

• Время отклика– Response

• Пропускная способность– Throughput– Bandwidth

www.rit2007.ru

Для бизнес-пользователейДля бизнес-пользователей

• Субъективное время реакции системы

www.rit2007.ru

Для владельцаДля владельца

• Количество обрабатываемых бизнес-операций в единицу времени

• Количество одновременно работающих пользователей

• Степень удовлетворённости пользователей

www.rit2007.ru

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

www.rit2007.ru

ПроактивноПроактивно

• При проектировании, построении системы– Make It Run, Make It Right, Make It Fast– Classic Engineering

• Performance Planning• Capacity Planning• Reliability Planning

– Кто: Системный архитектор• Плановая

– Профилактика– Предупреждение

• Мониторинг– Кто: Администрор БД / Системы

www.rit2007.ru

РеактивноРеактивно

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

www.rit2007.ru

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

www.rit2007.ru

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

www.rit2007.ru

Обзор типовой архитектуры Обзор типовой архитектуры многослойной системымногослойной системы

• Интерфейс, Внешние системы

• Глобальная сеть• Сервисный слой

(логика приложения)• Локальная сеть• Бизнес-логика• Логика доступа к данным

– Структура запросов• Интерфейс доступа к БД

– ODBC– Custom API

• Модель данных– Логическая модель

данных– Физическая модель

данных• СУБД

– Выделяемый пул– Режим работы

• ОС– Процессы

• Оборудование– Память– Диски– Процессор– Шина

www.rit2007.ru

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

www.rit2007.ru

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

www.rit2007.ru

Сокращение пути выполнения Сокращение пути выполнения запросовзапросов

• Don’t Repeat Yourself– Кэширование

www.rit2007.ru

Сокращение времениСокращение времени выполнения выполнения отдельнойотдельной операцииоперации

• Распараллеливание(Увеличение числа обработчиков)

www.rit2007.ru

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

www.rit2007.ru

Наивный экстенсивНаивный экстенсив

• Процессор

• Память

• Диски

www.rit2007.ru

"Метод научного тыка""Метод научного тыка"

• Содержание

• Проблема эффективности– Ищем где светло– Эффект от неважного до отрицательного

www.rit2007.ru

Метод RМетод R

• Aligning IT and business

• Цель Голдратта– "Слабое звено"– Теория ограничений– Сценарий оптимизации

• Identify• Profile• ROI-based choosing• Tune

www.rit2007.ru

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

www.rit2007.ru

Логика приложенияЛогика приложения

• Кэширование– HTTP-кэширование– Кэширование шаблонов– Кэш объектов– Сетевое кэширование

• "Ленивое" чтение– Минимум датасетов (.NET)

• Плоские файлы• Prefetching

www.rit2007.ru

Уровень доступа к данным (DAL)Уровень доступа к данным (DAL)

• Стратегия доступа

• Настройка SQL

www.rit2007.ru

Стратегия доступаСтратегия доступа

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

доступа• Временные таблицы

www.rit2007.ru

Настройка SQLНастройка SQL

• Кэширование запроса

• Кэширование результатов запроса

• Правильное использование SQL

• Минимизация объёма данных

• Политика блокировок

• Использование специфических свойств СУБД

• Использование оптимизатора

• Пакетная обработка

• Операторы

www.rit2007.ru

Кэширование запросаКэширование запроса

• Связанные переменные(Параметризация запросов)

– + Безопасность!

– Порядок выполнения SQL-запроса

• Синтаксический разбор

• Лексический разбор

• Проверка прав доступа

• Построение план выполнения

• Доступ к данным

• Извлечение

• Минимизация динамического SQL

www.rit2007.ru

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

• Операции над множествами VS циклы

www.rit2007.ru

Минимизация объёма данныхМинимизация объёма данных

• Минимизация проекции:Явное перечисление столбцовVS квантор *

• Минимизация выборки (WHERE)

• Кэширование результатов запроса

www.rit2007.ru

Политика блокировокПолитика блокировок

• Ослабление политики

• Частые фиксации транзакций

www.rit2007.ru

Использование специфических Использование специфических свойств СУБДсвойств СУБД

• Oracle Hierarchical Query

• TOP command

• LIMIT

• GROUP_CONCAT

www.rit2007.ru

Использование оптимизатораИспользование оптимизатора

• Читаемая структура(ANSI JOINs)

• Подсказки

• Сбор статистики

www.rit2007.ru

ПодсказкиПодсказки

• Порядок обхода таблиц– Кардинальность и селективность каждой

таблицы

• Исключение индексов• Метод соединения

– Вложенные циклы• Есть индексы• Результат необъёмен

– Сортировка слиянием

www.rit2007.ru

Пакетная обработкаПакетная обработка

• Отключение индексов, ключей и ограничений

• Загрузка и удаление данных кусками, а не целиком

www.rit2007.ru

ОператорыОператоры

• UNION VS UNION ALL

www.rit2007.ru

Модель БДМодель БД

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

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

www.rit2007.ru

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

• Денормализация

• Истинный ERM VS ORM

• Логическое секционирование

• Политика удаления

www.rit2007.ru

ДенормализацияДенормализация

• Хранимые представления только для чтения– Materialized View– Агрегаты– Соединения– Производные атрибуты

• Обновления– Автоматически

• Триггер• Систематическая задача• MV Refresh

– Явно• Замена кода типа булевыми признаками

www.rit2007.ru

Логическое секционированиеЛогическое секционирование

• Вертикальное

• Горизонтальное– По таблицам– По разным хостам (хэш)

www.rit2007.ru

Политика удаленияПолитика удаления

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

www.rit2007.ru

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

• Индексы– Минимально необходимые– На внешние ключи– С высокой селективностью– Правильный порядок столбцов в

составных индексах– Исключение дублей

• Секционирование– По времени– По региону– По имени

• Минимизация накладных расходов– Удаление индексов и ограничений– Отключение / Выключение индексов

и ограничений

• Кластеризация

• Распределение файлов данных и индексов

• Первичные ключи– Замена составных ключей

суррогатными

• DB Engine– In-memory

• Типы данных– Минимум длины– NOT NULL

www.rit2007.ru

Настройка СУБДНастройка СУБД

• Распределение файлов БД и журналов

• Буферы памяти

• Ограничение пула соединений

• Репликация

• Просмотр системых представлений

www.rit2007.ru

Файловая системаФайловая система

• RAID

• SAN

• Несколько дисков

www.rit2007.ru

Интерфейс доступаИнтерфейс доступа

• Native interfaces against ODBC

to be continued…

www.rit2007.ru

ВопросыВопросы?? Контакты Контакты!!

Приходите общаться!

Сообщество IT-аналитиков, архитекторов и консультантов UML2.ru:

• «Системный анализ и требования»• «Управление проектами»• «Проектирование систем»

Денис Бесков-ДоронинICQ 631.9839

LJ user beskov