MySQL є другою за популярністю системою керування реляційними базами даних з відкритим кодом у світі. Його використовують у всьому світі завдяки незмінно високій продуктивності, високій надійності та простоті використання. У цій статті представлені деякі з найкращих практик MySQL.

З ними ви можете потренуватися в SQL і поставити собі деякі вправи SQL, дізнатися про ліміт SQL і практику SQL онлайн, а також про найпоширеніші практичні проблеми SQL.

Це допоможе вам зрозуміти номер рядка SQL, верхню частину SQL, зсув обмеження MySQL і онлайн-практику SQL.

Якщо ви шукаєте гарний і простий спосіб відобразити велику кількість рядків і стовпців для відвідувачів вашого веб-сайту WordPress за допомогою барвистих, інформативних і адаптивних інтерактивних таблиць і діаграм, ви можете використовувати його з плагіном wpDataTables, перевірте ціни та всі функції, які він надає тут.

1 Завжди використовуйте правильний тип даних

Однією з найважливіших найкращих практик MySQL є використання типів даних на основі природи даних. Використання невідповідних типів даних може зайняти більше місця або призвести до помилок.

Наприклад: використання типу varchar (20) замість типу даних DATETIME для зберігання значень дати й часу призведе до помилок у обчисленнях, пов’язаних із датою та часом. Крім того, можливо, будуть збережені недійсні дані.

2 Використовуйте CHAR (1) замість VARCHAR (1)

VARCHAR (1) займає додаткові байти для зберігання інформації, тому, якщо ви вводите один символ, краще використовувати CHAR (1).

3 Використовуйте тип даних CHAR, щоб зберігати лише дані фіксованої довжини

Наприклад: якщо довжина даних менша за 1000, використання char (1000) замість varchar (1000) займатиме більше місця.

4 Уникайте використання регіональних форматів дати

Використовуючи тип даних DATETIME або DATE, завжди використовуйте формат дати РРРР-ММ-ДД або формат дати ISO, який підходить для вашої SQL Engine. Такі регіональні формати, як ДД-ММ-РРРР або ММ-ДД-РРРР, не зберігатимуться належним чином.

5 Ключові стовпці покажчика

Бажано, щоб запит швидко повертав результат, тому обов’язково індексуйте стовпці, які використовуються в реченнях JOIN.

Якщо ви використовуєте інструкцію UPDATE, що включає більше однієї таблиці, переконайтеся, що ви проіндексували всі стовпці, які використовуються для об’єднання таблиць.

6 Не використовуйте функції над індексованими стовпцями…

… тому що тоді індекс втрачає свою мету.

Наприклад, припустімо, що ви хочете отримати дані, де перші два символи коду клієнта є AK. Напишіть:

SELECT columns FROM table WHERE customer_code like 'AK%'

і не пишуть

SELECT columns FROM table WHERE left (customer_code,2)=’AK’

чому У першому прикладі буде використано індекс, що призведе до швидшого часу відповіді.

**7 Використовуйте SQL SELECT * лише за потреби**

Дотримуючись списку найкращих практик MySQL, не просто сліпо використовуйте SELECT * у коді. Якщо в таблиці багато стовпців, буде повернуто всі. Це сповільнить час відповіді, особливо якщо ви надішлете результат у зовнішню програму.

Замість цього явно введіть назви стовпців, які насправді потрібні.

Примітка: пам’ятайте, що всі оператори SELECT вимагають пропозиції WHERE.

8 Використовуйте речення ORDER BY лише за потреби

Якщо ви хочете показати результат у зовнішній програмі, дозвольте їй ЗАМОВИТИ набір результатів. Якщо ви зробите це в SQL, час відповіді може сповільнитися в багатокористувацькому середовищі.

9 Виберіть відповідну систему обробки баз даних

Якщо ви розробляєте програму, яка читає дані частіше, ніж записує (наприклад, пошукову систему), виберіть систему зберігання MyISAM.

Вибір неправильного механізму зберігання вплине на продуктивність.

10 Використовуйте речення EXISTS, де це необхідно

Для перевірки наявності даних використовуйте пропозицію EXISTS, яка має швидший час відповіді. Наприклад, використовуйте:

If EXISTS(SELECT * from Table WHERE col=’some value’)

Не використовувати :

If (SELECT count(*) from Table WHERE col=’some value’)>0

11 ПОЯСНІТЬ ваші запити SELECT

Якщо ви використовуєте ключове слово EXPLAIN, ви можете отримати уявлення про те, що MySQL робить для виконання вашого запиту. Це може допомогти вам виявити проблеми з вашим запитом або структурою таблиці (наприклад, вузькі місця).

Запит EXPLAIN показує вам, які індекси використовуються, як таблиця сканується, сортується тощо.

Все, що вам потрібно зробити, це додати ключове слово EXPLAIN перед запитом SELECT (бажано складним із об’єднаннями). Крім того, якщо ви використовуєте для цього phpmyadmin, ваші результати будуть показані у гарній таблиці.

12 Використовуйте LIMIT 1, щоб отримати унікальний рядок

