利用主键优化查询

         听别人说mysql可以利用主键优化查询，我们今天来尝试一下,先创建2个表。

包含主键的表t1：

mysql> create table t1(
    -> id int unsigned auto_increment primary key,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.09 sec)

不包含主键的表t2:

mysql> create table t2(
    -> name varchar(20) not null,
    -> score varchar(20));
Query OK, 0 rows affected (0.04 sec)

现在执行一下查询语句：

mysql> explain select * from t1 where id=3;
+----+-------------+-------+-------+---------------+---------+---------+-------+
------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   |
 rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+
------+-------+
|  1 | SIMPLE      | t1    | const | PRIMARY       | PRIMARY | 4       | const |
    1 |       |
+----+-------------+-------+-------+---------------+---------+---------+-------+
------+-------+
1 row in set (0.00 sec)

有主键的其查找对应的type为const。

mysql> explain select * from t2 where name=45;
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows
 | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-------------+
|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |    3
 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-------------+
1 row in set (0.01 sec)

没有主键的其查找对应的type为ALL，ALL是全盘扫描，属于效率最低的一种。

效率：const > All，可见主键可以优化查询。