Ich habe ein Jobanforderungssystem in PHP mit einer MySQL-Datenbank geschrieben und habe ein Problem mit einer langsamen Abfrage.

Mein Schema (vereinfacht) lautet wie folgt:

tbl_job
job_id
job_desc Requester_Benutzer_ID

tbl_user
user_id
Nutzername

tbl_workermap
workermap_id
job_id
worker_user_id

Eine Tabelle mit den Jobs, eine Benutzertabelle für die möglichen Worker und eine Tabelle zum Zuordnen von Workern zu Jobs. Ein Job kann einen oder mehrere Arbeiter haben, ein Arbeiter kann einen oder mehrere Jobs haben.

Tbl_user enthält sowohl Benutzer, die Arbeit anfordern, als auch Benutzer, die an den Jobs arbeiten. Daher werden Benutzer-IDs unter worker_user_id in tbl_workermap und Requester_user_id in tbl_job gespeichert

Wenn ein Job protokolliert wird, wird ein Eintrag in tbl_job erstellt, jedoch nichts in tbl_workermap, bis jemand speziell einen Mitarbeiter zuweist. Das heißt, wenn ich die Jobs abfrage, mache ich das mit einem linken Join, da nicht für jeden Job Einträge in tbl_workermap vorhanden sind:

SELECT 
job.job_id,
job.job_desc,
workermap.worker_user_id,
worker.worker_name

FROM tbl_job AS job

LEFT JOIN tbl_workermap AS workermap
ON job.job_id = workermap.job_id

LEFT JOIN tbl_user AS worker
ON workermap.worker_user_id = worker.user_id

Das System ist seit einer Weile in Gebrauch und ich habe jetzt ungefähr 8000 Einträge in tbl_job und 7000 in tbl_workermap und es dauert über 4 Sekunden, um alle Ergebnisse abzurufen. Eine EXPLAIN-Abfrage zeigt den tbl_workermap-Join, der ungefähr 7000 Zeilen zurückgibt, und "Using where; Using join buffer (Block Nested Loop)".

Kann ich irgendetwas tun, um dies zu beschleunigen?

BEARBEITEN: Tabelleninformationen hinzufügen
Ich hatte die zu erklärenden Dinge vereinfacht, aber hier ist die tatsächliche Tabellenstruktur. Es gibt mehr Joins, aber tbl_workermap ist das einzige Problem:

CREATE TABLE `tbl_job` (
  `job_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `job_title` varchar(100) DEFAULT NULL,
  `job_description` text,
  `job_added_datetime` int(11) DEFAULT '0',
  `job_due_datetime` int(11) NOT NULL DEFAULT '0',
  `job_time_estimate` int(11) DEFAULT NULL,
  `job_additional_fields` text,
  `addedby_user_id` int(11) NOT NULL DEFAULT '0',
  `requester_user_id` int(11) NOT NULL DEFAULT '0',
  `worker_user_id` int(11) NOT NULL DEFAULT '0',
  `job_active` tinyint(4) NOT NULL DEFAULT '1',
  `site_id` tinyint(4) NOT NULL DEFAULT '1',
  `status_id` int(11) NOT NULL DEFAULT '1',
  `estimategroup_id` int(11) DEFAULT '1',
  `brand_id` int(11) DEFAULT '1',
  `job_isproject` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`job_id`),
  FULLTEXT KEY `job_title` (`job_title`,`job_description`,`job_additional_fields`)
) ENGINE=MyISAM AUTO_INCREMENT=8285 DEFAULT CHARSET=latin1



CREATE TABLE `tbl_user` (
  `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_shortname` varchar(30) DEFAULT NULL,
  `user_name` varchar(30) DEFAULT NULL,
  `user_password` varchar(50) DEFAULT NULL,
  `user_password_reset_uuid` varchar(50) DEFAULT NULL,
  `user_email` varchar(50) DEFAULT NULL,
  `user_description` text,
  `user_sortorder` int(11) NOT NULL DEFAULT '0',
  `user_isworker` tinyint(4) NOT NULL DEFAULT '0',
  `user_active` tinyint(4) NOT NULL DEFAULT '1',
  `site_id` tinyint(4) NOT NULL DEFAULT '0',
  `user_avatar_file_id` int(11) DEFAULT NULL,
  `user_avatar_hub_url` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=917 DEFAULT CHARSET=latin1


CREATE TABLE `tbl_workermap` (
  `workermap_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `job_id` int(11) DEFAULT NULL,
  `workermap_datetime_added` int(11) DEFAULT NULL,
  `workermap_datetime_removed` int(11) DEFAULT NULL,
  `worker_user_id` int(11) DEFAULT NULL,
  `addedby_user_id` int(11) DEFAULT NULL,
  `removedby_user_id` int(11) DEFAULT NULL,
  `site_id` int(11) DEFAULT NULL,
  `workermap_isassigned` int(11) DEFAULT NULL,
  `workermap_active` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`workermap_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7145 DEFAULT CHARSET=latin1

INDEX ANZEIGEN

+---------+---+-----------+---+-----------------------+------+------+------+------+-----+----------+--+--+
| tbl_job | 0 |  PRIMARY  | 1 |        job_id         |  A   | 8283 | NULL | NULL |     |  BTREE   |  |  |
+---------+---+-----------+---+-----------------------+------+------+------+------+-----+----------+--+--+
| tbl_job | 1 | job_title | 1 | job_title             | NULL |    1 | NULL | NULL | YES | FULLTEXT |  |  |
| tbl_job | 1 | job_title | 2 | job_description       | NULL |    1 | NULL | NULL | YES | FULLTEXT |  |  |
| tbl_job | 1 | job_title | 3 | job_additional_fields | NULL |    1 | NULL | NULL | YES | FULLTEXT |  |  |
+---------+---+-----------+---+-----------------------+------+------+------+------+-----+----------+--+--+

+----------+---+---------+---+---------+---+-----+------+------+--+-------+--+--+
| tbl_user | 0 | PRIMARY | 1 | user_id | A | 910 | NULL | NULL |  | BTREE |  |  |
+----------+---+---------+---+---------+---+-----+------+------+--+-------+--+--+

+---------------+---+---------+---+--------------+---+------+------+------+--+-------+--+--+
| tbl_workermap | 0 | PRIMARY | 1 | workermap_id | A | 7184 | NULL | NULL |  | BTREE |  |  |
+---------------+---+---------+---+--------------+---+------+------+------+--+-------+--+--+

EXPLAIN-Abfrage

+---+--------+----------------+--------+---------+---------+------+-------------------------------+------+----------------------------------------------------+
| 1 | SIMPLE |      job       |  ALL   |  NULL   |  NULL   | NULL |             NULL              | 8283 |    Using where; Using temporary; Using filesort    |
+---+--------+----------------+--------+---------+---------+------+-------------------------------+------+----------------------------------------------------+
| 1 | SIMPLE | estimategroup  | eq_ref | PRIMARY | PRIMARY | 4    | jobq.job.estimategroup_id     |    1 | Using where                                        |
| 1 | SIMPLE | brand          | eq_ref | PRIMARY | PRIMARY | 4    | jobq.job.brand_id             |    1 | Using index condition                              |
| 1 | SIMPLE | site           | eq_ref | PRIMARY | PRIMARY | 4    | jobq.job.site_id              |    1 | Using where                                        |
| 1 | SIMPLE | addedby        | eq_ref | PRIMARY | PRIMARY | 4    | jobq.job.addedby_user_id      |    1 | Using index condition                              |
| 1 | SIMPLE | requester      | eq_ref | PRIMARY | PRIMARY | 4    | jobq.job.requester_user_id    |    1 | Using index condition                              |
| 1 | SIMPLE | worker         | eq_ref | PRIMARY | PRIMARY | 4    | jobq.job.worker_user_id       |    1 | Using index condition                              |
| 1 | SIMPLE | status         | ALL    | PRIMARY | NULL    | NULL | NULL                          |    6 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | workermap      | ALL    | NULL    | NULL    | NULL | NULL                          | 7184 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | user_workermap | eq_ref | PRIMARY | PRIMARY | 4    | jobq.workermap.worker_user_id |    1 | Using where                                        |
| 1 | SIMPLE | categorymap    | ALL    | NULL    | NULL    | NULL | NULL                          |    1 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | category       | eq_ref | PRIMARY | PRIMARY | 4    | jobq.categorymap.category_id  |    1 | Using where                                        |
+---+--------+----------------+--------+---------+---------+------+-------------------------------+------+----------------------------------------------------+

2
Adrian 17 Jän. 2019 im 20:03

3 Antworten

Beste Antwort

18. Januar 2019 um 13:43 Im Moment benötigen Sie zwei Indizes, um die Grundregel von BEIDEN linken und rechten Objekten eines JOIN abzudecken = benötigen einen Index. 1. ALTER TABLE tbl_workermap ADD INDEX idx_t_w_map_job_id (job_id) 2. ALTER TABLE tbl_workermap ADD INDEX idx_t_w_map_wrk_user_id (worker_user_id) nach der Erstellung führen Sie EXPLAIN ..... aus, um den neuen Ausführungsplan anzuzeigen.

1
Wilson Hauck 21 Jän. 2019 im 17:21

Wenn Sie dies noch nicht getan haben, erstellen Sie einen Clustered-Index, indem Sie einen Primärschlüssel festlegen (unter der Annahme des Tabellen sind richtig normalisiert). (Wenn Sie dies nicht getan haben, möchten Sie wahrscheinlich auch Fremdschlüsseleinschränkungen einrichten.)

Wenn diese Frage Microsoft SQL Server betrifft, würde ich empfehlen, eine gespeicherte Prozedur zu erstellen, insbesondere wenn es sich um eine Abfrage handelt, die häufig als regulärer Prozess ausgeführt wird. Wie in dieser Antwort angegeben, liegt der Hauptleistungsvorteil für einfache Abfragen wie diese jedoch im Tabellendesign und in den Indizes.

0
Paul 18 Jän. 2019 im 16:10

"Ein Job kann einen oder mehrere Arbeiter haben." Was ist mit umgekehrt (ein Arbeiter kann mehrere Jobs haben)? Wenn nicht, haben Sie nur 1: viele, und Sie sollten es nicht mit dieser zusätzlichen Tabelle implementieren.

Vorausgesetzt, es gibt wirklich viele: viele, gibt es hier finden Sie einige Tipps zur Optimierung dieser Tabelle.

Verwenden Sie LEFT nur, wenn Sie erwarten, dass in der "richtigen" Tabelle die gewünschte Zeile fehlt.

Stiltipp: Entfernen Sie tbl_, user_ (außer user_id) usw. Das heißt, Präfixe auf Namen sind unübersichtlich und redundant mit dem Kontext. Seien Sie konsistent in Bezug auf "Benutzer" und "Arbeiter".

Lassen Sie die Tabelle many: many mit beiden Zielen benennen (z. B. worker_job). Jetzt sehe ich jedoch, dass es mehr als eine einfache Mapping-Tabelle für viele: viele ist. Es ist eher eine Tabelle zum Zuweisen und Verfolgen, wer an was im Laufe der Zeit arbeitet.

Wenn Sie beide benötigen, um zu erfahren, wer an was gearbeitet hat, und den aktuellen Status, wer an was arbeitet, sollten Sie zwei Tabellen in Betracht ziehen. Die Geschichte wächst weiter; Der 'Strom' ändert sich ständig.

Verwenden Sie geeignete Datentypen wie DATE und DATETIME.

Verwenden Sie InnoDB anstelle von MyISAM.

0
Rick James 18 Jän. 2019 im 20:29