20 najlepszych rekomendacji dla MySQL

Baza danych MySQL stała się najpopularniejszą relacyjną bazą danych typu open source na świecie ze względu na jej wysoką wydajność, spójność, wysoką niezawodność i łatwość użytkowania. Jednak na te korzyści, które nam oferuje, często wpływa sposób, w jaki nad tym pracujemy.

W tym samouczku poznasz szereg porad, które będą bardzo przydatne i pozwolą nam wydobyć jak najwięcej z punktu widzenia programisty i administratora bazy danych.

Jakiego narzędzia użyjemy?Konsola poleceń MySQL '] MySQL posiada program o tej samej nazwie co baza danych (mysql), który służy do zarządzania bazą danych z wiersza poleceń.

OknaZnajduje się w katalogu takim jak:

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

Katalog może się różnić, na przykład może znajdować się w katalogu głównym dysku C: lub w dowolnym innym miejscu, w którym zainstalowaliśmy MySQL. Aby uzyskać dostęp do konsoli MySQL w systemie Windows, będziemy musieli znajdować się w tym katalogu.

LinuxZobacz poniższy link:

Uzyskaj dostęp do MySQL z Linuksa

ProchowiecKomputery z systemem Mac OS X mają wbudowany terminal wiersza poleceń wśród dostępnych aplikacji. Aby uzyskać dostęp do tego samego polecenia jest używane jak w Linuksie.

phpMyAdminJest to darmowe narzędzie programowe napisane w PHP, które służy do administrowania MySQL przez Internet. Jeśli masz zainstalowane lokalne środowisko programistyczne, takie jak XAMPP lub WAMP, to narzędzie zostanie już zainstalowane. Jeśli masz plan hostingowy z panelem administracyjnym, większość z nich oferuje to narzędzie jako administrator bazy danych MySQL.

[kolor = rgb (169,169,169)] phpMyAdmin z XAMPP [/ kolor]

POWIĘKSZAĆ

