Mysql的索引应用

当数据库的数据量很大时，为了能够大大提高查询效率。特别是当数据量非常大，查询涉及多个表时，使用索引往往能使查询速度加快成千上万倍。

索引就像书的目录，当表中的记录有成千上万条时，而对数据库的最频繁操作就是查询，在没有设置索引的情况下，每次查询操作都会从表的第一条记录开始查询，直到查到为止；而设置了索引后，就如书本的目录，可以很方便的查询到符合的记录。


1.
索引的创建


在创建数据表时可以创建索引，也可以单独用create index或alter table语句来增加索引

方法1：

alter table语句

此语句用来创建普通索引、UNIQUE索引（唯一性索引）或PRIMARY KEY索引（也是唯一性索引）。

格式：

alter table 表名 add index 索引名(索引字段1,索引字段2…);

例如：将commodity表的name字段设置索引，索引名为idx_name；操作如下：

mysql> alter table commodity add index idx_name(name);

Query OK, 5 rows affected (0.15 sec)

Records: 5 Duplicates: 0 Warnings: 0


2.
查看表的索引


mysql> show index from commodity;

+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| commodity | 0 | PRIMARY | 1 | ID | A | 5 | NULL | NULL | | BTREE | |

| commodity | 1 | idx_name | 1 | name | A | NULL | NULL | NULL | | BTREE | |

+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

2 rows in set (0.00 sec)




方法2：create index语句

此语句可对表增加普通索引或UNIQUE索引。

格式：

create index 索引名 on 表名(索引字段1,索引字段2…);

mysql> show index from commodity;

+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| commodity | 0 | PRIMARY | 1 | ID | A | 5 | NULL | NULL | | BTREE | |

| commodity | 1 | idx_name | 1 | name | A | NULL | NULL | NULL | | BTREE | |

+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

2 rows in set (0.00 sec)




其中，ID为主键；name为普通索引。







3．删除索引



删除表的索引的命令与格式如下：

格式：

drop index 索引名 on 表名；

例如：

mysql> drop index idx_name on commodity;

Query OK, 5 rows affected (0.11 sec)

Records: 5 Duplicates: 0 Warnings: 0



mysql> show index from commodity;

+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| commodity | 0 | PRIMARY | 1 | ID | A | 5 | NULL | NULL | | BTREE | |

+-----------+------------+----------+--------------+-------------+-----------+-------------+--------







4、索引的应用




在没有设置索引时，查看name列。如下：

mysql> select name from commodity;

+-----------+

| name |

+-----------+

| 酸奶 |

| 酸奶 |

| 牙刷 |

| 圆珠笔 |

| 上好佳 |

+-----------+

5 rows in set (0.00 sec)



mysql> create index idx_name on commodity(name);

Query OK, 5 rows affected (0.08 sec)

Records: 5 Duplicates: 0 Warnings: 0

给name 字段设置索引后，如下：

mysql> select name from commodity;

+-----------+

| name |

+-----------+

| 牙刷 |

| 酸奶 |

| 酸奶 |

| 圆珠笔 |

| 上好佳 |

+-----------+

5 rows in set (0.00 sec)




再如：

mysql> select ID from commodity where name='圆珠笔';

+-----------+

| ID |

+-----------+

| 110500001 |

+-----------+

1 row in set (0.00 sec)




当然，在表中记录不多的情况下是不需要索引的。只有在记录成千上万的情况下，设置索引，然后，select查询时会快速的定位，就如上，假如，表commodity中有10000条记录时，而”圆珠笔”在第9999条记录，在没有设置索引的情况下，从第一条记录开始一直到第9999条为止，而设置了索引后，可以直接定位于第9999条。







索引的缺点

　　到目前为止，我们讨论的都是索引的优点。事实上，索引也是有缺点的。
　　首先，索引要占用磁盘空间。通常情况下，这个问题不是很突出。但是，如果你创建每一种可能列组合的索引，索引文件体积的增长速度将远远超过数据文件。如果你有一个很大的表，索引文件的大小可能达到操作系统允许的最大文件限制。
　　第二，对于需要写入数据的操作，比如DELETE、UPDATE以及INSERT操作，索引会降低它们的速度。这是因为MySQL不仅要把改动数据写入数据文件，而且它还要把这些改动写入索引文件。