Mysql実践ノート(III) 実践編 #MySQL

V. テーブルデータを削除しても、テーブルファイルのサイズが変わらないのはなぜですか？

InnoDB テーブルは、テーブル構造定義とデータの 2 つの部分で構成されます。MySQL 8.0以前では、テーブル構造は拡張子.frmのファイルに格納されていました。MySQL 8.0 では、テーブル構造定義をシステムデータテーブルに置くことができるようになりました。

テーブル・データ

テーブル・データは、共有テーブル・スペースに存在することも、別々のファイルに存在することもできます。innodb_file_per_table この動作はパラメータ：

このパラメータをOFFに設定すると、テーブル・データはシステム共有テーブル・スペース、つまりデータ・ディクショナリと一緒に配置されます；
このパラメータをONに設定すると、各InnoDBテーブルのデータは拡張子.ibdのファイルに保存されます。

MySQL のどのバージョンを使用しているかに関係なく、この値を ON に設定します。これは、ファイルとして個別に保存されたテーブルを管理する方が簡単で、テーブルが不要になったときに drop table コマンドを使用すると、システムが単純にファイルを削除するためです。テーブルが共有テーブルスペースにある場合、テーブルが削除されてもスペースは取り戻されません。

データ削除処理

InnoDB エンジンは、削除するレコードにのみ削除マークを付けます。後でこの場所にレコードが挿入される場合、この場所は再利用される可能性があります。ただし、ディスクファイルのサイズは小さくなりません。

データページ上のすべてのレコードが削除された場合はどうなりますか？答えは、データ・ページ全体を再利用できます。しかし、データページの再利用はレコードの再利用とは異なります。

レコードの再利用は、範囲条件を満たすデータに限られます。例えば上記の例では、ID=400のレコードを削除した後、ID400の行が挿入されれば、そのスペースをそのまま再利用できます。

B+ツリーから全ページが削除された場合は、どの位置でも再利用可能です。

隣接する2つのデータページの利用率が小さい場合、システムはこれら2つのページのデータを1つのページにまとめ、もう1つのデータページは再利用可能としてマークされます。

削除コマンドはレコードの場所またはデータページを「再使用可能」としてマークするだけで、ディスクファイルのサイズは変更されません。つまり、delete コマンドは表領域を再利用しません。

データがインデックスの増分順に挿入される場合、インデックスはコンパクトになります。しかし、データがランダムに挿入されると、インデックスのデータページが分割される可能性があります。

テーブルの再構築

テーブルの追加、削除、変更が大量に行われると、空白が存在する可能性が高くなります。したがって、これらの空隙を除去することができれば、テーブルのスペースを縮小する目的を達成することができます。そして、テーブルの再構築は、そのような目的を達成することができます。

alter table A engine=InnoDBテーブルの再構築に使用される原理は、オンラインDDLです。

テーブルの再構築のプロセス

テーブルAの主キーについてすべてのデータ・ページをスキャンする一時ファイルを作成します；
データページのテーブルAのレコードでB+ツリーを生成し、一時ファイルに保存します；
一時ファイルを生成するプロセスでは、図中の状態2に対応するログファイルに、Aに対するすべての操作が記録されます；
テンポラリファイルが生成された後、ログファイルの操作がテンポラリファイルに適用され、図のstate3に対応する、表Aと同じ論理データのデータファイルが得られます；
テーブルAのデータファイルを一時ファイルに置き換えます。

alter文は、起動時にMDL書き込みロックを取得する必要がありますが、この書き込みロックは、実際にデータをコピーするときに読み取りロックにデグレードします。なぜデグレードするのでしょうか？オンラインを実現するために、MDL読み取りロックは追加、削除、または変更操作をブロックしません。では、なぜロックを解除しないのでしょうか？保護のため、他のスレッドが同時にこのテーブルに対してDDLを行うことは禁止されています。

Online とインプレース

上図では、テーブルAから再構築されたデータは、InnoDBが内部的に作成する一時ファイルである "tmp_file "に置かれます。全てのDDL処理はInnoDB内部で行われます。サーバーレベルでは、テンポラリテーブルにデータが移動されることはなく、"インプレース "操作となります。

では、1TBのテーブルがあり、ディスク間で1.2TBになった場合、インプレースDDLができるでしょうか？答えはノーです。なぜなら、tmp_fileも一時領域を占有するからです。

optimize tableanalyze tableとalter tableの違い

alter table t engine = InnoDBMySQL バージョン 5.6 以降、デフォルトは上の図 4 の処理になります；

