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

推薦する

Debian システムでの自動パッケージ更新の問題を解決する方法

いつから始まったのかはわかりませんが、コンピュータの電源を入れてインターネットに接続するたびに、デー...

Linux で Xfce デスクトップ環境を使用すべき 8 つの理由

いくつかの理由（好奇心も含む）から、数週間前に Linux デスクトップとして Xfce を使い始め...

初心者がdockerにmysqlをインストールするときに遭遇するさまざまな問題

序文最近、パソコンのシャットダウンに時間がかかることが多く、強制的にシャットダウンするには電源ボタン...

デザインスキルを向上させる良い方法

いわゆる才能（左脳と右脳）つまり、芸術的な才能があるかどうかは、人間の左脳と右脳の分業によって主に決...

Dockerイメージサイズを最適化する一般的な方法

通常、私たちが構築する Docker イメージはサイズが大きく、多くのディスク領域を占有します。コン...

JSONデータをHTMLで表示する方法

背景：場合によっては、json データをページに直接表示する必要があります (たとえば、インターフェ...

Dreamweaver で Zen コーディングを使用する方法

前回の記事「Zen Coding: HTML/CSS コードを素早く記述する方法」を公開した後、一部...

JavaScript カウントダウン プロンプト ボックス

この記事の例では、カウントダウンプロンプトボックスを実装するためのJavaScriptの具体的なコー...

jQuery の CSS スタイル属性 css() と width() の完全ガイド

目次1. css() の基本的な使用法: 1.1 CSSプロパティを取得する1.2 CSSプロパティ...

HTML タグのカスタム属性に関する質問

以前の開発では、クラス、名前などの HTML のデフォルト属性を使用していました。 Huawei社の...

Vue ライフサイクルの紹介とフック関数の詳細な説明

目次Vueライフサイクルの紹介とフック機能VUEライフサイクルフックVue ライフサイクルの紹介作成...

初心者がHTMLタグを学ぶ（1）

初心者は、いくつかの HTML タグを理解することで HTML を学習できます。この入門書は、初心者...

MySQL 8.0 における MySQL のインストールと新しいパスワード認証方法の詳細な説明

1. はじめにOracle が MySQL 8.0GA をリリースしました。海外での GA はリリー...

Linux ファイアウォール設定の詳細な手順 (yum ウェアハウス設定に基づく)

序文この実験では、デバッグ用に2つの仮想マシン（CentOs6とRed Hat 6）を準備します。 ...

React ページ ターナーの実装 (フロント エンドとバックエンドを含む)

目次フロントエンド上記のアイデアに従って、ページめくり機能を設計して記述します。バックエンド（Jav...