Início / dba / Perguntas / 330626
Accepted
Cyril N.
Asked: 2023-08-25 21:54:22 +0800 CST

Consulta lenta em uma visualização criada. Como posso consertar isso?

  • 772

Fiz uma View bastante complexa no meu banco de dados MySQL e percebi que algumas consultas são lentas. Minha primeira ideia foi adicionar índices, mas não é possível fazer isso em uma visualização, então estou perdido em como melhorar o desempenho da minha consulta.

Compartilharei aqui minha consulta de visualização e farei o possível para explicá-la, depois compartilharei minha estrutura de banco de dados (reduzida ao que é necessário apenas para a visualização).

CREATE VIEW conversations_search AS (
    SELECT a.id, a.organization_id, a.status, a.way, a.agent_id, a.subject, a.snooze_until, a.channel_id, a.source, a.contact_id, a.name, a.email, a.created, a.message, a.category, a.is_personal, a.is_customer, a.last_update,
        CONCAT(DATE_FORMAT(a.last_update, '%Y%m%d'), a.is_customer, DATE_FORMAT(a.last_update, '%H%i%S'), RIGHT(LPAD(a.id, 6, '0'), 6)) AS priority_sort,
        CONCAT(DATE_FORMAT(a.last_update, '%Y%m%d%H%i%S'), RIGHT(LPAD(a.id, 6, '0'), 6)) AS date_sort,
        CONCAT(RIGHT(LPAD(CAST((UTC_TIMESTAMP() - a.last_update) AS UNSIGNED) * 100, 12, '0'), 12), RIGHT(LPAD(a.id, 6, '0'), 6)) AS longest_sort
    FROM (
        SELECT c.id, c.organization_id, c.status, e.way, c.agent_id, c.subject AS subject, c.snooze_until, c.channel_id, c.source, c.contact_id, cn.name, cn.email, e.created, e.message, e.category, c.is_personal,
            IF(pc.customer_id IS NULL, 0, 1) AS is_customer,
            (
                IFNULL(
                    (SELECT created FROM messages WHERE conversation_id = c.id AND way = 'IN' ORDER BY created DESC LIMIT 1),
                    IFNULL(
                        (SELECT created FROM messages WHERE conversation_id = c.id AND way = 'OUT' ORDER BY created DESC LIMIT 1),
                        (SELECT created FROM message_drafts WHERE conversation_id = c.id)
                    )
                )
            ) AS last_update
        FROM (
            SELECT m.way, m.created, m.message, m.conversation_id, 'MESSAGE' AS category FROM messages m WHERE `status` != 'DRAFT'
            UNION
            SELECT NULL AS way, n.created AS created, n.message, n.conversation_id, 'NOTE' AS category FROM conversation_notes n WHERE `status` != 'DRAFT'
        ) AS e LEFT JOIN conversations c ON c.id = e.conversation_id LEFT JOIN contacts cn ON cn.id = c.contact_id
            LEFT JOIN processor_customers pc ON pc.contact_id = c.contact_id
    ) AS a
);

Explicação : Esta visualização carrega algumas colunas relacionadas a tickets, incluindo:

  • o ID do bilhete
  • o Organization_id ao qual o ticket é afetado
  • O status (fechado, aberto, etc.)
  • a forma como a última mensagem foi enviada (recebida ou enviada)
  • O ID do agente atribuído
  • O sujeito
  • O tempo de soneca (se houver)
  • o ID do canal (como [email protegido] )
  • o ID do contato (do e-mail)
  • O nome do contato
  • O e-mail do contato
  • A data de criação do ticket
  • o conteúdo da última mensagem
  • a categoria (se a última entrada for uma mensagem ou nota)
  • Se a mensagem recebida for pessoal (o canal para onde o email foi enviado é pessoal, como [email protegido] , ou aberto a qualquer pessoa da equipe, como suporte@..)
  • Se o contato for um cliente
  • A última vez que o ticket foi atualizado
  • Uma coluna usada para classificar por prioridade
  • Uma coluna para classificar por data
  • Uma coluna para classificar aguardando mais

