MySQL で主キーと ROWID を使用する際の落とし穴の概要

MySQL で主キーと ROWID を使用する際の落とし穴の概要

序文

MySQL の rowid の概念については聞いたことがあるかもしれませんが、テストや実践が難しく、次のような疑問が必ず生じます。

  • rowid の存在を感知する方法。
  • ROWID と主キーの関係は何ですか?
  • 主キーの使用にはどのような隠れた危険が存在しますでしょうか?
  • 潜在的な ROWID ボトルネックを理解し、検証をデバッグする方法。

この記事ではこれらの問題について説明します。テスト環境は MySQL バージョン 5.7.19 に基づいています。

質問1: rowidの存在を検出する方法

これを事例で説明してみましょう。

ある日、バックアップ データをカウントしていたときに、SQL 文を記述したのを覚えています。実行結果を見ると、SQL 文が完全ではなかったことがわかりました。統計作業を完了した後、この SQL 文を分析する準備をしました。

mysql> redis_backup_result から backup_date 、count(*) piece_no を選択します。

+-------------+-----------+

| バックアップ日付 | ピース番号 |

+-------------+-----------+

| 2018-08-14 | 40906 |

+-------------+-----------+

セット内の1行（0.03秒）

業務の特性上、1 日にこれほど多くのレコードが存在するべきではありません。これは明らかに間違っています。何が間違っていたのでしょうか?

SQL を注意深く調べたところ、group by がないことがわかりました。ランダムに 10 個のデータが見つかりました。

mysql> redis_backup_result から backup_date を制限 10 から選択します。

+-------------+

| バックアップ日付 |

+-------------+

| 2018-08-14 |

| 2018-08-14 |

| 2018-08-14 |

| 2018-08-15 |

| 2018-08-15 |

| 2018-08-15 |

| 2018-08-15 |

| 2018-08-15 |

| 2018-08-15 |

| 2018-08-15 |

+-------------+

セット内の行数は 10 です (0.00 秒)

以前のバージョンでは、データベース パラメータ sql_mode はデフォルトで空であり、この部分はチェックされません。文法上はこれは許可されていますが、バージョン 5.7 以降などの新しいバージョンではサポートされていません。したがって、解決策は非常に簡単です。group by を追加すると、結果は期待どおりになります。

mysql> redis_backup_result から backup_date 、count(*) piece_no を選択し、backup_date でグループ化します。

+-------------+-----------+

| バックアップ日付 | ピース番号 |

+-------------+-----------+

| 2018-08-14 | 3 |

| 2018-08-15 | 121 |

| 2018-08-16 | 184 |

| 2018-08-17 | 3284 |

| 2018-08-18 | 7272 |

| 2018-08-19 | 7272 |

| 2018-08-20 | 7272 |

| 2018-08-21 | 7272 |

| 2018-08-22 | 8226 |

+-------------+-----------+

セット内の 9 行 (0.06 秒)

しかし、解析ロジックについては興味があります。SQL は最初の行を解析し、count(*) 演算を出力しているようです。明らかに、これは実行プランからは取得できない情報です。

考え方を変えると、このテーブルには 40,000 件を超えるレコードがあることがわかります。

mysql> redis_backup_result から count(*) を選択します。

+----------+

| カウント(*) |

+----------+

|40944|

+----------+

セット内の1行（0.01秒）

検証のために、_rowid を使用して予備検証を行うことができます。

InnoDB テーブルは、デフォルトの主キーがない場合、自動的に増加する 6 バイトの主キーを生成します。次のように、select _rowid from table を使用してクエリを実行できます。

mysql> redis_backup_result から _rowid を選択し、制限 5 を設定します。

+--------+

| _rowid |

+--------+

| 117 |

| 118 |

| 119 |

| 120 |

| 121 |

+--------+

セット内の行数は 5 です (0.00 秒)

そうすれば、予備的なアイデアを実現することができます。

mysql> redis_backup_result から _rowid,count(*) を選択します。

+--------+-----------+

| _rowid | カウント(*) |

