Как и когда создавать индекс в MySQL

Когда дело доходит до оптимизации производительности баз данных MySQL, создание индексов – один из лучших способов. Индексы SQL помогают повысить производительность базы данных, обеспечивая быстрый доступ к данным, хранящимся в базе данных.

Индекс SQL не сильно отличается от обычного книжного указателя с тщательно подобранным списком информации и местами, где ее можно найти.

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

 

Когда создавать индекс в MySQL?

К сожалению, у нас нет прямого ответа, когда следует создавать индекс. Однако некоторые сценарии и факторы могут повлиять на создание индекса. Стоит отметить, что вам, возможно, придется пойти на компромисс при создании индекса.

1. Высокая доступность: если у вас есть таблица или столбец, к которым вы регулярно обращаетесь, вы можете повысить их производительность, создав индекс.
2. Размер: размер данных, хранящихся в таблице или столбце, также может играть роль при принятии решения о необходимости индекса. Большая таблица может получить больше преимуществ от индексов, чем меньшая таблица.
3. Ключ индекса: тип данных целочисленного ключа также имеет значение. Например, целое число является гораздо лучшим индексным ключом из-за его небольшого размера.
4. CRUD-операции: если у вас есть таблица или столбец с большим количеством CRUD-операций, индексирование этой таблицы или столбца может оказаться невыгодным и может отрицательно повлиять на производительность базы данных.
5. Размер базы данных: индексирование – это структура данных, которая будет занимать место в вашей базе данных, что может быть фактором, особенно в уже больших базах данных.

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

Если вы хотите узнать больше о том, как MySQL использует индексы, рассмотрите возможность чтения ресурса, представленного ниже:

https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html

 

Базовое использование индексов MySQL

По умолчанию, как только вы создаете таблицу с первичным ключом, MySQL автоматически создает специальный индекс, называемый PRIMARY, который хранится вместе с данными. Обычно это называется кластеризованным индексом.

Чтобы создать индекс в MySQL, мы можем использовать синтаксис, показанный ниже:

CREATE INDEX index_name ON tbl_name (cols);

Мы начинаем с вызова предложения CREATE INDEX, за которым следует имя индекса, который мы хотим создать. Затем мы указываем таблицу, в которой находится индекс, и, наконец, столбцы.

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

CREATE TABLE tbl_name (col1, col2, col3, INDEX(col1, col2));

 

Пример использования

На примере позвольте нам проиллюстрировать, как мы можем создать индекс для конкретной таблицы для повышения производительности.

Мы будем использовать таблицу фильмов в базе данных сотрудников. Вы можете найти ресурс на странице ресурсов ниже:

https://dev.mysql.com/doc/index-other.html

Во-первых, давайте посмотрим, как MySQL использует закулисный процесс для обработки простого оператора SELECT, в котором пол равен F.

EXPLAIN SELECT emp_no, first_name, last_name FROM employees WHERE gender = 'F';

 

Учитывая размер таблицы и запрашиваемых данных, сканирование более 200 000 строк не очень эффективно. В этом случае мы можем уменьшить это значение, создав индекс.

Чтобы создать индекс, мы можем:

CREATE INDEX gender on employees(gender);

 

После создания индекса MySQL просканирует значения.

 

Заключение

Мы надеемся, что эта статья дала вам более глубокое понимание использования индексов MySQL для повышения производительности базы данных.