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 <mihai.oaida@gmail.com> 01

● Vizualizări (Views)

● Descrierea comenzilor(DESCRIBE )

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

● Cache de interogări(Query cache)

● Performanţă

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

Arhitectura MySQL

Mihai Oaida <mihai.oaida@gmail.com> 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;

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 <mihai.oaida@gmail.com>

+----+--------------------+-------+------+---------------+| 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 | | -----------+---------+-----------------+------+----------------+

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/

Întrebări?

Notiuni avansate MySQL - LVLE 2009

Documents

Transcript of Notiuni avansate MySQL - LVLE 2009

CEM Notiuni

MySQL si PHP - web.ceiti.md · PHP/MySQL PASUL 1 – Introducere Introducere Pentru multă lume, principala raŃiune de a învăŃa un limbaj precum PHP ar fi interacŃiunea pe

Laborator 5. PHP si MySQL - hera.utcluj.rohera.utcluj.ro/comertelectronic/wp-content/uploads/2017/10/... · 1. Introducere MySQL este cea mai populara aplicatie open-source de baze

Php Si Mysql

curs php si mysql

Curs Radiocomunicatii Avansate

Baze de Date - davos.science.upm.rodavos.science.upm.ro/~ccalin/seminar/s1_Notiuni_Introductive.pdf · MySQL Conectarea la MySQL Baze de Date Calin-Adrian COMES˘ 1 1Universitatea

Baze de Date MySQL

Initiere in PHP & MySQL [Romana]

HTML Php Mysql

€¦ · Proiectarea, crearea administrarea bazelor de date în Microsoft Access. Bazele HTML. Concepte de programare PHI), interactiunea PI-IP — MySQL, script-uri MySQL. Bibliografia

Proiect MySQL

Tehnologii avansate

Mysql Tutorial

Modelarea Suprafetelor Avansate

Notiuni de

materiale avansate

Manual Notiuni Avansate Excel 2

Tehnici avansate de programare - profs.info.uaic.roacf/java/slides/ro/javame_slide.pdf · segment software. Tehnici avansate de ... Pachete optionale Tehnici avansate de programare

1.Notiuni Introductive1.Notiuni introductive.pdf