Notiuni avansate MySQL - LVLE 2009

Noţiuni avansate MySQL

Noţiuni avansate MySQL● MySQL ● Arhitectura MySQL● Tabele● Tipuri de date● Indecşi● Parti ionare ț● Declan atoareș

(Triggers)● Proceduri stocate


Mihai Oaida <[email protected]> 01

● Vizualizări (Views)

● Descrierea comenzilor(DESCRIBE )

● Jurnal de interogări lente (Slow query log)

● Cache de interogări(Query cache)

● Performanţă

MySQL

3.23 - vechi4.1x - stabil, puţine funcţionalităţi5.0x - GA5.1 - GA5.4 - Beta 6.x - ?



Arhitectura MySQL


Mihai Oaida <[email protected]> 03http://dev.mysql.com/doc/refman/5.1-maria/en/images/PSEArch.png

Tabele

Tipuri de tabele● MyISAM● InnoDB● Memory / HEAP ● MERGE, MRG_MyISAM● Black hole● CSV● ARCHIVE



CREATE TABLE t( columns) ENGINE = sau ALTER TABLE t ENGINE =

Tipuri de date - int

TINYINT 1byteSMALLINT 2BytesMEDIUMINT 3 bytesINT 4bytes

-2147483648 +2147483647BIGINT 8 bytes



Probleme comune

INT(4) – ZEROFILL – ex 0001

Tipuri de date - dată şi timp

DATETIME 2009-07-09 21:15:15DATE 2009-07-09TIMESTAMP la fel ca DATE dar în raza timestamp

de la 1 ian 1970 până în 2038

TIME 21:15:15



Tipuri de date - şir

CHAR - consumă spa iu fixțVARCHAR - consumă spa iu în funcţie de lungimeț• 0-255 caractere• coloane cu encoding



BINARYVARBINARY

• date binare

TINYBLOB BLOB MEDIUMBLOB LONGBLOB

TINYTEXT TEXTMEDIUMTEXTLONGTEXT

Tipuri de date – ENUM şi SET

Atunci când lista de valori este fixăConsumă puţin spaţiu



CREATE TABLE oameni{id TINYINT PRIMARY KEY AUTO_INCREMENT,nume VARCHAR(25),sex ENUM('masculin','feminin')

}INSERT INTO oameni(nume,sex) VALUES('Ion Popescu','masculin');

CREATE TABLE newsletter{id TINYINT PRIMARY KEY autoincrement,nume VARCHAR(25),categorie SET('evenimente','stiri','sport')

}INSERT INTO newsletter(nume,categorie) VALUES('Ion Popescu','stiri,sport');

Indecşi

• Ordonăm tabelul după una sau mai multe coloane (multiple index)

• Tipuri de indecşi- PRIMARY KEY

- Index

- Unique

- Full Text

• Tipuri de algoritmi : BTREE sau HASH



CREATE INDEX i_name ON table_name(col1,col2,..)

Full text index

● Căutare în texte● Returnează rezultatele în ordinea relevan eiț



SELECT id,titlu FROM table WHERE MATCH(titlu) AGAINST('xml')

SELECT id,titlu FROM table WHERE MATCH(titlu) AGAINST('xml' WITH QUERY EXPANSION)

Parti ionareț• Op iune de creare a tabelelor ț• Distribuie tabel pe mai multe parti ii după ț valorile unei coloane sau a unei expresii

• Trasparent pentru utilizator• Cre te viteza de execu ie a interogărilorș ț



• Nu are suport pentru chei străine• Suportă maxim 1024 de parti ii pentru un tabelț• MySQL 5.1

Op iuni de parti ionareț ț– RANGE valorile se încadrează în anumite intervale– LIST valorile se încadrează într-o listă de valori– HASH după o func ie specificată de utilizatorț– KEY similar cu HASH, func ia este furnizată de serverț

* pentru HASH i KEY se specifică numărul de parti iiș ț

Parti ionare – exemplu 1țCREATE TABLE users( id MEDIUMINT AUTO_INCREMENT, username VARCHAR(50), passsword CHAR(32), email VARCHAR(50), PRIMARY KEY (id)

)ENGINE=MyISAMPARTITION BY RANGE(id)( PARTITION p0 VALUES LESS THAN (1000), PARTITION p1 VALUES LESS THAN (2000), PARTITION p2 VALUES LESS THAN MAXVALUE )



Parti ionare – exemplu 2țCREATE TABLE users( id INT AUTO_INCREMENT, username VARCHAR(50), passsword CHAR(32), email VARCHAR(50), PRIMARY KEY (id)

)ENGINE=MyISAMPARTITION BY KEY(id)PARTITIONS 10



Trigger

Cod sql asociat unui tabel Se apelează la un anumit eveniment



CREATE TRIGGER trig_name {BEFORE| AFTER} {INSERT,REPLACE,UPDATE, DELETE} on t

