Pràctiques en MySQL!

Farem un repàs de MySQL i veurem perquè és una molt bona alternativa per les pràctiques a la UAB, ja que permet posar en pràctica totes les optimitzacions estudiades a BD i BD2.

1. MySQL 5

SGBD relacional multifil i multiusuari, creat per MySQL AB. Des de gener de 2008 pertany a Sun Microsystems.

S’ofereix sota la llicència GPL per qualsevol ús compatible amb la llicència, però per usos privatius (si es vol llicenciar una modificació de MySQL amb una llicència privativa) té una llicència comercial.

MySQL suporta múltiples motors d’emmagatzematge: MyISAM, Merge, InnoDB, BDB, Memory/heap, MySQL Cluster, Federated, Archive, CSV, Blackhole i Example, permetent l’usuari escollir la que més s’adeqüi a cada taula de la base de dades. Fins hi tot és relativament senzill afegir un sistema d’emmagatzematge propi.

1.1 Portabilitat

  • Escrit en C i C++.
  • Plataformes suportades: GNU, Windows*, AIX, *BSD, Solaris, SunOS, OS/2, Netware, Mac OS, HP-UX, QNX, IRIX, Tru64, OpenVMS, UnixWare.
  • APIs en els llenguatges de programació: C, C++, C#, Pascal, Delphi, Eiffel, Smaltalk, Java, Lisp, Perl, PHP, Python, Ruby, Gambas, REALbasic, FreeBASIC, Tcl.
  • Existeix una interfície ODBC i JDBC.
  • El servidor està disponible com a biblioteca i pot ser incrustat en aplicacions autònomes.
  •  Connectivitat: Utilitza sockets TCP/IP, named pipes a Windows i Unix sockets als *NIX.

 1.2 Principals característiques

  • Suport d’un ampli subconjunt del ANSI SQL 99 amb algunes extensions
  • Transaccions (amb els motors InnoDB, DBD i Cluster) i punts de recuperació
  • Procediments emmagatzemats
  • Disparadors
  • Cursors
  • Vistes actualitzables
  • Query caching
  • Sub-selects (selects anidats)
  • Suport del tipus VARCHAR
  • Mode Strict
  • Suport per SSL
  • Shared-nothing clustering mitjançant MySQL Cluster
  • Multi-thread midjançant els threads del kernel (ús de multicores)
  • Sistema de reserva de memòria basat en threads
  • Indexació i cerca en camps de text complet
  • Taules en disc B-tree per cerques ràpides amb compressió d’índex
  • Joins ràpids mitjançant multi-join d’un pas optimitzat
  • Taules hash en memoria utilitzades com taules temporals
  • Suport de X/Open XA de transaccions distribuides
  • Accés a la informació d’administració mitjanánt la taula INFORMATION_SCHEMA
  • Sistema de replicació (amb múltiples esclaus per mestre)
  • Sistema flexible de privilegis i claus encirptades amb verificació basada en host

1.3 Escalabilitat i ús

  • Suporta fins a 64 índexs per taula, cadascun pot tenir de 1a a 16 columnes o parts de columnes. Longitud màxima de clau de 1000 bytes. Un índex pot utilitzar prefixes d’una columna.
  • Suporta grans bases de dades; ús comprovat de servidors MySQL amb 60.000 taules i porp de 5.000.000.000.000 registres (5 bilions).
  • Usuaris destacats: Amazon, NASA, Google AdWords, Yahoo, Nokia, Slashdot, Wikipedia, Digg, …

2. Optimització de consultes

MySQL permet la optimització de consultes en molts nivells, des de la selecció del motor (com s’enregistren i s’organitzen les dades), fins a construccions òptimes de consultes SQL per aprofitar tots els mecanismes d’optimització que incorpora.

2.1 Administració del servidor

MySQL té un fitxer de configuració amb molts paràmetres personalitzables, en funció de la màquina on ha de funcionar o de les preferències per la optimització.

Les principals opcions que influencien les consultes son les aplicades sobre threads: thread_cache_size, thread_concurrency i thread_stack, que determinaràn el rendiment del SGBD, també es poden configurar els tamanys de la cache i dels buffers: table_cache, query_cache_size, key_cache_block_size. key_buffer_size, join_buffer_size, sort_buffer_size, etc.

