Как да използвате индекси за повишаване на производителността на MySQL

Начало » Питон » Как да използвате индекси за повишаване на производителността на MySQL

Ако вашата база данни MySQL или MariaDB е започнала да се чувства бавна, интелигентното индексиране обикновено е най-бързият начин да постигнете значително увеличение на производителността. Добре проектираните индекси могат да превърнат болезнено бавните заявки в бързи отговори, особено когато таблиците ви вече съхраняват стотици хиляди или милиони редове.

Обратната страна е, че лошото или прекомерно индексиране може тихомълком да намали производителността, да увеличи паметта и да накара записите да се забавят. така че ви е необходим солиден ментален модел за това как работят индексите, кои типове предлага MySQL, как InnoDB използва паметта и какви типични грешки трябва да се избягват. Точно за това е това подробно ръководство.

Какво е индекс на база данни в MySQL?

Индексът на базата данни е структура от данни, която позволява на MySQL да намира редове много по-бързо, отколкото да сканира цялата таблица, много подобно на индекса в края на книга. Вместо да проверява всеки ред един по един, MySQL следва компактна, подредена структура, която сочи директно към съответстващите записи.

Когато изпълнявате заявка без използваем индекс, MySQL обикновено трябва да извърши пълно сканиране на таблицата, четене на всеки ред, за да се провери дали отговаря на условията във вашите клаузи WHERE или JOIN. При големи таблици това става изключително бавно и изисква много I/O операции.

MySQL и MariaDB разчитат главно на балансирано дървовидно (B-Tree) индексиране за повечето работни натоварвания, където ключовете се съхраняват в йерархична дървовидна структура. Тази структура поддържа ключовете сортирани и позволява на MySQL да намира стойност за логаритмично време, като обхожда дървото, вместо да чете всеки ред от диска.

За да го направим по-конкретно, представете си таблица „Клиенти“, където често търсите по first_name = „Ava“, но няма индекс за first_name. MySQL трябва да проверява first_name на всеки ред, докато не намери съвпадащите, което означава, че средата за изпълнение расте линейно с размера на таблицата.

След като добавите B-дървовиден индекс към first_name, MySQL изгражда сортирано дърво от всички стойности на first_name, заедно с указателите на редове, така че може да прескача през нивата на дървото, следвайки левия или десния клон, докато достигне крайния възел, който препраща към всички редове, където first_name = 'Ava'. Вече не е необходимо да проверява всеки ред в таблицата.

Основни типове индекси, които ще използвате в MySQL

MySQL предлага няколко начина за индексиране на данни, всеки от които е оптимизиран за различни модели на заявки и форми на данни, и разбирането на тези опции ви помага да изберете правилния индекс, вместо сляпо да ги добавяте навсякъде.

1. Едностепенни (прости) индекси

Индекс с една колона съпоставя една ключова стойност директно с един или повече редове в таблица, и е най-основният и често срещан тип индекс, с който ще работите ежедневно.

Представете си колона с първичен ключ, като например customer_id в таблица Customer: тя уникално идентифицира всеки ред, и „под капака“ MySQL поддържа индекс, който съпоставя всеки customer_id със съответното местоположение на реда.

Тези прости индекси работят чудесно за малки или умерени таблици и за колони с ниска или умерена кардиналност, като например флагове за състояние или категория, стига тези колони да се показват във филтри (WHERE) или съединения.

Използвайте индекс с една колона, когато заявките обикновено филтрират по едно поле, например WHERE customer_id = 123 или WHERE status = 'active' и не е необходим комбиниран ред на филтриране в няколко колони.

2. Многоколонни (многостепенни / съставни) индекси

Съставният индекс комбинира множество колони в една подредена структура, което позволява на MySQL ефективно да разрешава заявки, които филтрират или сортират, използвайки няколко полета заедно.

Например, разгледайте индекс, дефиниран върху (address, customer_id) в таблицата Customer, където address е посочен първи, а customer_id втори. MySQL може бързо да намери всички клиенти, живеещи на определен адрес, и ако е необходимо, да ги прегледа ефективно по ред на customer_id.

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

Композитните индекси са изключително мощни, но редът е от значение: Индексът върху (address, customer_id) може да помогне на заявката да филтрира само по адрес или едновременно по адрес и customer_id, но няма да бъде използван напълно, ако търсите само по customer_id.