Іноді ви заздалегідь знаєте, що шукаєте лише один рядок під час запиту до таблиць. Наприклад, ви можете отримувати унікальний запис або просто перевіряти існування будь-якої кількості записів, які задовольняють вашу умову WHERE.

У таких випадках ви захочете використовувати функцію обмеження MySQL, щоб збільшити продуктивність. Ось ще одна найкраща практика MySQL: просто додайте LIMIT 1 до свого запиту. Таким чином механізму бази даних не доведеться переглядати всю таблицю чи індекс. Він припинить сканування, коли знайде лише 1 запис того, що ви шукаєте.

// do I have anyusersfrom Alabama?

// чого НЕ робити:
$r = mysql_query(“SELECT * FROM user WHERE state = ‘Alabama’");
if (mysql_num_rows($r) > 0) {
// …
}

// набагато краще:
$r = mysql_query(“SELECT 1 FROM user WHERE state = ‘Alabama’ LIMIT 1");
if (mysql_num_rows($r) > 0) {
// …
}

13 Індексуйте та використовуйте ті самі типи стовпців для об’єднань

Ще одна важлива порада щодо найкращих практик MySQL – якщо ваша програма має багато запитів JOIN, переконайтеся, що стовпці, за якими ви об’єднуєте, індексуються в обох таблицях. Це впливає на внутрішню оптимізацію операції приєднання MySQL.

Крім того, з’єднані стовпці мають бути одного типу. Наприклад, якщо ви приєднуєте стовпець DECIMAL до стовпця INT з іншої таблиці, MySQL не зможе використовувати жоден з індексів. Навіть кодування символів має бути одного типу для стовпців рядкового типу.

// looking for companies in my state $r = mysql_query("SELECT company_name FROM users LEFT JOIN companies ON (users.state = companies.state) WHERE users.id = $user_id");

// обидва стовпці стану мають бути проіндексовані
// і вони обидва мають мати однаковий тип і кодування символів
// або MySQL може виконати повне сканування таблиці

14 Приховайте MySQL від Інтернету

Досвідчені адміністратори баз даних і співробітники служби безпеки це знають – ніколи не розміщуйте базу даних у кореневому каталозі веб-сервера.

Для веб-додатків MySQL слід приховати за брандмауером. Зв’язок має бути активований лише між серверами програм і вашими веб-серверами.

Іншим варіантом є використання MySQL skip-networking. Коли його ввімкнено, MySQL прослуховує лише локальні з’єднання через сокет і ігнорує всі порти TCP.

15 Використовуйте найменші можливі типи даних

Дозволь мені розповісти тобі історію. Коли я навчався в коледжі, філософія полягала в тому, що «пам’яті мало». Це були дні жорстких дисків на 256 МБ. У наш час, здається, нікого навіть на йоту не хвилює пам’ять чи простір на жорсткому диску. Нова філософія полягає в тому, що «пам’ять коштує дешево». Це може бути правдою в доларах, але читання великих типів даних все одно займає більше часу, ніж читання менших. Великі типи даних вимагають більше секторів диска для читання в пам’ять.

Мораль така: ігноруйте спокусу негайно перейти до найбільшого типу даних під час розробки таблиць. Подумайте про використання int замість bigint.

Також уникайте текстових полів largechar (255), коли достатньо varchar або меншого char.

Якщо ви використовуєте правильний тип даних, більше записів поміститься в пам’ять або блок індексних ключів. Це призводить до меншої кількості читань і швидшої роботи.

16 Скористайтеся перевагами кешування запитів

Кешування запитів є одним із найефективніших методів підвищення продуктивності. У більшості серверів MySQL він увімкнено за замовчуванням.

Кеш запитів зберігає текст оператора SELECT разом із відповідним набором результатів. Якщо пізніше сервер отримає ідентичний оператор, він отримає результати з кешу запитів, а не аналізуватиме та виконає оператор знову. Кеш запитів є спільним для сеансів, тому набір результатів, створений одним клієнтом, може бути надісланий у відповідь на той самий запит, виданий іншим клієнтом.

Однак, як би це не було чудово, кешування запитів має свої обмеження. Візьміть таке твердження:

Проблема полягає в тому, що запити містять певні недетерміновані функції, наприклад NOW() і RAND(). MySQL не може обчислити такі функції заздалегідь, тому вони не кешуються.

На щастя, для цього є просте рішення: ви можете зберегти результати функції у змінній.

17 Не редагуйте файли дампа

Файли дампа дуже оманливі та можуть призвести до пошкодження. чому Якщо ви коли-небудь бачили файли дампа, створені mysqldump, ви погодитеся, що вони виглядають як звичайні, нешкідливі текстові файли. Тому більшість людей редагують їх у стандартному текстовому редакторі, що призводить до появи пошкоджень.

Якщо ви коли-небудь пробували редагувати файли дампа, ви швидко зрозуміли, що це все, що завгодно, але не текстовий файл. Отже, єдиний гарантований спосіб уникнути проблем – це залишити файли дампа в спокої.

18 Використовуйте налаштування розміру блоку MyISAM

Налаштування розмірів блоків в індексах таблиць MyISAM є одним із найкращих методів MySQL. Його можна знайти у файлах .MYI у буфері ключів, а також на диску. Налаштування виглядає так: myisam_block_size.

Він має значення за замовчуванням 1k. Це досить мало, щоб бути оптимальним у сучасній системі. Більшість файлових систем використовують блоки більшого розміру. І ми знаємо, що написання одного блоку індексу вимагає читання, а потім запису. Операційній системі ніколи не доведеться записувати на базовий диск, якщо розмір блоку дорівнює або перевищує розмір блоку файлової системи.

19 Увімкніть delay_key_write

Delay_key_write вимкнено за замовчуванням. На те є причина. Якщо у вас стався збій посеред проекту, ваша база даних може бути пошкоджена.

Отже, чому ви хочете його ввімкнути? Причина проста. Оскільки delay_key_write гарантує, що база даних не буде скидати файл ключа MyISAM після кожного запису. Тому, якщо найближчим часом ви знову будете писати, ви заощадите досить багато часу.

Ось ще одна цікава порада, яку ми вибрали з найкращих практик MySQL: увімкнення delay_key_write відрізняється для кожної версії. Щоб дізнатися, як увімкнути його в певній версії, зверніться до офіційного посібника сайту MySQL.

20 Використовуйте Stack Trace, щоб ізолювати помилки

Дотримуючись цих найкращих практик MySQL, цю пораду запозичено з Sky SQL, оскільки вона надто проста та надто зручна, щоб її не згадати.

MySQL stack_trace можна використовувати для ізоляції різних помилок. Замість того, щоб копатися у всіх можливих варіантах використання, програміст може захотіти звернути увагу на те, як легко нульовий покажчик може зіпсувати ваш код.

З цією порадою щодо Sky SQL виявляти, відстежувати та виправляти стає набагато легше.

21 MySQL змінює пароль ROOT

Зміна пароля ROOT може здатися простою, але знати, як це зробити, однаково важливо як для вашої домашньої операційної системи, так і для серверів MySQL.

Іноді буває так, що завзяті новачки і розсіяні досвідчені користувачі не можуть зрозуміти, чому вони не можуть змінити певні налаштування, і повертають помилки. Це може бути так само просто, як надання доступу ROOT. Користувачеві навіть не потрібно шукати це в Google.

Ось як налаштувати пароль ROOT і як змінити пароль користувача ROOT:

//Straightforward MySQL 101 $mysqladmin -u rootpassword [Type in selectedpassword] //Changingusers ROOT password $mysqladmin -u root -p [type oldpassword] newpass [hit enter and type new password. Pressenter] //Use mysqlsqlcommand $mysql -u root -p //prompt "mysql>" pops up. Enter: $use MySQL; //Enter usernameyouwant to change the password for $update user set password=PASSWORD (Type new PasswordHere) where User = 'username'; //Don'tforget the previoussemicolon, nowreload the settings for the user'sprivileges $flush privileges; $quit

22 Виправте файли конфігурації

MySQL Tuner — це сценарій Perl, який може якимось чином оптимізувати вашу продуктивність, пропонуючи зміни до файлів конфігурації.

Якщо деякі поради та підказки в MySQL надзвичайно зручні, такі інструменти, як MySQL Tuner, є знахідкою, яка заслуговує на те, щоб бути в окремій категорії.

Це те, чим можуть користуватися як новачки, так і професіонали. MySQL Tuner не є спеціальною підказкою для MySQL, тому існує безліч налаштувань і модифікацій, які можна застосувати. Чим більше ви використовуєте його, тим більше налаштувань ви можете навчитися застосовувати для власного використання.

Спочатку це може здатися страшним, але саме тому ви можете знайти посібник –man db, а також усі примітки, необхідну інформацію та налаштування на офіційній домашній сторінці проекту. При цьому MySQL Tuner має швидко стати вашим основним інструментом для прискорення роботи MySQL і тестування файлів конфігурації.

Кінець думками про найкращі практики MySQL

У світі інформатики MySQL, безсумнівно, є однією з найважливіших і найвпливовіших програм, які коли-небудь з’являлися. Він настільки захоплюючий і універсальний, що саме тоді, коли здається, що всі можливі хитрощі та ярлики вже виявлено, хтось інший включається з новим способом використання нового аргументу чи налаштування.

Ми окреслили лише найкращі практики MySQL, які повинні знати всі. Але чим більше ви використовуєте MySQL, тим більше ви будете використовувати їх. Ви можете практикувати SQL онлайн та складати собі тести SQL. Потім ви розробите свої власні хитрощі та знайдете свої власні головні поради, на які можна покластися, наприклад обмеження SQL, номер рядка SQL, і зрозумієте, чому вам взагалі варто вибрати MySQL.

Якщо вам сподобалося читати цю статтю про найкращі практики MySQL, ви також повинні прочитати ці:

Джерело запису: wpdatatables.com