Cal destacar els paràmetres optimizer_prune_level i optimizer_search_depth, el primer controla la heurística aplicada durant la optimització de consultes per no fer els plans parcials menys prometedors en l’espai de cerca de l’optimitzador, si es posa a 0 desactiva la opció.
optimizer_search_depth defineix la profunditat màxima realitzada per l’optimitzador de consultes, quan més gran sigui el valor millors plans de consulta obté, però implica més temps generar el pla d’execució.

Moltes d’aquestes opcions es poden modificar en temps d’execució com a variables del sistema, per tant permet definir els paràmetres en funció de la consulta, o el grup de consultes que s’hagin d’aplicar a la base dades.

2.2 Selecció del motor d'emmagatzematge

MyISAM

Motor d’emmagatzematge per defecte, basat en el codi ISAM (la versio 5 ja no suporta ISAM). Cada taula es guarda en 3 fitxers: .frm (definició), .MYD (dades) i .MYI (índexs). Els 3 fitxers poden estar en discs diferents.

Algunes de les característiques principals:

  • Portable a la majoria de màquines i sistemes operatius
  • Suporta fitxers llargs (fins a 63 bits), 64 índex per taula i claus de 1000 bytes
  • Poden indexar-se tots els tipus de camps
  • Tractament intern del camp AUTO_INCREMENT, millora un 10% el rendiment de INSERT.
  • Les taules es poden comprimir (per camps) i arreglar en cas de fallada amb myisamchk.
  • Suport real pel tipus VARCHAR, amb màxim de 64KB.

· InnoDB:

Motor d’emmagatzematge transaccional (conforme ACID). Guarda totes les taules i índexs en un únic espai de taules, que pot estar en diferents fitxers (en diferents directoris). Les taules poden ser de qualsevol tamany.

Algunes de les característiques principals:

  • Capacitats de commit, rollback i recuperació de fallides.
  • Realitza bloquejos a nivell de fila
  • Proporciona funcions de lectura consistent sense bloqueig en sentències SELECT.
  • Suporta restriccions de clau forània FOREIGN KEY.
  • Dissenyat per obtenir el màxim rendiment al processar grans volums de dades, sembla oferir gran eficiència en el ús de CPU.

Altres motors d’emmagatzematge:

  • Merge: Col·lecció de taules idèntiques que es poden utilitzar com una sola, permet tenir les dades dividides, de manera que part d’aquestes pot estar comprimida, en un altre disc. Permet fer cerques fraccionades.
  • Heap: Crea taules emmagatzemandes en memòria. Útils per taules temporals.
  • MySQL Cluster: Permet clustering de BD en memòria en un entorn de no compartició.
  • Archive: Optimitzat per guardar grans quantitats de dades sense índex. (no permet UPDATE)
  • Federated: Motor que accedeix a dades de taules de BD remotes en lloc de taules locals.
  • BDB: Motor transaccional Berkeley DB.
  • MaxDB: Motor utilitzat per entorns SAP (mySAP), suporta operacions continues de TeraBytes.
  • CVS: Utilitza fitxers de text amb valors separats per comes, al inici del fitxer hi ha la descripció.
  • Example: Motor de proves que no fa res, serveix d’exemple per crear nous motors.

2.3 Disseny de la base de dades

El rendiment de la base de dades depèn directament del tamany d’aquesta, s’han de definir els camps de les taules amb el tamany just de les dades que s’hi hagi d’inserir.

Declarar sempre que es pugui columnes NOT NULL, excepte en els casos que sigui estrictament necessari guardar valors buits a la base de dades.

Les claus primàries han de ser el més petites possibles, per identificar els registres fàcil i eficientment.

Per millorar el rendiment global s’ha de procurar crear únicament els índexs necessaris, creant índexs compostos, o índexs de prefixes de camps en lloc de crear índexs de camps de dades.

2.4 Creació d’índexs

Tots el tipus de columnes de MySQL poden ser indexades.

El tipus d’índex depèn del motor d’emmagatzematge, amb MyISAM i InnoDB els índex son HASH i BTREE, altres motors permeten índexs RTREE.

MySQL5 permet 2 índex especials, FULLTEXT i SPATIAL. Amb les busques de text complet (FULLTEXT) es poden realitzar cerques en camps de text indexats amb la sentència MATCH(índexs) AGAINS(‘text a buscar’).