3. Клъстерирани индекси в InnoDB

В InnoDB, клъстерният индекс определя както логическия ред на индекса, така и физическото разположение на редовете на диска. което означава, че самите данни от таблицата се съхраняват в реда на този индекс.

По дизайн, InnoDB използва PRIMARY KEY като клъстерен индекс, Така че, когато избирате първичен ключ за таблица, вие всъщност определяте как се съхраняват редовете и как вторичните индекси ще се позовават на тези редове.

Например, ако customer_id е първичният ключ, InnoDB съхранява редове, подредени по customer_id, което е идеално, когато много заявки осъществяват достъп до групи от клиенти по този идентификатор или често търсят отделни клиенти, използвайки техния идентификатор.

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

Изборът на стабилен, постоянно нарастващ първичен ключ (като автоматично увеличаващ се INT или сурогатен ключ, базиран на време) често води до по-добра производителност на клъстерния индекс, докато произволните или често променящите се ключове могат да фрагментират структурата и да забавят записите.

4. Вторични (неклъстерирани) индекси

Вторичните индекси са всички останали индекси в таблица InnoDB, освен клъстерирания първичен ключ. и те осигуряват допълнителни бързи пътища за търсене, без да променят начина, по който самата таблица е физически подредена.

Например, добавянето на индекс на имейл в таблица „Клиент“ създава отделна структура, която съпоставя всеки имейл с първичния ключ на съответния ред. така че MySQL може първо да намери първичния ключ чрез вторичния индекс и след това да извлече пълния ред от клъстерния индекс.

Вторичните индекси са гъвкави и ви позволяват да ускорите заявките към множество различни колони, което е от решаващо значение, когато моделите ви на четене варират и не всички могат да следват първичния ключ.

Всеки вторичен индекс обаче трябва да бъде актуализиран при INSERT, UPDATE и DELETE. така че ако добавите твърде много от тях, производителността на запис и използването на памет ще пострадат значително.

5. B-дърво, хеш, пълнотекстови и пространствени индекси

Под капака, MySQL поддържа няколко индексни структури освен стандартното B-дърво, всеки от които е насочен към специфични типове данни и модели на заявки.

• B-дървовидни индекси – настройката по подразбиране за InnoDB и повечето системи за съхранение, идеална за търсене на равенство, сканиране на диапазони, операции ORDER BY и GROUP BY.
• Хеш индекси – използва се от някои двигатели или като вътрешни структури; чудесно за чисти сравнения на равенство, но не и за заявки за диапазон или подреждане.
• ПЪЛНОТЕКСТОВИ индекси – оптимизиран за търсене на текстово съдържание, фрази и думи в колони TEXT или VARCHAR.
• ПРОСТРАНСТВЕНИ индекси – насочени към географски типове данни, позволяващи ефективни заявки към точки, линии и полигони.

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

Създаване на индекси в MySQL с практически примери

Създаването на индекси в MySQL е лесно на SQL ниво, но влиянието върху производителността може да бъде драматично, така че си струва да видите някои конкретни примери и да разберете какво прави всеки един от тях.

Подготовка на примерна таблица с клиенти

Да предположим, че започвате с проста таблица „Клиенти“, където съхранявате основни данни за контакт, като например целочислен идентификатор, имена, имейл, телефон и адрес.

Можете да създадете таблицата така:

CREATE TABLE Customer (
customer_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
phone_number VARCHAR(15),
address VARCHAR(255)
);

След като дефинирате таблицата, я попълвате с някои примерни редове, което предоставя на MySQL реални данни за оптимизиране и ви позволява да тествате как индексите променят плановете на заявките и времената за изпълнение.

Добавяне на прост индекс

Да предположим, че искате да ускорите още повече търсенията по customer_id, може би защото първичният ключ не е бил дефиниран първоначално или работите с различен двигател или стара схема.

Можете да създадете основен индекс по следния начин:

CREATE INDEX idx_customer_id ON Customer(customer_id);

След като тази команда е завършена, MySQL потвърждава създаването на индекса, и заявките с WHERE customer_id = ? или прости съединения на тази колона стават много по-бързи, тъй като могат да използват новата структура вместо пълно сканиране.

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

Когато заявките ви филтрират по повече от една колона едновременно, Често има смисъл да се създаде съставен индекс, който съхранява тези стойности заедно в определен ред.

