assignatura casi: administració de bases de ... -...

Assignatura CASI:Administració de bases de dades

MySQL 5.1

● Tipus de taules (motors de emmagatzemament)● Tipus de dades

Motors emmagatzemament

● MySQL 5.1 disposa de un mecanisme de plugins

● Motors no-MySQL: InnoDB● API per desenvolupar nous

Motors emmagatzemament

● MyISAM● MEMORY● InnoDB● MERGE● EXAMPLE● NDBCLUSTER● ARCHIVE● CSV● FEDERATED

Motor: MyISAM

● No transaccional● Ràpid● Cerques fulltext● Motor per defecte

Motor: MEMORY

● Abans conegut per HEAP● Emmagatzemament en memòria● No transaccional● Inclose per defecte a MySQL

Motor: InnoDB

● Transaccional● Integritat referencial● Recuperació de caigudes de sistema● S'ha d'activar si es compila MySQL● Propietat d'Oracle !!!

Motor: EXAMPLE

● No fa res.● Es poden crear taules però no afegir dades ni

llegir-los.● Exemple per escriure un motor de

emmagatzemament.

Motor: NDBCLUSTER

● Usat per MySQL-Cluster● Des de 5.1.24 es distribueix separadament de

MySQL standard.● Taules repartides en diferents ordinadors● Només per Linux, Solaris i Mac OS X

Motor: ARCHIVE

● Serveix per guardar molta informació ocupant poc espai en disc.

● Compressió amb zlib● No transaccional● No índexs● No DELETE, UPDATE,REPLACE● Si AUTO_INCREMENT des de 5.1.6● Bloqueig per registre

Motor: CSV

● Guarda les dades en fitxer de text.● Columnes separades per comes.● Compatible amb fulles de càlcul.

Motor: FEDERATED

● Permet accedir a taules remotament

MyISAM● Per defecte en MySQL● Bloqueig per taula● No transaccional● Ràpid● Dos variants:

– Estàtiques: columnes de mida fixa● Molt eficient● Dades mes segures

– Dinàmiques: VARCHAR, TEXT, BLOB● Registres ocupen lo necessari● Forats als fitxers de taules

Característiques especifiques de MyISAM

● Dades emmagatzemades amb little-endian (byte menys representatiu primer)– 99% d'ordinadors ens el últims 20 anys

– Només problemes amb sistemes empotrats

– Sense penalització

● Fitxer molt grans– 63 bits

● Límit de registres 232

– Si es compila es servidor amb –with-big-tables llavors (232)2


● Menys fragmentació si es combinan DELETE amb INSERT i UPDATE

● Numero màxim d'índexs 64– Es pot canviar recompilant

● Un índex pot composar-se de 16 columnes màxim

● Longitud màxima de clau es 1000 bytes– Es pot canviar recompilant

● BLOB i TEXT es poden indexar


● Es pot fer servir NULL en columnes indexades.– Això ocupa 0-1 byte per clau

● Les claus numèriques s'emmagatzemen amb el byte més representatiu primer– Això millora la compressió del índex

● Tractament intern de AUTO_INCREMENT per una columna per taula– Més rapidesa

– No reutilització de valors


● INSERT concurrents si es fa en forats del fitxer– Per exemple, en un bloc resultat d'un DELETE

● Cada columna de tipus text pot tenir el seu joc de caràcters

● Eina myisamchk:– Compressió

– Comprovació

– Estadístiques


● VARCHAR auténtic: només es guarda el que cal.– La cadena comença amb dos bytes per la longitud

● Taules amb VARCHAR poden ser de mida fixa o dinàmica

● La suma de columnes VARCHAR i CHAR poden ocupar fins a 64KB

InnoDB

● Transaccional– ACID (Atomicity, Consistency, Isolation, Durability)

● Bloqueig a nivell de registre● Restriccions sobre claus foranes● Recuperació automàtica de caigudes

