検索
ホームページデータベースmysql チュートリアルMySQL の複数テーブルの結合クエリ効率を分析して最適化する

MySQL のビッグ データ クエリの最適化は、多くの Web マスターによって慎重に分析されていません。編集者は最近 100W データの最適化の問題に遭遇しました。ここに、MySQL 関連のクエリ最適化テストと関連するいくつかのリストを示します。この分析が皆さんのお役に立てれば幸いです。 。

関連する mysql ビデオ チュートリアル: "mysql チュートリアル"

まず、単純な相関サブクエリの最適化です。

多くの場合、mysql に実装されたサブクエリのパフォーマンスは低く感じられます。 。特に、IN() サブクエリ ステートメントを使用する場合、ある程度の大きさのテーブルの場合、推定に時間がかかりすぎることがあります。私のmysqlの知識は深くないので、ゆっくりと謎を解くことしかできません。
次のような既存のクエリ ステートメントがあるとします。

 select * from table1 
  where exists
      (select * from table2 where id>=30000 and table1.uuid=table2.uuid);

table1 は 10 万行のテーブル、table2 は 100 万行のテーブルで、ローカル テストの結果には 2.40 秒かかります。

サブクエリが関連サブクエリ (DEPENDENCE SUBQUERY) であることがわかります。Mysql は最初に外側の table1 で完全なテーブル スキャンを実行し、次に返された uuid に基づいてサブクエリを 1 つずつ実行します。外部テーブルが大きなテーブルである場合、クエリのパフォーマンスはこのテストよりも悪くなることが想像できます。

簡単な最適化ソリューションは、内部結合メソッドを使用してサブクエリを置き換えることです。クエリ ステートメントを次のように変更できます。

 select * from table1 innner join table2 using(uuid) where table2.id>=30000;

ローカル テストの結果には 0.68 秒かかりました。

mysql が SIMPLE タイプ (サブクエリまたはユニオン以外のクエリメソッド) を使用していることがわかります。Mysql オプティマイザーは最初に table2 をフィルタリングし、次に table1 と table2 のデカルト積を実行して結果セットを取得し、次にデータをフィルタリングするための条件。

2. 複数テーブルの結合クエリの効率分析と最適化
1. 複数テーブルの接続タイプ
1. デカルト積 (クロスジョイン) MySQL では、CROSS JOIN または CROSS または JOIN を省略するか、',' などを使用できます。

  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

返される結果は接続された 2 つのデータ テーブルの積であるため、データ テーブル項目が多すぎると、非常に遅い。通常は LEFT [OUTER] JOIN または RIGHT [OUTER] JOIN を使用します

2. INNER JOIN INNER JOIN は MySQL では等結合と呼ばれ、MySQL では CROSS と INNER JOIN を Together に分けて指定する必要があります。 。 join_table: table_reference [INNER | CROSS] JOIN table_factor [join_condition]

3. MySQL の外部結合は、左外部結合と右結合に分けられます。つまり、結合条件を満たす結果を返すことに加えて、左のテーブル (左)接続)、または接続条件を満たさない右テーブルの結果(右結合)の場合は、それに応じて NULL を使用します。

例:

user table:

id | name
  ———
  1 | libk
  2 | zyfon
  3 | daodao

user_action table:

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              ⑤

分析:
user_action には user_id=4、action=swim のレコードがありますが、結果
user テーブル内の id=3 および name=daodao のユーザーは user_action に対応するレコードを持っていませんが、結果セットには表示されます
現在は left join であるため、すべての作業は left に基づいています。結果 1、2、3、および 4 は、左側のテーブルと右側のテーブルの両方のレコードです。5 は、左側のテーブルにのみ存在し、右側のテーブルには存在しません

動作原理:

左側のテーブルからレコードを読み取り、 on 条件に一致するすべてのレコードを選択します。 右側のテーブルのレコード (n) が接続されて n 個のレコードが形成されます (結果 1 と結果 3 などの重複行を含みます)。フィールドはすべて null です。次に読み続けます。

拡張機能:

右側のテーブルに一致するものが存在しない場合、null が表示され、左側のテーブルにはあるが右側のテーブルには存在しないすべてのレコードが検索されるというルールを使用できます。判断は無効ではないと宣言する必要があります。
例:
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

(注:


1. 列の値が null の場合は、=NULL の代わりに is null を使用する必要があります

2. ここで、a.user_id 列は NOT NULL として宣言する必要があります。 )
上記の SQL 結果:

 id | name | action  
 ————————–  
 3 | daodao | NULL
——————————————————————————–

一般的な使用法:

a. LEFT [OUTER] JOIN:

結合条件を満たす結果を返すことに加えて、左側のテーブルのデータ列を表示する必要もあります。

  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 は、接続条件を満たす結果を表示することに加えて、右のテーブルのデータ列も表示する必要があるという点だけが異なります。接続条件を満たさない場合は、NULL を使用してください

 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. on a.c1 = b.c1 は using(c1) と同等です

2. INNER JOIN および (カンマ) は意味的に同等です
3. MySQL が取得する場合テーブルからの情報を取得する場合は、どのインデックスを選択するかを求めることができます。
この機能は、MySQL が可能なインデックスのリストから間違ったインデックスを使用していることを EXPLAIN が示した場合に役立ちます。
USE INDEX (key_list) を指定すると、テーブル内の行を検索するために可能なインデックスのうち最も適切なものを使用するように MySQL に指示できます。
オプションの 2 番目に選択される構文 IGNORE INDEX (key_list) を使用して、MySQL に特定のインデックスを使用しないように指示できます。例:

  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. テーブル接続の制約

表示条件を追加する WHERE、ON、USING

1. WHERE 句 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;这样查询就变得简单了,只需要查询一条就行了,取到缓存字段,把其反序列化,把数组信息提取出来,然后显示到页面。如果是以前那个结构,在几十万的数据量下,估计早崩溃了。数据缓存的方法也许不是最好的,如果你有更好的方法,可以相互学习,相互讨论。

相关推荐:

ThinkPHP多表联合查询的常用方法_PHP教程

mysql多表联合查询返回一张表的内容实现代码

MYSQL多表联合查询的问题

以上がMySQL の複数テーブルの結合クエリ効率を分析して最適化するの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。

声明
この記事の内容はネチズンが自主的に寄稿したものであり、著作権は原著者に帰属します。このサイトは、それに相当する法的責任を負いません。盗作または侵害の疑いのあるコンテンツを見つけた場合は、admin@php.cn までご連絡ください。
图文详解mysql架构原理图文详解mysql架构原理May 17, 2022 pm 05:54 PM

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了关于架构原理的相关内容,MySQL Server架构自顶向下大致可以分网络连接层、服务层、存储引擎层和系统文件层,下面一起来看一下,希望对大家有帮助。

mysql怎么替换换行符mysql怎么替换换行符Apr 18, 2022 pm 03:14 PM

在mysql中,可以利用char()和REPLACE()函数来替换换行符;REPLACE()函数可以用新字符串替换列中的换行符,而换行符可使用“char(13)”来表示,语法为“replace(字段名,char(13),'新字符串') ”。

mysql的msi与zip版本有什么区别mysql的msi与zip版本有什么区别May 16, 2022 pm 04:33 PM

mysql的msi与zip版本的区别:1、zip包含的安装程序是一种主动安装,而msi包含的是被installer所用的安装文件以提交请求的方式安装;2、zip是一种数据压缩和文档存储的文件格式,msi是微软格式的安装包。

mysql怎么去掉第一个字符mysql怎么去掉第一个字符May 19, 2022 am 10:21 AM

方法:1、利用right函数,语法为“update 表名 set 指定字段 = right(指定字段, length(指定字段)-1)...”;2、利用substring函数,语法为“select substring(指定字段,2)..”。

mysql怎么将varchar转换为int类型mysql怎么将varchar转换为int类型May 12, 2022 pm 04:51 PM

转换方法:1、利用cast函数,语法“select * from 表名 order by cast(字段名 as SIGNED)”;2、利用“select * from 表名 order by CONVERT(字段名,SIGNED)”语句。

MySQL复制技术之异步复制和半同步复制MySQL复制技术之异步复制和半同步复制Apr 25, 2022 pm 07:21 PM

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了关于MySQL复制技术的相关问题,包括了异步复制、半同步复制等等内容,下面一起来看一下,希望对大家有帮助。

带你把MySQL索引吃透了带你把MySQL索引吃透了Apr 22, 2022 am 11:48 AM

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了mysql高级篇的一些问题,包括了索引是什么、索引底层实现等等问题,下面一起来看一下,希望对大家有帮助。

mysql怎么判断是否是数字类型mysql怎么判断是否是数字类型May 16, 2022 am 10:09 AM

在mysql中,可以利用REGEXP运算符判断数据是否是数字类型,语法为“String REGEXP '[^0-9.]'”;该运算符是正则表达式的缩写,若数据字符中含有数字时,返回的结果是true,反之返回的结果是false。

See all articles

ホットAIツール

Undresser.AI Undress

Undresser.AI Undress

リアルなヌード写真を作成する AI 搭載アプリ

AI Clothes Remover

AI Clothes Remover

写真から衣服を削除するオンライン AI ツール。

Undress AI Tool

Undress AI Tool

脱衣画像を無料で

Clothoff.io

Clothoff.io

AI衣類リムーバー

AI Hentai Generator

AI Hentai Generator

AIヘンタイを無料で生成します。

ホットツール

Safe Exam Browser

Safe Exam Browser

Safe Exam Browser は、オンライン試験を安全に受験するための安全なブラウザ環境です。このソフトウェアは、あらゆるコンピュータを安全なワークステーションに変えます。あらゆるユーティリティへのアクセスを制御し、学生が無許可のリソースを使用するのを防ぎます。

DVWA

DVWA

Damn Vulnerable Web App (DVWA) は、非常に脆弱な PHP/MySQL Web アプリケーションです。その主な目的は、セキュリティ専門家が法的環境でスキルとツールをテストするのに役立ち、Web 開発者が Web アプリケーションを保護するプロセスをより深く理解できるようにし、教師/生徒が教室環境で Web アプリケーションを教え/学習できるようにすることです。安全。 DVWA の目標は、シンプルでわかりやすいインターフェイスを通じて、さまざまな難易度で最も一般的な Web 脆弱性のいくつかを実践することです。このソフトウェアは、

SublimeText3 英語版

SublimeText3 英語版

推奨: Win バージョン、コードプロンプトをサポート!

EditPlus 中国語クラック版

EditPlus 中国語クラック版

サイズが小さく、構文の強調表示、コード プロンプト機能はサポートされていません

SublimeText3 Linux 新バージョン

SublimeText3 Linux 新バージョン

SublimeText3 Linux 最新バージョン