IEC Banco de Dados I Aula 17 – Otimização Modelo Relacional

1. IEC Banco de Dados I Aula 17 –Otimização Modelo Relacional Turmas: Sistemas de Informação Professora: André Luiz da Costa Carvalho E-mail: andre@icomp.ufam.edu.br Site: http://bdufam.wordpress.com

2. Sumário

3. Introdução • O primeirolugarondepodemosfazerBancos de Dados melhoresénacriação das tabelas (relações) • Com a aplicaçãorodando, mudartableaspodelevaràalteraçõesemtodososprogramasque a usam • Importanteacertarnaprimeiratentativa.

4. Algunsesquemassãomelhoresque outros • Vejamosestesdoisesquemasparainformações de pedidosfeitosparacompra de materiais: • Fornece1(ID_forn,ID_mat,qtde,end_forn) • Fornece2(ID_forn,ID_part,qtde)Fornecedor(ID_forn,end_forn) • Vamoscompararestesdoisesquemas no seguintecenário: • 100000 compras • 2000 fornecedores • Inteiros de 8 bytes e end_forn de 50 bytes.

5. 1 - Espaço • Segundo esquemausaespaço extra para o ID_fornredundante. • 2000 x 8 = 16 mil bytes • Contudo, economizanosendereços • 2000 endereçosvs 100000 do primeiro. • 98000 x 50 = 4.950.000 bytes a menos • Total 4.934.000 bytes a menospara o segundo • Só 5 megas? • Sim, mas ganho se manteriamesmo se aumentássemospara 1 bilhão de linhas

6. 2 – Preservação da Informação • Imaginemque um fornecedor X entregoutodosospedidospendentes. • Fazsentidoque as linhaspertinentes a elesejamdeletadas. • Dessa forma, no esquema 1, a informação de endereço de X seriaperdidajunto. • Então 1 ésemprepior? • Nemsempre

7. 3 - Performance • Se você tem umanecessidadeconstante de saber o endereço do fornecedor de um dados pedido/peça, elepodesermelhor. • Especialmente se houverempoucasinserções. • Se houveremnovasinserçõesconstantes, o endereço extra emcadapedidovaisertrabalho extra. • Então, cadaesquemapodeserútildependendo do caso.

8. Normalização • Esquema 1 énãonormalizado, enquanto o esquema 2 énormalizado. • Dependênciafuncional • X sãoatributos de R, A é um atributoespecífico de R • X -> A • X determina A • Se duasinstânciasdiferentes tem o mesmo valor de X, elasterão o mesmo valor para A • Istoémaisinteressante se A nãoé um atributo de X • Como assim? • Cadafornecedor tem um endereço • CadaID_forndetermina o endereço

9. ParticionamentoVertical • Banco: numCli, saldo e endereco • Dependências: numCli->endereco e numCli->saldo • Duasformas de fazer o esquemarespeitando as dependências • (numCli, endereco, saldo) • (numCli, endereco)(numCli,saldo) • Qualémelhor? • Depende das consultas!

10. Banco • Endereçoéusadogeralmenteumavezpormês. • Para mandarcontas. • Saldo, emcontrapartidaéusado o tempo todo • Tabela (numCli,saldo) seriamenor, o quetrariabenefícios • Índices de cluster esparsospodemsermenores. • Mais pares numCli,saldocaberãonamemória • Consultaqueprecisarlertodossaldosleriamenosblocos

11. R (X,Y,Z) X inteiro YZ são strings Consulta de Scan Particionamento Vertical ruimquandotodosatributossãoacessados. Melhoraquandosódoissãoacessados. Perfomance

12. Cenário 2 • numCli, endereco, cep, saldo • Este esquemafazsentido? • (numCli,endereco) • (numCli,cep) • (numCli,saldo) • Separar CEP e endereçonãopareceuma boa idéia, jáqueelesdevemsersempreacessadosjunto. • Quandoparticionar: • Acessoscostumamserem um dos conjuntos de atributos, nunca entre atributos de ambos. • Atributos Y e Z sãograndes (1/3 do tamanho de um bloco)

13. Desnormalização • As vezes, podeserinteressanteterumainformaçãonãorelacionadadiretamenteemumarelação, se houveremmuitasconsultasqueprojetemestainformação. • Ouseja, se tiverquefazervários joins paraacessar a informação. • Joins sãocaros. • Podevaler a penaterinformaçãoduplicadaparaagilizar.

14. Consulta: encontraritenscujofornecedorficana Europa. Normalizadoprecisa de 4 joins Desnormalizado: adicionanomedaregiãoao item (foreign key denormalization) Performance melhora 30% Desnormalização

15. Manutenção de agregação • Consultas com SUM, AVERAGE e afinspodemsermuitocaras. • Podevaler a penaguardarvisõesmaterializadasdestasconsultas. • Espaço extra seráusado. • Cada insert gerarácustos extras de atualização da visão. • Vale a pena se houveremmaisconsultasqueinserções. • Pode-se criartabelasredundantes com gatilhostambém.

16. Manutenção de agregação

17. Domínios dos atrinutos • Sempreprefira Integer a Float se possível. • Float força range queries. • Ex: Select nota from aluno where nota = 5 vira Select nota from aluno where nota >=4.999 and nota <=5.001 • Atributosquevariam de tamanho: Use tiposvariáveis. • Varcharmelhorque char se tamanho do textovariarmuito. • Porém updates podemcausarfragmentação se valor novo nãocouber no bloco

18. Otimização de Consultas

19. Otimização de Consultas • Semprecomeçe a otimizaçãopeloquepodeser 100% benéfico. • Criaríndices, mudar o esquema, criarvisões, podemgerarcustosnovos. • Reescreverconsultasparasermaisrápidassótrazbenefícios

