OpenNET: стаття - Про грамотному використанні БД MySQL (mysql)

Про грамотному використанні БД MySQL (mysql)
Ключові слова: mysql

, ( знайти схожі документи )
From: Євген В. Жданов < [email protected]. > Date: Mon, 26 Mar 2006 14:31:37 +0000 (UTC) Subject: Про грамотному використанні БД MySQL Оригінал: http://design.protoplex.ru/?showid2=37 Багато моїх друзів і знайомі часто запитують мене про те, як влаштований мій сайт, скільки у мене таблиць в базі даних, як я зберігаю дані і по яких полях веду пошук. Я, звичайно, не видаю всі свої державні таємниці, але завжди розумію причину таких питань і намагаюся допомогти людям побудувати швидку і надійну базу даних - тобто ретельно продумати структуру БД таким чином, щоб при збільшенні навантаження або обсягу таблиць динамічний веб-сайт не перетворився в гальмівне усмерть. А адже багато новачків (веб-будівельники) навіть не здогадуються про те, що великі динамічні сайти гальмують зовсім не через навантаження скриптів на процесор, а в основному через неоптимізованого або дохлий MySQL-сервера. При цьому багато в чому все залежить від того, як влаштована ваша база даних. Отже, почнемо лікнеп. Відразу всім питання: що робить MySQL під час запису в таблиці типу INSERT або UPDATE? Правильно - БЛОКУЄ ТАБЛИЦІ і пише в них дані. Швидкість запису і пошуку може бути досить низькою, тому статус таблиць забороняє іншим процесам зчитувати з них дані до закінчення операції запису або поновлення і зняття блокування. При цьому може вийти так, що під час запису єдиного поля в довгі таблиці, ваш MySQL-сервер надовго заблокує доступ до таблиці іншим скриптам. Наприклад, ви створили таблицю новин такого типу: ID - номер, первинний ключ TEMA - тема новини MESS - повідомлення, сама новина VIEWS - кількість переглядів При кожному зверненні до новин, скрипт виводитиме саму новину, а потім збільшувати поле VIEWS запитом UPDATE table ' NEWS 'set VIEWS = VIEWS + 1 where id = ID. При цьому кількість апдейтів буде досить високим. При високій відвідуваності веб-ресурсу або при "нападі" на сайт пошукового робота (ці хлопці страждають багатопоточність і можуть запросто повісити ваш сайт своїми запитами) декілька одночасних процесів стануть намагатися зробити UPDATE і SELECT. При кожному UPDATE таблиця буде блокуватися (на це йде час) і всі інші процеси будуть чекати завершення операції. А якщо таблиця досить велика? Наприклад, кілька тисяч записів. Їжаку зрозуміло, що побудується чергу з декількох десятків скриптів, які очікують відповіді MySQL-сервера. Кожен буде жерти пам'ять і тримати інші процеси. У підсумку все у вас зависне і переглючіт. Вихід: робити замість однієї таблиці кілька. Раджу розділяти поля по типу їх використання. Одну таблицю - тільки для виведення і рідкісних оновлень або вставок. Іншу - для частих оновлень, але рідкісного виведення. Наприклад, значення лічильника звернень тримати окремо в таблицю виду: ID - номер, первинний ключ VIEWS - кількість переглядів Самі новини краще тримати в іншій таблиці, де немає поля VIEWS. При цьому таблиця з новинами буде важкою (багато тексту, полів, індексів), а таблиця COUNT (лічильник) буде дуже легкою і швидкою. Таблиця NEWS буде кешироваться і виводитися дуже швидко при будь-яких обсягах, а таблиця COUNT буде швидко оновлюватися через те, що вона дуже легка (всього два цілочисельних поля). Поділ даних по декількох таблицях істотно прискорює роботу MySQL-сервера. Набагато швидше працюють кілька дрібних запитів по кожній таблиці, ніж один довгий запит по одній або кількох таблиць. Майте це на увазі, щоб спати спокійно. Далі - крутіше. Щоб не блокувати зайвий раз свої таблиці використовуйте при вставках директиву DELAYED. Приклад: INSERT DELAYED into STAT (ID, IP, UTIME) values ​​(null, $ ip, NOW ()). Він дозволяє серверу відгалужувати потік в режимі очікування, а саму вставку виробляти тоді, коли сервер звільниться від інших запитів або надійде наступний аналогічний INSERT DELAYED. Зазвичай відкладений метод підходить для будь-яких операцій з кумулятивними таблицями (коли в основному йдуть INSERTи, а дані накопичуються, а не модифікуються), при яких не особливо важливо коли саме подіють зміни - миттєво або через кілька секунд, хвилин. Наприклад, якщо хочете збирати IP-адреси своїх відвідувачів, урли, за якими вони ходять або сторінки, звідки прийшли, час. При додаванні з затримкою скрипт відпрацює майже миттєво, ще до виконання операції. Операція UPDATE йде в три етапи: пошук того, що будете змінювати, потім запис даних, оновлення індексів. При цьому, чим більше таблиця, тим довше пошук. Якщо є індекси, то операція кешируєтся і виконується досить швидко. Але сам процес дуже ємний. І тільки дурень не наздожене, що велика таблиця з безліччю індексів і записів, буде гальмувати при UPDATE. INSERT ж виконується одним залпом, дуже швидко. Тому зазвичай використовують адитивні записи (вставками INSERT) в тимчасові таблиці, потім блокують основні Таліци, підсумовують оновлення, і плюють їх в основну таблицю. Виходить, що в основному, головні таблиці працюють тільки в режимі виведення, а оновлення йдуть набагато рідше і швидше. Наприклад, можна збирати дані про завантаження новин в тимчасовій таблиці, а по крону або іншим чином оновлювати лічильник кожні 10 хвилин (або рідше). Це прискорить роботу сервера. При запитах SELECT * FROM таблиця скрипт отримає все поля даної таблиці. А чи потрібно це? Використання * веде до зайвої витрати ресурсів. Набагато ефективніше використовувати точні назви полів, які потрібні скрипту. Наприклад: SELECT id, name FROM таблиця. При такому запиті передача займе менше часу і знадобиться менше ресурсів. Намагайтеся обмежувати висновок за допомогою директиви LIMIT. Це також прискорює виведення. Пошук по БД йде швидше якщо замість LIKE '% слово%', ставити 'слово%'. Операції з шаблонами регулярних виразів кешуються тільки в тому випадку, якщо на початку відсутній символ%. Тому при побудові пошукових запитів з LIKE уникайте початківців символів%. При побудові таблиць для найбільш використовуваних полів (при пошуку, сортування і т.д.) обов'язково створюйте індекси. Без індексів таблиці будуть сильно гальмувати. Індекси служать для кешування і дозволяють істотно прискорити виведення даних з таблиць. При цьому таблиці будуть займати більше місця на диску і в пам'яті. Але це в наш час не проблема. Використовуйте належне тип полів для своїх записів. Тип TINYINT займає 1 байт - найшвидший. Таблиці з MEDIUMINT швидше таблиць з INT. Якщо ставити полях властивість NOT NULL, то в цілому їх робота буде швидше. VARCHAR повільніше CHAR, тому таблиці змінної довжини (де є тип VARCHAR або TEXT) займають менше дискового простору, але працюють повільніше. З власного досвіду скажу, що для більшості сайтів підходять викладені поради по роботі з MySQL. Щоб ще більше прискорити свій сервер, раджу частоїспользуємиє операції проводити по крону виділеними процесами і писати дані в різні файли. Наприклад, раз в 20 хвилин запускати скрипт, який буде створювати файл з новинами. Або наприклад, генерувати файл з новинами при їх поповнення або оновлення. Таким чином, ви економите на кожному зверненні до БД. Інтерактивність при цьому не втрачається, а продуктивність збільшується в багато разів. Особливо, повторюю, при високій відвідуваності ресурсу. Намагайтеся відокремити інтерактивні операції від фонових. Наприклад, на ПротоПлексе працює один інтерактивний движок, але в тлі за завданнями працюють з десяток різних роботів, які генерують часто викликаються сторінки, розсилають листи і т.д. Великий сайт - це не тільки те, що ви бачите, але і бек-енд (зворотна сторона). У фоновому режимі можна швидко і ефективно готувати контент, звільняючи основний двигун від зайвої роботи. Загалом, основи повинні бути всім зрозумілі. Дробіть все на дрібниці, будь то запити, таблиці або операції. Структура БД повинна бути такою, щоб не виконувалося нічого зайвого. Регулярно проводите OPTIMIZE на таблицях зі змінною довжиною, особливо, якщо в них йдуть видалення записів. Тестируйте свої запити на швидкість, спрощуйте їх. Ну а я вже втомився це писати :) Якщо будуть питання, пишіть, відповім.

