インデックスによるクエリーの最適化

インデックスはクエリーを最適化できると聞いていますが、今日は自分が手を出したほうが印象的なので、まずテーブルを作成してみましょう.

mysql> create table test(
    -> id int not null,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+-----------------+
| Tables_in_mapan |
+-----------------+
| student         |
| test            |
| user            |
+-----------------+
3 rows in set (0.00 sec)

mysql> insert into test value
    -> (12,"zhangsan"),(9,"lisi"),(25,"wangwu"),(15,"zhaoliu");
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from test;
+----+----------+
| id | name     |
+----+----------+
| 12 | zhangsan |
|  9 | lisi     |
| 25 | wangwu   |
| 15 | zhaoliu  |
+----+----------+
4 rows in set (0.00 sec)

mysql> show index from test;
Empty set (0.00 sec)

show index from testは、テーブルにインデックスが含まれているかどうかを表示します.
次にクエリを行います.

mysql> explain select * from test where id=25;
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows
 | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-------------+
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    4
 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-------------+
1 row in set (0.00 sec)

type=ALL、フルスキャン、性能は最悪です.次に、テーブルにインデックスを追加し、クエリーを行います.

mysql> alter table test add index(id);
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> show index from test;
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardi
nality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+---------------+
| test  |          1 | id       |            1 | id          | A         |
  NULL |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+---------------+
1 row in set (0.01 sec)
mysql> explain select * from test where id=25;
+----+-------------+-------+------+---------------+------+---------+-------+----
--+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | row
s | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+----
--+-------+
|  1 | SIMPLE      | test  | ref  | id            | id   | 4       | const |
1 |       |
+----+-------------+-------+------+---------------+------+---------+-------+----
--+-------+
1 row in set (0.00 sec)

alter table test add index(id)はidをテーブルtestのインデックスにし、type=refを見ることができ、効率はtype=ALLより高い.実際のテストインデックスは、クエリーを最適化します.