Клъстер индекси 1 не са отделен вид индекс. По-скоро, този подход към съхранение на данни. Детайли различават в различни изпълнения, но InnoDB струпани индекс всъщност съдържа както B-дърво-код и да се низ в една и съща структура.
Когато горната част на таблицата построен с клъстери индекс, индексната страница на страниците си, се съхраняват ред. Понятието "клъстер" се отнася до тази линия с подобни ключови ценности, съхранявани в квартала 2. Само един клъстерирани индекс може да бъде изградена върху масата, тъй като е невъзможно да се поддържа една и съща линия на две места (но покриваща индекси да подражават няколко скупчени индекси, като това ще бъде обсъдено по-късно в тази глава).
Що се отнася до изпълнението на показателите отговарят подсистемата за съхранение, не всички от тях подкрепят клъстерирани индекси. В момента тя може да се похвали само на solidDB и InnoDB. В този раздел ще говорим само за InnoDB, но обсъдени принципите, поне частично, ще се прилагат за всяко подсистема за съхранение, който поддържа клъстерирани индекси сега или в бъдеще.
Фиг. 3.3 показва как записи се намира в индекса на клъстер. Имайте предвид, че страниците на листа съдържат в себе си линии и възли - само индексирани колони. В този пример, индексирана колона съдържа числа.
Някои DBMSs ви позволяват да изберете кои индекс, за да клъстер, но в момента никой от подсистемата за съхранение на MySQL не разполага с тази възможност. InnoDB клъстеризира данните на първичния ключ. Това означава, че "индексира колона" на фиг. 3.3 е колона, съдържаща първичен ключ.
Фиг. 3.3. Местоположение записи в индекса струпани Ако не определите основна ключ, след InnoDB ще се опитат да го използвате вместо уникален индекс, който не позволява на нулеви стойности. Ако такъв индекс не съществува, InnoDB откриване на скрити основен ключ за вас, а след това клъстеризира масата върху него 1. InnoDB клъстери записите заедно само в рамките на страницата. Различни страници с подобни ключови стойности могат да бъдат далеч един от друг.
Ключът за първична клъстер понякога може да подобри производителността, понякога значително го намали. По този начин, трябва да се вземе решение на клъстеризация след внимателно обмисляне, особено ако подсистема подмяната на съхранение маса с InnoDB на някой друг, и обратно.
Клъстер данни имат няколко много важни предимства:
• Можете да съхраните номер на свързаните с тях данни. Например, при изпълнението на пощенската кутия може да се е събрало на таблицата по колона user_id, а след това да изтеглите всички съобщения от даден потребител, ще трябва да се чете само на няколко страници от диск. Ако не използвате групиране, за всяко съобщение, може да се изисква отделен диск I / O.
• Бърз достъп до данните. Магазините Индексът клъстер индекса и данните заедно в структурата на B-дърво, обаче извличане на редове от индекса на клъстер е обикновено по-бързо от сравнима не-клъстерирани индекс за търсене.
• Използвайки покриваща кодове искания може да получи стойността на първичния ключ на възела на листо.
Тези предимства значително да увеличи производителността, ако ще създавате свои таблици и заявки с профила си. Въпреки това, скупчени индекси, има и недостатъци:
• Групиране осигурява значителни подобрения, когато натоварването се характеризира с голям брой операции I / O. Ако данните се поставя в паметта, как да получите достъп няма значение, а след това, струпани индекси не ще донесат голяма полза.
• Скорост на операции за поставяне силно зависи от реда на обработка. Поставете редове в ред, съответстващ на първичния ключ е най-бързият начин да се зареди данни в InnoDB на маса. Ако изтеглите много данни в различен ред, а след това в края на изтеглянето че има смисъл да се реорганизират маса с помощта на командата ОПТИМИЗИРАНЕ таблица.
• Обновяване на колоните клъстерирани индекси са скъпи, защото InnoDB принудени да се движат на всеки ред се актуализира в новото място.
• За маси с групирана индекс се вмъкват нови линии или първичен ключ актуализация изисква движение ред може да доведе до разделяне на страницата. Това се случва, когато ключовата стойност на реда, като този ред е бил поставен в една страница, пълна с данни. За да се поберат на линията, подсистемата за съхранение е принуден в този случай е да се разчупи страница на две. Поради разделянето на таблицата на страниците, заема повече място на диска.
• пълно сканиране касетъчни маси могат да бъдат по-бавно, особено ако струните са опаковани толкова гъсто, съхранявани или несъвместими поради отцепване на страници.
• Средни (не-групова) индекси могат да бъдат повече, отколкото очаквате, защото стойностите на колоните, се съхраняват в възлите на листа, които съставят първичния ключ.
Това означава, че по време на подсистемата индекс съхранение на вторичния търсене низ, трябва първо да го намерите листо възел, а след това използвайте съхранява стойност, чиято основна ключът за намиране на него самия низ. Тази двойна работа: две минавания през B-дърво вместо един (в InnoDB индекс адаптивна хеш помага за намаляване на тези загуби).
Сравнение на разположение на данните в InnoDB и MyISAM
Разликите при организацията на струпани и не-клъстерирани поставянето на данни, както и всяка разлика между първични и вторични показатели могат да доведат до объркване и неочакваното. Помислете как InnoDB и MyISAM ще постави на данните в таблицата по-долу:
Създаване на таблица layout_test (Кол вътр NOT NULL, col2 НЕ INT NULL,
Да приемем, че 10 000 линии са били добавени към масата. Стойността на първичния ключ за всеки ред вкарана произволно избрана от диапазона от 1 до 10 000. След това, оптимизация се извършва чрез използване ОПТИМИЗИРАНЕ ТАБЛИЦА команда. С други думи, данните се съхраняват на диска оптимално (defragmented), но линията могат да бъдат организирани в случаен ред. Елементи на col2 колона предназначени случайна стойност между 1 и 100, така че има много дубликати.
Поставянето на данни в MyISAM
Наличните данни в MyISAM на подсистемата по-лесно, така че ние ще започнем с него. MyISAM съхранява данни на диска в реда, в който са вмъкнати, както е показано на фиг. 3.4.
В непосредствена близост до редовете сме ги номера, дадени, като се започне от нулата. Тъй като струни имат фиксиран размер, MyISAM може да намери всеки от тях чрез изместване на необходимия брой байтове, считано от началото на таблицата (MyISAM не винаги използва "номера на редове", които сме показали, в зависимост от това дали линиите имат фиксиран или променлив размер, тази подсистема съхранение използва различна стратегия).
С тази уговорка, изграждането на индекса не предизвиква сложност. Ние се убедите в това, с помощта на диаграми на последователност, отхвърли тези физически елементи, като например страници, както и показване на индекса само "възли". Всеки лист възел в индекса може просто да съдържа номера на реда. Фиг. 3.5 илюстрира основен ключ масата.
Ние пропуснахме някои детайли, като например факта, че един от вътрешен възел B-дърво може да има множество вътрешни дете възли, но
Фиг. 3.4. Наличните данни за масата на MyISAM layout_test
Фиг. 3.5. Поставянето на първичен ключ за таблицата в layout_test MyISAM
за общо разбиране на който данните в подсистема, която не е групирана съхранение не е от съществено значение.
Какво може да се каже и за индекса на col2 на колоната? Има ли нещо по-специално? Оказва се, нищо - това е един и същ код, както всеки друг. Фиг. 3.6 показва индексът на col2.
Фиг. 3.6. Настаняване в col2 индекс колона за MyISAM таблици в layout_test
В действителност, MyISAM никаква структурна разлика между първичен ключ или друг индекс. Първичният ключ е уникален индекс, който не е може да се анулира, наречен основен.
Наличните данни в InnoDB
Подсистема InnoDB съхранява една и съща информация по много различен начин, защото на техния сектор организация. InnoDB създава маса, както е показано на фиг. 3.7.
Фиг. 3.7. Поставянето на първичен ключ за таблицата в InnoDB layout_test
На пръв поглед, особени разлики от фиг. 3.5 не. Но ако се вгледате по-внимателно и ще забележите, че фигурата показва цялата таблица, а не само на индекса. Тъй като струпани индекс в InnoDB «е» таблицата е отделно съхранение на низове в `MyISAM", не.
Всеки лист възел в клъстера съдържа първичен ключ стойност индекс и идентификатор показалеца на намаление на цените операция, която използва InnoDB за подкрепа на операциите и механизъм MVCC и други колони (в този случай, col2). Ако основният ключ е създаден на префикс колона, в InnoDB се съхранява заедно с останалите, а общата стойност на тази колона.
Средни индекси в InnoDB са много различни от клъстера. Leaf възли вторични индекси в системата, вместо да съдържа "указатели към низове" първични ключови ценности, които действат като такива "указатели". Тази стратегия намалява количеството на работата, необходима за поддържането на вторичния индекс, когато се движат по линията или по време на разделянето на страницата с данни. Използвайте vanie ключови стойности основната линия, като индекса на показалеца увеличаване на размера, но това също така означава, че низът може да се движи, без да актуализирате InnoDB указатели към него.
Фиг. 3.8 илюстрира индексът на col2 колона за масата за демонстрация. Всеки лист възел съдържа индексирани колони (в този случай само col2), следвани от първични основни стойности (Coll).
Фиг. 3.8. Поставянето на вторичния индекс за таблицата в InnoDB layout_test
Тези диаграми илюстрират листа възли индекса на B-дърво, но ние умишлено пропуска подробностите относно възли без листа. Всеки трети лист възел на индекса B-дърво в InnoDB съдържа индексирани колони плюс указател към възела на следващото ниво (което може да бъде или не-лист или листа възел). Това важи за всички индекси, както клъстери и вторични.
Фиг. 3.9 са абстрактно представяне на организацията на таблицата в InnoDB и MylSAM. Лесно е да се види разликата между това как данните и индексите се съхраняват в тези две системи.
Ако ти не разбираш разликата между клъстер и съхранение без клъстер, и защо е толкова важно, не се притеснявайте. Това ще стане по-ясно, колкото да научите повече, особено в края на този раздел и в следващата глава. Тези концепции са много трудни, както и за тяхното пълно разбиране отнема време.
Поставете редове по ред на първичния ключ в InnoDB
Ако използвате InnoDB, а ти не се нуждаят от специфично групиране, че има смисъл да се определи сурогатното ключ, който е първичен ключ, стойността на която няма пряка връзка с данните на вашата кандидатура. Обикновено, най-лесният начин е да се използва колона с атрибута auto_increment. Това гарантира, че
Фиг. 3.9. Клъстер и не-клъстерирани маса стойността на полето, за които е изградена първичния ключ, това е монотонно се увеличава, което от своя страна осигурява по-добра производителност връзка с първичния ключ.
По-добре е да се избегне ключове случаен (непоследователни) касетъчни. Например, използването на UUID ценности е лош избор от гледна точка на изпълнение: това прави вмъкването в струпани индексът е случаен, което е най-лошият сценарий, и не води до полезна групиране на данните.
За демонстрационни цели, ние проведохме тестове за производителност за двете ситуации. В първия случай, въвеждането в USERINFO маса с идентификатор число определя, както следва:
Създаване на таблица USERINFO (
идентификатор вътр неподписан NOT NULL auto_increment,
име VARCHAR (64) NOT NULL DEFAULT "
имейл VARCHAR (64) NOT NULL DEFAULT '',
парола VARCHAR (64) NOT NULL DEFAULT '',
Дата на раждане дата DEFAULT NULL,
адрес VARCHAR (255) NOT NULL DEFAULT '',
град VARCHAR (64) NOT NULL DEFAULT '',
state_id TINYINT неподписан NOT NULL DEFAULT '0'
цип VARCHAR (8) NOT NULL DEFAULT '',
country_id SMALLINT неподписан NOT NULL DEFAULT '0'
пол ( "M", "F") NOT NULL DEFAULT "М",
account_type VARCHAR (32) NOT NULL DEFAULT '',
проверена TINYINT NOT NULL DEFAULT '0'
allow_mail TINYINT неподписан NOT NULL DEFAULT '0'
parrent_account вътр неподписан NOT NULL DEFAULT '0'
closest_airport VARCHAR (3) NOT NULL DEFAULT '',
Уникален ключ електронна поща (имейл),
KEY country_id (country_id),
KEY state_id (state_id),
KEY state_id_2 (state_id, град, адрес)
Обърнете внимание на число първичен ключ за автоматично увеличаване.
Втората таблица, userinfo_uuid, идентична маса USERINFO, с изключение на първичния ключ е UUID, вместо число:
Създаване на таблица userinfo_uuid (UUID VARCHAR (36) NOT NULL,
Ние тествахме двете таблици. На първо място, ние поставяме във всяка от милиони редове на сървъра, който има достатъчно памет, за да съдържа индекси. След това ще се постави три милиона реда в същата таблица, и това увеличава индексите, така че те вече не се поберат в паметта. Таблица. 3.2 показва сравнение на резултатите от теста.
Забележка: Ако основният ключ тип UUID не е само поставете линии отне повече, но размерът на индекса се увеличи значително. Една от причините е големият размер на първичния ключ, но, разбира се, също е довело до разделяне на страници, която произтича от това фрагментиране.
Таблица 3.2. вмъкване на резултатите от теста в редовете на InnoDB на маса
За да се разбере защо това е така, нека да видим какво се е случило в индекса, когато вмъкнете данни в първата таблица. Фиг. 3.10 е показан като добавя линия първоначално се изпълни една страница и след това се премине към следващата.
Фиг. 3.10. Поставяне последователни стойности на индекса в групирана индекс Както е показано на фиг. 3.10, InnoDB съхранява новия запис веднага след предишния, тъй като основните ключови стойности са последователни. Когато попълване фактор на страницата достига максималната стойност (в InnoDB първоначален фактор пълнене е 15/16, който да даде възможност за бъдещи модификации), следващото влизане е поставен на новата страница. След края на прогресивна сайтове за сваляне на данни почти бяха изпълнени с подредени записи, което е много желателно.
Съвсем различно е случило, когато ние вмъкнете данни във втората таблица с струпани индекс на колона, която съдържа UUID (фиг. 3.11).
Тъй като основен ключ стойността на всяка следваща линия не е непременно по-голяма от предишната, на InnoDB не винаги могат да се настанят новата линия в края на индекса. Тя трябва да търси подходящо място, линия - средно някъде в средата на съществуващите данни - и безплатно място за него. Това води до голямо количество допълнителна работа и води до неоптимално разположение на данните. Ето обобщение на недостатъци:
Фиг. 3.11. Поставете непоследователни стойности на индекса по струпани индекс
• Page, която трябва да се удари в ред, тя може да падне върху диска и отстранен от кеша, след InnoDB ще трябва да го намери и да се чете от диска, преди да вмъкнете нов ред. Това води до много случаен I / O операции.
• InnoDB понякога трябва да се съборят страницата, за да направи място за новите линии. То включва преместване на големи количества данни.
• Поради разделянето на страниците са пълни произволно и свободно, което често води до фрагментация.
След зареждане на такива произволни стойности в групирана индекс че има смисъл да стартирате ОПТИМИЗИРАНЕ НА ТАБЛИЦАТА, която възстановява масата и запълване на страницата оптимално.
Поуката от тази история е, че ако използвате InnoDB, което трябва да се стремим към въвеждането на данни по начин, съвместим с първичния ключ, и се опитайте да използвате ключова клъстер, който монотонно се увеличава за нови линии.
MySQL. Оптимизиране на производителността
Свързани статии