Например, за да ускорите търсенето както по адрес, така и по customer_id, можете да изпълните:

CREATE INDEX idx_address_customer_id
ON Customer(address, customer_id);

Този индекс е особено ефективен за заявки като WHERE address = ? AND customer_id = ?, или за сканирания, които групират или сортират по адрес и след това по customer_id, тъй като MySQL може да разчита на съществуващото подреждане в индекса.

Неклъстерен индекс на имейл

Полетата за имейл са класически кандидати за вторични индекси, защото те са склонни да бъдат уникални или силно селективни и често се използват за вход или търсене на акаунти.

Можете да добавите вторичен индекс към имейл с:

CREATE INDEX idx_email ON Customer(email);

След това, MySQL вече не е необходимо да сканира цялата таблица Customer, за да разреши WHERE email = ”, Той просто навигира в индекса, намира съответстващия ключ и след това чете свързания ред, използвайки препратката към първичния ключ.

Покриване на индекси за още по-бързо четене

Покриващ индекс е такъв, който включва всички колони, изисквани от заявката, така че MySQL може да отговори на заявката изцяло от структурата на индекса, без да докосва базовата таблица (известно също като сканиране само на индекса).

Представете си, че често изпълнявате заявка, която се нуждае само от собствено_име и фамилия:

CREATE INDEX idx_covering_name
ON Customer(first_name, last_name);

За заявки, избиращи само тези две полета и филтриращи по подходящ начин, MySQL може да чете директно от idx_covering_name, намалявайки дисковите I/O операции и подобрявайки латентността, особено при големи набори от данни.

Стратегии и най-добри практики за оптимизация на индекси

Хвърлянето на индекси във всяка колона е гарантиран начин да се навреди на производителността, а не да се подобри. така че са ви необходими ясни принципи за проектиране, наблюдение и орязване на индекси с течение на времето.

1. Изберете правилните колони за индексиране

Приоритизирайте колоните, които често се появяват в клаузите WHERE, JOIN, ORDER BY или GROUP BY. защото те са тези, които се възползват най-много от бързо търсене или сортиран достъп.

Колоните, които съдържат предимно уникални или силно селективни стойности, са особено добри кандидати за индексиране, тъй като MySQL може бързо да стесни търсенето до малък брой редове, често само един.

От друга страна, индексирането на колони с много ниска кардиналност (като булеви флагове) може да не помогне много, защото двигателят все още трябва да сканира много редове за всеки индексен ключ, което ограничава предимството.

2. Поддържайте индексите възможно най-къси и изчистени

Големите индексни ключове консумират повече памет и дисково пространство и забавят всяка операция по запис. така че обикновено искате да направите индексираните колони възможно най-компактни.

За дълги текстови полета или полета с променлива дължина, помислете за индексиране само на префикс, когато е достатъчно за разграничаване на стойностите. например индексиране на първите 20 знака от поле от 200 знака, ако това е достатъчно за вашите заявки.

Използването на числови типове вместо големи текстови полета за съединения и филтри също подобрява ефективността на индексите, защото целите числа и стойностите с фиксиран размер са по-евтини за сравняване и съхранение.

3. Избягвайте прекомерното индексиране

Всеки допълнителен индекс е нещо, което MySQL трябва да актуализира при INSERT, UPDATE и DELETE. което означава, че безразсъдното създаване на индекси може драстично да забави натоварването с голям обем на запис.

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

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

4. Премахнете излишните и неизползвани индекси

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

Трябва периодично да преглеждате кои индекси рядко или никога не се използват от заявки, използване на схемата за производителност на MySQL или външни инструменти за наблюдение за събиране на статистически данни за употребата.

След като идентифицирате наистина ненужен индекс, безопасното му премахване може да подобри производителността на запис и да освободи място за съхранение:

DROP INDEX idx_unnecessary_index ON Customer;

Винаги първо тествайте въздействието от премахването на индекс в тестова среда. особено за наследени системи, където скрити заявки могат да разчитат на него.

5. Анализирайте моделите на заявките преди индексиране

Сляпото добавяне на индекси без разбиране на поведението на заявките е един от най-големите анти-шаблони. и често води до бавно записване, плюс никакво реално подобрение при четене.

