Как да видя кой индекс използва дадена заявка?

Изпълнете EXPLAIN пред оператора SELECT. Колоната key показва кой индекс е избрал оптимизаторът, докато type и rows ви казват дали пътят за достъп е ефикасен.

MySQL Индекс: Урок за създаване, добавяне и пускане

⚡ Умно обобщение

MySQL Урокът за индексиране разглежда как индексите сортират и намират данни бързо. Индексът е сортирана структура за търсене, създадена върху една или повече колони; CREATE INDEX го добавя, SHOW INDEXES го проверява, а DROP INDEX го премахва, когато таблиците с голям обем за запис надвишават предимствата за четене.

???? Третирайте индексите като речник: Те сортират стойностите на колоните, така че енджина да може да намира редове, без да сканира цялата таблица.
🛠️ Създайте на масата или след това: Дефинирайте индекс в CREATE TABLE или го добавете по-късно с CREATE INDEX върху активна таблица.
🔍 Проверете с SHOW INDEXES: Използвайте SHOW INDEXES FROM table_name, за да изброите всеки индекс, ключова част, кардиналност и флаг за уникалност.
🧹 Премахване, когато цената на запис е твърде висока: Индексите забавят INSERT и UPDATE — премахнете неизползваните с DROP INDEX, за да възстановите пропускателната способност при запис.
🤖 Използвайте изкуствен интелект за проектиране на индекси: Асистентите с изкуствен интелект четат лог файлове за бавни заявки, предлагат подредба на колоните за съставни индекси и обясняват плановете EXPLAIN ред по ред.

Прочетете повече

Какво е MySQL Индекс?

An индекс in MySQL е структура от данни, която съхранява стойностите на колоните по подреден начин, така че системата да може бързо да търси редове. Индексите се създават в колоната или колоните, които най-често се използват за филтриране на данни. Представете си индекс като сортиран по азбучен ред списък: много по-бързо е да се намери име в сортиран списък, отколкото в несортирана купчина.

Индексите носят компромис — всяко INSERT или UPDATE трябва да поддържа индекса, така че добавянето на твърде много индекси към таблица с голям обем на запис може да навреди на цялостната производителност. Като общо правило, индексирайте колони, които се появяват в клаузите WHERE, JOIN и ORDER BY на таблици, които се четат по-често, отколкото се записват.

Защо да използваме индекс?

Никой не харесва бавни системи. Високата производителност е основен приоритет за почти всяко приложение, поддържано от база данни. Бизнесът харчи много за хардуер, за да поддържа заявките бързи, но има таван на това, което един хардуер може да осигури. Оптимизирането на индекси е по-евтин и по-ефективен лост.

Бавното време за реакция обикновено се дължи на това, че редовете се съхраняват във физически ред на диска. Без индекс, MySQL трябва да сканира всеки ред, за да намери тези, които съответстват на даден предикат – „пълно сканиране на таблицата“. Индексите позволяват MySQL преминава директно към съответстващите редове, което трансформира плана на заявката от O(n) на приблизително O(log n) за търсения в B-дърво.

Синтаксис: Създаване на индекс

Индексът може да бъде дефиниран на две места:

По време на създаването на таблицата.
След като таблицата вече съществува.

Пример: Създаване на индекс в кода с CREATE TABLE

За myflixdb базата данни, очакваме много търсения в колоната с пълно име. Скриптът по-долу създава нова members_indexed таблица с индекс върху full_names колона.

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;

Изпълнете скрипта в MySQL Работна маса срещу myflixdb база данни.

Обновяване myflixdb да видя новото members_indexed маса. full_names колоната сега се показва под Индекси възел.

С нарастването на членството, заявките за търсене на members_indexed които използват WHERE и ORDER BY спрямо full_names са много по-бързи от същите заявки в оригинала members таблица без индекса.

Добавяне на индекс, след като таблицата вече съществува

Често ще откриете, че съществуваща таблица се нуждае от индексиране — заявките за търсене са бавни, а планът EXPLAIN показва пълно сканиране на таблицата в колона, която се появява в WHERE. CREATE INDEX Изразът добавя индекс, без да пресъздава таблицата.

CREATE INDEX `id_index` ON `table_name` (`column_name`);

Конкретен пример — ускоряване на търсенията в title колоната на movies таблица:

CREATE INDEX `title_index` ON `movies` (`title`);

Всяка заявка, която филтрира по movies.title вече е подкрепен от новия индекс. Заявките, които филтрират по други колони, все още сканират таблицата, освен ако нямат собствен индекс.

Забележка: Можете да създадете съставен индекс в множество колони, когато заявките ви винаги филтрират или сортират по една и съща комбинация. Редът е важен – водещата колона определя дали индексът може да се използва.

Изброяване на индекси в таблица

