索引是一种特殊的文件,包含了对数据表中所有记录的引用指针。InnoDB引擎的数据库,其上的索引是表空间的一个组成部分。
(1).索引的优缺点
优点:加快搜索速度,减少查询时间
缺点:索引是以文件的形式存储,如果索引过多,会占用磁盘较大的空间。而且影响insert、update、delete的执行时间。
索引中的数据必须与数据表中的人数据同步,如果索引过多,当表中数据更新,索引也要同步更新,这就降低了效率。
(2).普通索引
最基本的索引,不具备唯一性,仅加快查询速度。
1)创建普通索引
create table [表名] ([字段名] [字段类型] [字段约束],...,[ index | key ] [索引名称]([字段名称]));
mysql> create table com_index1(id int,name varchar(20),key (name));
Query OK, 0 rows affected (0.02 sec)
mysql> desc com_index1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> create table com_index2(id int,name varchar(20),index com_name(name));
Query OK, 0 rows affected (0.11 sec)
mysql> desc com_index2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.03 sec)
mysql> create table com_index(id int,name varchar(20),index (name));
Query OK, 0 rows affected (0.02 sec)
mysql> desc com_index;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
注意1:索引文件是一表一个,所以在没有外键约束的情况下,只要表内没有重名索引即可。
注意2:在Key列值为MUL的一般是普通索引,可以使用show index from [表名]来确认索引类型,里面有索引类型列如果为BTREE那么就是普通索引。
注意3:如果没有指定索引名,那么默认索引名与字段名一致。
2)添加普通索引
alter table [表名] add [ index | key ] [索引名称]([字段名]);
mysql> alter table com_index1 add key (id);
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc com_index1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | MUL | NULL | |
| name | varchar(20) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> alter table com_index2 add index com_id(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc com_index2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | MUL | NULL | |
| name | varchar(20) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> alter table com_index add index (id);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc com_index;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | MUL | NULL | |
| name | varchar(20) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
3)删除普通索引
alter table [表名] drop [ key | index ] [索引名];
mysql> alter table com_index1 drop key name;
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc com_index1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | MUL | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> alter table com_index1 drop index id;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc com_index1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
(3).唯一索引
设置唯一索引的列中所有值都只能出现一次,允许NULL。唯一索引一般只在创建数据表以及数据表没有数据之前创建或添加,如果已经存在数据,必须保证添加的列所有值具有唯一性。
1)创建唯一索引
create table [表名] ([字段名] [字段类型] [字段约束],...,unique [索引名称]([字段名称]));
mysql> create table uni_index(id int,name varchar(20),unique (id));
Query OK, 0 rows affected (0.12 sec)
mysql> desc uni_index;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | UNI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
注意:在Key列值为UNI的即是唯一索引。
2)添加唯一索引(尽量不要用,用也尽量在存放数据之前)
alter table [表名] add unique [索引名]([字段名]);
mysql> alter table uni_index add unique (name);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc uni_index;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | UNI | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
3)删除唯一索引
alter table [表名] drop key [索引名];
mysql> alter table uni_index drop key name;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc uni_index;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | UNI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
(4).主键索引
查询数据库按主键查询是最快的,每个数据表只允许存在一个主键,主键必须唯一,不允许为NULL。主键和唯一索引一样,一般只在创建数据表以及数据表没有数据之前创建或添加,如果已经存在数据,必须保证添加的列所有值具有唯一性。
1)创建主键
create table [表名] ([字段名] [字段类型] [字段约束],...,primary key [索引名称]([字段名称]));
create table [表名] ([字段名] [字段类型] [字段约束] [ primary key | key ],[字段名] [字段类型] [字段约束]...);
mysql> create table pri_index1 (id int,primary key (id));
Query OK, 0 rows affected (0.01 sec)
mysql> desc pri_index1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql> create table pri_index2 (id int key);
Query OK, 0 rows affected (0.01 sec)
mysql> desc pri_index2;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> create table pri_index3 (id int primary key);
Query OK, 0 rows affected (0.02 sec)
mysql> desc pri_index3;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
注意:在Key列值为PRI的即是主键索引。
2)添加主键(尽量不要用,用也尽量在存放数据之前)
alter table [表名] add primary key [索引名]([字段名]);
mysql> create table pri_index4 (id int);
Query OK, 0 rows affected (0.02 sec)
mysql> desc pri_index4;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> alter table pri_index4 add primary key (id);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc pri_index4;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
3)删除主键
alter table [表名] drop primary key;
mysql> alter table pri_index4 drop primary key;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc pri_index4;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)
(5).复合索引
一个索引可以包含一个或一个以上的列,当索引包含两个及以上的列时,此时的索引被称之为复合索引。基本创建、添加、删除方式没有改变,只是字段名部分可以为一个以上,之间由逗号隔开。
创建
mysql> create table comp_index(id int,name varchar(20), primary key (id,name));
Query OK, 0 rows affected (0.01 sec)
mysql> desc comp_index;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | NO | PRI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
添加
mysql> create table comp_index1(id int,name varchar(20));
Query OK, 0 rows affected (0.25 sec)
mysql> alter table comp_index1 add primary key (id,name);
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc comp_index1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | NO | PRI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
删除
mysql> alter table comp_index drop primary key;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc comp_index;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
(6).全文索引
全文索引也称全文检索,是目前搜索引擎使用的一种关键技术。它能够利用多种算法智能分析出文本文字中关键词的频率和重要性,然后按照一定的算法规则智能筛选出想要的结果。3.2开始支持全文索引,但无法正确支持中文;5.7.6开始内置ngram全文检索插件,用来支持中文。
旧版的全文索引只能用在MyISAM数据库引擎的表上,但5.6.24上InnoDB也加入了全文索引。不够只支持char、varchar和text的字段类型。
1)创建全文索引
create table [表名] ([字段名] [字段类型] [字段约束],...,fulltext key [索引名称]([字段名称]));
mysql> create table ful_index(id int,name varchar(20),age varchar(20),fulltext key (name));
Query OK, 0 rows affected (0.25 sec)
mysql> desc ful_index;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | MUL | NULL | |
| age | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> show index from ful_index;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ful_index | 1 | name | 1 | name | NULL | 0 | NULL | NULL | YES | FULLTEXT | | |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
注意:这里Key的值虽然是MUL,但使用show index from [表名]可以看到实际索引类型是FULLTEXT。
2)添加全文索引
alter table [表名] add fulltext key [索引名]([字段名]);
mysql> alter table ful_index add fulltext key (age);
Query OK, 0 rows affected (0.26 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from ful_index;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ful_index | 1 | name | 1 | name | NULL | 0 | NULL | NULL | YES | FULLTEXT | | |
| ful_index | 1 | age | 1 | age | NULL | 0 | NULL | NULL | YES | FULLTEXT | | |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.01 sec)
3)删除全文索引
alter table [表名] drop key [索引名];
mysql> alter table ful_index drop key age;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from ful_index;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ful_index | 1 | name | 1 | name | NULL | 0 | NULL | NULL | YES | FULLTEXT | | |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
(7).索引创建原则
索引并不是越多越好:1.数据量不大时不需要创建索引;
2.列中值变化种类不多不需要创建索引;
3.经常排序(order by)和分组(group by)的列需要建立索引;
4.列中值需要具有唯一性,可以使用唯一索引或主键索引,但每个表中只能存在一个主键索引,并且不能为NULL。