Какво е индекс?
Индексите в MySQL сортират данните по организиран последователен начин. Те се създават в колоната (колоните), които ще се използват за филтриране на данните. Представете си индекса като сортиран по азбучен ред списък. По-лесно е да търсите имена, които са сортирани по азбучен ред, отколкото тези, които не са сортирани.
Използването на индекс на често актуализирани таблици може да доведе до лоша производителност. Това е така, защото MySQL създава нов индексен блок всеки път, когато данните се добавят или актуализират в таблицата. Обикновено индексите трябва да се използват в таблици, чиито данни не се променят често, но се използват много в избрани заявки за търсене.
Какво използва индекс?
Никой не обича бавните системи. Високата производителност на системата е от първостепенно значение в почти всички системи за бази данни. Повечето бизнеси инвестират сериозно в хардуер, така че извличането на данни и манипулациите могат да бъдат по-бързи. Но има ограничение за хардуерни инвестиции, които бизнесът може да направи. Оптимизирането на вашата база данни е по-евтино и по-добро решение.
Бавността във времето за реакция обикновено се дължи на записите, които се съхраняват произволно в таблици на базата данни. Заявките за търсене трябва да обикалят всички произволно съхранени записи един след друг, за да намерят желаните данни. Това води до лошо представяне на бази данни, когато става въпрос за извличане на данни от големи таблици. Следователно се използват индекси, които сортират данни, за да се улесни търсенето.
Синтаксис: Създаване на индекс
Индексите могат да бъдат дефинирани по 2 начина
- По време на създаването на таблицата
- След като таблицата е създадена
За нашия myflixdb очакваме много търсения в базата данни с пълно име.
Ще добавим колоната „пълни имена“ към индекса в нова таблица „members_indexed“.
Показаният по-долу скрипт ни помага да постигнем това.
CREATE TABLE `members_indexed` (`membership_number` int(11) NOT NULL AUTO_INCREMENT,`full_names` varchar(150) DEFAULT NULL,`gender` varchar(6) DEFAULT NULL,`date_of_birth` date DEFAULT NULL,`physical_address` varchar(255) DEFAULT NULL,`postal_address` varchar(255) DEFAULT NULL,`contact_number` varchar(75) DEFAULT NULL,`email` varchar(255) DEFAULT NULL,PRIMARY KEY (`membership_number`),INDEX(full_names)) ENGINE=InnoDB;
Изпълнете горния SQL скрипт в MySQL workbench срещу "myflixdb".
Опресняването на myflixdb показва новосъздадената таблица с име members_indexed.
Таблицата "Забележка" members_indexed има "пълни имена" във възела на индексите.
Тъй като базата на членовете се разширява и броят на записите се увеличава, заявките за търсене в таблицата members_indexed, които използват клаузите WHERE и ORDER BY, ще бъдат много по-бързи в сравнение с тези, изпълнявани в таблицата с членове, без дефиниран индекс.
Добавете основен синтаксис на индекса
Горният пример създаде индекса при дефиниране на таблицата на базата данни. Да предположим, че вече имаме дефинирана таблица и заявките за търсене в нея са много бавни. Отнема твърде много време, за да върнат резултатите. След проучване на проблема откриваме, че можем значително да подобрим производителността на системата, като създадем INDEX в най-често използваната колона в клаузата WHERE.
Можем да използваме следната заявка за добавяне на индекс
CREATE INDEX id_index ON table_name(column_name);
Да предположим, че заявките за търсене в таблицата с филми са много бавни и искаме да използваме индекс на „заглавието на филма“, за да ускорим заявките, можем да използваме следния скрипт, за да постигнем това.
CREATE INDEX `title_index` ON `movies`(`title`);
Изпълнението на горната заявка създава индекс в полето за заглавие в таблицата с филми.
Това означава, че всички заявки за търсене в таблицата с филми, използващи "заглавието", ще бъдат по-бързи.
Заявките за търсене в други полета в таблицата с филми обаче все още са по-бавни в сравнение с тези, базирани на индексираното поле.
Забележка: Можете да създавате индекси в множество колони, ако е необходимо, в зависимост от полетата, които възнамерявате да използвате за вашата търсачка на база данни.
Ако искате да видите индексите, дефинирани в определена таблица, можете да използвате следния скрипт, за да направите това.
SHOW INDEXES FROM table_name;
Нека сега да разгледаме всички индекси, дефинирани в таблицата с филми в myflixdb.
SHOW INDEXES FROM `movies`;
Изпълнението на горния скрипт в MySQL workbench срещу myflixdb ни дава резултати за създадения индекс.
Забележка: Първичните и външните ключове на таблицата вече са индексирани от MySQL. Всеки индекс има свое уникално име и колоната, в която е дефиниран, също е показана.
Синтаксис: Индекс на отпадане
Командата drop се използва за премахване на вече дефинирани индекси в таблица.
Може да има моменти, когато вече сте дефинирали индекс на таблица, който често се актуализира. Може да искате да премахнете индексите в такава таблица, за да подобрите производителността на UPDATE и INSERT заявките. Основният синтаксис, използван за пускане на индекс в таблица, е както следва.
DROP INDEX `index_id` ON `table_name`;
Нека сега разгледаме практически пример.
DROP INDEX ` full_names` ON `members_indexed`;
Изпълнението на горната команда изпуска индекса с id `пълни имена` от таблицата members_indexed.
Обобщение
- Индексите са много мощни, когато става въпрос за значително подобряване на производителността на заявките за търсене на MySQL.
- Индексите могат да бъдат дефинирани при създаване на таблица или добавени по-късно, след като таблицата вече е създадена.
- Можете да дефинирате индекси в повече от една колона в таблица.
- SHOW INDEX FROM table_name се използва за показване на дефинираните индекси на таблица.
- Командата DROP се използва за премахване на дефиниран индекс от дадена таблица.