主页 / dba / 问题 / 2915
Accepted
JIStone
Asked: 2011-05-25 10:08:30 +0800 CST

当我无法控制数据库变量时处理临时表

  • 772

我无法控制 和 之类的东西tmp_table_size，max_heap_table_size因此随着我们的表的增长，需要临时表的查询所花费的时间呈几何级数增长。

我想知道是否有办法阻止 MySQL 使用临时表进行这些查询？在这种情况下最好的方法是什么：

这是最大的罪犯的例子：

SELECT `skills`.`id`
FROM (`jobs_skills`)
JOIN `jobs` ON (`jobs`.`id` = `jobs_skills`.`job_id`)
JOIN `skills` ON (`skills`.`id` = `jobs_skills`.`skill_id`)
WHERE `jobs`.`job_visibility_id` = 1
AND `jobs`.`active` = 1
AND `skills`.`valid` = 1
AND `jobs_skills`.`skill_id` IN (96,101,103,108,121,2610,99,119,2607,102,104,112,113,122,1032,1488,2608,109,126,1438,2310,2318,2622,118,1046,1387,2609,100,116,123,2611,2612,2616,2618,114,127,1562,1587,1608,2276,2615,125,1070,1071,1161,1658,2613,2614,2617,105,110,111,120,1394,1435)
GROUP BY `jobs_skills`.`job_id`

其中copying to temp table耗时 107 秒，占总查询时间的 99%。

尽管担心 tl;dr 综合症，我还是提供了。. .

更多细节

这是EXPLAIN查询语句：

+----+-------------+-------------+--------+----------------------+--------------+---------+----------------------------------+--------+----------------------------------------------+
| id | select_type | table       | type   | possible_keys        | key          | key_len | ref                              | rows   | Extra                                        |
+----+-------------+-------------+--------+----------------------+--------------+---------+----------------------------------+--------+----------------------------------------------+
|  1 | SIMPLE      | jobs        | ref    | PRIMARY,active_index | active_index | 1       | const                            | 468958 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | jobs_skills | ref    | PRIMARY              | PRIMARY      | 4       | 557574_prod.jobs.id              |      1 | Using where; Using index                     |
|  1 | SIMPLE      | skills      | eq_ref | PRIMARY              | PRIMARY      | 4       | 557574_prod.jobs_skills.skill_id |      1 | Using where                                  |
+----+-------------+-------------+--------+----------------------+--------------+---------+----------------------------------+--------+----------------------------------------------+

以下CREATE TABLE是相关表格的说明：

| jobs  | CREATE TABLE `jobs` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `user_id` int(10) unsigned NOT NULL,
  `title` varchar(40) NOT NULL,
  `description` text NOT NULL,
  `address_id` int(10) unsigned NOT NULL,
  `proximity` smallint(3) unsigned NOT NULL default '15',
  `job_payrate_id` tinyint(1) unsigned NOT NULL default '1',
  `payrate` int(10) unsigned NOT NULL,
  `start_date` int(10) unsigned NOT NULL,
  `job_start_id` tinyint(1) unsigned NOT NULL default '1',
  `duration` tinyint(1) unsigned NOT NULL COMMENT 'Full-time, Part-time, Flexible',
  `posting_date` int(10) unsigned NOT NULL,
  `revision_date` int(10) unsigned NOT NULL,
  `expiration` int(10) unsigned NOT NULL,
  `active` tinyint(1) unsigned NOT NULL default '1',
  `team_size` tinyint(2) unsigned NOT NULL default '1',
  `job_type_id` tinyint(1) unsigned NOT NULL default '1',
  `job_shift_id` tinyint(1) unsigned NOT NULL default '1',
  `job_visibility_id` tinyint(1) unsigned NOT NULL default '1',
  `position_count` smallint(5) unsigned NOT NULL default '1',
  `impressions` int(10) unsigned NOT NULL default '0',
  `clicks` int(10) unsigned NOT NULL default '0',
  `employer_email` varchar(100) NOT NULL default '',
  `job_source_id` smallint(6) unsigned NOT NULL default '0',
  `job_password` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY `active_index` (`active`),
  KEY `user_id_index` (`user_id`),
  KEY `address_id_index` (`address_id`),
  KEY `posting_date_index` USING BTREE (`posting_date`)
) ENGINE=InnoDB AUTO_INCREMENT=875013 DEFAULT CHARSET=utf8

