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

Автор выбрал программу Write for DOnations.

Введение

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

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

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

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

Предпосылки

Чтобы следовать этому руководству, вам понадобится компьютер с системой управления реляционными базами данных (RDBMS) на основе SQL. Инструкции и примеры в этом руководстве были проверены с использованием следующей среды:

• Сервер под управлением Ubuntu 20.04, пользователь без полномочий root с правами администратора и брандмауэр, настроенный с помощью UFW, как описано в нашем руководстве по первоначальной настройке сервера для Ubuntu 20.04.
• MySQL установлен и защищен на сервере, как описано в шаге 3.
• Основные знания о выполнении запросов SELECT для извлечения данных из базы данных, как описано в нашем руководстве «Выбор строк из таблиц в SQL».

Примечание. Обратите внимание, что многие СУБД используют собственные уникальные реализации SQL. Хотя команды, описанные в этом руководстве, будут работать в большинстве СУБД, индексы не являются частью стандартного синтаксиса SQL, поэтому точный синтаксис или вывод могут отличаться, если вы тестируете их в системе, отличной от MySQL.

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

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

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

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

ssh sammy@your_server_ip

Затем откройте приглашение сервера MySQL, заменив sammy именем вашей учетной записи пользователя MySQL:

mysql -u sammy -p

Создайте базу данных с именем indexes:

CREATE DATABASE indexes;

Если база данных была создана успешно, вы получите такой вывод:

Output
Query OK, 1 row affected (0.01 sec)

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

USE indexes;

Вы получите следующий вывод:

Output
Database changed

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

Таблица employees будет содержать упрощенные данные о сотрудниках в базе данных. Он будет содержать следующие столбцы:

• employee_id: этот столбец содержит идентификатор сотрудника, представленный типом данных int. Этот столбец станет первичным ключом таблицы, а каждое значение станет уникальным идентификатором соответствующей строки.
• first_name: в этом столбце содержится имя каждого сотрудника, выраженное с использованием типа данных varchar с максимальным количеством символов 50.< /li>
• last_name: этот столбец содержит фамилию каждого сотрудника, выраженную с использованием типа данных varchar с максимальным количеством символов 50.< /li>
• device_serial: в этом столбце содержится серийный номер компьютера, назначенного сотруднику, выраженный с использованием типа данных varchar с максимальным значением 15 символов.
• salary: в этом столбце содержится зарплата каждого сотрудника, выраженная с использованием типа данных int, в котором хранятся числовые данные.

Создайте образец таблицы с помощью следующей команды:

CREATE TABLE employees (
    employee_id int,
    first_name varchar(50),
    last_name varchar(50),
    device_serial varchar(15),
    salary int
);

Если выводятся следующие выходные данные, таблица создана:

Output
Query OK, 0 rows affected (0.00 sec)

После этого загрузите в таблицу employees образцы данных, выполнив следующую операцию INSERT INTO:

INSERT INTO employees VALUES
    (1, 'John', 'Smith', 'ABC123', 60000),
    (2, 'Jane', 'Doe', 'DEF456', 65000),
    (3, 'Bob', 'Johnson', 'GHI789', 70000),
    (4, 'Sally', 'Fields', 'JKL012', 75000),
    (5, 'Michael', 'Smith', 'MNO345', 80000),
    (6, 'Emily', 'Jones', 'PQR678', 85000),
    (7, 'David', 'Williams', 'STU901', 90000),
    (8, 'Sarah', 'Johnson', 'VWX234', 95000),
    (9, 'James', 'Brown', 'YZA567', 100000),
    (10, 'Emma', 'Miller', 'BCD890', 105000),
    (11, 'William', 'Davis', 'EFG123', 110000),
    (12, 'Olivia', 'Garcia', 'HIJ456', 115000),
    (13, 'Christopher', 'Rodriguez', 'KLM789', 120000),
    (14, 'Isabella', 'Wilson', 'NOP012', 125000),
    (15, 'Matthew', 'Martinez', 'QRS345', 130000),
    (16, 'Sophia', 'Anderson', 'TUV678', 135000),
    (17, 'Daniel', 'Smith', 'WXY901', 140000),
    (18, 'Mia', 'Thomas', 'ZAB234', 145000),
    (19, 'Joseph', 'Hernandez', 'CDE567', 150000),
    (20, 'Abigail', 'Smith', 'FGH890', 155000);