SPATIAL permet fer cerques d’índex per dades espacials (dades de classes geomètriques).

MySQL optimitza l’accés als índex amb l’”index merge optimitzation”, on es tracten les interseccions, unions i unions ordenades.

Per forçar l’ús de determinats índexs d’una taula s’utilitza la sentència FORCE INDEX o IGNORE INDEX. Amb la sentència EXPLAIN podem veure quins índex s’estan utilitzant.

Si s’utilitza un índex que no coincideix amb la taula l’optimitzador l’ignorarà, per redefinir l’índex s’ha d’utilitzar la sentència ANALYZE TABLE.

2.5 Pla d’execució

Per defecte l’ordre establert depèn dels índex existents a les taules, l’ordre indicat a la consulta i de l’optimitzador de consultes de MySQL (mitjançant el catàleg), per forçar un ordre determinat dels JOIN s’ha d’especificar a la consulta SQL amb STRAIGHT_JOIN.

Amb EXPLAIN podem veure com MySQL resol els JOIN, l’ordre i el tamany en registres de cada un.

2.6 Catàleg

El catàleg que utilitza MySQL està a la base de dades INFORMATION_SCHEMA, hi podem trobar estadístiques sobre els índexs definits, la cardinalitat, les particions, l’ordre i el tipus d’índex, el SGBD ho utilitzarà per escollir el pla d’execució. A la taula TABLES hi trobem paràmetres que podem definir del catàleg sobre les taules.

Dins la BD INFORMATION_SCHEMA podem veure pràcticament tota la configuració del SGBD: taules, privilegis, sessions, particions, triggers, relacions, vistes, rutines, etc. Fins hi tot podem veure els motors d’emmagatzematge disponibles i les seves opcions.

2.7 Consulta SQL

MySQL incorpora diferents optimitzacions per les consultes SQL:

Clàusules WHERE

Elimina parèntesis innecessaris, substitueix per constants sempre que pot, elimina condicions redundants i comprova que les comparacions amb valors constants siguin factibles. Pot crear taules temporals intermitges en memòria. Determina el millor índex, en el cas que aquest sigui més eficient que utilitzar un table scan.

  • IS NULL: Utilitza la optimització de cerca de constants i índexs o rangs per buscar valors NULL.
  • DISTINCT: Utilitza una taula temporal amb combinació al ORDER BY.
  • LEFT/RIGHT JOIN: Calcula l’ordre amb el que s’ha de realitzar el JOIN i elimina permutacions.
  • ORDER BY: Aplica algorisme filesort si no es poden utilitzar índexs.
  • GROUP BY: Crea taules temporals amb accés per índex i utilitza ‘Loose / Tight index scan’.
  • LIMIT: Limita l’espai de les taules temporals de les consultes.

3. Cas practic

Optimitzarem una consulta modificant els índex de les taules i el pla d’execució.

Per fer les proves hem creat una base de dades amb 4 taules MyISAM.

A la taula clientes hem inserit 25.000 registres, a productes 110.000, a factures 500.000 i a factures_det 1.249.749. Així podem veure el rendiment real de la base de dades dins el sistema, ja que contarem per cada exemple el temps total de les consultes.

Per defecte MySQL crea un índex per totes les claus primàries, donat que les hem creat totes amb autonumèrics es podrà reduir considerablement el temps de cerca dins els índex ja que la taula sempre estarà ordenada.

Farem una consulta que ens retornarà els diferents noms de producte que s’han facturat al client amb DNI 44696077:

SELECT DISTINCT(P.producto) FROM clientes C, facturas F, facturas_det FD, productos P WHERE C.dni=’44696077′ AND C.id_cliente=F.id_cliente AND FD.id_factura = F.id_factura AND FD.id_producto=P.id_producto

select_type

table

type

possible_keys

key

key_len

ref

rows

filtered

Extra

SIMPLE

FD

ALL

NULL

NULL

NULL

NULL

1249749

100.00

Using temporary

SIMPLE

P

eq_ref

PRIMARY

PRIMARY

4

bd2.FD.id_producto

1

100.00

SIMPLE

F

eq_ref

PRIMARY

PRIMARY

4