-

| jobs_skills | CREATE TABLE `jobs_skills` (
  `job_id` int(10) unsigned NOT NULL,
  `skill_id` int(10) unsigned NOT NULL,
  `required` tinyint(1) unsigned NOT NULL,
  PRIMARY KEY  (`job_id`,`skill_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

-

| skills | CREATE TABLE `skills` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `parent_id` int(10) unsigned NOT NULL,
  `name` varchar(35) NOT NULL default '',
  `description` varchar(250) NOT NULL,
  `valid` tinyint(1) unsigned NOT NULL default '0',
  `is_category` tinyint(1) unsigned NOT NULL default '0',
  `last_edited` int(10) unsigned NOT NULL default '0',
  `impressions` int(10) unsigned NOT NULL default '0',
  `clicks` int(10) unsigned NOT NULL default '0',
  `jobs` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `name` (`name`),
  KEY `parent` (`parent_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2657 DEFAULT CHARSET=utf8 |

就像我说的那样，这不是唯一有此问题的查询，因此任何一般性建议都是最有帮助的，但我不会拒绝任何针对此查询的特定建议。

mysql query

1 个回答

  1. Best Answer

    您的原始子查询：

    SELECT `skills`.`id`
FROM (`jobs_skills`)
JOIN `jobs` ON (`jobs`.`id` = `jobs_skills`.`job_id`)
JOIN `skills` ON (`skills`.`id` = `jobs_skills`.`skill_id`)
WHERE `jobs`.`job_visibility_id` = 1
AND `jobs`.`active` = 1
AND `skills`.`valid` = 1
AND `jobs_skills`.`skill_id` IN (96,101,103,108,121,2610,99,119,2607,102,104,112,113,122,1032,1488,2608,109,126,1438,2310,2318,2622,118,1046,1387,2609,100,116,123,2611,2612,2616,2618,114,127,1562,1587,1608,2276,2615,125,1070,1071,1161,1658,2613,2614,2617,105,110,111,120,1394,1435)
GROUP BY `jobs_skills`.`job_id`

    您需要以控制和微管理正在创建的临时表及其大小的方式重构查询。仅基于 JOIN、WHERE 和 GROUP BY 子句，您需要实现以下更改：

    jobs 需要在 job_visibility_id,active,id 上建立索引

    需要子查询

    (SELECT id job_id FROM jobs WHERE job_visibility_id=1 AND active=1 ORDER BY id)

    skills 需要在 valid,id 上建立索引

    需要子查询

    (SELECT id skill_id FROM skills WHERE valid=1 ORDER BY id)

    jobs_skills 需要在 skill_id,job_id 上建立索引

    需要子查询

    (SELECT job_id FROM jobs_skills WHERE skill_id IN (96,101,103,108,121,2610,99,119,2607,102,104,112,113,122,1032,1488,2608,109,126,1438,2310,2318,2622,118,1046,1387,2609,100,116,123,2611,2612,2616,2618,114,127,1562,1587,1608,2276,2615,125,1070,1071,1161,1658,2613,2614,2617,105,110,111,120,1394,1435) ORDER BY skill_id,job_id)

    SQL创建所需的索引

    ALTER TABLE jobs ADD INDEX (job_visibility_id,active,id);
ALTER TABLE skills ADD INDEX (valid,id);
ALTER TABLE jobs_skills ADD INDEX (skill_id,job_id);

    现在结合子查询形成 VOLTRON

    SELECT skill_id
FROM (SELECT JS.*
FROM (SELECT skill_id,job_id FROM jobs_skills WHERE skill_id IN (96,101,103,108,121,2610,99,119,2607,102,104,112,113,122,1032,1488,2608,109,126,1438,2310,2318,2622,118,1046,1387,2609,100,116,123,2611,2612,2616,2618,114,127,1562,1587,1608,2276,2615,125,1070,1071,1161,1658,2613,2614,2617,105,110,111,120,1394,1435) ORDER BY skill_id,job_id) JS
INNER JOIN
(SELECT id job_id FROM jobs WHERE job_visibility_id=1 AND active=1 ORDER BY id) J
USING (job_id) INNER JOIN
(SELECT id skill_id FROM skills WHERE valid=1 ORDER BY id) S USING (skill_id)
) A
GROUP BY job_id;

    试试看 ！！！

    顺便说一句，如果语法不正确，我会尝试调整它！

    • 5

相关问题