Кластерні індекси 1 не є окремим типом індексу. Швидше, це підхід до зберігання даних. Деталі в різних реалізаціях відрізняються, але в InnoDB кластерний індекс фактично містить і B-Tree-індекс, і самі рядки в одній і тій же структурі.
Коли над таблицею побудований кластерний індекс, в листових сторінках індексу зберігаються самі рядки. Термін «кластерний» означає, що рядки з близькими значеннями ключа зберігаються по сусідству 2. Над таблицею можна побудувати тільки один кластерний індекс, оскільки неможливо зберігати одну і ту ж рядок одночасно в двох місцях (проте покривають індекси дозволяють емулювати кілька кластерних індексів, про що буде розказано нижче в цьому розділі).
Оскільки за реалізацію індексів відповідають підсистеми зберігання, не всі з них підтримують кластерні індекси. В даний час цим можуть похвалитися тільки solidDB і InnoDB. У цьому розділі ми будемо говорити виключно про InnoDB, але обговорювані принципи, по крайней мере, частково будуть застосовні до будь-якої підсистемі зберігання, що підтримує кластерні індекси в даний час або в майбутньому.
На рис. 3.3 показано, як розташовуються записи в кластерному індексі. Зверніть увагу, що листові сторінки містять самі рядки, а вузлові - тільки індексовані стовпці. У розглянутому прикладі індексований стовпець містить цілочисельні значення.
Деякі СУБД дозволяють вибрати, який індекс зробити кластерним, але на даний момент жодна з підсистем зберігання MySQL не володіє такою можливістю. InnoDB кластеризує дані по первинному ключу. Це означає, що «індексований стовпець» на рис. 3.3 є стовпцем, що містить первинний ключ.
Мал. 3.3. Розташування записів в кластерному індексі Якщо ви не визначили первинний ключ, то InnoDB спробує використовувати замість нього унікальний індекс, який не допускає порожніх значень. Якщо такого індексу не існує, InnoDB визначить прихований первинний ключ за вас і потім кластеризує таблицю по ньому 1. InnoDB кластеризує записи разом тільки всередині сторінки. Різні сторінки з близькими значеннями ключів можуть виявитися далеко один від одного.
Первинний кластерний ключ іноді може збільшити продуктивність, а іноді помітно знизити її. Таким чином, рішення про кластеризації потрібно приймати обдумано, особливо при заміні підсистеми зберігання таблиці з InnoDB на якусь іншу і навпаки.
Групові дані мають кілька дуже важливих достоїнств:
• Ви можете зберігати пов'язані дані поруч. Наприклад, при реалізації поштової скриньки можна кластеризувати таблицю по стовпцю user_id, тоді для вибірки всіх повідомлень одного користувача потрібно буде прочитати з диска лише невелика кількість сторінок. Якщо не використовувати кластеризацію, то для кожного повідомлення може знадобитися окрема операція дискового введення / виводу.
• Швидкий доступ до даних. Кластерний індекс зберігає і індекс, і дані разом в одній B-Tree структурі, тому витяг рядків з кластерного індексу зазвичай відбувається швидше, ніж можна порівняти пошук в некластерние індексі.
• Використовують покривають індекси запити можуть отримати значення первинного ключа з листового вузла.
Ці переваги значно збільшать продуктивність, якщо ви спроектуєте свої таблиці і запити з їх урахуванням. Однак у кластерних індексів є і недоліки:
• Кластеризация дає значні поліпшення, коли робоче навантаження характеризується великою кількістю операцій введення / виводу. Якщо дані містяться в пам'яті, то порядок доступу до них не має значення, і тоді кластерні індекси не принесуть великої користі.
• Швидкість операцій вставки сильно залежить від порядку обробки даних. Вставка рядків в порядку, відповідному первинному ключу, є найшвидшим способом завантажити дані в таблицю InnoDB. Якщо ви завантажуєте велику кількість даних в іншому порядку, то після закінчення завантаження має сенс реорганізувати таблицю за допомогою команди OPTIMIZE TABLE.
• Оновлення стовпців кластерного індексу обходиться дорого, оскільки InnoDB змушена переміщати кожну оновлену рядок в нове місце.
• Для таблиць з кластерним індексом вставка нових рядків або оновлення первинного ключа, що вимагає переміщення рядка, може призводити до розщеплення сторінки. Це відбувається тоді, коли значення ключа рядки таке, що рядок повинен був поміщений в сторінку, заповнену даними. Щоб рядок помістилася, підсистема зберігання змушена в цьому випадку розбити сторінку на дві. Через розщеплення сторінок таблиця займає більше місця на диску.
• Повне сканування кластерних таблиць може виявитися більш повільним, особливо якщо рядки упаковані менш щільно або зберігаються непослідовно через розщеплення сторінок.
• Вторинні (некластерние) індекси можуть виявитися більше, ніж ви очікуєте, оскільки в листових вузлах зберігаються значення стовпців, що становлять первинний ключ.
Це означає, що в процесі пошуку рядка по вторинному індексу підсистема зберігання повинна спочатку знайти в ньому листовий вузол, а потім використовувати зберігається там значення первинного ключа для відшукання по ньому самої рядки. Це подвійна робота: два проходи по B-дереву замість одного (в InnoDB адаптивний хеш-індекс допомагає зменшити ці втрати).
Порівняння розміщення даних в InnoDB і MyISAM
Відмінності в організації кластерізованного і некластерізованний розміщення даних, а також відповідна різниця між первинними і вторинними індексами можуть призводити до плутанини і несподіванок. Розглянемо, як InnoDB і MyISAM розмістять дані такої таблиці:
CREATE TABLE layout_test (coll int NOT NULL, col2 int NOT NULL,
Припустимо, що в таблицю було додано 10 000 рядків. Значення первинного ключа для кожної вставляється рядки випадковим чином вибиралося з діапазону від 1 до 10 000. Потім була проведена оптимізація за допомогою команди OPTIMIZE TABLE. Іншими словами, дані розміщуються на диску оптимальним чином (дефрагментовані), але рядки можуть розташовуватися в довільному порядку. Елементам стовпця col2 присвоєні випадкові значення між 1 і 100, тому є багато дублікатів.
Розміщення даних в MyISAM
Розміщення даних в підсистемі MyISAM простіше, тому ми почнемо з нього. MyISAM зберігає дані на диску в тому порядку, в якому вони були вставлені, як показано на рис. 3.4.
Поруч з рядками ми привели їх номери, починаючи з нуля. Оскільки рядки мають фіксований розмір, MyISAM може знайти будь-яку з них шляхом зміщення на необхідну кількість байтів від початку таблиці (MyISAM не завжди використовує «номера рядків», які ми показали: залежно від того, чи мають рядки фіксований або змінний розмір, ця підсистема зберігання використовує різні стратегії).
При такому розміщенні побудова індексу не викликає складності. Ми проілюструємо це за допомогою послідовності діаграм, відкинувши такі фізичні деталі, як сторінки, і показуючи в індексі тільки «вузли». Кожен листової вузол в індексі може просто містити номер рядка. На рис. 3.5 проілюстровано первинний ключ таблиці.
Ми опустили деякі деталі, наприклад, те, що у одного внутрішнього вузла B-дерева може бути кілька внутрішніх вузлів-нащадків, але
Мал. 3.4. Розміщення даних для таблиці layout_test в MyISAM
Мал. 3.5. Розміщення первинного ключа для таблиці layout_test в MyISAM
для загального розуміння розміщення даних в некластерние підсистемі зберігання це не суттєво.
Що можна сказати про індекс по стовпцю col2? Чи є тут щось особливе? Виявляється, нічого - це такий же індекс, як будь-який інший. На рис. 3.6 показаний індекс по стовпцю col2.
Мал. 3.6. Розміщення індексу по стовпцю col2 для таблиці layout_test в MyISAM
Фактично в MyISAM відсутні структурні відмінності між первинним ключем і будь-яким іншим індексом. Первинний ключ є просто унікальним індексом, що не допускає порожніх значень під назвою PRIMARY.
Розміщення даних в InnoDB
Підсистема InnoDB зберігає ті ж самі дані зовсім по-іншому в силу своєї кластерної організації. InnoDB формує таблицю так, як показано на рис. 3.7.
Мал. 3.7. Розміщення первинного ключа для таблиці layout_test в InnoDB
На перший погляд, особливих відмінностей від рис. 3.5 немає. Але подивіться уважніше, і ви помітите, що на малюнку показана вся таблиця, а не тільки індекс. Оскільки кластерний індекс у InnoDB «є» таблицею, то окремого сховища для рядків, як в MyISAM, немає.
Кожен листовий вузол в кластерному індексі містить значення первинного ключа, ідентифікатор транзакції і покажчик відкату, який InnoDB використовує для підтримки транзакцій та механізму MVCC, а також інші стовпці (в даному випадку col2). Якщо первинний ключ створений по префіксу стовпчика, то в InnoDB разом з іншими зберігається і повне значення цього стовпця.
Вторинні індекси в InnoDB сильно відрізняються від кластерних. Листові вузли вторинних індексів в даній системі містять замість «покажчиків на рядки» значення первинного ключа, які виступають в ролі таких «покажчиків». Така стратегія зменшує обсяг роботи, необхідної для обслуговування вторинних індексів при переміщенні рядки або в момент розщеплення сторінки даних. Викорис тання значень первинного ключа рядки в якості покажчика збільшує розмір індексу, але це також означає, що InnoDB може переміщати рядок без поновлення покажчиків на неї.
Мал. 3.8 ілюструє індекс по стовпцю col2 для демонстраційної таблиці. Кожен листовий вузол містить індексовані стовпці (в даному випадку тільки col2), за якими слідують значення первинного ключа (coll).
Мал. 3.8. Розміщення вторинного індексу для таблиці layout_test в InnoDB
Ці діаграми ілюструють листові вузли B-Tree індексу, але ми навмисне опустили деталі, що стосуються нелістових вузлів. Кожен нелістовой вузол B-Tree індексу в InnoDB містить індексовані стовпці плюс покажчик на вузол наступного рівня (яким може бути або інший нелістовой, або листової вузол). Це відноситься до всіх індексах, як кластерним, так і вторинним.
На рис. 3.9 показано абстрактне уявлення організації таблиці в InnoDB і MylSAM. Легко побачити відмінності між тим, як зберігаються дані і індекси в цих двох системах.
Якщо ви не розумієте, чим відрізняється кластерне і некластерние зберігання і чому це так важливо, не турбуйтеся. Це стане ясніше, коли ви дізнаєтеся більше, особливо в кінці цього розділу і в наступному розділі. Дані концепції дуже непрості, і для їх повного осмислення потрібен час.
Вставка рядків в порядку первинного ключа в InnoDB
Якщо ви використовуєте InnoDB і вам не потрібно ніяка конкретна кластеризація, то має сенс визначити сурогатний ключ, тобто первинний ключ, значення якого не має прямого зв'язку з даними вашого застосування. Зазвичай найпростішим способом є використання стовпчика з атрибутом AUTO_INCREMENT. Це гарантує, що
Мал. 3.9. Кластерні і некластерние таблиці значення поля, по якому побудований первинний ключ, монотонно зростає, що в свою чергу забезпечує кращу продуктивність з'єднання за допомогою первинний ключ.
Краще уникати випадкових (непослідовних) кластерних ключів. Наприклад, використання значень UUID є поганим вибором з точки зору продуктивності: це робить вставку в кластерний індекс випадкової, що є найгіршим сценарієм, і не призводить до корисної кластеризації даних.
З метою демонстрації ми провели тести продуктивності для двох ситуацій. У першому випадку виконувалася вставка в таблицю userinfo з цілочисельним ідентифікатором, певну наступним чином:
CREATE TABLE userinfo (
id int unsigned NOT NULL AUTO_INCREMENT,
name varchar (64) NOT NULL DEFAULT '
email varchar (64) NOT NULL DEFAULT '',
password varchar (64) NOT NULL DEFAULT '',
dob date DEFAULT NULL,
address varchar (255) NOT NULL DEFAULT '',
city varchar (64) NOT NULL DEFAULT '',
state_id tinyint unsigned NOT NULL DEFAULT '0',
zip varchar (8) NOT NULL DEFAULT '',
country_id smallint unsigned NOT NULL DEFAULT '0',
gender ( 'M', 'F') NOT NULL DEFAULT 'M',
account_type varchar (32) NOT NULL DEFAULT '',
verified tinyint NOT NULL DEFAULT '0',
allow_mail tinyint unsigned NOT NULL DEFAULT '0',
parrent_account int unsigned NOT NULL DEFAULT '0',
closest_airport varchar (3) NOT NULL DEFAULT '',
UNIQUE KEY email (email),
KEY country_id (country_id),
KEY state_id (state_id),
KEY state_id_2 (state_id, city, address)
Зверніть увагу на цілочисельний автоінкрементний первинний ключ.
Друга таблиця, userinfo_uuid, ідентична таблиці userinfo, за винятком того, що первинним ключем є UUID, а не ціле число:
CREATE TABLE 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. Вставка непослідовних значень індексу в кластерний індекс
• Сторінка, куди повинна потрапити рядок, може виявитися скинутої на диск і віддаленої з кешу, тоді InnoDB доведеться шукати її і зчитувати з диска, перш ніж вставити новий рядок. Це призводить до великої кількості випадкових операцій введення / виводу.
• InnoDB іноді доводиться розщеплювати сторінки, щоб звільнити місце для нових рядків. Це вимагає переміщення великого обсягу даних.
• Через розщеплення сторінки виявляються заповнені безладно і нещільно, що нерідко призводить до фрагментації.
Після завантаження таких випадкових значень в кластерний індекс має сенс запустити команду OPTIMIZE TABLE, яка перебудує таблицю і заповнить сторінки оптимальним чином.
Мораллю всій цій історії є те, що при використанні InnoDB вам потрібно прагнути до вставки даних в порядку, відповідному первинному ключу, і намагатися використовувати такий кластерний ключ, який монотонно зростає для нових рядків.