[kolor = # a9a9a9] phpMyAdmin z CPanel [/ kolor]

Mając te 2 narzędzia pod ręką, możemy zacząć testować wszystkie dobre praktyki, które pokazujemy poniżej.

To są 20 najlepszych praktyk MySQL:

1. Konwencja nazewnictwa

Główne zasadyPamiętaj o poniższych zasadach, aby uniknąć problemów.

• Używaj małych liter, ponieważ pomaga to w szybkości pisania, pozwoli to uniknąć błędów w przypadku funkcji wrażliwych na małe i wielkie litery itp.

• Nie używaj spacji, zamiast tego użyj podkreślenia (_).

• Nie używaj cyfr w nazwach, tylko angielskie litery alfabetu.

• Używaj prawidłowych, zrozumiałych nazw.

• Nazwy powinny być zrozumiałe.

• Nazwy nie mogą zawierać więcej niż 64 znaki.

• Unikaj używania przedrostków.

Zasady dotyczące nazw baz danychPrzestrzegaj wszystkich powyższych ogólnych zasad.

• Nazwa może być zarówno w liczbie pojedynczej, jak i mnogiej, ale baza danych reprezentuje bazę danych, więc powinna być w jak największym stopniu w liczbie pojedynczej.

• Jeśli to możliwe, unikaj prefiksów.

Zasady dotyczące nazw tabelUżywaj małych liter w nazwach tabel: MySQL jest zwykle hostowany na serwerach Linux, rozróżnia wielkość liter, więc najlepszą praktyką jest pisanie w nazwach tabel małymi literami.

• Nazwy tabel muszą być w liczbie pojedynczej: tabela jest pojedynczą jednostką, podobnie jak model, więc dziwne jest, aby nazwa tabeli była w liczbie mnogiej.

• Przedrostki w nazwie tabeli: Wielokrotnie widzieliśmy, że tabele są poprzedzone nazwą bazy danych lub nazwą projektu. Czasami jest to konieczne, gdy w naszym projekcie mamy wiele baz danych, aby przezwyciężyć ograniczenia niektórych dostawców hostingu. Ale jeśli nie jest to konieczne, a nasz projekt jest mały, unikaj używania przedrostków.

Nazwa pólStosuj wszystkie powyższe zasady, to znaczy używaj małych liter, nie używaj spacji, nie używaj liczb i unikaj przedrostków.

• Jeśli to możliwe, używaj jednego lub dwóch krótkich słów.

• Nazwy pól muszą być zrozumiałe, na przykład: cena, nazwa_firmy itp.

• Nazwa kolumny podstawowej: klucz podstawowy może mieć identyfikator lub nazwę tabeli _id. To będzie zależało od wyboru.

• Unikaj używania zastrzeżonych słów w polach: *****, data itp. W takich przypadkach lepiej jest używać przedrostków, takich jak data_zapisu itp.

• Unikaj używania nazw kolumn o tej samej nazwie tabeli. Może to powodować zamieszanie podczas pisania zapytań.

• Unikaj nazw w skróconych lub połączonych akronimach.

2. Zawsze używaj prawidłowego typu danych

PrzykładUżywanie varchar (20) do przechowywania wartości datetime zamiast DATETIME może powodować błędy podczas obliczania czasu związanego z datą i jest również możliwe w przypadku nieprawidłowego przechowywania danych.

3. Użyj CHAR (1) zamiast VARCHAR (1)

4. CHAR dla danych stałych

Przykład:

Używanie CHAR (1000) zamiast VARCHAR (1000) zajmuje więcej miejsca, jeśli długość danych jest mniejsza niż 1000.

5. Unikaj używania regionalnych formatów dat

• DATA GODZINA
• DATA

Zawsze używaj formatu RRRR-MM-DD lub formatu daty ISO, który pasuje do Twojego silnika SQL. Inne formaty regionalne, takie jak DD-MM-RRRR, DD-MM-RRRR, nie będą przechowywane prawidłowo.

6. Zoptymalizuj swoje żądania dotyczące pamięci podręcznej

 // Pamięć podręczna NIE działa $ r = mysql_query ("SELECT nazwa FROM użytkowników WHERE rekord> = CURDATE ()"); 

 // Pamięć podręczna DZIAŁA $ dzisiaj = data ("R-m-d"); $ r = mysql_query ("SELECT nazwa FROM users WHERE rekord> = '$ dzisiaj'"); 

7. Unikaj używania „SELECT *” w zapytaniach

To dobry nawyk określ niezbędne kolumny w instrukcji SELECT.

8. Użyj LIMIT 1 Jeśli chcesz tylko jeden wiersz

W takich przypadkach dodanie LIMIT 1 do zapytania może znacznie zwiększyć szybkość. W ten sposób baza danych przestanie skanować wyniki w momencie ich znalezienia, zamiast przeglądać całą tabelę lub indeks.

 // Mam użytkowników z Madrytu? // czego NIE robić: $ r = mysql_query ("SELECT * FROM user WHERE city =‘ Madrid ’"); if (mysql_num_rows ($ r)> 0) {//…} // dużo lepiej: $ r = mysql_query ("SELECT 1 FROM user WHERE city =‘ Madrid ’LIMIT 1"); if (mysql_num_rows ($ r)> 0) {//…}

9. Korzystanie z ***** BY

Nie nadużywaj jego użycia.

10. Wybierz odpowiedni silnik podstawowy

Jeśli tworzysz aplikację, która zapisuje dane częściej niż czytanie
(na przykład: bankowość w czasie rzeczywistym), wybierz silnik przechowywania InnoDB.

Wybranie niewłaściwego silnika magazynu wpłynie na wydajność zapytań.

11. W razie potrzeby użyj klauzuli EXISTS

 If (SELECT liczba (*) z tabeli WHERE col = 'jakaś wartość')> 0

 Jeśli EXISTS (SELECT * z tabeli WHERE col = 'jakaś wartość')

12. Użyj EXPLAIN w swoich WYBRANYCH zapytaniach

Wynik zapytania EXPLAIN pokaże używane indeksy, sposób eksploracji tabeli, sposób jej uporządkowania itp.

Wybierz zapytanie SELECT (najlepiej złożone, z łącznikami) i dodaj słowo EXPLAIN na początku wszystkiego. Zwróci wyniki w prostej tabeli. Załóżmy na przykład, że zapomniałem zindeksować kolumnę, wyświetli nam następujący ekran:

POWIĘKSZAĆ

Po dodaniu indeksu do tabeli stanów wyglądałoby to tak:

POWIĘKSZAĆ

13. Indeksuj i używaj tego samego typu kolumny dla złączeń

Ponadto kolumny, do których dołączasz, muszą być tego samego typu. Na przykład, jeśli łączysz kolumnę typu DECIMAL z kolumną typu INT z innej tabeli, MySQL nie będzie w stanie użyć przynajmniej jednego z dwóch indeksów. Nawet kodowanie znaków musi być tego samego typu dla kolumn Typ ciągu.

 // szukanie firm w moim mieście $ r = mysql_query ("SELECT nazwa_firmy FROM users LEFT JOIN companyias ON (users.city = companyias.city) WHERE users.id = $ user_id");

14. Użyj NOT NULL, jeśli możesz

Przede wszystkim zadaj sobie pytanie, czy istnieje różnica między pustym ciągiem a wartością NULL (lub dla pól INT: 0 kontra NULL). Jeśli nie ma problemu między tymi dwiema wartościami, nie potrzebujesz pola NULL. Kolumny NULL wymagają dodatkowego miejsca i mogą zwiększyć złożoność instrukcji porównania. Po prostu unikaj ich, kiedy tylko możesz.

W każdym razie rozumiemy, że w niektórych bardzo specyficznych przypadkach istnieje powód do używania kolumn NULL, co nie zawsze jest złe.

15. Tabele o stałych rozmiarach (statyczne) są szybsze

Oto kilka przykładów typów kolumn, które NIE mają stałego rozmiaru:

• VARCHAR
• TEKST
• KROPELKA

Jeśli uwzględnisz tylko jeden z tych typów kolumn, tabela nie będzie już miała stałego rozmiaru i będzie musiała być traktowana inaczej przez Silnik MySQL.

Tabele o stałym rozmiarze mogą zwiększyć produktywność, ponieważ silnik MySQL szybciej przeszukuje Twoje rekordy. Gdy chcesz przeczytać konkretny wiersz w tabeli, możesz szybko obliczyć jego pozycję. Jeśli rozmiar wiersza nie jest stały, za każdym razem, gdy musisz wyszukiwać, musisz najpierw wykonać zapytanie do indeksu klucza podstawowego.

Łatwiej je też przeszukiwać i odbudowywać po wypadku. Ale z drugiej strony mogłyby też zajmować więcej miejsca.

16. Partycjonowanie pionowe

Przykład 1:

Na pewno będziesz mieć tabelę użytkowników zawierającą adres pocztowy, który nie jest często używany. Tutaj możesz podzielić tabelę i przechowywać adresy w osobnej tabeli. W ten sposób twoja główna tabela użytkownika miałaby mniejszy rozmiar. Jak wiesz, im mniejsze, tym szybsze stoły.

Przykład 2:

Masz w tabeli pole „last_access”. Jest aktualizowany za każdym razem, gdy użytkownik uzyskuje dostęp do Twojej strony. Ale każdy dostęp powoduje zwolnienie pamięci podręcznej zapytań dla tej tabeli. To, co możesz zrobić, to umieścić to pole w innej tabeli, aby zmiany w tabeli użytkownika były ograniczone do minimum.

Ale musisz również upewnić się, że nie musisz stale dołączać do dwóch stołów po podzieleniu na partycje, w przeciwnym razie spadnie wydajność, w przeciwieństwie do tego, czego szukaliśmy.

17. Przechowuje adresy IP jako UNSIGNED INT

Ale musisz się upewnić, że kolumna jest UNSIGNED INT (liczba całkowita bez znaku), ponieważ adresy IP wykorzystują cały 32-bitowy zakres bez znaku.

W swoich zapytaniach możesz użyć Funkcja INET_ATON () aby przekonwertować adres IP na liczbę całkowitą, a INET_NTOA() zrobić odwrotnie. Istnieją również podobne funkcje w PHP zwane ip2long () Tak long2ip ().

18. Twórz widoki, aby uprościć powszechne użycie w tabelach

Może być również używany do filtrowania kolumn nieindeksowanych, pozostawiając tylko te pola, które wyświetlają się szybciej w wyszukiwaniu.

19. Nie używaj ***** BY RAND ()

Jeśli naprawdę potrzebujesz losowych tabel dla swojego wyniku, są na to znacznie lepsze sposoby. Oczywiste jest, że zajmą one więcej kodu, ale unikniesz możliwego wąskiego gardła, które rośnie wykładniczo wraz ze wzrostem zawartości.

Problem w tym, że MySQL będzie musiał działać RAND () (co wymaga mocy obliczeniowej) dla każdego z wierszy przed ich posortowaniem i zwróceniem pojedynczego wiersza.

 // sposób, aby tego NIE robić: $ r = mysql_query ("SELECT nazwa użytkownika FROM użytkownik ***** BY RAND () LIMIT 1"); // dużo lepiej: $ r = mysql_query ("liczba SELECT (*) FROM użytkownik"); $d = mysql_fetch_row ($r); $rand = mt_rand (0, $d [0] - 1); $r = mysql_query ("WYBIERZ nazwę użytkownika FROM nazwa użytkownika LIMIT $ rand, 1");

20. Zoptymalizuj klauzulę WHERE

• Usuń niepotrzebne nawiasy. Na przykład:

 OD: (do5 AND b = c AND a = 5

 WYBIERZ LICZNIK (*) Z tabeli.