MySQL上的极慢选择查询 [英] Extremely Slow Select Query on MySQL
本文介绍了MySQL上的极慢选择查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我试图理解为什么选择查询在笔记本电脑上运行极快而在服务器上运行极慢的原因.该查询在笔记本电脑上运行需要1.388秒，而在服务器上需要49.670秒.这两种模式与我从笔记本电脑中导出方案并将其导入服务器中的方式相同.两者都在WAMP 2.0上运行MySQL 5.1.36.
I’m trying to understand why a select query runs extremely fast on my laptop while extremely slow on the server. The query takes 1.388 seconds to run on the laptop while 49.670 seconds on the server. Both schemas are identical as I have exported the scheme from the laptop and imported it into the server. Both are running MySQL 5.1.36 on WAMP 2.0.
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
扩展扩展-服务器
+----+--------------------+-----------------------------+--------+----------------------------------------------------------------+-------------+---------+------------------------------------------+------+----------+---------------------------------+
| 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
Operating System: Microsoft Windows 7 Professional, Processor: Intel® Core™ i7-4600M Processor (4M Cache, up to 3.60 GHz), Memory: 8GB
服务器规格
操作系统:Microsoft Windows 2008 Standard SP2，处理器:Intel®Xeon®处理器X5570(8M高速缓存，2.93 GHz，6.40 GT/s英特尔®QPI)，内存:4GB
Operating System: Microsoft Windows 2008 Standard SP2, Processor: Intel® Xeon® Processor X5570 (8M Cache, 2.93 GHz, 6.40 GT/s Intel® QPI), Memory: 4GB
问题排查
1..通过优化，将两个数据库中所有表的引擎从InnoDB更改为MyISAM.在服务器上运行耗时89.435秒，在笔记本电脑上耗时57.252秒.与使用InnoDB引擎的1.388秒查询时间相比，笔记本电脑仍然更快，更慢.
1. Changed the engines for all tables in both databases from InnoDB to MyISAM with optimization. It took 89.435 seconds to run on the server and 57.252 seconds on the laptop. Laptop is still faster yet, extremely slow when compared to 1.388 seconds query time using InnoDB engine.
推荐答案
笔记本电脑和服务器之间的数据(不是架构)有所不同吗?
The data (not the schema) differs between laptop and server?
说明显示section表选择了服务器上的所有行，而不是笔记本电脑上的所有行.
The explain shows that the section table selects all rows on server, instead of just one like on the laptop.
此外，它在服务器中显示Using temporary; Using filesort:这可能是问题的根源.
Also, it shows Using temporary; Using filesort in the server: that might be the source of the problem.
这篇关于MySQL上的极慢选择查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
