Laboratorio - Otimizando Consultas MySql-mai16

7/25/2019 Laboratorio - Otimizando Consultas MySql-mai16

1/9

Prof. Edilberto Silva Otimizao de Consultas

Ps-Graduao em Banco de Dados 1

Laboratrio Otimizao de Consulta

IMPORTANTE: em cada sesso, antes de iniciar a execuo dos comandos,executar o comando:

-- desativar o cache de dadosSET GLOBAL QUERY_CACHE_SIZE=0;

-- Para verificarSHOW VARIABLES LIKE QUERY_CACHE_SIZE;

1 Demonstrao de como se trabalha a busca empginas de ndices

Busca de forma ordenada sem ndice:4, 5, 6, 10, 18, 26, 34, 36, 38, 47, 57, 67, 68, 72, 74

Busca em rvore binria:36

10 675 26 47 72

4 6 18 34 38 57 68 74

Quantas pginas para acessar 10; 36; 38; 74

Tarefa Preencher em uma tabela/planilha similar a apresentada a seguir

dados das execues das consultas

IDConsulta

Tempo (s) Engine(Inno,My,Mem)

Observaes

1 0,25 My Sem ndice1 0,02 InnoDB ndice Hash

Anotar os tempos de todas as consultas

1.1 Comandos bsicos

use employees;

show index from employees;

EXPLAIN select (...);


2/9



2 ENGINE: [InnoDB]2.1 Sem ndice

2.1.1 SELECT SQL_NO_CACHE * from employees.employees;

2.1.2 SELECT SQL_NO_CACHE èmp_no` , `birth_date`, `first_name` ,`last_name` , `gender` , `hire_date` from èmployees`.èmployeesòrder by first_name asc;

2.1.3 SELECT SQL_NO_CACHE èmp_no` , `birth_date`, `first_name` ,`last_name` , `gender` , `hire_date` from èmployees`.èmployeesòrder by first_name desc;

2.1.4 SELECT SQL_NO_CACHE * from employees where last_name like'%T%' and first_name like 'Ar%';

2.1.5 SELECT SQL_NO_CACHE * from employees where first_name='DeForest';

2.1.6 SELECT * from employees where first_name like 'Defo%';

2.1.7 SELECT * from employees where first_name like 'De%';

2.1.8 SELECT * from employees where first_name like '%Defo%';

2.2 ndices simples

2.2.1 CREATE INDEX IDX_firstnamea ON èmployees` (first_name asc);

2.2.2 CREATE INDEX IDX_firstnamed ON èmployees` (first_name desc);

Tarefas: Executar show index from employees;. Observar o index_type. Executar as consultas de 2.1 e comparar os tempos. Executar as consultas de 2.1 usando o EXPLAIN. Observar com

ateno key_len, key e rows. O que significa um nmero maior oumenor em rows;

2.2.3 DROP INDEX IDX_firstnamea ON èmployees`;

2.2.4 DROP INDEX IDX_firstnamed ON èmployees`;

2.2.5 CREATE INDEX IDX_firstnamea ON employees` (first_name (3) asc);

Tarefas: Executar as consultas de 2.1 usando o EXPLAIN. Observar com

ateno key_len, key e rows. O que significa um nmero maior oumenor em rows;

2.2.6 DROP INDEX IDX_firstnamea ON èmployees`;

2.2.7 CREATE INDEX IDX_firstnamea ON èmployees` (first_name asc);

2.2.8 CREATE INDEX IDX_firstnamed ON èmployees` (first_name desc);


3/9



2.3 Hints - Use e Force de ndices

Tarefas: Executar os comandos abaixo (um por vez) e anotar a cardinality

! show index from employees;! analyze table employees;! show index from employees;

O que significa este nmero? E sua alterao aps o analyze?

2.3.1 SELECT SQL_NO_CACHE èmp_no` , `birth_date`, `first_name` ,`last_name` , `gender` , `hire_date` from èmployees`.èmployeesùse index (IDX_firstnamea) order by first_name desc;

2.3.2 SELECT SQL_NO_CACHE èmp_no` , `birth_date`, `first_name` ,`last_name` , `gender` , `hire_date` from èmployees`.èmployees`force index (IDX_firstnamed) order by first_name desc;

Tarefas: Qual a diferena entre USE e FORCE?

! Executar as consultas de 2.3.* usando o EXPLAIN. Observarcom ateno key e rows. O que significa um nmero maior oumenor em rows;

Qual a forma mais rpida nestes casos: com o sem o uso do ndice? Eno caso de usar um ndice ASC para uma consulta que usa DESC

O SGBD escolheu a forma mais rpida? Justifique

2.4 ndices Compostos2.4.1 CREATE INDEX IDX_last_first_name ON èmployees` (last_name

asc,first_name asc);

