MySQL to drugi najpopularniejszy na świecie system zarządzania relacyjnymi bazami danych typu open source. Jest używany na całym świecie ze względu na niezmiennie wysoką wydajność, wysoką niezawodność i łatwość użytkowania. W tym artykule przedstawiono niektóre z najlepszych praktyk MySQL.

Dzięki nim możesz ćwiczyć SQL i ustawić sobie ćwiczenia SQL, poznać limit SQL i ćwiczyć SQL online, a także o najczęstszych problemach związanych z praktyką SQL.

Pomoże Ci zrozumieć SQL numer wiersza, top SQL, przesunięcie limitu MySQL i praktykę SQL online.

Jeśli szukasz przyjemnego i łatwego sposobu na wyświetlenie dużej liczby wierszy i kolumn odwiedzającym Twoją witrynę WordPress z kolorowymi, informacyjnymi i responsywnymi interaktywnymi tabelami i wykresami, możesz go użyć z wtyczką wpDataTables, sprawdź ceny i wszystkie funkcjonalności jakie daje tutaj.

1 Zawsze używaj właściwego typu danych

Jedną z najważniejszych najlepszych praktyk MySQL jest używanie typów danych opartych na naturze danych. Używanie nieistotnych typów danych może zajmować więcej miejsca lub prowadzić do błędów.

Na przykład: Użycie varchar (20) zamiast typu danych DATETIME do przechowywania wartości daty i czasu doprowadzi do błędów w obliczeniach związanych z datą i godziną. Możliwe jest również, że zostaną zapisane nieprawidłowe dane.

2 Użyj CHAR (1) zamiast VARCHAR (1)

VARCHAR (1) zajmuje dodatkowe bajty do przechowywania informacji, więc jeśli napiszesz pojedynczy znak, lepiej użyć CHAR (1).

3 Użyj typu danych CHAR do przechowywania tylko danych o stałej długości

Na przykład: Jeśli długość danych jest mniejsza niż 1000, użycie char (1000) zamiast varchar (1000) zajmie więcej miejsca.

4 Unikaj używania regionalnych formatów dat

Używając typu danych DATETIME lub DATE, zawsze używaj formatu daty RRRR-MM-DD lub formatu daty ISO odpowiedniego dla Twojego SQL Engine. Formaty regionalne, takie jak DD-MM-RRRR lub MM-DD-RRRR, nie będą prawidłowo przechowywane.

5 kolumn klucza indeksu

Pożądane jest, aby zapytanie szybko zwróciło wynik, dlatego należy zindeksować kolumny, które są używane w klauzulach JOIN.

W przypadku użycia instrukcji UPDATE obejmującej więcej niż jedną tabelę, należy zindeksować wszystkie kolumny, które są używane do łączenia tabel.

6 Nie używaj funkcji nad indeksowanymi kolumnami…

… bo wtedy indeks traci swój cel.

Załóżmy na przykład, że chcesz uzyskać dane, w których pierwsze dwa znaki kodu klienta to AK. Pisać:

SELECT columns FROM table WHERE customer_code like 'AK%'

i nie pisz

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

Czemu? Pierwszy przykład wykorzysta indeks, co spowoduje szybszy czas odpowiedzi.

**7 Użyj SQL SELECT * tylko w razie potrzeby**

Podążając za listą najlepszych praktyk MySQL, nie używaj na ślepo SELECT * w kodzie. Jeśli tabela ma wiele kolumn, wszystkie zostaną zwrócone. Spowoduje to spowolnienie czasu odpowiedzi, zwłaszcza jeśli wyślesz wynik do aplikacji typu front-end.

Zamiast tego wyraźnie wpisz nazwy kolumn, które są faktycznie potrzebne.

Uwaga: pamiętaj, że wszystkie instrukcje SELECT wymagają klauzuli WHERE.

8 Używaj klauzuli ORDER BY tylko w razie potrzeby

Jeśli chcesz pokazać wynik w aplikacji front-endowej, pozwól jej ZAMÓWIĆ zestaw wyników. Jeśli zrobisz to w SQL, czas odpowiedzi może zostać spowolniony w środowisku wielu użytkowników.

