Assignatura CASI: Administració de bases de ... -...
Transcript of 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: 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.
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
Característiques especifiques de MyISAM
● 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
Característiques especifiques de MyISAM
● 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
Característiques especifiques de MyISAM
● 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
Característiques especifiques de MyISAM
● 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
Característiques especifiques de InnoDB
● 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ó.
Característiques especifiques de InnoDB
● 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
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
● 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 !
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
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.
Formats pels dades GIS
● 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))
–
Formats pels dades GIS
● 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);
Manipular dades geomètriques
● 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
Manipular dades geomètriques
● LineString– GLength(ls)
– IsRing(ls)
● MultiLineString– IsClosed(mls)
● Polygon– Area(p)
– ExteriorRing(p)
– InteriorRing(p, n)