Característiques especifiques de InnoDB

● Totes les taules i índexs emmagatzemats a un espai de taules– Diferents fitxers

– No es poder reduir

– No es poden copiar a un altre servidor

● Ocupen més espai que MyISAM● No es pot fer servir fulltext● ANALIZE TABLES inexacte amb el número de

registres– I COUNT més lent


● AUTO_INCREMENT porta índex per defecte– No pot ser part de índex multi-columna

● INSERT en registres amb AUTO_INCREMENT bloquegen taula sencera per altres INSERT– Des de 5.1.22 això es pot evitar per INSERT dels

que es coneix el número de registres amb antelació.


● No fer servir LOCK TABLE ... READ/WRITE. – SELECT ... IN SHARE MODE

– SELECT ... FOR UPDATE

● Les taules de la base de dades mysql son MyISAM i no es poden transformar a InnoDB.

● Límit de 1023 transaccions concurrents.● Cost de la llicencia comercial x2

MyISAM vs. InnoDB

● Diferents models:– InnoDB: transaccional, integritat referencial

● Mes lent● Mes espai de disc● Més seguretat

– MyISAM: mes simple, menys features● Més ràpid● Menys espai en disc● Més insegur

MyISAM vs. InnoDB

● Diferents models:– InnoDB: bloqueig nivell de registre

● Més concurrència● Més recurses consumits● Més complexitat

– MyISAM: bloqueig a nivell de taula● Menys concurrencia● Més ràpd● Més simple

MyISAM vs. InnoDB

● Diferents models:– InnoDB: integritat referencial

● Més seguretat● Més lent● Més càrrega al servidor

