Как ускорить запрос MySQL, содержащий левый соединение и группировку с 4 м+ рядами? ⇐ MySql

[Anonymous]

Не могли бы вы, пожалуйста, помогите мне с MySQL (Join, Group By, Indexes) < /p>
Существуют таблицы «Проекты» (362K Rows) и «Projects_emploeees» (4,27 млн ​​строк), с одной стороны. Я пытаюсь получить агрегированные данные для каждого сотрудника, и это занимает 6-7 секунд. Есть ли возможность сделать его быстрее или это лучший?

Код: Выделить всё

CREATE TABLE projects (
id INT AUTO_INCREMENT PRIMARY KEY,
client_id INT,
manager_id INT,
team_size INT,
status_code INT,
priority_level INT,
risk_level INT,
estimated_hours INT,
actual_hours INT,
remaining_hours INT,
budget_cents INT,
cost_cents INT,
progress_percent INT,
tasks_total INT,
tasks_completed INT,
bugs_found INT,
bugs_fixed INT,
meetings_held INT,
files_uploaded INT,
comments_posted INT,
reviews_requested INT,
approvals_received INT,
escalations INT,
feedback_score INT,
archived TINYINT
);

CREATE TABLE projects_employees (
id INT AUTO_INCREMENT PRIMARY KEY,
project_id INT NOT NULL,
employee_id INT NOT NULL,
department_id INT,
role_code INT,
hours_allocated INT,
hours_logged INT,
is_active TINYINT DEFAULT 1,
joined_at DATE,
left_at DATE,

INDEX idx_employee_project (employee_id, project_id),
FOREIGN KEY (project_id) REFERENCES projects(id)
);
< /code>
Запрос: < /p>
-- It takes 6-7 seconds
EXPLAIN SELECT
projects_employees.employee_id,
SUM(projects.estimated_hours) as estimated_hours,
SUM(projects.actual_hours) as actual_hours,
SUM(projects.budget_cents) as budget_cents,
SUM(projects.cost_cents) as cost_cents,
SUM(projects.tasks_total) as tasks_total,
SUM(projects.bugs_fixed) as bugs_fixed
FROM projects_employees
LEFT JOIN projects ON projects.id=projects_employees.project_id
GROUP BY projects_employees.employee_id;

n.b.: левый не требуется функционально, за исключением того, что без него это было даже медленнее.
+----+-------------+--------------------+------------+--------+----------------------+----------------------+---------+-------------------------------------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------------+------------+--------+----------------------+----------------------+---------+-------------------------------------+---------+----------+-------------+
| 1 | SIMPLE | projects_employees | NULL | index | idx_employee_project | idx_employee_project | 8 | NULL | 4259544 | 100.00 | Using index |
| 1 | SIMPLE | projects | NULL | eq_ref | PRIMARY | PRIMARY | 4 | rates.projects_employees.project_id | 1 | 100.00 | NULL |
+----+-------------+--------------------+------------+--------+----------------------+----------------------+---------+-------------------------------------+---------+----------+-------------+
< /code>
заметил, что: < /p>
-- It takes 0.62 seconds
EXPLAIN SELECT COUNT(*)
FROM projects_employees
GROUP BY projects_employees.employee_id;

-- It takes 4.11 seconds
EXPLAIN SELECT projects_employees.id, projects.estimated_hours
FROM projects_employees
LEFT JOIN projects ON projects.id=projects_employees.project_id;
< /code>
Некоторые точки: < /p>

• Ресурсы: 6 ЦП, 16 ГБ ОЗУ, 12 ГБ innodb_buffer_pool_size < /li>
  Поскольку нам нужны данные для одного года, оба таблицы уже отделены годами: Производство < /li>
  В основном запросе нет фильтров по «проектам», поэтому все строки должны быть получены. Но будет возможность настроить запрос с фильтрами «Проекты»

Подробнее здесь: https://stackoverflow.com/questions/796 ... th-4m-rows