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

271
21 января 2017, 11:19

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

Answer 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 уже достаточно) таблиц по неиндексированным полям. Убивает сервер на раз!

Answer 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 будет проиндексирован.

Answer 3

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

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

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

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

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

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

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

READ ALSO
Как добавить(прибавить) значение в поле MySQL?

Как добавить(прибавить) значение в поле MySQL?

Как добавить в поле БД значение, не перезаписывая его ? Например, добавить в поле words БД users значения (сначала одно, потом второе и пр), НО только...

316
Espresso тест на ожидание

Espresso тест на ожидание

Есть следующий тест, который при переходе в форму логина, вводит некорректные данные, в ответ на которые ему выводится дилоговое окно с текстом...

382
Тест не проходит

Тест не проходит

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

296
Создание библиотеки. Не могу понять, как предоставить возможность ее использовать

Создание библиотеки. Не могу понять, как предоставить возможность ее использовать

ЗдравствуйтеНаписал библиотеку с анимациями, своими классами, методами и т

390