База данных ответит сообщением об успехе:

Output
Query OK, 20 rows affected (0.010 sec)
Records: 20  Duplicates: 0  Warnings: 0

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

После этого вы готовы следовать остальной части руководства и начать использовать индексы в MySQL.

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

Как правило, когда вы выполняете запрос к базе данных MySQL, база данных должна просмотреть все строки в таблице одну за другой. Например, вы можете найти фамилии сотрудников, соответствующие Смит, или всех сотрудников с зарплатой выше 100 000 долларов США. Каждая строка в таблице будет проверена одна за другой, чтобы проверить, соответствует ли она условию. Если это так, он будет добавлен в список возвращаемых строк. Если это не так, MySQL будет сканировать последующие строки, пока не просмотрит всю таблицу.

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

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

Используя в качестве примера таблицу employees, один из типичных запросов, которые вы можете выполнить, — найти сотрудников по их фамилиям. Без каких-либо индексов MySQL будет извлекать каждого сотрудника из таблицы и проверять, соответствует ли фамилия запросу. Но при использовании индекса MySQL будет хранить отдельный список фамилий, содержащий только указатели на строки для заданных сотрудников в основной таблице. Затем он будет использовать этот индекс для получения результатов без сканирования всей таблицы.

Вы можете думать об указателях как об аналогии с телефонной книгой. Чтобы найти человека по имени Джон Смит в книге, вы сначала открываете нужную страницу, где перечислены люди с именами, начинающимися с S, а затем просматриваете страницы в поисках людей. с именами, начинающимися с Sm. Следуя этой логике, вы можете быстро удалить множество записей, зная, что они не соответствуют человеку, которого вы ищете. Процесс работает только потому, что данные в телефонной книге отсортированы по алфавиту, что редко бывает с данными, хранящимися непосредственно в базе данных. Индекс в механизме базы данных служит той же цели, что и телефонная книга, сохраняя упорядоченные в алфавитном порядке ссылки на данные и, таким образом, помогая базе данных быстро находить нужные строки.

Использование индексов в MySQL имеет множество преимуществ. Наиболее распространенными являются более быстрое ускорение предложений ORDER BY и обеспечение уникальности значений.

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

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

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

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

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

Одностолбцовый индекс является наиболее распространенным и простым типом индекса, который можно использовать для оптимизации производительности запросов. Этот тип индекса помогает базе данных ускорить запросы, которые фильтруют набор данных на основе значений из одного столбца. Индексы, созданные для одного столбца, могут ускорить многие условные запросы, включая точное совпадение с помощью оператора = и сравнения с операторами > или <.

В примере базы данных, созданном на предыдущем шаге, нет индексов. Перед созданием индекса вы сначала проверите, как база данных обрабатывает запросы SELECT к таблице employees, когда предложение WHERE используется только для запроса подмножество данных из таблицы.

Предположим, вы хотите найти сотрудников с зарплатой ровно $100000. Выполните следующий запрос:

SELECT * FROM employees WHERE salary = 100000;

Предложение WHERE запрашивает точное совпадение сотрудников с зарплатой, соответствующей запрошенному значению. В этом примере база данных ответит следующим образом:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           9 | James      | Brown     | YZA567        | 100000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)

Примечание. Как показано в приведенном выше выводе, база данных почти мгновенно ответила на выданный запрос. Имея всего несколько образцов строк в базе данных, использование индексов не окажет заметного влияния на производительность запросов. Однако при работе с большими наборами данных вы заметите значительные изменения во времени выполнения запроса, сообщаемом базой данных после выполнения запроса.

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

Чтобы получить доступ к плану запроса для запроса SELECT, выполните следующее:

