mysql-test.php

<?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);
}

なお通常の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するというちょっと変わったアクションなのかもしれない。(想像)