MySQL の垂直テーブルを水平テーブルに変換する方法と最適化のチュートリアル

MySQL の垂直テーブルを水平テーブルに変換する方法と最適化のチュートリアル

1. 縦型テーブルと横型テーブル

垂直テーブル: テーブル内のフィールドとフィールド値はキーと値の形式です。つまり、テーブルには 2 つのフィールドが定義され、そのうちの 1 つにはフィールド名が格納され、もう 1 つのフィールドにはこのフィールド名で表されるフィールドの値が格納されます。

たとえば、次の ats_item_record テーブルでは、field_code はフィールドを表し、次の record_value はこのフィールドの値を表します。

長所と短所:

水平テーブル:テーブル構造がより明確になり、関連するクエリの一部 SQL ステートメントが簡単になり、後続の開発者が引き継ぐのに便利です。ただし、フィールドが足りず、新しいフィールドを追加する必要がある場合は、テーブル構造が変更されます。

垂直テーブル:スケーラビリティが高くなります。フィールドを追加する場合、テーブル構造を変更する必要はありません。ただし、一部の関連クエリは、メンテナンス担当者やフォローアップ担当者にとって面倒で不便になります。

通常の開発では、垂直テーブルではなく水平テーブルを使用するようにしてください。メンテナンスコストが比較的高く、関連するクエリもいくつか面倒です。

2. 縦の表を横の表に変換する

(1)最初のステップは、縦の表からこれらのフィールド名と対応するフィールド値を抽出することです。

r.original_record_id、r.did、r.device_sn、r.mac_address、r.record_time、r.updated_time updated_time を選択、
(case r.field_code when 'accumulated_cooking_time' then r.record_value else '' end ) 累積調理時間、
(case r.field_code when 'data_version' then r.record_value else '' end) data_version,
(case r.field_code when 'loop_num' then r.record_value else '' end) loop_num,
(case r.field_code when 'status' then r.record_value else '' end) ステータス
ats_item_record r から 
ここで、item_code = 'GONGMO_AGING'

結果:

case ステートメントにより、フィールドは垂直テーブルから正常に取り出されましたが、この時点ではまだ水平テーブルではありません。ここでの original_record_id は、同じデータ行を記録する一意の ID です。このフィールドを使用して、上記の 4 行を 1 行のレコードに結合できます。

注:ここでは、各フィールドを抽出し、ケース チェックを行う必要があります。フィールドの数と同じ数のケース ステートメントが必要です。 case 文は、条件を満たす when 文に遭遇すると、それ以降の文は実行されなくなります。

(2)同一行をグループ化し、結合して水平方向の表を生成する

(から*を選択
	r.original_record_idを選択し、
    max(r.did) は、
    max(r.device_sn) デバイス_sn、
    max(r.mac_address) mac_address、
    max(r.record_time) 記録時間、
	max(r.updated_time) 更新時間、
	max((case r.field_code when 'accumulated_cooking_time' then r.record_value else '' end )) 累積調理時間、
	max((case r.field_code when 'data_version' then r.record_value else '' end)) data_version,
	max((case r.field_code when 'loop_num' then r.record_value else '' end)) loop_num,
	max((case r.field_code when 'status' then r.record_value else '' end)) ステータス
	ats_item_record r から 
	ここで、item_code = 'GONGMO_AGING'
	r.original_record_id でグループ化
) m は m.updated_time の desc で順序付けされます。

クエリの結果:

注: group by を使用する場合は、フィールドに max 関数を追加する必要があります。 group by を使用する場合、通常は集計関数と一緒に使用されます。一般的な集計関数は次のとおりです。

  • AVG()は平均を求める
  • COUNT()は列の合計数を求める
  • MAX() 最大値を求める
  • MIN() 最小値を求める
  • 和()

垂直テーブル内の同じレコードの共通フィールド r.original_record_id を group by に入れていることに注意してください。このフィールドは、垂直テーブル内の同じレコードに対して同一かつ一意であり、変更されることはありません (前の水平テーブルの主キー ID に相当)。次に、他のフィールドを max に入れました (他のフィールドは同じか、最大のフィールドが取得できるか、垂直テーブル レコードの 1 つだけに値があり、他のレコードが空であるため、これら 3 つのケースでは max を直接使用できるため)。4 つのレコードの最大更新時刻を同じレコードの更新時刻として取得することは、論理的に適切です。次に、垂直テーブル フィールド field_code と record_value に対して max() 操作を実行します。これらは同じレコード内で一意であるため、同じデータ内に 2 つの同一の field_code レコードが存在することはありません。したがって、この方法で max() を実行しても問題はありません。

最適化ポイント:

