MySQL の最適化: ストレージ エンジンとインデックスの最適化についての深い理解

データベース内の 2 つのストレージ エンジンの保存方法を比較します。

MyIsam: よく観察すると、このエンジンを使用するデータベースには通常、**.frm、**.myi、(インデックス) という少なくとも 3 つのファイルが含まれていることがわかります。 **.myd (データの入力) は、インデックス (**.myi ファイル) を介して、データが保存されているデータ ファイル内の行を見つけます。これにより、改行が生成されます。行の戻りがない場合、つまりインデックス カバレッジがなければ、速度は非常に速くなります

InnoDb: これはファイルであり、インデックスとデータが一緒にまとめられています。つまり、クラスター化インデックスの欠点は、ファイルがサイズが大きいと、ディスクが回転し、検索が比較的遅くなります。これにより、ページ分割されたブロック ファイルも生成されます

テーブルの作成: myisam エンジンと innodb エンジンをそれぞれ使用して、主キー インデックスとジョイント インデックスが含まれます

create table smth (
id int auto_increment ,
ver int(11) default null 、
コンテンツ varchar(1000) が null ではありません、
イントロ varchar(1000) が null ではありません、
主キー (id)、
キー IDver(id,ver)

)engine = myisam デフォルト charset = utf8;

テーブル smth1 を作成します (
id int auto_increment 、
ver int(11) デフォルト null、
コンテンツ varchar(1000) not null、
イントロ varchar(1000) not null、
主キー (id)、
キー IDver (id,ver)

)engine = innodb default charset = utf8;
ファイル ディレクトリ分析は上記のとおりです:

次に、10,000 個のデータを挿入する 2 つのストアド プロシージャを作成します

プロシージャ smthTest を作成します( )
開始
num int のデフォルト 1050;
while num set num := num +1;
smth 値に挿入 (num,num,'I am step','who am I') ;
end while ;

end;


プロシージャ smthTest1() を作成
begin
num int のデフォルト 0;
while num set num := num +1;
smth1 値に挿入 ( num ,num ,'I am step','who am I');
end while ;

end;

set profiling = 1; 詳細情報を表示

実行結果分析:

ステートメント分析結果:

mysql>説明します
ID 順に ID、バージョン、コンテンツを選択します。  
+----+-------------+------+----------+-------------- -+------+-----------+------+-----+-----+
| ID |選択タイプ |テーブル |タイプ |可能なキー |キー |キー長 |参照 |行 |おまけ |
+----+-------------+-------+------+------------ ---+------+--------+------+------+------ -+
|  1 |シンプル |素敵 |すべて | NULL | NULL | NULL | NULL | 9946 |ファイルソートの使用 |
+----+-------------+-----------+------+----------- ----+------+-----------+------+------+-------------- --+
セット内の 1 行
mysql>説明します
ID、ver 順に ID、ver、コンテンツを選択してください;
+----+-------------+----------+----- -+--------------+------+----------+------+------+- ---------------+
| ID |選択タイプ |テーブル |タイプ |可能なキー |キー |キー長 |参照 |行 |おまけ |
+----+-------------+-------+------+------------ ---+------+--------+------+------+------ -+
|  1 |シンプル |素敵 |すべて | NULL | NULL | NULL | NULL | 9946 |ファイルソートの使用 |
+----+-------------+-----------+------+----------- ----+------+-----------+------+------+-------------- --+
セット内の 1 行
mysql>説明します
smth1 から ID、バージョン、コンテンツを選択 ID 順に並べます;
+----+-------------+-------+------- +------+-------+-----------+------+------ +------+
| ID |選択タイプ |テーブル |タイプ |可能なキー |キー |キー長 |参照 |行 |おまけ |
+------+---------------+-------+----------+----------- ----+---------+-----------+------+------+----------+
|  1 |シンプル | smth1 |インデックス | NULL |プライマリ | 4 | NULL | 9932 | NULL |
+----+-------------+-----------+-----------+----------- ----+---------+-----------+------+------+-----------+
1 行セット内
mysql>説明 smth1 から id
,ver,content を選択して id,ver;
+----+-------------+----------+----- -+--------------+------+----------+------+------+- ---------------+
| ID |選択タイプ |テーブル |タイプ |可能なキー |キー |キー長 |参照 |行 |おまけ |
+----+-------------+-------+------+------------ ---+------+--------+------+------+------ -+
|  1 |シンプル | smth1 |すべて | NULL | NULL | NULL | NULL | 9932 |ファイルソートの使用 |
+----+-------------+-----------+------+----------- ----+------+-----------+------+------+-------------- --+

mysql>説明します
ID によるスムーズな順序から ID を選択します;
+----+-------------+-------+----------+--- -------------+---------+----------+-----+------+--- ----------+
| ID |選択タイプ |テーブル |タイプ |可能なキー |キー |キー長 |参照 |行 |おまけ |
+----+-------------+-------+-------+----------- ----+---------+-----------+------+------+----------- --+
|  1 |シンプル |素敵 |インデックス | NULL |プライマリ | 4 | NULL | 9946 |インデックスの使用 |
+------+---------------+-------+----------+---------- ----------+-----------+-----------+------+------+---------- ---+
セット内の 1 行
mysql>説明します
ID を順に ID から選択します、ver;
+----+-------------+-------+-------+- ----------+------+----------+------+------+--- ----------+
| ID |選択タイプ |テーブル |タイプ |可能なキー |キー |キー長 |参照 |行 |おまけ |
+----+-------------+-------+-------+----------- ----+----------+--------+------+------+------------- +
|  1 |シンプル |素敵 |インデックス | NULL |イドバー | 9 | NULL | 9946 |インデックスの使用 |
+------+---------------+-------+----------+---------- ----------+----------+----------+------+------+------------ -+
セット内の 1 行
mysql>説明します
smth1 から ID を選択して ID で並べ替えます;
+----+-------------+-------+----------+--- -------------+---------+----------+-----+------+--- ----------+
| ID |選択タイプ |テーブル |タイプ |可能なキー |キー |キー長 |参照 |行 |おまけ |
+----+-------------+-------+-------+----------- ----+-----------+-----------+------+------+----------- --+
|  1 |シンプル | smth1 |インデックス | NULL |プライマリ | 4 | NULL | 9932 |インデックスの使用 |
+------+---------------+-------+----------+---------- ----------+-----------+-----------+------+------+---------- ---+
セット内の 1 行
mysql>説明します
smth1 から ID を選択して ID,ver;
+----+-------------+-------+-------+- ----------+------+----------+------+------+--- ----------+
| ID |選択タイプ |テーブル |タイプ |可能なキー |キー |キー長 |参照 |行 |おまけ |
+----+-------------+-------+-------+----------- ----+----------+--------+------+------+------------- +
|  1 |シンプル | smth1 |インデックス | NULL |イドバー | 9 | NULL | 9932 |インデックスの使用 |
+------+---------------+-------+----------+---------- ----------+----------+----------+------+------+------------ -+
1行セット