Caching interogări MySQL - articol pe

Solicitare cache îmbunătățește performanța fără a se scufunda în structura cererilor propriu-zise. Ne vom uita la MySQL mecanismul de interogare cache built-in și o abordare alternativă pentru cazurile în care mecanismul încorporat este inaplicabilă.

Built-in cache interogări mecanism în MySQL.

MySQL oferă un mecanism de interogare cache încorporată, care însă nu este activată în mod implicit. Acestea sunt setările implicite expuse în MySQL 5.0:

mysql> afișează variabile ca 'query_cache%';
+------------------------------+---------+
| VARIABLE_NAME | Valoarea |
+------------------------------+---------+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+

Pentru a activa memoria cache de interogare și selectați-l sub 32 MB de memorie, puteți rula setul de interogare @@ global.query_cache_size = 32 * 1024 * 1024; ca root, și pentru a face această setare permanentă, trebuie adăugat la subsecțiuni în my.cnf [mysqld] linia query_cache_size = 32m. Un al doilea parametru util - query_cache_limit specifică valoarea maximă a rezultatului interogării, care pot fi plasate în cache

După pornirea cache funcționează în mod automat:

  • Atunci când fiecare tip de solicitare SELECT calculeaza hash șir de interogare sumă și căutări în cache. În cazul în care constată - ruzeltat se întoarce din cache - în cazul în care nu, executa interogarea, iar rezultatele sunt înregistrate în memoria cache (în cazul în care rezultatul nu mai query_cache_limit valori).
  • Pentru fiecare tip de solicitare UPDATE, REPLACE, INSERT, DELETE, TRUNCATE sau ALTER, elimină din cache toate interogări folosind tabelul de renovare în curs.

Notă următoarele caracteristici ale memoriei cache:

  • Diferența este definit literalmente cereri, care răspund la registrul de comparație. Prin urmare, SELECT * FROM de știri și selectați *, de la știri vor cache două solicitări diferite.
  • Memoria cache devine întotdeauna rezultatul interogării în ansamblu, rezultatele sub-interogările nu sunt memorate în cache.
  • Cache funcționează la fel pentru interogări pe tabele cu diferite motoare de stocare. MySQL cache, de asemenea, interogări SELECT la reprezentările (vizualizare).
  • Un număr de cereri nu pot fi memorate în cache:
    • Cererile care conțin una dintre funcțiile de bază non-deterministic: ACUM (), SLEEP (), RAND (), CURTIME (), LAST_INSERT_ID () și alte pulsatilă
    • Interogări folosind funcții sau proceduri stocate, definite de utilizator.
    • Interogări folosind valori personalizate sau variabile locale.
    • Interogările care accesează baza de date MySQL, INFORMATION_SCHEMA sau performance_schema.
    • Interogările care accesează tabele, împărțite în partiții (ca de MySQL versiunea 1.5.63, 5.5.23, 5.6.5).
    • Queries precum SELECT. PENTRU UPDATE, SELECT. ÎN MOD SHARE, SELECT. ÎN outfile, SELECT. ÎN DUMPFILE, SELECT * FROM. UNDE autoincrement_col IS NULL.
    • Interogări folosind tabele temporare.
    • Interogările care nu sunt citate în tabele.
    • Interogările care generează avertismente (avertismente).
    • În cazul în care utilizatorul nu are drepturi la întreaga masă, dar numai într-o anumită coloană a tabelului. Această excepție - datorită faptului că o memorie cache de interogare pentru toți utilizatorii, precum și mijloacele de acces la cache drepturile sunt verificate doar la nivel de masă.

Ca un exemplu de interogare care pot fi memorate în cache, vom da solicitarea, generează o listă de discuții în webew.ru pagina de start:

SELECT T. *, # 40; corp SELECT FROM entitati unde id = t.lastid # 41; corp
DE LA
# 40; SELECT e2.type, e2.id, e2.title, e2.active,
conta # 40; * # 41; - IF # 40; e2.type = 3. 1. 0 # 41; CNT, MAX # 40; e.id # 41; lastid, MAX # 40; e.created # 41; lastcreated
De la entitățile de e
STÂNGA ÎNREGISTREAZĂ entități e2
ON e2.id = IF # 40; e.foreparent, e.foreparent, e.id # 41;
UNDE e.active = 1 AND e2.active = 1 AND e.type = 3
GROUP BY e2.id
ORDER BY MAX # 40; e.created # 41; DESC LIMIT 10 # 41; T
COMANDA PRIN DESC lastcreated

Notă: În cazul în care variabila de sistem ia query_cache_wlock_invalidate OFF, blocați tabelul de înregistrare nu șterge interogările stocate în memoria cache care implică masa. În acest caz, prin cache-ul de interogare va fi posibil să se obțină datele din tabelul blocat.

Starea actuală a memoriei cache