Обговорення [ RSS ]

• 2 , Олексій (??), 13:59 22/09/2011 [ відповісти ]

+

/ - "Операція UPDATE йде в три етапи: пошук того, що будете змінювати, потім
запис даних, оновлення індексів. При цьому, чим більше таблиця, тим
довше пошук. Якщо є індекси, то операція кешируєтся і виконується
достатньо швидко. Але сам процес дуже ємний. І тільки дурень не
наздожене, що велика таблиця з безліччю індексів і записів, буде
гальмувати при UPDATE. INSERT ж виконується одним залпом, дуже
швидко. "

Вото з цим блоком не можу погодиться.
Хм ... а при Insert що, індекс не оновлюється? Це щось нове. В тому то і справа, що все як-раз навпаки працює в описаній ситуації. У такій таблиці є індекс по id (може бути і по інших полях) а поле-лічильник природно ніхто не індексує і при insert спочатку СУБД треба записати дані, а потім ще і як мінімум оновити індекс по id. Коли ж іде update, то відбувається пошук за індексом id (це досить швидко) і інкремент поля-лічильника, індекс не чіпати, тому що поле-лічильник в ньому не присутній.
Ще хотів сказати, що варто для таблиць, де накручується лічильник переглядів, використовувати тип InnoBD. Тоді не буде блокувань таблиці, блокуватися будуть тільки оновлювані записи і це, не дивлячись на більш довгу роботу select в таких таблицях, дасть загальний виграш в роботі з ними.

Додати коментар

Спонсори:

Хостинг: