Created
October 20, 2019 12:46
-
-
Save yudoufu/65752ef2399bcd04b898e51c41e8947d to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
$rows = 10 * 10000; | |
$host = '127.0.0.1'; | |
$dbname = 'test'; | |
$username = 'root'; | |
$password = ''; | |
$dsn = "mysql:dbname=$dbname;host=$host;charset=utf8mb4"; | |
try { | |
$pdo = new PDO($dsn, $username, $password); | |
$pdo->exec('DROP TABLE IF EXISTS secondry_test'); | |
$pdo->exec('CREATE TABLE secondry_test ( | |
id INT NOT NULL AUTO_INCREMENT, | |
user_id INT NOT NULL, | |
deliver_id INT NOT NULL, | |
flag TINYINT NOT NULL, | |
message VARCHAR(255) NOT NULL, | |
delivered_at DATETIME default NULL, | |
created_at DATETIME NOT NULL, | |
updated_at DATETIME NOT NULL, | |
PRIMARY KEY (id), | |
KEY `user_deliverd` (user_id, delivered_at, deliver_id, message), | |
KEY `user_flag` (user_id, flag, deliver_id, message) | |
) ENGINE=InnoDB'); | |
$stmt = $pdo->prepare('INSERT INTO secondry_test (user_id, deliver_id, flag, message, delivered_at, created_at, updated_at) VALUES (?, ?, ?, ?, ?, NOW(), NOW())'); | |
for ($i = 1; $i <= $rows; $i++) { | |
$stmt->execute([ | |
mt_rand(1, 100), | |
mt_rand(1, 100), | |
(mt_rand(0, 10) / 5 ? 0 : 1), | |
sha1(mt_rand(0, 100 * 10000)), | |
mt_rand(0, 10) / 5 ? NULL : date("Y-m-d H:i:s"), | |
]); | |
if ($i % 10000 === 0) { | |
echo $i . "\n"; | |
} | |
} | |
echo 'done'; | |
} catch (PDOException $e) { | |
var_dump($e); | |
} | |
クエリのサンプルデータにするのはこの辺
mysql> SELECT * FROM secondry_test WHERE delivered_at IS NOT NULL AND flag = 1 AND user_id = 50;
+-------+---------+------------+------+------------------------------------------+---------------------+---------------------+---------------------+
| id | user_id | deliver_id | flag | message | delivered_at | created_at | updated_at |
+-------+---------+------------+------+------------------------------------------+---------------------+---------------------+---------------------+
| 84817 | 50 | 18 | 1 | cd4cee5e8e6fed721b7cdb8033925319cadc10af | 2019-10-20 12:29:03 | 2019-10-20 21:29:03 | 2019-10-20 21:29:03 |
| 19460 | 50 | 31 | 1 | 8ff174fd318c69fc7f61639ba7f342c81e42eb54 | 2019-10-20 12:28:17 | 2019-10-20 21:28:17 | 2019-10-20 21:28:17 |
| 19764 | 50 | 32 | 1 | 5b2ac39f346815716da8d60568179c6142dcc8e0 | 2019-10-20 12:28:17 | 2019-10-20 21:28:17 | 2019-10-20 21:28:17 |
| 98705 | 50 | 41 | 1 | f5f82fa4a84e00cec955b5918c54f29de215a688 | 2019-10-20 12:29:13 | 2019-10-20 21:29:13 | 2019-10-20 21:29:13 |
| 66410 | 50 | 58 | 1 | f4767f8e2e0067669aac67c331dc09c4c4c224ac | 2019-10-20 12:28:50 | 2019-10-20 21:28:50 | 2019-10-20 21:28:50 |
| 88719 | 50 | 61 | 1 | ac43032dff3e1bef3de1e8873bc1f8b8d4d5069f | 2019-10-20 12:29:06 | 2019-10-20 21:29:06 | 2019-10-20 21:29:06 |
| 98305 | 50 | 65 | 1 | 5bd75e0f9ed56a00401d605378ea4d7815f413c0 | 2019-10-20 12:29:13 | 2019-10-20 21:29:13 | 2019-10-20 21:29:13 |
| 39209 | 50 | 78 | 1 | 9dbb9d54b02cfdcfde1dea03061e6d1abdbc5245 | 2019-10-20 12:28:32 | 2019-10-20 21:28:32 | 2019-10-20 21:28:32 |
| 31601 | 50 | 81 | 1 | c065dfab39844b097c25b1f75614545d6aa761f3 | 2019-10-20 12:28:26 | 2019-10-20 21:28:26 | 2019-10-20 21:28:26 |
| 63348 | 50 | 88 | 1 | cf3ff06d58878dd4af906672daf487ab6fd7c848 | 2019-10-20 12:28:48 | 2019-10-20 21:28:48 | 2019-10-20 21:28:48 |
| 53249 | 50 | 91 | 1 | c7d55ad57a878bd9cf88eed0cd3e47ec9be0b38a | 2019-10-20 12:28:42 | 2019-10-20 21:28:42 | 2019-10-20 21:28:42 |
+-------+---------+------------+------+------------------------------------------+---------------------+---------------------+---------------------+
11 rows in set (0.00 sec)
これが全状態あるのでこれを使う
mysql> SELECT * FROM secondry_test WHERE user_id = 50 AND deliver_id = 88;
+-------+---------+------------+------+------------------------------------------+---------------------+---------------------+---------------------+
| id | user_id | deliver_id | flag | message | delivered_at | created_at | updated_at |
+-------+---------+------------+------+------------------------------------------+---------------------+---------------------+---------------------+
| 3522 | 50 | 88 | 0 | 1b2ea7c20b0f53a16d82c127983591200d377456 | NULL | 2019-10-20 21:28:06 | 2019-10-20 21:28:06 |
| 42898 | 50 | 88 | 0 | 38a7be4c75c6d5cdbc5e56db2c641d4829a82f8d | NULL | 2019-10-20 21:28:34 | 2019-10-20 21:28:34 |
| 90778 | 50 | 88 | 0 | 42ab592ac0c2d12d83734e9fe8932545531f3983 | NULL | 2019-10-20 21:29:07 | 2019-10-20 21:29:07 |
| 66136 | 50 | 88 | 0 | 45c00abdae869ffd4868a729566d8020f06be901 | NULL | 2019-10-20 21:28:50 | 2019-10-20 21:28:50 |
| 98133 | 50 | 88 | 0 | 54411cb7569c2949794fbfe9b8ed0f45d71afdf4 | NULL | 2019-10-20 21:29:12 | 2019-10-20 21:29:12 |
| 78607 | 50 | 88 | 0 | 5fc5ecfc6fb632fad78558a744fa07967e4df387 | NULL | 2019-10-20 21:28:59 | 2019-10-20 21:28:59 |
| 35984 | 50 | 88 | 0 | 755748b453793683ec5f2d2ff615f2783d5e639d | NULL | 2019-10-20 21:28:29 | 2019-10-20 21:28:29 |
| 47425 | 50 | 88 | 0 | a36bc9fdb4fcec113148448e67702997293408dd | NULL | 2019-10-20 21:28:37 | 2019-10-20 21:28:37 |
| 66313 | 50 | 88 | 0 | af2cc759cfdb91bdcbc8ab52de02368a16ba45da | NULL | 2019-10-20 21:28:50 | 2019-10-20 21:28:50 |
| 76098 | 50 | 88 | 0 | b0db0e4d28b38e3a7f8bdda4bdb41708e0581427 | NULL | 2019-10-20 21:28:57 | 2019-10-20 21:28:57 |
| 91450 | 50 | 88 | 1 | b9246da7fb3729328ecb33a7dd36a386f922c343 | NULL | 2019-10-20 21:29:08 | 2019-10-20 21:29:08 |
| 8193 | 50 | 88 | 0 | c38a4bd94a6903f097b2231c4a1d28c156abda9d | NULL | 2019-10-20 21:28:09 | 2019-10-20 21:28:09 |
| 70428 | 50 | 88 | 0 | d9107cae659470ad257459fcb39c4f515d1c69fb | NULL | 2019-10-20 21:28:53 | 2019-10-20 21:28:53 |
| 92766 | 50 | 88 | 0 | db0fe23ecd8d0ea572694f61c5643914b2db000d | NULL | 2019-10-20 21:29:09 | 2019-10-20 21:29:09 |
| 28745 | 50 | 88 | 0 | eeda3152334918a75ccd0704ab689819f50d3a15 | NULL | 2019-10-20 21:28:23 | 2019-10-20 21:28:23 |
| 18018 | 50 | 88 | 0 | f18eafe9e85fede2a5d1d73f3f2356e8b248d229 | NULL | 2019-10-20 21:28:16 | 2019-10-20 21:28:16 |
| 53296 | 50 | 88 | 0 | 01a069072c4d4a22ec4456a559c56e2ffbaf6a10 | 2019-10-20 12:28:42 | 2019-10-20 21:28:42 | 2019-10-20 21:28:42 |
| 63348 | 50 | 88 | 1 | cf3ff06d58878dd4af906672daf487ab6fd7c848 | 2019-10-20 12:28:48 | 2019-10-20 21:28:48 | 2019-10-20 21:28:48 |
+-------+---------+------------+------+------------------------------------------+---------------------+---------------------+---------------------+
18 rows in set (0.00 sec)
各パターンのEXPLAIN
delivered_at のIS NULL
mysql> EXPLAIN FORMAT=JSON SELECT user_id,deliver_id FROM secondry_test WHERE user_id = 50 AND deliver_id = 88 AND delivered_at IS NULL;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "2.32"
},
"table": {
"table_name": "secondry_test",
"access_type": "ref",
"possible_keys": [
"user_deliverd",
"user_flag"
],
"key": "user_deliverd",
"used_key_parts": [
"user_id",
"delivered_at",
"deliver_id"
],
"key_length": "14",
"ref": [
"const",
"const",
"const"
],
"rows_examined_per_scan": 16,
"rows_produced_per_join": 16,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "0.72",
"eval_cost": "1.60",
"prefix_cost": "2.32",
"data_read_per_join": "16K"
},
"used_columns": [
"user_id",
"deliver_id",
"delivered_at"
],
"attached_condition": "(`test`.`secondry_test`.`delivered_at` is null)"
}
}
} |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
delivered_at のIS NOT NULL
mysql> EXPLAIN FORMAT=JSON SELECT user_id,deliver_id FROM secondry_test WHERE user_id = 50 AND deliver_id = 88 AND delivered_at IS NOT NULL;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "21.97"
},
"table": {
"table_name": "secondry_test",
"access_type": "range",
"possible_keys": [
"user_deliverd",
"user_flag"
],
"key": "user_deliverd",
"used_key_parts": [
"user_id",
"delivered_at"
],
"key_length": "10",
"rows_examined_per_scan": 94,
"rows_produced_per_join": 9,
"filtered": "10.00",
"using_index": true,
"cost_info": {
"read_cost": "21.03",
"eval_cost": "0.94",
"prefix_cost": "21.97",
"data_read_per_join": "9K"
},
"used_columns": [
"user_id",
"deliver_id",
"delivered_at"
],
"attached_condition": "((`test`.`secondry_test`.`deliver_id` = 88) and (`test`.`secondry_test`.`user_id` = 50) and (`test`.`secondry_test`.`delivered_at` is not null))"
}
}
} |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
flagの0
mysql> EXPLAIN FORMAT=JSON SELECT user_id,deliver_id FROM secondry_test WHERE user_id = 50 AND deliver_id = 88 AND flag = 0;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "2.32"
},
"table": {
"table_name": "secondry_test",
"access_type": "ref",
"possible_keys": [
"user_deliverd",
"user_flag"
],
"key": "user_flag",
"used_key_parts": [
"user_id",
"flag",
"deliver_id"
],
"key_length": "9",
"ref": [
"const",
"const",
"const"
],
"rows_examined_per_scan": 16,
"rows_produced_per_join": 16,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "0.72",
"eval_cost": "1.60",
"prefix_cost": "2.32",
"data_read_per_join": "16K"
},
"used_columns": [
"user_id",
"deliver_id",
"flag"
]
}
}
} |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
flagの1
mysql> EXPLAIN FORMAT=JSON SELECT user_id,deliver_id FROM secondry_test WHERE user_id = 50 AND deliver_id = 88 AND flag = 1;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "0.48"
},
"table": {
"table_name": "secondry_test",
"access_type": "ref",
"possible_keys": [
"user_deliverd",
"user_flag"
],
"key": "user_flag",
"used_key_parts": [
"user_id",
"flag",
"deliver_id"
],
"key_length": "9",
"ref": [
"const",
"const",
"const"
],
"rows_examined_per_scan": 2,
"rows_produced_per_join": 2,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "0.28",
"eval_cost": "0.20",
"prefix_cost": "0.48",
"data_read_per_join": "2K"
},
"used_columns": [
"user_id",
"deliver_id",
"flag"
]
}
}
} |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
着目すべきは used_key_parts
delivered_at のIS NULL
"used_key_parts": [
"user_id",
"delivered_at",
"deliver_id"
],
delivered_atのIS NOT NULL
"used_key_parts": [
"user_id",
"delivered_at"
],
flagの0
"used_key_parts": [
"user_id",
"flag",
"deliver_id"
],
flagの1
"used_key_parts": [
"user_id",
"flag",
"deliver_id"
],
delivered_atのIS NOT NULL だけ deliver_id
を使えていない
なお通常のEXPLAINはこう。
mysql> EXPLAIN SELECT user_id,deliver_id FROM secondry_test WHERE user_id = 50 AND deliver_id = 88 AND delivered_at IS NULL;
+----+-------------+---------------+------------+------+-------------------------+---------------+---------+-------------------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+------+-------------------------+---------------+---------+-------------------+------+----------+--------------------------+
| 1 | SIMPLE | secondry_test | NULL | ref | user_deliverd,user_flag | user_deliverd | 14 | const,const,const | 16 | 100.00 | Using where; Using index |
+----+-------------+---------------+------------+------+-------------------------+---------------+---------+-------------------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT user_id,deliver_id FROM secondry_test WHERE user_id = 50 AND deliver_id = 88 AND delivered_at IS NOT NULL;
+----+-------------+---------------+------------+-------+-------------------------+---------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+-------+-------------------------+---------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | secondry_test | NULL | range | user_deliverd,user_flag | user_deliverd | 10 | NULL | 94 | 10.00 | Using where; Using index |
+----+-------------+---------------+------------+-------+-------------------------+---------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT user_id,deliver_id FROM secondry_test WHERE user_id = 50 AND deliver_id = 88 AND flag = 0;
+----+-------------+---------------+------------+------+-------------------------+-----------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+------+-------------------------+-----------+---------+-------------------+------+----------+-------------+
| 1 | SIMPLE | secondry_test | NULL | ref | user_deliverd,user_flag | user_flag | 9 | const,const,const | 16 | 100.00 | Using index |
+----+-------------+---------------+------------+------+-------------------------+-----------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT user_id,deliver_id FROM secondry_test WHERE user_id = 50 AND deliver_id = 88 AND flag = 1;
+----+-------------+---------------+------------+------+-------------------------+-----------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+------+-------------------------+-----------+---------+-------------------+------+----------+-------------+
| 1 | SIMPLE | secondry_test | NULL | ref | user_deliverd,user_flag | user_flag | 9 | const,const,const | 2 | 100.00 | Using index |
+----+-------------+---------------+------------+------+-------------------------+-----------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
delivered_at IS NOT NULLだけtype=rangeになるのがわかる。
ちなみに、delivered_at IS NULLはUsing whereが出ていてattached_conditionに delivered_at IS NULLをlookupに使っている・・・と書いているのだけど、一方でused_key_partsではdeliver_idまで使えているしrefで解決できていて、ちょっと何を信じていいかわからない。
MySQLのマニュアルを信じるなら IS NULLはindexをつかえる
ということでsecondry indexをlookupするというちょっと変わったアクションなのかもしれない。(想像)
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
cardinalityの状況確認。