Mysql パフォーマンス最適化サブクエリの詳細な紹介

私がプロジェクトに取り組んでいたときに、「サブクエリを使用しないようにしてください」という格言を聞いたのを覚えています。この文が正しいかどうかを確認するためにこの記事を見てみましょう

その前に、いくつかの概念的なことを紹介する必要があります。 MySQL のステートメントの一般的な処理。

MySQL サーバーの接続スレッドがクライアントから送信された SQL リクエストを受信すると、一連の分解解析を経て、対応する分析が実行されます。その後、MySQL はクエリ オプティマイザー モジュールに応じて渡します。 SQL に関与するデータ テーブルの関連する 統計情報 が計算および分析され、Mysql が最も合理的で最適化されていると考えられるデータ アクセス方法が導出されます。これは、私たちがよく「 実行計画」と呼ぶものです。取得された実行プランに基づいて、ストレージ エンジン インターフェイスを呼び出して対応するデータを取得します。その後、ストレージ エンジンから返されたデータが処理され、クライアントが必要とする形式が結果セットとして使用され、クライアントに返されます。 .

注: ここで説明されている内容は、Analyze table コマンドを通じてテーブルの関連データを分析するよう Mysql に通知した後に取得されるデータ統計です。これらのデータは、Mysql オプティマイザーと、Mysql オプティマイザーによって生成される実行プランにとって非常に重要です。オプティマイザーの良し悪しは主にこれらの統計データによって決まります。

1. テーブルを作成します

create table User(
　　Id int not null PRIMARY key auto_increment ,
　　NickName varchar(50) comment '用户昵称',
　　Sex int comment '性别',
　　Sign varchar(50) comment '用户签名',
　　Birthday datetime comment '用户生日',
　　CreateTime datetime comment '创建时间') default charset=utf8 comment '用户表';create table UserGroup(
　　Id int not null PRIMARY key auto_increment ,
　　UserId int not null comment 'user Id',
　　GroupId int not null comment '用户组Id',
　　CreateTime datetime comment '创建时间',
　　-- key index_groupid(GroupId) using btree,　　key index_userid(groupid, UserId) using btree
) default charset=utf8 comment '用户组表';

2. データを準備します

var conStr = ConfigurationManager.ConnectionStrings["ConStr"].ToString();
using (IDbConnection conn = new MySqlConnection(conStr))
{
    Stopwatch watch = new Stopwatch();
    var sql = string.Empty;
    var names = new string[] { "非", "想", "红", "帝", "德", "看", "梅", "插", "兔" };
    Random ran = new Random();  
    var insertSql = @" insert into User(NickName,Sex,Sign, Birthday, CreateTime) values(@NickName,@Sex,@Sign, @Birthday, @CreateTime); 
    INSERT INTO usergroup  (UserId,  GroupId,  CreateTime )  VALUES (LAST_INSERT_ID() ,   @GroupId,  @CreateTime);";
    watch.Start();
    if (conn.State == ConnectionState.Closed)
    {
        conn.Open();
    }

    var tran = conn.BeginTransaction();
    for (int i = 0; i < 100000; i++)
    {
        var param = new { NickName = names[ran.Next(9)] + names[ran.Next(9)] + i, Sign = names[ran.Next(9)] + names[ran.Next(9)], CreateTime = DateTime.Now, Birthday = DateTime.Now.AddYears(ran.Next(10, 30)), Sex = i % 2, GroupId = ran.Next(1, 100) };
        conn.Execute(insertSql, param, tran);
    }
    tran.Commit();

    conn.Dispose();
    watch.Stop();
    Console.WriteLine(watch.ElapsedMilliseconds);
}

ここでは、5000 個のデータを挿入し、グループをランダムに 99 のグループに分割しました。 3 . クエリsql

explain
select user.id, user.nickname from usergroup 
left join user  on usergroup.UserId = user.Id
where  usergroup.groupid = 1 
order by usergroup.UserId desc
limit 100, 20;

 explain
select user.id, user.nickname
from (select id, userid from usergroup where groupid = 1 order by userid limit 100, 20) t
left join  user on t.UserId = user.id ;

 explain
select user.id, user.nickname
from (select id, userid from usergroup where groupid = 1 order by userid ) t
left join  user on t.UserId = user.id 
limit 100, 20;

2番目と3番目の文はどちらもサブクエリを使用しています。 違いは、2番目の文が最初に20個のデータを取得し、それをユーザーテーブルに関連付けることです

4. 100,000個の場合データの内容:

最初の文を見てください

次に2番目の文を見てください

3番目の文

上の3枚の写真から、それがわかると思います何を注文しましたか。

まず、2 番目の文が最も多く、合計すると 1,000 個を超えます。他の 2 つの文は合計 996 個になります。しかし、私が言いたいのは、これです。行の合計を見るのではなく、大きい方の ID を持つステートメントから始めて、同じ ID を持つステートメントを上から下に実行します

次に、2 番目の ID=2 を持つステートメントを見てください。文と最初の文の id=1 の文はまったく同じです。これらはすべて usergroup テーブルからデータをフィルタリングし、同じ結果セット A を取得できます。

それらはすべて同じ結果に基づいて動作しているようです。

最初の章を見てください。結果セット A に基づいて、左側の結合テーブル ユーザーに移動し、最終データをフィルタリングして、クライアントに返します。 2 番目の文は結果セット A に基づいており、データを再度フィルタリングして必要な結果データを取得し、このデータを使用してユーザー テーブルを左結合して最終結果を取得します

上記の観点から、

サブクエリによって大幅に削減できる場合は、サブクエリ ステートメントの使用を検討できます。

以上がMysql パフォーマンス最適化サブクエリの詳細な紹介の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。