Optimització de consultes en MySQL (unes notes)

1. Optimització de consultes en MySQL (unes notes) Toni Navarrete Enginyeria del Software II – UPF 2007

2. Optimitzador de consultes • MySQL també té un optimitzador que determina quina és la millor manera d’executar una consulta • Basat en costos • Intenta minimitzar el nombre de files a processar • Basat en “força bruta” (analitza totes les possibilitats i en tria la millor)

3. Cache de consultes • Permet que la consulta no s’executi sinó que la resposta s’obtengui de la cache • Al fitxer ini: • query_cache_type = 1 (sempre amb cache) • query_cache_type = 2 (només quan s’especifiqui) • select sql_cache * from T; • select sql_non_cache * from T;

4. Explicació de plans de consulta.Exemple amb un accés complet a una taula mysql> explain select poblacio1991 from municipi\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: municipi type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 8404 Extra: 1 row in set (0.00 sec)

5. Explicació de plans de consulta.Descripció del pla • Id: identifica la taula en la query (genera un registre per cada taula involucrada) • Select_type: el rol de la taula en la query: • SIMPLE • PRIMARY • UNION • DEPENDENT UNION • SUBSELECT • DERIVED • Table: el nom dela taula

6. Explicació de plans de consulta.Descripció del pla • Type: el tipus d’accés a la taula: • ALL (totes les files) • CONST (a partir d’una constant) • RANGE • REF (en un join, la foreign key) • EQ_REF

7. Explicació de plans de consulta.Descripció del pla • Possible_keys: els possibles índexs que pot usar • Key: el que n’utilitza • Key_len: el tamany en bytes de l’índex • Ref: el valor (o columna) que s’utilitza per fer la cerca (const si és un valor fixat) • Rows: el número de files que s’espera que s’hagin de processar • Extra: informació addicional

8. Explicació de plans de consulta.Exemple amb un join i índexs mysql> explain select m.* from municipi m, comunitat c where c.nom='Illes Balears' and c.ca_id=m.ca_id\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: c type: ref possible_keys: index_canom key: index_canom key_len: 53 ref: const rows: 1 Extra: Using where *************************** 2. row *************************** id: 1 select_type: SIMPLE table: m type: ref possible_keys: index_mcaid key: index_mcaid key_len: 5 ref: municipis.c.ca_id rows: 210 Extra: Using where 2 rows in set (0.03 sec)

9. Hints • Tot el que va entre /*! i */ no és SQL i el gestor s’ho “saltarà” si no ho entén • Exemple: select /*! sql_cache */ * from T; • Forçar índex: • Un dels possibles: • Select * from T use index(ca_id,nom,…); • Forçar l’ús d’un índex concret: • Select * from T force index(nom_index); • Ignorar un índex concret: • Select * from T ignore index(…); • Forçar ordre del join: • Select * from T1 straight_join T2 where …

10. Detecció de queries “lentes” • En el fitxer ini podem establir un límit de temps a partir del qual una query es considera “lenta” (per exemple, 2 segons) • En aquests cassos s’escriu a un registre de log • No sempre indica un problema, però sovint sí