最後に、この SQL を最適化できます。テンプレート フィールドを具体的に保存するテーブルからすべてのテンプレート フィールド (r.original_record_id、r.did、r.device_sn、r.mac_address、r.record_time など) を取り出し (同じ論理垂直テーブル内のすべてのフィールドが取り出されます)、コード内の max() 部分をパラメーターとしてつなぎ合わせて実行します。このようにして、汎用的にすることができます。新しいテンプレート フィールドを追加するたびに、SQL ステートメントを変更する必要はありません (これが、China Mobile が携帯電話のパラメーター データを保存する方法です)。

最適化されたビジネス レイヤー (SQL テンプレートを組み立てるためのコード) は次のとおりです。

@オーバーライド
パブリック PageInfo<AtsAgingItemRecordVo> getAgingItemList(AtsItemRecordQo qo) {
    //1. モデルのエイジング フィールドのテンプレートを取得します。LambdaQueryWrapper<AtsItemFieldPo> queryWrapper = Wrappers.lambdaQuery();
    queryWrapper.eq(AtsItemFieldPo::getItemCode、AtsItemCodeConstant.GONGMO_AGING.getCode());
    リスト<AtsItemFieldPo> fieldPoList = atsItemFieldDao.selectList(queryWrapper);
    //2. クエリ条件を組み立てる List<String> tplList = Lists.newArrayList(), conditionList = Lists.newArrayList(), validList = Lists.newArrayList();
    CollectionUtils.isEmpty(fieldPoList) の場合:
        //3. 動的最大クエリフィールドを組み立てる for (AtsItemFieldPo itemFieldPo : fieldPoList) {
            tplList.add("max((case r.field_code when '" + itemFieldPo.getFieldCode() + "' then r.record_value else '' end )) " + itemFieldPo.getFieldCode());
            有効なリストを追加します(itemFieldPo.getFieldCode());
        }
        tplList を設定します。
        //4. 動的なwhereクエリ条件を組み立てる if (StringUtils.isNotBlank(qo.getDid())) {
            conditionList.add("AND は CONCAT('%'," + qo.getDid() + ",'%') のように実行しました");
        }
        (validList.contains("batch_code") && StringUtils.isNotBlank(qo.getBatchCode()))の場合{
            conditionList.add("AND batch_code は CONCAT('%'," + qo.getBatchCode() + ",'%') のように機能します");
        }
        条件リストを設定します。
    }
    qo.setItemCode(AtsItemCodeConstant.GONGMO_AGING.getCode());
    //4. エイジング自動化テスト項目レコードを取得します。Pag​​eHelper.startPage(qo.getPageNo(), qo.getPageSize());
    リスト<Map<文字列、オブジェクト>> dataList = atsItemRecordDao.selectItemRecordListByCondition(qo);
    PageInfo pageInfo = 新しい PageInfo(dataList);
    //5. 返された結果を組み立てる List<AtsAgingItemRecordVo> recordVoList = null;
    CollectionUtils.isEmpty(dataList) の場合:
        recordVoList = JSONUtils.copy(dataList、AtsAgingItemRecordVo.class);
    }
    ページ情報。レコードリストを設定します。
    pageInfo を返します。
}

最適化された Dao レイヤー コードは次のとおりです。

パブリックインターフェース AtsItemRecordDao は BaseMapper<AtsItemRecordPo> を拡張します {
 
    List<Map<String, Object>> selectItemRecordListByCondition(AtsItemRecordQo qo);
}

最適化された SQL ステートメント コードは次のとおりです。

<select id="selectItemRecordListByCondition" resultType="java.util.HashMap"
        パラメータタイプ="com.galanz.iot.ops.restapi.model.qo.AtsItemRecordQo">
    選択 * から (
        r.original_record_id id を選択、
        max(r.did) は、
        max(r.device_sn) デバイス_sn、
        max(r.updated_time) 更新時間、
        max(r.record_time) 記録時間、
        <if test="tplList != null かつ tplList.size() > 0">
            <foreach コレクション="tplList" 項目="tpl" インデックス="インデックス" セパレーター=",">
                ${tpl}
            </foreach>
        </if>
        ats_item_record r から
        ここで、item_code = #{itemCode}
        r.original_record_id によるグループ化
    )
    <どこ>
        <if test="conditionList != null かつ conditionList.size() > 0">
            <foreach コレクション="条件リスト" 項目="条件" インデックス="インデックス">
                ${条件}
            </foreach>
        </if>
    </どこ>
    ORDER BY m.updated_time DESC
</選択>

テンプレート フィールド テーブル構造 (ats_item_field テーブル) は次のとおりです。

フィールド名タイプ長さ注記
idビッグイント20主キーID
フィールドコードvarchar 32フィールドエンコーディング
フィールド名varchar 32フィールド名
述べるvarchar 512述べる
作成者ビッグイント20作成者ID
作成日時日時0作成時間
更新者ビッグイント20アップデータID
更新日時日時0更新時間

