検索
ホームページデータベース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のデータベースアップグレードをどのように処理しますか?Apr 30, 2025 am 12:28 AM

MySQLデータベースをアップグレードする手順には次のものがあります。1。データベースをバックアップします。2。現在のMySQLサービスを停止します。3。MySQLの新しいバージョンをインストールします。アップグレードプロセス中に互換性の問題が必要であり、Perconatoolkitなどの高度なツールをテストと最適化に使用できます。

MySQLに使用できるさまざまなバックアップ戦略は何ですか?MySQLに使用できるさまざまなバックアップ戦略は何ですか?Apr 30, 2025 am 12:28 AM

MySQLバックアップポリシーには、論理バックアップ、物理バックアップ、増分バックアップ、レプリケーションベースのバックアップ、クラウドバックアップが含まれます。 1. Logical BackupはMySqldumpを使用してデータベースの構造とデータをエクスポートします。これは、小さなデータベースとバージョンの移行に適しています。 2.物理バックアップは、データファイルをコピーすることで高速かつ包括的ですが、データベースの一貫性が必要です。 3.インクリメンタルバックアップは、バイナリロギングを使用して変更を記録します。これは、大規模なデータベースに適しています。 4.レプリケーションベースのバックアップは、サーバーからバックアップすることにより、生産システムへの影響を減らします。 5. Amazonrdsなどのクラウドバックアップは自動化ソリューションを提供しますが、コストと制御を考慮する必要があります。ポリシーを選択するときは、データベースサイズ、ダウンタイム許容度、回復時間、および回復ポイントの目標を考慮する必要があります。

MySQLクラスタリングとは何ですか?MySQLクラスタリングとは何ですか?Apr 30, 2025 am 12:28 AM

mysqlclusteringenhancesdatabaserobustnessnessnessnessnessnistandistributiondistributingdataacrossmultiplenodes.itesthendbenginefordatareplication andfaulttolerance、保証highavailability.setupinvolvesconfiguringmanagement、data、ssqlnodes、carefulmonitoringringandpe

MySQLのパフォーマンスのためにデータベーススキーマ設計を最適化するにはどうすればよいですか?MySQLのパフォーマンスのためにデータベーススキーマ設計を最適化するにはどうすればよいですか?Apr 30, 2025 am 12:27 AM

MySQLのデータベーススキーマ設計の最適化は、次の手順を通じてパフォーマンスを改善できます。1。インデックス最適化:一般的なクエリ列にインデックスを作成し、クエリのオーバーヘッドのバランスをとり、更新を挿入します。 2。テーブル構造の最適化:正規化または反通常化によりデータ冗長性を削減し、アクセス効率を改善します。 3。データ型の選択:Varcharの代わりにINTなどの適切なデータ型を使用して、ストレージスペースを削減します。 4。パーティション化とサブテーブル:大量のデータボリュームの場合、パーティション化とサブテーブルを使用してデータを分散させてクエリとメンテナンスの効率を改善します。

MySQLのパフォーマンスをどのように最適化できますか?MySQLのパフォーマンスをどのように最適化できますか?Apr 30, 2025 am 12:26 AM

tooptimizemysqlperformance、soflowthesesteps:1)properindexingtospeedupqueries、2)useexplaintoanalyzeandoptimize Queryperformance、3)AductServerContingSettingStingsinginginnodb_buffer_pool_sizeandmax_connections、4)

データ処理と計算にMySQL関数を使用する方法データ処理と計算にMySQL関数を使用する方法Apr 29, 2025 pm 04:21 PM

MySQL関数は、データ処理と計算に使用できます。 1.基本的な使用には、文字列処理、日付計算、数学操作が含まれます。 2。高度な使用法には、複数の関数を組み合わせて複雑な操作を実装することが含まれます。 3.パフォーマンスの最適化では、Where句での機能の使用を回避し、GroupByおよび一時テーブルを使用する必要があります。

MySQLにデータを挿入する効率的な方法MySQLにデータを挿入する効率的な方法Apr 29, 2025 pm 04:18 PM

MySQLでデータを挿入するための効率的な方法には、次のものが含まれます。1。insertInto ...値構文、2。LoadDatainFileコマンドの使用、3。トランザクション処理の使用、4。バッチサイズの調整、5。Insurtignoreまたは挿入の使用...

フィールドをMySQLテーブルに追加および削除する手順フィールドをMySQLテーブルに追加および削除する手順Apr 29, 2025 pm 04:15 PM

MySQLでは、AlterTabletable_nameaddcolumnnew_columnvarchar(255)afterexisting_columnを使用してフィールドを追加し、andtabletable_namedopcolumncolumn_to_dropを使用してフィールドを削除します。フィールドを追加するときは、クエリのパフォーマンスとデータ構造を最適化する場所を指定する必要があります。フィールドを削除する前に、操作が不可逆的であることを確認する必要があります。オンラインDDL、バックアップデータ、テスト環境、および低負荷期間を使用したテーブル構造の変更は、パフォーマンスの最適化とベストプラクティスです。

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衣類リムーバー

Video Face Swap

Video Face Swap

完全無料の AI 顔交換ツールを使用して、あらゆるビデオの顔を簡単に交換できます。

ホットツール

ドリームウィーバー CS6

ドリームウィーバー CS6

ビジュアル Web 開発ツール

SAP NetWeaver Server Adapter for Eclipse

SAP NetWeaver Server Adapter for Eclipse

Eclipse を SAP NetWeaver アプリケーション サーバーと統合します。

PhpStorm Mac バージョン

PhpStorm Mac バージョン

最新(2018.2.1)のプロフェッショナル向けPHP統合開発ツール

AtomエディタMac版ダウンロード

AtomエディタMac版ダウンロード

最も人気のあるオープンソースエディター

Safe Exam Browser

Safe Exam Browser

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