2.4.2 SELECT SQL_NO_CACHE * FROM employees` WHERE last_namelike 'Warwick';

2.4.3 SELECT SQL_NO_CACHE * FROM èmployees` WHEREfirst_name='Dekang' and last_name = 'Warwick' order by first_nameasc,last_name asc;

2.4.4 SELECT SQL_NO_CACHE * FROM èmployees` order by first_name

asc,last_name asc;2.4.5 SELECT SQL_NO_CACHE * FROM employees` order by first_name

desc,last_name desc;

2.4.6 SELECT SQL_NO_CACHE èmp_no` , `birth_date`, `first_name` ,`last_name` , `gender` , `hire_date` from èmployees`.èmployees`where first_name >= "e" and first_name


4/9



Forar o uso de um ndice e selecionar a consulta mais rpida.Justifique a seleo e se o SGBD escolheu (ou no) a forma maisrpida.

2.5 Comparando Engines InnoDB MyISAM MEMORY

Tarefas: Criar uma tabela employees_myisam. Importar os dados da tabela

employees! CREATE TABLE employees_myisam` ( èmp_no` int(11) NOT

NULL, `birth_date` date NOT NULL, `first_name`varchar(14)NOT NULL, `last_name` varchar(16) NOT NULL, `genderènum('M','F') NOT NULL, `hire_date` date NOT NULL,PRIMARY KEY (èmp_no`)) ENGINE= myisam DEFAULTCHARSET=utf8;

! insert into employees_myisam SELECT sql_no_cache * fromemployees;

Reexecutar todos os comandos SQL (mudando o nome da tabela)em2* acima e comparar os tempos com a InnoDB x MyISAM.

Tarefas:

Criar uma tabela employees_mem. Importar os dados da tabelaemployees! CREATE TABLE èmployees_mem` ( èmp_no` int(11) NOT

NULL, `birth_date` date NOT NULL,`first_name`varchar(14)NOT NULL, `last_name` varchar(16)NOT NULL, `gender` enum('M','F') NOT NULL, mes smallint,`hire_date` date NOT NULL, PRIMARY KEY (èmp_no`), KEYÌDX_firstnamea` (`first_name`), KEY ÌDX_firstnamed`(`first_name`), KEY ÌDX_last_first_name`(`last_name`,`first_name`)) ENGINE=memory DEFAULTCHARSET=utf8;

! insert into employees_mem SELECT sql_no_cache emp_no,birth_date, first_name, last_name, gender,month(birth_date),hire_date from employees;

Observar o erro por conta do tamanho default 16M.! Executar show variables like '%max_heap%';! Aumentar para 512M set

max_heap_table_size=1024*1024*512; Observar que h valores GLOBAIS e locais

! Executar show variables like '%max_heap%';! Executar show global variables like '%max_heap%';

Executar! DROP TABLE èmployees_mem`;


5/9



! Reexecutar o CREATE e INSERT desta tarefa; Reexecutar todos os comandos SQL (mudando o nome da tabela)em

2* acima e comparar os tempos com a InnoDB x MyISAM x Memory

3 Tipos de ndices3.1 B-Tree

ENGINES: InnoDB, MyISAM, Memory Indicado para comparaes que usam =, >, >=,


6/9



Qual foi o mais rpido? Ambos usaram key (no explain)?Justifique

3.3 Alternativa para mltiplas colunas em um ndice

Indicado para comparaes = com CASE INSENSITIVE Vantagem: tamanho do ndice

Tarefas: Executar:

! ALTER TABLE employees add column name_hash char(32);! UPDATE employees set

name_hash=md5(concat(first_name,last_name))! CREATE INDEX IDX_name_hash ON `employees` (name_hash

asc);! SELECT * from employees where first_name='filipe' and

last_name='baba';! SELECT * from employees where

name_hash=md5(concat('Filipe', 'Baba')); Testar:

! SELECT * from employees wherename_hash=md5(concat('filipe', 'baba'));

! Usar explain nos selects acima; (Observar key)

3.4 Full Text

Full-text = InnoDB and MyISAM Com exceo de partitioned tables.


7/9



3.4.1 CREATE TABLE articles (id INT UNSIGNED AUTO_INCREMENTNOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT,FULLTEXT (title,body) ) ENGINE = MYISAM;

3.4.2 INSERT INTO articles (title,body) VALUES ('MySQL Testtutorial','DBMS stands for database ...'), ('How To Use MySQLWell','After you went through a ...'), ('Optimizing MySQL','In this tutorialwe will show ...'), ('1001 MySQL Tricks','1. Never run mysqld as root.2. ...'), ('MySQL vs. YourSQL','In the following database comparison...'), ('MySQL security','When configured properly, MySQL ...');

Testar:

3.4.3 SELECT SQL_NO_CACHE * FROM articles WHERE MATCH(title,body) AGAINST ('database');

3.4.4 SELECT SQL_NO_CACHE id, MATCH (title,body) AGAINST ('tutorial')FROM articles; 9 [relevncia no resultado]

3.4.5 SELECT SQL_NO_CACHE id, title, body, MATCH (title,body)AGAINST('tutorial DBMS database') AS score FROM articles WHEREMATCH (title,body) AGAINST ('tutorial DBMS database'); [relevnciae dados]

3.4.6 SELECT SQL_NO_CACHE id, title, body FROM articles WHEREMATCH (title,body) AGAINST ('+MySQL -YourSQL -tutorial' INBOOLEAN MODE);

3.4.7 SELECT SQL_NO_CACHE id, title, body, MATCH (title,body)AGAINST ('+MySQL -YourSQL -tutorial' IN BOOLEAN MODE) asscore FROM articles WHERE MATCH (title,body) AGAINST ('+MySQL-YourSQL -tutorial' IN BOOLEAN MODE);

3.4.8 SELECT SQL_NO_CACHE id, title, body, MATCH (title,body)AGAINST ('+MySQL -YourSQL -tutorial' IN BOOLEAN MODE) asscore FROM articles WHERE MATCH (title,body) AGAINST ('+MySQL~YourSQL ~tutorial' IN BOOLEAN MODE);

Tarefas: Executar:

!

ALTER TABLE èmployees` ADD FULLTEXT INDEXÌDX_FT_name` (`first_name` ASC, `last_name` ASC) ;

! SELECT SQL_NO_CACHE * FROM èmployees` WHEREMATCH (first_name, last_name) AGAINST ('wi*' in booleanmode);

! SELECT SQL_NO_CACHE * FROM èmployees` WHEREMATCH (first_name, last_name) AGAINST ('wi* -s' in booleanmode);

! SELECT SQL_NO_CACHE * FROM èmployees` WHERE

MATCH (first_name, last_name) AGAINST ('wi* -wiegley' inboolean mode);


8/9



Comparar consulta com indice normal e com fulltext na mesma colunatabela! SELECT SQL_NO_CACHE * FROM èmployees` WHERE

MATCH (first_name, last_name) AGAINST ('wi*' in booleanmode);

! SELECT SQL_NO_CACHE * FROM èmployees` WHEREfirst_name like "wi%" or last_name like "wi%";

Incluir ndice full text na tabela èmployees_myisam` e reexecutartodos os comandos SQL (mudando o nome da tabela)acima ecomparar os tempos com a InnoDB x MyISAM

V3.mai.16


9/9



Atividades de Laboratrio

1) Em qual situao o MyISAM mais performtico? Justifique.

2) Em qual situao o InnoBD mais performtico? Justifique.