+--------+-----------+

| 117 | 41036 |

+--------+-----------+

セット内の1行（0.03秒）

次に、rownum を使用してさらに改善することができます。もちろん、この機能は MySQL ではネイティブにサポートされていないため、間接的に実装する必要があります。

mysql> redis_backup_result から rowno,r._rowid として @rowno:=@rowno+1 を選択

r 、(@rowno:=0 を選択) t 制限 20;

+-------+--------+

| 行番号 | _rowid |

+-------+--------+

| 1 | 117 |

| 2 | 118 |

| 3 | 119 |

| 4 | 120 |

| 5 | 121 |

| 6 | 122 |

| 7 | 123 |

| 8 | 124 |

| 9 | 125 |

| 10 | 126 |

| 11 | 127 |

| 12 | 128 |

| 13 | 129 |

| 14 | 130 |

| 15 | 131 |

| 16 | 132 |

| 17 | 133 |

| 18 | 134 |

| 19 | 135 |

| 20 | 136 |

+-------+--------+

セット内の行数は 20 です (0.00 秒)

完全な文を次のように書きます。

mysql> SELECT @rowno:=@rowno+1 を rowno,r._rowid,backup_date,count(*) として選択します

redis_backup_result r から、(@rowno:=0 を選択) t ;

+-------+---------+------------+-----------+

| 行番号 | _rowid | バックアップ日付 | カウント(*) |

+-------+---------+------------+-----------+

| 1 | 117 | 2018-08-14 | 41061 |

+-------+---------+------------+-----------+

セット内の1行（0.02秒）

この場合、1 行目のレコードであることは明らかであり、count(*) 操作が実行されます。

もちろん、私たちの目標は、ROWID と主キーの関係の一部を理解することなので、主キーの使用における隠れた危険性についても確認します。

質問 2: ROWID と主キーの関係は何ですか?

MySQL 開発仕様のインデックス仕様を研究する際に、1 つの重要なポイントが強調されました。それは、各テーブルに主キーがあることが推奨されるということです。ここでその理由を簡単に分析してみましょう。

仕様に加えて、保存方法の観点から、InnoDB ストレージ エンジンでは、テーブルは主キーの順序で保存され、これをクラスター化インデックス テーブルまたはインデックス構成テーブル (IOT) と呼びます。テーブル内の主キーの参照基準は次のとおりです。

  • 主キーを明示的に作成します。
  • テーブル内に空でない一意のインデックスがあるかどうかを確認します。ある場合、それが主キーです。
  • 上記の条件のいずれも満たされない場合は、6 バイトの bigint 符号なし値が生成されます。

上記から、MySQL には主キーのメンテナンス メカニズムがあり、一部の一般的なインデックスにも対応する影響があることがわかります。たとえば、一意のインデックス、一意でないインデックス、カバー インデックスなどはすべて補助インデックス (セカンダリ インデックス、セカンダリ インデックスとも呼ばれます) です。ストレージの観点から見ると、セカンダリ インデックス列にはデフォルトで主キー列が含まれます。主キーが長すぎると、セカンダリ インデックスも多くのスペースを占有します。

質問 3: 主キーの使用における隠れた危険性は何ですか?

これにより、業界では非常に一般的な主要なパフォーマンスの問題が浮上します。これは単一の問題ではなく、技術的価値とビジネス価値を組み合わせるために、MySQL の方向への継続的な変革が必要です。多くのビジネスで自動増分列が設定されているのを見てきましたが、ほとんどの場合、この自動増分列には実際のビジネス上の意味はありません。主キー列はIDの一意性を保証しますが、ビジネス開発者は主キー自動増分列に基づいて直接クエリを実行できないため、新しいビジネス属性を見つけたり、一連の一意のインデックス、一意でないインデックスなどを追加したりする必要があり、私たちが遵守する仕様とビジネスでの使用方法の間には乖離があります。

