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); | |
| } | |
着目すべきは 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
各パターンのEXPLAIN
delivered_at のIS NULL
delivered_at のIS NOT NULL
flagの0
flagの1