слова, написанные впереди
Оптимизация баз данных включает в себя все аспекты знаний.Архитектура и методы оптимизации каждой базы данных также сильно различаются.Если вы хотите добиться хороших результатов в оптимизации базы данных, вы должны понимать техническую архитектуру, структуру хранения, метод хранения, структуру кэша и Процесс выполнения оператора SQL базы данных. Дождитесь глубокого понимания. Эта статья является лишь введением в общие методы оптимизации, которые разработчики используют каждый день, и содержание, связанное с базой данных, такое как настройка параметров базы данных, больше не входит в рамки этой статьи. Цель состоит в том, чтобы дать разработчикам понимание общих оптимизаций баз данных.
28 правил оптимизации базы данных
Правило двадцати восьми гласит, что в любом наборе вещей на самые важные приходится лишь малая часть, около 20%, а остальные 80%, хотя и составляют большинство, являются второстепенными, например: 20% богатых владеют 80% богатства. 20% сильных потребляют 80% ресурсов и так далее. Оптимизация базы данных также соответствует правилу 28:
- 80% проблем с производительностью вызваны 20% приложений. Узкие места в производительности, такие как полное сканирование небольшого количества больших таблиц. Дело не в том, что систему нужно перенастраивать всякий раз, когда возникает проблема с приложением, необходимо только оптимизировать несколько функций с проблемами производительности, чтобы значительно улучшить производительность системы.
- 80% проблем с производительностью можно решить с помощью 20% методов оптимизации. Например, добавление индексов, выполнение анализа плана и т. д. может решить большинство проблем с производительностью.
Таким образом, если мы сможем понять общие методы оптимизации баз данных, мы сможем решить 80% проблем, возникающих при разработке.
Как оптимизировать базу данных
В скетче я спросил у слона, сколько ступеней поставить в холодильнике? Откройте дверцу холодильника, положите туда слона и закройте дверцу холодильника.
Каковы шаги по оптимизации базы данных? Найдите конкретные причины, влияющие на производительность базы данных, и решите проблему.
проблема найдена
Миф: Моя инструкция SQL выполняется очень быстро, проблема определенно не во мне.
Правильное решение: скорость одного выполнения оператора SQL не обязательно отражает качество оператора. Хотя некоторые операторы имеют высокую эффективность одиночного выполнения, по мере увеличения объема данных увеличение параллелизма, вероятно, станет узким местом производительности.
Как сделать? После того, как SQL написан, посмотрите на план выполнения этого SQL и говорите фактами.
Так что же такое план выполнения?
SQL — глупый язык, каждое условие является требованием, а порядок доступа отличается для формирования другого плана выполнения. База данных должна делать выбор, и в каждый момент времени может быть только один путь доступа. План выполнения — это описание процесса выполнения или пути доступа к оператору запроса в базе данных.
В каждой базе данных есть инструменты для просмотра плана выполнения SQL. Только если нет проблем с планом выполнения, значит, нет проблем с SQL. Если вы можете понять план выполнения. Тогда поздравляю, вы решили 80% задачи оптимизации. Потому что найти проблемы часто сложнее, чем решить их.
Решать проблему
Выше упоминалось, как найти проблемы.На самом деле, помимо просмотра плана выполнения, есть и другие средства, такие как просмотр количества выполнений одного SQL и т. Д. Однако план выполнения является наиболее распространенным и прямым способом для разработчиков. , который может помочь нам решить большинство проблем. Итак, как решить проблему после ее обнаружения?
Проще говоря, есть два направления оптимизации: делать как можно меньше, а если нельзя меньше, пытаться использовать производительность сервера.
Как повысить производительность сервера, вы можете использовать параллелизм, чтобы максимизировать ресурсы сервера. Некоторые базы данных сами предоставляют некоторые механизмы параллелизма.Например, Oracle может установить количество параллельных SQL-запросов, увеличив подсказку, или может максимально снизить производительность сервера за счет параллелизма приложения. Конечно, вы также можете повысить производительность, добавив серверы и подтаблицы базы данных для базы данных.
Другой способ — делать как можно меньше (стучать по доске, подчеркивать ключевые моменты).
Как заставить базу данных работать меньше? Способов много, самый распространенный — через index. Эксперт, проработавший в ORACLE более 20 лет, однажды сказал: «На самом деле, я немного разбираюсь в ИТ, я немного разбираюсь в ORACLE в ИТ, и я немного разбираюсь в базе данных в ORACLE, и только немного SQL в базе данных. В SQL я немного разбираюсь в индексах". Узнайте, насколько важны индексы для оптимизации базы данных.
индекс чата
Как упоминалось ранее, индексы особенно важны для оптимизации базы данных.Давайте поговорим о некоторых мерах предосторожности при создании и использовании индексов. Префиксность и избирательность создания индекса, несколько предложений по созданию индекса, распространенные ситуации подавления индекса.
префикс индекса
Давайте рассмотрим следующий пример.Предположим, что индекс построен на трех полях (ENAME, JOB, MGR) таблицы сотрудников (emp), например, имя индекса IDX_1. Три поля — это имя сотрудника, должность и идентификатор менеджера. Затем напишите следующий оператор запроса и продолжайте упорядочивать и комбинировать условия запроса и заказы, например:
Select * from emp where ENAME=’a’ and JOB=’b’ and MGR=3;
Select * from emp where JOB=’b’ and MGR=3 and ENAME=’a’;
Select * from emp where JOB=’b’ and ENAME=’a’ and MGR=3;
Select * from emp where JOB=’b’ and MGR=3;
Select * from emp where ENAME=’a’ and MGR=3;
Select * from emp where ENAME=’a’;
Select * from emp where JOB=’b’;
Select * from emp where MGR=3;
В случае различных комбинаций условий следует ли использовать только что созданный индекс (IDX_1) или нет? То есть, является ли доступ к таблице emp полным сканированием таблицы и доступом по индексу (IDX_1)?
Ответ заключается в том, что при наличии условия ENAME='a' можно использовать индекс (IDX_1) вместо полного сканирования таблицы. При создании составного индекса необходимо учитывать префикс индекса, иначе будет вызвано полное сканирование таблицы из-за отсутствия столбца префикса в условии извлечения.
Префикс индекса относится к первому полю, которое должно использоваться в индексе.
Селективность индекса
Избирательность индекса относится к отношению уникального значения индекса (мощности) к количеству записей в таблице. Селективность — это показатель возможностей фильтрации индекса. Чем выше селективность, тем больше значение индекса.
Например, если в таблице заказов содержится 100 000 записей заказов, уникальное значение столбца user_id в таблице равно 10000, а уникальное значение столбца order_date равно 1000, то эффективность запроса при создании индекса по user_id более эффективна. чем создание индекса на order_date.high. Это связано с тем, что чем больше значений полей, тем сильнее необязательность, тем меньше записей нужно располагать после индексного запроса и тем выше эффективность запроса.
Несколько советов по созданию индексов
Наиболее часто используемым индексом базы данных является индекс B-дерева (разные реализации баз данных немного отличаются, например: oralce создает дерево B*, mysql — дерево B+), и разные базы данных также могут иметь свои собственные уникальные индексы, такие как как: битовый индекс оракула, хеш-индекс mysql и так далее. Здесь мы обсуждаем только часто используемые индексы B-дерева. Вот несколько предложений по разработке индексов с одним полем и составных индексов при создании индексов B-tree:
- Проанализируйте поле ограничения в операторе SQL.Если поле ограничения относительно фиксировано, предпочтительно создать составной индекс для нескольких полей. Например, для условий, включающих несколько полей одновременно, можно рассмотреть возможность создания составного индекса.
- Если одно поле является первичным ключом, или уникальным полем, или полем с очень высокой степенью необязательности, хотя поле ограничения относительно фиксировано, нет необходимости создавать составной индекс. Стоимость составного индекса.
- При разработке составного индекса необходимо учитывать первый принцип построения составного индекса: префикс составного индекса. То есть в операторе SQL в качестве ограничения используется только первое поле составного индекса, составной индекс будет включен.
- Во-вторых, при разработке составного индекса необязательный характер составного индекса следует учитывать. То есть поля составного индекса сортируются в соответствии с уровнем необязательности.
- Если поля, участвующие в условиях, не являются фиксированными, а комбинация более гибкая, индексы одного поля устанавливаются для разных столбцов соответственно.
- Если это SQL-оператор объединения нескольких таблиц, обратите внимание на то, можно ли создать составной индекс для поля соединения управляемой таблицы и других полей ограничения таблицы.
- С помощью различных инструментов анализа SQL проанализируйте план выполнения и оцените эффект в количественной форме.
Распространенные сценарии подавления индекса
После понимания правил создания индекса индекс создается в соответствии с потребностями бизнеса, но, просмотрев план выполнения оператора, обнаруживается, что полное сканирование таблицы все еще выполняется, а созданный индекс не используется. Тогда индекс не может быть использован ни по какой причине.
1. В индексном столбце есть выражение или функция. тогда индекс недействителен.
Если следующее утверждение:
select user_name from user where age -30 = 0
select user_name from user where age = 30
Хотя для столбца age создается индекс, первый оператор все равно будет выполняться в соответствии с полным сканированием таблицы.
2. Происходит неявное преобразование типа данных
Если следующее утверждение:
select user_name from user where age = ‘30’
Однако столбец age, определенный в пользовательской таблице, имеет тип number, и при выполнении запроса происходит неявное преобразование типа. затем индекс подавляется. Эта ситуация менее очевидна во время разработки, чем предыдущая ситуация. Легко быть проигнорированным всеми.
3. Опциональность данных невысокая
Например, в пользовательской таблице 100 000 единиц данных, но в столбце гендера всего два столбца пола.В этом случае, даже если будет создан индекс, индекс не будет использоваться при выполнении оператора. Причина в том, что набор результатов, найденный на основе индекса, все еще очень велик, а эффективность запроса не так высока, как у полного сканирования таблицы, а это означает, что база данных будет выполнять полное сканирование таблицы.
4. Игнорируемый префикс индекса
Как было сказано выше, при выполнении оператора, игнорирующего предлог индекса, индекс не будет взят при выполнении оператора.
Если значение равно null или не равно null, значение null не определено, поэтому индекс подавляется.
поговори еще немного
О плане выполнения
Как упоминалось ранее, оптимизация SQL во многом зависит от плана выполнения, так как же генерируется план выполнения? Проще говоря, база данных периодически собирает основную информацию, такую как объем данных каждой таблицы в базе данных. Перед отправкой SQL в базу данных для выполнения после завершения проверки достоверности база данных рассчитает оптимальный план выполнения SQL на основе объема данных, индекса и другой информации о каждой таблице.
О переменных привязки
Как упоминалось выше, план выполнения SQL должен быть рассчитан с использованием различной информации, поэтому, если я кэширую план выполнения SQL. Таким образом, каждый раз, когда один и тот же SQL выполняется несколько раз, не удается избежать затрат на каждое вычисление. На самом деле база данных делает именно это. База данных кэширует план выполнения в памяти в соответствии с хэш-значением каждого SQL.
Чтобы улучшить частоту попаданий кэшированного SQL, мы чаще используем заполнители при написании SQL. вместо прямой передачи значения.
Если следующий SQL:
select user_name from user where user_id = 1
select user_name from user where user_id = 2
В это время база данных будет рассматриваться как две части SQL. Потому что кеш не попал. Внесите следующие изменения:
select user_name from user where user_id = ?
Каждый раз, когда вы привязываете разные параметры, кеш будет попадать, что снижает стоимость разбора SQL.
Так должны ли все переменные передаваться с помощью связывающих переменных? Конечно, нет. см. пример ниже
Если есть таблица заказов t_order(user_id, order_date,amount), где user_id — идентификатор пользователя, а order_date — дата заказа
Создайте индексы отдельно:
idx_order_1(user_id)
idx_order_2(order_date)
Если я хочу, чтобы SQL для запроса был:
select user_id, order_date,amount
from t_order
where user_id >= :num1
and user_id <= :num2
and order_date >= :date1
and order_date <= :date2
В это время, когда диапазон между входящими параметрами num1 и num2 мал и num1=num2, то есть, если я хочу запросить заказ определенного человека за период времени, будет более эффективно использовать idx_order_2. А когда диапазон входящих параметров num1 и num2 большой, а диапазон date1 и date2 мал. Если вы запрашиваете заказы всех в определенный день, будет эффективнее использовать idx_order_1. На этот раз, если мы используем способ связывания переменных. Потому что база данных не знает, каков диапазон параметров при анализе плана выполнения. Невозможно дать оптимальный план, и не рекомендуется использовать переменные связывания для передачи значений.
В этой статье обсуждаются только общие методы оптимизации для баз данных. Чтобы хорошо справляться с оптимизацией базы данных, необходимо много пробовать в ежедневной разработке.
об авторе
Ли Гуанмин, Minsheng Technology Co., Ltd., инженер-разработчик Java мобильной финансовой платформы Firefly отдела технологий взаимодействия с пользователем.