9 Wybierz odpowiedni silnik bazy danych

Jeśli tworzysz aplikację, która częściej odczytuje dane niż zapisuje (np. wyszukiwarkę), wybierz silnik pamięci masowej MyISAM.

Wybór niewłaściwego silnika pamięci masowej wpłynie na wydajność.

10 W razie potrzeby użyj klauzuli EXISTS

Aby sprawdzić istnienie danych, użyj klauzuli EXISTS, która jest szybsza w czasie odpowiedzi. Na przykład użyj:

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

Nie używać :

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

11 WYJAŚNIJ swoje WYBRANE pytania

Jeśli użyjesz słowa kluczowego EXPLAIN, możesz uzyskać wgląd w to, co MySQL robi, aby wykonać zapytanie. Może to pomóc w wykryciu problemów ze strukturami zapytań lub tabel (np. wąskich gardeł).

Zapytanie EXPLAIN pokazuje, które indeksy są używane, jak tabela jest skanowana, sortowana itp.

Wystarczy dodać słowo kluczowe EXPLAIN przed zapytaniem SELECT (najlepiej złożonym z łącznikami). Ponadto, jeśli użyjesz do tego phpmyadmina, twoje wyniki zostaną pokazane w ładnej tabeli.

12 Użyj LIMIT 1, gdy otrzymasz unikalny rząd

Czasami wiesz z góry, że podczas wykonywania zapytań do tabel szukasz tylko jednego wiersza. Na przykład możesz pobierać unikalny rekord lub po prostu sprawdzać istnienie dowolnej liczby rekordów spełniających klauzulę WHERE.

W takich przypadkach będziesz chciał użyć funkcji limitu MySQL, aby zwiększyć wydajność. Oto kolejna z najlepszych praktyk MySQL: po prostu dodaj LIMIT 1 do zapytania. W ten sposób silnik bazy danych nie będzie musiał przechodzić przez całą tabelę lub indeks. Przestanie skanować, gdy znajdzie tylko 1 rekord tego, czego szukasz.

// do I have anyusersfrom Alabama?