bd2.FD.id_factura

1

100.00

Distinct

SIMPLE

C

eq_ref

PRIMARY

PRIMARY

4

bd2.F.id_cliente

1

100.00

Using where; Distinct

Resultat: 60 registres, la consulta tarda 11.4941 segons

Podem observar que l’ordre dels JOIN és molt ineficient, això és degut a que no tenim un índex a la taula de Clients per la cerca que fem, per solucionar-ho afegirem com a índex (amb ADD_INDEX) el camp DNI de clients.

Fent la mateixa consulta:

select_type

table

type

possible_keys

key

key_len

ref

rows

filtered

Extra

SIMPLE

C

ref

PRIMARY,DNI

DNI

12

const

1

100.00

Using where; Using temporary

SIMPLE

FD

ALL

NULL

NULL

NULL

NULL

1249749

100.00

Using join buffer

SIMPLE

F

eq_ref

PRIMARY

PRIMARY

4

bd2.FD.id_factura

1

100.00

Using where

SIMPLE

P

eq_ref

PRIMARY

PRIMARY

4

bd2.FD.id_producto

1

100.00


Resultat: 60 registres, la consulta tarda 1,3390 segons

Inserint l’índex id_cliente a factures farem que el planificador resolgui primer el JOIN amb aquesta taula, minimitzant la cerca dins el milió de registres per cada factura seleccionada a 500.000 registres per un sol client, rebaixant el temps de consulta a 0,2 segons.

Finalment si afegim com a índex les IDs de factures_det podem reduir els registres consultats a desenes, ja que podrà fer tots els joins amb els índex definits.

Fent la mateixa consulta:

select_type

table

type

possible_keys

key

key_len

ref

rows

filtered

Extra

SIMPLE

C

ref

PRIMARY,DNI

DNI

12

const

1

100.00

Using where; Using temporary

SIMPLE

F

ref

PRIMARY,id_cliente

id_cliente

4

bd2.C.id_cliente

20

100.00

SIMPLE

FD

ref

id_factura,id_producto

id_factura

4

bd2.F.id_factura

2

100.00

SIMPLE

P

eq_ref

PRIMARY

PRIMARY

4

bd2.FD.id_producto

1

100.00

Resultat: 60 registres, la consulta tarda 0.0012 segons

D’altra banda podem veure que l’ordre de la taula factures_det no té sentit, ja que s’ordena per id_facturas_det, en lloc de id_factura. Forçarem l’ordre de la taula factures_det:

# myisamchk facturas_det –sort-index –sort-records=2

– Sorting records for MyISAM-table ‘facturas_det’

Data records: 1249749 Deleted: 0

– Sorting index for MyISAM-table ‘facturas_det’

Al fer la consulta -> resultat: 60 registres, la consulta tarda 0.0008 segons

Podríem fer el mateix a la taula clientes, però mantenir la taula ordenada pot tenir un cost excessiu si s’apliquen moltes modificacions, MySQL permet fer particions de les taules amb la sentència PARTITIONS, que insereix els registres dins d’una partició d’una taula segons el criteri d’un camp.

Per exemple en aquest cas podríem haver fet particions a factures_det en el camp id_factura (0 < id_factura < 250000 i 250000 < id_factura < 500000), això té un cost mínim per les modificacions i és molt útil per les cerques.

4. Conclusions

Després de repassar bé el manual de MySQL (l’introductori evidentment), ens adonem que tot i que el SGBD ens ofereix molts algorismes d’optimització, els paràmetres per què s’apliquin de manera òptima poden ser més complicats d’aconseguir que els mateixos algorismes.

MySQL permet aplicar pràcticament tot el que hem donat sobre el processament de consultes a bases de dades, tot i que no hem tingut temps de veure com funcionen totes les opcions que hem utilitzat per fer la optimització: el motor d’avaluació i el d’emmagatzematge, gracies a la teoria donada sabem com s’aconsegueix un model senzill, que ens ha permès entendre en poques hores el manual de processament de consultes de MySQL.

Penjaré més informació sobre optimitzacions de MySQL informació quan acabi el PFC que estic fent sobre Ibexestudio , hi ha estudi complet d’una consultes de d’Ibexrentacar i Ibextravelpack on s’han aplicat més de 10 optimitzacions diferents.