別の観点から見ると、主キーに対する私たちの理解は偏っています。主キーは 1 から始まる整数型でなければならないと単純に想定することはできません。ビジネス シナリオと組み合わせて検討する必要があります。たとえば、ID カードは実際には良い例です。ID 番号は、検索と保守のためにいくつかのセクションに分かれています。または、外食時に取得したシリアル番号には特定のビジネス属性が含まれており、これはビジネスの使用を理解するための良い参照になります。

質問4: ROWIDの潜在的なボトルネックを理解し、デバッグして検証する方法

rowid は 6 バイトしかないため、最大値は 2^48 であることがわかっています。したがって、row_id がこの値を超えると、さらに増加し​​ます。この場合、隠れた危険はありますか?

練習せずに話すことは、ただの空論に過ぎません。これを説明するためにテストを行うことができます。

1) インデックスなしでテーブル test_inc を作成します。

テーブル test_inc(id int) を作成します。engine=innodb;

2) ps -ef|grep mysql で対応するプロセス番号を取得し、gdb を使用してデバッグ構成を開始します。覚えておいてください。これはあなた自身のテスト環境である必要があります。

[root@dev01 mysql]# gdb -p 3132 -ex 'p dict_sys->row_id=1' -batch

[新LWP 3192]

[新LWP 3160]

[新LWP 3159]

[新LWP 3158]

[新LWP 3157]

[新LWP 3156]

[新LWP 3155]

[新LWP 3154]

[新LWP 3153]

[新LWP 3152]

[新LWP 3151]

[新LWP 3150]

[新LWP 3149]

[新LWP 3148]

[新LWP 3147]

[新LWP 3144]

[新LWP 3143]

[新LWP 3142]

[新LWP 3141]

[新LWP 3140]

[新LWP 3139]

[新LWP 3138]

[新LWP 3137]

[新LWP 3136]

[新LWP 3135]

[新LWP 3134]

[新LWP 3133]

[libthread_db を使用したスレッドデバッグが有効]

/lib64/libc.so.6 からの poll() で 0x00000031ed8df283

1ドル = 1

3) テーブル作成ステートメントを取得し、テストが期待どおりであることを確認するためにいくつかの基本的なチェックを実行します。

mysql> テーブル test_inc\G の作成を表示します

************************** 1. 行 ****************************

  テーブル: test_inc

テーブルの作成: CREATE TABLE `test_inc` (

 `id` int(11) デフォルト NULL

) エンジン=InnoDB デフォルト文字セット=utf8

セット内の 1 行 (0.00 秒)

4) rowid が増加し続けるようにデータを挿入します。

mysql> test_inc に値(1),(2),(3) を挿入します。

クエリは正常、3 行が影響を受けました (0.08 秒)

記録: 3 重複: 0 警告: 0

5) rowidをリセットして2^48に調整します。

mysql> power(2,48) を選択します。

+-----------------+

| パワー(2,48) |

+-----------------+

|281474976710656 |

+-----------------+

セット内の 1 行 (0.00 秒)

 

[root@dev01 mysql]# gdb -p 3132 -ex 'p dict_sys->row_id=281474976710656' -batch

。 。 。

。 。 。

[libthread_db を使用したスレッドデバッグが有効]

/lib64/libc.so.6 からの poll() で 0x00000031ed8df283

1ドル = 281474976710656

6) 引き続きデータを書き込んでいきます。たとえば、4、5、6 の 3 行のデータを書き込みます。

mysql> test_inc に値(4),(5),(6) を挿入します。 

クエリは正常、3 行が影響を受けました (0.07 秒)

記録: 3 重複: 0 警告: 0

7) データ結果を確認し、行 1 と 2 が上書きされていることを確認します。

mysql> test_inc から * を選択します。

+------+

|id|

+------+

| 4 |

| 5 |

| 6 |

| 3 |

+------+

セット内の 4 行 (0.00 秒)

このことから、ROWID が増分された後も、使用ボトルネックが残っていることがわかります。もちろん、この可能性は非常に低いです。自動増分列の値は 281 兆に達する必要があり、これは非常に大きな数です。機能的な観点からは、重複した値を書き込むというエラーをスローする方が合理的です。

