Привет, Вы узнаете о том , что такое производительность баз данных, Разберем основные их виды и особенности использования. Еще будет много подробных примеров и описаний. Для того чтобы лучше понимать что такое
производительность баз данных, оптимизация sql , настоятельно рекомендую прочитать все из категории Базы данных, знаний и хранилища данных. Big data, СУБД и SQL и noSQL.
я перечисляю и объясняю здесь некоторые из наиболее распространенных мифов и заблуждений.
Миф 1 - Индексы могут вырождаться
Самый распространенный миф заключается в том, что индекс через некоторое время может выродиться и его необходимо регулярно перестраивать. Прежде всего, база данных всегда сохраняет баланс дерева . Невозможно, чтобы отдельный фрагмент индекса становился все глубже и глубже, пока обход дерева не стал медленным. Может случиться так, что индекс станет больше, чем нужно. Если имеется много заявлений UPDATE или, DELETE использование пространства может стать неоптимальным. Однако даже если индекс больше, чем требуется, очень маловероятно, что глубина индекса из-за этого вырастет. Как пояснили в « The Search Tree (B-Tree) Делает индекс Fast », количество записей в индексе обычно должно увеличиваться в сто раз, чтобы увеличить глубину индекса на один уровень.
Перестройка индекса может уменьшить количество конечных узлов примерно на 20–30%. Наиболее возможно , вы можете ожидать от этого снижения на 20% -30% для очень дорогих операций , как у FULL INDEX SCAN. Типичный INDEX UNIQUE SCAN выигрыш от перестроения индекса составляет 0% -2%, поскольку глубина индекса не уменьшается при перестроении.
Миф 2 - Самый селективных индекс- первый в составном индексе
Каждый раз, когда создается составной индекс, необходимо с умом выбирать порядок столбцов. Этому вопросу посвящена статья « составной индекс » .
Однако существует миф, что вы всегда должны ставить наиболее избирательную колонку на первую позицию; это просто неправильно.
Важно
Наиболее важным соображением при определении конкатенированного индекса является выбор порядка столбцов, чтобы его можно было использовать как можно чаще.
После этого есть даже причины поставить на первое место наименее избирательный столбец. База данных Oracle может, например, INDEX SKIP SCANв этом случае использовать. Но это дополнительная функция. Самый важный фактор ... ммм, я уже говорил это раньше?
Истинная суть мифа связана с индексированием условий независимого диапазона - это единственный случай, когда избирательность должна влиять на дизайн индекса
Этот миф чрезвычайно устойчив в среде SQL Server и встречается даже в официальной документации. Причина в том, что SQL Server хранит гистограмму только для первого столбца индекса. Но это означает, что рекомендация должна читаться как «сначала неравномерно распределенные столбцы», потому что гистограммы в любом случае не очень полезны для равномерно распределенных столбцов.
Я не первый, кто борется с этим мифом. Вот еще несколько ссылок, опровергающих этот миф:
Не ставьте автоматически наиболее избирательный термин первым в составном указателе.
- Гай Харрисон в « Руководстве по выживанию производительности Oracle »
Одной из часто цитируемых сказок об индексах была директива «ставить на первое место самый избирательный столбец». Это никогда не было разумным практическим правилом (кроме, возможно, до версии 6.0).
- Джонатан Льюис в « Блокноте Oracle »
Бесполезно располагать наиболее избирательный столбец индекса слева, если по нему фильтруется очень мало запросов. Запросы, которые не фильтруют по нему, но фильтруют по другим столбцам индекса, придется сканировать, а сканирование стоит дорого.
- Гейл Шоу в « SQL (Server) в дикой природе »
Миф 3 - База данных Oracle не может индексировать NULL
Источник этого мифа довольно легко понять, если взглянуть на правильно сформулированное утверждение:
База данных Oracle не включает строки в индекс, если все индексированные столбцы включены NULL.
Разница между мифом и реальностью невелика - кажется, что миф - это небрежная форма истины.
Дело в том, что NULL можно проиндексировать, добавив в индекс еще один столбец, не допускающий значения NULL:
Миф 4 - Динамический SQL медленный
Истинная суть мифа « Dynamic SQL is Slow » довольно проста; динамический SQL может быть медленным - если он сделан неправильно.
Проблема в том, что динамический SQL часто используется не по той причине, иногда даже не зная. Чтобы прояснить путаницу, я буду использовать следующие термины, как объяснено:
Встроенный SQL
Встраивание SQL непосредственно в исходный код программы очень распространено в языках процедурных баз данных, таких как Oracle PL / SQL или Microsoft Transact-SQL. Также возможно встраивать SQL в другие языки, такие как C.
Преимущество встроенного SQL - плавная интеграция с соответствующим языком программирования. Однако встроенный SQL компилируется в программу. Он не может изменить среду выполнения - он статичен.
Динамический SQL
Динамический SQL обрабатывается в приложении как строка. Приложение может изменить строку SQL во время выполнения, прежде чем передать ее на уровень базы данных. На самом деле это наиболее распространенный способ доступа к базам данных.
Статический SQL
Я использую термин статический SQL для описания операторов SQL, которые не изменяются во время выполнения. Независимо от того, это встроенный SQL, который не может измениться во время выполнения, или динамический SQL, который может измениться, но не может.
Суть этих определений в том, что оператор может быть динамическим и статическим SQL одновременно. Другими словами, существуют разные уровни динамического SQL. Рассмотрим следующий пример:
Это динамический SQL? Согласно приведенному выше определению, это так. Оператор SQL подготавливается в виде строки и передается на уровень базы данных. Но разве это статический SQL? Если предположить, что значение employeeIdпеременной изменяется, это не статический SQL, поскольку строка SQL изменяется во время выполнения. Это пример динамического SQL, который действительно снижает производительность. Проблема не в том, что это динамический SQL, а в том, что он не использует переменные связывания . Связанные переменные SQL - например, вопросительный знак ?или :name- являются заполнителями для значений, которые изменяются во время выполнения. Это означает, что пример можно преобразовать в статический SQL, используя переменную связывания вместо фактического значения employeeIdпеременной.
Важно
Не с помощью связывания параметров злоупотребляют динамический SQL.
Параметры привязки очень важны для безопасности и производительности.
Разумным использованием динамического SQL является изменение структуры оператора во время выполнения. Это то, что нельзя сделать с параметрами привязки . Например, условное whereпредложение:
Код создает оператор SQL для выборки сотрудников на основе любой комбинации трех критериев фильтрации. Об этом говорит сайт https://intellect.icu . Хотя это довольно неудобно, построенный SQL может быть выполнен с использованием наилучшего доступного индекса. Тем не менее, этот подход проблематичен из-за возможной уязвимости SQL-инъекции и больших накладных расходов на оптимизацию: база данных должна каждый раз воссоздавать план выполнения, потому что условия поиска - которые каждый раз могут быть разными - предотвращают кэширование. « Параметризованные запросы » подробно объясняют накладные расходы на оптимизацию. Опять же, динамический SQL не является проблемой, но не использование параметров привязки.
Пример, который where динамически строит предложение и использует параметры связывания, опущен, потому что он еще более неудобен, чем пример выше. Однако большинство фреймворков ORM предлагают достаточно удобный способ динамического создания SQL с использованием параметров связывания. В следующем обзоре показаны некоторые образцы:
PHP
В следующем примере демонстрируется структура PHP Doctrine :
Perl
Java
В следующем примере демонстрируются классы критериев Hibernate :
Обратите внимание, что используется параметр привязки и LOWERфункция для реализации функции ignoreCase () . То же самое и с ограничением ilike . Это очень важный факт для индексирования на основе функций .
API Java Persistence (JPA) имеет аналогичную функциональность:
Однако он менее прямой и не поддерживает собственный поиск без учета регистра, что, вероятно, хорошо):
Вы видите, что пример менее прямолинеен в пользу безопасности типов во время компиляции. Еще одно отличие состоит в том, что JPA не поддерживает собственные операторы без учета регистра - требуется явное преобразование регистра. Наверное, хорошо знать и контролировать это. Просто в качестве примечания; собственный Hibernate API также поддерживает явное преобразование регистра.
Doctrine генерирует следующий запрос SQL для поиска по фамилии (MySQL):
Использование динамического SQL с параметрами связывания позволяет оптимизатору выбрать лучший план выполнения для конкретной комбинации whereпредложений. Это даст лучшую производительность, чем конструкции, подобные описанным в « Умной логике » :
Причина того, что динамический SQL работает медленно, очень часто не в использовании параметров связывания, то есть в использовании динамического SQL по неправильной причине.
Однако есть некоторые - я бы сказал, редкие - случаи, когда динамический SQL может быть медленнее, чем «умная логика», как указано выше. Это когда очень дешевые (быстрые) операторы SQL выполняются с очень высокой частотой. Но прежде всего нужно объяснить еще два термина:
Жесткий анализ
Жесткий синтаксический анализ - это построение плана выполнения на основе оператора SQL. Это серьезное усилие; проверка всех частей SQL; с учетом всех показателей; учитывая все заявки на присоединение и так далее. Жесткий разбор очень ресурсоемкий.
Мягкий анализ
Мягкий синтаксический анализ - это поиск, нахождение и использование кэшированного плана выполнения. Выполняются некоторые незначительные проверки, например, права доступа, но план выполнения можно повторно использовать как есть. Это довольно быстрая операция.
Ключ к кеш-памяти - это, по сути, буквальная строка SQL - обычно ее хеш. Если нет точного совпадения, запускается жесткий синтаксический анализ. Вот почему встроенные литералы - в отличие от параметров привязки - вызывают жесткий синтаксический анализ, если те же самые условия поиска не используются снова. Но даже в этом случае есть хорошие шансы, что предыдущий план выполнения уже истек из кеша, потому что новые появляются снова и снова.
Однако есть способ выполнить оператор вообще без синтаксического анализа - даже без мягкого анализа. Уловка состоит в том, чтобы держать проанализированный оператор открытым, например, как в следующем псевдокоде Java:
Обратите внимание, что PreparedStatementоткрывается и закрывается только один раз, но его можно выполнять много раз. Это означает, что существует только одна операция синтаксического анализа - во время подготовки - но не внутри цикла.
Ошибка в том, что преобразование оператора в динамический SQL перемещает prepareStatementвызов в цикл, вызывая мягкий синтаксический анализ для каждого выполнения. Накладные расходы на синтаксический анализ, которые могут также включать задержки в сети, могут превышать экономию от лучшего плана выполнения, когда оператор выполняется часто и в любом случае выполняется быстро. Это особенно верно, если фактический план выполнения не меняется для разных предложений where - например, потому что whereвсегда присутствует одно хорошо проиндексированное предложение.
Несмотря на то, что трюк «подготовка перед циклом» редко используется явно, он очень распространен в хранимых процедурах, но неявно. Такие языки, как PL / SQL - с настоящим статическим SQL - подготавливают SQL при компиляции процедуры или, самое большее, один раз за выполнение. Изменение этого на динамический SQL может легко убить производительность.
Миф 5 : Select * - это плохо
Это один из самых стойких мифов, которые я видел в этой области. Он существует десятилетиями. Если миф существует так долго, в нем должна быть доля правды. Итак, что может быть плохого select *? Давайте посмотрим поближе.
Все мы знаем, что выбор «*» - это всего лишь сокращение для выбора всех столбцов. Вы не поверите, но для многих это имеет большое значение. Итак, давайте сначала перефразируем вопрос, используя этот «вывод»:
Почему плохо выбирать все столбцы?
На самом деле, есть несколько очень веских причин, по которым нельзя выбирать все столбцы, если они вам не нужны. И все они сводятся к производительности. Однако удивительно то, что влияние на производительность может быть огромным.
До 100 раз медленнее при предотвращении сканирования только индекса
Вообще говоря, чем меньше столбцов вы запрашиваете, тем меньше данных необходимо загружать с диска при обработке вашего запроса. Однако эта зависимость нелинейна .
Довольно часто выбор из таблицы включает два шага: (1) использование индекса для поиска адреса, по которому хранятся выбранные строки; (2) загрузить выбранные строки из таблицы. Теперь представьте, что вы просто выбираете столбцы, которые присутствуют в индексе. Почему база данных все же должна выполнять второй шаг? Фактически, большинство баз данных этого не делают. Они могут обрабатывать ваш запрос только с информацией, хранящейся в индексе - следовательно, сканирование только индекса .
Но почему сканирование только индекса должно быть в 100 раз быстрее? Просто: в идеальном индексе выбранные строки хранятся рядом друг с другом. Нередко каждая страница индекса содержит около 100 строк - приблизительная цифра; это зависит от размера проиндексированных столбцов. Тем не менее, это означает, что одна операция ввода-вывода может получить 100 строк. Табличные данные, с другой стороны, не организованы подобным образом ( исключения). Здесь довольно часто страница содержит только одну из выбранных строк вместе со многими другими строками, которые не представляют интереса для конкретного запроса. Итак, причина, по которой сканирование только по индексу может быть в 100 раз быстрее, заключается в том, что при доступе к индексу можно легко доставить 100 строк на ввод-вывод, тогда как при доступе к таблице обычно выбирается только несколько строк на ввод-вывод.
Если вы выберете один столбец, которого нет в индексе, база данных не сможет выполнить сканирование только индекса. Если вы выберете все столбцы, ... ну, я думаю, вы знаете ответ.
Кроме того, некоторые базы данных хранят большие объекты в отдельном месте (например, LOB в Oracle). Доступ к ним также вызывает дополнительный ввод-вывод.
До 5 раз медленнее при увеличении объема памяти сервера
Хотя базы данных избегают сохранения результата в основной памяти сервера - вместо этого они доставляют каждую строку после загрузки и снова забывают о ней - иногда это неизбежно. При сортировке, например, для выполнения работы необходимо, чтобы все строки - и все выбранные столбцы - находились в памяти. Опять же, чем больше столбцов вы выберете, тем больше памяти потребуется базе данных. В худшем случае базе данных может потребоваться внешняя сортировка на диске.
Тем не менее, большинство баз данных очень хорошо настроены для такой нагрузки. Хотя я довольно часто наблюдал ускорение сортировки в два раза - просто удалив несколько неиспользуемых столбцов, - я не могу припомнить, чтобы у меня был коэффициент больше пяти. Однако это не просто сортировка, хеш-соединения также довольно чувствительны к раздутию памяти. Не знаю, что это? Пожалуйста, прочтите эту статью .
Это только две главные проблемы с точки зрения базы данных. Помните, что клиенту также необходимо обрабатывать данные, что может серьезно повлиять на сборку мусора.
Теперь, когда мы выработали общее понимание того, почему выбор всего плохого для производительности, вы можете спросить, почему это занесено в список мифов? Это потому, что многие люди думают, что звезда - это плохо. Кроме того, они считают, что не совершают этого преступления, потому что их ORM все равно перечисляет все столбцы по имени. Фактически преступление состоит в том, чтобы выбрать все столбцы, не задумываясь об этом - и большинство ORM с готовностью совершают это преступление от имени своих пользователей.
На select * самом деле причина плохая - следовательно, причина того, что миф очень устойчив, - в том, что звезда просто используется как аллегория для «выбора всего, не задумываясь об этом». Это плохо. Но если вам нужна более популярная фраза, чтобы вспомнить правду, скрывающуюся за этим мифом, возьмите это:
Сама звезда тоже плохая?
Помимо упомянутых выше проблем с производительностью, которые не вызваны самой звездой (звездочкой), сама звезда может вызывать другие проблемы. Например, с помощью программного обеспечения, которое ожидает столбцы в определенном порядке при добавлении или удалении столбца. Тем не менее, по моим наблюдениям, я бы сказал, что эти проблемы довольно хорошо изучены на практике и обычно легко обнаруживаются (программное обеспечение перестает работать) исправлены.
Основное внимание в статье уделяется очень тонким вопросам, которые трудно понять, трудно найти и часто даже трудно исправить (например, при использовании инструментов ORM). Основная цель этой статьи - не дать людям задуматься о самой звезде. Как только люди начинают явно называть нужные столбцы, чтобы получить преимущество в производительности, описанное выше, проблемы, вызванные самой звездочкой, также исчезают.
Миф 6 : База данных А быстрее и лучше базы Б
базу данных выбрать нужно не по наслышке, т к все основывается не на названии базы данных а на механизме хранения данных и используемых алгоритмах
а их не так много и почти все современные базы данных их используют а именно
- B-tree– O(log n) и индексы почти все DB
- Hash– O(1)– O(n) и индексы почти все DB
- Log-structuredmerge-tree– O(log n) и индексы БД Cassandra
- K-Dtree – O(log n)–O(n) и индексы Postgres/graphDBs
- Алгоритм Бойера — Мура (Boyer–Moore string search algorithm) – O(n)
- полный перебор
Миф 7 : проверенная временем или наоборот новая – значит это быстрая база данных
Если база данных - проверенная временем или наоборот новая – значит быстрая
Базовые алгоритмы поиска неменялись на протяжении десятилети
1961–QuickSort
1968 – BinaryTree
1972– B-Tree
нового не изобретено!! следовательно кроме маркетинга для новых баз данных или наоборот для старых баз ничего нет.
Миф 8 : Если СУБД хранит данные в памяти – значит она быстрая
Частично это утверждение верно, однако в реальности -
Все упирается в ресурсные ограничения:
Для памяти - ее размер
Для CPU - скорость операций в секунду - scheduledby OS,dependson LA
RAM speed -фрагментирование
Diskspeed - fragmentation,latency,depends ontypeand LA
многопоточности, архитектуре данных используемых самим программистом и д.р. факторам
Значительный прирост производительности баз данных используемых хранение в памяти происходит потому ,что вы убираете целое измерение(слой) при работе сданными
Миф 9 : чем больше железа – тем все будет быстрее для БД
В реальности опять сталкиваемся с физическими ограничениями :
Железо и все что с ним связано
- Частота одного ядра CPU ограничена сверху
- Скорость памяти ограничена типом
- Скорость диска ограничена типом
Представим,что доступ к одному юниту равен 1ms
дляизвлечения 1000 записей понадобится 1s, норма
100K –100 секунд ,долго
1M–~15 минут!
тоесть просеживается зависимость от того как используют СУБД, а не только от испоьзуемого железа.
Миф 10 : Распределенная БД – значит быстрая
Падение производительности распределеных баз данных происходит потому,
что добавляется новое,ненадежное измерение(слой шардирования) при работес данными.
Что влечетза собой ряд проблем
- Consistency or availability
- Split-brainscenarios
- Conflicts resolution/merging(optimistic replication)
- Quorum-based reads/writes
- Manual/auto sharding configuration etc.
В частности с репликацией
- Будут возникать конфликты
- Вопрос только в том
- 1. Когда их нужно решать
- 2. Кто их будет решать
Например
- Amazon Dynamo решает на этапе чтенияи предоставляет это приложению
- А Apache Couch DB на этапе записии last write win + отложенный механизм решения конфликтов
Что реально нужно для оптимизации в БД?
Как решается вопрос с чтением ?
- Построение индекса при записи(Postgres, Redisсхранимыми процедурами)
- Устаревшие результаты(stale)
- Асинхронное создание/предвычисление индекса(вариант stale с внешним индексером)
- Шардинг,множественный запрос к нодам шард
Как решается вопрос с записью?
- контроль над системным вызовом fsync дляпроцесса БД
- Отложенное создание индекса до первого чтения(CouchDB)
- Отложенное создание индекса до (commit явно или потаймауту,Solr)
Оптимизациив на уровне клиента(приложения)
Как решается вопрос с чтением на уровне прирложения?
- Read onwrites (SQLAlchemy)
- Кеширование ответов БД
- Асинхронные запросы к БД
- Асинхронное кеширование
Проблема с чтением в основном связана с внутренней организацией структур данных приложения и множества прослоек по пути между чистым запросом ичистым ответом
Как решается вопросс записью на уровне прирложения?
- Read onwrites (SQLAlchemy)
- Pub/subиасинхроннаязапись/ очереди
- Прямая запись в БД с отложенным/ асинхронным созданием индекса (CouchDB)
Вау!! 😲 Ты еще не читал? Это зря!
Данная статья про производительность баз данных подтверждают значимость применения современных методик для изучения данных проблем. Надеюсь, что теперь ты понял что такое производительность баз данных, оптимизация sql
и для чего все это нужно, а если не понял, или есть замечания,
то не стесняйся, пиши или спрашивай в комментариях, с удовольствием отвечу. Для того чтобы глубже понять настоятельно рекомендую изучить всю информацию из категории
Базы данных, знаний и хранилища данных. Big data, СУБД и SQL и noSQL
Комментарии
Оставить комментарий
Базы данных, знаний и хранилища данных. Big data, СУБД и SQL и noSQL
Термины: Базы данных, знаний и хранилища данных. Big data, СУБД и SQL и noSQL