Топ 20 препоръки за MySQL

Базата данни MySQL се превърна в най -популярната релационна база данни с отворен код в света поради своята висока производителност, последователност, висока надеждност и лекота на използване. Тези предимства, които ни предлага, обаче често се влияят от начина, по който работим по него.

В този урок ще научите поредица от съвети, които ще бъдат много полезни и ще ни позволят да извлечем максимума както от гледна точка на програмиста, така и на администратора на базата данни.

Какъв инструмент ще използваме?Командната конзола на MySQL '] MySQL има програма, наречена със същото име като базата данни (mysql), която се използва за управление на базата данни чрез командния ред.

WindowsТой се намира в директория като:

C: \ Program Files \ MySQL \ MySQL Server 5.6 \ bin

Директорията може да варира, например, може да се намира в корена на диска C: или на всяко друго място, където може да сме инсталирали MySQL. За достъп до конзолата MySQL в Windows ще трябва да се намираме в тази директория.

LinuxВижте следната връзка:

Достъп до MySQL от Linux

MacКомпютри със система Mac OS X те имат вграден терминал на командния ред сред наличните приложения. За достъп се използва същата команда като в Linux.

phpMyAdminТова е безплатен софтуерен инструмент, написан на PHP, който се използва за администриране на MySQL чрез Интернет. Ако имате инсталирана локална среда за разработка, като XAMPP или WAMP, този инструмент вече ще бъде инсталиран. Ако имате хостинг план с административен панел, повечето от тях предлагат този инструмент като администратор на MySQL база данни.

[color = rgb (169,169,169)] phpMyAdmin от XAMPP [/ color]

Увеличете

