Ajuste de ÍNDICES do Banco de Dados
Autor: Evandro Procedi quinta-feira, Dia 20 de dezembro de 2018, às 12:53:12 horas
Revisado Por: Leandro Almeida sexta-feira, Dia 21 de dezembro de 2018, às 16:19:21 horas
Visualização: Todos
Acessos: 717
Um índice de banco de dados funciona exatamente igual ao índice de um livro, revista, etc... ou seja, ele auxilia na busca de dados, pois quando procuro por um determinado título ou capítulo do livro, o índice me diz em qual página o mesmo se encontra. O mesmo ocorre com os bancos de dados, os índices guardam a informação de onde se encontra determinado registro ou grupo de registros, agilizando a busca e o retorno das informações.
Mas tanto nos livros como nos bancos de dados, cada vez que um novo registro for inserido, excluído ou modificado, implica em atualizar o índice para que quando o leitor for buscar, o índice lhe indique a página correta do conteúdo que está buscando, e é aí que está o problema, pois essa atualização demanda tempo. O gerenciador de banco de dados precisa analisar e atualizar tais informações. Logo, o uso indiscriminado de índices faz aumentar o tamanho do banco de dados e consumir um tempo precioso na sua atualização, prejudicando o desempenho desta base, refletindo diretamente na velocidade do sistema que faz uso da mesma.
A principal avaliação de um bom ou um mau índice é relativa a sua granularidade. Quanto mais granular melhor. Por granularidade entende-se o número de repetições que ocorrem da mesma informação. Por exemplo: Um índice de chave primária tem sua granularidade extremamente alta, pois nenhuma informação daquele índice se repete, ou seja, cada grão deste tipo de índice equivale a apenas um único registro. Para índices não únicos e àqueles compostos de mais de um campo, essa granularidade tende a diminuir, pois poderá haver informação repetida dentro do mesmo. Exemplo: Um índice de nome de cliente. Podem haver diversas pessoas com o mesmo nome.
Outra avaliação importante a fazer antes de se criar um novo índice é em função da importância daquele(s) campo(s) nas pesquisas realizadas pelo sistema, pois, não há motivo nenhum para que se crie índices em campos não utilizados em pesquisas. Como já descrito, estes trazem mais transtorno do que solução. Um exemplo de índice inútil (salvo raríssimas exceções): o campo endereço do cliente. Dificilmente haverá uma pesquisa onde se busque clientes que residem na "Rua Pernambuco, 212". Justifica-se buscar clientes de determinada cidade, rua, bairro, etc, mas não do endereço completo. Outra situação semelhante ocorre na criação de índices para, por exemplo, o campo que corresponde ao valor pelo qual foi vendido determinado produto em determinada data. Neste caso, procura-se pelo produto e pela data (que justifica possuírem índices), e aí verifica-se o valor que o produto foi vendido.
Então, visando diminuir a quantidade de índices existentes atualmente nas bases de dados de nossos clientes, resolvi analisar alguns com uma ferramenta da IBSourgeon, a qual indica se o índice faz alguma diferença no desempenho da base ou só atrapalha e ocupa espaço. Como resultado a primeira ação indicada é a exclusão de todos os índices dos campos do replicador e, adicionalmente, outros índices em tabelas muito utilizadas pelo sistema, como por exemplo as tabelas envolvidas nas vendas e movimentação.
Conforme exposto anteriormente, é fácil entender porque os índices dos campos do replicador não são eficientes. Estes campos armazenam apenas informação referente a replicação daquele registro, ou seja, 0 ou 1 e, em uma tabela onde todos os registros já foram replicados, todos os registros da tabela estarão com valor zero para esse campo. Fazendo analogia com o índice de um livro ou revista, equivale dizer que todas as páginas tem o mesmo número. Assim, o índice não serve pra nada, pois para encontrar um título ou capítulo que o leitor procura, deverá folhear o livro, página por página, até encontrar o que busca.
Segue uma lista de instruções a executar na base de dados dos clientes, com objetivo de remover índices desnecessários buscando diminuir o tamanho da base de dados e melhorar a performance do sistema.
OBS 1: Não há como fazer uma lista com nomes de índices a remover, pois em nossas bases esses índices são criados por qualquer pessoa que entenda que deve criá-los, e não há padronização de nomes, então, falarei sobre os campos que não tem motivo para indexar.
OBS2: Conforme explicação sobre avaliação da eficiência dos índices, chega-se facilmente à conclusão que, em clientes que possuem apenas uma loja, é desnecessário indexar o campo EMPRESA nas diversas tabelas que contém esse campo. De fato, para esses clientes o índice em tal campo não agrega em nada, pois o tamanho do grão desse índice é do tamanho total da coluna, mas para manter um certo padrão entre os bancos de todos os clientes, oriento a não remover tais índices. Também, não justifica remover um índice chave estrangeira, mesmo que seu valor se repita muitas vezes na tabela. Esses índices podem não agregar em desempenho, mas fazem parte da integridade referencial entre tabelas, então, favor não removê-los.
REMOÇÃO DOS ÍNDICES DO REPLICADOR
Escrevi uma Stored Procedure (vide Anexo I) para procurar e remover esses campos automaticamente. Para isso é só copiar o DDL da procedure no Anexo I deste documento, colar o conteúdo na ferramenta de acesso ao banco e executar a sua criação. Após, procurar tal procedure na sessão das Stored Procedures, executá-la e fazer o commit da transação.
Importante: Caso o replicador venha a ser reconfigurado por qualquer motivo, os índices serão recriados. Nesse caso deve-se executar novamente a procedure para sua remoção.
DEMAIS ÍNDICES A EXCLUIR
Como dito anteriormente, pela falta de padrão de criação e nomenclatura de índices, não há como listar os nomes dos índices a excluir, então, a lista a seguir definirá os índices que são necessários em cada tabela, sendo que os demais podem (devem) ser removidos.
OBS 3: Para criação de todo e qualquer índice deve-se usar a ordenação ASCENDENTE.
OBS 4: Nas tabelas onde o campo indexado pode ser uma FK, preferencialmente criar uma FK ao invés de um índice simples.
OBS 5: Talvez desnecessário, mas importante dizer, em tabelas onde haja, por exemplo, o campo EMPRESA como FK e novamente o campo EMPRESA (sozinho) como índice, remover o índice do campo EMPRESA (sozinho). Manter os dois seria uma duplicidade desnecessária. O mesmo vale para as demais FKs.
OBS 6: Após a remoção/reconfiguração dos índices, indica-se executar uma manutenção no banco de dados.
TABELA CAIXA - índices necessários
EMPRESA (pode ser FK)
NRCAIXA
DATA
LANCAMEN
CUPOM
VENDA,EMPRESA (PK)
TABELA VENDAS - índices necessários
EMPRESA (pode ser FK)
CLIENTE
OPERADOR
DATA
VENDA,EMPRESA (PK)
TABELA VENDIDOS - índices necessários
EMPRESA (pode ser FK)
VENDA,EMPRESA
N_FICHA
DATA
EMPRESA,SEQ (PK)
TABELA VENDASTEF - índices necessários
EMPRESA (pode ser FK)
DATA
NVENDA,EMPRESA,SEQ_CARTAO (PK)
TABELA CAB_NF - índices necessários
EMPRESA (pode ser FK)
VENDA
DATA_EMISSAO
CODIGO_EMPRESA (PK)
TABELA PRD_NF - índices necessários
COD_NOTA,EMPRESA (pode ser FK)
EMPRESA,SEQ (PK)
TABELA MOVIMENTACAO - índices necessários
EMPRESA (pode ser FK)
ID_OPERACAO, TABELA (esse índice atualmente é composto por 4 campos, editar deixando só esses dois)
DATA
HORA
PRODUTO
ID,EMPRESA (PK)
TABELA PRODUTOS - índices necessários
COD_BARRA
DESCRICAO
CLASSIFICACAO
GRUPO
PRINCATIVO2
STATUS
TIPO
CODIGO (PK)
TABELA CLIENTES - índices necessários
NOME
CONVENIO
STATUS
CPF
CODIGO (PK)
TABELA CONVENIO - índices necessários
NOME
CODIGO (PK)
TABELA CLIENTES_CONVENIOS - índices necessários
COD_CLIENTE (FK)
COD_CONVENIO (FK)
MATRICULA
COD_CLIENTE,COD_CONVENIO (PK)
NOTAS_G - índices necessários
EMPRESA (pode ser FK)
CODIGO (pode ser FK)
FIN_COD_CONTA_BOL,FIN_COD_EMPRESA_CONTA_BOL
COD_BARRAS_NFE
ENTRADA
EMPRESA,ID (PK)
PRD_NF_G - índices necessários
EMPRESA (pode ser FK)
EMPRESA,NOTA (pode ser FK)
CODIGO (pode ser FK)
NF
EMPRESA,SEQ (PK)
Com certeza há mais índices que podem ser removidos/modificados, mas as tabelas aqui listadas são as tabelas mais utilizadas no dia a dia de todos nossos clientes, então preferi começar por elas. Com o passar do tempo esse documento pode (e deve) sofrer alterações no sentido de alimentar mais informações referentes a índices necessários em outras tabelas.
ANEXO I
DDL da Stored Procedure para remoção dos índices do replicador
SET TERM ^ ;
CREATE PROCEDURE SP_REMOVER_INDICES
AS
declare variable cSql varchar(250);
declare variable indexName varchar(50);
begin
for select rdb$index_name from RDB$INDEX_SEGMENTS where
rdb$field_name like 'CTR_REPL%' into :indexName do begin
cSql = 'drop index ' || :indexName || ';';
execute statement cSql;
end
end^
SET TERM ; ^
GRANT EXECUTE
ON PROCEDURE SP_REMOVER_INDICES TO SYSDBA;