O melhor de MyISAM e InnoDB

O gen_clust_index (índice agrupado) sob o capô do InnoDB abriga entradas de chaves primárias junto com rowids. O que é interessante sobre o uso do gen_clust_index é o fato de que quaisquer índices não exclusivos que você criar sempre terão um rowid correspondente para o gen_clust_index de uma tabela. Portanto, sempre há pesquisas de índice duplo, uma para o índice secundário e outra para o gen_clust_index.

Qualquer tentativa de melhorar o layout de uma tabela ou chave primária é anulada por causa do gen_clust_index, ou pelo menos resultados marginais na melhor das hipóteses.

EXEMPLO

Algumas pessoas tentam classificar um MyISAM na ordem PRIMARY KEY. De acordo com MySQL Database Design and Tuning, página 236, parágrafo 7, sob o subtítulo "Storing a Table in Index Order":

Se você frequentemente recupera grandes intervalos de dados indexados de uma tabela ou classifica consistentemente os resultados na mesma chave de índice, talvez queira considerar a execução de myisamchk com a opção --sort-records. Fazer isso diz ao MySQL para classificar os dados da tabela na mesma ordem física do índice e pode ajudar a acelerar esses tipos de operações. Como alternativa, você pode combinar a instrução ALTER TABLE com uma opção ORDER BY de uma coluna específica para obter os mesmos resultados.

Concedido, isso funciona e funciona efetivamente PARA MyISAM . Você pode executar ALTER TABLE ... ORDER BY col1,col2,...,coln no InnoDB, onde as colunas podem ou não ser as da PRIMARY KEY. Isso não produzirá resultados mais rápidos para o InnoDB porque ... isso mesmo ... você deve consultar o gen_clust_index a cada vez.

Algumas pessoas podem fazer o formato de linha da tabela FIXED usando ALTER TABLE mydb.mytb ROW_FORMAT=Fixed;e podem obter um aumento de 20% no desempenho de leitura sem nenhuma outra alteração. Isso funciona e funciona efetivamente PARA MyISAM . Isso não produzirá resultados mais rápidos para o InnoDB porque ... isso mesmo ... você deve consultar o gen_clust_index a cada vez.

Você pode executar o seguinte em uma tabela InnoDB chamada mydb.mytb:

CREATE TABLE mydb.mytc LIKE mydb.mytb;
INSERT INTO mydb.mytc SELECT * FROM mydb.mytb ORDER BY col1,col2,...coln;
ALTER TABLE mydb.mytb RENAME mydb.mytd;
ALTER TABLE mydb.mytc RENAME mydb.mytb;
DROP TABLE mydb.mytd;

Isso colocará a tabela em ordem de rowid no gen_clust_index. Isso pode produzir resultados marginais para o InnoDB, na melhor das hipóteses, porque... isso mesmo... você deve consultar o gen_clust_index toda vez.

Agora, vamos ser um pouco ridículos. Existe uma interface NoSQL para consultar (somente SELECT) MyISAM e InnoDB chamada interface HandlerSocket (anteriormente chamada de HANLDER) . Isso fornece acesso a dados que permitem ignorar todos os protocolos SQL, ACID e MVCC . Embora seja possível, IMHO MUITO COMPLICADO PARA CODIFICAR E MANTER. AFAIK, não há nada impresso informando se a interface HandlerSocket interage com o gen_clust_index ou não.

Em resumo, existem muitas maneiras de esfolar um gato. Nesse caso, você não pode obter o gato (o gen_clust_index). Acho que é por isso que o MyISAM continua a existir por seu desempenho de leitura, sua flexibilidade na ordenação de tabelas, formato de linha de tabela e as ferramentas de suporte a ele. O InnoDB permanecerá projetado em torno de sua natureza compatível com ACID até que alguma alma corajosa pegue o código-fonte do InnoDB e o transforme em algo que tenha o melhor do MyISAM e do InnoDB .

O índice clusterizado talvez seja o motivo do desempenho de simultaneidade do InnoDB em unidades de rotação tradicionais.

O acesso a uma linha por meio do índice clusterizado é rápido porque os dados da linha estão na mesma página para onde a pesquisa de índice leva. Se uma tabela for grande, a arquitetura de índice clusterizado geralmente salva uma operação de E/S de disco quando comparada a organizações de armazenamento que armazenam dados de linha usando uma página diferente do registro de índice. (Por exemplo, MyISAM usa um arquivo para linhas de dados e outro para registros de índice.)

A E/S de disco é cara. Portanto, reduzir isso é um grande benefício para melhorar a simultaneidade.

Se a E/S de disco começar a ficar mais barata e menos gargalo (por exemplo, à medida que a tecnologia SSD se torna mais estável), a Oracle pode decidir mudar a forma como os índices InnoDB funcionam. É mais provável que permaneça o mesmo, porque a mesma tecnologia fará com que a 'limitação da RAM' seja menos problemática.

Resposta curta: Não.

O InnoDB agrupa por meio da chave primária e, na ausência de uma chave primária, ele escolhe o primeiro índice exclusivo. Na ausência de um índice exclusivo, ele cria uma chave oculta de 6 bytes para clustering.

Quando você tem a chave oculta de 6 bytes, quaisquer índices secundários referem-se a essa chave, em vez de ponteiros exatos para locais de linha (como em MyISAM), então você acaba com uma passagem de chave secundária e, em seguida, uma passagem de chave primária para encontrar seus registros .

Para extrapolar um pouco a sua pergunta, presumo que você esteja preocupado com o ajuste da memória em uma árvore, porque para pesquisar com eficiência, todos os nós raiz devem estar na memória, pois você sempre precisa percorrer esse caminho para encontrar suas páginas de folha?

Isso é verdade, mas um consolo é que os bancos de dados comerciais tentam tornar suas árvores o mais gordas possível, em vez de profundas. Tente executar xtrabackup --stats em seus dados para ver. Por exemplo:

<INDEX STATISTICS>
  table: test/table1, index: PRIMARY, space id: 12, root page 3
  estimated statistics in dictionary:
    key vals: 25265338, leaf pages 497839, size pages 498304
  real statistics:
     level 2 pages: pages=1, data=5395 bytes, data/pages=32%
     level 1 pages: pages=415, data=6471907 bytes, data/pages=95%
        leaf pages: recs=25958413, pages=497839, data=7492026403 bytes, data/pages=91%

Havia 497839 páginas de folha (~8 GB), mas apenas 416 páginas acima (6,5 MB). Eu executei esse comando algumas vezes em dados de produção e sempre me surpreende quando tenho milhões e bilhões de registros e apenas páginas de nível 1-3 + páginas de folha.