В основному індекс в таблиці працює як індекс в книзі (звідки і пішла назва):
Припустимо, у вас є книга про базах даних, і ви хочете знайти деяку інформацію про сховище. Без індексу (без додаткової допомоги, наприклад змісту) вам доведеться проходити сторінки один за іншим, поки не знайдете тему (це full table scan). З іншого боку, індекс має список ключових слів, тому ви звернетеся до індексу і побачите, що storage згадано на сторінках 113-120, 231 і 354. Потім ви можете безпосередньо переходити на ці сторінки без пошуку (це пошук за допомогою Індекс, трохи швидше ).
Звичайно, наскільки корисний індекс, залежить від багатьох речей - кілька прикладів, використовуючи порівняння вище:
- Якщо у вас є книга про базах даних і індексовано слово «база даних», ви побачите, що це згадується на сторінках 1-59,61-290 і 292-400. У цьому випадку індекс не надто допомагає, і він може Швидше переходити по сторінках один за іншим (в базі даних це «погана вибірковість»).
- Для 10-сторінкової книги немає сенсу складати індекс, так як ви можете отримати 10-сторінкову книгу з префіксом 5 сторінок, що просто нерозумно - просто сканувати 10 сторінок і зробіть з ними,
- Індекс також повинен бути корисний - зазвичай немає сенсу індексувати, наприклад, частоту букви «L» на сторінці.
Перше, що ви повинні знати, це те, що індекси - це спосіб уникнути сканування всієї таблиці, щоб отримати результат, який ви шукаєте.
Існують різні типи індексів, і вони реалізовані на рівні сховища, тому між ними немає стандарту, і вони також залежать від використовуваного механізму зберігання.
InnoDB і індекс B + Tree
Для InnoDB найбільш поширеним типом індексу є індекс B + Tree, який зберігає елементи в відсортованому порядку. Крім того, вам не потрібно звертатися до реальної таблиці, щоб отримати індексовані значення, що прискорить ваш запит.
«Проблема» в цьому типі індексу полягає в тому, що вам потрібно запросити саме ліве значення для використання індексу. Отже, якщо ваш індекс має два стовпці, наприклад last_name і first_name, порядок, який ви запитуєте в цих полях, має велике значення.
Отже, з огляду на наступну таблицю:
У цьому запиті буде використовуватися індекс:
Тому що спочатку ви запитуєте перший стовпець first, і це не самий лівий стовпець в індексі.
Цей останній приклад ще гірше:
Тому що тепер ви порівнюєте праву частину самого правого поля в індексі.
Хеш-індекс
Це інший тип індексу, який, на жаль, підтримує тільки пам'ять. Це блискавично, але корисно тільки для повного пошуку, а це означає, що ви не можете використовувати його для операцій типу>. <или LIKE.
Оскільки він працює тільки для бекеда пам'яті, ви, ймовірно, не будете його використовувати дуже часто. Основний випадок, про який я можу зараз думати, - це створити тимчасову таблицю в пам'яті з набором результатів з іншого вибору і виконати безліч інших вибірок в цій тимчасовій таблиці з використанням індексів хеша.
Проблема з наведеним вище прикладом полягає в тому, що, оскільки функція CRC32 генерує досить невеликий хеш, ви отримаєте багато зіткнень в хешировать значеннях. Якщо вам потрібні точні значення, ви можете виправити цю проблему, виконавши такі дії:
Як і раніше стоїть хеш-речі, навіть якщо число зіткнень є великим, тому що ви будете виконувати тільки друге порівняння (рядок) проти повторюваних хеш.
На жаль, використовуючи цей метод, вам все одно потрібно потрапити в таблицю, щоб порівняти поле url.
загортати
Деякі факти, які ви можете враховувати кожен раз, коли хочете поговорити про оптимізацію:
Целочисленное порівняння виконується швидше, ніж порівняння рядків. Це можна проілюструвати на прикладі емуляції хеш-індексу в InnoDB.
Можливо, додавання додаткових кроків в процес робить його швидше, а не повільніше. Це можна проілюструвати тим фактом, що ви можете оптимізувати SELECT. розділивши його на два кроки, зробивши перші значення сховища у новоствореній таблиці в пам'яті, а потім виконайте важчі запити в цій другій таблиці.
У MySQL є й інші індекси, але я думаю, що B + Tree один з найбільш використовуваних коли-небудь, а хеш - хороша річ, але ви можете знайти інші в документації MySQL.
Я настійно рекомендую вам прочитати книгу «Висока продуктивність MySQL», відповідь вище безумовно був заснований на її чолі про індекси.
Отже, що таке індекс? Ну, індекс - це структура даних (найчастіше це B-дерево), яка зберігає значення для певного стовпця в табліце.Індекс створюється в стовпці табліци.Ітак, ключові моменти, які слід пам'ятати, це те, що індекс складається з значень стовпців з однієї таблиці і що ці значення зберігаються в структурі данних.Індекс - це структура даних - пам'ятайте про це.
Давайте почнемо наш підручник і пояснимо, чому вам потрібен індекс бази даних, пройшовши дуже простий приклад. Припустимо, що we have a database table called Employee with three columns - Employee_Name, Employee_Age, and Employee_Address. Припустимо, що таблиця Employee має тисячі рядків.
Тепер припустимо, що ми хочемо запустити запит, щоб find all the details of any employees who are named 'Jesus'? Отже, ми вирішили запустити простий запит наступним чином:
Що станеться без індексу на столі?
Як тільки ми запускаємо цей запит, what exactly goes on behind the scenes to find employees who are named Jesus? Well, the database software would literally have to look at every single row in the Employee table to see if the Employee_Name for that row is 'Jesus'. what exactly goes on behind the scenes to find employees who are named Jesus? Well, the database software would literally have to look at every single row in the Employee table to see if the Employee_Name for that row is 'Jesus'. І оскільки ми хочемо, щоб кожен рядок з ім'ям «Ісус» всередині неї, ми не можемо просто перестати дивитися, як тільки знайдемо тільки один рядок з ім'ям «Ісус», тому що можуть бути інші рядки з ім'ям Ісус. Таким чином, кожен рядок до останнього рядка повинна бути найдена` - що означає, що тисячі рядків в цьому сценарії повинні бути перевірені базою даних, щоб знайти рядки з ім'ям «Ісус». Це те, що називається повним скануванням таблиці.
Як індекс бази даних може допомогти продуктивності
Яка структура даних є індексом?
B-дерева є найбільш часто використовуваними структурами даних для індексов.Прічіна, по якій B-дерева є найпопулярнішою структурою даних для індексів, пояснюється тим, що вони ефективні в часі - оскільки пошук, видалення та вставки можуть виконуватися в логарифмічному времені.І ще одна важлива причина, по якій B-дерева частіше використовуються, полягає в тому, що дані, які зберігаються всередині B-дерева, можуть бути отсортіровани.СУРБД зазвичай визначає, яка структура даних фактично використовується для індекса.Но в НЕ я их сценаріях з певними СУБД ви можете вказати, яку структуру даних ви хочете використовувати в своїй базі даних при створенні самого індексу.
Як індекс підвищує продуктивність?
Як створити індекс в SQL:
Ось як виглядав би фактичний SQL, щоб створити індекс в стовпці Employee_Name з нашого прикладу раніше:
Як створити індекс з декількома стовпцями в SQL:
Ми могли б також створити індекс в двох стовпчиках таблиці Employee, як показано в цьому SQL:
В основному індекс - це карта всіх ваших ключів, відсортованих по порядку. Зі списком в порядку, то замість перевірки кожного ключа він може зробити щось на зразок цього:
1: Перехід до середини списку - вище або нижче того, що я шукаю?
2: Якщо вище, перейдіть до проміжної точці між серединою і дном, якщо нижній, середній і верхній
3: Чи є вище або нижче? Знову перейдіть в середню точку і т. Д.
Використовуючи цю логіку, ви можете знайти елемент в відсортованому списку приблизно за 7 кроків замість перевірки кожного елемента.
Очевидно, що є складнощі, але це дає вам основну ідею.
Індекс бази даних або просто індекс допомагає прискорити вилучення даних з таблиць. Коли ви запитуєте дані з таблиці, спочатку MySQL перевіряє, чи існують індекси, тоді MySQL використовує індекси для вибору точних фізичних відповідних рядків таблиці замість сканування всієї таблиці.
Індекс бази даних схожий на індекс книги. Якщо ви хочете знайти тему, спочатку ви переглядаєте індекс, а потім відкриваєте сторінку з темою, що не переглядаючи всю книгу.
Настійно рекомендується створити індекс в шпальтах таблиці, з яких ви часто запитуєте дані. Зверніть увагу, що всі стовпці первинного ключа в основному індексі таблиці автоматично.
Якщо індекс допомагає прискорити дані запиту, чому б нам не використовувати індекси для всіх стовпців? Якщо ви створюєте індекс для кожного стовпця, MySQL повинен будувати і підтримувати індексний таблицю. Всякий раз, коли робиться зміна записів в таблиці, бази даних MySQL повинен перебудувати індекс, що вимагає часу, а також знижує продуктивність сервера бази даних. Створення індексу MySQL
Ви часто створюєте індекси при створенні таблиць. MySQL автоматично додає в індекс будь-стовпець, який оголошується як PRIMARY KEY, KEY, UNIQUE або INDEX. Крім того, ви можете додавати індекси до таблиць, у яких вже є дані.
Щоб створити індекси, ви використовуєте оператор CREATE INDEX. Нижче наведено синтаксис оператора CREATE INDEX: 1 2 3
По-перше, ви вказуєте індекс на основі типу таблиці або сховища:
UNIQUE означає, що MySQL створить обмеження, що всі значення в індексі повинні бути унікальними. Дубльовані значення NULL може використовуватися надворі механізмах зберігання, крім BDB. Індекс FULLTEXT підтримується тільки механізмом зберігання MyISAM і приймається тільки в стовпці з типом даних CHAR, VARCHAR або TEXT. Індекс SPATIAL підтримує просторовий стовпець і доступний для механізму зберігання MyISAM. Крім того, значення стовпця не повинно бути NULL.
Потім ви вказуєте індекс і його тип після ключового слова USING, такого як BTREE, HASH або RTREE, також на основі механізму зберігання таблиці.
Нижче наведені механізми зберігання таблиці з відповідними дозволеними типами індексів: Підтримувані типи індексів сховища MyISAM BTREE, RTREE InnoDB BTREE MEMORY / HEAP HASH, BTREE NDB HASH
По-третє, ви оголошуєте ім'я таблиці і стовпчики списку, який потрібно додати до індекс. Приклад створення індексу в MySQL
У базі даних прикладів ви можете додати стовпець OfficeCode таблиці employee в індекс за допомогою інструкції CREATE INDEX наступним чином: 1
CREATE INDEX officeCode ON employees (officeCode)
Крім створення індексу, ви також можете видалити індекс, використовуючи оператор DROP INDEX. Цікаво, що оператор DROP INDEX також зіставляється з виразом ALTER TABLE. Нижче наведено синтаксис видалення індексу: 1
DROP INDEX index_name ON table_name
Наприклад, якщо ви хочете видалити індекс OfficeCode таблиці employee, який ми створили вище, ви можете виконати наступний запит: 1
DROP INDEX officeCode ON employees
Те, як вони працюють, занадто широкий для охоплення однієї посади SO.
Ось одне з кращих пояснень індексів, які я бачив. На жаль, це для SQL Server, а не для MySQL. Я не впевнений, наскільки схожі ці два.
Просте індексування. Ви можете створити унікальний індекс в таблиці. Унікальний індекс означає, що два рядки не можуть мати однакове значення індексу. Ось синтаксис створення індексу на таблиці
Ви можете використовувати один або декілька стовпців для створення індексу. Наприклад, ми можемо створити індекс на tutorials_tbl за допомогою tutorial_author.
Ви можете створити простий індекс в таблиці. Просто залиште ключове слово UNIQUE із запиту, щоб створити простий індекс. Простий індекс дозволяє дублювати значення в таблиці.
Якщо ви хочете індексувати значення в стовпці в порядку убування, ви можете додати зарезервоване слово DESC після імені стовпчика.