mysql - SQL 查询速度呈指数级下降

Question

我有一个联系消息系统的查询，随着我加入的次数越多，该系统的速度就越慢。

表结构基本上是一个联系人表，一个联系人字段表。

查询多次连接联系人字段表，我每次连接都需要两倍的时间。

这是查询。

SELECT  SQL_CALC_FOUND_ROWS
    `contact_data`.`id`,
    `contact_data`.`name`,
    `fields0`.`value` AS `fields0`,
    `fields1`.`value` AS `fields1`,
    `fields2`.`value` AS `fields2`,
    ...etc...
    CONTACT_DATA_TAGS(
        GROUP_CONCAT(DISTINCT `contact_data_tags`.`name`),
        GROUP_CONCAT(DISTINCT `contact_data_assignment`.`user`),
        GROUP_CONCAT(DISTINCT `contact_data_read`.`user`)
    ) AS `tags`,
    GROUP_CONCAT(DISTINCT `contact_data_assignment`.`user`) AS `assignments`,
    `contact_data`.`updated`,
    `contact_data`.`created`
FROM
    `contact_data`
LEFT JOIN contact_data_tags ON contact_data.`id` = contact_data_tags.`data`
LEFT JOIN contact_data_assignment ON contact_data.`id` = contact_data_assignment.`data`
LEFT JOIN contact_data_read ON contact_data.`id` = contact_data_read.`data`
LEFT JOIN contact_data_fields AS fields0 ON contact_data.`id` = fields0.`contact_data_id` AND fields0.`key` = :field1
LEFT JOIN contact_data_fields AS fields1 ON contact_data.`id` = fields1.`contact_data_id` AND fields1.`key` = :field2
LEFT JOIN contact_data_fields AS fields2 ON contact_data.`id` = fields2.`contact_data_id` AND fields2.`key` = :field3
...etc...
GROUP BY contact_data.`id`
ORDER BY `id` DESC

这是表结构:

CREATE TABLE IF NOT EXISTS `contact_data` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `format` varchar(50) NOT NULL,
  `fields` longtext NOT NULL,
  `url` varchar(2000) NOT NULL,
  `referer` varchar(2000) DEFAULT NULL,
  `ip` varchar(40) NOT NULL,
  `agent` varchar(1000) DEFAULT NULL,
  `created` datetime NOT NULL,
  `updated` datetime NOT NULL,
  `updater` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`),
  KEY `url` (`url`(333)),
  KEY `ip` (`ip`),
  KEY `created` (`created`),
  KEY `updated` (`updated`),
  KEY `updater` (`updater`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `contact_data_assignment` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user` int(10) unsigned NOT NULL,
  `data` int(10) unsigned NOT NULL,
  `created` datetime NOT NULL,
  `updater` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_assignment` (`user`,`data`),
  KEY `user` (`user`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `contact_data_fields` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `contact_data_id` int(10) unsigned NOT NULL,
  `key` varchar(200) NOT NULL,
  `value` text NOT NULL,
  `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `contact_data_id` (`contact_data_id`),
  KEY `key` (`key`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `contact_data_read` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user` int(10) unsigned NOT NULL,
  `data` int(10) unsigned NOT NULL,
  `type` enum('admin','email') NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user` (`user`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `contact_data_tags` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `data` int(10) unsigned NOT NULL,
  `created` datetime NOT NULL,
  `updater` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_tag` (`name`,`data`),
  KEY `name` (`name`),
  KEY `data` (`data`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

DELIMITER $$
CREATE FUNCTION `contact_data_tags`(`tags` TEXT, `assigned` BOOL, `read` BOOL) RETURNS text CHARSET latin1
BEGIN
    RETURN CONCAT(
        ',',
        IFNULL(`tags`, ''),
        ',',
        IF(`tags` IS NULL OR FIND_IN_SET('Closed', `tags`) = 0, 'Open', ''),
        ',',
        IF(`assigned` IS NULL, 'Unassigned', ''),
        ',',
        IF(`read` IS NULL, 'New', ''),
        ','
    );
END$$

DELIMITER ;

有人知道为什么它运行这么慢吗？我该怎么做才能让它更快？我是否需要调整查询(我不想调整结构)？我可以设置任何配置选项来加快速度吗？

同样奇怪的是，与我的 Debain 生产服务器相比，它似乎在我的 Windows 开发机器上运行得更快(几乎是即时的，而 30 秒以上)。

但 Windows 机器远不如 Debain 服务器(8 核 Xeon，32GB RAM)强大。

在 Debian 上运行 MySQL 5.1.49(我无法更新)，在 Windows 上运行 5.5.28。

所以读到 EAV 在 RDBMS 中表现不佳(或者至少在我的情况下)，是一个配置选项，我可以增加它来使这个运行更快(即我可以向它投入更多的 RAM) ？

Answer 1

加快查询速度的一种方法是链接到 contact_data_fields 一次(在 contact_data.id = contact_data_fields.contact_data_id 上)和将 fields 列更改为 max 表达式 - 如下所示:

SELECT  SQL_CALC_FOUND_ROWS
    `contact_data`.`id`,
    `contact_data`.`name`,
    MAX(CASE WHEN fields.`key` = :field1 THEN fields.`value` END) AS `fields0`,
    MAX(CASE WHEN fields.`key` = :field2 THEN fields.`value` END) AS `fields1`,
    MAX(CASE WHEN fields.`key` = :field3 THEN fields.`value` END) AS `fields2`,
    ...etc...
    CONTACT_DATA_TAGS(
        GROUP_CONCAT(DISTINCT `contact_data_tags`.`name`),
        GROUP_CONCAT(DISTINCT `contact_data_assignment`.`user`),
        GROUP_CONCAT(DISTINCT `contact_data_read`.`user`)
    ) AS `tags`,
    GROUP_CONCAT(DISTINCT `contact_data_assignment`.`user`) AS `assignments`,
    `contact_data`.`updated`,
    `contact_data`.`created`
FROM
    `contact_data`
LEFT JOIN contact_data_tags ON contact_data.`id` = contact_data_tags.`data`
LEFT JOIN contact_data_assignment ON contact_data.`id` = contact_data_assignment.`data`
LEFT JOIN contact_data_read ON contact_data.`id` = contact_data_read.`data`
LEFT JOIN contact_data_fields AS fields
       ON contact_data.`id` = fields.`contact_data_id` 
...etc...
GROUP BY contact_data.`id`
ORDER BY `id` DESC