Як працюють індекси в базах на прикладі. MySQL vs Postgres. UUID vs Auto Increment.
HTML-код
- Опубликовано: 12 май 2024
- На прикладі розберемо роботу індексів в базах даних. Чому це варто знати й що це нам дає?
Спробував відповісти на наступні питання:
✅Як працюють індекси?
✅Що таке кластерний індекс?
✅Різниця між MySQL та Postgres при роботі з індексами?
✅Пару слів про MVCC й Heap Only Tuple
✅Чому UUID може з'їсти ваші гроші?
✅Що таке covering index?
✅Як OFFSET впливає на виборки?
✅Як індекси впливають на INSERT?
Приклади коду з відео - github.com/koorchik/jabascrip...
Станьте спонсором цього каналу: / @aboutprogramming
Допоможіть каналу розвиватися й отримуйте доступ до ексклюзивного контенту.
Зміст відео:
0:00 - Попередні вступні відео
1:01 - Що таке індекс
2:42 - Кластерний індекс
3:24 - Пошук в індексі MySQL
4:57 - Пошук в індексі Postgres
5:55 - UUID vs Auto Increment
7:38 - MVCC
9:04 - Heap Only Tuple
10:34 - Практичні висновки з теорії
15:00 - Практика
15:15 - Розміри таблиць
17:52 - Count(*)
18:55 - Виборки без індексів
20:20 - Додаємо індекси
21:23 - Розмір індексків
23:48 - Виборки з індексами
28:00 - Covering index
30:52 - Швидкість вставки з індексами
33:18 - Швидкість вставки без індексів
34:22 - Підсумок
🏠 Мої соцмережі:
Жабаскрипт в телеграмі - t.me/jabascript
Я в Твітер - / viktorturskyi
Мій Linkedin - / turskyi
#програмування #українською #mysql #postgres #sql #indexes #programming #javascript
Прекрасний контент. Таких якісних відео з програмування я не бачив на російськомовному ютубі. На англомовному ютубі бачив канали десь Вашого рівня, але не кращі за Вас.
Це просто супер. Я Вами захоплююсь.
Дякую за відгук) Радий, що контент виходить корисним
а поділіться будьласка англомовним каналом? будь ласка
дочитав коментарі, і відповіді на них, тоже дуже цікаво і корисно.
Дякую
Дякую ютубу, що порекомендував мені цей канал
у нього ще є телеграм канал
Чудове відео. З третього відео допетрав, що мускул == MySQL)
найкраще пояснення про індекси під капотом, яке тільки можна знайти
Велике вам дякую, якраз зараз працюю з таблицями в mySQL, на проекті. І таке глибоке розуміння цих процесів,дуже корисне для побудови загальної структури, та алгоритмів.
Вдячний за чудовий матеріал! Але є питання: чому ніхто не звернув увагу, що UUID зберігається як varchar? Є ж можливість зберігати як char(32), що економить 1 байт, а ще є можливість взагалі зберігати як binary(16). Було б чудово обговорити і такі формати й подивитись на розміри індексів. Дякую за популяризацію інженерії через голову, а не через дупу ;)
Дякую) Гарний коментар. Спочатку думав для демо робити BINARY, але потім зрозумів, що майже завжди бачу VARCHAR й це буде більш показовим. А ось окреме відео про формати мабуть варто зробити, можна й про IP як INT(4) згадати та інші кейси
Вельми дякую за такий чудовий та корисний матеріал.
Дякую за чудовий контент. Чітко, цікаво, без води, ще й солов`їна ласкає вуха ;)
Все круто. але дійшов до місця, де проставляються індекси, то у мене на ССД один індекс робився 9 хвилин) Після цього вирішив просто дивитись)
Дякую за ваші відео, дивлюся вже 5те відео поспіль, ви підіймаєте дуже цікаві теми, про які хочеться зайвий раз послухати і дізнатися щось нове)
Сильний контент. Все чітко і зрозуміло. Будь ласка продовжуйте. Цікавлять від вас основи як все працює на максимально низькому рівні. Мені в роботі це дуже допомагає, коли розумієш щось на глибокому рівні. Мені здається краще розуміти кілька речей досить глибоко ніж супер багато і поверхнево.
Пропоновані теми:
Алгоритми
Ооп
Як працюють різні мови програмування (низькорівневі моменти)
Як працює конвеєр nodejs
Як влаштовані процеси sdlc у вас в google)))
Було дуже цікаво. Щось я знав, щось забув, тому було познавально. Особливо нюанс з офсетом!
Дуже крутезне відео. Таке враження, що до цього моменту я працював з мускулем як макака )
Дякую! Радий, що відео вийшло корисним :)
@@AboutProgramming з задоволенням ще б послухав про ньюанси комбінованих індексів, якщо там матеріалу набереться на окреме відео )
мені кажеться, більшість таких
Цікава тема.
Дякую автору за гарне пояснення з прикладами ще й українською мовою)
Ну і ютубу за рекомендацію.
Дуже дякую! Чудовий та унікальний матеріал. Згадую часи коли працював в базами, якби я тоді це знав... :)
Дуже дякую за круте відео, багато дізнався, було б дуже круто якщо б ти розповів про кеши у базах та рівні ізоляцій транзакцій із прикладами
Дякую за пізнавальний контент українською!
Дуже зрозуміла подача складних і незрозумілих речей.
Дуже допомагає практична частина відео(приклади)
Дякую. Було б цікаво продовження в сторону роботи з великими обʼємами даних, sharding vs partitioning vs cqs/cqrs.
Було б круто побачити такий ж відос по Postgress. Дуже крутий контент, не так давно проходив курс по SQL і там не було таких корисних деталей.
А що саме цікавить? В цьому відео ж розповів про особливості Postgres в тому числі. Відносно бенчмарків, то весь код є на GitHub, можна трохи адаптувати й протестувати. Тобто, навіть не знаю, що ще можна додати 🙂
Дуже круте відео, дякую, чекаю з нетерпінням наступних відосів!!!
Дякую! Класна подача і дуже цікаво дивитись)
Дякую за відео, та ще з прикладами
Дуже цікаво було подивитися - а ще цікавіше було скачати дамп і трохи покрутити його самостійно!))) Щиро дякую за чудовий і корисний контент, Вікторе! Так тримати!!!
Віктор, це просто розрив 🔥 відео ТОП!
Дякую, дуже гарно розтлумачуєте тему.
Ви дуже чітко роз'яснили принцип роботи індексів у MySQL та PostgreSQL, а також важливість вибору між UUID та Auto Increment. Чекаю на ваші наступні відео. Дякую
Дякую!
Дякую за твої зусилля. Чудовий матеріал. Особливо дякую за порівняння між mysql та postgres!
Дякую за цікаве відео
Дуже багато чого дізнався, дякую
Дуже корисне відео, дякую!
Спасибо большое за материал)
Very useful info
Відео супер! Дуже цікаву тему вибрав автор. Дякую!
Дуже корисно! Дякую за відео!
Дуже корисне відео. Я от не знав що в Постгресі вирішили проблему з довгими пк в індексах. Ну і звичайно требо поширювати знання про БД серед розробників, бо часто трапляється на проекті немає ДБА і базу проектують саме розробники.
Дякую за відео!
це вже цікаво, з підв'язкою до заліза і практики
Дякую за класний контент)
Топчик
Крутезне відео! Дякую!
Мені здається, що відео з камери краще змістити в верхній правий кут при шерингі консолі! Вибачайте за непрохану пораду )))
Дякую! Спробую погратися з розміщенням відео з камери. Може буде краще)
Норм пояснив. Дякую
Дуже дякую!
Круто. Дякую!
Як завжди на найвищому рівні!!!!
Дуже круте відео дякую
Дякую за корисний контент для розробників, очікую на наступні відео з нетерпінням)
Дякую!
Вподобайка і коммент! :)
І підписка :)))
окремий лайк за українізований адмінер😊
Дякую за пізнавальний матеріал. Я навіть і не здогадувався що primary key індекс такий підступний і саме він фігурує в інших індексах, а не фізичний адрес даних. Це при тому що про мускл я почитую(чисто для себе, на рівні хоббі) кілька років.
Реально отакі от матеріали з неочевидними нюансами дуже корисні , допомагають краще розуміти що відбувається під капотом бази даних при тих чи інших запитах.
дякую, класно все показуєш!
Неймовірно круто.
Що прям дуже незвично це те що, є одночасно і теорія і реальна практика з прикладами реальних систем. І це напевно про весь канал можна сказати. Респект, напевно ніколи такого не бачив. (Хоча може і погано шукав :D)
Чудовий контент. Цікаво було почути, як краще організувати онволення індексів на проекті, особливо коли дані часто онволюються.
унікальний контент! а подачааааааааааа)))
Чудово, дякую! Поступово буду індексувати контент з вашого каналу у своїй голові :)
Дякую. Класне відео!
топ канал для програмістів
повага за українську мову
Чудове і дуже корисне відео)
Лайк за хардковий контент :)
Хотілось би побачити відео на тему вибір між mysql і postgres. Основні нюанси які потрібно враховувати на початку створенні проекту, по швидкодії, розміру проекту і тд.
Зазвичай критичної різниці немає в стандартних фічах. Головне вміти готувати ту чи іншу базу. Але різниця є в додаткових фічах. Колись в postgres не було logical replication, а в MySQL геоіндексів. Зараз більш менш парітет. Також є різниця в ліцензіях. Також раніше не всі провайдери підтримували обидві бази в managed варіанті. Я персонально в більшості випадків користую MySQL, оскільки він мені зрозуміліший й немає всіляких штук типу Auto vacuum й тд. Також мені подобається тулінг під MySQL типу workbench. Але в ряді проектів був й postgres
@@AboutProgramming дякую, зараз в багатьох вакансіях вимагають іменно postgres , а я мав справу тільки з MySQL тому і цікаво чи є там щось настільки кардинального
Привіт, Вікторе! Класні відео -- дякую.
Є маленька прохання: коли показуєш консоль, то підійми її від нижнього краю екрану, бо якщо зачепити мишку, то вспливає панель керування переглядом на ю-тьюб і командний рядок не видно -- доводиться перематувати, а бо читати вже історію. Будь ласка.
Я щиро вдячний вам за контент такого рівня, дивлячись його я розумію за що люблю програмування! Підкажіть який у вас SSD?)
Дякую за відгук🙂 Відносно SSD, у мене Samsung 980 Pro M.2, але зараз можна взяти трохи новіший - 990 Pro
@@AboutProgramming беріть кілька і в raid масив їх )))
Привіт, дякую за відос - дуже цікава тема) ти згадував про убер, що вони переїздили с постргреса на майсіквел, так от взагалі було б цікаво подивитися, як би ти до прикладу аналізував архітектури БД різних біг тех проектів. Ну звісно там велика варіативність, але думаю зрозуміло що маю на увазі. Я вивчаю бази даних зараз, і вважаю найбільш важливою але і складною темою - розуміння концептуального використання різних БД під різні кейси. Так от мені здається, що великі тех стартапи - це гарний приклад хайлоаду і аналізу що і як повинно працювати.
Можливо це більше про сістем дизайн, ніж про лоу левельні фундаментальні речі, але виходить все сильно пов'язано.
Дуже класний контент, дякую велике!
Хотілось би ще почути "Real life uses" для індексування, як саме аналізувати проект під час вибору Бази даних, чи використовувати індексування, чи може коли треба комбінувати різні техніки.
P.S.
Дуже подобається такий україномовний контент
Мені подобається цілісність бачення у створенні цих відео. Думаю, тому я досі не помітив води, все по-суті
Гарне пояснення. Тюнінг індексів це завжди пошук балансу між R/W.
У MS SQL Server є можливість побачити статистику по індексах (Seek, Scan, Lookup, Update).
Чи є можливість побачити аналогічну статистику у вказаних СУБД?
Дякую. Практично в кожній базі є якість інтсрументи профайлінгу. В MySQL є PEROFRMANCE_SCHEMA також є explain analyze для окремого запиту, чи profile. Чи конкретно є загальна статистику по тому, яки використовувався індекс й скільки часу в кожному режимі провів, то такого не памʼятаю (але може вже й є таке). Ну, й можливо Percona MySQL мають більше метрик по індексам
В postgres є така сама статистика pg_stat_all_tables and pg_statio_all_indexes
Дякую за відео, нещодавно ютуб підкинув в рекомендаціях. В очевидних речах знаходиш шось нове. Дозволю собі ремарку щодо показаного в відео (27:01). Не знайшов в коментарях, можливо зле шукав, тому сорі якщо дубль.
На прикладі з одним полем name не так критично. Але по факту прийдеться додати пів таблиці в індекс по умовно взятому року, бо то все треба показати користувачу. Ми ж не тільки назву покажемо. Ще якусь картинку, кусок опису і т.д. Тому якось не сильно ефективно з точки зору розміру такого індексу.
Як варіант, щоб не тримати все в індексі можна то ділити в два запити. Спочатку з індексу витягувати айді, а потім робити селект по айді. На практиці то виходить ефективніше.
Так й є. Часом два запити краще або можна subquery й join - www.taogenjia.com/2022/08/23/MySQL-Query-Optimization-Tips/#High-Offset-Page-Query-Improvement
Дякую за контент.
Таке питання, як у вашому досвіді на практиці подібні мікрооптимізації (накшталт додавання поля name в індекс, щоб швидко робити звужений селект по id, year, name суто з індексу й не ходити за основними данними) синхронізували з кодом за канонами DDD?
Бо, наприклад, з одним й тим самим Entity можуть бути різні дії, й для якихось операцій не потрібні всі дані з БД по даному об'єкту. Під різні дії створювати окремі, обрізані підмножини Entity (ну й, відповідно, репозиторії чи звідки там його хто буде конструювати)?
Гарне запитання) На практиці були різні ситуації. Але були такі, коли треба було дуже зважено підходити до індексів й навіть писати підказки (index hints) для mysql (зараз це можна зробити через SQL, раніше це було через коменти), які індекси використовувати бо query planner часом робив не те, що треба. Але в більшості випадків проблеми немає, або вона вже вирішена за рахунок кешування. На практиці це може бути потрібно, коли наприклад обробка телефоних дзвінків (це був мій кейс) або електронна біржа або якійсь специфічний IoT. Ну й залежить від стораджу. На HDD додатковий пошук по диску це наймовірно дорого у порівнянні з SSD, але внутрішній кеш бази теж вирішує.
Відносно Entity, то я майже завжди витягую всі поля, якщо треба інстанціювати один об'єкт. А от якщо треба отримати список, то я частіше просто роблю абстракцію типу Report, яка вже тягне те, що треба. Й спочатку це може бути витягування даних з різних entities, потім це може бути прямй доступ в базу з джойнами, а потім взагалі окрема таблиція в яку копіються дані для репортів під час створення/оновлення різних entites (або просто роблю якусь денормалізацію). Тобто це такий собі CQRS, коли для звітів у мене окремі абстракції, бо майже завжди треба тягнути про різні entities й з різних таблиць. А от такі звіти вже парамтризовані й всередені звіт вирішує, що тягнути з бази або таких звітів може бути багато навіть на базі тих самих таблиць. Й відповідно такі абстракції дозволяють легко використовувати переваги covering indexes або include columns (postgres вміє) в індексах
@@AboutProgramming прошарений ORM як наприклад дотнет ентіті фреймворк вміє робити проекцію - тобто в сіквел статементі вибирвє тіки ті колонки які ти запросив в коді.
Дякую, дещо дізнався нове. Але от у мене ще в житті не було стільки записів в таблицях. Це може якісь круті магазини, чи системи високонавантажені. Один раз у мене була таблиця там десь трішки більше 1 млн записів.
Домашне завдання по темі мігрувати праймарі ключ з юід в ід на живій базі ;) Якщо що то це був жарт :)
MySQL може використовувати різні storage engine. Тому бажано уточнити, що те, що ви розповіли актуально для InnoDb ;)
Так, innodb вже давно дефолт в MySQL. Myisam навіть не знаю навіщо сьогодні може знадобитися на практиці. Але так, в відео все про innodb. Гарне зауваження
Дякую за класне відео. Дуже цікаво! Як на рахунок uuid v7 який залежний від часу, це буде теж настільки важким?
Дякую) з точки зору розміру те саме, а cache hit буде краще, тому кеш бази буде ефективніше працювати. Тобто при інсерті скоріше за все потрібна сторінка буде в кеші, бо primary key відсортований, але всі інші проблеми залишаться ті самі
Дякую за відео.
Які книги по БД, можете порекомендувати ?
Загалом, думаю, буде круто, якщо зможете записити відео зі списком літератури, яка корисна на вашу думку
Робив одне відео на каналі про книжки, але там більше про проектування. Відносно баз даних, то Designing Data-Intensive Applications Мартіна Клепмана. Але ще пару відео про книжки планую зробити
@@AboutProgramming дякую за рекомендацію)
GO UA!
Привіт, круте відео, дуже пізновально.
Якщо можна покрити історію з індексами у SQL-server, так як цю базу використовуємо на роботі. Дякую
Дякую 🙂 SQL server концептуально працювати має схоже - індекси, дерева, кластерні індекси й тд. Дрібні деталі будуть відрізнятися, але це вже найкраще дивитися по документації для конкретної версії бази. Ще один аспект, який хочу покрити в наступних відео це інвертовані індекси. Й можливо ще geo індекси, теж цікава тема
тут прийшов про mssql розповісти, а тут як питання. як вчасно.
коротко - є сторінки по 8кб. це контейнери для як для рядків, так і для індексів. кожна сторінка має інформацію хідер, де є номер файла де вона сберігається, та порядковий номер, а також objectid об'єкта (таблиці) що вона зберігає в собі, а також id індекса (indexid - 0,1,255 зарезервовані). і купу оптимізаційних речей, наприклад сторінки виділяються суміжними блоками по 8 сторінок - екстентами що належать однієй таблиці, це видно коли індекс майже пустої таблиці займає 64кб, doubly linked list - посилання на сусідні сторінки які належать одному і тому ж індексу, якщо індекс (також таблицю) представити у вигляді ланцюжка сторінок, ну і ще є allocation-map. але про індекси.
індекси, як і в інших btree-структурах, це можна окремо розділити на root-branch-сторінки, вони там якось побудовані, що в кінці кіців посилаються на leaves цього дерева, і дозволяють швидко знайти ту сторінку з "листом", в якому невелика кількість записів ключів індексів відсортована.
в листовій сторінці - в залежності від того чи це кластерний індекс чи ні, :
якщо некластерний індекс то там ключі та координати сторінки де шукати рядок з данними. здається це номер-файла-номер-сторінки-оффсет-рядка. "але це не точно", можливо складніше. але фактично посилаються на номер сторінки та запис в сторінці.
якщо кластерний, то її то листовою сторінкою будуть самі рядки з даними таблиці. в такому випадку сторінки мають indexid=1, кластерний, і це ввається відсортовані рядки таблиці. такий індекс може бути лише один, тому що для другого сортування потрібно зробити копію листових сторінок таблиці з даними, і підтримувати консистентність.
якщо кластерних індексів у таблиці нема, данні таблиці лежать в сторінках що називаються heap table, мають indexid=0, сторінки мають doubly linked list-посилання, це дозволяє сканувати їх.
порядок в якому лежать рядки - як придеться, в якому порядку вставили записи, його декларативно нема, і якщо були оновлення що змінюють загальну длину рядка, то select без order by може повернути на той самий запит рядки в іншому порядку.
звичайні некластерні індексі - їх може бути багато, мають indexid=2,3... і так далі.
сторінки фізично на диску вони можуть лежати в будь-якому порядку, а "таблиця відсортована" - мається на увазі те що якщо зчитувати ланцюжок в порядку який задано в doubly linked list - то рядки будуть відсортовані по ключу (одному або групі полів).
перевага саме двозв'язкового списку в тому, що якщо були дуже щільно заповнені даними сторінки в середині ланцюжка, а потім прийшла команда що збільшила довжину рядка, саме тих даних в середині, то підсистема бд виділяє місце в файлах бд в будь-якому вільному місці, додає їх в середину ланцюжка таким чином, що змінює посилання на сусідів лише в двох сторінках між якими вставляє новий екстент з 8 сторінок. до речі в індексних сторінках такі зміни бувають дуже часто, виділяються сторінки, частково заповнені даними, і тому вони розбухають, стають нещільними, потребують регулярної дефрагментації.
@@olegmakarikhin дякую!
Дякую за відео!
Підкажіть будь ласка, чи варто використовувати фултекст індекс для пошуку запису з строгою рівністю, а не по слову в середині з використанням %{searchPattern}%?
Тобто чи буде відрязнятися швидкість where name="somename" від where Match(name) against ('somename')?
Тут краще підійде звичайний індекс. По перформансу має бути як мінімум не гірше
Віктор а який планшет ви використовуєте та як транслюєте з нього на лінукс?
Зазвичай просто записував екран планшету прямо на планшет, а потім вже це відео додавав під час монтажу. Але в наступних відео планую використовувати програму scrcpy. Дуже проста й гарно працює.
Відносно планшету, то у мене Galaxy Tab S7 FE
Дуже корисно!
А чого не було подібного бенчмарку з postgres?
Дякую! Відносно postgres, то не ставив задачі порівнювати перформанс баз. Ідея була розповісти концептуально про роботу індексів. MySQL більш показово й більш цікаво, оскільки покриває більшість кейсів й кейс з кластерними індексами, тому вибрав його. Ну й на все и часу не вистачає 🙂
15:52 підозрюю що така різниця між розмірами обумовлена тим що кластерний uuid це гарантовані і вставки в "середину" індекса, це можна перевірити якщо перебудувати індекс, optimize /alter table.. force. Але в реальній базі на це витратиш дофіга часу, заблокуєш, але подальша робота, інсерти знову приведуть до того ж результату
Так, дякую! В відео я згадував, що може резервуватися додаткове місце, але на цьому не робив акцент, оскільки це відбувається для всіх індексів. Й окрім того по дефолту innodb_fill_factor дорівнює 100, тому ефект мінімальний. Але протестую на базі й відпишу, що вийшло
В мене на проекті теж була нещодавно дискусія auto incement vs uuid, але ніхто не зміг доказати що щось є краще за інше. В результаті лишили uuid і все. Але я більше за автоінкремент, бо це просто зручно
Ще часто є другий аспект - як ресурс ідентифікується зовні, наприклад в URL. Й тут вже не стільки технічний аспект, а скільки бізнесовий. Авто інкремент дозволяє спарсити сайт (бо можна просто перебирати айді), дозволяє побачити скільки чого є на сайті (наприклад юзерів, товарів й тд). Але, наприклад, якщо це тікет система, то автоінкремент навпаки дозволяє легко зрозуміти, що один тікет був створений раніше/пізніше іншого. В Гуглі у нас коміти в системі контроля версій так нумеруються, що теж дуже зручно
@@AboutProgramming ну так цей автоінкремент можна не показувати в API. Тоді і бізнес в порядку, і швидкодія, і розміри, і в ногу собі не вистрілили за допомогою uuid=primary key
@@OlegShevtsov512 Так й є :)
якщо є час і наснага, пару слів oracle db, а то був на проєкті, де мігрували з оракла в мускуль, і йому було дуже тяжко, часто були таски на оптимізацію. Що там за великі гроші оракл пропонує кардинально краще?
Залежить від характеру даних. Я був на проекті й MySQL показував кращі результатати, але рішення з ораклом коштувало на порядок більше. Але в оракл тоді можна було помістити значно більше даних, але це було не те, що нам треба. Цікаво, що зараз Google Cloud пропонує AlloyDB (оптимізований Postgres), як альтернативу ораклу. Також цікаво, що ліцензія оракл не дозволяє публікувати бенчмарки й результати
@@AboutProgramming дякую, дали їжу для подумати
AllowDb коштує... треба вважати чи дійсно потрібно чи звичайний Postgres буде достатньо.
Цікавезний матеріал, але чомусь думав що PostgreSQL поле індексу зберігає PK, аналогічно як це Ви показали для MySQL
❤❤❤❤❤❤❤❤❤❤❤❤
Дякую! Все чудово, але... Швидкість вставки - ви робите 20_000 транзакцій (мережевих) порціями по 1_000 товарів. А якщо навпаки - зробити TOTAL_BATCHES = 1_000 , а BATCH_SIZE = 20_000. Гадаю що усі показники часу зменшуватимуться, але їх рейтинг так само збережеться.
Так, гарне зауваження, пакетні вставки зазвичай працюють швидше, бо додаєтсья багато даних в рамках однієї транзакції. Але на практиці, зазвичай вставляється взагалі по 1-2 об'єкти (якщо це просто веб-трафік). Пачками вставляється зазвичай при імпорті даних з файлів.
Цікаво дякую
Підкажіть, , будь ласка:
Covering index важливий тільки для MySQL?
Створення індексу з двух полів корисно лише при використані OFFSET?
При створені індексу з декількох полів, наприкла (year, name), інформація у вториному індексі для year зявиться не лише первиний ключ, а й name, тобто якщо потрібно створити для поля name, то потрібно змінити порядок (name, year,)? Чи індекс (year, name) добавить до вториного індексу name силку на поле year теж?
Буду дуже вдячний за вашу відповідь
1. Covering index важливий я для postgres, але там є альтернатива у вигляді include індексів. Концепетуально дуже схожа ідея - додати в індекс додаткові поля, але тільки в leaf nodes.
2. Відносно OFFSET це більш показово, оскільки треба пройтися по великий кількості рядочків, але й просто виборка великої кількості рядків буде швидша з covering index.
3. Не зовсім зрозумів питання. Але ідея така, що якщо второнний індекс має всі поля, які потрібні при виборці, то немає необходіності ходит в основну таблицю (кластерний індекс) й порядок полів не грає ролі. Порядок полів грає роль тільки при фільтрації й сортуванні - якщо у нас індекс (name, year), то не можна зробити виборку чисто по "year", оскільки в першу чергу все в індексі відсотовано по name, а потів вже по year
Віктор, ще питання - я так розумію в мусклі ви використовували таблиці InnoDB? Бо я так здогадуюсь що у інших таблицб інший типу індексів, так як зустрів вказання що саме інноДБ використовують кластерний індекс, для MyIsam щось такого уточнення не зустрів.
Для перевірки перестворив табличку з даними але іншого типу. InnoDB зайняла 96 кілобайт (64+32), MyISAM 47кілобайт(28+19). Без індексів: InnoDB - 64/0kb, MyISAM - 28/1kb(таки зробив якісь індекси на 1 кілобайт)
Отже получається що різні системи зберігання в MySQL по різному працюють з індексами. Якщо колись у вас буде натхнення то будемо вдячні якщо зробити невеличкий розбір цього питання, як ви зробили в цьому відео. Наперед дякую.
Так, все правильно - все про InnoDB. MyISAM ніколи не використовую оскільки:
1. Не підтримує транзакцій
2. Не підтримує Foreign Keys
3. Не підтримує блокування на рівні рядків, а лочить всю таблицю при записі.
Тобто великого сенсу в MyISAM немає. Раніше він вмів FTS, але вже давно й InnoDB це теж вміє
Тепер я знаю, що у Вас на комп'ютері KDE Plasma))
ех...
**плачу від того що всі PK у всіх таблицях UUID**
😅🙈
Як щодо uuid primary key в mysql використовуючи binary(16) ?
Це можна. Трохи ускладнює написання запитів, бо треба конвертувати hex в binary, але розмір індексів зменшить, але все одно це далеко від оптимального варіанту. Припустимо ми індексуємо поле INT, то у нас 4 байти на INT й 16 байт на BINARY UUID. Тобто 80% даних індексу це primary key. А таких індексів може бути багато. Навіть багато текстових полів мають довжину меншу за 16 байт.
Друга проблема це cache miss при рандомній вставці, що теж може суттєво уповільнити.
Третя це менше записів в блоку й більше блоків й відповідно більше резервування місця під виставку.
Тобто мати auto increment int як primary key й окрему колонку для UUID може бути набагато краще, але якщо таблиця зовсім без індексів й виборка тільки по UUID (типу такий собі key value пошук), то тоді від окремої колонки буде тільки гірше
Виходить, якщо ви ще не знаєте кількості запитів на вставку (чи зміну) даних і запитів на читання - то важко зрозуміти які додаткові індекси створювати?
Скоріше, якщо невідомий характер навантаження, то зайвими індексами можна зробити гірше. Тобто чим більші індексів, тим повільніше вставка. Але навіть при read heavy load теж треба чітко розуміти, які запита робляться до бази, щоб додати потрібні індекси - на потрібні колонки (часом індекси, що включають декілька колонок)
Виходить, що можливий варіант, коли в Postgres швидше не оновлювати рядок, тому що потрібно перебудовувати індекс, а додати новий рядок (тоді індекс перебудовувати не потрібно), тобто зберігати всі стани цього рядка і діставати коли потрібно останній його стан, але тоді збільшується об'єм інформації яку потрібно зберігати. Як вважаєте чи має такий підхід право на існування?
Коли ми додаємо рядок, то всі індекси треба перебудувати інакше пошук по цим даним не буде працювати. В Postgres особливість, що оновлення рядка працює, як вставка нового - тобто всі індекси будуть перебудовані (якщо не брати до уваги HOT оптимізацію). В MySQL при оновленні будуть перебудовані тільки ті індекси, де змінилися дані
@@AboutProgramming Дякую за відповідь і крутий контент!
Я може скіпнув це якось, але коли ви робили запит до products, і шукали по двом полям id, name - це працювало довго, коли id, year - швидко. Притому індекси були створені для усіх ціх окремих стовбців. Різниця в тому що name це строка, а year це число? Чому субд треба перелопатити дані у випадку з id, name, і не треба у випадку з id, year?
В обох випадках ми сортуємо по year й відповідно для швидкого пошуку використовується index по year. Але коли ми вибираємо поля id, year - всі дані є в індексі по year, а коли ми вибираємо id, name - нам не вистачає поля name й mysql спочатку витягує поля, а потім робить offset. Й тут звісно виникає питання, а чи не можна було б спочатку прорахувати offset по індексу year, а потім один раз глянути name в кластерному індексі. Mysql так не робить, але можна зробити руками 2 запити (або написати subquery) й тоді буде працювати швидко
А чому для uuid не використовувати binary, який буде займати 16 байт ? Якщо використовувати ULID або UUID v6 або v7, результати мають вийти зовсім інші.
Можна було в бінарному вигляді, трохи складніше писати й вибирати запити було б в консолі (хоча можна було й через BIN_TO_UUID, який вже є в MySQL 8), але хотілося показати на такому прикладі, який найчастіше зустрічаю й його ефект. Використання бінарного UUUD не сильно змінить ситуацію - скоріше за все більше половини даних буде займати UUID (зараз це 70%). Відносно ULID, то це покращить cache hit при вставках, але решта не зміниться
@@AboutProgramming Створив таблички з PK - int, bigint, uuid (binary 16), uuid_str (varchar 32), uuid_v6 (binary 16). Розміри індексів при 1 млн записів int - 37.56 mb, bigint - 44.58, uuid (binary 16) - 78.64 mb, uuid_str (varchar 32) - 110.97 mb, uuid_v6 (binary 16) - 52.61 mb. Менший розмір має пришвидшити пошук SELECT, бо зменшиться кількість листових сторінок (судячи з параметру n_leaf_pages в таблиці innodb_index_stats). Але дуже цікаво що різниця між UUIDv4 з varchar(32) у 2 рази більша за UUIDv6 binary(16).
@@youknowme9732 О, цікава статистика. Це розмір додаткових індексів чи це кластерний індекс?
@@AboutProgramming кластерні
@@youknowme9732 Ага, зрозумів. Цікавіше навіть було б побачити розмір додаткових індексів. Наприклад по полю price чи name. Там вже не має бути такої різниці в розмірі між uuid (binary 16) та uuid_v6 (binary 16). Окрім того буде краще видно вплив вибору PK , бо в leaf nodes буде лише 4 байта під ціну й 16 байт під PK
Постгрес гарно відправьовує якщо дані не апдейтити?
MySQL й Postgres для певних сценаріїв добре, а для певних гірше. Чи буде підхід Postgres проблемним - можливо, але це залежить від того чи ми апдейтим індексовану колонку й скільки індексів всього нам треба оновити. Також при вставці в базу в будь-якому випадку треба оновити всі індекси в таблиці в обох базах. MySQL по ідеї буде мати швидший read по primary key (оскільки, все знаходиться відразу в кластерному індексі). Тобто в кожному конкретному сценарії може бути по різному. Й різні синтетичні бенчмарки показують різні результати - то Postgres швидший, то MySQL. Також в Postgres є нюанси при роботі AutoVacuum (особливіть реалізації MVCC), MySQL теж робить копію даних для транзацій, але не в основній таблиці, тому періодичний Vacuum не потрібен
апдейт в постгресі фактично це інсерт, та відкладений на потім delete/vacuum. це дає класні плюси в аспектах isolation/locking та durability, але оновлювати маленький флажок, статус або counter в широченній таблиці буде дорого.
Насправді, MVCC не вимагає реалізації такого механізму апдейту. Наприклад, MySQL має всі ті самі гарантії по ізоляції й по durability, але потреби перебудовувати всі індекси немає. Так, дані будуть копіюватися, але зайві індекси оновлюватися не будуть
@@AboutProgramming Цікаві відео, про багато речей не задумавався, тому для розширення кругозору і, часом, очей - класний україномовний контент. Дякую.
GUID як key має переваги в деяких сценаріях шарінгу даних між кількома серверами. Ну і в адресної стрічці показувати INT так собі ідея. Тому, це складне питання.
Так, рішення завжди залежить від задачі. Наприклад, перевага UUID, що його може генерувати клієнт, а не база. Також питання при master-master реплікації. Відносно URL, то я вже відповідав в іншому коментарі, що це не стільки технічне рішення, скільки продуктове. Завжди можна мати додаткове поле для зовнішнього ідентифікатору. Наприклад, для блогу часто додають slug, щоб його використовувати, як ідентифіктор статті. UUID або INT явно гірше з точки зору SEO.
@@AboutProgramming Додавання GUID виключно для URL не дуже допомагає, потім все одно по ньому ж доводиться шукати.
Не дуже зрозумів чому з офсетом так довго шукає. Якщо він знайшов потрібний індекс і йому треба піти у основну таблицю і знайти строку, нащо йому проходити по всьому списку, якщо у індексі вказан primary key і достатньо зробити пошук з кластерним індексом
Пропустив коментар. З офсетом довго оскільки треба робити скан по даним індекса, щоб зміститися на потрібну позицію, оскільки ми не можемо просто зробити стрибок по індексу в даній ситуації на конкретний запис. Це для кейсу з covering index - запит в рази повільніший, але це ще не секунди. Коли ж немає всіх потрібних полів в індексі, то MySQL ще ходить в кластерний індекс й дочитає додаткові поля для кожного рядка, то це вже просто кладе базу на секунди
Хіба MySQL шукає другий раз по кластерному індексу? Наскільки я памʼятаю кластерний індекс ти можеш створити сам, і він не для пошуку елементу по айді
А як MySQL знаходить елементи по айді?
@@AboutProgramming Ну це питання на яке у мене немає відповіді 😂 може він якось по іншому цей індекс називається)
@@AboutProgramming у моїй голові кластерний індекс - це індекс по двом і більше полям. Він може бути тільки один, бо це не окрема "колонка" а це сортування самої таблиці. Може я щось плутаю...
@@muomieg )) Ні, якраз кластерний й називається dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html
Також кажуть кластеризований, але це те саме. Але можливо кластеризований й більш корректно навіть)