20. funcionario(cpf, nome, dept, salario, numamigos); estudante(cpf, nome, curso, periodo); dept(dept, gerente, localizacao); clustered index i1 on funcionario (cpf); nonclustered index i2 on funcionario(nome); nonclustered index i3 on funcionario(dept); clustered index i4 on estudante (cpf); nonclustered index i5 on estudante(nome); clustered index i6 on dept(dept); 100000 tuplasfuncionario, 100000 estudante, 10 departments; Cold buffer Base de exemplo

21. Elimine DISTINCTs desnecessários • Query: Encontrarempregados do departamento“ti”. Semduplicatas. SELECT distinct cpfFROM funcionarioWHERE dept = ‘ti’ • DISTINCT desnecessário, jáquecpféchave de funcionario

22. Elimine DISTINCTs desnecessários • Query: Encontrarcpf dos funcionarios de algumdepartamentosemduplicatas. SELECT DISTINCT ssnumFROM employee, techWHERE employee.dept = tech.dept • Distinct necessário?

23. DISTINCT desnecessário • Como deptéchave de dept, cadafuncionáriovaicasar com um únicoregistro de dept. • Cpféchave do funcionário, entãoserá 1 para um, semprecisar de distinct.

24. Generalizando • Relacionamento entre DISTINCT, chaves e joins podesergeneralizado: • Uma tabela T échamada de privilegiada se oscamposretornadospelo select contémumachave de T • Dado R, umatabelanãoprivilegiada. Se R éjoinadaatravés de umachave a outratabela S, diz se que R alcança S (reaches). • Alcanceétransitivo. Se R1 alcança R2 e R2 alcança R3, então R1 alcança R3.

25. Reaches: Teorema • Nãohaverãoduplicatas, mesmosem distinct, se uma das duascondições for válida: • Toda tabela no FROM éprivilegiada • Toda tabelanão-privilegiadaalcançaaomenosumatabelaprivilegiada.

26. Reaches • Se todarelaçãoéprivilegiadaentãonãohaverãoduplicatas • Chaves da relaçãoestão no from. • Dada umarelação T quenãoéprivilegiadamas alcançaaomenosumarelaçãoprivilegiada R. • O Link entre T e R garantequecadacombinação de registrosprivilegiadoséjuntada com pelomenos um registro de T.

27. Reaches: Exemplo1 • O mesmofuncionáriopodecasar com váriosdeptos (gerentenãoéchave), entãocpfpodeaparecerváriasvezes. • Deptnãoalcança a a relaçãoprivilegiadafuncionário. • SELECT funcionario.cpfFROM funcionario, deptWHERE funcionario.gerente= dept.gerente

28. Reaches: Exemplo2 • Cada valor de cpfseriaacompanhado de um valor de departamentodiferente, jáquedept.nomeéchave de dept. • Ambasrelaçõessãoprivilegiadas. SELECT cpf, dept.nomeFROM funcionario, deptWHERE funcionario.gerente = dept.gerente

29. Reaches: Exemplo3 • Estudanteéprivilegiado • Funcionárionãoalcançaestudante(Nome nãoéchave) • DISTINCT énecessário. • Sóemcaso de nomesrepetidos. SELECT estudante.cpfFROM estudante, funcionario, deptWHERE estudente.nome= funcionario.nome AND funcionario.dept= dept.nome;

30. Original: select cpffrom funcionarioe1 where salario= (select max(salario) from funcionarioe2 where e2.dept = e1.dept); Reescrita: select max(salario) as maiorsalario, dept into TEMP from funcionariogroup by dept; Select cpf from funcionario, TEMP where salario= maiorsalario and funcionario.dept= temp.dept; Queries – Subconsultascorrelacionadas

31. SQL Server 2000 vaibem (usahashjoinaoinvés de repetiçãoaninhada) Subconsultasrelacionadas > 1000 > 10000

32. Manutenção de Agregação pedidos( numPedido, itemnum, qtde, idloja, idvend); create clustered index i_pedron pedidos(itemnum); store( idloja, nome); item(itemnum, preco); create clustered index i_item on item(itemnum); vendOtimo( idvend, total); lojaOtimo( idloja, total); • 1000000 pedidos, 10000 lojas, 400000 items; Cold buffer

33. Manutenção de Agregação-- triggers Triggers paraManutenção de Agregação create trigger atualizaVendOtimo on pedidos for insert as update vendOtimo set qtde = (select vendOtimo.total+sum(inserted.qtde*item.preco) from inserted,item where inserted.itemnum = item.itemnum ) Where idvend= (select idvendfrom inserted) ; create trigger atualizaLojaOtimoon pedidos for insert as update lojaOtimo set qtde = (select lojaOtimo.total+sum(inserted.qtde*item.preco) from inserted,item where inserted.itemnum = item.itemnum ) where idloja= (select idloja from inserted)

34. Aggregate Maintenance -- transações • Inserções insert into pedidos values (1000350,7825,562,'xxxxxx6944',’jose'); • Consultas (sem e com tabelasredundantes) select pedidos.vendid, sum(pedidos.qtde*item.preco) from pedidos,item where pedidos.itemnum= item.itemnum group by pedidos.vendid; vs. select * from vendOtimo; select loja.nome, sum(pedidos.qtdey*item.preco) from pedidos,item, loja where pedidos.itemnum= item.itemnumand pedidos.idloja= loja.idloja group by loja.idloja; vs. select * from lojaOtimo;

35. Gatilhosparamanutenção Se tem muitasconsultas e poucasinserções, então vale a pena. Manutenção de integração