– MyISAM: sense integritat referencial● Menys seguretat● Més ràpid● Menys càrrega al servidor (però més a l'aplicació)

MyISAM vs. InnoDB

● Per què fer servir InnoDB:– Prestacions: Ha millorar respecte de MyISAM

– Concurrència: Bloqueig a nivell de registre

– Fiabilitat: Transaccions + recuperació de caigudes

– Seguretat: backups amb molta més concurrencia

MyISAM vs. InnoDB

● Per què fer servir MyISAM:– Simplicitat: motor senzill, facilitat per desenvolupar

complements.

– Optimització: motor molt provat i molt optimitzat

– Us de recursos: CPU + disc

Motor NDBCLUSTER

● Aplicacions molt grans● Requeriments especials:

– Alta disponibilitat

– Alta redundància

● Cluster de ordinadors sense memòria compartida

● Dades EN MEMORIA– 5.1.6+ afegeix Disk Data per columnes no

indexades

NDBCLUSTER: Arquitectura

Nodes al Cluster

● Node de administració:– Controla la resta de nodes del cluster

– Iniciar o detenir altres nodes

– Fer backups

– Primer a iniciarse

– Comanda: ndb_mgmd

Nodes al Cluster

● Node de dades:– Emmagatzema les dades

– Es necessiten: nº fragments x nº repliques

– Comanda: ndbd

Nodes al Cluster

● Node MySQL– Interfície amb les aplicacions

– Servidor MySQL + motor NDB

– Accedeix als nodes de dades

– Transparent pels usuaris

– Comanda: ndbcluster

Consideracions amb NBDCLUSTER

● Diferencia amb replicació:– Replicació: 1 Master + N Slaves

– Replicació: transaccions seqüencials

– Replicació: no sincronització

● Pensat per construir-se amb hardware barato● Configuració mínima: 4 ordinadors:

– 1 managment node

– 1 SQL node

– 2 Data nodes (per replicació mínima)

Consideracions amb NBDCLUSTER

● Pensat per ser “infal·lible”– La caiguda d'un node només provoca pèrdua de

unes poques transaccions

– Es pot recuperar d'una caiguda global

● Cada node te els seus recursos no compartits– No fer servir compartició de fitxers

Resum de característiquesFeature MyISAM Memory InnoDB Archive NDB

Storage limits 256TB Yes 64TB No 384EB

Transactions No No Yes No Yes

Locking granularity Table Table Row Row Row

MVCC (snapshot read) No No Yes Yes NoGeospatial datatype Yes No Yes Yes Yes

Geospatial indexing Yes No No No No

B-tree indexes Yes Yes Yes No Yes

Hash indexes No Yes No No YesFull-text search Yes No No No NoClustered indexes No No Yes No No

Data caches No N/A Yes No Yes

Index caches Yes N/A Yes No Yes

Resum de característiques

Feature MyISAM Memory InnoDB Archive NDB

Compressed data Yes No No Yes NoEncrypted data Yes Yes Yes Yes Yes

Cluster database No No No No YesReplication support Yes Yes Yes Yes YesForeign key support No No Yes No NoBackup / point-in-time recovery Yes Yes Yes Yes YesQuery cache support Yes Yes Yes Yes YesUpdate statistics for data dictionary Yes Yes Yes Yes Yes

MySQL 6.0: Falcon engine

● La principal novetat de 6.0 es el nou motor Falcon:– Propietat de MySQL

– Transaccional (similar a InnoDB)

– Pensada per arquitectures 64 bits● Aprofitament de les caches

– Dissenyada per suportar molt tràfic

Falcon: característiques

● MVCC (Multi Version Concurrency Control)– Elimina la necessitat de bloqueigs

● Millors bloqueigs:– Més nivells

– Detecció de deadlocks automàtica

● Optimitzada per funcional amb molts threads● Serial log:

– Transaccions més eficaces

– Recuperació de caigudes

Falcon: característiques

● Millores als B-Trees pels índexs● Compressió de dades “on the fly”● Més caches:

– Dades

– Índexs

Tipus de dades

● Numèrics: sencers, floats, bits, booleans● Cadenes de caràcters: fixes, variables● Dates i temps● GIS

Sencers: xxxINT

Sencers: xxxINT

● INT(n): mostra mínim n caràcters– S'emplena amb espais

– Amb ZEROFILL s'amplia amb 0

● AUTO_INCRMENT– NOT NULL

– PRIMARY KEY o NULL

– Overflow = no més inserst ! Fer servir 64 bits

– LAST_INSERT_ID()● Per INSERT múltiple retorna el primer !

BIT i BOOL

● BOOL es sinònim de TINYINT● BIT pot guardar fins a 64 bits (des de 5.0.2)

Coma flotant

Dates i temps

Dates i temps

● Des de 5.0.2 hi ha un mecanisme per validar dates. Depen dels següents atributs:

Cadenes de caràcters

Jocs de caràcters● Per canviar:

– CHARACTER_SET nom_joc COLLATE ordenacio

● Jocs més usats:– Llatins:Latin1 (ISO-8859-1), Latin2 (eropa del est),

Latin15 (Latin1 + €)● 1 byte● Cap els conté tots

– Unicode● 2 bytes● Conté gairebé tots (asiàtics inclosos)● UTF-16 (problema amb el 0 al segon byte)● UTF-8

Joc UTF-8

● 99% 1byte– Si 1 byte -> primer bit 0

– Si 2 bytes -> primer bit 1

Binaris: xxxBLOB

COLLATION

| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |

| utf8_bin | utf8 | 83 | | Yes | 1 |

| utf8_unicode_ci | utf8 | 192 | | Yes | 8 |

| utf8_icelandic_ci | utf8 | 193 | | Yes | 8 |

| utf8_latvian_ci | utf8 | 194 | | Yes | 8 |

| utf8_romanian_ci | utf8 | 195 | | Yes | 8 |

| utf8_slovenian_ci | utf8 | 196 | | Yes | 8 |

| utf8_polish_ci | utf8 | 197 | | Yes | 8 |

| utf8_estonian_ci | utf8 | 198 | | Yes | 8 |

| utf8_spanish_ci | utf8 | 199 | | Yes | 8 |

| utf8_swedish_ci | utf8 | 200 | | Yes | 8 |

| utf8_turkish_ci | utf8 | 201 | | Yes | 8 |

| utf8_czech_ci | utf8 | 202 | | Yes | 8 |

| utf8_danish_ci | utf8 | 203 | | Yes | 8 |

| utf8_lithuanian_ci | utf8 | 204 | | Yes | 8 |

| utf8_slovak_ci | utf8 | 205 | | Yes | 8 |

| utf8_spanish2_ci | utf8 | 206 | | Yes | 8 |

| utf8_roman_ci | utf8 | 207 | | Yes | 8 |

| utf8_persian_ci | utf8 | 208 | | Yes | 8 |

| utf8_esperanto_ci | utf8 | 209 | | Yes | 8 |

| utf8_hungarian_ci | utf8 | 210 | | Yes | 8 |

GIS

● Disponible per MyISAM, InnoDB, NDB, y ARCHIVE

● Índex només en MyISAM● OpenGIS. Cada objecte:

– Associat a un sistema de referència

– Pertany a una classe geomètrica

Jerarquia geomètrica

● Geometry (ni)

– Point (i)– Curve (ni)

● LineString (i)–Line (i)–LineRing (i)

– Surface (ni)● Polygon (i)

Jerarquia geomètrica

– GeometryCollecion (i)● MultiPoint (i)● MultiCurve (ni)

–MultiLineString (i)● MultiSurface (ni)

–MultiPolygon (i)

Formats pels dades GIS

● Formats fets servir:– WKT (Well Known Text)

– WKB (Well Known Binary)

● Internament MySQL fa servir un format intermig.


● WKT:– POINT(15 20)

– LINESTRING(0 0, 10 10, 20 25, 50 60)

– POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))

