我试图理解为什么选择查询在我的笔记本电脑上运行速度极快，而在服务器上运行速度极慢。该查询在笔记本电脑上运行 1.388 秒，在服务器上运行 49.670 秒。这两个模式是相同的，因为我已经从笔记本电脑导出了该模式并将其导入到服务器中。两者都在 WAMP 2.0 上运行 MySQL 5.1.36。
SQL 转储
查询
SELECT form.id AS 'Form ID',
DATE_FORMAT(form.created_on, '%d %b %Y') AS 'Created On - Date',
DATE_FORMAT(form.created_on, '%h:%i %p') AS 'Created On - Time',
department.name AS 'Department',
section.name AS 'Section',
reporting_to_1.id AS 'Reporting To 1 - System ID',
reporting_to_1.real_name AS 'Reporting To 1 - Name',
reporting_to_1_department.name AS 'Reporting To 1 - Department',
reporting_to_1_section.name AS 'Reporting To 1 - Section',
CONVERT(IFNULL(reporting_to_2.id, '') USING utf8) AS 'Reporting To 2 - System ID',
IFNULL(reporting_to_2.real_name, '') AS 'Reporting To 2 - Name',
IFNULL(reporting_to_2_department.name, '') AS 'Reporting To 2 - Department',
IFNULL(reporting_to_2_section.name, '') AS 'Reporting To 2 - Section',
form_type.type AS 'Form Type',
CONVERT(IF(form.customer_number = 0, '-', form.customer_number) USING utf8) AS 'Customer Number',
form.customer_name AS 'Customer Name',
form.customer_contract AS 'Customer Contract No.',
DATE_FORMAT(form.action_date, '%d %b %Y') AS 'Action - On Date',
CONCAT('http://cns', attachment_1.path, '/', attachment_1.filename_generated) AS 'Attachment - 1',
CONCAT('http://cns', attachment_2.path, '/', attachment_2.filename_generated) AS 'Attachment - 2',
agent.name AS 'Agent - Name',
agent.tag AS 'Agent - Tag',
agent.type AS 'Agent - Type',
CONVERT(IFNULL(agent_teamleader.real_name, '') USING utf8) AS 'Agent - Team Leader - Name',
creator.id AS `creator id`,
creator.real_name AS `creator full name`,
CONVERT(IFNULL(authorizing_teamleader_user.id, '') USING utf8) AS `processed by - team leader - system id`,
IFNULL(authorizing_teamleader_user.real_name, '') AS `processed by - team leader - name`,
CONVERT(IFNULL(authorizing_teamleader_user.employee_id, '') USING utf8) AS `processed by - team leader - employee id`,
CONVERT(IFNULL(DATE_FORMAT(authorizing_teamleader.action_date, '%d %b %Y'), '') USING utf8) AS `processed on - team leader - date`,
CONVERT(IFNULL(DATE_FORMAT(authorizing_teamleader.action_date, '%h:%i %p'), '') USING utf8) AS `processed on - team leader - time`,
CONVERT(IFNULL(authorizing_manager_user.id, '') USING utf8) AS `processed by - manager - system id`,
IFNULL(authorizing_manager_user.real_name, '') AS `processed by - manager - name`,
CONVERT(IFNULL(authorizing_manager_user.employee_id, '') USING utf8) AS `processed by - manager - employee id`,
CONVERT(IFNULL(DATE_FORMAT(authorizing_manager.action_date, '%d %b %Y'), '') USING utf8) AS `processed on - manager - date`,
CONVERT(IFNULL(DATE_FORMAT(authorizing_manager.action_date, '%h:%i %p'), '') USING utf8) AS `processed on - manager - time`,
CONVERT(IFNULL(authorizing_director_user.id, '') USING utf8) AS `processed by - director - system id`,
IFNULL(authorizing_director_user.real_name, '') AS `processed by - director - name`,
CONVERT(IFNULL(authorizing_director_user.employee_id, '') USING utf8) AS `processed by - director - employee id`,
CONVERT(IFNULL(DATE_FORMAT(authorizing_director.action_date, '%d %b %Y'), '') USING utf8) AS `processed on - director - date`,
CONVERT(IFNULL(DATE_FORMAT(authorizing_director.action_date, '%h:%i %p'), '') USING utf8) AS `processed on - director - time`,
status.name AS `status`,
CONVERT(IF(status.name = 'Pending', '', user_status_by.id) USING utf8) AS `status by - system id`,
IFNULL(user_status_by.real_name, '') AS `status by - name`,
CONVERT(IFNULL(user_status_by.employee_id, '') USING utf8) AS `status by - employee id`,
IFNULL(user_status_by_role.name, '') AS `status by - position`,
CONVERT(IFNULL(DATE_FORMAT(form.status_on, '%d %b %Y'), '') USING utf8) AS `status on - date`,
CONVERT(IFNULL(DATE_FORMAT(form.status_on, '%h:%i %p'), '') USING utf8) AS `status on - time`,
CONCAT('http://cns/pdf/', form.pdf) AS `pdf`
FROM forms AS form
JOIN (sections AS section,
departments AS department)
ON form.section_id = section.id
AND section.department_id = department.id
JOIN (users AS reporting_to_1,
sections AS reporting_to_1_section,
departments AS reporting_to_1_department)
ON reporting_to_1.id = form.reporting_to_1
AND reporting_to_1.section_id = reporting_to_1_section.id
AND reporting_to_1_section.department_id = reporting_to_1_department.id
LEFT JOIN (users AS reporting_to_2, sections AS reporting_to_2_section,
departments AS reporting_to_2_department)
ON reporting_to_2.id = form.reporting_to_2
AND reporting_to_2.section_id = reporting_to_2_section.id
AND reporting_to_2_section.department_id = reporting_to_2_department.id
JOIN form_type
ON form.type = form_type.id
LEFT JOIN attachments AS attachment_1
ON form.id = attachment_1.form
AND attachment_1.id = ( SELECT min(id)
FROM attachments
WHERE form = form.id)
LEFT JOIN attachments AS attachment_2
ON form.id = attachment_2.form
AND attachment_2.id = ( SELECT max(id)
FROM attachments
WHERE form = form.id)
LEFT JOIN (agents AS agent,
users AS agent_teamleader,
branches AS branch)
ON form.id = agent.form_id
AND agent_teamleader.id = agent.teamleader_id
AND branch.id = agent.branch_id
JOIN users AS creator
ON form.user_id = creator.id
LEFT JOIN (authorizers AS authorizing_teamleader,
users AS authorizing_teamleader_user)
ON authorizing_teamleader.form_id = form.id
AND authorizing_teamleader_user.id = authorizing_teamleader.`from`
AND authorizing_teamleader_user.role = 't'
LEFT JOIN (authorizers AS authorizing_manager,
users AS authorizing_manager_user)
ON authorizing_manager.form_id = form.id
AND authorizing_manager_user.id = authorizing_manager.`from`
AND authorizing_manager_user.role = 'm'
LEFT JOIN (authorizers AS authorizing_director,
users AS authorizing_director_user)
ON authorizing_director.form_id = form.id
AND authorizing_director_user.id = authorizing_director.`from`
AND authorizing_director_user.role = 'd'
JOIN status
ON form.status = status.id
LEFT JOIN (users AS user_status_by,
roles AS user_status_by_role)
ON user_status_by.id = form.status_by_user_id
AND user_status_by_role.id = user_status_by.role
GROUP BY form.id
ORDER BY form.id DESC
LIMIT 0, 100
EXPLAIN EXTENDED - 服务器
+----+--------------------+-----------------------------+--------+----------------------------------------------------------------+-------------+---------+------------------------------------------+------+----------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-----------------------------+--------+----------------------------------------------------------------+-------------+---------+------------------------------------------+------+----------+---------------------------------+
| 1 | PRIMARY | section | ALL | PRIMARY,IDX_DEPARTMENT | | | | 18 | 100 | Using temporary; Using filesort |
| 1 | PRIMARY | department | eq_ref | PRIMARY | PRIMARY | 4 | cns.section.department_id | 1 | 100 | |
| 1 | PRIMARY | form | ref | IDX_USER_ID,IDX_REPORTING_TO_1,IDX_SECTION,IDX_TYPE,IDX_STATUS | IDX_SECTION | 4 | cns.section.id | 528 | 100 | |
| 1 | PRIMARY | status | eq_ref | PRIMARY | PRIMARY | 3 | cns.form.status | 1 | 100 | |
| 1 | PRIMARY | form_type | eq_ref | PRIMARY | PRIMARY | 4 | cns.form.type | 1 | 100 | |
| 1 | PRIMARY | authorizing_teamleader | ref | IDX_FORM_ID,IDX_FROM_USER_ID | IDX_FORM_ID | 4 | cns.form.id | 1 | 100 | |
| 1 | PRIMARY | authorizing_teamleader_user | eq_ref | PRIMARY,IDX_ROLE | PRIMARY | 4 | cns.authorizing_teamleader.from | 1 | 100 | |
| 1 | PRIMARY | authorizing_manager | ref | IDX_FORM_ID,IDX_FROM_USER_ID | IDX_FORM_ID | 4 | cns.form.id | 1 | 100 | |
| 1 | PRIMARY | authorizing_manager_user | eq_ref | PRIMARY,IDX_ROLE | PRIMARY | 4 | cns.authorizing_manager.from | 1 | 100 | |
| 1 | PRIMARY | authorizing_director | ref | IDX_FORM_ID,IDX_FROM_USER_ID | IDX_FORM_ID | 4 | cns.form.id | 1 | 100 | |
| 1 | PRIMARY | authorizing_director_user | eq_ref | PRIMARY,IDX_ROLE | PRIMARY | 4 | cns.authorizing_director.from | 1 | 100 | |
| 1 | PRIMARY | attachment_1 | eq_ref | PRIMARY,IDX_FORM_ID | PRIMARY | 4 | func | 1 | 100 | |
| 1 | PRIMARY | attachment_2 | eq_ref | PRIMARY,IDX_FORM_ID | PRIMARY | 4 | func | 1 | 100 | |
| 1 | PRIMARY | agent | ref | IDX_FORM_ID,IDX_BRANCH_ID,IDX_TEAMLEADER_ID | IDX_FORM_ID | 4 | cns.form.id | 1 | 100 | |
| 1 | PRIMARY | agent_teamleader | eq_ref | PRIMARY | PRIMARY | 4 | cns.agent.teamleader_id | 1 | 100 | |
| 1 | PRIMARY | branch | eq_ref | PRIMARY | PRIMARY | 4 | cns.agent.branch_id | 1 | 100 | Using index |
| 1 | PRIMARY | reporting_to_1 | eq_ref | PRIMARY,IDX_SECTION | PRIMARY | 4 | cns.form.reporting_to_1 | 1 | 100 | |
| 1 | PRIMARY | reporting_to_2 | eq_ref | PRIMARY,IDX_SECTION | PRIMARY | 4 | cns.form.reporting_to_2 | 1 | 100 | |
| 1 | PRIMARY | reporting_to_2_section | eq_ref | PRIMARY,IDX_DEPARTMENT | PRIMARY | 4 | cns.reporting_to_2.section_id | 1 | 100 | |
| 1 | PRIMARY | reporting_to_2_department | eq_ref | PRIMARY | PRIMARY | 4 | cns.reporting_to_2_section.department_id | 1 | 100 | |
| 1 | PRIMARY | creator | eq_ref | PRIMARY | PRIMARY | 4 | cns.form.user_id | 1 | 100 | |
| 1 | PRIMARY | reporting_to_1_section | eq_ref | PRIMARY,IDX_DEPARTMENT | PRIMARY | 4 | cns.reporting_to_1.section_id | 1 | 100 | |
| 1 | PRIMARY | reporting_to_1_department | eq_ref | PRIMARY | PRIMARY | 4 | cns.reporting_to_1_section.department_id | 1 | 100 | |
| 1 | PRIMARY | user_status_by | eq_ref | PRIMARY,IDX_ROLE | PRIMARY | 4 | cns.form.status_by_user_id | 1 | 100 | |
| 1 | PRIMARY | user_status_by_role | eq_ref | PRIMARY | PRIMARY | 3 | cns.user_status_by.role | 1 | 100 | |
| 3 | DEPENDENT SUBQUERY | attachments | ref | IDX_FORM_ID | IDX_FORM_ID | 4 | cns.form.id | 1 | 100 | Using index |
| 2 | DEPENDENT SUBQUERY | attachments | ref | IDX_FORM_ID | IDX_FORM_ID | 4 | cns.form.id | 1 | 100 | Using index |
+----+--------------------+-----------------------------+--------+----------------------------------------------------------------+-------------+---------+------------------------------------------+------+----------+---------------------------------+
解释扩展 - 笔记本电脑
+----+--------------------+-----------------------------+--------+----------------------------------------------------------------+---------------+---------+------------------------------------------+------+----------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-----------------------------+--------+----------------------------------------------------------------+---------------+---------+------------------------------------------+------+----------+----------------------------------------------+
| 1 | PRIMARY | form_type | index | PRIMARY | IDX_FORM_TYPE | 137 | | 2 | 100 | Using index; Using temporary; Using filesort |
| 1 | PRIMARY | form | ref | IDX_USER_ID,IDX_REPORTING_TO_1,IDX_SECTION,IDX_TYPE,IDX_STATUS | IDX_TYPE | 4 | cns.form_type.id | 1443 | 100 | |
| 1 | PRIMARY | status | eq_ref | PRIMARY | PRIMARY | 3 | cns.form.status | 1 | 100 | |
| 1 | PRIMARY | section | eq_ref | PRIMARY,IDX_DEPARTMENT | PRIMARY | 4 | cns.form.section_id | 1 | 100 | |
| 1 | PRIMARY | department | eq_ref | PRIMARY | PRIMARY | 4 | cns.section.department_id | 1 | 100 | |
| 1 | PRIMARY | authorizing_teamleader | ref | IDX_FORM_ID,IDX_FROM_USER_ID | IDX_FORM_ID | 4 | cns.form.id | 1 | 100 | |
| 1 | PRIMARY | authorizing_teamleader_user | eq_ref | PRIMARY,IDX_ROLE | PRIMARY | 4 | cns.authorizing_teamleader.from | 1 | 100 | |
| 1 | PRIMARY | authorizing_manager | ref | IDX_FORM_ID,IDX_FROM_USER_ID | IDX_FORM_ID | 4 | cns.form.id | 1 | 100 | |
| 1 | PRIMARY | authorizing_manager_user | eq_ref | PRIMARY,IDX_ROLE | PRIMARY | 4 | cns.authorizing_manager.from | 1 | 100 | |
| 1 | PRIMARY | authorizing_director | ref | IDX_FORM_ID,IDX_FROM_USER_ID | IDX_FORM_ID | 4 | cns.form.id | 1 | 100 | |
| 1 | PRIMARY | authorizing_director_user | eq_ref | PRIMARY,IDX_ROLE | PRIMARY | 4 | cns.authorizing_director.from | 1 | 100 | |
| 1 | PRIMARY | attachment_1 | eq_ref | PRIMARY,IDX_FORM_ID | PRIMARY | 4 | func | 1 | 100 | |
| 1 | PRIMARY | attachment_2 | eq_ref | PRIMARY,IDX_FORM_ID | PRIMARY | 4 | func | 1 | 100 | |
| 1 | PRIMARY | agent | ref | IDX_FORM_ID,IDX_BRANCH_ID,IDX_TEAMLEADER_ID | IDX_FORM_ID | 4 | cns.form.id | 1 | 100 | |
| 1 | PRIMARY | agent_teamleader | eq_ref | PRIMARY | PRIMARY | 4 | cns.agent.teamleader_id | 1 | 100 | |
| 1 | PRIMARY | branch | eq_ref | PRIMARY | PRIMARY | 4 | cns.agent.branch_id | 1 | 100 | Using index |
| 1 | PRIMARY | reporting_to_1 | eq_ref | PRIMARY,IDX_SECTION | PRIMARY | 4 | cns.form.reporting_to_1 | 1 | 100 | |
| 1 | PRIMARY | reporting_to_2 | eq_ref | PRIMARY,IDX_SECTION | PRIMARY | 4 | cns.form.reporting_to_2 | 1 | 100 | |
| 1 | PRIMARY | reporting_to_2_section | eq_ref | PRIMARY,IDX_DEPARTMENT | PRIMARY | 4 | cns.reporting_to_2.section_id | 1 | 100 | |
| 1 | PRIMARY | reporting_to_2_department | eq_ref | PRIMARY | PRIMARY | 4 | cns.reporting_to_2_section.department_id | 1 | 100 | |
| 1 | PRIMARY | creator | eq_ref | PRIMARY | PRIMARY | 4 | cns.form.user_id | 1 | 100 | |
| 1 | PRIMARY | reporting_to_1_section | eq_ref | PRIMARY,IDX_DEPARTMENT | PRIMARY | 4 | cns.reporting_to_1.section_id | 1 | 100 | |
| 1 | PRIMARY | reporting_to_1_department | eq_ref | PRIMARY | PRIMARY | 4 | cns.reporting_to_1_section.department_id | 1 | 100 | |
| 1 | PRIMARY | user_status_by | eq_ref | PRIMARY,IDX_ROLE | PRIMARY | 4 | cns.form.status_by_user_id | 1 | 100 | |
| 1 | PRIMARY | user_status_by_role | eq_ref | PRIMARY | PRIMARY | 3 | cns.user_status_by.role | 1 | 100 | |
| 3 | DEPENDENT SUBQUERY | attachments | ref | IDX_FORM_ID | IDX_FORM_ID | 4 | cns.form.id | 1 | 100 | Using index |
| 2 | DEPENDENT SUBQUERY | attachments | ref | IDX_FORM_ID | IDX_FORM_ID | 4 | cns.form.id | 1 | 100 | Using index |
+----+--------------------+-----------------------------+--------+----------------------------------------------------------------+---------------+---------+------------------------------------------+------+----------+----------------------------------------------+
型号
笔记本电脑规范
操作系统:Microsoft Windows 7 Professional，处理器:Intel® Core™ i7-4600M 处理器(4M 缓存，高达 3.60 GHz)，内存:8GB
服务器规范
操作系统:Microsoft Windows 2008 Standard SP2，处理器:Intel® Xeon® Processor X5570(8M 缓存，2.93 GHz，6.40 GT/s Intel® QPI)，内存:4GB
疑难解答
1. 通过优化将两个数据库中所有表的引擎从 InnoDB 更改为 MyISAM。在服务器上运行花费了 89.435 秒，在笔记本电脑上运行了 57.252 秒。笔记本电脑仍然更快，与使用 InnoDB 引擎的 1.388 秒查询时间相比，速度非常慢。
最佳答案
也许在将数据库从一台机器转移到另一台机器后，您的表中的索引没有得到重建(我以前遇到过这种情况)。你必须手动告诉 MySQL 重建索引。如果我没记错的话，您可以使用 OPTIMIZE 查询
优化表 your_table
不存在索引会显着降低您的查询速度，尽管您遇到的差异可能太大而无法用这个问题来解释。 就像之前的评论一样，您可以发布您的表格/查询吗？
关于mysql - MySQL 上的极慢选择查询，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/28040705/
我正在用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.
我想设置一个默认日期，例如实际日期，我该如何设置？还有如何在组合框中设置默认值顺便问一下，date_field_tag和date_field之间有什么区别？ 最佳答案 试试这个:将默认日期作为第二个参数传递。youcorrectlysetthedefaultvalueofcomboboxasshowninyourquestion. 关于ruby-on-rails-date_field_tag，如何设置默认日期？[rails上的ruby]，我们在StackOverflow上找到一个类似的问
我将我的Rails应用程序部署到OpenShift，它运行良好，但我无法在生产服务器上运行“Rails控制台”。它给了我这个错误。我该如何解决这个问题？我尝试更新rubygems，但它也给出了权限被拒绝的错误，我也无法做到。railsc错误:Warning:You'reusingRubygems1.8.24withSpring.UpgradetoatleastRubygems2.1.0andrun`gempristine--all`forbetterstartupperformance./opt/rh/ruby193/root/usr/share/rubygems/rubygems
我知道我可以指定某些字段来使用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
我正在尝试从Postgresql表(table1)中获取数据，该表由另一个相关表(property)的字段(table2)过滤。在纯SQL中，我会这样编写查询:SELECT*FROMtable1JOINtable2USING(table2_id)WHEREtable2.propertyLIKE'query%'这工作正常:scope:my_scope,->(query){includes(:table2).where("table2.property":query)}但我真正需要的是使用LIKE运算符进行过滤，而不是严格相等。然而，这是行不通的:scope:my_scope,->(que
状态:我正在构建一个应用程序，其中需要一个可供用户选择颜色的字段，该字段将包含RGB颜色代码字符串。我已经测试了一个看起来很漂亮但效果不佳的。它是“挑剔的颜色”，并托管在此存储库中:https://github.com/Astorsoft/picky-color.在这里我打开一个关于它的一些问题的问题。问题:请建议我在Rails3应用程序中使用一些颜色选择器。 最佳答案 也许页面上的列表jQueryUIDevelopment:ColorPicker为您提供开箱即用的产品。原因是jQuery现在包含在Rails3应用程序中，因此使用基
文章目录一、概述简介原理模块二、配置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中提取小时
我有一个.pfx格式的证书，我需要使用ruby提取公共(public)、私有(private)和CA证书。使用shell我可以这样做:#ExtractPublicKey(askforpassword)opensslpkcs12-infile.pfx-outfile_public.pem-clcerts-nokeys#ExtractCertificateAuthorityKey(askforpassword)opensslpkcs12-infile.pfx-outfile_ca.pem-cacerts-nokeys#ExtractPrivateKey(askforpassword)o
我完全不是程序员，正在学习使用Ruby和Rails框架进行编程。我目前正在使用Ruby1.8.7和Rails3.0.3，但我想知道我是否应该升级到Ruby1.9，因为我真的没有任何升级的“遗留”成本。缺点是什么？我是否会遇到与普通gem的兼容性问题，或者甚至其他我不太了解甚至无法预料的问题？ 最佳答案 你应该升级。不要坚持从1.8.7开始。如果您发现不支持1.9.2的gem，请避免使用它们(因为它们很可能不被维护)。如果您对gem是否兼容1.9.2有任何疑问，您可以在以下位置查看:http://www.railsplugins.or