3) Em qual situao o Memory mais performtico? Justifique.

4) Qual a diferena em % de tempo de ganho com o ndice entre oMyISAM e o InnoDB? Era esperado este ganho? Justifique

5) Em qual situao o ndice foi mais performtico (ASC ou DESC)quando forado o ndice com correspondente ASC ou DESC?Justifique.

6) possvel usar o Hash em todas as tabelas de uma Database? Qualsua relao com o tamanho total da tabela?

7) Crie trs consultas usando!="para first_name. Mostre e explique pormeio do tempo de consulta e explain qual o mais rpido: hash, b-tree

ou Full-Text.

8) Qual a resposta para situao de negcio colocado no exerccio defixao

Unique x Primary Key CREATE TABLE Clientes (

ClienteID INT NOT NULL, ClienteNome VARCHAR(50) NOTNULL, ClienteRG VARCHAR(15) NOT NULL, ClienteCPFCHAR(11) NOT NULL, primary key (ClienteID));

CREATE UNIQUE INDEX un_cpf ON Clientes (ClienteCPF)USING BTREE;

--Testes INSERT INTO Clientes VALUES

(1,"Natanael","14538543","75712230466"); INSERT INTO Clientes VALUES

(2,"Marcelo","9845423","82013423920"); INSERT INTO Clientes VALUES (3,"Jonas","557023"

,"12345678900"); INSERT INTO Clientes VALUES (3,"Joao","557023"

,"99999999988"); INSERT INTO Clientes VALUES (4,"Joao","557023"

,"82013423920"); SELECT SQL_NO_CACHE * FROM CLIENTES;

Documents

Laboratorio - Otimizando Consultas MySql-mai16