EXPLAIN SELECT * FROM employees WHERE salary = 100000;

Команда EXPLAIN указывает MySQL выполнить запрос SELECT, но вместо возврата результатов она покажет информацию о том, как ядро базы данных выполнило запрос внутри.

План выполнения будет примерно таким (ваша таблица может немного отличаться):

Output
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

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

• possible_keys перечисляет индексы, которые MySQL рассматривает для использования. В данном случае их нет (NULL).
• key описывает индекс, который MySQL решил использовать при выполнении запроса. В данном случае индекс не использовался (NULL).
• rows показывает количество строк, которые MySQL должен был проанализировать по отдельности, прежде чем возвращать результаты. Здесь это 20, что соответствует количеству всех возможных строк в таблице. Это означает, что MySQL должен был сканировать каждую строку в таблице employees, чтобы найти единственную возвращенную строку.
• Extra показывает дополнительную описательную информацию о плане запроса. В этом примере аннотация Using where означает, что база данных отфильтровала результаты непосредственно из таблицы с помощью оператора WHERE.

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

Примечание. Более новые версии MySQL при использовании EXPLAIN показывают 1 строку в наборе, 1 предупреждение в выходных данных, в то время как более старые версии MySQL и MySQL- совместимые базы данных часто вместо этого просто показывают 1 строку в наборе. Предупреждение не является признаком проблемы. MySQL использует свой механизм предупреждений для предоставления дополнительной расширенной информации о плане запроса. Такое использование этой дополнительной информации выходит за рамки данного руководства. Вы можете узнать больше об этом поведении на странице расширенного формата вывода EXPLAIN в документации MySQL.

Запрос SELECT, который вы только что выполнили, использовал точное условие запроса, ГДЕ зарплата=100000. Далее давайте проверим, будет ли база данных вести себя аналогично с условием сравнения. Попробуйте получить сотрудников с зарплатой ниже 70000:

SELECT * FROM employees WHERE salary < 70000;

На этот раз база данных вернула две строки для John Smith и Jane Doe:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
|           2 | Jane       | Doe       | DEF456        |  65000 |
+-------------+------------+-----------+---------------+--------+
8 rows in set (0.000 sec)

Однако, когда вы используете EXPLAIN, чтобы понять выполнение запроса следующим образом:

EXPLAIN SELECT * FROM employees WHERE salary < 70000;

Вы заметите, что таблица почти идентична предыдущему запросу:

Output
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |    33.33 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

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

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

CREATE INDEX salary ON employees(salary);

Синтаксис оператора CREATE INDEX требует:

• Имя индекса, в данном случае salary. Имя должно быть уникальным в пределах одной таблицы, но может повторяться в разных таблицах одной и той же базы данных.
• Имя таблицы, для которой создается индекс. В данном случае это сотрудники.
• Список столбцов, для которых создается индекс. Здесь вы используете один столбец с названием salary для построения индекса.

Примечание. В зависимости от ваших прав пользователя MySQL вы можете получить сообщение об ошибке при выполнении команды CREATE INDEX: ОШИБКА 1142 (42000): команда INDEX запрещена пользователю user@host для сотрудников таблицы. Чтобы предоставить вашему пользователю разрешения INDEX, войдите в MySQL как root и выполните следующие команды, при необходимости заменив имя пользователя и хост MySQL:

GRANT INDEX on *.* TO 'sammy'@'localhost';
FLUSH PRIVILEGES;

После обновления разрешений пользователя выйдите из системы как пользователь root и снова войдите в систему как пользователь, а затем повторно запустите оператор CREATE INDEX.

База данных подтвердит, что индекс был успешно создан:

Output
Query OK, 0 rows affected (0.024 sec)
Records: 0  Duplicates: 0  Warnings: 0

Имея индекс, попробуйте повторить предыдущие запросы, чтобы проверить, не изменилось ли что-нибудь. Начните с получения одного сотрудника с зарплатой ровно 100000:

SELECT * FROM employees WHERE salary = 100000;