[color = # a9a9a9] phpMyAdmin от CPanel [/ color]

Като имаме под ръка тези 2 инструмента, можем да започнем да тестваме всички тези добри практики, които показваме по -долу.

Това са Топ 20 на най -добрите практики на MySQL:

1. Конвенция за наименуване

Общи правилаИмайте предвид следните правила, за да избегнете проблеми.

• Използвайте малки букви, тъй като ви помага при скоростта на въвеждане, ще избегнете грешки в случай на чувствителни към малки и големи букви и т.н.

• Не използвайте интервали, вместо това използвайте долната черта (_).

• Не използвайте числа в имената, само английски букви.

• Използвайте валидни разбираеми имена.

• Имената трябва да се обясняват сами.

• Имената не трябва да съдържат повече от 64 знака.

• Избягвайте използването на префикси.

Правила за имена на бази данниСледвайте всички общи правила по -горе.

• Името може да бъде както в единствено, така и в множествено число, но базата данни представлява база данни, така че тя трябва да бъде единична, доколкото е възможно.

• Избягвайте префиксите, когато е възможно.

Правила за имена на таблициИзползвайте малки букви за имена на таблици: MySQL обикновено се хоства на сървъри на Linux, той е чувствителен към малки и големи букви, така че най -добрата практика е да се използват малки имена на таблици.

• Имената на таблиците трябва да са в единствено число: таблицата е единична единица, както и моделът, така че е странно името на таблицата да е в множествено число.

• Префикси в името на таблицата: Виждали сме много пъти, че таблиците са с префикс с името на базата данни или с името на проекта. Понякога това се налага, когато в нашия проект имаме много бази данни, за да преодолеем ограниченията на някои хостинг доставчици. Но ако не е необходимо и нашият проект е малък, избягвайте използването на префикси.

Име на полетатаИзползвайте всички горни правила, тоест използвайте малки букви, не използвайте празни интервали, не използвайте числа и избягвайте префикси.

• Използвайте една или две кратки думи, когато е възможно.

• Имената на полетата трябва да могат да бъдат разбрани, например: цена, име на фирма и т.н.

• Име на първична колона: Първичният ключ може да има идентификатор или име на таблица _id. Това ще зависи от избора.

• Избягвайте да използвате запазени думи в полетата: *****, дата и т.н. В тези случаи е за предпочитане да се използват префикси като record_date и т.н.

• Избягвайте да използвате имена на колони със същото име на таблица. Това може да причини объркване при писането на вашите заявки.

• Избягвайте имена в съкратени или съчетани акроними.

2. Винаги използвайте правилния тип данни

ПримерИзползването на varchar (20) за съхраняване на стойности за дата и час вместо DATETIME може да причини грешки по време на изчисленията на времето, свързани с датата, а също така е възможно в случай на невалидно съхранение на данни.

3. Използвайте CHAR (1) над VARCHAR (1)

4. CHAR за фиксирани данни

Пример:

Използването на CHAR (1000) вместо VARCHAR (1000) изразходва повече място, ако дължината на данните е по -малка от 1000.

5. Избягвайте да използвате регионални формати за дата

• ВРЕМЕ ЗА СРЕЩА
• ДАТА

Винаги използвайте YYYY-MM-DD формат или ISO формат за дата, който отговаря на вашия SQL механизъм. Други регионални формати като DD-MM-YYY, DD-MM-YYYY няма да се съхраняват правилно.

6. Оптимизирайте заявките си за кеша

 // Кешът НЕ работи $ r = mysql_query ("SELECT name FROM users WHERE record> = CURDATE ()"); 

 // Кешът РАБОТИ ли $ днес = дата ("Y-m-d"); $ r = mysql_query ("SELECT name FROM users WHERE record> = '$ today'"); 

7. Избягвайте да използвате „SELECT *“ във вашите заявки

Това е добър навик посочете необходимите колони във вашия SELECT израз.

8. Използвайте LIMIT 1, когато искате само един ред

В тези случаи добавянето на LIMIT 1 към заявката ви може значително да подобри скоростта. По този начин базата данни ще спре да сканира резултатите в момента, в който намери такава, вместо да премине през цялата таблица или индекс.

 // Имам потребители от Мадрид? // какво НЕ трябва да се прави: $ r = mysql_query ("SELECT * FROM user WHERE city = 'Madrid" "); if (mysql_num_rows ($ r)> 0) {//…} // много по -добре: $ r = mysql_query ("SELECT 1 FROM user WHERE city = 'Madrid' LIMIT 1"); if (mysql_num_rows ($ r)> 0) {//…}

9. Използване на ***** BY

Не злоупотребявайте с употребата му.

10. Изберете подходящ основен двигател

Ако разработите приложение, което записва данни по -често от четене
(например: банкиране в реално време), изберете двигателя за съхранение InnoDB.

Изборът на грешен механизъм за съхранение ще повлияе на ефективността на вашите заявки.

11. Използвайте клаузата EXISTS, когато е необходимо

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

 Ако съществува (SELECT * от таблица WHERE col = 'някаква стойност')

12. Използвайте EXPLAIN във вашите SELECT заявки

Резултатът от заявка EXPLAIN ще ви покаже използваните индекси, как се изследва таблицата, как се подрежда и т.н.

Изберете заявка SELECT (за предпочитане сложна, с съединения) и добавете думата EXPLAIN в началото на всичко. Той ще върне резултатите в проста таблица. Да предположим например, че съм забравил да индексирам колона, тя ще ни покаже следния екран:

Увеличете

След като добавите индекса към таблицата на състоянието, той ще изглежда така:

Увеличете

13. Index и използвайте същия тип колона за Joins

Също така колоните, към които се присъединявате, трябва да са от същия тип. Например, ако се присъединявате към колона от тип DECIMAL с колона от тип INT от друга таблица, MySQL няма да може да използва поне един от двата индекса. Дори кодирането на знаци трябва да бъде от същия тип за колоните на Тип низ.

 // търся компании в моя град $ r = mysql_query ("SELECT company_name FROM users LEFT JOIN companyias ON (users.city = companyias.city) WHERE users.id = $ user_id");

14. Използвайте NOT NULL, ако можете

Преди всичко се запитайте дали няма да има разлика между празен низ и стойност NULL (или за INT полета: 0 спрямо NULL). Ако няма проблем между двете стойности, нямате нужда от поле NULL. Колоните NULL изискват допълнително пространство и могат да добавят сложност към вашите изявления за сравнение. Просто ги избягвайте, когато можете.

Във всеки случай разбираме, че в някои много специфични случаи има причина да се използват NULL колони, което не винаги е лошо.

15. Таблиците с фиксиран размер (статични) са по -бързи

Някои примери за типове колони, които НЕ са с фиксиран размер, са:

• ВАРЧАР
• ТЕКСТ
• BLOB

Ако включите само един от тези типове колони, таблицата вече няма да е с фиксиран размер и ще трябва да се третира по различен начин от MySQL двигател.

Таблиците с фиксиран размер могат да увеличат производителността, защото по-бързо за двигателя на MySQL да търси във вашите записи. Когато искате да прочетете определен ред в таблицата, можете бързо да изчислите позицията му. Ако размерът на реда не е фиксиран, всеки път, когато трябва да търсите, първо трябва да попитате индекса на първичния ключ.

Те също са по -лесни за търсене и възстановяване след инцидент. Но от друга страна те също биха могли да заемат повече място.

16. Вертикално разделяне

Пример 1:

Със сигурност ще имате потребителска таблица, която съдържа пощенски адрес, който не се използва много често. Тук можете да разделите таблицата и да съхранявате адресите в отделна таблица. По този начин основната ви потребителска таблица ще има по -малък размер. Както знаете, колкото по -малки, толкова по -бързи са масите.

Пример 2:

Имате поле "last_access" в таблицата си. Актуализира се всеки път, когато потребител влезе в страницата ви. Но всеки достъп причинява кеша на заявката за освобождаване на тази таблица. Това, което можете да направите, е да поставите това поле в друга таблица, така че промените във вашата потребителска таблица да бъдат сведени до минимум.

Но също така трябва да се уверите, че не е нужно постоянно да се присъединявате към двете таблици след разделянето, в противен случай ще претърпите спад на производителността, точно обратното на това, което търсехме.

17. Съхранява IP адресите като UNSIGNED INT

Но трябва да сте сигурни, че колоната е UNSIGNED INT (беззнаково цяло число), защото IP адресите използват целия 32-битов диапазон без знаци.

Във вашите заявки можете да използвате INET_ATON () функция да конвертирате IP адрес в цяло число и INET_NTOA () да направите обратното. В PHP има и подобни функции, наречени ip2long () Y long2ip ().

18. Създайте изгледи, за да опростите обичайната употреба в таблици

Може да се използва и за филтриране на неиндексирани колони, оставяйки само полетата, които се показват по-бързо в търсенето.

19. Не използвайте ***** BY RAND ()

Ако наистина имате нужда от произволни таблици за вашия резултат, има много по -добри начини да го направите. Ясно е, че те ще заемат повече код, но вие ще предотвратите възможно затруднение, което се увеличава експоненциално с нарастването на съдържанието ви.

Проблемът е, че MySQL ще трябва да работи RAND () (което изисква процесорна мощност) за всеки от редовете, преди да ги сортира и да върне един ред.

 // начинът да НЕ го правим: $ r = mysql_query ("SELECT username FROM user ***** BY RAND () LIMIT 1"); // много по -добре: $ r = mysql_query ("SELECT count (*) FROM user"); $ d = mysql_fetch_row ($ r); $ rand = mt_rand (0, $ d [0] - 1); $ r = mysql_query ("SELECT username FROM username LIMIT $ rand, 1");

20. Оптимизирайте клаузата WHERE

• Премахнете ненужните скоби. Например:

 От до5 И b = c И a = 5

 SELECT COUNT (*) FROM от таблицата.