时间戳

首页 > 解决方案 > MySQL查询减慢计算机速度

mysql - MySQL查询减慢计算机速度

问题描述

我正在对我的 MySQL 数据库进行查询。查询最多需要 5 分钟：

SELECT
  COUNT(*) as tasks,
  tasks.owner,
  ROUND(AVG(TIMESTAMPDIFF(day, tasks.created_on, tasks.last_modified)), 0) as avg_time
FROM
  tasks 
LEFT JOIN
  objects ON objects.number = tasks.relationship
WHERE
  tasks.status != "Potential"
GROUP BY
  tasks.owner
ORDER BY avg_time DESC
LIMIT 10

该tasks表有大约 60,000 行。我的 MacBook Pro的mysqld进程占用了整个 CPU，我的粉丝也出现了。我的查询是坏的还是 MySQL 有 60,000 行？objects我要加入的表有大约 15,000 行。

以下是创建语句：

CREATE TABLE `tasks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `complete` varchar(255) DEFAULT NULL,
  `completed_by` varchar(255) DEFAULT NULL,
  `owner` varchar(255) DEFAULT NULL,
  `delegate` varchar(255) DEFAULT NULL,
  `created_on` timestamp NULL DEFAULT NULL,
  `task_name` varchar(255) DEFAULT NULL,
  `last_modified` timestamp NULL DEFAULT NULL,
  `reassigned` varchar(255) DEFAULT NULL,
  `role` varchar(255) DEFAULT NULL,
  `status` varchar(255) DEFAULT NULL,
  `relationship` varchar(255) DEFAULT NULL,
  `unique_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=59444 DEFAULT CHARSET=utf8

CREATE TABLE `objects` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `number` varchar(255) DEFAULT NULL,
  `context` varchar(255) DEFAULT NULL,
  `owner` varchar(255) DEFAULT NULL,
  `route` varchar(255) DEFAULT NULL,
  `created_on` timestamp NULL DEFAULT NULL,
  `last_modified` timestamp NULL DEFAULT NULL,
  `status` varchar(255) DEFAULT NULL,
  `project` varchar(255) DEFAULT NULL,
  `relationship` varchar(255) DEFAULT NULL,
  `unique_id` varchar(255) DEFAULT NULL,
  `type` varchar(255) DEFAULT NULL,
  `description` text,
  `proposed_solution` text,
  `region_of_approval` varchar(255) DEFAULT NULL,
  `requested_by` varchar(255) DEFAULT NULL,
  `customer` varchar(255) DEFAULT NULL,
  `promotion_state` varchar(255) DEFAULT NULL,
  `priority` varchar(255) DEFAULT NULL,
  `business` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `number` (`number`)
) ENGINE=InnoDB AUTO_INCREMENT=16700 DEFAULT CHARSET=utf8

以下是一些示例数据objects：

"id","name","number","context","owner","route","created_on","last_modified","status","project","relationship","unique_id","type","description","proposed_solution","region_of_approval","requested_by","customer","promotion_state","priority","business"
1,"NEW ARTICLE","CN0345928","Library","Max","Change Released","2020-11-17 06:13:00","2020-11-17 06:13:00","Released",NULL,"CR86030","2516967988","Change Notice","",NULL,"Americas-Europe",NULL,"","Released",NULL,"EU"
2,"Set Hose to Service Only","CN0345931","Library","Mo","Set to Service Only","2020-11-17 06:37:00","2020-11-17 06:37:00","Released",NULL,"CR86171","2516984806","Change Notice","",NULL,"Americas-Europe",NULL,"","Service Only",NULL,"EU"
3,"NEW MEASURING DEVICE","CN0344110","Library","Max","Promote to Release","2020-10-19 08:24:00","2020-10-19 08:39:00","Released",NULL,"CR85808","2493544772","Change Notice","",NULL,"Europe",NULL,"","Released",NULL,"EU"

和tasks：

"id","complete","completed_by","owner","delegate","created_on","task_name","last_modified","reassigned","role","status","relationship","unique_id"
1,"No","member",NULL,"","2008-08-27 20:28:00","Test of creating a parent part","2008-08-27 20:28:00","No","Assignee","Potential",11377,"3A67054501"
2,"Yes","member",NULL,"","2008-09-02 16:02:00","create a bom","2008-09-02 16:11:00","No","Assignee","Completed",11496,"3A67490902"
3,"Yes","member",NULL,"","2008-09-02 16:12:00","create a bom","2008-09-02 16:17:00","No","Reviewer","Completed",11496,"3A67491252"

标签： mysql

解决方案

您可以将 WHERE 条件放入 LEFT JOIN。你真的需要左连接吗？你不能在这里使用 INNER JOIN 吗？

INNER JOIN objects ON objects.number = tasks.relationship AND tasks.status <> 'Potential'

60.000 或 15.000 个实体对于 MySQL/MariaDB 之类的数据库来说不算什么：)。数据库使用 Indizes 来显着改进查找，但您必须识别并创建索引。

如果您有性能问题：

  1. 尝试减少连接的行数（不要只是左连接..有时从任务开始并连接对象可能会更好。如果可能，使用内部连接）

  2. 在加入之前先使用任务条件并减少行数

  3. 从您的语句中创建一个存储过程（服务器可以更快地执行它）

  4. 在搜索的重要字段上使用 indizes

推荐阅读