索引的作用

加了索引后，我那一千万条数据的表，查找起来都0.0x sec。

现在去掉索引看看：

====================================================================================

mysql> show index from tb_2;
+-------+------------+---------------+--------------+------------
| Table | Non_unique | Key_name      | Seq_in_index | Column_name
+-------+------------+---------------+--------------+------------
| tb_2  |          0 | PRIMARY       |            1 | id
| tb_2  |          1 | last_first    |            1 | lastName
| tb_2  |          1 | last_first    |            2 | firstName
| tb_2  |          1 | birthdayIndex |            1 | birthday
+-------+------------+---------------+--------------+------------
4 rows in set (0.11 sec)


mysql> drop index  last_first on tb_2;
Query OK, 10000000 rows affected (3 min 52.19 sec)
Records: 10000000  Duplicates: 0  Warnings: 0


mysql> drop index  birthdayIndex on tb_2;
Query OK, 10000000 rows affected (2 min 52.42 sec)
Records: 10000000  Duplicates: 0  Warnings: 0


mysql> select * from tb_2 where firstName='' AND lastName='';
Empty set (8.44 sec)


mysql> select * from tb_2 where firstName='' AND lastName='';
Empty set (6.66 sec)


mysql> select * from tb_2 where firstName='' AND lastName='';
Empty set (6.67 sec)


mysql> select * from tb_2 where firstName='ml' AND lastName='w';
+---------+-----------+----------+------------+------+
| id      | firstName | lastName | birthday   | sex  |
+---------+-----------+----------+------------+------+
| 1313635 | ml        | w        | 1915-02-09 |    1 |
| 3047002 | ml        | w        | 1935-01-09 |    1 |
| 3098465 | ml        | w        | 1935-08-14 |    1 |
+---------+-----------+----------+------------+------+
3 rows in set (6.67 sec)


mysql> select * from tb_2 where firstName='ml' AND lastName='w';
+---------+-----------+----------+------------+------+
| id      | firstName | lastName | birthday   | sex  |
+---------+-----------+----------+------------+------+
| 1313635 | ml        | w        | 1915-02-09 |    1 |
| 3047002 | ml        | w        | 1935-01-09 |    1 |
| 3098465 | ml        | w        | 1935-08-14 |    1 |
+---------+-----------+----------+------------+------+
3 rows in set (6.67 sec)


mysql> select * from tb_2 where birthday='2000-02-29' limit 3;
+---------+-----------+---------------+------------+------+
| id      | firstName | lastName      | birthday   | sex  |
+---------+-----------+---------------+------------+------+
| 8708260 | pmrbxlif  | lclsbyukdbiuj | 2000-02-29 |    0 |
| 8708261 | asw       | s             | 2000-02-29 |    1 |
| 8708262 | pwaxnalm  | otp           | 2000-02-29 |    1 |
+---------+-----------+---------------+------------+------+
3 rows in set (5.86 sec)


mysql> select * from tb_2 where birthday='2000-02-29' limit 3;
+---------+-----------+---------------+------------+------+
| id      | firstName | lastName      | birthday   | sex  |
+---------+-----------+---------------+------------+------+
| 8708260 | pmrbxlif  | lclsbyukdbiuj | 2000-02-29 |    0 |
| 8708261 | asw       | s             | 2000-02-29 |    1 |
| 8708262 | pwaxnalm  | otp           | 2000-02-29 |    1 |
+---------+-----------+---------------+------------+------+

3 rows in set (5.86 sec)