利用索引优化查询

        听说索引能优化查询，今天我们尝试一次，毕竟自己动手印象更深刻，还是先创建一个表。

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。实际测试索引能优化查询。