Analysieren und optimieren Sie die Effizienz gemeinsamer MySQL-Mehrtabellenabfragen
- 小云云Original
- 2017-12-13 14:10:5414122Durchsuche
Die Optimierung von MySQL-Big-Data-Abfragen wird von vielen Webmastern nicht sorgfältig analysiert. Bei diesem Problem ist der Herausgeber kürzlich auf ein Problem mit der 100-W-Datenoptimierung gestoßen Eine entsprechende Analyse kann jedem helfen.
Empfohlene MySQL-Video-Tutorials: „MySQL-Tutorial“
1. Eine Optimierung einer einfachen korrelierten Unterabfrage.
Oft ist die Leistung von auf MySQL implementierten Unterabfragen schlecht, was wirklich traurig klingt. Besonders manchmal dauert die Schätzung bei Verwendung der IN()-Unterabfrageanweisung für Tabellen einer bestimmten Größenordnung zu lange. Meine MySQL-Kenntnisse sind nicht tiefgreifend, daher kann ich das Rätsel nur langsam lösen.
Angenommen, es gibt eine solche Abfrageanweisung:
select * from table1
where exists
(select * from table2 where id>=30000 and table1.uuid=table2.uuid);Tabelle1 ist eine Tabelle mit einhunderttausend Zeilen und Tabelle2 ist eine Tabelle mit einer Million Zeilen. Das lokale Testergebnis dauert 2,40 Sekunden.
Sie können der Erklärung entnehmen, dass es sich bei der Unterabfrage um eine verwandte Unterabfrage handelt (DEPENDENCE SUBQUERY). MySQL führt zunächst einen vollständigen Tabellenscan für die äußere Tabelle1 durch und führt dann die Unterabfrage nacheinander basierend auf der zurückgegebenen UUID aus. Wenn es sich bei der äußeren Tabelle um eine große Tabelle handelt, können wir uns vorstellen, dass die Abfrageleistung schlechter sein wird als in diesem Test.
Eine einfache Optimierungslösung besteht darin, die Unterabfrage durch die Inner-Join-Methode zu ersetzen. Die Abfrageanweisung kann wie folgt geändert werden:
select * from table1 innner join table2 using(uuid) where table2.id>=30000;
Das lokale Testergebnis dauerte 0,68 Sekunden.
Sie können erklären, dass MySQL den SIMPLE-Typ verwendet (eine andere Abfragemethode als Unterabfrage oder Union); der MySQL-Optimierer filtert zuerst Tabelle2 und erstellt dann das kartesische Produkt von Tabelle1 und Tabelle2, um die Ergebnismenge zu erhalten . Filtern Sie dann die Daten nach Bedingungen.
2. Analyse und Optimierung der Effizienz gemeinsamer Abfragen mit mehreren Tabellen
1. Verbindungstyp mit mehreren Tabellen
1. Das kartesische Produkt (Kreuzverbindung) in MySQL kann CROSS JOIN sein oder CROSS weglassen JOIN. Oder verwenden Sie „,“ wie zum Beispiel:
01.SELECT * FROM table1 CROSS JOIN table2 02.SELECT * FROM table1 JOIN table2 03.SELECT * FROM table1,table2 SELECT * FROM table1 CROSS JOIN table2 SELECT * FROM table1 JOIN table2 SELECT * FROM table1,table2
Da das zurückgegebene Ergebnis das Produkt der beiden verbundenen Datentabellen ist, wird die Verwendung im Allgemeinen nicht empfohlen, wenn WHERE-, ON- oder USING-Bedingungen vorliegen Denn wenn zu viele Datentabellenelemente vorhanden sind, ist die Geschwindigkeit sehr langsam. Verwenden Sie im Allgemeinen LEFT [OUTER] JOIN oder RIGHT [OUTER] JOIN
2. INNER JOIN INNER JOIN wird in MySQL als Equijoin bezeichnet, das heißt, Sie müssen die Equijoin-Bedingungen in CROSS in MySQL angeben INNER JOIN werden ineinander geteilt. join_table: table_reference [INNER |. CROSS] JOIN table_factor [join_condition]
3. Äußere Verknüpfungen werden in MySQL in linke äußere Verknüpfungen und rechte Verknüpfungen unterteilt, d. h. zusätzlich zur Rückgabe von Ergebnissen, die die Verknüpfungsbedingungen erfüllen Bei Ergebnissen, die die Join-Bedingungen in der linken Tabelle (Links-Join) oder rechten Tabelle (Rechts-Join) nicht erfüllen, wird entsprechend NULL verwendet.
Beispiel:
Benutzertabelle:
id | name ——— 1 | libk 2 | zyfon 3 | daodao
Benutzeraktionstabelle:
user_id | action ————— 1 | jump 1 | kick 1 | jump 2 | run 4 | swim
SQL:
01.select id, name, action from user as u 02.left join user_action a on u.id = a.user_id select id, name, action from user as u left join user_action a on u.id = a.user_idresult: id | name | action ——————————– 1 | libk | jump ① 1 | libk | kick ② 1 | libk | jump ③ 2 | zyfon | run ④ 3 | daodao | null ⑤
Analyse :
Beachten Sie, dass es in user_action einen Datensatz mit user_id=4, action=swim gibt, dieser jedoch nicht in den Ergebnissen erscheint.
Und der Benutzer mit id=3, name=daodao in der Benutzertabelle nicht einen entsprechenden Datensatz in user_action haben, aber er erscheint im Ergebnissatz
Da es sich jetzt um einen Left-Join handelt, basiert die gesamte Arbeit auf Left.
Die Ergebnisse 1, 2, 3 und 4 sind alle Datensätze Sowohl in der linken als auch in der rechten Tabelle ist 5 ein Datensatz, der nur in der linken Tabelle, aber nicht in der rechten Tabelle vorhanden ist
Arbeitsprinzip:
Einen Datensatz aus der linken Tabelle lesen und Wählen Sie alle richtigen Tabellendatensätze (n) aus, die bei der Verknüpfung übereinstimmen, um n Datensätze zu bilden (einschließlich doppelter Zeilen, z. B. Ergebnis 1 und Ergebnis 3). Wenn rechts keine Tabelle vorhanden ist, die der Ein-Bedingung entspricht, sind alle verbundenen Felder vorhanden null. Dann lesen Sie weiter.
Erweiterung:
Wir können die Regel verwenden, Null anzuzeigen, wenn in der rechten Tabelle keine Übereinstimmung vorhanden ist, um alle Datensätze zu finden, die in der linken Tabelle, aber nicht in der rechten Tabelle enthalten sind Die zur Beurteilung verwendete Spalte muss als nicht null deklariert werden.
Zum Beispiel:
sql:
01.select id, name, action from user as u 02.left join user_action a on u.id = a.user_id 03.where a.user_id is NULL select id, name, action from user as u left join user_action a on u.id = a.user_id where a.user_id is NULL
(Hinweis:
1. Wenn der Spaltenwert null ist, sollten Sie is null anstelle von =NULL verwenden
2 .Hier muss die Spalte a.user_id als NOT NULL deklariert werden.)
Das Ergebnis der obigen SQL:
id | name | action ————————– 3 | daodao | NULL ——————————————————————————–
Allgemeine Verwendung:
a. LEFT [OUTER] JOIN:
Zusätzlich zur Rückgabe von Ergebnissen, die die Join-Bedingungen erfüllen, müssen Sie auch die Datenspalten in der linken Tabelle anzeigen, die die Join-Bedingungen nicht erfüllen. Verwenden Sie entsprechend NULL für
01.SELECT column_name FROM table1 LEFT [OUTER] JOIN table2 ON table1.column=table2.column SELECT column_name FROM table1 LEFT [OUTER] JOIN table2 ON table1.column=table2.column b. RIGHT [OUTER] JOIN：
RIGHT ähnelt LEFT JOIN. Der Unterschied besteht darin, dass Sie zusätzlich zur Anzeige der Ergebnisse, die die Verbindungsbedingungen erfüllen, auch die Datenspalten in der rechten Tabelle anzeigen müssen, die die Verbindungsbedingungen nicht erfüllen . Die Verwendung von NULL entspricht
01.SELECT column_name FROM table1 RIGHT [OUTER] JOIN table2 ON table1.column=table2.column SELECT column_name FROM table1 RIGHT [OUTER] JOIN table2 ON table1.column=table2.columnTips:
1 auf a.c1 = b1. Die Verwendung von (c1)
2. INNER JOIN und (Komma) ist semantisch äquivalent
Wenn MySQL Informationen aus einer Tabelle abruft, können Sie es auffordern, den Index auszuwählen.
Diese Funktion ist nützlich, wenn EXPLAIN zeigt, dass MySQL den falschen Index aus der Liste der möglichen Indizes verwendet.
Durch die Angabe von USE INDEX (key_list) können Sie MySQL anweisen, den am besten geeigneten der möglichen Indizes zu verwenden, um Zeilen in der Tabelle zu finden.
Die optionale Second-Choice-Syntax IGNORE INDEX (key_list) kann verwendet werden, um MySQL anzuweisen, keinen bestimmten Index zu verwenden. Zum Beispiel:
01.mysql> SELECT * FROM table1 USE INDEX (key1,key2) 02.-> WHERE key1=1 AND key2=2 AND key3=3; 03.mysql> SELECT * FROM table1 IGNORE INDEX (key3) 04.-> WHERE key1=1 AND key2=2 AND key3=3; mysql> SELECT * FROM table1 USE INDEX (key1,key2) -> WHERE key1=1 AND key2=2 AND key3=3; mysql> SELECT * FROM table1 IGNORE INDEX (key3) -> WHERE key1=1 AND key2=2 AND key3=3;
2. Tabellenverbindungsbeschränkungen
Anzeigebedingungen WHERE, ON, USING hinzufügen
1.WHERE-Klausel mysql>
01.SELECT * FROM table1,table2 WHERE table1.id=table2.id; SELECT * FROM table1,table2 WHERE table1.id=table2.id;
2 . ON
mysql>
01.SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id; 02. 03.SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id 04.LEFT JOIN table3 ON table2.id=table3.id; SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id; SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id LEFT JOIN table3 ON table2.id=table3.id;
3. USING子句，如果连接的两个表连接条件的两个列具有相同的名字的话可以使用USING
例如：
SELECT FROM LEFT JOIN USING ()
连接多于两个表的情况举例：
mysql>
01.SELECT artists.Artist, cds.title, genres.genre 02. 03.FROM cds 04. 05.LEFT JOIN genres N cds.genreID = genres.genreID 06. 07.LEFT JOIN artists ON cds.artistID = artists.artistID; SELECT artists.Artist, cds.title, genres.genre FROM cds LEFT JOIN genres N cds.genreID = genres.genreID LEFT JOIN artists ON cds.artistID = artists.artistID;
或者 mysql>
01.SELECT artists.Artist, cds.title, genres.genre 02. 03.FROM cds 04. 05.LEFT JOIN genres ON cds.genreID = genres.genreID 06. 07. LEFT JOIN artists -> ON cds.artistID = artists.artistID 08. 09. WHERE (genres.genre = 'Pop'); SELECT artists.Artist, cds.title, genres.genre
FROM cds
LEFT JOIN genres ON cds.genreID = genres.genreID LEFT JOIN artists -> ON cds.artistID = artists.artistID WHERE (genres.genre = 'Pop');
--------------------------------------------
另外需要注意的地方 在MySQL中涉及到多表查询的时候，需要根据查询的情况，想好使用哪种连接方式效率更高。
1. 交叉连接(笛卡尔积)或者内连接 [INNER | CROSS] JOIN
2. 左外连接LEFT [OUTER] JOIN或者右外连接RIGHT [OUTER] JOIN 注意指定连接条件WHERE, ON，USING.
3. MySQL如何优化LEFT JOIN和RIGHT JOIN
在MySQL中，A LEFT JOIN B join_condition执行过程如下：
1)· 根据表A和A依赖的所有表设置表B。
2)· 根据LEFT JOIN条件中使用的所有表(除了B)设置表A。
3)· LEFT JOIN条件用于确定如何从表B搜索行。(换句话说，不使用WHERE子句中的任何条件）。
4)· 可以对所有标准联接进行优化，只是只有从它所依赖的所有表读取的表例外。如果出现循环依赖关系，MySQL提示出现一个错误。
5)· 进行所有标准WHERE优化。
6)· 如果A中有一行匹配WHERE子句，但B中没有一行匹配ON条件，则生成另一个B行，其中所有列设置为NULL。
7)· 如果使用LEFT JOIN找出在某些表中不存在的行，并且进行了下面的测试：WHERE部分的col_name IS NULL，其中col_name是一个声明为 NOT NULL的列，MySQL找到匹配LEFT JOIN条件的一个行后停止(为具体的关键字组合)搜索其它行。
RIGHT JOIN的执行类似LEFT JOIN，只是表的角色反过来。
联接优化器计算表应联接的顺序。LEFT JOIN和STRAIGHT_JOIN强制的表读顺序可以帮助联接优化器更快地工作，因为检查的表交换更少。请注意这说明如果执行下面类型的查询，MySQL进行全扫描b，因为LEFT JOIN强制它在d之前读取：
01.SELECT * 02.FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key) 03.WHERE b.key=d.key; SELECT * FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key) WHERE b.key=d.key;
在这种情况下修复时用a的相反顺序，b列于FROM子句中：
01.SELECT * 02.FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key) 03.WHERE b.key=d.key; SELECT * FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key) WHERE b.key=d.key;
MySQL可以进行下面的LEFT JOIN优化：如果对于产生的NULL行，WHERE条件总为假，LEFT JOIN变为普通联接。
例如，在下面的查询中如果t2.column1为NULL，WHERE 子句将为false：
01.SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;
SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;因此，可以安全地将查询转换为普通联接：
01.SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;
SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;这样可以更快，因为如果可以使查询更佳，MySQL可以在表t1之前使用表t2。为了强制使用表顺序，使用STRAIGHT_JOIN。
三、利用缓存来实现
现在社区分享类网站很火，就拿方维购物分享网站举例说明吧。也是对二次开发方维购物分享网站的一点总结，高手可以飞过。
购物分享的关键表有：分享表、图片表、文件表、评论表、标签表、分类表等。
围绕分享的表就么多，哇，那也不少啊。当我们查看一个图片的详细信息时，就要显示以上表里的信息。显示图片所属的分类、给图片打的标签、图片的评论、有文件的话还要显示文件下载信息等。难道让我们6个表去关联查询嘛，当然不能这么多关联来查询数据，我们可以只查询一个表即可，这怎么讲？这里分享表是主表，我们可以在主表里建立一个缓存字段。比如我们叫cache_data字段，赋予它text类型，这样可以存储很长的字符串，而不至于超过字段的最大存储。
这个缓存字段怎么用呢？在新增一条分享信息后，产生分享ID。如果用户发布图片或文件的话，图片信息入图片表，文件信息入文件表，然后把新产生的图片或文件信息写入到缓存字段里。同样的，如果用户有选择分类、打了标签的话，也把相应的信息写入到缓存字段里。对于评论而言，没有必要把全部评论存到缓存字段里，因为你不知道他有多少条记录，可以把最新的10条存到缓存字段里用于显示，这样缓存字段就变成一个二维或三维数组，序列化后存储到分享表里。
array( 'img' = array( name => '123.jpg', url => 'http: //tech.42xiu.com/123.jpg', width => 800, width => 600, ), 'file' = array( name => 'abc.zip', download_url => 'http: //tech.42xiu.com/abc.zip', size => 1.2Mb, ), 'category' = array( 1 => array( id => 5, name => PHP乐知博客 ), 2 => array( id => 6, name => PHP技术博客 ), ), 'tag' => array( tag1 tag2 ...... ), 'message' => array( 1 => array(id, uid, name, content, time), 2 => array(id, uid, name, content, time), 3 => array(id, uid, name, content, time), 4 => array(id, uid, name, content, time), ), ) //比如，上面的数组结构，序列化存入数据库。
UPDATE share SET cache_data=mysql_real_escape_string(serialize($cache_data)) WHERE id=1;这样查询就变得简单了，只需要查询一条就行了，取到缓存字段，把其反序列化，把数组信息提取出来，然后显示到页面。如果是以前那个结构，在几十万的数据量下，估计早崩溃了。数据缓存的方法也许不是最好的，如果你有更好的方法，可以相互学习，相互讨论。
相关推荐：
Das obige ist der detaillierte Inhalt vonAnalysieren und optimieren Sie die Effizienz gemeinsamer MySQL-Mehrtabellenabfragen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!