MySQLのindexについてのメモ

mysql> SHOW CREATE TABLE accounts;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| accounts | CREATE TABLE accounts (
id int NOT NULL AUTO_INCREMENT,
branch varchar(255) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW INDEX FROM accounts;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| accounts | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE PROCEDURE create_dummy_accounts;
+-----------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |
+-----------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| create_dummy_accounts | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=root@localhost PROCEDURE create_dummy_accounts()
BEGIN DECLARE i INTEGER DEFAULT 0; START TRANSACTION; WHILE i < 1000000 DO INSERT INTO accounts (branch) VALUES (FLOOR(1+(RAND()*100))); SET i = i + 1; END WHILE; COMMIT;END | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci |
+-----------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

mysql> call create_dummy_accounts();
Query OK, 0 rows affected (16.63 sec)

mysql> SELECT COUNT() FROM accounts;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') FROM accounts' at line 1
mysql> SELECT COUNT() FROM accounts;
+----------+
| COUNT() |
+----------+
| 1000000 |
+----------+
1 row in set (0.04 sec)

mysql> EXPLAIN SELECT * FROM accounts where epoch < 50 LIMIT 10;
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | accounts | NULL | ALL | NULL | NULL | NULL | NULL | 998785 | 33.33 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM accounts where epoch < 50 ORDER BY id LIMIT 10;
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | accounts | NULL | index | NULL | PRIMARY | 4 | NULL | 10 | 33.33 | Using where |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

sync && echo 3 >/proc/sys/vm/drop_caches && systemctl restart mysql

mysql> SELECT * FROM accounts where branch = 50 LIMIT 10;
+-----+--------+---------+
| id | branch | balance |
+-----+--------+---------+
| 17 | 50 | 447372 |
| 108 | 50 | 23868 |
| 145 | 50 | 700808 |
| 390 | 50 | 345857 |
| 393 | 50 | 641430 |
| 473 | 50 | 897262 |
| 535 | 50 | 875737 |
| 565 | 50 | 542684 |
| 617 | 50 | 440247 |
| 645 | 50 | 72158 |
+-----+--------+---------+
10 rows in set (0.05 sec)