употреба SHOW INDEXES за да видите всеки индекс, дефиниран в таблица.

SHOW INDEXES FROM `table_name`;

Пример — изброяване на индекси на movies таблица:

SHOW INDEXES FROM `movies`;

Изпълнете оператора в MySQL Workbench срещу myflixdb за да видите съществуващите индекси и колоните, които те покриват.

Забележка: Първичните и външните ключове се индексират автоматично от MySQLВсеки индекс има уникално име и изброява колоната(ите), която(ите) покрива.

Синтаксис: Изтриване на индекс

употреба DROP INDEX за премахване на съществуващ индекс от таблица. Това е полезно, когато таблица с многобройни операции за запис се забавя от индекс, който вече не се справя добре от страната на четене.

DROP INDEX `index_id` ON `table_name`;

Конкретен пример — пуснете full_names индекс от members_indexed:

DROP INDEX `full_names` ON `members_indexed`;

Видове MySQL Индекси

MySQL поддържа няколко типа индекси, всеки от които е подходящ за различно натоварване.

Тип	Цел
ОСНОВЕН КЛЮЧ	Уникален идентификатор на ред; клъстериран с данните от таблицата в InnoDB.
НОВО СТРОИТЕЛСТВО	Налага уникалност, докато служи като индекс.
ИНДЕКС (B-дърво)	Вторичен индекс по подразбиране, използван за заявки за диапазон и търсения за равенство.
ПЪЛЕН ТЕКСТ	Оптимизирано за търсене на текст на естествен език с MATCH … AGAINST.
ПРОСТРАНСТВЕНО	R-дърво индекс за типове ГИС данни като POINT и POLYGON.
HASH	Търсения за равенство за постоянно време; използвани от двигателя за съхранение MEMORY.
Композитен (многоколонен)	Комбинира няколко колони в един индекс; спазва правилото за най-левия префикс.

Най-добри практики за MySQL Индекси

Следните навици поддържат индексите полезни и предотвратяват превръщането им в ненужни ресурси.

Индекс за шаблона на заявката, а не името на колоната: добавете индекси, които съответстват на реални клаузи WHERE, JOIN и ORDER BY, а не на „всяка колона, която звучи важна“.
Следете реда на композитния индекс: Водещата колона трябва да се появи в заявката, за да може индексът да се използва.
Избягвайте дублиращи се индекси: Водещ префикс на съставен индекс вече покрива търсения в една колона с този префикс.
Проверете с ОБЯСНЕНИЕ: потвърдете, че плановикът действително избира новия индекс.
Премахнете неизползваните индекси: употреба sys.schema_unused_indexes in MySQL 5.7+ за намиране на индекси, които нищо не чете.
Съвпадение на типове данни: Ако клауза WHERE сравнява колона VARCHAR с число, индексът не може да се използва поради имплицитно преобразуване.

Въпроси и Отговори

Първичният ключ уникално идентифицира всеки ред и винаги е индексиран. Общият ИНДЕКС ускорява търсенето, но позволява дублиращи се стойности. Всеки първичен ключ е индекс, но не всеки индекс е първичен ключ.

Избягвайте индекси върху много малки таблици, върху колони с много малко различни стойности (ниска кардиналност) и върху таблици, в които се пише много по-често, отколкото се четат. Всеки допълнителен индекс забавя всяко INSERT, UPDATE и DELETE.

Съставният (многоколонен) индекс обхваща повече от една колона в един индекс. Той спазва правилото за най-левия префикс, така че може да обслужва заявки, които филтрират по първата колона, първите две колони и т.н., но не само по втората колона.

бягане EXPLAIN пред оператора SELECT. ключ колоната показва кой индекс е избрал оптимизаторът, докато тип намлява редове ще ви кажат дали пътят за достъп е ефикасен.

Покриващият индекс съдържа всяка колона, от която се нуждае заявката, така че системата отговаря на заявката само от индекса, без да чете таблицата. EXPLAIN докладва „Използва се индекс“, когато това се случи.

Често срещани причини включват увиванеping колоната във функция (WHERE YEAR(col) = …), имплицитни преобразувания на типове, много ниска кардиналност и остаряла статистика. Изпълнете ANALYZE TABLE за обновяване на статистиката и проверка EXPLAIN поради истинската причина.

Асистентите с изкуствен интелект приемат регистрационни файлове за бавни заявки, класифицират най-скъпите модели, предлагат индекси с една колона или съставни индекси и обясняват плановете EXPLAIN на разбираем език. Те намаляват времето за настройка от часове на минути за рутинни натоварвания.

Да. Инструментите с изкуствен интелект превръщат заявка като „ускоряване на търсенето на клиенти по имейл и дата на регистрация“ в работещ оператор CREATE INDEX, препоръчват ред на колоните и обясняват очакваното въздействие върху производителността при четене и запис.