Vezi starea memoriei cache, puteți folosi interogarea:

  • Qcache_free_memory - cantitatea de memorie alocată cache.
  • Qcache_hits - numărul de cereri îndeplinite din cache.
  • Qcache_inserts - introduce numărul de cereri în memoria cache.
  • Qcache_lowmem_prunes - numărul de comunicate de memorie din cauza plinătatea cache.
  • Qcache_not_cached - numărul de cereri care nu pot fi stocate în memoria cache.
  • Qcache_queries_in_cache - numărul de cereri din cache-ul acum.

O măsură a eficienței cache este relația Qcache_hits / (Qcache_inserts + Qcache_not_cached).

Cum de a evita utilizarea de cache?

În unele cazuri, este de dorit să nu utilizeze cache. două situații sunt cele mai frecvente:

  • Faci interogările care nu repeta exact și nu doresc să se încarce memoria cache.
  • Faci optimizare interogare și doriți să măsoare timpul execuției sale.

Pentru a executa interogarea fără a utiliza cache-ul este utilizat Directiva SQL_NO_CACHE, care este plasat imediat după instrucțiunea SELECT. De exemplu:

SELECT id SQL_NO_CACHE, titlu, autor, de la știri în cazul în care mesajul ca „%% racletă“;

Notă. Dacă serverul efectuează un număr mare de interogări rapide, cache-ul poate avea un impact negativ asupra performanței. Deoarece cererile de cache obrabytyvaet asociate numai in mod constant cu ea o parte din interogarea va folosi doar un singur nucleu procesor. În cazul în care cache-ul duce la o performanță slabă, acesta trebuie să fie dezactivat în fișierul de configurare.

Metode alternative pentru punerea în cache

În ciuda faptului că a construit în interogare cache este mecanism puternic și clar, în unele cazuri, nu se aplică. Vom descrie cele mai frecvente:

  • Interogarea utilizează funcția personalizată, dar cu toate acestea este determenisticheskim.
  • Optimizarea sub-interogări trebuie să împartă cererea în două cu utilizarea de tabele temporare.
  • Tabelul este actualizat în mod frecvent, invalidarea cache-ul, dar nu face neapărat proba reală.
  • Tabelul este actualizat în mod frecvent domenii care nu sunt utilizate în interogare (care în continuare va invalida cache-ul pentru interogarea tabelul).

Să presupunem că masa de multe ori obnovalyaetsya, dar actualizarea informațiilor privind principalele suficient timpii pe minut. Va prezentam un mod simplu de a cache manual interogare utilizând MEMORY-tabelă. În cazul în care rezultatul este stocat în memorie de masă într-un minut, vom executa interogarea și afișează rezultatele în memoria-tabel.

În primul rând, în cazul în care un tabel cu rezultate stocate în memoria cache nu mai vechi de un cec minut acolo:

SELECT # 40; create_time> acum # 40; # 41; - INTERVAL 1 minut # 41; proaspete DE LA cache_mainpage_query LIMIT 1;

Analyse valoare proaspătă în șirul rezultat. În cazul în care a avut loc = 0 proaspete sau o eroare, atunci o cerere ar trebui să fie efectuate și pentru a face MEMORY-tabel:

DROP TABLE DACĂ EXISTĂ 'cache_mainpage_query';
CREATE TABLE cache_mainpage_query ENGINE = MEMORY
SELECT acum # 40; # 41; AS create_time. DIN. UNDE. ORDER BY. LIMIT. ;

Apoi, la fel ca în cazul c = 0, iar în caz de c = 1, vom efectua cererea și returnează rezultatul la client:

SELECT * de la comanda cache_mainpage_query BY. ;

Notă. În acest exemplu, crearea unui tabel este stocat în fiecare linie a tabelului temporar (coloana create_time). Acest lucru este ineficient pentru ranguri în memorie, astfel încât în ​​cazul în care memoria devine strangulare ar trebui să fie mutat în timp ce cache într-un tabel separat.

În cazul în care o regulă este de sortare complexă (de exemplu, necesită conectarea tabele suplimentare sau calcule pe câmpuri), sortarea apoi interogarea din cache are nevoie de timp. Pentru a evita acest lucru, atunci când numărul liniei interogarea principală:

SET @c: = 0;
CREATE TABLE cache_mainpage_query ENGINE = MEMORY
SELECT acum # 40; # 41; AS create_time, @c: = @ c +1 AS ord. DIN. UNDE. ORDER BY. LIMIT. ;

Eșantionul în acest caz este mai simplu:

SELECT * FROM COMANDA PRIN cache_mainpage_query ord;

Cu memorie legate de tabele o restricție: în aceste tabele nu poate stoca un câmp de tip BLOB / TEXT. Dacă doriți să stocați acest tip de câmp, puteți să creați un tabel-MEMORIE explicit cu câmpuri de tip VARCHAR () până la 65.535 de caractere, sau de a folosi tipul de tabel MyISAM.