– MULTIPOINT(0 0, 20 20, 60 60)

– MULTILINESTRING((10 10, 20 20), (15 15, 30 15))

– MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0)),((5 5,7 5,7 7,5 7, 5 5)))

– GEOMETRYCOLLECTION(POINT(10 10), POINT(30 30), LINESTRING(15 15, 20 20))

–


● WKB– Similar a un BLOB

– Fa servir sencers de 1-byte i 4-bytes, i floats de 8-bytes

Per exemple, el valor WKB que correspon a POINT(1 1) és:

0101000000000000000000F03F000000000000F03F

Que correspon a:

Byte order : 01WKB type : 01000000X : 000000000000F03FY : 000000000000F03F

Manipular dades geomètriques

● Creació:– CREATE TABLE geom (g GEOMETRY)

● Afegir i treure columnes:– ALTER TABLE geom ADD pt POINT

– ALTER TABLE geom DROP pt

● Afegir dades:– INSERT INTO geom VALUES

(GeomFromText('POINT(1 1)'));

– SET @g = GeomFromText('POINT(1 1)');

INSERT INTO geom VALUES (@g);


● Afegir dades amb funcions específiques:– SET @g = 'POINT(1 1)';

INSERT INTO geom VALUES

(PointFromText(@g))

● Llegir dades:– SELECT g FROM geom

– SELECT AsText(g) FROM geom

– SELECT AsBinary(g) FROM geom


● LineString– GLength(ls)

– IsRing(ls)

● MultiLineString– IsClosed(mls)

● Polygon– Area(p)

– ExteriorRing(p)

– InteriorRing(p, n)


● MultiPolygon– Area(mp)

– Centroid(mp)

– PointOnSurface(mp)


● Funcions per crear objectes a partir d'altres● Operadors espacials:

– ConvexHull, Difference, Union, ...

● Relacions espacials entre objectes– Contains, Distance, Equals, Intersects, ...

● Funcions per operacions amb caixes englobants (MBR Minimum Bounding Rectangle)

assignatura casi: administració de bases de ... -...

Documents