Início / dba / Perguntas / 283439
Accepted
Hasan Can Saral
Asked: 2021-01-18 11:05:09 +0800 CST

Como adiciono índices compostos do MySQL com desnormalização e JOINS?

  • 772

Tenho as seguintes tabelas:

CREATE TABLE base_event (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    created_by ... -- some columns
);

CREATE TABLE transaction_events (
    event_id BIGINT UNSIGNED NOT NULL,
    transaction_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    merchant_id BIGINT UNSIGNED NULL DEFAULT NULL,   
    merchant_city VARCHAR (...) NULL DEFAULT NULL, -- Denormalize
    customer_id BIGINT UNSIGNED NULL DEFAULT NULL,
    customer_ip_address VARCHAR(...) NULL DEFAULT NULL, -- Denormalize
    ...
    FOREIGN KEY (event_id) REFERENCES base_event(id),
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (merchant_id) REFERENCES merchants(id),
);

CREATE TABLE customers (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    customer_ip_address VARCHAR(...) NULL DEFAULT NULL,
    ...
);

CREATE TABLE merchants (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ...
);

E meu SELECT:

(SELECT t.*, c.name AS customer_name ...
FROM transaction_events t
JOIN customers c ON t.customer_id = c.id
JOIN merchants m ON t.merchant_id = m.id
WHERE t.customer_ip_address = 'abc' AND t.transaction_time > 'abc')
UNION DISTINCT
(SELECT t.*, c.name AS customer_name ...
FROM transaction_events t
JOIN customers c ON t.customer_id = c.id
JOIN merchants m ON t.merchant_id = m.id
WHERE t.merchant_city = 'abc' AND t.transaction_time > 'abc')

E meus índices são:

ALTER TABLE transaction_events 
ADD INDEX index_1 (customer_ip_address, transaction_time),
ADD INDEX index_2 (merchant_city, transaction_time);
  1. Minha consulta está neste formulário para evitar arquivos OR.
  2. Eu desnormalizei até certo ponto por causa dos índices.
  3. Não preciso referenciar minha base_eventtabela para esta consulta.
  4. A relação com transaction_eventsto customerse merchantsnão é 1-para-1, mas 1-para-0-ou-1.

Minhas perguntas:

  1. Eu posso me livrar do curinga, mas transaction_eventstem cerca de 20 colunas (ajudaria a criar mais índices para acelerar a consulta?
  2. Preciso colocar outros índices compostos (que potencialmente referenciam meus FKs) para melhorar ainda mais essa consulta?
mysql index

2 respostas

  1. Você precisa de todas as 20 colunas de transaction_events? Caso contrário, eliminar *e especificar apenas as colunas de que você precisa não apenas reduz a quantidade de dados que você está recuperando de uma só vez, mas também reduz as chances de um plano de consulta abaixo do ideal. É possível que o plano de consulta gerado varie com base nas colunas da sua SELECTcláusula.

    Você pode testar a adição de um índice no customer_idcampo e outro índice no merchant_idcampo para suas JOINcláusulas e ver se isso melhora o desempenho e produz um plano de consulta melhor. Mas isso exigirá testes e comparação do EXPLAIN para cada caso.

    • 1
  2. Best Answer

    As WHEREcláusulas referem-se a t, portanto, é muito provável que o Optimizer comece com tem cada uma SELECT. Você tem os índices ideais para eles.

    Em seguida, ele precisa acessar as outras duas tabelas ( merchantse customers) e obter 1 (ou 0) linha delas. Essas tabelas têm o índice ótimo para o JOIN, ou seja, PRIMARY KEY(id)em cada caso. (Os FKs não desempenham nenhum papel nesta consulta.)

    t.*pode atrasar as coisas se estiver buscando TEXTcolunas grandes que você ignora.

    Como você precisa de todas as colunas, a única ineficiência possível é se cada uma SELECTestiver buscando a mesma linha de forma redundante, apenas para ser desduplicada por UNION DISTINCT. Acho que não vale a pena corrigir esse problema. (A correção seria UNIONapenas encontrar e desduplicar t.id; depois juntar novamente para tobter as outras 19 colunas. O custo do trabalho extra pode superar o benefício; não posso dizer.)

    • 1

relate perguntas