主キーを使用すると、上記のボトルネックは存在しなくなるようです。

>>>> 参考文献

RowidのデバッグはDing Qiのブログを参照しています

https://www.jb51.net/article/172262.htm

要約する

以上がこの記事の全内容です。この記事の内容が皆様の勉強や仕事に何らかの参考学習価値をもたらすことを願います。123WORDPRESS.COM をご愛顧いただき、誠にありがとうございます。

以下もご興味があるかもしれません:
  • Mysql 主キー UUID と自動増分主キーの違いと利点と欠点
  • MySQL が uuid または snowflake id を主キーとして使用することを推奨しない理由の詳細な分析
  • Spring Boot は、MySQL を使用して主キー UUID を実装するために、mybatis を統合します。
  • Python3はMySQLを操作してデータを挿入し、主キーIDの例を返します
  • 複合主キーと複数列インデックスに遭遇した場合の MySQL 行ロックの詳細な説明
  • MySQL の主キーがクエリを高速化するために数値を使用するか UUID を使用するかについての簡単な分析

<<:  非常に詳細な Vue-Router のステップバイステップのチュートリアル

>>:  CentOS 8.1 で LEMP (Linux+Nginx+MySQL+PHP) 環境を構築する (チュートリアルの詳細)

推薦する

Vueリスナーの使用例の詳細な説明

1つ目はjQueryのajaxを使用してリクエストを送信することです ユーザーが登録するときに、リス...

CSSマウスを画像の上に置いたときにマスクレイヤー効果を追加する実装

まず効果を見てみましょう: マウスを画像の上に移動すると、影の効果とテキスト/アイコンが追加されます...

Vue コンポーネントはどのように解析され、レンダリングされるのでしょうか?

序文この記事では、Vue コンポーネントがどのように解析され、レンダリングされるかを説明します。 V...

Dockerを使用してDjango+MySQL8開発環境をデプロイする方法の詳細な説明

しばらく前にシステムを再インストールしましたが、バックアップを取っていなかったので、コンピューター上...

Vant アップローダーは、画像をアップロードするためのドラッグ アンド ドロップ機能を実装します (カバーとして設定)

効果図は以下のとおりです。 <!DOCTYPE html> <html lang=...

Linux で 1 つのファイルの内容を別のファイルの末尾にコピーする

問題の説明:たとえば、ファイル 11 の内容は次のとおりです。こんにちはファイル22の内容は次のとお...

CSS3 を使用した背景ぼかし効果の 3 つの例

導入から始めず、いきなり本題に入りましょう。通常の背景ぼかし効果は次のとおりです。 プロパティを使用...

MySQL パスワードに特殊文字が含まれている場合とコマンドラインからログインする場合

サーバーでは、データベースにすばやくログインするために、通常は mysql -hhost -uuse...

Vue ルーティング this.route.push ジャンプ ページが更新されない場合の解決策

Vue ルーティング this.route.push ジャンプ ページが更新されない1. 背景概要:...

VUEプロジェクトでXSS攻撃に遭遇した実体験

目次序文原因を発見するカスタムフィルタリングルール要約する序文インターネットの急速な発展に伴い、情報...

HTML で Flash を読み込む方法 (2 つの実装方法)

最初の方法: CSSコード:コードをコピーコードは次のとおりです。 .b970-a{幅:970px;...

CSS3の3D効果を使って立方体を作成する

CSS3 の 3D 効果を使用して立方体を作成する方法を学ぶと、3D シーンの回転と変位のプロパティ...

Centos7 での MySQL 5.7.20 のインストールと設定に関する詳細なチュートリアル

1. 公式 Web サイトから MySQL 5.7 インストール パッケージ (mysql-5.7....

VMware 15.5 バージョンのインストール Windows_Server_2008_R2 システム チュートリアル図

1. VMware 15.5から新しい仮想マシンを作成する1. VMware を開き、ホームページで...

JavaScript イベント委任の原則

目次1. イベント委任とは何ですか? 2. イベント委任の原則3. イベント委託の役割1. イベント...