20-минутная практика проектирования индекса базы данных

база данных

Как легко и эффективно спроектировать большое количество индексов базы данных в работе бэкэнд-разработки? Благодаря следующим четырем шагам вам больше не придется беспокоиться о дизайне индекса базы данных через 20 минут.

Плавное чтение этой статьи требует понимания того, как устроены индексы базы данных.Если вы не знакомы с этим, вы можете быстро узнать об этом через другую статью -Интеграция индекса базы данных.

Эта статья является третьей в серии статей об индексировании баз данных, которая включает следующие четыре статьи:

  1. Что такое индекс базы данных? Словарь Синьхуа вам в помощь-- понимать
  2. Интеграция индекса базы данных- глубоко
  3. 20-минутная практика проектирования индекса базы данных—— Фактический бой
  4. Почему индекс базы данных реализован с деревом B+?- расширение

Эта серия охватывает ряд знаний об индексировании баз данных от теории к практике и решает весь процесс от понимания до мастерства за одну остановку Я считаю, что каждая статья может дать вам более глубокий опыт.

1. Организуйте условия запроса

Цель разработки индекса — ускорить выполнение запроса, поэтому структура индексапервый шагИменно для того, чтобы разобраться с условиями запроса, которые необходимо использовать, т. е. мы будемwhereпункт,joinПоля, используемые в условиях соединения. Вообще говоря, все операторы SQL, кроме оператора вставки в программе, сортируются, а условия запроса для каждой таблицы сортируются в соответствии с разными таблицами. Вы также можете добавить некоторые условия запроса, которые пока не использовались, исходя из вашего понимания бизнеса.

Структура индекса обычно выполняется потаблично, поэтому сбор условий запроса по таблице данных может облегчить выполнение последующих шагов.

2. Необязательность полей анализа

Разобравшись со всеми условиями запроса, нам нужно проанализироватьпо желанию, так что же такое необязательность?

Избирательность поля относится к степени дискриминации значения поля, например, в таблице хранятся поля номера мобильного телефона пользователя, пола, имени и возраста, а номер мобильного телефона может зарегистрировать только одного пользователя. . В этом случае уникальное поле, такое как номер мобильного телефона, обладает наибольшей избирательностью; хотя существуют десятки возможностей для возраста, степень дискриминации не так велика, как у номера мобильного телефона; пол - это всего лишь несколько вариантов, поэтому выбор наихудший. Так что я могу упорядочить по возрастанию: номер мобильного телефона > возраст > пол.

Однако распределение значений в разных полях различно, и количество значений примерно однородно.Например, количество значений для мужчин и женщин может не сильно отличаться, но такие записи, как возраст более 100 очень редко. Таким образом, для поля возраста значение 20-30 имеет очень низкую избирательность, поскольку для каждого возраста имеется много записей, а значение 100 имеет очень высокую избирательность.

Если мы добавим в таблицу поле, чтобы указать, является ли пользователь администратором, то это поле будет необязательным при запросе списка информации администратора веб-сайта. Но если мы хотим запросить список информации, не являющейся администратором, избирательность этого поля очень низкая.

По опыту мы помещаем поля с высокой селективностью впереди, а поля с низкой селективностью сзади.Если селективность очень низкая, такие поля вообще не помещаются в индекс.

3. Комбинируйте условия запроса

Хотя индексы могут повысить эффективность запросов, чем больше индексов, тем выше стоимость вставки и обновления данных, поскольку индексы хранятся отдельно, и все операции вставки и обновления данных должны модифицировать соответствующие индексы. Следовательно, необходимо контролировать количество индексов при разработке индексов и нельзя увеличивать индексы вслепую.

Как правило, мы будемКрайний левый принцип соответствиядля объединения условий запроса и максимально возможного использования одного и того же индекса для разных условий запроса. Например, есть два условия запросаwhere a = 1 and b = 1иwhere b = 1, то мы можем создать индексidx_eg(b, a)для обслуживания двух условий запроса одновременно.

В то же время, поскольку условие диапазона прекратит использование последующих полей в индексе, мы поместим поля, запрошенные с использованием условия диапазона, в конец индекса, насколько это возможно.

4. Подумайте, нужно ли вам использовать полностью покрывающий индекс

Наконец, мы рассмотрим, нужно ли нам использовать полностью покрывающий индекс, потому чтоПолный индекс покрытиянетформа возвратанакладные расходы, эффективность будет выше. Поэтому мы обычно рассматриваем вопрос о том, следует ли использовать индекс с полным покрытием, когда стоимость возврата таблицы особенно высока, например, если результат, отфильтрованный по полю индекса, должен возвращать другие поля или использовать другие поля для дальнейшей фильтрации.

Например, у нас есть таблица пользователей с тремя полями: возраст, имя и номер мобильного телефона. Нам нужно запросить имена всех пользователей в указанном возрасте, есть индексidx_age_name(年龄, 姓名), в настоящее время мы используем следующий оператор запроса для запроса:

SELECT *
FROM 用户表
WHERE 年龄 = ?;

В общем, есть два способа оптимизировать индекс как полностью покрывающий:

  1. Увеличьте поля в индексе, чтобы поля индекса покрывали все поля, используемые в операторе SQL.
    • В этом примере мы можем создать индекс, который включает все поля одновременно.idx_all(年龄, 姓名, 手机号), чтобы повысить эффективность запроса.
  2. Сократите количество полей, используемых в операторах SQL, чтобы все поля, требуемые SQL, были включены в существующий индекс.
    • В этом примере лучшим подходом было быSELECTПункт изменен наSELECT 姓名, поскольку нам нужно только запросить имя пользователя и не нужно поле номера мобильного телефона, удалитеSELECTИзбыточные поля предложения могут не только удовлетворить наши потребности, но и не требуют изменения индекса.
Категории