Início / dba / Perguntas / 188091
Accepted
Yunus UYANIK
Asked: 2017-10-11 02:35:39 +0800 CST

Desempenho de consulta MySQL usando índice

  • 772

77 milhões de registros na tabela de códigos de campanha.

Declaração de índice:

ÍNDICE IDX_CampaignCode_Id_CodeDateId_CodeId_CustomerId

(CampaignId, CampaignCodeDateId, PKCampaignCodeId, CustomerId)

Primeira consulta, tempo de execução: 0:00:0.546

SELECT
 cc.*
FROM CampaignCode cc
WHERE cc.CampaignId = 18
AND cc.CampaignCodeDateId = 19325
ORDER BY cc.PKCampaignCodeId LIMIT 20000 OFFSET 0;

insira a descrição da imagem aqui

Segunda consulta, tempo de execução: 0:01:11.597

SELECT
 cc.*
FROM CampaignCode cc
WHERE cc.CampaignId = 30
AND cc.CustomerId is not null
ORDER BY cc.PKCampaignCodeId LIMIT 25 OFFSET 0

insira a descrição da imagem aqui

A segunda pergunta não precisa ser mais rápida? O que devo fazer?

Editar:

Eu criei o índice para a segunda consulta.

INDEX IDX_Test_Index

(CampaignId, CustomerId,PKCampaignCodeId)

Após a criação do índice, a segunda consulta é mais rápida, mas a primeira consulta é mais lenta

Plano de execução da primeira consulta alterado: tempo de execução: 0:01:17.236 insira a descrição da imagem aqui

CREATE TABLE CampaignCode (
  PKCampaignCodeId int(11) NOT NULL AUTO_INCREMENT,
  CampaignId int(11) NOT NULL,
  Code varchar(255) NOT NULL,
  CreatedBy int(11) NOT NULL,
  CreatedOn datetime NOT NULL,
  CustomerId int(11) DEFAULT NULL,
  IsActive bit(1) NOT NULL,
  IsUsed bit(1) DEFAULT NULL,
  ModifiedBy int(11) DEFAULT NULL,
  ModifiedOn datetime DEFAULT NULL,
  OrderNumber varchar(255) DEFAULT NULL,
  CampaignCodeDateId int(11) NOT NULL,
  PRIMARY KEY (PKCampaignCodeId),
  INDEX IDX_CampaignCode_CampaignId (CampaignId),
  INDEX IDX_CampaignCode_CampaignId_CodeDateId_CodeId_CampaignCodeId
            (CampaignId, CampaignCodeDateId, PKCampaignCodeId),
  INDEX IDX_CampaignCode_Code (Code),
  INDEX IDX_Test_Index (CampaignId, CustomerId, PKCampaignCodeId)
)
ENGINE = INNODB
AUTO_INCREMENT = 114306664
AVG_ROW_LENGTH = 61
CHARACTER SET latin5
COLLATE latin5_turkish_ci
ROW_FORMAT = DYNAMIC;
mysql index

3 respostas

  1. Best Answer

    Não existe um índice perfeito para isso:

    WHERE cc.CampaignId = 30
  AND cc.CustomerId is not null
ORDER BY cc.PKCampaignCodeId

    É porque o índice não pode passar IS NOT NULLpara chegar ao arquivo ORDER BY.

    No geral,

    WHERE a=1 AND b=2 ORDER BY c

    pode tirar bom proveito INDEX(a,b,c)ou(b,a,c)

    Mas, isso não pode:

    WHERE a=1 AND b!=2 ORDER BY c

    ( IS NOT NULLé semelhante ao != 2fato de ser um "intervalo", não um valor único.)

    Para otimizar isso:

    WHERE cc.CampaignId = 30
  AND cc.CustomerId is not null
ORDER BY cc.PKCampaignCodeId LIMIT 25 OFFSET 0

    há duas possibilidades. O Optimizer escolherá um, às vezes o menos ideal:

    INDEX(CampaignId, CustomerId)
INDEX(CampaignId, PKCampaignCodeId)

    O primeiro fará a filtragem, mas ainda terá que ordenar e limitar.
    O segundo fará parte da filtragem, evitará a classificação, mas poderá ter que examinar muito mais do que 25 linhas.

    Não há benefício em ter todas as 3 colunas em um índice; a terceira coluna não será usada. (E, aparentemente, causa problemas com seu primeiro arquivo SELECT.

    Mais sobre como criar índices: http://mysql.rjweb.org/doc.php/index_cookbook_mysql (embora não responda totalmente a todas as suas perguntas)

    • 2

  2. A segunda pergunta não precisa ser mais rápida? O que devo fazer?

    Nenhuma segunda consulta não utilizará o índice devido à ordenação e à coluna em que a condição importa no índice composto não clusterizado.

    Portanto, para utilizar o índice na segunda consulta, você deve outro índice composto não clusterizado.

    INDEX IDX_CampaignCode_Id_CodeDateId_CodeId_CustomerId1

(CampaignId, CustomerId,PKCampaignCodeId)

SELECT
 cc.*
FROM CampaignCode cc
WHERE cc.CampaignId = 30
AND cc.CustomerId is not null
ORDER BY cc.PKCampaignCodeId LIMIT 25 OFFSET 0

    Evite também usar *, use o nome da coluna.

    • 0

  3. A segunda consulta nem está usando o índice, em vez disso está usando a PRIMARY KEY. Se você não tiver muita gravação na tabela, poderá manter os dois índices, portanto, INDEX IDX_CampaignCode_CampaignId_CodeDateId_CodeId_CampaignCodeId (CampaignId, CampaignCodeDateId, PKCampaignCodeId) e INDEX IDX_Test_Index (CampaignId, CustomerId, PKCampaignCodeId).

    E se você precisar de um conselho adicional, o índice aqui: INDEX IDX_CampaignCode_CampaignId (CampaignId) é redundante. Ele não será usado e você pode deixá-lo cair.

    • 0

relate perguntas