Skoro wyznaczyłeś nagrodę, podzielę się moimi trudnymi sekretami...

Ogólnie rzecz biorąc, wszystkie SQL, które dziś dostroiłem, wymagały użycia zapytań podrzędnych. Po przybyciu ze świata baz danych Oracle rzeczy, które brałem za pewnik, nie działały tak samo z MySQL. A moje czytanie na temat tuningu MySQL sprawia, że dochodzę do wniosku, że MySQL stoi za Oracle pod względem optymalizacji zapytań.

Podczas gdy proste zapytania wymagane dla większości aplikacji B2C mogą działać dobrze dla MySQL, większość zagregowanego typu raportowania z zapytań potrzebnych do raportowania inteligencji wydaje się wymagać sporo planowania i reorganizacji zapytań SQL, aby poprowadzić MySQL do ich szybszego wykonania.

Administracja:

max_connections liczba jednoczesnych połączeń. Domyślną wartością jest 100 połączeń (151 od wersji 5.0) - bardzo małe.

Uwaga:

Połączenia wymagają pamięci, a system operacyjny może nie być w stanie obsłużyć wielu połączeń.

Binaria MySQL dla Linuksa / x86 pozwalają na do 4096 jednoczesnych po3 ± czeñ, ale samodzielnie skompilowane binaria czêsto maj ± mniejszy limit.

Ustaw table_cache, aby pasował do liczby otwartych tabel i jednoczesnych połączeń. Obserwuj wartość open_tables i jeśli szybko rośnie, musisz zwiększyć jej rozmiar.

Uwaga:

2 poprzednie parametry mogą wymagać dużej ilości otwartych plików. 20 + max_connections+table_cache * 2 jest dobrym oszacowaniem tego, czego potrzebujesz. MySQL na Linuksie ma opcję open_file_limit, ustaw to limit.

Jeśli masz złożone zapytania sort_buffer_size i tmp_table_size mogą być bardzo ważne. Wartości będą zależeć od złożoności zapytania i dostępnych zasobów, ale zalecane są odpowiednio 4MB i 32MB.

Uwaga: są to wartości "na połączenie", wśród read_buffer_size, read_rnd_buffer_size i innych, co oznacza, że ta wartość może być potrzebna dla każdego połączenia. Tak więc, należy wziąć pod uwagę swój ładunek i dostępny zasób podczas ustawiania tych parametry. Na przykład sort_buffer_size jest alokowany tylko wtedy, gdy MySQL musi wykonać sortowanie. Uwaga: uważaj, aby nie zabrakło pamięci.

Jeśli masz wiele połączeń (np. witrynę internetową bez trwałych połączeń), możesz poprawić wydajność, ustawiając thread_cache_size na wartość niezerową. 16 to dobra wartość na początek. Zwiększ wartość, dopóki threads_created nie zwiększy się bardzo szybko.

KLUCZ PODSTAWOWY:

Może być tylko jedna kolumna AUTO_INCREMENT każda tabela musi być indeksowana i nie może mieć wartości domyślnej

Klucz jest zwykle synonimem indeksu. Atrybut Key PRIMARY KEY może być również określony jako tylko klucz, gdy podany jest w definicji kolumny. Zostało to zaimplementowane w celu kompatybilności z innymi systemami baz danych.

Klucz podstawowy jest unikalnym indeksem, w którym wszystkie kolumny klucza muszą być zdefiniowane jako NOT NULL

Jeśli klucz podstawowy lub indeks UNIQUE składa się tylko z jednej kolumny, która ma typ integer, możesz również odnieść się do do kolumny jako "_rowid" w poleceniach SELECT.

Obecnie tylko InnoDB (v5.1?) tabele obsługują klucze obce.

Zazwyczaj tworzysz wszystkie indeksy potrzebne podczas tworzenia tabel. Każda kolumna zadeklarowana jako klucz podstawowy, klucz, Unikat lub indeks zostanie zindeksowana.

NULL oznacza "brak wartości". Aby sprawdzić wartość NULL, nie można użyć operatorów porównania arytmetycznego, takich jak=,. Użycie zamiast tego operatory IS NULL i IS NOT NULL:

NO_AUTO_VALUE_ON_ZERO wyłącza automatyczny przyrost dla 0 tak, że tylko NULL generuje następny numer sekwencji. Ten tryb może być przydatny, jeśli 0 zostało zapisane w kolumnie AUTO_INCREMENT tabeli. (Przy okazji, przechowywanie 0 nie jest zalecaną praktyką.)

