Коли дані зберігаються на дискових накопичувачах, вони зберігаються у вигляді блоків даних. Доступ до цих блокам здійснюється цілком, що робить їх доступними для атомарного доступу до диска. Диск-блоки структуровані так само, як і пов'язані списки; Обидва містять розділ для даних, покажчик на розташування наступного вузла (або блоку), і обидва вони не повинні зберігатися суміжно.
У зв'язку з тим, що ряд записів можна сортувати тільки в одному полі, ми можемо заявити, що пошук в поле, яке сортується, вимагає лінійного пошуку, для якого потрібно N / 2 блокування доступу (в середньому), де N - Кількість блоків , на які поширюється таблиця. Якщо це поле є неключових полем (т. Е містить унікальних записів), тоді потрібно шукати все табличний простір в N доступі до блоку.
Якщо в відсортованому поле може використовуватися двійковий пошук, це має доступ до log2 N Крім того, оскільки дані сортуються з урахуванням неключевого поля, інша частина таблиці не потребує пошуку повторюваних значень, як тільки буде знайдено більш високе значення. Таким чином, збільшення продуктивності є істотним.
Що таке індексування?
Індексування - це спосіб сортування декількох записів по декількох полях. Створення індексу в поле в таблиці створює іншу структуру даних, яка містить значення поля, і покажчик на запис, до якої вона відноситься. Потім ця структура індексу сортується, що дозволяє виконувати виконавчі пошуки.
Недоліком індексації є те, що для цих індексів потрібний додатковий простір на диску, оскільки індекси зберігаються разом в таблиці за допомогою механізму MyISAM, цей файл може швидко досягти обмежень розміру базової файлової системи, якщо багато поля в одній таблиці індексуються.
По-перше, давайте намалюємо приблизну схему таблиці бази даних;
Примітка. char був використаний замість varchar, щоб забезпечити точний розмір на диску. Ця приблизна база даних містить п'ять мільйонів рядків і не вказана. Тепер буде проаналізована продуктивність декількох запитів. Це запит з використанням ідентифікатора (поля відсортованого ключа) і одного з використанням firstName (несімвольное несортоване поле).
Приклад 1 - відсортовані або несортовані поля
З огляду на нашу приблизну базу даних r = 5,000,000 записів фіксованого розміру, що дає довжину запису R = 204 байта, і вони зберігаються в таблиці з використанням механізму MyISAM, який використовує розмір блоку за замовчуванням B = 1,024 байта. Блокуючим фактором таблиці буде bfr = (B / R) = 1024/204 = 5 записів на блок диска. Загальна кількість блоків, необхідних для зберігання таблиці, дорівнює N = (r / bfr) = 5000000/5 = 1,000,000 блоків.
Для лінійного пошуку в поле id потрібно середнє число N / 2 = 500,000 запитів блоків, щоб знайти значення, враховуючи, що поле id є ключовим полем. Але так як поле id також сортується, може бути проведено бінарний пошук, що вимагає в середньому log2 1000000 = 19.93 = 20 блоків доступу. Миттєво ми бачимо, що це радикальне поліпшення.
Тепер поле firstName не сортуються і не є ключовим полем, тому бінарний пошук неможливий, і значення не унікальні, і, отже, таблиця потребують пошуку до кінця для точного доступу до N = 1,000,000 блоків. Саме ця ситуація спрямована на виправлення індексації.
З огляду на, що індексна запис містить тільки проіндексована поле і покажчик на початковий запис, розумно, що вона буде менше, ніж багатопільно запис, на яку вказує. Таким чином, для самого індексу потрібно менше блоків диска, ніж вихідна таблиця, тому для цього потрібна менша кількість звернень до блоків. Схема для індексу в поле firstName приведена нижче;
Примітка. Дороговкази в MySQL мають довжину 2, 3, 4 або 5 байтів в залежності від розміру таблиці.
З огляду на нашу приблизну базу даних r = 5,000,000 записів з довжиною запису індексу R = 54 байта і використанням розміру блоку за замовчуванням B = 1,024 байта. Блокуючим фактором індексу буде bfr = (B / R) = 1024/54 = 18 записів на блок диска. Загальна кількість блоків, необхідних для зберігання індексу, так само N = (r / bfr) = 5000000/18 = 277,778 блоків.
Коли його слід використовувати?
З огляду на, що для створення індексу потрібен додатковий дисковий простір (277 778 блоків додатково з вищенаведеного прикладу, збільшення на 28%), і що занадто багато індексів можуть викликати проблеми, пов'язані з обмеженнями розміру файлових систем, необхідно ретельно подумати, щоб вибрати правильний Поля для індексації.
Оскільки індекси використовуються тільки для прискорення пошуку відповідного поля в записах, то зрозуміло, що поля індексування, що використовуються тільки для виведення, будуть просто втратою дискового простору і часу обробки при виконанні операції вставки або видалення, і, таким чином, необхідно уникати. Також, з огляду на характер бінарного пошуку, важлива потужність або унікальність даних. Індексування в поле з потужністю 2 розділило б дані навпіл, тоді як мощность 1000 повернула б приблизно 1000 записів. При такій низькій потужності ефективність зводиться до лінійної сортуванні, і оптимізатор запитів уникає використання індексу, якщо потужність становить менше 30% від номера запису, що робить цей індекс марною тратою простору.
У перший раз, коли я прочитав це, це було дуже корисно для мене. Дякуємо.
З тих пір я отримав деяке уявлення про нестачу створення індексів: якщо ви записуєте в таблицю (UPDATE або INSERT) з одним індексом, у вас фактично є дві операції запису в файлової системі. Один для даних таблиці і інший для даних індексу (і його використання (і - якщо кластерізованний - використання табличних даних)). Якщо таблиця і індекс розташовані на одному жорсткому диску, це вимагає більше часу. Таким чином, таблиця без індексу (купи) дозволить швидше виконувати операції запису. (Якщо у вас було два індексу, у вас було б три операції запису і т. Д.),
Однак визначення двох різних місць на двох різних жорстких дисках для даних індексу і табличних даних може зменшити / усунути проблему збільшення вартості часу. Це вимагає визначення додаткових груп файлів з відповідними файлами на необхідних жорстких дисках щоб визначити своє місцезнаходження таблиці / індексу за бажанням.
Ще одна проблема з індексами - їх фрагментація з плином часу при вставці даних. REORGANIZE допомагає, ви повинні написати процедури, щоб зробити це.
У деяких сценаріях купа більш корисна, ніж таблиця з індексами,
Наприклад: - Якщо у вас є багато конкуруючих записів, але тільки одне нічне читання за межами робочого часу для звітності.
Крім того, диференціація між кластерними і некластерізованний індексами вельми важлива.
Індекс - це просто структура даних, яка прискорює пошук конкретного стовпця в базі даних. Ця структура зазвичай є b-деревом, але також може бути хеш-таблицею або деякої іншої логічної структурою.
Тепер припустимо, що ми хочемо запустити запит, щоб знайти всі відомості про будь-яких співробітників, які отримали назву «Abc»?
Що станеться без індексу?
Програмне забезпечення бази даних буквально мало б розглядати кожну окрему рядок в таблиці Employee, щоб дізнатися, чи є Employee_Name для цього рядка «Abc». І оскільки нам потрібна кожен рядок з ім'ям «Abc» всередині неї, ми не можемо просто перестати дивитися, як тільки знайдемо тільки один рядок з ім'ям «Abc», тому що можуть бути інші рядки з ім'ям Abc. Таким чином, кожен рядок до останнього рядка повинна бути знайдена - це означає, що тисячі рядків в цьому сценарії повинні бути перевірені базою даних, щоб знайти рядки з ім'ям «Abc». Це те, що називається повним скануванням таблиці
Як індекс бази даних може допомогти продуктивності
Весь сенс мати індекс - прискорити пошукові запити, істотно скоротивши кількість записів / рядків у таблиці, які необхідно вивчити. Індекс являє собою структуру даних (найчастіше це B-дерево), яка зберігає значення для певного стовпця в таблиці.
Як працює індекс B-дерев?
Причина, по якій B-дерева є найбільш популярною структурою даних для індексів, пояснюється тим, що вони ефективні в часі - оскільки пошук, видалення та вставки можуть виконуватися в логарифмічному часу. І ще одна важлива причина, по якій B-дерева частіше використовуються, полягає в тому, що дані, які зберігаються всередині B-дерева, можуть бути відсортовані. СУРБД зазвичай визначає, яка структура даних фактично використовується для індексу. Але в деяких сценаріях з певними СУБД ви можете вказати, яку структуру даних ви хочете використовувати в своїй базі даних при створенні самого індексу.
Як працює індекс хеш-таблиці?
Причина, по якій використовуються хеш-індекси, полягає в тому, що хеш-таблиці надзвичайно ефективні, коли мова йде про пошук тільки значень. Таким чином, запити, які порівнюються для рівності з рядком, можуть дуже швидко отримувати значення, якщо вони використовують хеш-індекс.
Наприклад, запит, про який ми говорили раніше, може отримати вигоду з хеш-індексу, створеного в стовпці Employee_Name. Спосіб роботи хеш-індексу буде полягати в тому, що значення стовпця буде ключем в хеш-таблиці, а фактичне значення, зіставлене цього ключа, буде просто покажчиком на дані рядка в таблиці. Оскільки хеш-таблиця в основному представляє собою асоціативний масив, типова запис буде виглядати приблизно так: «Abc => 0x28939», де 0x28939 є посиланням на рядок таблиці, де Abc зберігається в пам'яті. Пошук значення типу «Abc» в індексі таблиці хеш і повернення посилання на рядок в пам'яті, очевидно, набагато швидше, ніж сканування таблиці, щоб знайти всі рядки зі значенням «Abc» в стовпці Employee_Name.
Хеш-таблиці не сортують структури даних, і є багато типів запитів, з якими хеш-індекси не можуть навіть допомогти. Наприклад, припустимо, що ви хочете дізнатися всіх співробітників, яким менше 40 років. Як ви могли це зробити за допомогою індексу хеш-таблиці? Ну, це неможливо, тому що хеш-таблиця хороша тільки для пошуку пар значень ключа - це значить, що запити, що перевіряють рівність
Що саме знаходиться в індексі бази даних? Отже, тепер ви знаєте, що індекс бази даних створюється в стовпці в таблиці і що індекс зберігає значення в цьому конкретному стовпчику. Але важливо розуміти, що індекс бази даних не зберігає значення в інших стовпцях однієї і тієї ж таблиці. Наприклад, якщо ми створюємо індекс в стовпці Employee_Name, це означає, що значення стовпця Employee_Age і Employee_Address також не зберігаються в індексі. Якби ми просто зберегли всі інші стовпці в індексі, то це було б схоже на створення іншої копії всієї таблиці, яка займала б занадто багато місця і була б дуже неефективною.
Як база даних знає, коли використовувати індекс? Коли виконується запит типу «SELECT * FROM Employee WHERE Employee_Name = 'Abc», база даних перевіряє, чи є індекс для стовпця (ів), який запитується. Припускаючи, що стовпець Employee_Name має індекс, створений на ньому, база даних повинна буде вирішити, чи дійсно має сенс використовувати індекс для пошуку шуканих значень - оскільки існують деякі сценарії, де насправді менш ефективно використовувати індекс бази даних. І більш ефективно просто сканувати всю таблицю.
Яка вартість індексу бази даних?
Це займає простір - і чим більше ваш стіл, тим більше ваш індекс. Ще один показник продуктивності з індексами - це той факт, що всякий раз, коли ви додаєте, видаляєте або оновлюєте рядки у відповідній таблиці, ті ж операції повинні виконуватися з вашим індексом. Пам'ятайте, що індекс повинен містити те ж саме аж до хвилинних даних, що і всі, що знаходиться в стовпці (стовпцях) таблиці, який охоплює індекс.
Як правило, індекс повинен створюватися лише в таблиці, якщо дані в індексованих стовпці будуть запитуватися часто.
Індекс - це не що інше, як структура даних, в якій зберігаються значення для певного стовпця в таблиці. Індекс створюється в стовпці таблиці.
Тепер припустимо, що ми хочемо запустити запит, щоб знайти всі відомості про користувачів з ім'ям «Джон». Якщо ми запустимо наступний запит.
Програмне забезпечення бази даних буквально мало б дивитися на кожен рядок в таблиці User, щоб дізнатися, чи є Name для цього рядка «John». Це забере багато часу.
Тут індекс допомагає нам «індекс використовується для прискорення пошукових запитів, істотно скорочуючи кількість записів / рядків у таблиці, які необхідно вивчити».
Як створити індекс
Індекс складається з значень стовпців (наприклад: John) з однієї таблиці і що ці значення зберігаються в структурі даних.
Отже, тепер база даних буде використовувати індекс для пошуку співробітників з ім'ям John, тому що індекс, мабуть, буде впорядкований за алфавітом по імені пользователей.І оскільки він впорядкований, це означає, що пошук імені набагато швидше, тому що всі імена, починаються з «J», будуть поруч один з одним в індексі!
Просто швидке пропозицію. Оскільки для індексації варто додаткова запис і простір для зберігання, тому, якщо вашому додатку потрібно більше операцій вставки / оновлення, ви можете використовувати таблиці без індексів, але якщо для цього потрібно більше операцій пошуку даних, вам слід перейти на індексовані Таблиця.
Коротше кажучи, індекс являє собою структуру даних, в якій зберігаються значення для певного стовпця в таблиці, щоб прискорити пошук запитів.
Класичний приклад «Індекс в книгах»
Розглянемо «Книгу» 1000 сторінок, розділену на 100 розділів, кожен розділ з X-сторінками.
Тепер, без індексного сторінки, щоб знайти конкретний розділ, що починається з букви «S», у вас немає іншого вибору, окрім сканування всієї книги. Тобто 1000 сторінок
Але з вказівний сторінкою на початку ви перебуваєте. І ще, щоб прочитати який-небудь конкретний розділ, який має значення, вам просто потрібно кожен раз переглядати сторінку індексу знову і знову. Після знаходження відповідного індексу ви можете ефективно перейти до розділу, пропускаючи інші розділи.
Але тоді, крім 1000 сторінок, вам знадобиться ще
10 сторінок, щоб відобразити сторінку індексу, так що все 1010 сторінок.
Таким чином, індекс являє собою окремий розділ, який зберігає дані індексованих записів в відсортованому порядку для ефективного пошуку.
У школах все просто, чи не так. П