Результат будет таким же, как и раньше, с возвратом только James Brown:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           9 | James      | Brown     | YZA567        | 100000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)

Однако запрос MySQL на объяснение того, как он подошел к запросу, покажет некоторые отличия от предыдущих. Выполните запрос EXPLAIN следующим образом:

EXPLAIN SELECT * FROM employees WHERE salary = 100000;

На этот раз вывод будет таким:

Output
+----+-------------+-----------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+--------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | salary        | salary | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

MySQL заявляет, что из одного возможного ключа, показанного в possible_keys, он решил использовать ключ с именем salary, который является созданным вами индексом. В столбце rows теперь отображается 1 вместо 20. Поскольку использовался индекс, база данных избегала сканирования всех строк в базе данных и могла немедленно возвращать одну запрошенную строку. Столбец Extra теперь не упоминает Using WHERE, потому что для выполнения запроса не требовалось перебирать основную таблицу и проверять каждую строку на соответствие условию запроса.

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

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

Выполните следующий запрос:

SELECT * FROM employees WHERE salary < 70000;

Будут возвращены те же две строки для John Smith и Jane Doe:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
|           2 | Jane       | Doe       | DEF456        |  65000 |
+-------------+------------+-----------+---------------+--------+
8 rows in set (0.000 sec)

Однако при использовании EXPLAIN следующим образом:

EXPLAIN SELECT * FROM employees WHERE salary < 70000;

Таблица будет отличаться от предыдущего выполнения того же запроса:

Output
+----+-------------+-----------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | range | salary        | salary | 5       | NULL |    2 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

Столбец key говорит вам, что MySQL использовал индекс для выполнения запроса. В rows для возврата результата были проанализированы только две строки. На этот раз в столбце Дополнительно указано Использование условия индекса, что означает, что в данном конкретном случае MySQL отфильтровал с помощью индекса, а затем использовал основную таблицу только для извлечения уже совпадающие строки.

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

EXPLAIN SELECT * FROM employees WHERE salary < 140000;

План выполнения будет выглядеть следующим образом:

Output
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | salary        | NULL | NULL    | NULL |   20 |    80.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Несмотря на то, что зарплата указана в possible_keys, пустой столбец key (читая NULL) означает, что MySQL решил не используйте индекс, что может быть подтверждено отсканированными строками 20. Планировщик запросов к базе данных анализирует каждый запрос по возможным индексам, чтобы определить самый быстрый путь выполнения. Если стоимость доступа к индексу превышает выгоду от его использования (например, если запрос возвращает значительную часть данных исходной таблицы), база данных может решить, что на самом деле быстрее выполнить полное сканирование таблицы.

Аналогичным образом аннотации в столбце Дополнительно, такие как Использование условия индекса или Использование где, более подробно описывают, как ядро базы данных выполняло запрос. . В зависимости от контекста база данных может выбрать другой способ выполнения запроса, и у вас могут быть выходные данные с отсутствующей аннотацией Using index condition или с другой аннотацией. Это не означает, что индекс не используется должным образом, а только то, что база данных решила, что другой способ доступа к строкам будет более производительным.

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

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

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

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

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

Попробуйте добавить другого сотрудника с уже используемым серийным номером устройства:

INSERT INTO employees VALUES (21, 'Sammy', 'Smith', 'ABC123', 65000);

База данных обяжет и вставит строку, уведомив вас об успехе:

Output
Query OK, 1 row affected (0.009 sec)

Однако если вы теперь запросите базу данных о сотрудниках, использующих компьютер ABCD123, следующим образом:

SELECT * FROM employees WHERE device_serial = 'ABC123';

В результате вы получите двух разных людей:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
|          21 | Sammy      | Smith     | ABC123        |  65000 |
+-------------+------------+-----------+---------------+--------+
2 rows in set (0.000 sec)

Это не является ожидаемым поведением для поддержания базы данных employees в силе. Давайте отменим это изменение, удалив только что созданную строку:

DELETE FROM employees WHERE employee_id = 21;

Вы можете убедиться в этом, повторно выполнив предыдущий запрос SELECT:

SELECT * FROM employees WHERE device_serial = 'ABC123';

Опять же, только John Smith использует устройство с серийным номером ABC123:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)

Чтобы защитить базу данных от таких ошибок, вы можете создать уникальный индекс для столбца device_serial.

Для этого выполните:

CREATE UNIQUE INDEX device_serial ON employees(device_serial);

Добавление ключевого слова UNIQUE при создании индекса указывает базе данных, что значения в столбце device_serial не могут повторяться. С уникальными индексами все новые строки, добавляемые в таблицу, будут проверяться по индексу, чтобы определить, удовлетворяет ли значение столбца ограничению.

База данных подтвердит создание индекса:

Output
Query OK, 0 rows affected (0.021 sec)
Records: 0  Duplicates: 0  Warnings: 0

Теперь проверьте, возможно ли добавление повторяющейся записи в таблицу. Попробуйте снова выполнить ранее успешный запрос INSERT:

INSERT INTO employees VALUES (21, 'Sammy', 'Smith', 'ABC123', 65000);

На этот раз будет показано сообщение об ошибке:

Output
ERROR 1062 (23000): Duplicate entry 'ABC123' for key 'device_serial'

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

SELECT * FROM employees WHERE device_serial = 'ABC123';

Теперь возвращается одна строка:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)

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

EXPLAIN SELECT * FROM employees WHERE device_serial = 'ABC123';

План выполнения будет примерно таким (ваша таблица может немного отличаться):

Output
+----+-------------+-----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type  | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | const | device_serial | device_serial | 63      | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Индекс device_serial отображается как в столбце possible_keys, так и в столбце key, подтверждая, что индекс использовался при выполнении запроса.

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

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

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

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

SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John';

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

Если вы таким образом создали два отдельных индекса, MySQL будет знать, как найти всех сотрудников с именем Smith. Он также будет знать, как найти всех сотрудников по имени Джон. Однако он не знает, как найти людей по имени Джон Смит.

Чтобы проиллюстрировать проблему наличия двух отдельных индексов, представьте, что у вас есть две отдельные телефонные книги, одна из которых упорядочена по фамилиям, а другая — по именам. Обе телефонные книги напоминают индексы, созданные для столбцов last_name и first_name соответственно. Как пользователь телефонной книги, вы можете найти Джона Смита тремя возможными способами:

• Используйте телефонную книгу, упорядоченную по фамилиям, чтобы найти всех людей с именем Смит, игнорируйте вторую телефонную книгу и вручную просматривайте всех людей Смит, пока не найдете Джон Смит.
• Сделайте противоположное: используйте телефонную книгу, упорядоченную по именам, чтобы найти всех людей с именем Джон, игнорируйте вторую телефонную книгу и вручную просматривайте всех людей Джон по очереди. один, пока не найдете Джона Смита.
• Попробуйте использовать обе телефонные книги вместе: найдите всех людей с именем Джон и всех людей с именем Смит по отдельности, запишите промежуточные результаты и попробуйте вручную пересечь два подмножества данных для поиска людей из обоих отдельных списков.

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

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

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

В MySQL, чтобы создать многостолбцовый индекс для фамилий и имен в таблице employees, выполните:

CREATE INDEX names ON employees(last_name, first_name);

В этом случае оператор CREATE INDEX немного отличается. Теперь в скобках после названия таблицы (employees) указаны два столбца: last_name и затем first_name. Это создает многостолбцовый индекс для обоих столбцов. Порядок, в котором столбцы перечислены в определении индекса, важен, в чем вы вскоре убедитесь.

База данных покажет следующее сообщение, подтверждающее успешное создание индекса:

Output
Query OK, 0 rows affected (0.024 sec)
Records: 0  Duplicates: 0  Warnings: 0

Теперь попробуйте выполнить запрос SELECT, чтобы найти строки, в которых имя соответствует John, а фамилия соответствует Smith:

SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John';

Результатом является одна строка с сотрудником по имени Джон Смит:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)