Започнете, като записвате бавни и високочестотни заявки, след което ги проверете внимателно, като се консултирате с пълно ръководство за оптимизация на MySQL заявки, като обърнете специално внимание на условията WHERE, колоните JOIN и клаузите за сортиране или групиране.

Използвайте EXPLAIN, за да видите как MySQL планира да изпълни всяка заявка и да проверите дали използва индекси или се връща към пълно сканиране на таблици. и съответно прецизирайте стратегията си за индексиране.

Буферен пул на InnoDB и производителността на индексите от страна на паметта

Индексите не се намират само на диск: InnoDB разчита в голяма степен на RAM паметта, чрез буферния пул, за да кешира както данни, така и индексни страници, и този слой памет има огромен ефект върху производителността в реалния свят.

Буферният пул на InnoDB е голяма област от паметта, където MySQL кешира таблични данни, индексни страници, модифицирани редове, чакащи да бъдат изчистени, и някои вътрешни структури, като например адаптивния хеш индекс; вижте нашите преглед на системите за съхранение за свързани съображения.

В управлявани услуги като Cloud SQL, размерът на буферния пул по подразбиране обикновено е зададен на около 70-75% от паметта на инстанцията, но можете и често трябва да го коригирате в зависимост от натоварването и наличната RAM памет.

Целта е буферният пул да бъде достатъчно голям, за да може най-често използваните данни и индексни страници да останат в паметта, като същевременно се оставя място за буфери за връзки, таблици performance_schema и други MySQL разходи.

Можете да следите колко четения се извършват от диска спрямо от буферния пул, И ако видите много четения от диска спрямо попаденията в буфера, увеличаването на innodb_buffer_pool_size (и паметта на инстанциите) може значително да ускори заявките за четене, които разчитат на индекси.

Схема, заявки и скриптове: по-голямата картина на производителността на MySQL

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

1. Проектирайте разумен релационен модел

Прекарване на време предварително в проектиране на изчистена релационна схема с подходящи таблици, полета и релации се отплаща изключително много с по-лесна поддръжка и по-предсказуема производителност.

Идентифицирайте обекти, техните свойства и как те са свързани помежду си, след което преобразувайте това в таблици, първични ключове, външни ключове и поддържащи индекси, стремеж към ниво на нормализация, което избягва излишното, без да се изпада в свръхнормализирани крайности.

Първичните ключове уникално идентифицират всеки ред, докато външните ключове изразяват връзките между таблиците, и двете обикновено заслужават индекси, защото се появяват постоянно в съединения и ограничения.

2. Избирайте типовете полета разумно

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

Използвайте последователни типове данни за един и същ вид информация в различните таблици, защото това опростява съединенията и позволява на MySQL да извършва сравнения по-ефективно.

Предпочитайте типове с фиксирана дължина (като CHAR или числови типове с фиксиран размер), където е уместно, пред много големи полета с променлива дължина. и избягвайте използването на TEXT или BLOB за стойности, които редовно филтрирате или обединявате.

Винаги, когато е възможно, декларирайте колоните като NOT NULL, тъй като работата с полета, които могат да бъдат нулирани, може да забави някои операции и да усложни използването на индекси.

3. Поддържайте масите компактни и подредени

По-бързите таблици са по-бързи, защото има по-малко данни за преместване през паметта и диска. особено когато сложни заявки докосват множество редове или съединения.

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

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

След големи изтривания или много структурни промени, изпълнението на OPTIMIZE TABLE може да помогне за реорганизиране на паметта и подобряване на моделите на входно/изходни операции. въпреки че трябва да бъдете внимателни с големи производствени таблици поради последиците от заключване и прекъсване на работата.

4. Пишете заявки, имайки предвид производителността

Дори и с отлични индекси, лошо написаният SQL може да саботира производителността, така че дизайнът на заявките трябва да върви ръка за ръка с дизайна на индексите.

Избягвайте SELECT * в производствените заявки и изрично изброявайте само колоните, от които се нуждаете, намаляване на количеството прехвърлени данни и броя на индексните или информационните страници, които MySQL трябва да прочете.

Бъдете внимателни с LIKE модели, които започват със заместващ символ (напр. '%term'), защото те обикновено предотвратяват използването на нормални B-дървовидни индекси; за търсене на голям обем текст, FULLTEXT индексите обикновено са по-добър избор.

Използвайте GROUP BY, ORDER BY и HAVING само когато е наистина необходимо. и се уверете, че съответните колони са правилно индексирани или съответстват на водещите колони на съставен индекс, когато е възможно.