Aby zmienić wartość licznika AUTO_INCREMENT używanego dla nowych wierszy:

ALTER TABLE mytable AUTO_INCREMENT = value; 

Lub SET INSERT_ID = value;

O ile nie określono inaczej, wartość będzie zacznij od: 1000000 lub określ go w ten sposób:

...) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1

Znaczniki czasu:

Wartości kolumn znacznika czasu są konwertowane z bieżącej strefy czasowej na UTC do przechowywania, i od UTC do bieżącej strefy czasowej do pobrania.

Http://dev.mysql.com/doc/refman/5.1/en/timestamp.html Dla jednej kolumny znacznika czasu w tabeli można przypisać bieżący znacznik czasu jako wartość domyślną i automatyczną aktualizację wartość.

Jedna rzecz, na którą należy zwrócić uwagę podczas używania jednego z tych typów klauzuli WHERE, najlepiej zrobić WHERE datecolumn = FROM_UNIXTIME(1057941242) i nie WHERE UNIX_TIMESTAMP(datecolumn) = 1057941242. wykonanie tego ostatniego nie wykorzysta indeksu w tej kolumnie.

Http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

 UNIX_TIMESTAMP() 
 FROM_UNIXTIME() 
 UTC_DATE()
 UTC_TIME()
 UTC_TIMESTAMP()

Jeśli przekonwertujesz datetime na Unix timestamp w MySQL:
A następnie dodać 24 godziny do it:
A potem przekonwertować go z powrotem do datetime magicznie traci godzinę!

Oto, co się dzieje. Podczas konwersji Unix timestamp z powrotem do datetime Strefa czasowa jest brana pod uwagę i tak się składa, że między 28 a 29 października 2006 roku straciliśmy czas letni i straciliśmy godzinę.

Począwszy od MySQL 4.1.3, funkcje CURRENT_TIMESTAMP(), CURRENT_TIME(), CURRENT_DATE () i FROM_UNIXTIME() zwracają wartości w połączeniu bieżąca Strefa czasowa , która jest dostępna jako wartość zmiennej systemowej time_zone. Ponadto UNIX_TIMESTAMP() zakłada, że jej argument jest wartością datetime w bieżącej strefie czasowej.

Bieżące ustawienie strefy czasowej nie wpływa na wartości wyświetlane przez funkcje takie jak UTC_TIMESTAMP() lub wartości w kolumnach daty, czasu lub DATETIME.

Uwaga: przy aktualizacji tylko aktualizuje DateTime, jeśli pole zostanie zmienione, jeśli aktualizacja nie spowoduje zmiany pól wtedy DateTime nie jest aktualizowany!

Dodatkowo, pierwszym znacznikiem czasu jest domyślnie zawsze AUTOUPDATE, nawet jeśli nie podano

Podczas pracy z datami, prawie zawsze konwetuję do daty Juliańskiej, ponieważ matematyka danych jest wtedy prostą sprawą dodawania lub odejmowania liczb całkowitych i sekund od północy z tego samego powodu. Rzadko potrzebuję czasu o drobniejszej ziarnistości niż sekundy.

Oba te mogą być zapisane jako 4-bajtowa liczba całkowita, a jeśli spacja jest naprawdę ciasna, może być w UNIX time (sekundy od epoki 1/1/1970) jako niepodpisana liczba całkowita, która będzie dobra do około 2106 jako:

' secs in 24Hrs = 86400

' Signed Integer max val = 2,147,483,647 - może pomieścić 68 lat sekund

' Unsigned Integer max val = 4,294,967,295 - może pomieścić 136 lat sekund

Protokół Binarny:

MySQL 4.1 wprowadził protokół binarny, który umożliwia wysyłanie wartości danych nie będących ciągami i zwrócony w natywnym formacie bez konwersji do i z formatu łańcuchowego. (Bardzo przydatne)

Pomijając, mysql_real_query() jest szybszy niż mysql_query (), ponieważ nie wywołuje strlen() operować na łańcuchu instrukcji.

Http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html Protokół binarny obsługuje instrukcje przygotowane po stronie serwera i umożliwia transmisję wartości danych w natywnym formacie. Protokół binarny przeszedł sporo poprawek podczas wcześniejszego wydania MySQL 4.1.

Możesz użyć makra IS_NUM (), aby sprawdzić, czy pole ma typ liczbowy. Przekaż wartość typu do is_num () i zostanie ona obliczona na TRUE, jeśli pole jest numeryczne:

