MySQL Содержание: Руководство по созданию, добавлению и удалению объектов

Что такое MySQL Индекс?

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

Индексирование имеет свои недостатки — каждая операция INSERT или UPDATE должна поддерживать индекс, поэтому добавление слишком большого количества индексов к таблице с высокой частотой записи может негативно сказаться на общей производительности. Как правило, индексировать столбцы, которые фигурируют в предложениях WHERE, JOIN и ORDER BY, следует таблицы, которые читаются чаще, чем записываются.

Зачем нужен указатель?

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

Медленное время отклика обычно связано с тем, что строки хранятся на диске в физическом порядке. Без индекса... MySQL Необходимо просканировать каждую строку, чтобы найти те, которые соответствуют предикату — «полное сканирование таблицы». Индексы позволяют MySQL Переход непосредственно к соответствующим строкам, что преобразует план запроса со O(n) до приблизительно O(log n) для поиска в B-дереве.

Синтаксис: Создать индекс

Индекс можно определить в двух местах:

1. На момент создания таблицы.
2. Если таблица уже существует.

Пример: Создание индекса непосредственно с помощью команды CREATE TABLE.

Для того, чтобы получить myflixdb В базе данных ожидается большое количество поисковых запросов по столбцу "полное имя". Приведенный ниже скрипт создает новый объект. members_indexed таблица с указателем по full_names колонка.

CREATE TABLE `members_indexed` (
    `membership_number` INT(11) NOT NULL AUTO_INCREMENT,
    `full_names`        VARCHAR(150) DEFAULT NULL,
    `gender`            VARCHAR(6)   DEFAULT NULL,
    `date_of_birth`     DATE         DEFAULT NULL,
    `physical_address`  VARCHAR(255) DEFAULT NULL,
    `postal_address`    VARCHAR(255) DEFAULT NULL,
    `contact_number`    VARCHAR(75)  DEFAULT NULL,
    `email`             VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (`membership_number`),
    INDEX (`full_names`)
) ENGINE = InnoDB;

Выполните скрипт в MySQL Рабочий стол против myflixdb .

обновление myflixdb увидеть новое members_indexed Таблица. В full_names Теперь столбец отображается под Индексы узел.

По мере роста числа участников, поисковые запросы по теме members_indexed которые используют операторы WHERE и ORDER BY против full_names выполняются гораздо быстрее, чем те же самые запросы в исходном файле. members Таблица без указателя.

Добавьте индекс после того, как таблица уже существует.

Часто вы обнаружите, что существующей таблице необходим индекс — поисковые запросы выполняются медленно, а план выполнения запроса EXPLAIN показывает полное сканирование таблицы по столбцу, который присутствует в условии WHERE. CREATE INDEX Эта инструкция добавляет индекс без повторного создания таблицы.

CREATE INDEX `id_index` ON `table_name` (`column_name`);

Конкретный пример — ускорение поиска в title столбец movies таблица:

CREATE INDEX `title_index` ON `movies` (`title`);

Каждый запрос, который фильтрует movies.title Теперь используется новый индекс. Запросы, фильтрующие по другим столбцам, по-прежнему сканируют таблицу, если у них нет собственного индекса.

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

Список индексов в таблице

Используйте SHOW INDEXES Чтобы увидеть все индексы, определенные для таблицы.

SHOW INDEXES FROM `table_name`;

Пример — список индексов по movies таблица:

SHOW INDEXES FROM `movies`;

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

Примечание: Первичные и внешние ключи индексируются автоматически. MySQLКаждый индекс имеет уникальное имя и содержит список столбцов, которые он охватывает.

Синтаксис: Drop Index

Используйте DROP INDEX Это позволяет удалить существующий индекс из таблицы. Это полезно, когда таблица с высокой интенсивностью записи замедляется из-за индекса, который перестал приносить пользу при чтении.

DROP INDEX `index_id` ON `table_name`;

Конкретный пример — отбросьте full_names индекс из members_indexed:

DROP INDEX `full_names` ON `members_indexed`;

Виды MySQL Индексы

MySQL Поддерживает несколько типов индексов, каждый из которых подходит для разных типов рабочих нагрузок.

Тип	Цель
ПЕРВИЧНЫЙ КЛЮЧ	Уникальный идентификатор строки; данные кластеризованы с данными таблицы в InnoDB.
УНИКАЛЬНЫЙ	Обеспечивает уникальность, одновременно выполняя функцию указателя.
ИНДЕКС (B-дерево)	Вторичный индекс по умолчанию используется для запросов диапазона и поиска равенства.
ПОЛНЫЙ ТЕКСТ	Оптимизировано для поиска по естественному тексту с помощью функции MATCH … AGAINST.
ПРОСТРАНСТВЕННЫЙ	Индекс R-дерева для типов данных ГИС, таких как POINT и POLYGON.
HASH / ХЭШ	Поиск равенства за постоянное время; используется механизмом хранения данных MEMORY.
Составной (многоколоночный)	Объединяет несколько столбцов в один индекс; учитывает правило префикса слева.

Передовые методы для MySQL Индексы

Приведенные ниже рекомендации помогут сохранить полезность индексов и предотвратить их превращение в балласт.

• Индекс для шаблона запроса, а не для имени столбца: Добавляйте индексы, соответствующие реальным условиям WHERE, JOIN и ORDER BY, а не «каждому столбцу, который кажется важным».
• Смотрите порядок размещения акций составного индекса: Для использования индекса ведущий столбец должен присутствовать в запросе.
• Избегайте дублирования индексов: Ведущий префикс составного индекса уже охватывает поиск по одному столбцу в пределах этого префикса.
• Проверьте с помощью EXPLAIN: подтвердить, что планировщик действительно выбирает новый индекс.
• Удалить неиспользуемые индексы: используют sys.schema_unused_indexes in MySQL 5.7+ для поиска индексов, которые никто не читает.
• Соответствие типов данных: Если в предложении WHERE столбец типа VARCHAR сравнивается с числом, индекс использовать нельзя из-за неявного приведения типов.

Часто задаваемые вопросы (FAQ)