https://qiita.com/ishizuka215/items/e63621fbd5ec42506385
の続き
①何のインデックスも張っていないテーブル
CREATE TABLE data1(
id INT(11) NOT NULL AUTO_INCREMENT,
status TINYINT(1) NOT NULL,
name VARCHAR(50) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);
insert into data1(name,status)
SELECT CONCAT ('test', @rownum := @rownum + lpad(FLOOR( (RAND() * 100000000)),9,'0')),
FLOOR( (RAND() * 2)) FROM data AS s1, (SELECT @rownum := 0) AS v;
mysql> explain select * from data1 where name = "test000000000";
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | data1 | ALL | NULL | NULL | NULL | NULL | 4193910 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)
②nameにインデックスを張っているテーブル
CREATE TABLE data2(
id INT(11) NOT NULL AUTO_INCREMENT,
status TINYINT(1) NOT NULL,
name VARCHAR(50) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
INDEX idx_1(name)
);
insert into data2(name,status)
SELECT CONCAT ('test', @rownum := @rownum + lpad(FLOOR( (RAND() * 100000000)),9,'0')),
FLOOR( (RAND() * 2)) FROM data AS s1, (SELECT @rownum := 0) AS v;
mysql> explain select * from data2 where name = "test000000000";
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------+
| 1 | SIMPLE | data2 | ref | idx_1 | idx_1 | 52 | const | 1 | Using index condition |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------+
1 row in set (0.02 sec)
③statuu,nameの順番で複合インデックスを張っているテーブル
CREATE TABLE data3(
id INT(11) NOT NULL AUTO_INCREMENT,
status TINYINT(1) NOT NULL,
name VARCHAR(50) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
INDEX idx_1(status,name)
);
insert into data3(name,status)
SELECT CONCAT ('test', @rownum := @rownum + lpad(FLOOR( (RAND() * 100000000)),9,'0')),
FLOOR( (RAND() * 2)) FROM data AS s1, (SELECT @rownum := 0) AS v;
mysql> explain select * from data3 where name = "test000000000";
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | data3 | ALL | NULL | NULL | NULL | NULL | 4183161 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)
インデックスはstatus,nameの順の為、
nameだけ指定した検索ではインデックスが使用されない。
その為
mysql> explain select * from data3 where name = "test000000000" and (status = 0 or status= 1 );
+----+-------------+-------+-------+---------------+-------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-----------------------+
| 1 | SIMPLE | data3 | range | idx_1 | idx_1 | 53 | NULL | 2 | Using index condition |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
不要でも、statusを含め無いとインデックスを使用してくれない。
④name,statusの順で複合インデックスを張っているテーブル
CREATE TABLE data4(
id INT(11) NOT NULL AUTO_INCREMENT,
status TINYINT(1) NOT NULL,
name VARCHAR(50) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
INDEX idx_1(name,status)
);
insert into data4(name,status)
SELECT CONCAT ('test', @rownum := @rownum + lpad(FLOOR( (RAND() * 100000000)),9,'0')),
FLOOR( (RAND() * 2)) FROM data AS s1, (SELECT @rownum := 0) AS v;
mysql> explain select * from data4 where name = "test000000000";
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------+
| 1 | SIMPLE | data4 | ref | idx_1 | idx_1 | 52 | const | 1 | Using index condition |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
⑤status、nameにそれぞれ別のインデックスを張っているテーブル
CREATE TABLE data5(
id INT(11) NOT NULL AUTO_INCREMENT,
status TINYINT(1) NOT NULL,
name VARCHAR(50) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
INDEX idx_1(status),
INDEX idx_2(name)
);
insert into data5(name,status)
SELECT CONCAT ('test', @rownum := @rownum + lpad(FLOOR( (RAND() * 100000000)),9,'0')),
FLOOR( (RAND() * 2)) FROM data AS s1, (SELECT @rownum := 0) AS v;
mysql> explain select * from data5 where name = "test000000000";
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------+
| 1 | SIMPLE | data5 | ref | idx_2 | idx_2 | 52 | const | 1 | Using index condition |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)