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) 環境を構築する (チュートリアルの詳細)

推薦する

CentOS7.4 で JDK1.8 をインストールするためのグラフィカル チュートリアル

Linux インストール JDK1.8 手順1. CentOS に独自の openJDK があるかど...

Node.js のワーカー スレッドの詳細な理解

目次概要Node.js における CPU バウンド アプリケーションの歴史CPUを集中的に使用する操...

MySQL 5.7.18 インストーラーのインストール ダウンロード グラフィック チュートリアル

この記事では、MySQL 5.7.18インストーラーの詳細なインストールチュートリアルを参考までに記...

垂直方向の中央揃えをエレガントに実現する方法を教えます（推奨）

序文CSS で水平方向と垂直方向に中央揃えする方法はたくさんあります。この記事で紹介する方法は非常に...

JavaScript コードを省略する一般的な方法の概要

目次序文矢印関数一般的な配列操作をマスターするスプレッド演算子オブジェクトの省略形構造化割り当てデー...

ウェブサイト標準の検証方法を通じてFlashページを共有する方法

1. 埋め込みは違法です<embed> タグは Netscape のプライベート タグで...

HTML タグのネスト規則の紹介

XHTML タグには、div、ul、li、dl、dt、dd、h1～h6、p、a、addressa、s...

MySQL で lower_case_table_names を記録する際の落とし穴の詳細な説明

1 原因プロジェクトがデータベースを移行して再起動した後、「T_AAAテーブルが存在しません」という...

MySQL で重複行を見つけて削除する方法

目次1. 重複行を見つける方法2. 重複行を削除する方法3. 複数の列で重複を見つける方法4. クエ...

Ubuntu にグラフィック ドライバーが正常にインストールされたかどうかを確認する方法

次のコマンドを実行します: glxinfo | grep レンダリング結果が「はい」の場合、グラフィ...

Vueはショッピングカート決済機能をシミュレートします

この記事では、ショッピングカート決済機能を実現するためのVueの具体的なコードを例として紹介します。...

Vue ルーティング遅延読み込みの詳細

目次1. ルートの遅延読み込みとは何ですか? 2. ルートの遅延読み込みの使用1. ルートの遅延読み...

Nginx http ヘルスチェック構成プロセス分析

パッシブチェックパッシブ ヘルス チェックでは、NGINX と NGINX Plus はイベントの発...

Linux の一般的な Java プログラム起動スクリプトのコード例

シェルを起動する頻度は非常に低いですが。 。 。しかし、書くたびに、多くの jar ファイル パスを...

複数の Docker コンテナが同じポート番号を持たない場合の解決策

背景Dockerでは、同じイメージを使用して4つのコンテナを作成します。ネットワークはブリッジモード...