Что такое индекс mysql и как их использовать

#mysql #sql

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

Ответы

Ответ 1

Если в кратце, то индекс, это поле по которому оптимизирован(ускорен) поиск.

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

Допустим есть таблица.

CREATE TABLE MyGuests (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    firstname VARCHAR(30) NOT NULL,
    lastname VARCHAR(30) NOT NULL,
    email VARCHAR(50),
    reg_date TIMESTAMP
)


id - уже индекс

Допустим вам нужен поиск по имени (firstname). 

SELECT * FROM MyGuests WHERE firstname = "Вася"


тогда есть смысл добавить индекс по данному полю.

CREATE INDEX firstname_index ON MyGuests (firstname) USING BTREE;


Будет созданна "карта" которая позволет легко находить записи в оригинальном списке.

https://ru.wikipedia.org/wiki/B-%D0%B4%D0%B5%D1%80%D0%B5%D0%B2%D0%BE

Для одной небольшой таблицы приемущество не будет очевидно < 1000 записей, но только
до тех пор, пока вы не попытаетесь объеденит join несколько (3-4 уже достаточно) таблиц
по неиндексированным полям. Убивает сервер на раз!


Ответ 2

Вкратце, индексы создаются для повышения производительности поиска данных. Таблицы
могут иметь огромное количество строк, которые хранятся в произвольном порядке. Без
индекса поиск нужных строк идёт по порядку (последовательно), что на больших объемах
данных отнимает много времени.

Индекс - обычно один или несколько столбцов таблицы и указателей на соответствующие
строки таблицы, позволяет искать строки, удовлетворяющие критерию поиска. Ускорение
работы с использованием индексов достигается в первую очередь за счёт того, что индекс
имеет структуру, оптимизированную под поиск — например, в MySQL b-дерева. Индекс лучше
использовать на тех столбцах таблицы, на которые вы чаще всего будете накладывать условия
через where column_name = ...



Индекс создаётся по правилу:

create index название_индекса
on название_таблицы (название_столбца)


Например, у вас таблица называется test, где хранятся данные по городам России с
улицами вида Город, Улица, Дом. Понятно, что строк в таблице при таком раскладе будет
много. Если вы часто делаете выборку по определенному городу, например:

select *
from test
where city = 'Омск'


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

create index city_index
on test (city)


Тогда тот же самый запрос

select *
from test
where city = 'Омск'


отработает гораздо быстрее, если столбец city будет проиндексирован.


Ответ 3

На пальцах можно объяснить так:

Когда Вы создаёте таблицу, добавляете в неё данные, то таблица разрастается и она
выглядит как просто последовательный список, упорядоченный по тому как в неё данные
добавлялись.

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

Это связано с тем, что когда Вы ищите какую-то запись, то просматриваются все записи,
пока не дойдут до нужной.

Когда Вам это окончательно надоедает и Вы хотите что-нибудь сделать, то к Вам на
помощь приходят индексы.

Индекс создаётся по какому-то определённому полю (можно по нескольким) по которому,
обычно, выполняется поиск. Когда Вы создаёте индекс, то MySql (и любая другая БД) обходит
все записи в таблице и строит дерево (скорее всего B-дерево или разновидность), в котором
ключами выступает выбранное поле, а содержимым ссылки на записи в таблице.

И когда Вы делаете очередной свой select запрос по таблице, по полю для которого
создали индекс MySql (и любая другая БД) знает что у неё есть индекс, по которому пройтись
будет быстрее, нежели перебирать все записи и Ваш запрос будет направлен этому индексу
и записи, удовлетворяющие условию, будут найдены гораздо быстрее, так как поиск по
построенному дереву будет гораздо быстрее, нежели простой перебор всех записей.