MySQLクエリのパフォーマンスに影響を与える大きなオフセットの理由と最適化の詳細な説明

テーブル「メンバー」を作成（
 `id` int(10) 符号なし NOT NULL AUTO_INCREMENT,
 `name` varchar(10) NOT NULL COMMENT '名前',
 `gender` tinyint(3) unsigned NOT NULL COMMENT '性別',
 主キー (`id`)、
 キー `gender` (`gender`)
)ENGINE=InnoDB デフォルト文字セット=utf8;

<?php
$pdo = 新しい PDO ("mysql:host=localhost;dbname=user","root",'');

($i=0; $i<1000000; $i++){
 $name = substr(md5(time().mt_rand(000,999)),0,10);
 性別 = mt_rand(1,2);
 $sqlstr = "メンバー(名前、性別)に値('".$name."','".$gender."')を挿入します";
 $stmt = $pdo->prepare($sqlstr);
 $stmt->execute();
}
?>

mysql> メンバーから count(*) を選択します。
+----------+
| カウント(*) |
+----------+
| 1000000 |
+----------+
セット内1列（0.23秒）

mysql> バージョンを選択します();
+-----------+
| バージョン() |
+-----------+
| 5.6.24 |
+-----------+
セット内の1行（0.01秒）

mysql> メンバーから * を選択し、性別 = 1 を制限 10,1 にします。
+----+------------+--------+
| ID | 名前 | 性別 |
+----+------------+--------+
| 26 | 509e279687 | 1 |
+----+------------+--------+
セット内の 1 行 (0.00 秒)

mysql> メンバーから * を選択して、性別 = 1 の制限を 100,1 にします。
+-----+------------+--------+
| ID | 名前 | 性別 |
+-----+------------+--------+
| 211 | 07c4cbca3a | 1 |
+-----+------------+--------+
セット内の 1 行 (0.00 秒)

mysql> メンバーから * を選択して、性別 = 1 の制限を 1000,1 にします。
+------+------------+---------+
| ID | 名前 | 性別 |
+------+------------+---------+
| 1975 | e95b8b6ca1 | 1 |
+------+------------+---------+
セット内の 1 行 (0.00 秒)

mysql> メンバーから * を選択して、性別 = 1 の制限を 100000,1 にします。
+--------+------------+---------+
| ID | 名前 | 性別 |
+--------+------------+---------+
| 199798 | 540db8c5bc | 1 |
+--------+------------+---------+
セット内1列（0.12秒）

mysql> メンバーから * を選択し、性別 = 1 を制限 200000,1 にします。
+--------+------------+---------+
| ID | 名前 | 性別 |
+--------+------------+---------+
| 399649 | 0b21fec4c6 | 1 |
+--------+------------+---------+
セット内1列（0.23秒）

mysql> メンバーから * を選択して、性別 = 1 の制限を 300000,1 にします。
+--------+------------+---------+
| ID | 名前 | 性別 |
+--------+------------+---------+
| 599465 | f48375bdb8 | 1 |
+--------+------------+---------+
セット内1列（0.31秒）

性別=1 の制限 300000,1 のメンバーから * を選択します。

mysql> メンバーから ID を選択し、性別を 1 に制限します (300000,1)。
+--------+
|id|
+--------+
|599465|
+--------+
セット内の1行（0.09秒）

mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('primary','gender') and TABLE_NAME like '%member%' group by index_name;
空セット (0.04 秒)

mysql> メンバーから * を選択して、性別 = 1 の制限を 300000,1 にします。
+--------+------------+---------+
| ID | 名前 | 性別 |
+--------+------------+---------+
| 599465 | f48375bdb8 | 1 |
+--------+------------+---------+
セット内1列（0.38秒）

mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('primary','gender') and TABLE_NAME like '%member%' group by index_name;
+------------+-----------+
| インデックス名 | カウント(*) |
+------------+-----------+
| 性別 | 261 |
| プライマリ | 1385 |
+------------+-----------+
セットに2行（0.06秒）

mysql> メンバーから ID を選択し、性別を 1 に制限します (300000,1)。
+--------+
|id|
+--------+
|599465|
+--------+
セット内の1行（0.08秒）

mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('primary','gender') and TABLE_NAME like '%member%' group by index_name;
+------------+-----------+
| インデックス名 | カウント(*) |
+------------+-----------+
| 性別 | 263 |
| プライマリー | 13 |
+------------+-----------+
セットに2行（0.04秒）

mysql> メンバーから a.* を内部結合として選択します (メンバーから id を選択し、性別 = 1、制限 300000,1)、b として a.id = b.id で選択します。
+--------+------------+---------+
| ID | 名前 | 性別 |
+--------+------------+---------+
| 599465 | f48375bdb8 | 1 |
+--------+------------+---------+
セット内の1行（0.08秒）

選択
  キーワード
から
  キーワードランク
どこ
  広告id='59'
並び替え
  キーワード
制限 2 オフセット 1;

選択
  キーワード
から
  キーワードランク
どこ
  広告id='59'
注文する
  キーワード
制限 2,1;