Теперь используйте запрос EXPLAIN, чтобы проверить, использовался ли индекс:

EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John';

План выполнения будет примерно таким (ваша таблица может немного отличаться):

Output
+----+-------------+-----------+------------+------+---------------+-------+---------+-------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key   | key_len | ref         | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+-------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | names         | names | 406     | const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+-------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

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

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

Если индекс определен для обоих столбцов, что произойдет, если вы попытаетесь найти всех сотрудников с именем Смит, но не будете фильтровать по имени? Запустите измененный запрос:

SELECT * FROM employees WHERE last_name = 'Smith';

Вывод вернет следующее:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|          20 | Abigail    | Smith     | FGH890        | 155000 |
|          17 | Daniel     | Smith     | WXY901        | 140000 |
|           1 | John       | Smith     | ABC123        |  60000 |
|           5 | Michael    | Smith     | MNO345        |  80000 |
+-------------+------------+-----------+---------------+--------+
4 rows in set (0.000 sec)

Четыре сотрудника имеют фамилию Смит.

Еще раз откройте план выполнения запроса:

EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith';

План выполнения будет примерно таким (ваша таблица может немного отличаться):

Output
+----+-------------+-----------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+-------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | names         | names | 203     | const |    4 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+-------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

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

В предыдущих запросах столбец, используемый для фильтрации результатов (last_name), передавался первым в операторе CREATE INDEX. Теперь вы отфильтруете таблицу employees по first_name, который был вторым столбцом в списке столбцов для этого многостолбцового индекса. Выполните следующий запрос:

SELECT * FROM employees WHERE first_name = 'John';

Вывод будет выглядеть следующим образом:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)

Получите доступ к плану выполнения запроса:

EXPLAIN SELECT * FROM employees WHERE first_name = 'John';

Вывод будет выглядеть следующим образом:

Output
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

И снова возвращенные результаты содержат одного сотрудника, но на этот раз индекс не использовался. База данных просканировала всю таблицу, о чем свидетельствует аннотация Using where в столбце Extra, а также отсканированные строки 20.

В этом случае база данных не использовала индекс из-за порядка столбцов, переданных в оператор CREATE INDEX при первом создании индекса: last_name, first_name. База данных может использовать индекс только в том случае, если запрос использует либо первый столбец, либо и первый, и второй столбцы; он не может поддерживать запросы к индексу, если не используется первый столбец определения индекса.

С индексом, созданным для нескольких столбцов, база данных может использовать индекс для ускорения запросов, включающих все индексированные столбцы или растущий левый префикс всех индексированных столбцов. Например, многостолбцовый индекс, включающий столбцы (a, b, c), можно использовать для ускорения запросов, включающих все три столбца, и запросов, включающих только первые два столбца или даже запросы, которые включают только первый столбец. С другой стороны, индекс не поможет с запросами, включающими только последний столбец, c, или последние два столбца, b и c.

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

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

Список и удаление существующих индексов

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

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

SHOW INDEXES FROM employees;

Вывод будет похож на следующий:

Output
+-----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table     | Non_unique | Key_name      | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| employees |          0 | device_serial |            1 | device_serial | A         |          20 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| employees |          1 | salary        |            1 | salary        | A         |          20 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| employees |          1 | names         |            1 | last_name     | A         |          16 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| employees |          1 | names         |            2 | first_name    | A         |          20 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.01 sec)

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

Чтобы удалить существующие индексы, вы можете использовать оператор SQL DROP INDEX. Представьте, что вы больше не хотите обеспечивать уникальность столбца device_serial. Таким образом, индекс device_serial больше не понадобится. Выполните следующую команду:

DROP INDEX device_serial ON employees;

device_serial — это имя индекса, а employees — это таблица, для которой был определен индекс. База данных подтвердит удаление индекса:

Output
Query OK, 0 rows affected (0.018 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

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

Заключение

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

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

Если вы хотите узнать больше о различных концепциях языка SQL и работе с ним, мы рекомендуем вам ознакомиться с другими руководствами из серии «Как использовать SQL».