Използвайте EXPLAIN, за да проверите как MySQL планира да изпълни заявка, търсят се знаци като „type = ALL“ или „rows“ да са много високи, което обикновено показва пълно сканиране и лошо използване на индекса.

5. Оптимизирайте вмъкванията и поведението на ниво скрипт

Освен суровия SQL, начинът, по който скриптовете на приложението ви се свързват и взаимодействат с MySQL, е от голямо значение за цялостната бързина на реакция. особено в мащаб.

Пакетните вложки обикновено са по-ефективни от многото едноредови вложки, например, използвайки INSERT INTO table (col1, col2) VALUES (…), (…), (…); така че MySQL да може да обработва множество редове наведнъж и да следва Основи на MySQL транзакциите за правилно дозиране.

Странично разделяне на резултатите от заявките с помощта на LIMIT, вместо зареждане на огромни набори от резултати в паметта или изпращането им на клиента наведнъж, което намалява времето за зареждане и използването на ресурси.

Въвеждане на кеширащи слоеве (кеш в паметта, кеш на ниво приложение, съхранение на сесии) за данни, които се променят рядко, но се четат често, разтоварване на повтарящи се четения от MySQL и повишаване на устойчивостта на системата при пиково натоварване.

Минимизирайте ненужните връзки към базата данни и поддържайте живота на връзките разумен, често чрез обединяване на връзки и чрез извършване на интензивна обработка, след като вече сте извлекли данните, вместо докато връзката е отворена.

Често срещани грешки при индексиране и как да ги избегнем

Злоупотребата с индекси може незабелязано да създаде огромни проблеми с производителността, така че е полезно да знаете обичайните капани и как да ги избегнете.

Прекомерно индексиране

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

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

Недостатъчно индексиране или липсващи критични индекси

Обратната грешка е да не се индексират колоните, които имат значение, особено ключовете за свързване и често филтрираните полета, което принуждава MySQL да извършва скъпи сканирания на таблици.

Редовно анализирайте дневника на бавните заявки и най-често срещаните заявки. и да създадат целеви индекси върху колоните, които се появяват многократно в клаузите WHERE и JOIN.

Грешен тип индекс или грешен ред на колоните

Използването на неподходяща структура на индекса може да направи заявките по-бавни, вместо по-бързи, като например очакване на хеш-подобен модел на достъп от B-дърво за определени натоварвания или обратно.

При съставните индекси, грешното подреждане на колоните е друг често срещан проблем, защото MySQL използва изцяло само най-лявата част на индекса за много типове заявки.

Остарели индексни статистики и липса на поддръжка

Статистиките на индексите насочват решенията на оптимизатора и когато те остареят, MySQL може да избере лоши планове за заявки, които не отразяват текущото разпределение на данните.

Изпълняване на ANALYZE TABLE и, където е уместно, OPTIMIZE TABLE по редовен график помага за поддържането на статистиката и физическите оформления във форма, особено при бързо променящи се набори от данни.

Игнориране на мониторинга и изхода на EXPLAIN

Проектирането на индекси без проверка как всъщност се използват е рецепта за догадки. и догадките рядко водят до устойчиво представяне.

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

Съвети за управление на индекси в MySQL за дългосрочна производителност

Поддържането на MySQL бърз във времето е непрекъснат процес на измерване, настройване и почистване, особено с нарастването на данните ви и развитието на моделите на заявки.

• Изберете индекси, които са тясно свързани с реалните модели на заявки, фокусирайки се върху колони, използвани за филтриране, обединяване и подреждане.
• Помислете за съставни индекси, когато множество колони винаги се заявяват едновременно, като първо изберете най-селективната или най-често филтрираната колона.
• Следене на размера на индексите и производителността на запис, и премахнете или консолидирайте припокриващи се индекси, когато те вече не предоставят ясни ползи.
• Редовно анализирайте заявките, използвайки EXPLAIN и регистрационни файлове за бавни заявки, настройване както на SQL, така и на индексите, когато работното ви натоварване се променя.

Когато подхождате към индексирането като към непрекъснат, базиран на данни процес на настройване, а не като към еднократна задача, Можете да поддържате MySQL, който да обработва по-големи натоварвания, повече потребители и по-сложни заявки, без да спира.