FOR EACH ROW BEGIN ..sql..END;

Trigger - Exemplu

CREATE TRIGGER updateCateg AFTER INSERT ONproduse FOR EACH ROW BEGIN UPDATE cats SET nr=nr+1 WHERE id=NEW.cat_id;END;



Views

CREATE VIEW view_name AS [query]DROP VIEW view_name



CREATE VIEW stats_l AS SELECT l.id,l.nume, ( SELECT count( c.id ) FROM comentarii c WHERE c.lectie_id = l.id ) AS nr_comentarii , ( SELECT count( r.id ) FROM rezolvari r WHERE r.lectie_id = l.id ) AS nr_rezolvari FROM lectii l ORDER BY id ASC

SELECT * FROM stats_l

Proceduri stocate

Subrutine care se reţin în baza de date

Folosite pentru ● A testa datele ● A muta SQL din logica aplicaţiei în db ● A minimiza traficul dintre client şi db



Exemplu procedură stocatădelimiter // DROP PROCEDURE IF EXISTS colavg// CREATE PROCEDURE colavg(IN tbl CHAR(64),IN col CHAR(64))

READS SQL DATA COMMENT 'Selects the avg of column col in table tbl' BEGIN SET @s = CONCAT('SELECT AVG(' , col , ') FROM ' , tbl); PREPARE stmt FROM @s; EXECUTE stmt; END; // delimiter ;



CALL colavg('t_note','nota');

DESCRIBE

• Arată planul de execuţie ales de optimizator• Arată pentru fiecare tabel din sql : indecşii

folosiţi, tipul căutării, numărul de rânduri prin care caută, etc



• Câmpuri Select_type tip interogare Table nume tabel Possible_keys indec i lua i în considerareș ț Key index-ul ales Key_len lungimea index-ului Rows nr. aprox. de rânduri prin care caută Extra file sort, tmp table, etc

• DESCRIBE [sql]

DESCRIBE - exempluDESCRIBE SELECT l.id,l.nume, (SELECT count( c.id ) FROM comentarii c WHERE c.lectie_id = l.id) AS nr_comentarii, (SELECT count( r.id ) FROM rezolvari r WHERE r.lectie_id = l.id) AS nr_rezolvariFROM lectii l ORDER BY id ASC


Mihai Oaida <[email protected]>

+----+--------------------+-------+------+---------------+| id | select_type | table | type | possible_keys |+----+--------------------+-------+------+---------------+| 1 | PRIMARY | l | ALL | NULL | | 3 | DEPENDENT SUBQUERY | r | ref | lectie_id || 2 | DEPENDENT SUBQUERY | c | ref | lectie_id |+----+--------------------+-------+------+---------------+

-----------+---------+-----------------+------+----------------+ key | key_len | ref | rows | Extra |-----------+---------+-----------------+------+----------------+ NULL | NULL | NULL | 8 | Using filesort | lectie_id | 2 | web-1-2008.l.id | 182 | | lectie_id | 2 | web-1-2008.l.id | 91 | | -----------+---------+-----------------+------+----------------+

20

Slow query log

Locaţie my.cnf



log_slow_queries = /var/log/mysql/my-slow.loglong_query_time = 2log-queries-not-using-indexes

Jurnal de interogări lente

Query cache

Locaţie my.ini



query_cache_limit = 64Mquery_cache_size = 64M

Nu trebuie setată prea mare

Raportul read/write – invalidarea cache-ului

Trebuie urmărit cache hits

Performanţă

• Datele reţinute pe tipul cel mai mic md5 hash – binary(16) pack() ip – int 4 bytes ip2long()

• Indecşi mici – mai multă informaţie încape într-un bloc de memorie • Indecşi pe cheia de join• Binary log off



NU SELECT * FROM table ORDER BY RAND() LIMIT 1 SELECT COUNT(*) FROM table SELECT DISTINCT column FROM table

Resurse

Documenţatiehttp://dev.mysql.com/doc/refman/5.0/en/http://dev.mysql.com/doc/refman/5.1/en/http://dev.mysql.com/tech-resources/articles/

Uneltehttp://dev.mysql.com/workbench/http://dev.mysql.com/downloads/gui-tools/5.0.htmlhttp://munin.projects.linpro.no/

Lecturăhttp://www.mysqlperformanceblog.com/



http://dev.mysql.com/doc/refman/5.0/en/




http://dev.mysql.com/tech-resources/articles/

http://dev.mysql.com/workbench/



http://dev.mysql.com/downloads/gui-tools/5.0.html

http://munin.projects.linpro.no/



http://www.mysqlperformanceblog.com/



Întrebări?



Notiuni avansate MySQL - LVLE 2009

Documents

Transcript of Notiuni avansate MySQL - LVLE 2009