我有一个 mysql View :
CREATE VIEW
loggingquarantine_quarantine ( id, mail_id, partition_tag, content, rs, subject, sender, TIME,
spam_level, size, sid, email ) AS
SELECT
concat(CAST(`mr`.`mail_id` AS CHAR(255) charset utf8),CAST(`mr`.`partition_tag` AS CHAR(255)
charset utf8)) AS `id`,
`mr`.`mail_id` AS `mail_id`,
`mr`.`partition_tag` AS `partition_tag`,
`mr`.`content` AS `content`,
`mr`.`rs` AS `rs`,
`m`.`subject` AS `subject`,
`m`.`from_addr` AS `sender`,
`m`.`time_num` AS `TIME`,
`m`.`spam_level` AS `spam_level`,
`m`.`size` AS `size`,
`m`.`sid` AS `sid`,
`maddr`.`email` AS `email`
FROM
(((`msgrcpt` `mr` JOIN `msgs` `m`
ON
(
`m`.`partition_tag` = `mr`.`partition_tag`
AND
`m`.`mail_id` = `mr`.`mail_id`
)
)
JOIN `maddr` maddr
ON
(
`mr`.`rid` = `maddr`.`id`
)
))
当我尝试对这个 View 进行计数时，250 万条记录大约需要 13 分钟。那太慢了。所有字段都有索引。如果我确实指望每张 table ，则不会超过 20 秒。 这是 mysql explain 显示的内容:
mysql> explain SELECT COUNT(*) FROM `loggingquarantine_quarantine`;
+----+-------------+-------+--------+-----------------------------------------------------------------------+-------------------------+---------+-----------------------------------------------------------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------------------------------------------------------------+-------------------------+---------+-----------------------------------------------------------+---------+-------------+
| 1 | SIMPLE | maddr | index | PRIMARY | maddr_partition_tag_idx | 5 | NULL | 1016497 | Using index |
| 1 | SIMPLE | mr | ref | PRIMARY,msgrcpt_idx_rid,msgrcpt_mail_id_idx,msgrcpt_partition_tag_idx | msgrcpt_idx_rid | 8 | mroute_logquar.maddr.id | 2 | Using index |
| 1 | SIMPLE | m | eq_ref | PRIMARY,msgs_mail_id_idx,msgs_partition_tag_idx | PRIMARY | 22 | mroute_logquar.mr.partition_tag,mroute_logquar.mr.mail_id | 1 | Using index |
+----+-------------+-------+--------+-----------------------------------------------------------------------+-------------------------+---------+-----------------------------------------------------------+---------+-------------+
我怎样才能优化查询/ View ，使计算不会花费 13 分钟。当前查询有什么问题？
更新。 如果我在不查看的情况下直接对选择进行选择计数，它仍然会得到相同的 14 分钟查询。
mysql> select count(1) FROM (((`msgrcpt` `mr` JOIN `msgs` `m` ON ( `m`.`partition_tag` = `mr`.`partition_tag` AND `m`.`mail_id` = `mr`.`mail_id` ) ) JOIN `maddr` maddr ON ( `mr`.`rid` = `maddr`.`id` ) ));
+----------+
| count(1) |
+----------+
| 2582227 |
+----------+
1 row in set (14 min 28.96 sec)
这是我在三个单独的查询中执行计数时的结果:
mysql> select count(1) from msgrcpt;
+----------+
| count(1) |
+----------+
| 2587307 |
+----------+
1 row in set (46.02 sec)
mysql> select count(1) from msgs;
+----------+
| count(1) |
+----------+
| 2421710 |
+----------+
1 row in set (7.77 sec)
mysql> select count(1) from maddr;
+----------+
| count(1) |
+----------+
| 994880 |
+----------+
1 row in set (0.23 sec)
更新 2.
所有表都是 InnoDB。
mysql> SHOW status like 'key_%'; +------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 26792 |
| Key_blocks_used | 0 |
| Key_read_requests | 0 |
| Key_reads | 0 |
| Key_write_requests | 0 |
| Key_writes | 0 |
+------------------------+-------+
msgs 和 msgrcpt 表有一个复合主键(msgs 的 mail_id, partation_tag 和 msgrpt 的 (partition_tag,mail_id,rseqnum) ).
更新
单表解释:
mysql> explain select count(1) from msgs;
+----+-------------+-------+-------+---------------+-------------------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------------------+---------+------+---------+-------------+
| 1 | SIMPLE | msgs | index | NULL | msgs_idx_time_num | 4 | NULL | 2357360 | Using index |
+----+-------------+-------+-------+---------------+-------------------+---------+------+---------+-------------+
1 row in set (0.00 sec)
mysql> explain select count(1) from msgrcpt;
+----+-------------+---------+-------+---------------+----------------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+----------------+---------+------+---------+-------------+
| 1 | SIMPLE | msgrcpt | index | NULL | msgrcpt_rs_idx | 3 | NULL | 2620758 | Using index |
+----+-------------+---------+-------+---------------+----------------+---------+------+---------+-------------+
1 row in set (0.00 sec)
mysql> explain select count(1) from maddr;
+----+-------------+-------+-------+---------------+-------------------------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------------------------+---------+------+--------+-------------+
| 1 | SIMPLE | maddr | index | NULL | maddr_partition_tag_idx | 5 | NULL | 967058 | Using index |
+----+-------------+-------+-------+---------------+-------------------------+---------+------+--------+-------------+
1 row in set (0.00 sec)
更新。 为所有表创建表:
mysql> show create table msgrcpt;
| Table | Create Table
| msgrcpt | CREATE TABLE `msgrcpt` (
`partition_tag` int(11) NOT NULL DEFAULT '0',
`mail_id` varbinary(16) NOT NULL,
`rseqnum` int(11) NOT NULL DEFAULT '0',
`rid` bigint(20) unsigned NOT NULL,
`is_local` char(1) NOT NULL DEFAULT '',
`content` char(1) NOT NULL DEFAULT '',
`ds` char(1) NOT NULL,
`rs` char(1) NOT NULL,
`bl` char(1) DEFAULT '',
`wl` char(1) DEFAULT '',
`bspam_level` float DEFAULT NULL,
`smtp_resp` varchar(255) DEFAULT '',
PRIMARY KEY (`partition_tag`,`mail_id`,`rseqnum`),
KEY `msgrcpt_idx_rid` (`rid`),
KEY `msgrcpt_mail_id_idx` (`mail_id`),
KEY `msgrcpt_rs_idx` (`rs`),
KEY `msgrcpt_ds_idx` (`ds`),
KEY `msgrcpt_partition_tag_idx` (`partition_tag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
| msgs | CREATE TABLE `msgs` (
`partition_tag` int(11) NOT NULL DEFAULT '0',
`mail_id` varbinary(16) NOT NULL,
`secret_id` varbinary(16) DEFAULT '',
`am_id` varchar(20) NOT NULL,
`time_num` int(10) unsigned NOT NULL,
`time_iso` char(16) NOT NULL,
`sid` bigint(20) unsigned NOT NULL,
`policy` varchar(255) DEFAULT '',
`client_addr` varchar(255) DEFAULT '',
`size` int(10) unsigned NOT NULL,
`originating` char(1) NOT NULL DEFAULT '',
`content` char(1) DEFAULT NULL,
`quar_type` char(1) DEFAULT NULL,
`quar_loc` varbinary(255) DEFAULT '',
`dsn_sent` char(1) DEFAULT NULL,
`spam_level` float DEFAULT NULL,
`message_id` varchar(255) DEFAULT '',
`from_addr` varchar(255) DEFAULT '',
`subject` varchar(255) DEFAULT '',
`host` varchar(255) NOT NULL,
PRIMARY KEY (`partition_tag`,`mail_id`),
KEY `msgs_idx_sid` (`sid`),
KEY `msgs_idx_mess_id` (`message_id`),
KEY `msgs_idx_time_num` (`time_num`),
KEY `msgs_mail_id_idx` (`mail_id`),
KEY `msgs_partition_tag_idx` (`partition_tag`),
KEY `msgs_content_idx` (`content`),
FULLTEXT KEY `ft_from_addr` (`from_addr`),
FULLTEXT KEY `ft_subject` (`subject`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
| maddr | CREATE TABLE `maddr` (
`partition_tag` int(11) DEFAULT '0',
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`email` varbinary(255) NOT NULL,
`domain` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `part_email` (`partition_tag`,`email`),
KEY `maddr_email_idx` (`email`),
KEY `maddr_partition_tag_idx` (`partition_tag`)
) ENGINE=InnoDB AUTO_INCREMENT=3373444 DEFAULT CHARSET=utf8 |
带有配置文件的查询:
mysql> SET PROFILING=1; SELECT
Query OK, 0 rows affected (0.00 sec)
-> count(1)
-> FROM
-> (((`msgrcpt` `mr` JOIN `msgs` `m`
-> ON
-> (
-> `m`.`partition_tag` = `mr`.`partition_tag`
-> AND
-> `m`.`mail_id` = `mr`.`mail_id`
-> )
-> )
-> JOIN `maddr` maddr
-> ON
-> (
-> `mr`.`rid` = `maddr`.`id`
-> )
-> )); SHOW PROFILE ALL;
+----------+
| count(1) |
+----------+
| 4279394 |
+----------+
1 row in set (23 min 56.61 sec)
+----------------------+------------+-----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+------------------+-------------+
| Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function | Source_file | Source_line |
+----------------------+------------+-----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+------------------+-------------+
| starting | 0.000161 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL |
| checking permissions | 0.000030 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | check_access | sql_parse.cc | 5043 |
| checking permissions | 0.000019 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | check_access | sql_parse.cc | 5043 |
| checking permissions | 0.000020 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | check_access | sql_parse.cc | 5043 |
| Opening tables | 0.000039 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | open_tables | sql_base.cc | 5014 |
| System lock | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 304 |
| init | 0.000040 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_select | sql_select.cc | 1041 |
| optimizing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 138 |
| statistics | 0.000063 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 358 |
| preparing | 0.000032 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 470 |
| executing | 0.000021 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | exec | sql_executor.cc | 137 |
| Sending data | 999.999999 | 97.014251 | 10.376423 | 681167 | 25822 | 5157072 | 1951032 | 0 | 0 | 4 | 277 | 0 | execute | sql_executor.cc | 758 |
| end | 0.000106 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_select | sql_select.cc | 1071 |
| query end | 0.000017 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 4761 |
| closing tables | 0.000021 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 4809 |
| freeing items | 0.000030 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_parse | sql_parse.cc | 5997 |
| logging slow query | 0.000059 | 0.000000 | 0.000000 | 0 | 0 | 0 | 8 | 0 | 0 | 0 | 0 | 0 | log_slow_statement | sql_parse.cc | 1720 |
| cleaning up | 0.000019 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | dispatch_command | sql_parse.cc | 1654 |
+----------------------+------------+-----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+------------------+-------------+
18 rows in set (0.02 sec)
表上的索引:
mysql> show index from msgs;
+-------+------------+------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| msgs | 0 | PRIMARY | 1 | partition_tag | A | 16 | NULL | NULL | | BTREE | | |
| msgs | 0 | PRIMARY | 2 | mail_id | A | 4174440 | NULL | NULL | | BTREE | | |
| msgs | 1 | msgs_idx_sid | 1 | sid | A | 2087220 | NULL | NULL | | BTREE | | |
| msgs | 1 | msgs_idx_mess_id | 1 | message_id | A | 4174440 | NULL | NULL | YES | BTREE | | |
| msgs | 1 | msgs_idx_time_num | 1 | time_num | A | 1391480 | NULL | NULL | | BTREE | | |
| msgs | 1 | msgs_mail_id_idx | 1 | mail_id | A | 4174440 | NULL | NULL | | BTREE | | |
| msgs | 1 | msgs_partition_tag_idx | 1 | partition_tag | A | 16 | NULL | NULL | | BTREE | | |
| msgs | 1 | msgs_content_idx | 1 | content | A | 16 | NULL | NULL | YES | BTREE | | |
| msgs | 1 | ft_from_addr | 1 | from_addr | NULL | 4174440 | NULL | NULL | YES | FULLTEXT | | |
| msgs | 1 | ft_subject | 1 | subject | NULL | 4174440 | NULL | NULL | YES | FULLTEXT | | |
+-------+------------+------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
10 rows in set (0.97 sec)
MSGRCPT
mysql> show index from msgrcpt;
+---------+------------+---------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+---------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| msgrcpt | 0 | PRIMARY | 1 | partition_tag | A | 29 | NULL | NULL | | BTREE | | |
| msgrcpt | 0 | PRIMARY | 2 | mail_id | A | 5218535 | NULL | NULL | | BTREE | | |
| msgrcpt | 0 | PRIMARY | 3 | rseqnum | A | 5218535 | NULL | NULL | | BTREE | | |
| msgrcpt | 1 | msgrcpt_idx_rid | 1 | rid | A | 347902 | NULL | NULL | | BTREE | | |
| msgrcpt | 1 | msgrcpt_mail_id_idx | 1 | mail_id | A | 5218535 | NULL | NULL | | BTREE | | |
| msgrcpt | 1 | msgrcpt_rs_idx | 1 | rs | A | 29 | NULL | NULL | | BTREE | | |
| msgrcpt | 1 | msgrcpt_ds_idx | 1 | ds | A | 29 | NULL | NULL | | BTREE | | |
| msgrcpt | 1 | msgrcpt_partition_tag_idx | 1 | partition_tag | A | 29 | NULL | NULL | | BTREE | | |
+---------+------------+---------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
8 rows in set (0.70 sec)
地址:
mysql> show index from maddr;
+-------+------------+-------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| maddr | 0 | PRIMARY | 1 | id | A | 1653970 | NULL | NULL | | BTREE | | |
| maddr | 0 | part_email | 1 | partition_tag | A | 19 | NULL | NULL | YES | BTREE | | |
| maddr | 0 | part_email | 2 | email | A | 1653970 | NULL | NULL | | BTREE | | |
| maddr | 1 | maddr_email_idx | 1 | email | A | 1653970 | NULL | NULL | | BTREE | | |
| maddr | 1 | maddr_partition_tag_idx | 1 | partition_tag | A | 19 | NULL | NULL | YES | BTREE | | |
+-------+------------+-------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.41 sec)
Inno 数据库缓冲区大小
mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| innodb_buffer_pool_size | 2147483648 |
+-------------------------+------------+
1 row in set (0.02 sec)
最佳答案
您是否尝试过使用一些 STRAIGHT_JOIN 而不是简单的 JOIN 来改变 JOIN 中使用的表的顺序？有时查询优化器不会为特定查询选择理想的顺序。换句话说，您认为它的性能如何？
SELECT
concat(CAST(`mr`.`mail_id` AS CHAR(255) charset utf8),CAST(`mr`.`partition_tag` AS CHAR(255) charset utf8)) AS `id`,
`mr`.`mail_id` AS `mail_id`,
`mr`.`partition_tag` AS `partition_tag`,
`mr`.`content` AS `content`,
`mr`.`rs` AS `rs`,
`m`.`subject` AS `subject`,
`m`.`from_addr` AS `sender`,
`m`.`time_num` AS `TIME`,
`m`.`spam_level` AS `spam_level`,
`m`.`size` AS `size`,
`m`.`sid` AS `sid`,
`maddr`.`email` AS `email`
FROM
`msgrcpt` `mr`
STRAIGHT_JOIN `msgs` `m`
ON
`m`.`partition_tag` = `mr`.`partition_tag` AND
`m`.`mail_id` = `mr`.`mail_id`
STRAIGHT_JOIN `maddr` maddr
ON
`mr`.`rid` = `maddr`.`id`
或者由于您在 msgrcpt 表上使用了一些繁重的转换和强制转换，如果您像这样强制它在查询的最后加入，会发生什么情况？
SELECT
concat(CAST(`mr`.`mail_id` AS CHAR(255) charset utf8),CAST(`mr`.`partition_tag` AS CHAR(255) charset utf8)) AS `id`,
`mr`.`mail_id` AS `mail_id`,
`mr`.`partition_tag` AS `partition_tag`,
`mr`.`content` AS `content`,
`mr`.`rs` AS `rs`,
`m`.`subject` AS `subject`,
`m`.`from_addr` AS `sender`,
`m`.`time_num` AS `TIME`,
`m`.`spam_level` AS `spam_level`,
`m`.`size` AS `size`,
`m`.`sid` AS `sid`,
`maddr`.`email` AS `email`
FROM
`maddr` `maddr`
STRAIGHT_JOIN `msgrcpt` `mr`
ON
`maddr`.`id` = `mr.`rid`
STRAIGHT_JOIN `msgs` `m`
ON
`m`.`partition_tag` = `mr`.`partition_tag` AND
`m`.`mail_id` = `mr`.`mail_id`
如果需要，您可以尝试更多订单，可能有一个“神奇订单”导致您的查询压缩。例如，另一种变体可能是:
FROM
`msgs` `m`
STRAIGHT_JOIN `msgrcpt` `mr`
ON
`m`.`partition_tag` = `mr`.`partition_tag` AND
`m`.`mail_id` = `mr`.`mail_id`
STRAIGHT_JOIN `maddr`
ON
`maddr`.`id` = `mr`.`rid`
...等等。
此外，在您的 SELECT 列中，所有这些强制转换都会稍微降低您的性能，因为无论您选择多少行，它都必须这样做，这可能很多。您是否考虑过只向已计算的 msgrcpt 表添加一列，这样它就不必在查询中执行此操作？如果您不想在代码中考虑额外的列，则可以向数据库添加触发器以使用计算值高效自动地更新它。通常，使用这种优化有点矫枉过正，但在对数百万行执行 SELECT 查询时，它可能就可以解决问题。
编辑:这是我建议的更改 msgrcpt 表的方法。由于它正在运行一个触发器来计算新的列值，因此它会使插入行的速度变慢非常小，但我认为它会在加快您尝试运行的查询速度方面得到返回。标准免责声明适用——如果没有先在测试副本上进行彻底测试，请勿在您的生产数据库上运行它!
-- The UPDATE command will probably take some time to run since it's updating
-- millions of rows. Be patient!
ALTER TABLE `msgrcpt` ADD COLUMN `friendly_id` TEXT NULL AFTER `rs`;
UPDATE `msgrcpt` SET
`friendly_id` = CONCAT(CAST(`mail_id` AS CHAR(255) CHARSET utf8),
CAST(`partition_tag` AS CHAR(255) CHARSET utf8));
DELIMITER $$
CREATE TRIGGER `trig_calc_id` BEFORE INSERT ON `msgrcpt`
FOR EACH ROW BEGIN
SET NEW.`friendly_id` =
CONCAT(CAST(NEW.`mail_id` AS CHAR(255) CHARSET utf8),
CAST(NEW.`partition_tag` AS CHAR(255) CHARSET utf8));
END $$
CREATE TRIGGER `trig_update_id` BEFORE UPDATE ON `msgrcpt`
FOR EACH ROW BEGIN
SET NEW.`friendly_id` =
CONCAT(CAST(NEW.`mail_id` AS CHAR(255) CHARSET utf8),
CAST(NEW.`partition_tag` AS CHAR(255) CHARSET utf8));
END $$
DELIMITER ;
现在不要选择那些乱七八糟的 CONCATed CASTed 东西，只需选择 mr.friendly_id(或您选择命名该列的任何内容)。你的表现应该会好很多。
希望这对您有所帮助，让我知道进展如何!
关于MySql 对连接查询的计数非常慢，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/11253996/
我正在用Ruby编写一个简单的程序来检查域列表是否被占用。基本上它循环遍历列表，并使用以下函数进行检查。require'rubygems'require'whois'defcheck_domain(domain)c=Whois::Client.newc.query("google.com").available?end程序不断出错(即使我在google.com中进行硬编码)，并打印以下消息。鉴于该程序非常简单，我已经没有什么想法了-有什么建议吗？/Library/Ruby/Gems/1.8/gems/whois-2.0.2/lib/whois/server/adapters/base.
我正在使用Sequel构建一个愿望list系统。我有一个wishlists和itemstable和一个items_wishlists连接表(该名称是续集选择的名称)。items_wishlists表还有一个用于facebookid的额外列(因此我可以存储opengraph操作)，这是一个NOTNULL列。我还有Wishlist和Item具有续集many_to_many关联的模型已建立。Wishlist类也有:selectmany_to_many关联的选项设置为select:[:items.*,:items_wishlists__facebook_action_id].有没有一种方法可以
我知道我可以指定某些字段来使用pluck查询数据库。ids=Item.where('due_at但是我想知道，是否有一种方法可以指定我想避免从数据库查询的某些字段。某种反拔？posts=Post.where(published:true).do_not_lookup(:enormous_field) 最佳答案 Model#attribute_names应该返回列/属性数组。您可以排除其中一些并传递给pluck或select方法。像这样:posts=Post.where(published:true).select(Post.attr
我使用的是Firefox版本36.0.1和Selenium-Webdrivergem版本2.45.0。我能够创建Firefox实例，但无法使用脚本继续进行进一步的操作无法在60秒内获得稳定的Firefox连接(127.0.0.1:7055)错误。有人能帮帮我吗？ 最佳答案 我遇到了同样的问题。降级到firefoxv33后一切正常。您可以找到旧版本here 关于ruby-无法在60秒内获得稳定的Firefox连接(127.0.0.1:7055)，我们在StackOverflow上找到一个类
尝试在我的RoR应用程序中实现计数器缓存列时出现错误Unknownkey(s):counter_cache。我在这个问题中实现了模型关联:Modelassociationquestion这是我的迁移:classAddVideoVotesCountToVideos0Video.reset_column_informationVideo.find(:all).eachdo|p|p.update_attributes:videos_votes_count,p.video_votes.lengthendenddefself.downremove_column:videos,:video_vot
我正在尝试按0-9和a-z的顺序创建数字和字母列表。我有一组值value_array=['0','1','2','3','4','5','6','7','8','9','a','b'，'光盘'，'e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','','u','v','w','x','y','z']和一个组合列表的数组，按顺序，这些数字可以产生x个字符，比方说三个list_array=[]和一个当前字母和数字组合的数组(在将它插入列表数组之前我会把它变成一个字符串，]current_combo['0','0','0']
文章目录一、概述简介原理模块二、配置Mysql使用版本环境要求1.操作系统2.mysql要求三、配置canal-server离线下载在线下载上传解压修改配置单机配置集群配置分库分表配置1.修改全局配置2.实例配置垂直分库水平分库3.修改group-instance.xml4.启动监听四、配置canal-adapter1修改启动配置2配置映射文件3启动ES数据同步查询所有订阅同步数据同步开关启动4.验证五、配置canal-admin一、概述简介canal是Alibaba旗下的一款开源项目，Java开发。基于数据库增量日志解析，提供增量数据订阅&消费。Git地址：https://github.co
我正在尝试查询我的Rails数据库(Postgres)中的购买表，我想查询时间范围。例如，我想知道在所有日期的下午2点到3点之间进行了多少次购买。此表中有一个created_at列，但我不知道如何在不搜索特定日期的情况下完成此操作。我试过:Purchases.where("created_atBETWEEN?and?",Time.now-1.hour,Time.now)但这最终只会搜索今天与那些时间的日期。 最佳答案 您需要使用PostgreSQL'sdate_part/extractfunction从created_at中提取小时
Rails相对较新。我正在尝试调用一个API，它应该向我返回一个唯一的URL。我的应用程序中捆绑了HTTParty。我已经创建了一个UniqueNumberController，并且我已经阅读了几个HTTParty指南，直到我想要什么，但也许我只是有点迷路，真的不知道该怎么做。基本上，我需要做的就是调用API，获取它返回的URL，然后将该URL插入到用户的数据库中。谁能给我指出正确的方向或与我分享一些代码？ 最佳答案 假设API为JSON格式并返回如下数据:{"url":"http://example.com/unique-url"
require"socket"server="irc.rizon.net"port="6667"nick="RubyIRCBot"channel="#0x40"s=TCPSocket.open(server,port)s.print("USERTesting",0)s.print("NICK#{nick}",0)s.print("JOIN#{channel}",0)这个IRC机器人没有连接到IRC服务器，我做错了什么？ 最佳答案 失败并显示此消息::irc.shakeababy.net461*USER:Notenoughparame