Как добавить индексы в таблицы MySQL?

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

SELECT * FROM table WHERE id = '1';

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

SELECT * FROM table WHERE product_id = '1';

Это поле не было ранее проиндексировано; тем не менее, я добавил один, так что теперь mysql индексирует поле, но когда я пытаюсь выполнить вышеупомянутый запрос, он выполняется очень медленно. Запрос EXPLAIN показывает, что для поля product_id нет индекса, когда я его уже добавил, и в результате запрос занимает от 20 до 30 минут, чтобы вернуть одну строку.

Мои полные результаты EXPLAIN:

| id | select_type | table | type | possible_keys| key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------+------+--------------+------+---------+------+-------+------------------+
|  1 | SIMPLE      | table | ALL  | NULL         | NULL | NULL    | NULL |157211 | Using where |
+----+-------------+-------+------+--------------+------+---------+------+-------+------------------+

Может быть полезно отметить, что я только что посмотрел, и поле ID хранится как INT, тогда как поле PRODUCT_ID хранится как VARCHAR. Может ли это быть источником проблемы?

Ответы

Ответ 1

ALTER TABLE `table` ADD INDEX `product_id` (`product_id`)

Никогда не сравнивайте integer с strings в MySQL. Если id - int, удалите кавычки.

Ответ 2

ALTER TABLE TABLE_NAME ADD INDEX (COLUMN_NAME);

Ответ 3

Вы можете использовать этот синтаксис для добавления индекса и управления типом индекса (HASH или BTREE).

create index your_index_name on your_table_name(your_column_name) using HASH;
or
create index your_index_name on your_table_name(your_column_name) using BTREE;

Вы можете узнать о различиях между BTREE и HASH индексами здесь: http://dev.mysql.com/doc/refman/5.5/en/index-btree-hash.html

Ответ 4

Стоит отметить, что несколько индексов полей могут значительно улучшить производительность вашего запроса. Таким образом, в приведенном выше примере мы предполагаем, что ProductID является единственным полем для поиска, но если запрос скажет ProductID = 1 AND Category = 7, тогда поможет индекс из нескольких столбцов. Это достигается с помощью следующего:

ALTER TABLE 'table' ADD INDEX 'index_name' ('col1','col2')

Кроме того, индекс должен соответствовать порядку полей запроса. В моем расширенном примере индекс должен быть (ProductID, Category), а не наоборот.

Ответ 5

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

объяснение

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

tbl_student вас есть таблица tbl_student и вы хотите, чтобы student_id первичным ключом:

ALTER TABLE 'tbl_student' ADD PRIMARY KEY ('student_id')

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

Укажите имя индекса

ALTER TABLE 'tbl_student' ADD INDEX student_index ('student_id')

Выше оператор создаст обычный индекс с именем student_index.

Создать уникальный индекс

ALTER TABLE 'tbl_student' ADD UNIQUE student_unique_index ('student_id')

Здесь student_unique_index - это имя индекса, назначенное для student_id, и создает индекс, для которого значения должны быть уникальными (здесь можно принять ноль).

Полнотекстовая опция

ALTER TABLE 'tbl_student' ADD FULLTEXT student_fulltext_index ('student_id')

Вышеупомянутое утверждение создаст полнотекстовое имя индекса с student_fulltext_index, для которого вам нужен MyISAM Mysql Engine.

Как убрать индексы?

DROP INDEX 'student_index' ON 'tbl_student'

Как проверить доступные индексы?

SHOW INDEX FROM 'tbl_student'

Ответ 6

Вы говорите, что у вас есть индекс, объяснение говорит иначе. Однако, если вы действительно это сделаете, это как продолжить:

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

  1. Есть другой индекс в запросе, который MySQL считает более подходящим для использования, и он может использовать только один. Решением обычно является индекс, охватывающий несколько столбцов, если их обычный метод поиска по значению более одного столбца.
  2. MySQL решает, что есть много подходящих строк, и считает, что сканирование таблицы, вероятно, быстрее. Если это не так, иногда помогает ANALYZE TABLE.
  3. В более сложных запросах он решает не использовать его на основе чрезвычайно интеллектуально продуманного вуду в плане запросов, который по какой-то причине просто не соответствует вашим текущим требованиям.

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

Ответ 7

Индекс используется для ускорения поиска в базе данных. В MySQL есть хорошая документация по этому вопросу (которая актуальна и для других серверов SQL): http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html.

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

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

В MySQL вы можете использовать EXPLAIN перед оператором SELECT, чтобы увидеть, будет ли ваш запрос использовать какой-либо индекс. Это хорошее начало для устранения проблем с производительностью. Подробнее читайте здесь: http://dev.mysql.com/doc/refman/5.0/en/explain.html

Ответ 8

В MariaDB вы сделаете это по-другому.

CREATE INDEX index_name ON table_name (column_name);

см. документацию для дополнительных опций: https://mariadb.com/kb/en/mariadb/create-index/