Как использовать индексы в MySQL в Linux

at0mSec

Введение в индексы

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

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

К основным преимуществам использования индексов относятся:

  • Ускоренный поиск определенных строк и значений
  • Улучшена производительность различных запросов, особенно JOINов и формул WHERE
  • Возможность оптимизировать поиск данных в больших таблицах базы данных
  • Более быстрые операции сортировки и группировки по индексированным столбцам

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

  • NORMAL — Базовый индекс по одному или нескольким столбцам
  • UNIQUE — Обеспечивает уникальность индексируемых значений
  • FULLTEXT — Для оптимизации поиска по тексту
  • SPATIAL — Для пространственных данных, таких как местоположение

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

Подключение к MySQL и настройка базы данных образца

Перед демонстрацией индексов нам понадобится база данных MySQL с некоторыми образцами данных. Здесь описаны шаги по подключению к MySQL и созданию простой базы данных «контакты»:

  1. Подключитесь к серверу MySQL из командной строки с помощью клиента mysql:
$ mysql -u root -p
  1. Когда появится запрос, введите пароль для пользователя root MySQL. Теперь вы должны оказаться на мониторе MySQL.
  2. Создайте новую базу данных под названием contacts:
mysql> CREATE DATABASE contacts;
  1. Переключитесь на новую базу данных контактов:
mysql> USE contacts;
  1. Создайте contacts таблицу с некоторыми примерными данными:
mysql> CREATE TABLE contacts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  email VARCHAR(100),
  phone VARCHAR(20)
);
mysql> INSERT INTO contacts (first_name, last_name, email, phone) 
VALUES
  ('John', 'Doe', '[email protected]', '555-555-5555'),
  ('Jane', 'Doe', '[email protected]', '555-555-5556'),
  ('Bob', 'Smith', '[email protected]', '555-555-5557');

Здесь создается простая таблица для хранения контактной информации, такой как имя, электронная почта и номер телефона.

Мы можем убедиться, что все работает, сделав запрос к таблице:

mysql> SELECT * FROM contacts;

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

+----+------------+-----------+--------------------+---------------+
| id | first_name | last_name | email              | phone         |
+----+------------+-----------+--------------------+---------------+   
|  1 | John       | Doe       | [email protected]   | 555-555-5555  |
|  2 | Jane       | Doe       | [email protected]   | 555-555-5556  |
|  3 | Bob        | Smith     | [email protected]    | 555-555-5557  |
+----+------------+-----------+--------------------+---------------+
3 lignes trouvées (0.00 sec)

Использование одноколоночных индексов

Стандартный индекс в MySQL индексирует один столбец таблицы базы данных. Это позволяет быстро выполнять поиск и сортировку по данному столбцу.

Чтобы добавить индекс к столбцу, мы можем использовать функцию CREATE INDEX оператор:

mysql> CREATE INDEX idx_last_name ON contacts(last_name);

Это добавляет индекс с именем idx_last_name на last_name столбец contacts таблица.

Индексирование столбца фамилии позволяет оптимизировать такие запросы, как этот, которые фильтруют по фамилии:

mysql> SELECT * FROM contacts WHERE last_name="Doe";

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

+----+------------+-----------+--------------------+---------------+
| id | first_name | last_name | email              | phone         |
+----+------------+-----------+--------------------+---------------+
|  1 | John       | Doe       | [email protected]   | 555-555-5555  |
|  2 | Jane       | Doe       | [email protected]   | 555-555-5556  |
+----+------------+-----------+--------------------+---------------+
2 rows in set (0.00 sec)

Здесь показаны строки, возвращенные из таблицы contacts, где фамилия — ‘Doe’. Индекс по столбцу last_name позволяет быстро выполнить этот запрос без сканирования всей таблицы.

Мы можем добавить аналогичные индексы для других столбцов таблицы, например email или phone:

mysql> CREATE INDEX idx_email ON contacts(email);
mysql> CREATE INDEX idx_phone ON contacts(phone);

Это позволит быстро искать информацию и по этим столбцам.

Чтобы удалить индекс, если он больше не нужен, используйте DROP INDEX:

mysql> DROP INDEX idx_email ON contacts;

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

Использование уникальных индексов для предотвращения дублирования данных

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

mysql> CREATE UNIQUE INDEX idx_email ON contacts(email);

Этот индекс позволяет вставлять в этот столбец только уникальные значения электронной почты. Если мы попытаемся вставить дубликат электронной почты:

mysql> INSERT INTO contacts (first_name, last_name, email, phone)
VALUES ('Bob', 'Jones', '[email protected]', '555-555-5558');

, мы получим ошибку:

ERROR 1062 (23000): Duplicate entry '[email protected]' for key 'idx_email'

Уникальный индекс не позволил вставить дубликат письма. Это помогает обеспечить целостность данных в важных столбцах.

Как и другие индексы, индекс UNIQUE по-прежнему обеспечивает быстрый поиск по индексируемому столбцу. Уникальность — это просто дополнительное ограничение.

Использование индексов для нескольких столбцов

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

Например, чтобы проиндексировать столбцы имени и фамилии:

mysql> CREATE INDEX idx_name ON contacts(first_name, last_name);

Это может оптимизировать запрос с предложением WHERE для обоих столбцов:

mysql> SELECT * FROM contacts WHERE first_name="Jane"AND last_name="Doe";

Он также работает для запросов, фильтрующих только первый столбец:

mysql> SELECT * FROM contacts WHERE first_name="Jane";

Но он не оптимизирует запрос, фильтрующий только по второму индексированному столбцу:

mysql> SELECT * FROM contacts WHERE last_name="Doe";

В этом случае одноколоночный индекс на last_name будет использоваться вместо него.

Порядок столбцов в многостолбцовом индексе имеет значение. Оптимизация применяется к предложениям WHERE с фильтрами по префиксам столбцов индекса.

Перечисление и удаление существующих индексов

Чтобы понять, какие индексы существуют в таблице, мы можем запросить INFORMATION_SCHEMA к базе данных системы:

mysql> SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA ='contacts'AND TABLE_NAME ='contacts';

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

Например:

+------+------------+------------+------------+--------------+-------------+----------+------------+----------+--------+------+------------+---------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | NON_UNIQUE | INDEX_SCHEMA | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | COLLATION | CARDINALITY | SUB_PART | PACKED | NULLABLE | INDEX_TYPE | COMMENT |
+------+------------+------------+------------+--------------+-------------+----------+------------+----------+--------+------+------------+---------+---------------+
| def  | contacts   | contacts   |          0 | contacts     | PRIMARY     |            1 | id         | A        |           3 |     NULL | NULL   |         | BTREE      |         |  
| def  | contacts   | contacts   |          1 | contacts     | idx_email   |            1 | email      | A        |           3 |     NULL | NULL   | YES    | BTREE      |         |
+------+------------+------------+------------+--------------+-------------+----------+------------+----------+--------+------+------------+---------+---------------+

Когда индекс больше не нужен, мы можем удалить его с помощью DROP INDEX:

mysql> DROP INDEX idx_name ON contacts;

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

Заключение

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

Некоторые ключевые моменты, которые следует запомнить:

  • Используйте одноколоночные индексы для часто фильтруемых столбцов
  • Индексы UNIQUE предотвращают дублирование записей в столбце
  • Многостолбцовые индексы оптимизируют запросы, фильтрующие столбцы по порядку
  • Не переборщите с индексами! Оценивайте потребности запросов и удаляйте неиспользуемые индексы.

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *