Início / dba / Perguntas / 110707
Accepted
mvorisek
Asked: 2015-08-12 10:04:15 +0800 CST

Como posso forçar o MySQL a IGNORAR TODOS os índices?

  • 772

Eu li artigos sobre FORCEíndice, mas como posso forçar o MySQL a IGNORE ALLindexar?

Eu tentei SELECT * FROM tbl IGNORE INDEX(*), mas não tive sucesso.

Quanto ao motivo pelo qual eu (e outros) preciso fazer isso: Por exemplo, eu precisava resumir as estatísticas de referenciadores por tld assim:

SELECT 
    count(*) as c, 
    SUBSTRING
    (
        domain_name, 
        LENGTH(domain_name) - LOCATE('.', REVERSE(domain_name)) + 2
    ) as tld
FROM `domains_import` 
    IGNORE INDEX(domain_name)
GROUP BY tld
ORDER BY c desc
LIMIT 100

...mas sempre tenho que ver quais índices estão definidos ou determinar qual índice será usado via Explicar. Seria muito útil simplesmente escrever IGNORE INDEX ALLe simplesmente não se importar.

Alguém sabe a sintaxe ou um hack? (Dezenas de linhas via tabelas de definição do MySQL não são realmente um atalho).

Adicionado da discussão do bate -papo :

Marca de referência:

  • sem índice = 148,5 segundos

  • com índice = 180 segundos e ainda rodando com Enviando dados A matriz SSD é tão poderosa que você quase não se importa com o cache de dados...

Definição para benchmark:

CREATE TABLE IF NOT EXISTS `domains_import` (
`domain_id` bigint(20) unsigned NOT NULL,
`domain_name` varchar(253) CHARACTER SET ascii COLLATE ascii_bin NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `domains_import`
ADD PRIMARY KEY (`domain_id`),
ADD UNIQUE KEY `domain_name` (`domain_name`);

ALTER TABLE `domains_import`
MODIFY `domain_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT;

InnoDB, o teste com índice (sem USE INDEX() ou similar) ainda está em execução, 250 segundos, acabei de matá-lo.

mysql index

3 respostas

  1. Best Answer

    Não está absolutamente claro por que você deseja isso, mas pode usar a dica USE INDEX ()para dizer ao otimizador para não usar nenhum índice. Dos documentos do MySQL: dicas de índice

    É sintaticamente válido omitir index_listparaUSE INDEX , que significa “não usar índices”. Omitir index_list para FORCE INDEXou IGNORE INDEXé um erro de sintaxe.

    Sua consulta se torna:

    SELECT count(*) AS c, 
       substring_index(domain_name, '.', -1) AS tld
FROM domains_import 
       USE INDEX ()        -- use no indexes
GROUP BY tld
ORDER BY c DESC
LIMIT 100 ;

    Nota lateral: a expressão complexa:

    SUBSTRING(domain_name, LENGTH(domain_name) - LOCATE('.', REVERSE(domain_name)) + 2)

    pode ser simplificado de 4 chamadas de função para 1:

    SUBSTRING_INDEX(domain_name, '.', -1)
    • 27

  2. Você também pode incorporarWHERE 1=1

    SELECT 
    count(*) as c, 
    SUBSTRING
    (
        domain_name, 
        LENGTH(domain_name) - LOCATE('.', REVERSE(domain_name)) + 2
    ) as tld
FROM `domains_import` 
WHERE 1=1
GROUP BY tld
ORDER BY c desc
LIMIT 100

    ypercube acabou de me perguntar

    Rolando, o otimizador do MySQL é tão burro que uma simples condição sempre verdadeira proibirá o uso de índices?

    Sim, mas você deu ao MySQL uma consulta realmente idiota. 1=1reverteria para o Índice Agrupado. No entanto, ainda existe outra maneira, mas requer ser um pouco malicioso para o Optimizer.

    SELECT 
    count(*) as c, 
    SUBSTRING
    (
        domain_name, 
        LENGTH(domain_name) - LOCATE('.', REVERSE(domain_name)) + 2
    ) as tld
FROM `domains_import` 
WHERE domain_name = domain_name
GROUP BY tld
ORDER BY c desc
LIMIT 100

    Isso lançará todos os índices sob o barramento, com certeza, porque o valor de cada linha domain_nameserá verificado. Se domain_nameestiver indexado, você deve escolher uma coluna para o WHERE column_name=column_nameque não esteja indexado.

    Acabei de tentar isso em uma mesa grande em um servidor Staging

    mysql > explain SELECT COUNT(1) FROM VIDEO WHERE EMBEDDED_FLG=EMBEDDED_FLG;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | VIDEO | ALL  | NULL          | NULL | NULL    | NULL | 354327 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

    Nenhum índice foi escolhido

    • 2

  3. Supondo que você tenha esses dois índices:

    ADD PRIMARY KEY (`domain_id`),
ADD UNIQUE KEY `domain_name` (`domain_name`);

    Então não importa o que o otimizador faz; ele deve digitalizar essencialmente uma quantidade idêntica de material.

    Caso 1: Faz um scan da tabela (ou usa domain_id): Vai fazer o scan dos pares (id, name), localizando todos os nomes, fazendo o SUBSTRING..LOCATE, GROUP BY, e finalmente ORDER BY. O GROUP BY e o ORDER BY provavelmente precisam de uma tabela tmp e filesort. Verifique EXPLAIN SELECT ...se isso acontece.

    Caso 2: Ele faz uma varredura de índice (do nome_do_domínio): Esse índice realmente contém pares (nome, id) -- porque o InnoDB implicitamente coloca o PK no final de qualquer chave secundária. O restante do processamento é paralelo ao Caso 1.

    Uma coisa pode ser diferente -- o tamanho dos dois BTrees. Faça SHOW TABLE STATUS LIKE domains_importpara ver o Data_length (para o Caso 1) e o Index_length (para o Caso 2). O BTree maior será mais lento.

    Outra coisa pode ser diferente - cache. Qual é o valor de innodb_buffer_pool_size? Quanta RAM você tem? Os Dados (ou Índice) podem estar contidos no buffer pool. (Ou será 37% dele, devido a esta ser uma varredura de tabela/índice?) Se couber, execute a consulta duas vezes. A segunda vez será cerca de 10 vezes mais rápida devido a não atingir o disco (caching).

    Se esta for uma tarefa única, o SSD ajudará. Caso contrário, e você pode armazenar em cache a tabela inteira, isso não ajudará depois que o buffer_pool for carregado.

    • 0

relate perguntas