SELECT * FROM members WHERE id = '1' - так чому з індексом це працює швидше? Що цей індекс робить тут? - good_evening 20 травня '17 о 18:00
В принципі індекс в таблиці працює як індекс в книзі (звідки прийшла назва):
Припустимо, у вас є книга про базах даних, і ви хочете знайти деяку інформацію про, скажімо, сховище. Без індексу (без будь-якої іншої допомоги, наприклад змісту) вам доведеться проходити по сторінках один за іншим, поки ви не знайдете тему (це full table scan). З іншого боку, індекс має список ключових слів, тому ви повинні звернутися до індексу і побачити, що storage згадується на сторінках 113-120, 231 і 354. Потім ви можете перемикатися на ці сторінки безпосередньо, без пошуку (це Пошук з індексом, кілька швидше).
Звичайно, наскільки корисний індекс, залежить від багатьох речей - кілька прикладів, що використовують наведене вище порівняння:
- Якщо у вас є книга по базах даних і індексується слово «база даних», ви побачите, що вона згадується на сторінках 1-59,61-290 і 292-400. У цьому випадку індекс не надто допомагає, і він може Швидше проходити сторінки один за іншим (в базі даних це «низька вибірковість»).
- Для 10-сторінкової книги немає ніякого сенсу складати індекс, так як в результаті ви можете отримати 10-сторінкову книгу з префіксом 5-сторінкового індексу, що просто нерозумно - просто відскануйте 10 сторінок і покінчіть з ним,
- Індекс також повинен бути корисним - зазвичай немає сенсу індексувати, наприклад, частоту букви «L» на сторінці.
відповідь дан Piskvor 20 травня '17 о 18:00
Перше, що ви повинні знати, це те, що індекси - це спосіб уникнути сканування повної таблиці, щоб отримати результат, який ви шукаєте.
Існують різні типи індексів, і вони реалізовані на рівні сховища, тому між ними немає стандарту, і вони також залежать від використовуваного вами механізму зберігання.
ІнноДБ і індекс дерева B +
Для InnoDB найбільш поширеним типом індексу є індекс на основі B + Tree, який зберігає елементи в відсортованому порядку. Крім того, вам не потрібно звертатися до реальної таблиці, щоб одержати проіндексовані значення, що прискорює ваш запит.
«Проблема» з цим типом індексу полягає в тому, що вам потрібно запросити крайнє ліве значення, щоб використовувати індекс. Отже, якщо ваш індекс має два стовпці, скажімо last_name і first_name, порядок, в якому ви запитуєте ці поля, має велике значення.
Отже, з огляду на наступну таблицю:
Цей запит буде використовувати індекс:
Але слід не буде
Тому що спочатку ви запитуєте стовпець first_name. а це не самий лівий стовпець в індексі.
Цей останній приклад ще гірше:
Тому що зараз ви порівнюєте саму праву частину самого правого поля в індексі.
Хеш-індекс
Це інший тип індексу, який, на жаль, підтримує тільки бекенда пам'яті. Це швидко, але корисно тільки для повного пошуку, що означає, що ви не можете використовувати його для таких операцій, як>. <или LIKE.
Оскільки він працює тільки з внутрішньою пам'яттю, ви, ймовірно, не будете її використовувати дуже часто. Головний випадок, про який я можу згадати зараз, це той, який ви створюєте тимчасову таблицю в пам'яті з набором результатів з іншого select і виконуєте безліч інших вибірок в цій тимчасовій таблиці з використанням хеш-індексів.
Якщо у вас є велике поле VARCHAR. ви можете «емулювати» використання хеш-індексу при використанні B-дерева, створюючи інший стовпець і зберігаючи на ньому хеш великого значення. Припустимо, ви зберігаєте url в поле і значення досить великі. Ви також можете створити ціле поле з ім'ям url_hash і використовувати хеш-функцію типу CRC32 або будь-яку іншу хеш-функцію для хеширования url при вставці. І потім, коли вам потрібно запросити це значення, ви можете зробити щось на зразок цього:
Проблема з вищенаведеним прикладом полягає в тому, що, оскільки функція CRC32 генерує досить невеликий хеш, ви зіткнетеся з великою кількістю колізій в хешировать значеннях. Якщо вам потрібні точні значення, ви можете усунути цю проблему, виконавши такі дії:
Все одно варто хешірованного речі, навіть якщо номер зіткнення високий, тому що ви будете виконувати тільки друге порівняння (строкове) з повторними хешамі.
На жаль, використовуючи цей метод, вам все одно потрібно потрапити в таблицю, щоб порівняти поле url.
загортати
Деякі факти, які ви можете розглянути кожен раз, коли хочете поговорити про оптимізацію:
Целочисленное порівняння набагато швидше, ніж порівняння рядків. Це можна проілюструвати на прикладі про емуляції індексу хеша в InnoDB.
Можливо, додавання додаткових кроків в процесі робить його швидше, а не повільніше. Це можна проілюструвати тим фактом, що ви можете оптимізувати SELECT. розбивши його на два етапи, зробивши перший з них зберігає значення у новоствореній таблиці в пам'яті, а потім виконати більш важкі запити по цій другій таблиці.
MySQL має і інші індекси, але я думаю, що B + Tree є найбільш використовуваним і хеш-одним з них добре знати, але ви можете знайти інші в документації MySQL.
Я настійно рекомендую вам прочитати книгу «Висока продуктивність MySQL», відповідь вище безумовно був заснований на її чолі про індекси.
відповідь дан clarete 20 травня '17 о 18:00
У таких випадках переваги матимуть такі запити: 1. SELECT last_name, first_name FROM person WHERE last_name = "Constantine" 2. SELECT last_name, first_name FROM person WHERE last_name LIKE "% Constantine" - Akshay Taru 20 травня '17 о 18:00
Я підвищив вам рейтинг, тому що ви були на 127, а відповідь №1 - на 256. Я не міг не робити все красивим і чистим, двійковим способом. - pbarney 20 травня '17 о 18:00
Це була нова інформація для мене: «Порядок, за яким ви запитуєте ці поля, має велике значення». Дякую. - Khatri 20 травня '17 о 18:00
Мені більше подобається цей відповідь, ніж прийнятий відповідь. завдяки - Rahul Goyal 20 травня '17 о 18:00
Отже, що таке індекс? Ну, індекс - це структура даних (найчастіше це 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'? Отже, ми вирішили виконати простий запит на зразок цього:
Що станеться без індексу на столі?
Як індекс бази даних може допомогти в продуктивності
Яка структура даних є індексом?
По-дерева є найбільш часто використовуваними структурами даних для індексів. Причина, по якій дерева B є найбільш популярною структурою даних для індексів, пов'язана з тим, що вони ефективні в часі - оскільки пошук, видалення та вставка можуть виконуватися в логарифмічному часу. І ще одна важлива причина, по якій B-дерева частіше використовуються, полягає в тому, що дані, що зберігаються всередині B-дерева, можуть бути відсортовані. СУРБД зазвичай визначає, яка структура даних фактично використовується для індексу. Але в деяких сценаріях з певними СУРБД ви можете фактично вказати, яку структуру даних ви хочете використовувати для своєї бази даних при створенні самого індексу.
Як індекс підвищує продуктивність?
Як створити індекс в SQL:
Ось те, що фактичний SQL буде виглядати для створення індексу в стовпці Employee_Name з нашого прикладу раніше:
Як створити індекс з декількома стовпцями в SQL:
Ми також могли б створити індекс по двом з стовпців таблиці Employee, як показано в цьому SQL:
відповідь дан Pankaj katiyar 20 травня '17 о 18:00
@ User64141 В ідеалі цей індекс використовується для скорочення часу пошуку. Сказавши, що в кінці дня значення будуть обрані з точною рядки. Ви не можете мати їх частина індексу. Якщо вам потрібно виконати пошук за кількома значеннями стовпчика, ви можете зробити складовою індекс. Все ще вибір рядків виконується тільки тут, значення вибираються з початкового рядка. - Karthikeyan 20 травня '17 о 18:00
Індекс бази даних, або просто індекс, допомагає прискорити отримання даних з таблиць. Коли ви запитуєте дані з таблиці, спочатку 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 index підтримує просторовий стовпець і доступний на движку зберігання MyISAM. Крім того, значення стовпця не повинно бути NULL.
Потім ви називаєте індекс і його тип після ключового слова USING, наприклад, BTREE, HASH або RTREE, також на основі механізму зберігання таблиці.
Нижче перераховані механізми зберігання таблиці з відповідними дозволеними типами індексів: допустимі типи індексів сховища даних MyISAM BTREE, RTREE InnoDB BTREE MEMORY / HEAP HASH, BTREE NDB HASH
По-третє, ви оголошуєте ім'я таблиці і стовпчики списку, які ви хочете додати в індекс. Приклад створення індексу в MySQL
У прикладі бази даних ви можете додати стовпець officeCode таблиці employees в індекс за допомогою оператора CREATE INDEX наступним чином: 1
CREATE INDEX officeCode ON employees (officeCode)
Крім створення індексу ви також можете видалити індекс за допомогою інструкції DROP INDEX. Цікаво, що оператор DROP INDEX також підтверджено оператором ALTER TABLE. Нижче наведено синтаксис видалення індексу: 1
DROP INDEX index_name ON table_name
Наприклад, якщо ви хочете видалити index officeCode таблиці employees, який ми створили вище, ви можете виконати наступний запит: 1
DROP INDEX officeCode ON employees
відповідь дан sheriff 20 травня '17 о 18:00