analyze table t は実際にはテーブルを再構築するのではなく、データを変更せずにテーブルのインデックス情報を再カウントするだけです；

optimiseテーブルtはrecreate+analyzeと同じです。

delete truncate drop

truncateは内容を削除し、領域を解放しますが、テーブルの構造は削除しません。

drop テーブルの内容と構造を削除し、領域を解放します。

VI. COUNT なぜこんなに遅いのですか？

count(*) の実装は

MyISAMエンジンはテーブルの総行数をディスクに保存しているため、count(*)を実行するとこの数が直接返され、非常に効率的です；

InnoDBエンジンはMyISAMエンジンほど効率的ではなく、エンジンから行ごとにデータを読み出し、カウントを累積します。

なぜInnoDBはMyISAMのように数値を保存しないのでしょうか？これは、同時に複数のクエリを実行しても、InnoDBテーブルは、マルチバージョンの同時実行制御により、"何行を返すべきか "が不明確 だからです。これはInnoDBのトランザクション設計に関係しており、繰り返し読み取りがデフォルトの分離レベルとなっています。そのため、count(*)リクエストでは、InnoDBは行ごとにデータを読み、"このクエリに基づいて "テーブル内の行の総数を計算するために使用する可視行を決定しなければなりません。

InnoDBはインデックスで構成されたテーブルで、プライマリキーインデックスツリーのリーフノードはデータで、通常のインデックスツリーのリーフノードはプライマリキーの値です。そのため、通常のインデックスツリーはプライマリキーインデックスツリーよりもはるかに小さくなります。count(*) のような操作では、どちらのインデックスツリーをトラバースしても論理的には同じ結果になります。したがって、MySQL のオプティマイザはトラバースする最小のツリーを見つけます。

show table status コマンドは、直接使用できない行を表示します。

count(*) カウントの実装

キャッシュによるカウントの保存: redis

データベースへのカウントの保存

count(*)、count(主キーid)、count(フィールド)、count(1)の違い

count(主キーid): InnoDBエンジンは、テーブル全体を走査し、各行のid値を取り出し、サーバ層に返します。サーバ層は、idを取得し、それが空であることはあり得ないと判断し、行ごとに合計します。
count(1)：InnoDBエンジンは、テーブル全体を走査しますが、値を取りません。返された各行に対して、サーバ層は、それが空であることは不可能であると判断し、そこに数字 "1 "を入れ、行ごとに合計します。
count(フィールド)：
1. field "がnullでないと定義されている場合は、レコードからフィールドを1つずつ読み出し、nullでないことを判定して行数を加算します；
2. field "がnullと定義されている場合は、実行時にnullの可能性があると判断しますが、nullでないことを再度判断してから値を取り出して集計します。
count(*)は、すべてのフィールドを取り出すわけではありませんが、特に値を取らないように最適化されています。

VII.オーダーバイはどのように機能するのですか？

完全なフィールド・ソート

select city,name,age from t where city=' ' order by name limit 1000 MySQL は各スレッドに sort_buffer と呼ばれるソート用のメモリブロックを割り当てます。名前によるソートはメモリ内で行われるか、外部ソートを使用する必要があります。ソートに必要なメモリ量と、MySQL がソート用にオープンするメモリのサイズである sort_buffer_size パラメータに依存します。ソートされるデータ量が sort_buffer_size より少ない場合、ソートはメモリ内で行われます。

しかし、ソートするデータ量が大きすぎてメモリに収まらない場合は、ディスク上の一時ファイルを使用してソートを補助する必要があります。外部ソートは一般的にマージソートアルゴリズムを使用します。ソートされるデータはいくつかのコピーに分割され、それぞれが個別にソートされて一時ファイルに保存されます。そして、これらの並べ替えられたファイルは1つの大きな並べ替えファイルにマージされます。

sort_buffer_size が小さいほど、より多くの部分に分割する必要があり、 number_of_tmp_files の値が大きくなります。

rowid

ソートの1行が長すぎると思った場合、MySQLは何をしますか？

フルフィールドソートVS行IDソート

MySQLがソートメモリが小さすぎてソートの効率に影響する ことを懸念する場合、一度に多くの行をソートするrowidソートアルゴリズムを使用 しますが、データを取得するために元のテーブルに戻る必要があります。MySQL は十分なメモリがあると判断した場合、フルフィールドソートを優先し、 必要なフィールドをすべて sort_buffer に格納します 。これはMySQLの設計哲学の1つである、十分なメモリがあるのであれば、ディスクアクセスを最小限にするために、より多くのメモリを使用する必要があるということを反映しています。