Definitivamente, não sou um especialista em banco de dados, então talvez já existam muitas melhorias disponíveis nessa consulta, mas o que percebi é que, na produção, uma consulta simples como:

SELECT COUNT(id) FROM Conversations_search WHERE Organization_id = X; Leva cerca de 2,5 segundos para ser executado. Tentei com alguns IDs diferentes e o resultado está entre 900 e 18.000, mas ainda leva cerca de 2,5 segundos.

Claramente, estão faltando algumas otimizações ...

Aqui está minha estrutura de banco de dados (removi algumas colunas inúteis para simplificar aqui):

CREATE TABLE `contacts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(250) DEFAULT NULL,
  `email` varchar(250) NOT NULL,
  `created` datetime NOT NULL,
  `organization_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`,`organization_id`),
  KEY `organization_id` (`organization_id`),
  CONSTRAINT `contacts_ibfk_2` FOREIGN KEY (`organization_id`) REFERENCES `organizations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11181 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `conversations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `subject` varchar(250) DEFAULT NULL,
  `status` varchar(15) NOT NULL,
  `source` varchar(15) NOT NULL,
  `created` datetime(6) NOT NULL,
  `snooze_until` datetime DEFAULT NULL,
  `channel_id` bigint(20) unsigned DEFAULT NULL,
  `organization_id` int(11) NOT NULL,
  `contact_id` bigint(20) unsigned NOT NULL,
  `agent_id` bigint(20) unsigned DEFAULT NULL,
  `is_personal` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `agent_id` (`agent_id`),
  KEY `channel_id` (`channel_id`),
  KEY `contact_id` (`contact_id`),
  KEY `organization_id` (`organization_id`),
  CONSTRAINT `conversations_ibfk_1` FOREIGN KEY (`agent_id`) REFERENCES `agents` (`id`),
  CONSTRAINT `conversations_ibfk_2` FOREIGN KEY (`channel_id`) REFERENCES `channels` (`id`),
  CONSTRAINT `conversations_ibfk_3` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`),
  CONSTRAINT `conversations_ibfk_4` FOREIGN KEY (`organization_id`) REFERENCES `organizations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17502 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `messages` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `message` mediumtext DEFAULT NULL,
  `way` varchar(3) NOT NULL,
  `created` datetime(6) NOT NULL,
  `last_update` datetime NOT NULL,
  `status` varchar(20) NOT NULL,
  `conversation_id` bigint(20) unsigned NOT NULL,
  `contact_id` bigint(20) unsigned DEFAULT NULL,
  `agent_id` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `agent_id` (`agent_id`),
  KEY `contact_id` (`contact_id`),
  KEY `conversation_id` (`conversation_id`),
  CONSTRAINT `messages_ibfk_1` FOREIGN KEY (`agent_id`) REFERENCES `agents` (`id`),
  CONSTRAINT `messages_ibfk_2` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`),
  CONSTRAINT `messages_ibfk_3` FOREIGN KEY (`conversation_id`) REFERENCES `conversations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27845 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `conversation_notes` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `agent_id` bigint(20) unsigned DEFAULT NULL,
  `message` mediumtext DEFAULT NULL,
  `status` varchar(20) NOT NULL,
  `created` datetime(6) NOT NULL,
  `conversation_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `agent_id` (`agent_id`),
  KEY `conversation_id` (`conversation_id`),
  CONSTRAINT `conversation_notes_ibfk_1` FOREIGN KEY (`agent_id`) REFERENCES `agents` (`id`),
  CONSTRAINT `conversation_notes_ibfk_2` FOREIGN KEY (`conversation_id`) REFERENCES `conversations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1452 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `message_drafts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `content` mediumtext DEFAULT NULL,
  `created` datetime(6) NOT NULL,
  `last_updated` datetime NOT NULL,
  `conversation_id` bigint(20) unsigned NOT NULL,
  `agent_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `agent_id` (`agent_id`),
  KEY `conversation_id` (`conversation_id`),
  CONSTRAINT `message_drafts_ibfk_1` FOREIGN KEY (`agent_id`) REFERENCES `agents` (`id`),
  CONSTRAINT `message_drafts_ibfk_2` FOREIGN KEY (`conversation_id`) REFERENCES `conversations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=653 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `agents` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(250) DEFAULT NULL,
  `email` varchar(250) NOT NULL,
  `organization_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ix_agents_email` (`email`),
  UNIQUE KEY `email` (`email`,`organization_id`),
  KEY `organization_id` (`organization_id`),
  CONSTRAINT `agents_ibfk_1` FOREIGN KEY (`organization_id`) REFERENCES `organizations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=679 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `organizations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(250) NOT NULL,
  `slug` varchar(250) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ix_organizations_slug` (`slug`),
) ENGINE=InnoDB AUTO_INCREMENT=647 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Como posso melhorar a consulta acima? (A visualização contém esses valores porque posso pesquisar por nome de contato ou e-mail, por status, por agente, por caminho (entrada/saída), por canal, etc. Todas as colunas podem ser usadas na pesquisa.

Obrigado pela ajuda!

mysql

2 respostas

  1. Best Answer

    Você está extraindo muitas coisas em uma única visualização, mas posso sugerir duas otimizações:

    1. Como organization_ide contact_idsão declarados NOT NULL conversationse há restrições para ambos, você pode converter todos esses LEFT JOINs em JOINs simples. Isso poderia ajudar o otimizador a escolher um plano melhor.
    2. As subconsultas SELECT created FROM messages WHERE conversation_id = c.id AND ...são executadas para cada linha. Estes podem ser otimizados substituindo o KEY conversation_id (conversation_id)in messagespor KEY conversation_id_way_created (conversation_id, way, created DESC)que inclui todos os campos necessários à consulta na ordem correta.

    Muito tempo é gasto em cálculos last_update. Para cada mensagem ou conversa_note você executará uma, duas ou três seleções apenas para obter o valor last_update. Este bloco poderia ser simplificado para apenas dois SELECTs da seguinte maneira:

        IFNULL(
            (SELECT created FROM messages WHERE conversation_id = c.id ORDER BY way ASC, created DESC LIMIT 1),
            (SELECT created FROM message_drafts WHERE conversation_id = c.id)
            ) AS last_update

    Além disso, o valor last_update é calculado para cada mensagem, mas depende apenas do conversa_id, que é o mesmo para várias mensagens na mesma conversa. Você pode mover esse cálculo para o nível de conversa desta forma:

    SELECT c.id, c.organization_id, c.status, e.way, c.agent_id, c.subject AS subject, c.snooze_until, c.channel_id, c.source, c.contact_id, cn.name, cn.email, e.created, e.message, e.category, c.is_personal,
    IF(pc.customer_id IS NULL, 0, 1) AS is_customer,
    c.last_update
FROM (
    SELECT m.way, m.created, m.message, m.conversation_id, 'MESSAGE' AS category FROM messages m WHERE `status` != 'DRAFT'
    UNION
    SELECT NULL AS way, n.created AS created, n.message, n.conversation_id, 'NOTE' AS category FROM conversation_notes n WHERE `status` != 'DRAFT'
) AS e JOIN 
(SELECT conversations.*, 
    IFNULL(
            (SELECT created FROM messages WHERE conversation_id = conversations.id ORDER BY way ASC, created DESC LIMIT 1),
            (SELECT created FROM message_drafts WHERE conversation_id = conversations.id)
            ) AS last_update 
    FROM conversations
) AS c ON c.id = e.conversation_id JOIN contacts cn ON cn.id = c.contact_id
    JOIN processor_customers pc ON pc.contact_id = c.contact_id
) AS a
    • 1

  2. Índices:

    messages: INDEX(conversation_id, way, created)
message_drafts: INDEX(conversation_id,created)
pc:  INDEX(contact_id,  customer_id)

    Tente encontrar uma maneira que evite buscar mensagens criadas duas vezes.

    • 1

relate perguntas