Início / dba / Perguntas / 270276
Accepted
Nir
Asked: 2020-07-03 07:19:30 +0800 CST

Índice errado foi espiado

  • 772

Eu tenho um índice que filtra 99% da tabela ou seja ix_magic_composite(para os argumentos dessa consulta). Quando eu adiciono outro orfiltro ele escolhe o índice errado, ou seja, fTSmesmo que eu crie um índice que comece com esse campo ele ainda escolhe o índice errado. Os tempos de execução são 20s vs 3s para o melhor índice. ix_magic_compositeindex retorna (filtro inicial) para ambos os SQLs em torno de 10 linhas de milhões, enquanto fTSretorna milhões de volta.

Meio sem noção. Parece-me que as estatísticas não estão dando ao mecanismo a imagem certa com todas essas colunas combinadas.

Simplifiquei a tabela, ela tem muito mais colunas e índices.

SQL com bom plano:

select *
from tblExample
where 1=1
and status = 'okay'
and textCol > ''
and insrBLN = 1
and (magic is NULL or magic = '')
and (itemId is NULL or itemId = '')
and fTS > '2020-01-01'
and fTS > '2020-01-01'
order by fTS
limit 50

+----+-------------+------------+------------+-------------+--------------------------------------------------+---------------------+---------+-------+---------+----------+----------------------------------------------------+
| id | select_type | table      | partitions | type        | possible_keys                                    | key                 | key_len | ref   | rows    | filtered | Extra                                              |
+----+-------------+------------+------------+-------------+--------------------------------------------------+---------------------+---------+-------+---------+----------+----------------------------------------------------+
|  1 | SIMPLE      | tblExample | NULL       | ref_or_null | textCol,status,textCol_4,ix_magic_composite,fTS  | ix_magic_composite  | 53      | const | 5892974 |     0.24 | Using index condition; Using where; Using filesort |
+----+-------------+------------+------------+-------------+--------------------------------------------------+---------------------+---------+-------+---------+----------+----------------------------------------------------+

SQL com plano ruim:

select *
from tblExample
where 1=1
and status = 'okay'
and textCol > ''
and insrBLN = 1
and (magic is NULL or magic = '' or magic = 'retry')
and (itemId is NULL or itemId = '' or itemId = 'retry')
and fTS > '2020-01-01'
and fTS > '2020-01-01'
order by fTS
limit 50

+----+-------------+------------+------------+-------+-------------------------------------------------+---------+---------+------+---------+----------+------------------------------------+
| id | select_type | table      | partitions | type  | possible_keys                                   | key     | key_len | ref  | rows    | filtered | Extra                              |
+----+-------------+------------+------------+-------+-------------------------------------------------+---------+---------+------+---------+----------+------------------------------------+
|  1 | SIMPLE      | tblExample | NULL       | range | textCol,status,textCol_4,ix_magic_composite,fTS | fTS     | 5       | NULL | 6271587 |    0.18  | Using index condition; Using where |
+----+-------------+------------+------------+-------+-----------------------------------------    ----+---------+---------+------+---------+----------+------------------------------------+

Mesa:

CREATE TABLE `tblExample` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `fTS` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `status` varchar(50) NOT NULL DEFAULT 'new',
  `textCol` varchar(50) DEFAULT NULL,
  `insrBLN` tinyint(4) NOT NULL DEFAULT '0',
  `itemId` varchar(50) DEFAULT NULL ,
  `magic` varchar(50) DEFAULT NULL ,
  PRIMARY KEY (`id`),
  KEY `ix_magic_composite` (`itemId`,`magic`,`fTS`,`insrBLN`),
  KEY `fTS` (`fTS`)
) ENGINE=InnoDB AUTO_INCREMENT=14391289 DEFAULT CHARSET=latin1

EDITAR

Nós refatoramos o código para que a consulta se pareça com:

select *
from tblExample
where 1=1
and status = 'okay'
and textCol > ''
and insrBLN = 1
and (retry = '' or (retry='retry' and retryDT < now() - interval 1 day))
and fTS > '2020-01-01'
order by fTS
limit 50