// czego NIE robić:
$r = mysql_query(„SELECT * FROM user WHERE state = 'Alabama’");
if (mysql_num_rows($r) > 0) {
// …
}

// znaczniebetter:
$r = mysql_query("SELECT 1 FROM user WHERE state = 'Alabama’ LIMIT 1");
if (mysql_num_rows($r) > 0) {
// …
}

13 Indeksuj i używaj tych samych typów kolumn dla złączeń

Kolejna ważna wskazówka dotycząca najlepszych praktyk MySQL — jeśli Twoja aplikacja ma wiele zapytań JOIN, upewnij się, że kolumny, którymi się łączysz, są indeksowane w obu tabelach. Wpływa to na wewnętrzną optymalizację operacji łączenia przez MySQL.

Ponadto połączone słupy muszą być tego samego typu. Na przykład, jeśli połączysz kolumnę DECIMAL z kolumną INT z innej tabeli, MySQL nie będzie mógł używać żadnego z indeksów. Nawet kodowanie znaków musi być tego samego typu dla kolumn typu string.

// 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");

// obie kolumny stanu powinny być indeksowane
// i obie powinny mieć ten sam typ i kodowanie znaków
// lub MySQL może wykonywać pełne skanowanie tabel

14 Ukryj MySQL przed Internetem

Wiedzą o tym doświadczeni administratorzy baz danych i personel ds. bezpieczeństwa — nigdy nie umieszczaj bazy danych w katalogu głównym serwera WWW.

W przypadku aplikacji obsługujących Internet MySQL powinien być ukryty za zaporą sieciową. Komunikacja powinna być włączona tylko między serwerami aplikacji a serwerami WWW.

Inną opcją jest użycie pomijania sieci MySQL. Gdy jest włączony, MySQL nasłuchuje tylko lokalnych połączeń gniazd i ignoruje wszystkie porty TCP.

15 Użyj najmniejszych możliwych typów danych

Pozwól, że opowiem ci historię. Kiedy chodziłem do college’u, filozofia była taka, że „pamięci jest mało". To były czasy dysków twardych 256 MB. W dzisiejszych czasach nikt nie przejmuje się ani trochę pamięcią lub miejscem na dysku twardym. Nowa filozofia mówi, że „pamięć jest tania”. Może to być prawda w dolarach, ale odczytywanie dużych typów danych nadal trwa dłużej niż odczytywanie mniejszych. Duże typy danych wymagają wczytania do pamięci większej liczby sektorów dysku.

Morał polega na tym, że podczas projektowania tabel zignoruj pokusę, aby natychmiast przeskoczyć do największego typu danych. Pomyśl o użyciu int zamiast bigint.

Unikaj również pól tekstowych typu largechar (255), gdy wystarczy varchar lub mniejszy znak.

Jeśli użyjesz właściwego typu danych, więcej rekordów zmieści się w pamięci lub bloku klucza indeksu. Prowadzi to do mniejszej liczby odczytów i większej wydajności.

16 Skorzystaj z buforowania zapytań

Buforowanie zapytań to jedna z najskuteczniejszych metod zwiększania wydajności. Większość serwerów MySQL ma to domyślnie włączone.

Pamięć podręczna zapytań przechowuje tekst instrukcji SELECT wraz z odpowiednim zestawem wyników. Jeśli serwer później otrzyma identyczną instrukcję, pobierze wyniki z pamięci podręcznej zapytań, zamiast analizować i ponownie wykonywać instrukcję. Pamięć podręczna zapytań jest współużytkowana przez sesje, dzięki czemu zestaw wyników wygenerowany przez jednego klienta może zostać wysłany w odpowiedzi na to samo zapytanie wysłane przez innego klienta.

Jednak, jakkolwiek jest świetne, buforowanie zapytań ma swoje ograniczenia. Weź następujące zdanie:

Problem polega na tym, że zapytania zawierają pewne niedeterministyczne funkcje, takie jak NOW() i RAND(). MySQL nie może z góry obliczyć takich funkcji, więc nie są one buforowane.

Na szczęście jest na to proste rozwiązanie: możesz przechowywać wyniki funkcji w zmiennej.

17 Nie edytuj plików zrzutu

Pliki zrzutu są bardzo mylące i mogą powodować uszkodzenie. Czemu? Jeśli kiedykolwiek widziałeś pliki zrzutu utworzone przez mysqldump, zgodzisz się, że wyglądają jak zwykłe, nieszkodliwe pliki tekstowe. Dlatego większość ludzi edytuje je w standardowym edytorze tekstu, co powoduje pojawienie się uszkodzeń.

Jeśli kiedykolwiek próbowałeś edytować pliki zrzutów, szybko dowiedziałeś się, że nie są to pliki tekstowe. Tak więc jedynym gwarantowanym sposobem uniknięcia problemów jest pozostawienie plików zrzutu w spokoju.

18 Użyj ustawienia rozmiaru bloku MyISAM

Ustawienie rozmiarów bloków w indeksach tabel MyISAM jest jedną z najlepszych praktyk MySQL. Można go znaleźć w plikach .MYI w buforze klucza, a także na dysku. Ustawienie wygląda tak: myisam_block_size.

Ma domyślną wartość 1k. Jest dość mały, aby był optymalny w nowoczesnym systemie. Większość systemów plików używa bloków o większym rozmiarze. Wiemy, że napisanie pojedynczego bloku indeksu wymaga odczytu, a następnie zapisu. System operacyjny nigdy nie będzie musiał zapisywać danych na dysku, o ile rozmiar bloku jest równy lub większy niż rozmiar bloku systemu plików.

19 Włącz delay_key_write

Delay_key_write jest domyślnie wyłączone. Jest ku temu powód. Jeśli wystąpiła awaria w trakcie projektu, Twoja baza danych może zostać uszkodzona.

Dlaczego więc chcesz go włączyć? Powód jest prosty. Ponieważ delay_key_write zapewnia, że baza danych nie opróżni pliku klucza MyISAM po każdym zapisie. Dlatego też, jeśli w najbliższym czasie będziesz pisać kolejny raz, zaoszczędzisz sporo czasu.

Oto kolejna fajna wskazówka, którą wybraliśmy z najlepszych praktyk MySQL: Włączanie delay_key_write jest inne dla każdej wersji. Aby zobaczyć, jak go włączyć w określonej wersji, zapoznaj się z oficjalnym podręcznikiem witryny MySQL.

20 Użyj śledzenia stosu, aby wyizolować błędy

Zgodnie z najlepszymi praktykami MySQL, ta wskazówka została zapożyczona ze Sky SQL, ponieważ jest po prostu zbyt prosta i zbyt wygodna, aby ją pominąć.

Stack_trace MySQL może służyć do izolowania różnych błędów. Zamiast zagłębiać się we wszystkie możliwe zastosowania, programista może chcieć zwrócić uwagę na to, jak łatwo pusty wskaźnik może zrujnować twój kod.

Dzięki tej wskazówce Sky SQL wykrywanie, śledzenie i naprawianie staje się znacznie łatwiejsze.

21 MySQL zmienia hasło ROOT

Zmiana hasła ROOT może wydawać się prosta, ale wiedza, jak to zrobić, jest równie ważna dla domowego systemu operacyjnego, jak i serwerów MySQL.

Czasami zdarza się, że nadgorliwi początkujący i roztargnieni doświadczeni użytkownicy nie mogą zrozumieć, dlaczego nie mogą zmienić niektórych ustawień i zwracają błędy. To może być tak proste, jak przyznanie sobie dostępu ROOT. Użytkownik nie powinien nawet go wyszukiwać.

Oto jak ustawić hasło ROOT i jak zmienić hasło ROOT użytkownika:

//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 Napraw swoje pliki konfiguracyjne

MySQL Tuner to skrypt Perla, który może w jakiś sposób zoptymalizować wydajność, sugerując zmiany w plikach konfiguracyjnych.

Jeśli niektóre wskazówki i triki w MySQL są niezwykle wygodne, narzędzia takie jak MySQL Tuner są darem niebios, który zasługuje na bycie w swojej własnej kategorii.

Jest to coś, z czego mogą korzystać zarówno nowicjusze, jak i profesjonaliści. MySQL Tuner nie jest konkretną wskazówką dla MySQL, więc istnieje wiele poprawek i modyfikacji, które można zastosować. Im częściej go używasz, tym więcej poprawek możesz nauczyć się stosować na własny użytek.

Na pierwszy rzut oka może się to wydawać onieśmielające, ale właśnie dlatego na oficjalnej stronie projektu można znaleźć podręcznik –man db, a także wszystkie notatki, niezbędną lekturę i ustawienia. Dzięki temu MySQL Tuner powinien szybko stać się Twoim podstawowym narzędziem do przyspieszania MySQL i testowania plików konfiguracyjnych.

Końcowe przemyślenia na temat najlepszych praktyk MySQL

W świecie informatyki MySQL jest niewątpliwie jednym z najważniejszych i najbardziej wpływowych programów, jakie kiedykolwiek się pojawiły. Jest tak zaangażowana i wszechstronna, że gdy tylko wygląda na to, że wszystkie możliwe sztuczki i skróty zostały odkryte, ktoś inny wtrąca się z nowym sposobem wykorzystania nowej argumentacji lub ustawienia.

Przedstawiliśmy tylko najlepsze praktyki MySQL, które każdy powinien znać. Ale im częściej używasz MySQL, tym częściej będziesz ich używać. Możesz ćwiczyć SQL online i ustawiać sobie testy SQL. Następnie opracujesz własne sztuczki i znajdziesz własne najważniejsze wskazówki, na których możesz polegać, takie jak limit SQL, numer wiersza SQL i zrozumiesz, dlaczego powinieneś wybrać MySQL w pierwszej kolejności.

Jeśli podobał Ci się ten artykuł o najlepszych praktykach MySQL, powinieneś również przeczytać te:

Źródło nagrywania: wpdatatables.com