レコード テーブル構造 (ats_item_record テーブル) は次のとおりです。

フィールド名タイプ長さ注記
idビッグイント20主キーID
したvarchar 64デバイス固有ID
デバイス_sn varchar 32装置
mac_アドレスvarchar 32デバイスのMacアドレス
フィールドコードvarchar 32フィールドエンコーディング
オリジナルレコードID varchar 64オリジナルレコードID
レコード値varchar 32記録的な価値
作成者ビッグイント20作成者ID
作成日時日時0作成時間
更新者ビッグイント20アップデータID
更新日時日時0更新時間

注: original_record_id は、垂直テーブルを水平テーブルに変換した後の各レコードの一意の ID です。これは、通常の水平テーブルの主キー ID と同じと見なすことができます。

これで、MySQL の垂直テーブルを水平テーブルに変換する方法の紹介は終了です。

要約する

これで、MySQL の垂直テーブルを水平テーブルに変換する方法についての説明は終わりです。MySQL の垂直テーブルを水平テーブルに変換する方法の詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません:
  • MySQLにおける遅いSQLの最適化の方向性について詳しく話しましょう
  • MySQL 選択最適化ソリューションに関する簡単な説明
  • 数千万データを持つMySQLテーブルを最適化する実践記録
  • MySqlサブクエリINの実装と最適化
  • MySQLを素早く最適化する
  • MySQL データ挿入最適化メソッドconcurrent_insert
  • MySQL 最適化 query_cache_limit パラメータの説明
  • MySQL の最適化: 高品質の SQL 文を書く方法
  • MySQLクエリ最適化: 100万件のデータに対するテーブル最適化ソリューション
  • MYSQL の 10 の典型的な最適化ケースとシナリオ

<<:  nginxの基礎を学ぶ

>>:  CSS 背景画像を設定するための 6 つの興味深いヒント

推薦する

Firefox ブラウザでバックグラウンド ミュージックを再生するための究極のソリューション (Chrome マルチブラウザ対応)

FirefoxでBGMを再生するための推奨コードがテストに合格しました空のコントロールパネルを開いて...

MySQL でのストアド プロシージャと関数の作成の詳細な説明

目次1. ストアドプロシージャ1.1. 基本構文1.2 実行権限を指定してストアドプロシージャを作成...

ネイティブ JavaScript 継承方法とその長所と短所の詳細な説明

目次序文プロトタイプ継承アドバンテージ欠点コンストラクタの継承アドバンテージ欠点組み合わせ継承寄生的...

AWS無料サーバーアプリケーションとネットワークプロキシ設定チュートリアルの詳細な説明

目次予防必要条件AWSアカウントを申請する仮想マシンの申請と有効化仮想マシンを申請するセキュリティグ...

MySQLのグローバルロックとテーブルロックに関する詳細な理解

序文ロックの範囲に応じて、MySQL のロックは、グローバル ロック、テーブル ロック、行ロックに大...

iOS WeChat H5ページのゴム反発効果の落とし穴記録

ビジネス要件最近開発したプロジェクトの一つに、WeChat公式アカウントに関連したH5ページがありま...

ARM64アーキテクチャでmysql5.7.22をインストールするプロセス全体

MySQLダウンロードアドレス: https://obs.cn-north-4.myhuaweicl...

イメージを再構築せずにDockerにポートを動的に追加する方法

操作中に Docker コンテナの公開ポートを変更または追加する必要がある場合がありますが、実行中の...

Docker インストール Nginx チュートリアル 実装図

Nginx をインストールして試してみましょう。画像はクラスであり、コンテナはオブジェクトであること...

Linuxプロセス通信におけるFIFOの実装

FIFO通信(先入れ先出し)関連のないプロセス間の通信を可能にする FIFO 名前付きパイプ。パイプ...

JavaScript ジグソーパズルゲーム

この記事の例では、ジグソーパズルゲームを実装するためのJavaScriptの具体的なコードを参考まで...

MySQL 8.0.14 のインストールと設定方法のグラフィックチュートリアル (一般)

MySQLサービス8.0.14のインストール(一般)の参考までに、具体的な内容は次のとおりです。イ...

Linux の ufw ファイアウォールの紹介

Linux のufw (Uncomplicated Firewall) を見て、ファイアウォールに変...

IE8は優れたエクスペリエンスを提供します: アクティビティ

今日は IE8 ベータ 1 (以下、IE8 と略します) をチラ見しました。IE8 は素晴らしい体験...

Windows 10 での MySQL 5.7.21 winx64 のインストールと設定方法のグラフィック チュートリアル

mysql 5.7.21 winx64 のインストールと設定方法: MySQLのコミュニティバージョ...