JIStone's questions

在执行大型二进制插入时，有时会出现“msyql 服务器已消失”错误，因此我想检查 mysql 上的设置，例如wait_timeout和max_allowed_packet. 然而

show create table mysql.user

回报

[Err] 1142 - SELECT command denied to user '557574_prod'@'96.25.95.162' for table 'user'

每当我想了解一些相关设置时，我都不想寻求技术支持，所以我想知道是否有解决方法？

我无法控制 和 之类的东西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_connect() [function.mysql-connect]：用户 '557574_prod' 已超出 'max_user_connections' 资源（当前值：10）

这会阻止网站被访问并有效地关闭。

（显然增加“max_user_connections”可以解决这个问题，但这不是我们托管方案的一个选项）

我可能已经“修复”了它，但我不是 100% 确定我理解这个问题并且我想澄清一下。我认为问题一定是由于太多的 cron 作业耗尽了连接（我们每分钟运行几个），我减少了 cron 作业的数量，这似乎减少了错误的数量。我为 cron 作业创建了一个单独的用户帐户，所以如果这是问题，现在应该解决。

但是，我们的日志系统在错误发生时并没有记录，所以我还是很担心。

所以我要问：什么是 MySQL 中的“user_connection”？

• 每个数据库调用是否有单独的连接？
• 如果没有，数据库调用何时需要单独的连接？
• 为什么这个错误只会发生在现场制作网站上？也许每个唯一的ip或simialr都有一个单独的连接？

我觉得我想多了。. .

好的，我正在向表中添加索引，并使用outputshow processlist和stateoutput检查进度Repair by sorting。

从 MySQL 文档：

分类修复

修复代码使用排序来创建索引。

那么什么是“修复代码”？有什么东西坏了吗？

我有一个在 和 上有一个多列索引UNIQUE的表。_job_id____keyword_id__

__job_id__如果我有一个GROUP BY在该列上执行的频繁查询，我是否还需要添加另一个索引？

（在 1 亿行可能需要一段时间。这就是为什么我要问而不是只做）

在 MySQL 中删除重复索引需要相当长的时间，所以在等待期间我搜索了它并找到了这篇2006 年的帖子，讨论 MySQL 如何处理ADD和DROP索引。

如果表 T 是具有四个索引（ndx1、ndx2、ndx3、ndx4）的 MySQL 表，并且您想要“更改表 T 删除索引 ndx3；” 这正是引擎盖下发生的事情：

1) MySQL 将 T.MYD 复制到一个临时表，即 S.MYD 和一个零字节的 S.MYI。2) MySQL does 'alter table S add index ndx1 (...); 3) MySQL does 'alter table S add index ndx2 (...); 4) MySQL does 'alter table S add index ndx4 (...); 5）MySQL删除T.MYD，删除T.MYI 6）MySQL将S.MYD重命名为T.MYD，将S.MYI重命名为T.MYI

这仍然是真的吗？他的建议仍然有效吗？

给定具有四个索引（ndx1、ndx2、ndx3、ndx4）的同一个 MyISAM 表 T，并且您想“更改表 T 删除索引 ndx3；” 试试这个：

1）像T一样创建表T1；这将创建一个空表 T1，其索引为 ndx1、ndx2、ndx3 和 ndx4。2) 改变表 T1 删除索引 ndx3；这会在空的 T1 上删除索引 ndx3，这应该是瞬时的。3) 插入到 T1 select * from T; 这将填充表 T 并一次加载 T1 的所有三 (3) 个索引。4) 删除表表T；5) 改变表 T1 重命名为 T；

你们都如何处理在大型表中添加和删除索引？

有时在 SNAFU 期间，我必须跑kill query xxxxxxx二十或三十次。kill all我缺少任何命令吗？

因为我不喜欢打字。

我正在处理的站点有很多后端/cron 活动，因此每天每秒执行 5 到 15 个查询。这可以将页面加载速度减慢一秒或更长时间。

我在想最好的办法是创建两个数据库并每天同步它们。然而：

• 我不知道该怎么做
• 我担心同步会很慢并阻止数据库访问。我真的不喜欢让网站离线的想法，即使是凌晨 3 点 10 分钟。

所以我想知道：

• 单独的数据库是解决此问题的最佳解决方案，还是其他更好的解决方案？
• 如何在不过多干扰“用户体验”的情况下同步数据库？

非常感谢！

顺便说一句，在“可能”服务器上运行 PHP/MySQL。

我只能想象用两个数据库查询来做到这一点。第一个查找数据库中的行数，第二个ORDER BY在我感兴趣的列上选择一个，LIMIT X, 1其中 X 是行数的一半。

有没有一种简单的方法可以只用一个查询来做到这一点？

现在我在计算中使用平均值，但我认为平均值会更好；这些值没有上限，它们从下方以 0 为界。

编辑：是的，我的意思是说“中值”，但有一些大脑错误并搜索“平均值”。我现在在stackoverflow上找到了答案