MySQL индексирование таблиц

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

Общая информация по индексам

1. Индексы можно проставлять по одному столбцу или по комбинации столбцов
2. Режим содержания для индекса это или режим повторяющихся значений, или режим только уникальных значений
3. Одна таблица может иметь более одного индекса, использующего различные столбцы таблицы
4. Строковый тип, кроме ENUM и SET можно индексировать по первым некотором кол-ве символов слева. Нельзя создавать индексы по столбцам типа BLOB и TEXT, пока не задана длина префикса. Индексирование только префикса столбца позволяет уменьшить индекс и ускорить доступ к нему
5. Индекс таблиц типа InnoDB так же может строиться и на внешних ключах, т.е. значения в индексе должны соответствовать значениям, представленным в другой таблице. MyISAM таблицы не поддерживают внешние ключи, зато можно использовать индекс FULLTEXT, которые нельзя использовать в InnoDB таблицах

Примеры добавления индексов

Добавление обычного индекса (дублирование записей в обычном индексе разрешено):

ALTER TABLE tbl_name ADD INDEX index_name (index_columns);

где tbl_name - имя таблицы, index_name - имя индекса (указывать не обязательно), index_columns - список индексируемых столбцов

Пример добавления обычного индекса к уже имеющейся таблице:

mysql> alter table users add index index_for_name (`name`);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from users;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| users |          1 | index_for_name |            1 | name        | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

Добавление уникального индекса (соответственно дублирование запрещено):

ALTER TABLE tbl_name ADD UNIQUE index_name (index_columns);
ALTER TABLE tbl_name ADD PRIMARY KEY index_name (index_columns);

Индекс типа PRIMARY KEY не может содержать пустых значений, а индекс UNIQUE пару пустых значений содержать может. Это все связано с тем что нельзя сравнивать между собой два пустых значения.

Пример добавления PRIMARY KEY к уже созданной таблице:

mysql> ALTER TABLE account_access ADD PRIMARY KEY (`id`, `RealmID`);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from account_access;
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table          | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| account_access |          0 | PRIMARY  |            1 | id          | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| account_access |          0 | PRIMARY  |            2 | RealmID     | A         |           0 |     NULL | NULL   |      | BTREE      |         |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

Добавление индексов по префиксу столбца строкового типа

Пример добавления индекса по префиксу при создании таблицы:

mysql> create table users (name char(50) not null, address binary(100) not null, index index_for_name (name(20)), index index_for_address (address(25)));
Query OK, 0 rows affected (0.01 sec)

mysql> show index from users;
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| users |          1 | index_for_name    |            1 | name        | A         |        NULL |       20 | NULL   |      | BTREE      |         |
| users |          1 | index_for_address |            1 | address     | A         |        NULL |       25 | NULL   |      | BTREE      |         |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

В итоге создался индекс по первым 20 символам столбца CHAR и первым 25 байтам столбца BINARY

Пример добавления индекса по префиксу к уже созданной таблице:

mysql> alter table users add index index_for_name (`name`(20));
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from users;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| users |          1 | index_for_name |            1 | name        | A         |        NULL |       20 | NULL   |      | BTREE      |         |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

Примеры удаления индексов

Удаление индексов

ALTER TABLE tbl_name DROP INDEX index_name;

Пример удаления обычного индекса

mysql> show index from users;
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| users |          1 | index_for_name    |            1 | name        | A         |        NULL |       20 | NULL   |      | BTREE      |         |
| users |          1 | index_for_address |            1 | address     | A         |        NULL |       25 | NULL   |      | BTREE      |         |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

mysql> alter table users drop index index_for_address;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from users;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| users |          1 | index_for_name |            1 | name        | A         |        NULL |       20 | NULL   |      | BTREE      |         |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

ALTER TABLE tbl_name DROP PRIMARY KEY; 

Пример (имя индекса при удалении PRIMARY KEY не указывается, так как в одной таблице может быть только один первичный ключ и его имя всегда будет PRIMARY) удаления PRIMARY KEY

mysql> show index from account_access;
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table          | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| account_access |          0 | PRIMARY  |            1 | id          | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| account_access |          0 | PRIMARY  |            2 | RealmID     | A         |           0 |     NULL | NULL   |      | BTREE      |         |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

mysql> alter table account_access drop primary key;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from account_access;
Empty set (0.00 sec)

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