Maison >base de données >tutoriel mysql >Analyser et optimiser l'efficacité des requêtes conjointes multi-tables Mysql
Analyser et optimiser l'efficacité des requêtes conjointes multi-tables Mysql
- 小云云original
- 2017-12-13 14:10:5414229parcourir
L'optimisation des requêtes MySQL Big Data n'est pas quelque chose que de nombreux webmasters analyseront attentivement Pour ce problème, l'éditeur a récemment rencontré un problème d'optimisation des données de 100 W. Voici quelques corrélations MySQL, j'espère que le test d'optimisation des requêtes et. une analyse connexe peut aider tout le monde.
Tutoriels vidéo mysql recommandés : "tutoriel mysql"
1. Une optimisation d'une sous-requête corrélée simple.
Souvent, les performances des sous-requêtes implémentées sur MySQL sont médiocres, ce qui semble vraiment triste. Surtout parfois, lors de l'utilisation de l'instruction de sous-requête IN(), pour des tables d'un certain ordre de grandeur, l'estimation prend trop de temps. Mes connaissances de MySQL ne sont pas approfondies, je ne peux donc que lentement comprendre le mystère.
Supposons qu'il existe une telle instruction de requête existe :
select * from table1
where exists
(select * from table2 where id>=30000 and table1.uuid=table2.uuid);table1 est une table avec cent mille lignes et table2 est une table avec un million de lignes. Le résultat du test local prend 2,40 secondes.
Vous pouvez voir en expliquant que la sous-requête est une sous-requête associée (SOUS-REQUETE DE DÉPENDANCE) ; Mysql effectuera d'abord une analyse complète de la table sur la table externe1, puis exécutera la sous-requête l'une après l'autre en fonction de l'uuid renvoyé. Si la table externe est une grande table, on peut imaginer que les performances des requêtes seront moins bonnes que dans ce test.
Une solution d'optimisation simple consiste à utiliser la méthode de jointure interne pour remplacer la sous-requête. L'instruction de requête peut être modifiée en :
select * from table1 innner join table2 using(uuid) where table2.id>=30000;
Le résultat du test local a pris 0,68 s.
Vous pouvez voir en expliquant que mysql utilise le type SIMPLE (méthode de requête autre que la sous-requête ou l'union) ; l'optimiseur Mysql filtrera d'abord la table2, puis fera le produit cartésien de la table1 et de la table2 pour obtenir l'ensemble de résultats. . Filtrez ensuite les données selon des conditions.
2. Analyse et optimisation de l'efficacité des requêtes conjointes multi-tables
1. Type de connexion multi-tables
1. Le produit cartésien (connexion croisée) dans MySQL peut être CROSS JOIN ou omettre CROSS, qui est JOIN. Ou utilisez ',' tel que :
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
Étant donné que le résultat renvoyé est le produit des deux tables de données connectées, il n'est généralement pas recommandé de l'utiliser lorsqu'il y a des conditions WHERE, ON ou USING. , car lorsqu'il y a trop d'éléments de table de données, cela sera très lent. Utilisez généralement LEFT [OUTER] JOIN ou RIGHT [OUTER] JOIN
2. INNER JOIN INNER JOIN est appelé une équijointure dans MySQL, c'est-à-dire que vous devez spécifier les conditions d'équijoin dans CROSS dans MySQL et INNER JOIN sont divisés ensemble. join_table: table_reference [INNER | CROSS] JOIN table_factor [join_condition]
3. Les jointures externes dans MySQL sont divisées en jointures externes gauche et jointures droites, c'est-à-dire qu'en plus de renvoyer des résultats qui répondent aux conditions de jointure, les résultats qui ne remplissent pas les conditions de jointure dans la table de gauche (jointure gauche) ou de droite (jointure droite), NULL est utilisé en conséquence.
Exemple :
table utilisateur :
id | name ——— 1 | libk 2 | zyfon 3 | daodao
table d'action_utilisateur :
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 :
Notez qu'il y a un enregistrement de user_id=4, action=swim dans user_action, mais il n'apparaît pas dans les résultats
Et l'utilisateur avec id=3, name=daodao dans la table user n'en a pas. enregistrement correspondant dans l'enregistrement user_action, mais il apparaît dans l'ensemble de résultats
Comme il s'agit désormais d'une jointure à gauche, tout le travail est basé sur la gauche
Les résultats 1, 2, 3 et 4 sont tous des enregistrements dans les deux. la table de gauche et la table de droite, 5 est un enregistrement qui se trouve uniquement dans la table de gauche mais pas dans la table de droite
Principe de fonctionnement :
Lisez un enregistrement de la table de gauche et sélectionnez tous les enregistrements de table de droite (n) qui correspondent lors de la jointure pour former n enregistrements (y compris les lignes en double, telles que le résultat 1 et le résultat 3). S'il n'y a pas de table à droite qui correspond à la condition on, les champs connectés sont tous nuls. Continuez ensuite à lire le suivant.
Extension :
On peut utiliser la règle d'affichage de null s'il n'y a pas de correspondance dans la table de droite pour retrouver tous les enregistrements qui sont dans la table de gauche mais pas dans la table de droite. la colonne utilisée pour le jugement doit être déclarée non nulle.
Par exemple :
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
(Remarque :
1. Si la valeur de la colonne est nulle, vous devez utiliser is null au lieu de =NULL
2 .Ici, la colonne a.user_id doit être déclarée comme NON NULL.)
Le résultat du sql ci-dessus :
id | name | action ————————– 3 | daodao | NULL ——————————————————————————–
Utilisation générale :
a. LEFT [OUTER] JOIN :
En plus de renvoyer des résultats qui répondent aux conditions de jointure, vous devez également afficher les colonnes de données dans le tableau de gauche qui ne remplissent pas les conditions de jointure. En conséquence, utilisez NULL pour.
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 est similaire à LEFT JOIN La différence est qu'en plus d'afficher les résultats qui remplissent les conditions de connexion, vous devez également afficher les colonnes de données dans le tableau de droite qui ne remplissent pas les conditions de connexion . Utilisez NULL en conséquence pour
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 sur a.c1 = b1 équivaut à utiliser (c1)
2. INNER JOIN et, (virgule) sont sémantiquement équivalents
3. Lorsque MySQL récupère des informations d'une table, vous pouvez lui demander de choisir quel index.
Cette fonctionnalité est utile si EXPLAIN montre que MySQL utilise le mauvais index dans la liste des index possibles.
En spécifiant USE INDEX (key_list), vous pouvez demander à MySQL d'utiliser le plus approprié des index possibles pour trouver les lignes de la table.
La syntaxe facultative de deuxième choix IGNORE INDEX (key_list) peut être utilisée pour indiquer à MySQL de ne pas utiliser un index spécifique. Par exemple :
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. Contraintes de connexion à la table
Ajouter des conditions d'affichage WHERE, ON, USING
1.WHERE clause mysql>
01.SELECT * FROM table1,table2 WHERE table1.id=table2.id; SELECT * FROM table1,table2 WHERE table1.id=table2.id;
2. SUR
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;这样查询就变得简单了，只需要查询一条就行了，取到缓存字段，把其反序列化，把数组信息提取出来，然后显示到页面。如果是以前那个结构，在几十万的数据量下，估计早崩溃了。数据缓存的方法也许不是最好的，如果你有更好的方法，可以相互学习，相互讨论。
相关推荐：
Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!