Mysql

優化 3 表連接查詢

  • May 29, 2015
SELECT job.id_job, job.fk_company, job.fk_user, job.fk_job_category, 
job.fk_job_type, job.fk_place, job.job_identifier, job.external_job_id, job.title,
job.short_description, job.status, job.expires_at, job.nb_vacancies, 
job.featured_rank, job.created_at, job.updated_at, job.deleted_at, job.slug, 
COUNT(fk_job) AS `application_count` 
FROM `job` 
INNER JOIN `company` ON (job.fk_company=company.id_company) 
LEFT JOIN `job_application` ON (job.id_job=job_application.fk_job) 
WHERE job.deleted_at IS NULL  
GROUP BY job.id_job 
ORDER BY job.id_job DESC 
LIMIT 50

JOIN儘管我們在,GROUP BY和ORDER BY語句上使用的所有欄位都被索引或聲明為foreign keyor ，但這個查詢需要相當長的時間primary key。

+----+-------------+-----------------+-------+----------------------+----------------------+---------+----------------------------+------+----------------------------------------------+
| id | select_type | table           | type  | possible_keys        | key                  | key_len | ref                        | rows | Extra                                        |
+----+-------------+-----------------+-------+----------------------+----------------------+---------+----------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | company         | index | PRIMARY              | company_I_2          | 4       | NULL                       | 3244 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | job             | ref   | job_FI_1             | job_FI_1             | 4       | intjobs.company.id_company |    3 | Using where                                  |
|  1 | SIMPLE      | job_application | ref   | job_application_FI_2 | job_application_FI_2 | 4       | intjobs.job.id_job         |    1 | Using index                                  |
+----+-------------+-----------------+-------+----------------------+----------------------+---------+----------------------------+------+----------------------------------------------+
3 rows in set (0.00 sec)

SHOW CREATE TABLE job;

CREATE TABLE `job` (
 `id_job` int(11) NOT NULL AUTO_INCREMENT,
 `fk_company` int(11) NOT NULL,
 `fk_user` int(11) NOT NULL,
 `fk_job_category` int(11) NOT NULL,
 `fk_job_type` int(11) NOT NULL,
 `fk_place` int(11) DEFAULT NULL,
 `job_identifier` varchar(20) DEFAULT NULL,
 `external_job_id` varchar(50) DEFAULT NULL,
 `title` varchar(255) DEFAULT NULL,
 `short_description` text,
 `status` enum('new','active_expiration_reminded','active','inactive','expired') NOT NULL DEFAULT 'new',
 `expires_at` datetime DEFAULT NULL,
 `nb_vacancies` int(11) NOT NULL DEFAULT '1',
 `featured_rank` int(11) NOT NULL DEFAULT '0',
 `created_at` datetime DEFAULT NULL,
 `updated_at` datetime DEFAULT NULL,
 `deleted_at` datetime DEFAULT NULL,
 `slug` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id_job`),
 UNIQUE KEY `job_U_1` (`job_identifier`),
 UNIQUE KEY `job_slug` (`slug`),
 KEY `job_FI_1` (`fk_company`),
 KEY `job_FI_2` (`fk_user`),
 KEY `job_FI_3` (`fk_job_category`),
 KEY `job_FI_4` (`fk_job_type`),
 KEY `job_FI_6` (`fk_place`),
 KEY `job_FI_5` (`fk_place`),
 CONSTRAINT `job_FK_1` FOREIGN KEY (`fk_company`) REFERENCES `company` (`id_company`),
 CONSTRAINT `job_FK_2` FOREIGN KEY (`fk_user`) REFERENCES `user` (`id_user`),
 CONSTRAINT `job_FK_3` FOREIGN KEY (`fk_job_category`) REFERENCES `job_category` (`id_job_category`),
 CONSTRAINT `job_FK_4` FOREIGN KEY (`fk_job_type`) REFERENCES `job_type` (`id_job_type`),
 CONSTRAINT `job_FK_5` FOREIGN KEY (`fk_place`) REFERENCES `place` (`id_place`)
) ENGINE=InnoDB AUTO_INCREMENT=27630 DEFAULT CHARSET=utf8 |

SHOW CREATE TABLE company;

   CREATE TABLE `company` (
     `id_company` int(11) NOT NULL AUTO_INCREMENT,
     `fk_place` int(11) DEFAULT NULL,
     `fk_industry` int(11) DEFAULT NULL,
     `fk_modified_by_user` int(11) DEFAULT NULL,
     `name` varchar(255) NOT NULL,
     `featured_rank` int(11) NOT NULL DEFAULT '0',
     `created_at` datetime DEFAULT NULL,
     `updated_at` datetime DEFAULT NULL,
     `deleted_at` datetime DEFAULT NULL,
     `slug` varchar(255) DEFAULT NULL,
     PRIMARY KEY (`id_company`),
     UNIQUE KEY `company_slug` (`slug`),
     KEY `company_I_1` (`name`),

SHOW CREATE TABLE job_application;

CREATE TABLE `job_application` (
 `id_job_application` int(11) NOT NULL AUTO_INCREMENT,
 `fk_user` int(11) NOT NULL,
 `fk_job` int(11) NOT NULL,
 `status` enum('new','application_sent','approval_pending','in_call','invalid','jobseeker_notified','improvement_pending','read_pending','application_read','read_pending_reminded','manual_interaction_pending') NOT NULL DEFAULT 'new',
 `created_at` datetime DEFAULT NULL,
 `updated_at` datetime DEFAULT NULL,
 `deleted_at` datetime DEFAULT NULL,
 PRIMARY KEY (`id_job_application`),
 KEY `job_application_FI_1` (`fk_user`),
 KEY `job_application_FI_2` (`fk_job`),
 CONSTRAINT `job_application_FK_1` FOREIGN KEY (`fk_user`) REFERENCES `user` (`id_user`),
 CONSTRAINT `job_application_FK_2` FOREIGN KEY (`fk_job`) REFERENCES `job` (`id_job`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf

mysql版本：

+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.6.22                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.6.22-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+

查詢可以改進的三點：

  • 刪除加入company. 加入條件是通過一個不可為空的外鍵，所以它應該始終為真。
  • 更改GROUP BY x ORDER BY x DESC為：GROUP BY x DESC。這將避免額外的排序。請注意，該語法已被棄用，因此您可能需要在未來的 mysql 升級中將其更改回來。
  • 在 上添加索引(deleted_at, id_job)。這對於此查詢至關重要。通常，標誌（真/假）列上的索引或具有很少不同值的索引通常是無用的。但是該deleted_at列是一個時間戳，因此該索引在其他查詢中也很有用。這個查詢有額外的ORDER BY / LIMIT，所以可以有效地使用2列索引。

查詢重寫：

SELECT job.id_job, job.fk_company, job.fk_user, job.fk_job_category, 
 job.fk_job_type, job.fk_place, job.job_identifier, job.external_job_id, job.title,
 job.short_description, job.status, job.expires_at, job.nb_vacancies, 
 job.featured_rank, job.created_at, job.updated_at, job.deleted_at, job.slug, 
 COUNT(fk_job) AS application_count 
FROM job
 LEFT JOIN job_application ON job.id_job = job_application.fk_job 
WHERE job.deleted_at IS NULL  
GROUP BY job.id_job DESC 
LIMIT 50 ;

您還可以使用此版本，它強制使用索引並避免GROUP BY在主查詢中（內聯子查詢中的分組，僅針對 50 個值執行）：

SELECT job.id_job, job.fk_company, job.fk_user, job.fk_job_category, 
 job.fk_job_type, job.fk_place, job.job_identifier, job.external_job_id, job.title,
 job.short_description, job.status, job.expires_at, job.nb_vacancies, 
 job.featured_rank, job.created_at, job.updated_at, job.deleted_at, job.slug, 
 ( SELECT COUNT(*)
   FROM job_application AS ja
   WHERE j.id_job = ja.fk_job  
 ) AS application_count 
FROM 
 ( SELECT id_job
   FROM job
   WHERE deleted_at IS NULL  
   ORDER BY id_job DESC 
   LIMIT 50
 ) AS j
 JOIN job ON job.id_job = j.id_job  
ORDER BY j.id_job DESC  ;

引用自：https://dba.stackexchange.com/questions/102765

相關問答

Mysql

慢查詢記錄一切

  • December 31, 2021
檢視問答
Mysql

非常慢的查詢

  • November 19, 2021
檢視問答
Mysql

慢查詢日誌不儲存慢查詢

  • September 10, 2020
檢視問答
Mysql

MySQL選擇查詢不使用必需索引

  • December 20, 2019
檢視問答
Mysql-5.6

MySql GROUP BY, ORDER BY LIMIT 查詢耗時超過 5000 秒

  • August 27, 2019
檢視問答
Mysql

MySQL 5.6 使用者索引而不是范圍類型選擇大表和簡單查詢

  • July 30, 2019
檢視問答