我在 MySQL 数据库上做了一个相当复杂的视图,我意识到有些查询很慢。我的第一个想法是添加索引,但不可能在视图上执行此操作,因此我不知道如何提高查询的性能。
我将在这里分享我的视图查询,我将尽力解释它,然后我将分享我的数据库结构(精简到仅视图所需的内容)。
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
);
说明:此视图加载与工单相关的一些列,包括:
- 票证 ID
- 工单受影响的organization_id
- 状态(关闭、打开等)
- 最后一条消息的发送方式(接收或发送)
- 分配的代理 ID
- 主题
- 贪睡时间(如果有)
- 频道 ID(如[email protected])
- 联系人 ID(来自电子邮件)
- 联系人姓名
- 联系人的电子邮件
- 票证的创建日期
- 最后一条消息内容
- 类别(如果最后一个条目是消息或注释)
- 如果收到的消息是个人的(发送电子邮件的渠道是个人的,例如[email protected],或者对团队中的任何人开放,例如 support@..)
- 如果联系人是客户
- 票证上次更新时间
- 用于按优先级排序的列
- 按日期排序的列
- 按等待时间较长进行排序的列
我绝对不是数据库专家,所以也许该查询已经有大量可用的改进,但我意识到在生产中,一个简单的查询如下:
从对话搜索中选择 COUNT(id),其中组织 ID = X;运行大约需要 2.5 秒。我尝试了几个不同的 ID,结果在 900 到 18000 之间,但仍然需要大约 2.5 秒。
显然,它缺少一些优化......
这是我的数据库结构(为了简单起见,我删除了一些无用的列):
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;
如何改进上面的查询?(该视图包含这些值,因为我可以按联系人姓名或电子邮件、按状态、按代理、按方式(进/出)、按渠道等进行搜索。搜索时可以使用所有列。
感谢您的帮助!