Створюється проста таблиця даних:
Як видно з цього прикладу, в колонці id створюються унікальні ключі (за рахунок AUTO_INCREMENT). Поле user_id також містить унікальні значення ідентифікаторів користувачів, які генеруються скриптом (використовую mt_rand) під час реєстрації в особистому кабінеті.
Для вибірки роблю наступний запит:
Задумався про використання індексів у своїй таблиці даних, для прискорення вибірки. У документації написано:
Наявність індексу може істотно підвищити швидкість виконання деяких запитів і скоротити час пошуку необхідних даних за рахунок фізичного або логічного їх упорядкування.
Чи потрібно створювати індекси для поля user_id для прискорення вибірки з мільйонів записів, якщо всі значення ідентифікаторів в цьому полі і так унікальні?
Якщо все таки потрібно, то який індекс створити: кластерний або НЕ кластерний (не зовсім розумію різницю між ними)?
Умова WHERE (в моєму прикладі зверху) змушує СУБД перебирати всі записи в таблиці? Або ж СУБД відразу звертається конкретно тільки до тих записів, які задовольняють умовам пошуку (user_id = 28572), не зачіпаючи при цьому інші записи?
заданий 24 Квітня '15 в 6:38
Чи потрібно створювати індекси для поля user_id для прискорення вибірки з мільйонів записів, якщо всі значення ідентифікаторів в цьому полі і так унікальні?
Так, потрібно - тому що збираєтеся часто робити вибірку з таблиці за ознакою вмісту цього поля. Раз архітектурою обумовлена унікальність даних, що потрапляють в це поле, буде гарною ідеєю використовувати UNIQUE індекс. Якщо на полі призначений унікальний індекс - БД не дасть вставити запис з дублюючим його значенням. Такий крок в сторону нормалізації.
Якщо все таки потрібно, то який індекс створити: кластерний або НЕ кластерний (не зовсім розумію різницю між ними)?
Умова WHERE змушує СУБД перебирати всі записи в таблиці?
Так. Дізнатися це (і багато іншого) можна, виконавши запит з ключовим словом EXPLAIN перед ним (EXPLAIN SELECT * FROM. WHERE. ORDER BY. LIMIT.). Якщо відбувається вибірка з умовою, і умова включає в себе поле, не покриті відповідним індексом - MySQL буде, швидше за все, виконувати полнотаблічное сканування. Це дорога з точки зору введення-виведення операція, тому грамотна розстановка індексів - суть половина успіху оптимізації БД для швидкої роботи.
Додам до відповіді @Mirdin
Класерний індекс фізично впорядковує таблицю за індексом. Найшвидший (для пошуку). Очевидно що він може бути тільки один. PK завжди кластерний індекс за замовчуванням.
Ви можете скоротити час виконання на 50% (в середньому) якщо додасте LIMIT 0,1 - тому що база не знає про унікальності вашого стовпчика. Без ліміту вона буде перебирати всі значення таблиці, навіть якщо вже знайде збіг.
Але правильно так, зробити цей стовпець:
Загалом як ви напевно зрозуміли однозначної відповіді немає. Дійте послідовно, додайте один індекс і подивіться на результат. Цілком можливо що одного індексу буде досить.
відповідь дан 24 Квітня '15 в 7:34
Дякую Вам за вичерпні відповіді. Тепер я бачу різницю між кластерними і не кластерними індексами. У мене залишився заключний питання, щодо комбінованих індексів. А саме. Залежно від завдання, вибірка з БД може проводитися по одному або декількох полях. Чи правильно буде створити окремі індекси для кожного з полів і один (комбінований) індекс для всіх полів? - StasHappy 24 Квітня '15 о 7:45
Залежить від запитів. EXPLAIN допоможе знайти відповідь. - AntonioK 24 Квітня '15 о 7:51
@stashappy я додав у відповідь. - Petr Abdulin 24 Квітня '15 о 8:05
БлагоДарю Вас Друзі. Буду пробувати різні варіанти. Подивлюсь що буде повертати EXPLAIN, порівняю результати. - StasHappy 24 Квітня '15 о 8:15
- Так, якщо часто фільтруєте саме по цьому полю.
- Чи не кластерний, у вас вже є PK.
- Сервер не володіє магією, тому, якщо немає індексу, буде перебирати всі записи в таблиці, якщо є то пройдеться по структурі яку реалізує індекс, але це теж не "відразу звертається конкретно тільки до тих записів".
P.S. Написав взагалі, конкретно в MySQL можуть бути якісь відмінності.
відповідь дан 24 Квітня '15 в 7:02