XOOPS Brasil

 

5.4. Otimizando a Estrutura de Banco de Dados

5.4.1. Opções do Projeto

O MySQL mantem dados de registros e índices em arquivos separados. Vários (quase todos) bancos de dados misturam dados de registros e índice no mesmo arquivo. Nós acreditamos que a escolha do MySQL é melhor para uma ampla escala de sistemas modernos.

Outra forma de armazenar os dados de registros é manter a informação para cada coluna em uma área separada (exemplos são o SDBM e o Focus). Isto irá causar um ponto de performance para toda consulta que acessar mais de uma coluna. Como isto degrada rapidamente quando mais de uma coluna é acessada, acreditamos que este modelo não é bom para propósitos gerais de bancos de dados.

O caso mais comum é aquele em que o índice e dados são armazenados juntos (como no Oracle/Sybase). Neste caso você irá encontrar a informação do registro na folha da página de índice. A coisa boa com este layout é que ele, em vários casos, dependendo de como o índice é armazenado no cache, salva uma leitura de disco. As desvantagens deste layout são:

  • A varredura da tabela é muito mais lenta porque você tem que ler os índices para encontrar os dados.

  • Não podem ser usados apenas a tabela de índices para recuperar dados para uma consulta.

  • Você perde muito espaço de armazenagem, já que que os índices devem ser duplicados nos nós (pois os registros não podem ser armazenados nos nós).

  • Deleções irão degenerar a tabela depois de um tempo (já que os índices nos nós normalmente não são atualizados na deleção).

  • É mais difícil fazer o cache somente dos dados de índices.

5.4.2. Deixando os Dados com o Menor Tamanho Possível

Uma das otimizações mais básicas é tentar manter seus dados (e índices) utilizando o menor espaço possível no disco (e em memória). Isto pode fornecer grandes melhorias porque a leitura de disco é mais rápida e normalmente menos memória principal será usada. A indexação também exige menos recursos se for feita em colunas menores.

O MySQL suporta vários diferentes tipos de tabelas e formatos de registros. Você pode ter um ótimo ganho de performance escolhendo o formato certo de tabela a ser usada. Veja mais informações sobre isto na Capítulo 7, Tipos de Tabela do MySQL.

Pode-se obter melhor performance em uma tabela e minimizar espaço de armazenagem utilizando as técnicas listadas abaixo:

  • Utilize os tipos mais eficientes (menores) sempre que possível. O MySQL tem vários tipos especializados que economizam espaço em disco e memória.

  • Utilize tipos inteiros menores se possível para obter tabelas menores. Por exemplo, MEDIUMINT normalmente é melhor que INT.

  • Declare colunas para serem NOT NULL se possível. Isto deixa tudo mais rápido e você economiza um bit por coluna. Perceba que se você realmente precisa de NULL nas suas aplicações, podem ser usados. Tente simplesmente não usá-la em todas as colunas por padrão.

  • Se você não possui nenhuma coluna de tamanho variável (VARCHAR, TEXT ou BLOB), um formato de registro de tamanho fixo para é utilizado. Isto é mais rápido mas infelizmente pode ocupar mais espaço. Veja mais informações sobre isto na Seção 7.1.2, “Formatos de Tabelas MyISAM.

  • O índice primário de uma tabela deve ser o mais curto possível. Isto torna a identificação de um registro fácil e eficiente.

  • Para cada tabela, você deve decidir qual metódo de armazenamento/índice utilizar. Veja mais informações sobre isto na Capítulo 7, Tipos de Tabela do MySQL.

  • Crie somente os índices necessários. Índices são bons para recuperação mas ruins quando você precisa armazenar os dados rapidamente. Se na maioria das vezes você acessa uma tabela pesquisando em uma combinação de colunas, crie um índice para elas. A primeira parte do índice deve ser a coluna mais utilizada. Se você SEMPRE utiliza várias colunas, deve usar a coluna com mais duplicações em primeiro lugar para obter melhor compactação do índice.

  • Se for melhor que uma coluna tenha um prefixo único nos primeiros caracteres, é melhor indexar somente este prefixo. O MySQL suporta um índice em uma parte de uma coluna de caracteres. Índices menores são mais rápidos não somente porque eles exigem menos espaço em disco mas também porque eles irão fornecer a você mais acerto no cache de índice e isto diminui acessos a disco. Veja mais informações sobre isto na Seção 5.5.2, “Parâmetros de Sintonia do Servidor”.

  • Em algumas circunstâncias pode ser benéfico dividir uma tabela que é varrida frequentemente em duas. Isto é verdade especificamente se a tabela tiver um formato dinâmico e for possível utilizar um formato de tabela estático que possa ser usada para encontrar os registros relevantes quando se fizer uma varredura da tabela.

5.4.3. Como o MySQL Utiliza Índices

Os índices são utilizados para encontrar registros com um valor específico de uma coluna rapidamente. Sem um índice o MySQL tem de iniciar com o primeiro registro e depois ler através de toda a tabela até que ele encontre os registros relevantes. Quanto maior a tabela, maior será o custo. Se a tabela possui um índice para as colunas em questão, o MySQL pode rapidamente obter uma posição para procurar no meio do arquivo de dados sem ter que varrer todos os registros. Se uma tabela possui 1000 registros, isto é pelo menos 100 vezes mais rápido do que ler todos os registros sequencialmente. Note que se você precisar acessar quase todos os 1000 registros, seria mais rápido acessá-los sequencialmente porque evitaria acessos ao disco.

Todos os índices do MySQL (PRIMARY, UNIQUE e INDEX) são armazenados em árvores B. Strings são automaticamente compactadas nos espaços finais e prefixados. Veja mais informações sobre isto na Seção 6.5.7, “Sintaxe CREATE INDEX.

Índices são utilizados nos seguintes modos:

  • Para encontrar rapidamente os registros que coincidam com uma cláusula WHERE.

  • Para recuperar registros de outras tabelas ao realizar joins.

  • Para encontrar o valor MAX() ou MIN() para uma coluna indexada espeifica. Isto é otimizado por um preprocessador que confere se você está utilizando WHERE key_part_#=constante em todas as partes da chave < N. Neste caso o MySQL irá fazer uma simples procura na chave e trocar a expressão MIN() com uma constante. Se todas as expressões forem trocadas por constantes, a consulta retornará imediatamente:

    SELECT MIN(key_part2),MAX(key_part2) FROM nome_tabela where key_part1=10
    
  • Para ordenar ou agrupar uma tabela se a ordenação ou agrupamento for feito em um prefixo mais à esquerda de uma chave util (por exemplo, ORDER BY key_part_1, key_part_2 ). A chave é lida na ordem invertida se todas as partes da chave forem seguidas por DESC. Veja mais informações sobre isto na Seção 5.2.8, “Como o MySQL Otimiza Cláusulas ORDER BY.

  • Em alguns casos uma consulta pode ser otimizada para recuperar valores sem consultar o arquivo de dados. Se todas colunas utilizadas para alguma tabela são numéricas e formam um prefixo mais à esquerda para alguma chave, os valores podem ser recuperados da árvore de índices para aumentar a velocidade:

    SELECT key_part3 FROM nome_tabela WHERE key_part1=1
    

Suponha que você utilize a seguinte instrução SELECT:

mysql> SELECT * FROM nome_tabela WHERE col1=val1 AND col2=val2;

Se um índice de colunas múltiplas existir em col1 e col2, os registros apropriados podem ser recuperados diretamente. Se índices separados de únicas colunas existirem em col1 e col2, o otimizador tentará encontrar o índice mais restritivo decidindo qual índice irá encontrar menos registros e usará este índice para recuperar os registros.

Se a tabela possuir um índice de múltiplas colunas, qualquer prefixo mais à esquerda do índice pode ser usado pelo otimizador para encontrar registros. Por exemplo, se você possui um índice de três colunas em (col1, col2, col3), você tem capacidades de busca indexada em (col1), (col1, col2) e (col1, col2, col3).

O MySQL não pode utilizar um índice parcial se as colunas não formarem um prefixo mais à esquerda do índice. Suponha que você tenha as instruções SELECT mostradas abaixo:

mysql> SELECT * FROM nome_tabela WHERE col1=val1;
mysql> SELECT * FROM nome_tabela WHERE col2=val2;
mysql> SELECT * FROM nome_tabela WHERE col2=val2 AND col3=val3;

Se um índice existir em (col1, col2, col3), somente a primeira consulta anteriores utiliza o índice. A segunda e terceira consultas involvem colunas indexadas, mas (col2) e (col2, col3) não são os prefixos mais à esquerda de (col1, col2, col3).

O MySQL também utiliza índices para comparações do tipo LIKE se o argumento para LIKE for uma string constante que não inicie com um meta caracter Por exemplo as seguintes instruções SELECT utilizam índices:

mysql> SELECT * FROM nome_tbl WHERE key_col LIKE "Patrick%";
mysql> SELECT * FROM nome_tbl WHERE key_col LIKE "Pat%_ck%";

Na primeira instrução, somente os registros com "Patrick" <= key_col < "Patricl" são considerados. Na segunda instrução, somente registros com "Pat" <= key_col < "Pau" são considerados.

As seguintes instruções SELECT não usarão índices:

mysql> SELECT * FROM nome_tbl WHERE key_col LIKE "%Patrick%";
mysql> SELECT * FROM nome_tbl WHERE key_col LIKE other_col;

Na primeira instrução, o valor LIKE inicia com um meta caracter. Na segunda instrução, o valor LIKE não é uma constante.

O MySQL 4.0 faz outra otimização em LIKE. Se você usar ... LIKE "%string%" e string tiver mais de 3 caracteres, o MySQL usará o algorítmo Turbo Boyer-Moore para inicializar o padrão para a string e então usar este padrão para realizar a pesquisa mais rápido.

Buscas usando nome_coluna IS NULL usa índices se nome_coluna é um índice.

O MySQL normalmente utiliza o índice que encontra o menor número de registros. Um índice é usado para colunas que você compara com os seguintes operadores: =, >, >=, <, <=, BETWEEN ou um LIKE com um padrão que começa com um prefixo sem meta caracteres como 'algo%'.

Qualquer índice que não cobrem todos os níveis de AND na cláusula WHERE não é utilizado para otimizar a consulta. Em outras palavras: Para poder usar um índice, um prefixo do índice deve ser utilizado em todo agrupamento AND.

A seguinte cláusula WHERE utilizará índices:

... WHERE index_part1=1 AND index_part2=2 AND other_column=3
... WHERE index=1 OR A=10 AND index=2 /* index = 1 OR index = 2 */
... WHERE index_part1='hello' AND index_part_3=5
/* optimised like "index_part1='hello'" */
... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;
/* Can use index on index1 but not on index2 or index 3 */

Estas cláusulas WHERE não utilizam índices:

... WHERE index_part2=1 AND index_part3=2 /* index_part_1 is not used */
... WHERE index=1 OR A=10 /* Index is not used in
both AND parts */
... WHERE index_part1=1 OR index_part2=10 /* No index spans all rows */

Perceba que algumas vezes o MySQL não utilizará um índice, mesmo se algum estiver disponível. Um exemplo deste caso é quando o uso do índice necessita que o MySQL acesse mais de 30% dos registros na tabela. (Neste caso uma varredura da tabela é provavelmente mais rápido, já que ela necessitará de menos pesquisas em discos). No entanto, se uma consulta utiliza LIMIT para recuperar somente parte dos registros, o MySQL irá utilizar um índice de qualquer forma, pois assim pode encontrar os poucos registros mais rapidamente e retornar o resultado.

5.4.4. Índices de Colunas

Todos os tipos de colunas do MySQL podem ser indexadas. O uso de índices nas colunas relevantes é a melhor forma de melhorar a performance de operações SELECT.

O número máximo de índices por tabelas e o tamanho máximo de um índice é definido pelo mecanismo de armazenamento. Veja mais informações sobre isto na Capítulo 7, Tipos de Tabela do MySQL. Todos os mecanismos de armazenamentos suportam um mínimo de 16 chaves por tabela e um índice de tamanho total mínimo de 256 bytes.

Para colunas CHAR e VARCHAR você pode indexar um prefixo da coluna. Isto é muito mais rápido e necessita de menos espaço em disco do que indexar a coluna inteira. A sintaxe para utilizar na instrução CREATE TABLE para indexar um prefixo de uma coluna se parece com o exemplo a seguir:

INDEX nome_indice (nome_campo(tamanho))

O exemplo abaixo cria um índice para os primeiros 10 caracteres da coluna nome:

mysql> CREATE TABLE teste (
nome CHAR(200) NOT NULL,
INDEX nome_indice (nome(10)));

Para colunas BLOB e TEXT, você deve indexar um prefixo da coluna. O índice pode ter até 255 bytes.

No MySQL Versão 3.23.23 ou posterior, você pode também criar índices FULLTEXT especiais. Eles são utilizados para pesquisas textuais. Somente o tipo de tabela MyISAM suporta índices FULLTEXT e apenas para colunas CHAR, VARCHAR e TEXT. Indexação sempre acontece sobre toda a coluna e indexação parcial (prefixo) não é suportada. Veja Seção 6.8, “Pesquisa Full-text no MySQL” para detalhes.

5.4.5. Índices de Múltiplas Colunas

O MySQL pode criar índices em múltiplas colunas. Um índice pode consistir de até 15 colunas. (Em colunas CHAR e VARCHAR você também pode utilizar um prefixo da coluna como parte de um índice).

Um índice de múltiplas colunas pode ser considerado um array ordenado contendo valores que são criados concatenando valores de colunas indexadas.

O MySQL utiliza índices de múltiplas colunas de forma que consultas são rápidas quando você especifica uma quantidade conhecida para a primeira coluna do índice em uma cláusula WHERE, mesmo se você não especificar valores para as outras colunas.

Suponha que uma tabela tenha a seguinte especificação:

mysql> CREATE TABLE teste (
id INT NOT NULL,
ultimo_nome CHAR(30) NOT NULL,
primeiro_nome CHAR(30) NOT NULL,
PRIMARY KEY (id),
INDEX nome (ultimo_nome,primeiro_nome));

Então o índice nome é um índice com ultimo_nome e primeiro_nome. O índice será usado para consultas que especificarem valores em um limite conhecido para ultimo_nome, ou para ambos ultimo_nome e primeiro_nome. Desta forma, o índice nome será usado nas seguintes consultas:

mysql> SELECT * FROM teste WHERE ultimo_nome="Widenius";
mysql> SELECT * FROM teste WHERE ultimo_nome="Widenius"
AND primeiro_nome="Michael";
mysql> SELECT * FROM teste WHERE ultimo_nome="Widenius"
AND (primeiro_nome="Michael" OR primeiro_nome="Monty");
mysql> SELECT * FROM teste WHERE ultimo_nome="Widenius"
AND primeiro_nome >="M" AND primeiro_nome < "N";

Entretanto, o índice nome não será usado nas seguintes consultas:

mysql> SELECT * FROM teste WHERE primeiro_nome="Michael";
mysql> SELECT * FROM teste WHERE ultimo_nome="Widenius"
OR primeiro_nome="Michael";

Para maiores informações sobre a maneira que o MySQL utiliza índices para melhorar o desempenho das consultas, veja Seção 5.4.3, “Como o MySQL Utiliza Índices”.

5.4.6. Como o MySQL Conta as Tabelas Abertas

Ao executar o comando mysqladmin status, você verá algo deste tipo:

Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: 12

O valor Open tables de 12 ode ser bastante estranho se você só possui 6 tabelas.

O MySQL é multithreaded, portanto ele pode haver clientes enviando consultas para uma determinada tabela simultaneamente. Para minimizar o problema com dois clientes tendo diferentes estados no mesmo arquivo, a tabela é aberta independentemente por cada thread concorrente. Isto exige mais memória mas normalmente aumentará o desempenho. Com tabelas ISAM e MyISAM, um descritor extra de arquivo é necessário para o arquivo de dados, para cada cliente que tem a tabela aberta. O descritor de arquivo de índice é compartilhado entre todas as threads.

Você pode ler mais sobre este tópico na próxima seção. Veja mais informações sobre isto na Seção 5.4.7, “Como o MySQL Abre e Fecha as Tabelas”.

5.4.7. Como o MySQL Abre e Fecha as Tabelas

As variáveis do servidor table_cache, max_connections e max_tmp_tables afetam o número máximo de arquivos que o servidor mantêm abertos. Se você aumentar um ou ambos destes valores, você pode ir contra um limite imposto pelo seu sistema operacional no número de arquivos abertos por processo. Você pode aumentar o limite de arquivos abertos em muitos sistemas operacionais, embora o método varia muito de um sistema para outro. Consulte a documentação de seu Sistema Operacional para saber como fazê-lo, porque o método para alterar o limite varia muito de um sistema para outro.