オーバーレイ・インデックスを使用することで、ソート・テーブルやテンポラリ・テーブルの使用を防ぐことができます。

alter table t add index city_user_age(city, name, age); ご覧のように、Extraフィールドには "Using index "とあり、これはカバード・インデックスを使用していることを意味し、パフォーマンス的にははるかに高速です。

VIII.ランダムメッセージを正しく表示するには？

メモリ内テンポラリ・テーブル

select word from words order by rand() limit 3; ExtraフィールドにはUsing temporaryと表示されていますが、これは一時テーブルを使用する必要があることを意味し、Using filesortはソートを実行する必要があることを意味します。つまり、このExtraは、一時テーブルが必要であり、一時テーブルでソートを行う必要があることを意味します。

上記のSQLの実行フローです：

一時テーブルを作成します。この一時テーブルは メモリエンジンを 使用し、 2つのフィールドを持ちます。最初のフィールドはdouble型で、後の説明のためにフィールドRと呼び、2番目のフィールドはvarchar(64)型で、フィールドWと呼びます。
単語テーブルから、主キーの順序ですべての単語値を取り出します。各単語値について、rand()関数を呼び出して0より大きく1より小さいランダムな小数を生成し、このランダムな小数と単語をRフィールドとWフィールドの一時テーブルに格納します。
テンポラリテーブルには10,000行のデータがあるので、次に、インデックスを持たないこのメモリ内テンポラリテーブルをフィールドRでソートします。
sort_buffer には2つのフィールドがあり、1つは double 型、もう1つは integer 型です。
一時記憶テーブルからR値と位置情報を行ごとに取り出し、sort_bufferの2つのフィールドに格納します。この処理は一時記憶テーブルのフルテーブルスキャンであり、スキャンされる行数は10,000から20,000行増加します。
Rの値に基づいてsort_bufferでソートします。この処理はテーブル操作を伴わないので、スキャンされる行数は増えないことに注意してください。
ソートが完了すると、最初の3つの結果の位置情報が取り出され、メモリ上の一時テーブルから順番にワード値が取り出されてクライアントに返されます。この処理の間に、テーブルの3行がアクセスされ、スキャンされた行の総数は20003行になります。

遅いクエリーログは

# Query_time: 0. Lock_time: 0. Rows_sent: 3 Rows_examined: 20003
SET timestamp=;
select word from words order by rand() limit 3;

図中のposは位置情報で、「各エンジンがデータ行を一意に識別するために使用する情報」を表します。主キーを持つInnoDBテーブルでは、このrowidが主キーIDになります。

簡単にまとめると、order by rand()はメモリ内テンポラリテーブルを使用し、メモリ内テンポラリテーブルはrowidソートメソッドを使用してソートされます。

ディスク一時テーブル

テンポラリ・テーブル・サイズがtmp_table_sizeを超える場合、メモリ内テンポラリ・テーブルはディスク・テンポラリ・テーブルに変換されます。 internal_tmp_disk_storage_engine ディスク一時テーブルに使用されるデフォルトのエンジンはInnoDBです。

ディスク一時テーブルを使用する場合、これは明示的なインデックスを持たないInnoDBテーブルのソート処理に相当します。

ナイン、SQL文のロジックは同じなのに、パフォーマンスが大きく違うのはなぜですか？

条件付きフィールド関数

すべての年の 7 月に発生したトランザクションレコードの総数を数えます。

select count(*) from tradelog where month(t_modified)=7;

t_modifiedフィールドにはインデックスがありますが、このフィールドで関数計算を行うと、インデックスを使用できません。 where month(t_modified)=7 条件が変更されたときにはインデックスを使用できるのに、なぜ変更されたときには使用できないのでしょうか?インデックスが付けられたフィールドに対して関数演算を行うと、インデックスが付けられた値の順序が破壊される可能性があり、オプティマイザはツリー検索関数に行くことをあきらめることになります。に変更します：

 select count(*) from tradelog 
 where (t_modified >= '' and t_modified<'') 
 or (t_modified >= '' and t_modified<'') 
 or (t_modified >= '' and t_modified<'');

暗黙の型変換

select * from tradelog where tradeid=;

tradeidのフィールド型はvarchar(32)ですが、入力パラメータはintegerなので、型変換が必要です。オプティマイザにとって、このステートメントは以下のものと等価です。

select * from tradelog where CAST(tradid AS signed int) = ;

暗黙の文字エンコーディング変換

文字セットの違いによる問題：トレードIDの違い

select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;

Mysql実践ノート(III) 実践編