O problema NÃO foi classificado (também tentei uma ordem de colunas diferente no índice). Parece que ele escolhe o índice certo apenas se eu remover o pedido.

mysql performance

3 respostas

  1. A adição de cláusulas OR torna mais difícil estimar quão bem o índice filtrará. Uma solução é adicionar uma coluna sempre gerada que calcula se os predicados para magic e itemId são satisfeitos e indexar que:

    CREATE TABLE tblExample (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  fTS timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  status varchar(50) NOT NULL DEFAULT 'new',
  textCol varchar(50) DEFAULT NULL,
  insrBLN tinyint(4) NOT NULL DEFAULT '0',
  itemId varchar(50) DEFAULT NULL ,
  magic varchar(50) DEFAULT NULL ,
  retry tinyint GENERATED ALWAYS AS 
      ( case when  (magic is NULL or magic = '' or magic = 'retry') 
               AND (itemId is NULL or itemId = '' or itemId = 'retry')
             then 1 
             else 0
        end
      ) STORED,  
  PRIMARY KEY (`id`),
  KEY `ix_magic_composite` (retry,`fTS`,`insrBLN`),
  KEY `fTS` (`fTS`)
) ENGINE=InnoDB AUTO_INCREMENT=14391289 DEFAULT CHARSET=latin1

    A consulta pode então ser alterada para:

    SELECT t.*
FROM tblExample t
WHERE status = 'okay'
and textCol > ''
and insrBLN = 1
and retry
and fTS > '2020-01-01'
and fTS > '2020-01-01'  -- can be removed I assume
order by fTS
limit 50;

    A solução correta é provavelmente corrigir o modelo de dados, mas isso pode não ser possível.

    • 1
  2. Best Answer

    A refatoração não funcionou. Resolvi mudar para o UNION ALLque dá os mesmos resultados que use indexou force index. Eu escolho essa abordagem, pois não exigirá nenhuma alteração de código se o índice for descartado ou renomeado.

    • 1

  3. Há várias coisas que atrapalham a eficiência:

    • OR. Mesmo "ref_or_null" está abaixo do ideal. Para começar, você pode evitar ter ambos ''e NULLpara a coluna? Ou seja, limpe os dados e o processamento para usar ou . Dessa forma, você não precisará testar para ambos.'' NULL
    • Por causa de (itemId is NULL or itemId = '' or itemId = 'retry'), eu recomendo escolher NULL, não '', para que "ref_or_null_ possa ser usado.
    • Como você já descobriu, UNION(de preferência ALL) é uma solução alternativa para OR. Infelizmente, isso fica confuso com mais de um OR.
    • Existem vários "intervalos" (textcol, fTS, retryDT). Apenas um pode ser efetivamente usado. E, com muita frequência, o Otimizador não consegue escolher corretamente em qual focar.
    • O Otimizador prefere "filtrar" os dados (via WHERE) e não se preocupar com a ordenação ( ORDER BY). Mas quando a WHEREcláusula fica muito complexa, o Optimizer pode abandonar WHEREe simplesmente focar em ORDER BY. Sua segunda consulta é um exemplo disso.
    • Na verdade, essa segunda consulta pode ser melhorada mudando INDEX(fTS)para INDEX(status, insrBLN, fTS). Dessa forma, ele pode fazer algumas filtragens antes de classificar; em seguida, termine de filtrar à medida que percorre as linhas.
    • Então, depois de escolher ''ou NULL, esse índice pode ser alterado para INDEX(status, insrBLN, magic, itemId, fTS).
    • Observe que tenho os =testes primeiro no INDEX, e o "intervalo" e/ou ORDER BYcoluna ( FTS) por último. (A ordem das =colunas não importa.)
    • A existência de um segundo teste de "intervalo" ( textcol > '') impede que haja um único índice que trate tanto da filtragem quanto da classificação. (Acho que não há solução alternativa.)
    • A ordem das colunas ix_magic_compositeé abaixo do ideal para todas as consultas apresentadas. O importante é que insrBLN(testado com =) precisa ser antes FTS(um intervalo), não depois.
    • Ao usarUNION , você precisará criar um índice ideal para cada componente do UNION.
    • 1

relate perguntas