table_cache é relacionado a max_connections. Por exemplo, para 200 conexões concorrentes em execução, você deve ter um tamanho de cache de tabela de pelo menos 200 * n, onde n é o número máximo de tabelas em um join. Você também precisa reservar alguns descritores de arquivos para tabelas e arquivos temporários.

Esteja certo de que o seu sistema operacional pode tratar o número de descritores de arquivos abertos definido pelo valor de table_cache. Se table_cache for muito alto, o MySQL pode esgotar os descritores de arquivo e recusar conexões, falhar na execução de consultas e ser muito instavel. Você também têm que levar em conta que o mecanismo de armazenamento MyISAM precisa de dois descritores de arquivos para cada tabela aberta. Você pode aumentar o número de descritores de arquivo disponíveis para o MySQL com a opção de inicialização --open-files-limit=#. Veja mais informações sobre isto na Seção A.2.17, “Arquivo Não Encontrado”.

A cache de tabelas abertas será mantido em um nível de table_cache entradas. O valor padrão é 64; isto pode ser alterado com a opção -O table_cache=# do mysqld. Note que o MySQL pode temporariamente abrir mais tabelas para poder se executar consultas.

Um tabela não usada é fechada e removida da cache de tabelas sob as seguintes circuntâncias:

  • Quando a cache está cheia e um thread tenta abrir uma tabela que não está na cache.

  • Quando a cache contém mais que table_cache entradas e uma thread não está mais usando uma tabela.

  • Quando alguém executa mysqladmin refresh ou mysqladmin flush-tables.

  • Quando alguém executa uma instrução FLUSH TABLES.

Quando o cache de tabela encher, o servidor usa o seguinte procedimento para encontrar uma entrada de cache para usar:

  • Tabelas que não estiverem em uso são liberadas, na ordem LRU (least-recently-used), ou seja, a tabela que foi usada menos rcentemente.

  • Se o cache estiver cheio e nenhuma tabelas pode ser liberada, mas uma nova tabela precisar ser aberta, o cache é extendido temporariamente quando necessário.

  • Se o cache estiver no estado temporariamente extendido e uma tabela vai do estado em-uso para o fora-de-uso, a tabela é fechada e liberada do cache.

A table is opened for each concurrent access. This means the table needs to be opened twice if two threads access the same table or if a thread accesses the table twice in the same query (for example, by joining the table to itself).

Uma tabela é aberta para cada acesso simultâneo. Isto significa a tabela precisa ser aberta duas vezes se duas threads acessam a mesma tabela ou se uma thread acessa a tabela duas vezes na mesma consulta (por exemplo, fazendo um join da tabela com ela mesma). A primeira abertura de qualquer tabela exige dois descritores de arquivos; cada uso adicional da tabela exige somente um descritor. O descritor extra para a primeira abertura é para o arquivo de índice: este descritor é compartilhado entre todas as threads.

Se você está abrindo uma tabela com a instrução HANDLER nome_tabela OPEN, uma tabela dedicada é alocada para a thread. Este objeto da tabela não é compartilhado por outras threads e não será fechado até que a thread chame HANDLER nome_tabela CLOSE ou seja finalizada. Veja mais informações sobre isto na Seção 6.4.9, “Sintaxe HANDLER. Quando isto acontece, a tabela é colocada de volta na cache de tabela (se a cache não estiver cheia).

Você pode conferir se o seu cache de tabela está muito pequeno conferindo a variável opened_tables do mysqld. Se este valor for muito grande, mesmo se você não fez vários FLUSH TABLES, você deve aumentar o tamanho da sua cache de tabelas. Veja mais informações sobre isto na Seção 4.6.8.3, “SHOW STATUS.

5.4.8. Desvantagem em Criar um Número Grande de Tabelas no Mesmo Banco de Dados

Se você possui muitos arquivos em um diretório, operações de abrir, fechar e criação ficarão lentos. Se você executar instruções SELECT em diversas tabelas, existirá uma pequena sobrecarga quando o cache de tabela estiver cheio, porque para toda tabela que teve que ser aberta, outra deve ser fechada. Você pode reduzir esta sobrecarga tornando o cache de tabelas maior.