索引(一)

前言:

   在mysql生成千万级的测试数据这一章节中,我们生成了一张千万级的数据表.

   索引的优化对于数据库查询优化而言是最有效的手段.

   为了验证索引优化的威力,我们做了下面一个demo.

1.表未添加索引之前的效果  

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

mysql> select * from card where card_id=9999999;
+---------+--------------------------------------+
| card_id | card_number                          |
+---------+--------------------------------------+
| 9999999 | 268bfe6a-a0e7-11e7-bd81-078eb4dd3572 |
+---------+--------------------------------------+

1 row in set (10.07 sec)

2.为数据表添加普通索引

mysql> alter table card add index(card_id);
Query OK, 0 rows affected, 2 warnings (1 min 1.84 sec)
Records: 0  Duplicates: 0  Warnings: 2


mysql> explain select * from card where card_id=9999999;
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | card  | ref  | card_id       | card_id | 8       | const |    1 | NULL  |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------+

1 row in set (0.00 sec)

3.测试添加索引之后的效果

mysql> select * from card where card_id=9999999;
+---------+--------------------------------------+
| card_id | card_number                          |
+---------+--------------------------------------+
| 9999999 | 268bfe6a-a0e7-11e7-bd81-078eb4dd3572 |
+---------+--------------------------------------+

1 row in set (0.00 sec)

总结:添加索引之后表的查询速度从10.07sec直接提升到了0.00sec.查询速度提升明显。

对于中大型的表,索引是非常有效的.

正文:

几种常见的索引:

1.普通索引,为最基本的索引,没有任何限制.

2.唯一索引,索引列的值必须唯一,允许有空值.

3.主键索引,不允许为空值.

4.组合索引.一个索引包含了多个列.

下面有一个组合索引的demo

表未添加组合索引之前的效果

mysql> explain select * from card where  card_number='1' and card_name='1';
--------------
explain select * from card where  card_number='1' and card_name='1'
--------------


+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | card  | ALL  | NULL          | NULL | NULL    | NULL | 9703851 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)


mysql>  select * from card where  card_number='1' and card_name='1';
--------------
select * from card where  card_number='1' and card_name='1'
--------------


+---------+-------------+-----------+
| card_id | card_number | card_name |
+---------+-------------+-----------+
|       1 | 1           | 1         |
+---------+-------------+-----------+
1 row in set (11.04 sec)

为表添加组合索引

mysql> alter table card add index(card_number,card_name);
--------------
alter table card add index(card_number,card_name)
--------------


Query OK, 0 rows affected, 2 warnings (1 min 29.04 sec)

Records: 0  Duplicates: 0  Warnings: 2

测试添加组合索引后的查询效率

mysql> explain select * from card where card_number='1' and card_name='1';
--------------
explain select * from card where card_number='1' and card_name='1'
--------------


+----+-------------+-------+------+---------------+-------------+---------+-------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref         | rows | Extra                 |
+----+-------------+-------+------+---------------+-------------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | card  | ref  | card_number   | card_number | 366     | const,const |    1 | Using index condition |
+----+-------------+-------+------+---------------+-------------+---------+-------------+------+-----------------------+
1 row in set (0.00 sec)


mysql>  select * from card where card_number='1' and card_name='1';
--------------
select * from card where card_number='1' and card_name='1'
--------------


+---------+-------------+-----------+
| card_id | card_number | card_name |
+---------+-------------+-----------+
|       1 | 1           | 1         |
+---------+-------------+-----------+
1 row in set (0.00 sec)

总结:可以看到组合索引的效果非常之明显.