<分区>
我有一个包含大约 500 万行的数据库，我遇到了查询耗时很长(超过一分钟)的问题。我希望这些信息足以让别人给我一些建议，如果我需要发布更多信息，请告诉我。
提前感谢您的任何建议
EXPLAIN SELECT count( * ) AS count
FROM vtiger_time
INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_time.timeid
INNER JOIN vtiger_crmentityrel ON ( vtiger_crmentityrel.relcrmid = vtiger_crmentity.crmid
OR vtiger_crmentityrel.crmid = vtiger_crmentity.crmid )
LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid
LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid
WHERE vtiger_crmentity.deleted =0
AND (
vtiger_crmentityrel.crmid =211294
OR vtiger_crmentityrel.relcrmid =211294
)
+----+-------------+---------------------+-------------+-------------------------------------------------+----------------+---------+--------------------------------+-------+------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+-------------+-------------------------------------------------+----------------+---------+--------------------------------+-------+------------------------------------------+
| 1 | SIMPLE | vtiger_crmentityrel | index_merge | crmid,relcrmid | crmid,relcrmid | 4,4 | NULL | 5881 | Using union(crmid,relcrmid); Using where |
| 1 | SIMPLE | vtiger_crmentity | ref | PRIMARY,deleted,deleted_2,crmentity_multi_index | deleted_2 | 4 | const | 84424 | Using where; Using index |
| 1 | SIMPLE | vtiger_users | eq_ref | PRIMARY | PRIMARY | 4 | crm.vtiger_crmentity.smownerid | 1 | Using index |
| 1 | SIMPLE | vtiger_groups | eq_ref | PRIMARY | PRIMARY | 4 | crm.vtiger_crmentity.smownerid | 1 | Using index |
| 1 | SIMPLE | vtiger_time | eq_ref | PRIMARY,timeid | PRIMARY | 4 | crm.vtiger_crmentity.crmid | 1 | Using index |
+----+-------------+---------------------+-------------+-------------------------------------------------+----------------+---------+--------------------------------+-------+------------------------------------------+
另外这些是我当前设置的索引
+------------------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| vtiger_crmentity | 0 | PRIMARY | 1 | crmid | A | 755968 | NULL | NULL | | BTREE | |
| vtiger_crmentity | 0 | crmid | 1 | crmid | A | 755968 | NULL | NULL | | BTREE | |
| vtiger_crmentity | 1 | crmentity_IDX0 | 1 | smcreatorid | A | 15 | NULL | NULL | | BTREE | |
| vtiger_crmentity | 1 | crmentity_IDX1 | 1 | smownerid | A | 15 | NULL | NULL | | BTREE | |
| vtiger_crmentity | 1 | crmentity_IDX2 | 1 | modifiedby | A | 15 | NULL | NULL | | BTREE | |
| vtiger_crmentity | 1 | deleted | 1 | deleted | A | 15 | NULL | NULL | | BTREE | |
| vtiger_crmentity | 1 | deleted | 2 | smownerid | A | 15 | NULL | NULL | | BTREE | |
| vtiger_crmentity | 1 | smownerid | 1 | smownerid | A | 199 | NULL | NULL | | BTREE | |
| vtiger_crmentity | 1 | smownerid | 2 | deleted | A | 199 | NULL | NULL | | BTREE | |
| vtiger_crmentity | 1 | deleted_2 | 1 | deleted | A | 15 | NULL | NULL | | BTREE | |
| vtiger_crmentity | 1 | deleted_2 | 2 | smownerid | A | 15 | NULL | NULL | | BTREE | |
| vtiger_crmentity | 1 | smownerid_2 | 1 | smownerid | A | 385 | NULL | NULL | | BTREE | |
| vtiger_crmentity | 1 | smownerid_2 | 2 | deleted | A | 758 | NULL | NULL | | BTREE | |
| vtiger_crmentity | 1 | crm_ownerid_del_setype_idx | 1 | smownerid | A | 15 | NULL | NULL | | BTREE | |
| vtiger_crmentity | 1 | crm_ownerid_del_setype_idx | 2 | deleted | A | 15 | NULL | NULL | | BTREE | |
| vtiger_crmentity | 1 | crm_ownerid_del_setype_idx | 3 | setype | A | 613 | NULL | NULL | YES | BTREE | |
| vtiger_crmentity | 1 | crmentity_multi_index | 1 | crmid | A | 755968 | NULL | NULL | | BTREE | |
| vtiger_crmentity | 1 | crmentity_multi_index | 2 | smownerid | A | 755968 | NULL | NULL | | BTREE | |
| vtiger_crmentity | 1 | crmentity_multi_index | 3 | deleted | A | 755968 | NULL | NULL | | BTREE | |
+------------------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
+---------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| vtiger_crmentityrel | 1 | crmid | 1 | crmid | A | 223960 | NULL | NULL | | BTREE | |
| vtiger_crmentityrel | 1 | relcrmid | 1 | relcrmid | A | 12442 | NULL | NULL | | BTREE | |
+---------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
+--------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| vtiger_users | 0 | PRIMARY | 1 | id | A | 39 | NULL | NULL | | BTREE | |
| vtiger_users | 1 | idx_user_name | 1 | user_name | A | 39 | NULL | NULL | YES | BTREE | |
| vtiger_users | 1 | user_password | 1 | user_password | A | 39 | NULL | NULL | YES | BTREE | |
+--------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
+---------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| vtiger_groups | 0 | PRIMARY | 1 | groupid | A | 5 | NULL | NULL | | BTREE | |
| vtiger_groups | 1 | groupname | 1 | groupname | A | 5 | NULL | NULL | YES | BTREE | |
| vtiger_groups | 1 | idx_groups_123group | 1 | groupname | A | 5 | NULL | NULL | YES | BTREE | |
+---------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
+----------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| vtiger_time | 0 | PRIMARY | 1 | timeid | A | 591772 | NULL | NULL | | BTREE | |
| vtiger_time | 0 | timeid | 1 | timeid | A | 591772 | NULL | NULL | | BTREE | |
| vtiger_time | 1 | relatedto | 1 | relatedto | A | 1405 | NULL | NULL | YES | BTREE | |
| vtiger_time | 1 | date_start | 1 | date_start | A | 7129 | NULL | NULL | YES | BTREE | |
| vtiger_time | 1 | relatedto_2 | 1 | relatedto | A | 3269 | NULL | NULL | YES | BTREE | |
+----------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
+------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| vtiger_timecf | 0 | PRIMARY | 1 | timeid | A | 591324 | NULL | NULL | | BTREE | |
| vtiger_timecf | 0 | timeid | 1 | timeid | A | 591324 | NULL | NULL | | BTREE | |
| vtiger_timecf | 1 | timeid_2 | 1 | timeid | A | 591324 | NULL | NULL | | BTREE | |
+------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
添加如下索引后
ALTER TABLE vtiger_crmentity ADD INDEX TMP_deletion_smownerid_crmid (smownerid, deleted,crmid);
新解释:
+----+-------------+---------------------+-------------+--------------------------------------------------------------------------+----------------+---------+--------------------------------+-------+------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+-------------+--------------------------------------------------------------------------+----------------+---------+--------------------------------+-------+------------------------------------------+
| 1 | SIMPLE | vtiger_crmentityrel | index_merge | crmid,relcrmid | crmid,relcrmid | 4,4 | NULL | 5891 | Using union(crmid,relcrmid); Using where |
| 1 | SIMPLE | vtiger_crmentity | ref | PRIMARY,crmid,deleted,deleted_2,crmentity_multi_index,_deletion_crmid | deleted | 4 | const | 84424 | Using where; Using index |
| 1 | SIMPLE | vtiger_users | eq_ref | PRIMARY | PRIMARY | 4 | crm.vtiger_crmentity.smownerid | 1 | Using index |
| 1 | SIMPLE | vtiger_groups | eq_ref | PRIMARY | PRIMARY | 4 | crm.vtiger_crmentity.smownerid | 1 | Using index |
| 1 | SIMPLE | vtiger_time | eq_ref | PRIMARY,timeid | PRIMARY | 4 | crm.vtiger_crmentity.crmid | 1 | Using index |
+----+-------------+---------------------+-------------+--------------------------------------------------------------------------+----------------+---------+--------------------------------+-------+------------------------------------------+
当我使用Bundler时，是否需要在我的Gemfile中将其列为依赖项？毕竟，我的代码中有些地方需要它。例如，当我进行Bundler设置时:require"bundler/setup" 最佳答案 没有。您可以尝试，但首先您必须用鞋带将自己抬离地面。 关于ruby-我需要将Bundler本身添加到Gemfile中吗？，我们在StackOverflow上找到一个类似的问题： https://stackoverflow.com/questions/4758609/
我注意到像bundler这样的项目在每个specfile中执行requirespec_helper我还注意到rspec使用选项--require，它允许您在引导rspec时要求一个文件。您还可以将其添加到.rspec文件中，因此只要您运行不带参数的rspec就会添加它。使用上述方法有什么缺点可以解释为什么像bundler这样的项目选择在每个规范文件中都需要spec_helper吗？ 最佳答案 我不在Bundler上工作，所以我不能直接谈论他们的做法。并非所有项目都checkin.rspec文件。原因是这个文件，通常按照当前的惯例，只
我实际上是在尝试使用RVM在我的OSX10.7.5上更新ruby，并在输入以下命令后:rvminstallruby我得到了以下回复:Searchingforbinaryrubies,thismighttakesometime.Checkingrequirementsforosx.Installingrequirementsforosx.Updatingsystem.......Errorrunning'requirements_osx_brew_update_systemruby-2.0.0-p247',pleaseread/Users/username/.rvm/log/138121
我正在阅读SandiMetz的POODR，并且遇到了一个我不太了解的编码原则。这是代码:classBicycleattr_reader:size,:chain,:tire_sizedefinitialize(args={})@size=args[:size]||1@chain=args[:chain]||2@tire_size=args[:tire_size]||3post_initialize(args)endendclassMountainBike此代码将为其各自的属性输出1,2,3,4,5。我不明白的是查找方法。当一辆山地自行车被实例化时，因为它没有自己的initialize方法
这个问题在这里已经有了答案:关闭10年前。PossibleDuplicate:Rubysyntaxquestion:Rational(a,b)andRational.new!(a,b)我正在阅读ruby镐书，我对创建有理数的语法感到困惑。Rational(3,4)*Rational(1,2)产生=>3/8为什么Rational不需要new方法(我还注意到例如我可以在没有new方法的情况下创建字符串)？
文章目录一、概述简介原理模块二、配置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
Region是HBase数据管理的基本单位,region有一点像关系型数据的分区。region中存储这用户的真实数据，而为了管理这些数据，HBase使用了RegionSever来管理region。Region的结构hbaseregion的大小设置默认情况下，每个Table起初只有一个Region，随着数据的不断写入，Region会自动进行拆分。刚拆分时，两个子Region都位于当前的RegionServer，但处于负载均衡的考虑，HMaster有可能会将某个Region转移给其他的RegionServer。RegionSplit时机：当1个region中的某个Store下所有StoreFile
我发现自己需要这个。假设cart是一个包含用户列表的模型。defindex_of_itemcart.users.each_with_indexdo|u,i|ifu==current_userreturniendend获取此类关联索引的更简单方法是什么？ 最佳答案 indexArray上的方法与您的index_of_item方法相同，例如cart.users.index(current_user)返回数组中第一个对象的索引==给obj。如果未找到匹配项，则返回nil。 关于ruby-on-
因此，当我遵循MichaelHartl的RubyonRails教程时，我注意到在用户表中，我们为:email属性添加了一个唯一索引，以提高find的效率方法，因此它不会逐行搜索。到目前为止，我们一直在根据情况使用find_by_email和find_by_id进行搜索。然而，我们从未为:id属性设置索引。:id是否自动索引，因为它在默认情况下是唯一的并且本质上是顺序的？或者情况并非如此，我应该为:id搜索添加索引吗？ 最佳答案 大多数数据库(包括sqlite，这是RoR中的默认数据库)会自动索引主键，对于RailsMigration
我需要用任何语言编写一个算法，根据3个因素对数组进行排序。我以度假村为例(如Hipmunk)。假设我想去度假。我想要最便宜的地方、最好的评论和最多的景点。但是，显然我找不到在所有3个中都排名第一的方法。Example(assumingthereare20importantattractions):ResortA:$150/night...98/100infavorablereviews...18of20attractionsResortB:$99/night...85/100infavorablereviews...12of20attractionsResortC:$120/night