当数据库的数据量很大时,为了能够大大提高查询效率。特别是当数据量非常大,查询涉及多个表时,使用索引往往能使查询速度加快成千上万倍。
索引就像书的目录,当表中的记录有成千上万条时,而对数据库的最频繁操作就是查询,在没有设置索引的情况下,每次查询操作都会从表的第一条记录开始查询,直到查到为止;而设置了索引后,就如书本的目录,可以很方便的查询到符合的记录。
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不仅要把改动数据写入数据文件,而且它还要把这些改动写入索引文件。
索引就像书的目录,当表中的记录有成千上万条时,而对数据库的最频繁操作就是查询,在没有设置索引的情况下,每次查询操作都会从表的第一条记录开始查询,直到查到为止;而设置了索引后,就如书本的目录,可以很方便的查询到符合的记录。
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不仅要把改动数据写入数据文件,而且它还要把这些改动写入索引文件。