Należy zauważyć, że dane binarne mogą być wysyłane wewnątrz zwykłego zapytania, jeśli je unikniesz i zapamiętasz, że MySQL wymaga tylko , że Ukośnik i znak cudzysłowu mają być zabezpieczone. Tak więc jest to naprawdę łatwy sposób na wstawianie krótszych ciągów binarnych, takich jak zaszyfrowane / solone hasła na przykład.

Serwer Główny:

Http://www.experts-exchange.com/Database/MySQL/Q_22967482.html

Http://www.databasejournal.com/features/mysql/article.php/10897_3355201_2

GRANT REPLICATION SLAVE ON . do slave_user identyfikowanego przez 'slave_password'

#Master Binary Logging Config  STATEMENT causes replication 
              to be statement-based -  default

log-bin=Mike
binlog-format=STATEMENT
server-id=1            
max_binlog_size = 10M
expire_logs_days = 120    


#Slave Config
master-host=master-hostname
master-user=slave-user
master-password=slave-password
server-id=2

Plik dziennika binarnego musi powinno być:

Http://dev.mysql.com/doc/refman/5.0/en/binary-log.html

Http://www.mydigitallife.info/2007/10/06/how-to-read-mysql-binary-log-files-binlog-with-mysqlbinlog/

Http://dev.mysql.com/doc/refman/5.1/en/mysqlbinlog.html

Http://dev.mysql.com/doc/refman/5.0/en/binary-log.html

Http://dev.mysql.com/doc/refman/5.1/en/binary-log-setting.html

Możesz usunąć wszystkie pliki dziennika binarnego z instrukcją Reset MASTER lub ich podzbiór z instrukcją PURGE MASTER

--result-file=binlog.txt TrustedFriend-bin.000030

Normalizacja:

Http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

Funkcje UDF

Http://www.koders.com/cpp/fid10666379322B54AD41AEB0E4100D87C8CDDF1D8C.aspx

Http://souptonuts.sourceforge.net/readme_mysql.htm

DataTypes:

Http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html

Http://www.informit.com/articles/article.aspx?p=1238838&seqNum=2

Http://bitfilm.net/2008/03/24/saving-bytes-efficient-data-storage-mysql-part-1/

Należy zauważyć, że w tabeli mieszanej z CHAR i VARCHAR, mySQL zmieni CHAR 's to VARCHAR' s

MySQL zawsze przedstawia daty z pierwszym rokiem, zgodnie ze standardowymi specyfikacjami SQL i ISO 8601

Misc:

Wyłączenie niektórych funkcji MySQl spowoduje powstanie mniejszych plików z danymi i szybszy dostęp. Na przykład:

--datadir określi katalog danych i

--skip-innodb wyłączy opcję inno i zapisze ty 10-20m

Więcej tutaj http://dev.mysql.com/tech-resources/articles/mysql-c-api.html

Pobierz Rozdział 7-Za Darmo

InnoDB jest transakcyjny, ale wiąże się z tym narzut wydajności. Moje tabele są wystarczające dla 90% moich projektów. Tabele bez transakcji (MyISAM) mają kilka zalet własnych, z których wszystkie występują, ponieważ: {]}

Nie ma transakcji:

Niższe wymagania dotyczące miejsca na dysku

Mniej pamięci do wykonania aktualizacji

Każda tabela MyISAM jest przechowywana na dysku w trzech plikach. Pliki mają Nazwy zaczynające się od nazwy tabeli i mają rozszerzenie wskazujące typ pliku. A .plik frm przechowuje format tabeli. Plik z danymi ma .Rozszerzenie Myd (MYData). Plik indeksu zawiera .Rozszerzenie MYI (MYIndex).

Te pliki można skopiować do nienaruszonego miejsca przechowywania bez użycia tworzenie kopii zapasowych przez administratorów MySQL jest czasochłonne (podobnie jak przywracanie)

Sztuką jest zrobić kopię tych plików, a następnie upuścić tabelę. Po odłożeniu plików MySQl rozpozna je i zaktualizuje śledzenie tabeli.

Jeśli musisz wykonać kopię zapasową / przywrócić,

Przywracanie kopii zapasowej lub importowanie z istniejącego pliku zrzutu może zająć dużo czasu w zależności od liczby indeksów i kluczy głównych w każdej tabeli. Możesz znacznie przyspieszyć ten proces modyfikując oryginalny plik zrzutu, otaczając go następującym tekstem:

SET AUTOCOMMIT = 0;
SET FOREIGN_KEY_CHECKS=0;

.. your dump file ..

SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
SET AUTOCOMMIT = 1;

Aby znacznie zwiększyć szybkość przeładowania, dodaj polecenie SQL SET AUTOCOMMIT = 0; Na początku pliku zrzutu i dodaj polecenie COMMIT; na końcu.

Domyślnie autocommit jest włączony, co oznacza, że każde polecenie insert w plik zrzutu zostanie potraktowany jako osobna transakcja i zapisany na dysk przed uruchomieniem kolejnej. Jeśli nie dodasz tych poleceń, przeładuj duża baza danych w InnoDB może zająć wiele godzin...

Wartości VARCHAR nie są wypełniane, gdy są przechowywane. Spacje końcowe są zachowywane, gdy wartości są przechowywane i pobierane, zgodnie ze standardowym SQL.

Wartości CHAR i VARCHAR w MySQL są porównywane bez względu na końcowe miejsca.

Użycie CHAR przyspieszy dostęp tylko wtedy, gdy cały rekord ma stały rozmiar. To jest, jeśli używasz dowolnego obiektu o zmiennej wielkości, możesz równie dobrze zmienić wszystkie z nich. Nie zyskujesz prędkości, używając znaku w tabeli, która zawiera również VARCHAR.

Limit VARCHAR 255 znaków został podniesiony do 65535 znaków od MySQL 5.0.3

Wyszukiwanie pełnotekstowe jest obsługiwane tylko dla tabel MyISAM.

Http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

Kolumny BLOB nie mają zestawu znaków, a sortowanie i porównywanie oparte są na wartości liczbowe bajtów w wartościach kolumn

Jeśli tryb strict SQL nie jest włączony i przypisujesz wartość do obiektu BLOB lub kolumny tekstowej, która przekracza maksymalną długość kolumny, wartość jest obcięta, aby pasowała i generowane jest Ostrzeżenie.

Przydatne Polecenia:

Sprawdź tryb ścisły: Wybierz @@ global.sql_mode;

Wyłącz tryb ścisły:

SET @ @ global.sql_mode=";

SET @ @ global.sql_mode= 'MYSQL40'

Lub usunąć: SQL-mode= " STRICT_TRANS_TABLES,...

POKAŻ KOLUMNY Z mytable

Wybierz max (namecount) jako virtualcolumn z mytable ORDER BY virtualcolumn

Http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html

Http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id last_insert_id ()

Pobiera PK ostatniego wiersza wstawionego w bieżącym wątku max(pkcolname) pobiera Ostatnie PK ogółem.

Uwaga: jeśli tabela jest pusta max(pkcolname) zwraca 1 mysql_insert_id() konwertuje Typ zwracanej funkcji natywnego API MySQL C mysql_insert_id() do typu long (nazwany int w PHP).

Jeśli twoja kolumna AUTO_INCREMENT ma typ kolumny BIGINT, wartość zwracana przez mysql_insert_id() będzie niepoprawne. Zamiast tego użyj wewnętrznej funkcji MySQL SQL LAST_INSERT_ID () w zapytaniu SQL.

Http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

Tylko zauważ, że gdy próbujesz wstawić dane do tabeli i otrzymujesz błąd:

Unknown column ‘the first bit of data what you want to put into the table‘ in ‘field list’

Używanie czegoś w rodzaju

INSERT INTO table (this, that) VALUES ($this, $that)

To dlatego, że nie masz żadnych apostrofów wokół wartości, które próbujesz przykleić do tabeli. Więc powinieneś zmienić kod na:

INSERT INTO table (this, that) VALUES ('$this', '$that') 

Przypomnienie, że " są używane do definiowania pól, baz danych lub tabel MySQL, a nie wartości;)

Utracone połączenie z serwerem podczas zapytanie:

Http://dev.mysql.com/doc/refman/5.1/en/gone-away.html

Http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html

Http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html

Http://dev.mysql.com/doc/refman/5.1/en/show-variables.html

Http://dev.mysql.com/doc/refman/5.1/en/option-files.html

Http://dev.mysql.com/doc/refman/5.1/en/error-log.html

Tuning Queries

Http://www.artfulsoftware.com/infotree/queries.php?&bw=1313

The ultimate MySQL legacy database nightmare

Wymagało to szeregu aplikacji do przetworzenia tabel w coś użytecznego przy użyciu wielu sztuczek wymienionych tutaj.

Jeśli uznałeś to za zdumiewająco pomocne, podziękuj, głosując.

Zobacz także inne moje artykuły i dokumenty na: www.coastrd.com