Начало работы с индексами

Для очень краткого обзора см. Основные принципы индекса.

Существует четыре основных типа индексов: первичные ключи (уникальные и не допускающие NULL), уникальные индексы (уникальные и могут допускать NULL), обычные индексы (не обязательно уникальные) и индексы полнотекстового поиска (для полнотекстового поиска).

Термины «КЛЮЧ» и «ИНДЕКС» обычно используются взаимозаменяемо, и операторы должны работать с любым из этих ключевых слов.

Первичный ключ

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

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

Многие таблицы используют числовое поле ID в качестве первичного ключа. Атрибут AUTO_INCREMENT может использоваться для генерации уникального идентификатора для новых строк и обычно используется с первичными ключами.

Первичные ключи обычно добавляются при создании таблицы с помощью оператора CREATE TABLE. Например, следующая команда создает первичный ключ для поля ID. Обратите внимание, что поле ID должно быть определено как NOT NULL, иначе индекс не может быть создан.

CREATE TABLE `Employees` (
  `ID` TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  `First_Name` VARCHAR(25) NOT NULL,
  `Last_Name` VARCHAR(25) NOT NULL,
  `Position` VARCHAR(25) NOT NULL,
  `Home_Address` VARCHAR(50) NOT NULL,
  `Home_Phone` VARCHAR(12) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=Aria;

Вы не можете создать первичный ключ с помощью команды CREATE INDEX. Если вы хотите добавить его после того, как таблица уже создана, используйте команду ALTER TABLE, например:

ALTER TABLE Employees ADD PRIMARY KEY(ID);

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

Таблицы в базе данных information_schema могут быть запрошены для поиска таблиц, у которых нет первичных ключей. Например, здесь показан запрос, использующий таблицы TABLES и KEY_COLUMN_USAGE, которые можно использовать:

SELECT t.TABLE_SCHEMA, t.TABLE_NAME
FROM information_schema.TABLES AS t
LEFT JOIN information_schema.KEY_COLUMN_USAGE AS c 
ON t.TABLE_SCHEMA = c.CONSTRAINT_SCHEMA
   AND t.TABLE_NAME = c.TABLE_NAME
   AND c.CONSTRAINT_NAME = 'PRIMARY'
WHERE t.TABLE_SCHEMA != 'information_schema'
   AND t.TABLE_SCHEMA != 'performance_schema'
   AND t.TABLE_SCHEMA != 'mysql'
   AND c.CONSTRAINT_NAME IS NULL;

Уникальный индекс

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

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

CREATE TABLE `Employees` (
  `ID` TINYINT(3) UNSIGNED NOT NULL,
  `First_Name` VARCHAR(25) NOT NULL,
  `Last_Name` VARCHAR(25) NOT NULL,
  `Position` VARCHAR(25) NOT NULL,
  `Home_Address` VARCHAR(50) NOT NULL,
  `Home_Phone` VARCHAR(12) NOT NULL,
  `Employee_Code` VARCHAR(25) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY (`Employee_Code`)
) ENGINE=Aria;

Уникальные ключи также могут быть добавлены после создания таблицы с помощью команды CREATE INDEX или команды ALTER TABLE, например:

ALTER TABLE Employees ADD UNIQUE `EmpCode`(`Employee_Code`); 

и

CREATE UNIQUE INDEX HomePhone ON Employees(Home_Phone);

Индексы могут содержать более одного столбца. MariaDB может использовать один или несколько столбцов в левой части индекса, если не может использовать весь индекс (за исключением типа индекса HASH).

Рассмотрим другой пример:

CREATE TABLE t1 (a INT NOT NULL, b INT, UNIQUE (a,b));

INSERT INTO t1 values (1,1), (2,2);

SELECT * FROM t1;
+---+------+
| a | b    |
+---+------+
| 1 |    1 |
| 2 |    2 |
+---+------+

Поскольку индекс определен как уникальный для обоих столбцов a и b, следующая строка допустима, так как, хотя ни a, ни b не уникальны сами по себе, их комбинация уникальна:

INSERT INTO t1 values (2,1);

SELECT * FROM t1;
+---+------+
| a | b    |
+---+------+
| 1 |    1 |
| 2 |    1 |
| 2 |    2 |
+---+------+

Тот факт, что ограничение UNIQUE может быть NULL часто упускается из виду. В SQL любое NULL никогда не равно чему-либо, даже другому NULL. Следовательно, ограничение UNIQUE не помешает хранению дублирующих строк, если они содержат значения NULL:

INSERT INTO t1 values (3,NULL), (3, NULL);

SELECT * FROM t1;
+---+------+
| a | b    |
+---+------+
| 1 |    1 |
| 2 |    1 |
| 2 |    2 |
| 3 | NULL |
| 3 | NULL |
+---+------+

Действительно, в SQL две последние строки, даже если они идентичны, не равны друг другу:

SELECT (3, NULL) = (3, NULL);

+---------------------- +
| (3, NULL) = (3, NULL) |
+---------------------- +
| 0                     |
+---------------------- +

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

create table Table_1 (
  user_name varchar(10),
  status enum('Active', 'On-Hold', 'Deleted'),
  del char(0) as (if(status in ('Active', 'On-Hold'),'', NULL)) persistent,
  unique(user_name,del)
)

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

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

create table t1 (a int primary key,
b blob,
c1 varchar(1000),
c2 varchar(1000),
c3 varchar(1000),
c4 varchar(1000),
c5 varchar(1000),
c6 varchar(1000),
c7 varchar(1000),
c8 varchar(1000),
c9 varchar(1000),
unique key `b` (b),
unique key `all_c` (c1,c2,c3,c4,c6,c7,c8,c9)) engine=myisam;

show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` blob DEFAULT NULL,
  `c1` varchar(1000) DEFAULT NULL,
  `c2` varchar(1000) DEFAULT NULL,
  `c3` varchar(1000) DEFAULT NULL,
  `c4` varchar(1000) DEFAULT NULL,
  `c5` varchar(1000) DEFAULT NULL,
  `c6` varchar(1000) DEFAULT NULL,
  `c7` varchar(1000) DEFAULT NULL,
  `c8` varchar(1000) DEFAULT NULL,
  `c9` varchar(1000) DEFAULT NULL,
  PRIMARY KEY (`a`),
  UNIQUE KEY `b` (`b`) USING HASH,
  UNIQUE KEY `all_c` (`c1`,`c2`,`c3`,`c4`,`c6`,`c7`,`c8`,`c9`) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci

Обычные индексы

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

CREATE TABLE t2 (a INT NOT NULL, b INT, INDEX (a,b));

INSERT INTO t2 values (1,1), (2,2), (2,2);

SELECT * FROM t2;
+---+------+
| a | b    |
+---+------+
| 1 |    1 |
| 2 |    2 |
| 2 |    2 |
+---+------+

Индексы полнотекстового поиска

Индексы полнотекстового поиска поддерживают полнотекстовый поиск. См. раздел Индексы полнотекстового поиска.

Выбор индексов

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

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

Если ваш запрос содержит что-то вроде LIKE '%word%', без индекса полнотекстового поиска каждый раз используется полный перебор таблицы, что очень медленно.

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

Используйте команду CREATE INDEX для создания индекса.

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

Просмотр индексов

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

Если вы хотите узнать, как пересоздать индекс, выполните SHOW CREATE TABLE.

Когда удалять индекс

Если индекс редко используется (или вообще не используется), удалите его, чтобы повысить производительность INSERT и UPDATE.

Если включена статистика пользователей, таблица Information Schema INDEX_STATISTICS хранит статистику использования индексов.

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

См. также

• AUTO_INCREMENT
• Основные принципы индекса