Индекс MySQL
Индекс MySQL — это структура данных, используемая для ускорения скорости и производительности запросов к базе данных.
Создание индексов MySQL важно для эффективной работы MySQL; индексы могут значительно повысить скорость выборки MySQL.
Индекс MySQL похож на указатель в книге. Храня указатели на строки данных, он позволяет быстро находить и получать доступ к конкретным данным в таблице.
Для аналогии: если база данных MySQL с хорошо спроектированными и правильно используемыми индексами — это Lamborghini, то база данных MySQL без спроектированных и используемых индексов — это трехколесный велосипед с мускульной тягой.
Возьмем в качестве примера страницу указателя китайского словаря: мы можем быстро находить нужные иероглифы, используя оглавление (указатель), отсортированное по пиньиню, количеству черт, радикалам и так далее.
Индексы делятся на одностолбцовые индексы и составные индексы:
- Одностолбцовый индекс означает, что индекс содержит только один столбец; таблица может иметь несколько одностолбцовых индексов.
- Составной индекс означает, что индекс содержит несколько столбцов.
При создании индекса нужно убедиться, что индекс применяется к условиям в SQL-запросах (обычно к условиям в предложении WHERE).
Фактически индекс — это тоже таблица, которая хранит первичный ключ и поля индекса и указывает на записи в таблице сущностей.
Хотя индексы могут повысить производительность запросов, необходимо также учитывать следующие моменты:
- Индексы требуют дополнительного места для хранения.
- При выполнении операций вставки, обновления и удаления в таблице индексы необходимо поддерживать, что может повлиять на производительность.
- Слишком много индексов или нерациональные индексы могут привести к снижению производительности, поэтому индексы нужно тщательно выбирать и планировать.
Обычный индекс
Индексы могут значительно повысить скорость запросов, особенно при поиске в больших таблицах. Используя индексы, MySQL может напрямую находить строки данных, удовлетворяющие условиям запроса, без построчного сканирования всей таблицы.
Создание индекса
Используйте операторCREATE INDEXдля создания обычного индекса.
Обычный индекс — наиболее распространённый тип индекса, используемый для ускорения запросов к данным в таблице.
CREATE INDEXСинтаксис выглядит следующим образом:
CREATE INDEX index_name ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);
CREATE INDEX: ключевое слово, используемое для создания обычного индекса.index_name: задаёт имя создаваемого индекса. Имя индекса должно быть уникальным в пределах таблицы.table_name: указывает, в какой таблице создать индекс.(column1, column2, ...): задаёт имена столбцов таблицы, по которым создаётся индекс. Можно указать один или несколько столбцов для составного индекса. Типы данных таких столбцов обычно — числовой, текстовый или дата.ASCиDESC(Необязательно): используется для указания порядка сортировки индекса. По умолчанию индекс сортируется по возрастанию (ASC).
В следующем примере предполагается, что у нас есть таблица с именем students, содержащая столбцы id, name и age. Мы создадим обычный индекс по столбцу name.
CREATE INDEX idx_name ON students (name);
Приведённая выше инструкция создаст обычный индекс с именем idx_name по столбцу name таблицы students, что поможет повысить производительность запросов при поиске по name.
Обратите внимание, что если объём данных в таблице велик, создание индекса может занять некоторое время, но после создания производительность запросов значительно улучшится.
Изменение структуры таблицы (добавление индекса)
Мы можем использоватьALTER TABLEкоманду для создания индекса в существующей таблице.
ALTER TABLE позволяет изменять структуру таблицы, включая добавление, изменение или удаление индексов.
Синтаксис создания индекса с помощью ALTER TABLE:
ALTER TABLE table_name ADD INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);
ALTER TABLE: Ключевое слово, используемое для изменения структуры таблицы.table_name: Указывает имя таблицы, которую нужно изменить.ADD INDEX: Предложение для добавления индекса.ADD INDEXИспользуется для создания обычного индекса.index_name: задаёт имя создаваемого индекса. Имя индекса должно быть уникальным в пределах таблицы.(column1, column2, ...): задаёт имена столбцов таблицы, по которым создаётся индекс. Можно указать один или несколько столбцов для составного индекса. Типы данных таких столбцов обычно — числовой, текстовый или дата.ASCиDESC(Необязательно): используется для указания порядка сортировки индекса. По умолчанию индекс сортируется по возрастанию (ASC).
Ниже приведён пример. Мы создадим обычный индекс в существующей таблице с именем employees:
ALTER TABLE employees ADD INDEX idx_age (age);
Приведённая выше инструкция создаст обычный индекс с именем idx_age по столбцу age таблицы employees.
Указание непосредственно при создании таблицы
При создании таблицы можноCREATE TABLEнепосредственно указать индексы в операторе, чтобы создать комбинацию таблицы и индексов.
CREATE TABLE table_name ( column1 data_type, column2 data_type, ..., INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...) );
CREATE TABLE: Ключевое слово, используемое для создания новой таблицы.table_name: Указывает имя создаваемой таблицы.(column1 data_type, column2 data_type, ...): Определяет имена столбцов и типы данных таблицы. Можно указать один или несколько столбцов в качестве составного индекса. Типы данных этих столбцов обычно числовые, текстовые или дата.INDEX: ключевое слово, используемое для создания обычного индекса.index_name: задаёт имя создаваемого индекса. Имя индекса должно быть уникальным в пределах таблицы.(column1, column2, ...): задаёт имена столбцов таблицы, по которым создаётся индекс. Можно указать один или несколько столбцов для составного индекса. Типы данных таких столбцов обычно — числовой, текстовый или дата.ASCиDESC(Необязательно): используется для указания порядка сортировки индекса. По умолчанию индекс сортируется по возрастанию (ASC).
Ниже приведён пример. Мы создадим таблицу с именем students и создадим обычный индекс по столбцу age.
CREATE TABLE students ( id INT PRIMARY KEY, name VARCHAR(50), age INT, INDEX idx_age (age) );
В приведённом выше примере мы создали обычный индекс с именем idx_age по столбцу age таблицы students.
Синтаксис удаления индекса
Мы можем использоватьDROP INDEXоператор для удаления индекса.
Синтаксис для DROP INDEX:
DROP INDEX index_name ON table_name;
DROP INDEX: Ключевое слово, используемое для удаления индекса.index_name: Указывает имя удаляемого индекса.ON table_name: Указывает, в какой таблице удалить индекс.
ИспользуйтеALTER TABLEСинтаксис удаления индекса с помощью этого оператора следующий:
ALTER TABLE table_name DROP INDEX index_name;
ALTER TABLE: Ключевое слово, используемое для изменения структуры таблицы.table_name: Указывает имя таблицы, которую нужно изменить.DROP INDEX: предложение, используемое для удаления индекса.index_name: Указывает имя удаляемого индекса.
В следующем примере предполагается, что у нас есть таблица с именем employees с индексом с именем idx_age по столбцу age. Теперь мы хотим удалить этот индекс:
DROP INDEX idx_age ON employees;
Или используйтеALTER TABLEоператор:
ALTER TABLE employees DROP INDEX idx_age;
Обе команды удалят индекс с именем idx_age из таблицы employees.
Если индекс не существует, при выполнении команды возникнет ошибка. Поэтому перед удалением индекса лучше всего убедиться, существует ли индекс, или использовать механизм обработки ошибок для обработки возможных ошибочных ситуаций.
Уникальный индекс
В MySQL можно использоватьCREATE UNIQUE INDEXоператор для создания уникального индекса.
Уникальный индекс гарантирует, что значения в индексе уникальны, а дублирующиеся значения не допускаются.
Создание индекса
CREATE UNIQUE INDEX index_name ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);
CREATE UNIQUE INDEX: комбинация ключевых слов, используемая для создания уникального индекса.index_name: указывает имя создаваемого уникального индекса. Имя индекса должно быть уникальным в пределах таблицы.table_name: указывает, в какой таблице создать уникальный индекс.(column1, column2, ...): задаёт имена столбцов таблицы, по которым создаётся индекс. Можно указать один или несколько столбцов для составного индекса. Типы данных таких столбцов обычно — числовой, текстовый или дата.ASCиDESC(Необязательно): используется для указания порядка сортировки индекса. По умолчанию индекс сортируется по возрастанию (ASC).
Пример создания уникального индекса: Предположим, у нас есть таблица с именем employees с колонками id и email. Теперь мы хотим создать уникальный индекс на колонке email, чтобы гарантировать, что адрес электронной почты каждого сотрудника уникален.
CREATE UNIQUE INDEX idx_email ON employees (email);
Приведенный выше пример создаст уникальный индекс с именем idx_email на колонке email таблицы employees.
Изменение структуры таблицы для добавления индекса
Мы можем использовать командуALTER TABLEдля создания уникального индекса.
ALTER TABLEКоманда позволяет изменять структуру существующей таблицы, включая добавление новых индексов.
ALTER table table_name ADD CONSTRAINT unique_constraint_name UNIQUE (column1, column2, ...);
ALTER TABLE: Ключевое слово, используемое для изменения структуры таблицы.table_name: Указывает имя таблицы, которую нужно изменить.ADD CONSTRAINT: Это ключевое слово, используемое для добавления ограничений (включая уникальные индексы).unique_constraint_name: Задает имя создаваемого уникального индекса. Имя ограничения должно быть уникальным в пределах таблицы.UNIQUE (column1, column2, ...): задаёт имена столбцов таблицы, по которым создаётся индекс. Можно указать один или несколько столбцов для составного индекса. Типы данных таких столбцов обычно — числовой, текстовый или дата.
Ниже приведен пример использования командыALTER TABLEдля создания уникального индекса: предположим, у нас есть таблица с именем employees, содержащая колонки id и email, и теперь мы хотим создать уникальный индекс на колонке email, чтобы гарантировать, что адрес электронной почты каждого сотрудника уникален.
ALTER TABLE employees ADD CONSTRAINT idx_email UNIQUE (email);
Приведенный выше пример создаст уникальный индекс с именем idx_email на колонке email таблицы employees.
Обратите внимание: если в таблице уже есть дублирующиеся значения email, добавление уникального индекса завершится ошибкой. Перед созданием уникального индекса вам, возможно, потребуется убедиться, что колонка email в таблице не содержит дублирующихся значений.
Указание непосредственно при создании таблицы
Мы также можем создать таблицу и одновременно использовать ключевое словоCREATE TABLEв оператореUNIQUEдля создания уникального индекса.
Это определяет ограничение уникального индекса одновременно с созданием таблицы.
CREATE TABLEСинтаксис создания уникального индекса в операторе:
CREATE TABLE table_name ( column1 data_type, column2 data_type, ..., CONSTRAINT index_name UNIQUE (column1 [ASC|DESC], column2 [ASC|DESC], ...) );
CREATE TABLE: Ключевое слово, используемое для создания новой таблицы.table_name: Указывает имя создаваемой таблицы.(column1 data_type, column2 data_type, ...): Определяет имена столбцов и типы данных таблицы. Можно указать один или несколько столбцов в качестве комбинации индекса. Типы данных этих столбцов обычно числовые, текстовые или дата.CONSTRAINT: Ключевое слово, используемое для добавления ограничения.index_name: Задает имя создаваемого уникального индекса. Имя ограничения должно быть уникальным в пределах таблицы.UNIQUE (column1, column2, ...): Указывает имя столбца таблицы, для которого создается индекс.ASCиDESC(Необязательно): используется для указания порядка сортировки индекса. По умолчанию индекс сортируется по возрастанию (ASC).
Ниже приведен пример создания уникального индекса при создании таблицы: предположим, мы хотим создать таблицу с именем employees, содержащую столбцы id, name и email, и мы хотим, чтобы значения в столбце email были уникальными, поэтому мы определяем уникальный индекс при создании таблицы.
CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(50), email VARCHAR(100) UNIQUE );
В этом примере столбец email определяется как уникальный индекс, потому что после него добавлено ключевое слово UNIQUE.
Обратите внимание, что после использованияUNIQUEключевого слова имя индекса автоматически генерируется, и вы также можете указать имя индекса при необходимости.
Использование команды ALTER для добавления и удаления индексов
Существует четыре способа добавления индексов в таблицу данных:
- ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):Эта инструкция добавляет первичный ключ. Значения в столбце первичного ключа должны быть уникальными. Список столбцов первичного ключа может состоять из одного или нескольких столбцов и не может содержать значения NULL.
- ALTER TABLE tbl_name ADD UNIQUE index_name (column_list):Значения индекса, созданного этой инструкцией, должны быть уникальными (за исключением NULL, который может встречаться несколько раз).
- ALTER TABLE tbl_name ADD INDEX index_name (column_list):Добавляет обычный индекс; значения индекса могут встречаться несколько раз.
- ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):Этот оператор определяет индекс как FULLTEXT, используемый для полнотекстового индексирования.
В следующем примере добавляется индекс в таблицу.
mysql> ALTER TABLE testalter_tbl ADD INDEX (c);
Вы также можете использовать предложение DROP в команде ALTER, чтобы удалить индекс. Попробуйте следующий пример, чтобы удалить индекс:
mysql> ALTER TABLE testalter_tbl DROP INDEX c;
Использование команды ALTER для добавления и удаления первичных ключей
Первичный ключ применяется к столбцу (это может быть один столбец или несколько столбцов, объединенных в качестве первичного ключа). При добавлении первичного ключа необходимо убедиться, что первичный ключ по умолчанию не равен NULL (NOT NULL). Пример:
mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL; mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);
Вы также можете использовать команду ALTER, чтобы удалить первичный ключ:
mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;
При удалении первичного ключа достаточно указать только PRIMARY KEY, но при удалении индекса необходимо знать имя индекса.
Отображение информации об индексах
Вы можете использоватьSHOW INDEXкоманду для вывода соответствующей информации об индексах в таблице.
Вы можете добавить\Gдля форматирования выходной информации.
Инструкция SHOW INDEX:
mysql> SHOW INDEX FROM table_name\G ........
SHOW INDEX: Ключевое слово, используемое для отображения информации об индексах.FROM table_name: Указывает имя таблицы, информацию об индексах которой вы хотите просмотреть.\G: Форматирует выходную информацию.
После выполнения вышеуказанной команды будет отображена подробная информация обо всех индексах указанной таблицы, включая имя индекса (Key_name), столбец индекса (Column_name), является ли он уникальным индексом (Non_unique), метод сортировки (Collation), кардинальность индекса (Cardinality) и так далее.
Другие расширения