12 MySQL索引_employeeidmysql-CSDN博客

一、索引简介及优缺点

一、索引的作用：

索引相当于图书上的目录，可以根据目录上的页码快速找到所需的

内容，提高性能（查询速度）。

二、索引优点：

1.通过创建唯一性索引，可以保证数据库表中的每一行数据的唯一性；

2.可以加快数据的检索速度；

3.可以加速表与表之间的连接；

4.在使用分组和排序进行检索的时候，可以减少查询中分组和排序的时间；

三、索引缺点：

1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加；

2.索引需要占用物理空间，数据量越大，占用空间越大；

3.会降低表的增删改的效率，因为每次增删改索引都需要进行动态维护；

四、什么时候需要创建索引:

1.频繁作为查询条件的字段应该创建索引;

2.查询中排序的字段创建索引将大大提高排序的速度（索引就是排序加快速查找）；

3.查询中统计或者分组的字段；

五、什么时候不需要创建索引

1.频繁更新的字段不适合创建索引，因为每次更新不单单是更新记录，还会更新索引，保存索引文件；

2.where条件里用不到的字段，不创建索引；

3.表记录太少，不需要创建索引；

4.经常增删改的表；

5.数据重复且分布平均的字段，因此为经常查询的和经常排序的字段建立索引。注意某些数据包含大量重复数据，因此他建立索引就没有太大的效果，例如性别字段，只有男女，不适合建立索引；

二、MySQL中的四种索引类型:

1.普通索引：

最基本的索引，它没有任何限制。

2.唯一索引：

索引列的值必须唯一，但允许有空值，如果是组合索引，则列值

的组合必须唯一。

3.主键索引：

特殊的索引，唯一的标识一条记录，不能为空，一般用primary key来约束。(主键默认带有索引)

4.联合索引：

在多个字段上建立索引，能够加速查询到速度。

一、普通索引：

1.普通索引特点:

是最基本的索引，它没有任何限制。在创建索引时，可以指定索引

长度。length 为可选参数，表示索引的长度，只有字符串类型的字

段才能指定索引长度，如果是 BLOB 和 TEXT 类型，必须指定

length。

创建索引时需要注意：

如果指定单列索引长度，length 必须小于这个字段所允许的最

大字符个数。

2.基本语法：

2.1查询索引：

SHOW INDEX FROM table_name;

2.2直接创建索引：

CREATE INDEX index_name ON table(column(length));

例：create index emp3_name_index ON emp3(name);

表示：为 emp3 表中的 name 创建一个索引，索引名为emp3_name_index；

2.3添加索引：

ALTER TABLE table_name ADD INDEX index_name (column(length));

例：alter table emp3 add index emp3_address_index(address);

表示：修改 emp3 表，为 addrees 列添加索引，索引名为emp3_address_index；

2.4删除索引：

DROP INDEX index_name ON tablename；

例：drop index emp3_address_index on emp3;

例：create table emp4(emp_id int primary key

auto_increment, name varchar(30), address

varchar(50),index emp4_name_index(name));

表示：创建 emp4 表，包含 emp_id,name,address 列，同时为 name 列创建索引，索引名为 emp4_name_index。

二、唯一索引：

1.唯一索引特点：

唯一索引与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。

注意：

当我们为表分配了唯一性约束时，列就已经自带了唯一索引。

2.基本语法：

2.1创建唯一索引：

注意：当列中有重复值时，不能够成功创建唯一索引

CREATE UNIQUE INDEX indexName ON table(column(length));

例：create unique index emp_name_index on emp(name);

表示：为 emp 表中的 name 创建一个唯一索引，索引名为emp_name_index。

2.2修改表添加唯一索引：

ALTER TABLE table_name ADD UNIQUE indexName (column(length));

例：alter table emp add unique emp_salary_index(salary);

表示：修改 emp 表，为 salary 列添加唯一索引，索引名为

emp_salary_index。

2.3创建表指定唯一索引：

CREATE TABLE `table` (COLUMN TYPE ,PRIMARY KEY (`id`), UNIQUE index_name (column(length)));

例：create table emp5(emp_id int primary key,name varchar(30),address varchar(30),unique emp5_name_index(name));

表示：创建 emp5 表，包含 emp_id,name,address 列，同时为 name 列创建唯一索引。索引名为 emp5_name_index。

三、主键索引：

1.主键索引特点：

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许

有空值。一般是在建表的时候同时创建主键索引。

（通俗地讲：创建主键的时候就同时有了主键索引，不可以为非主键列创建主键索引）

2.基本语法：

2.1修改表添加主键索引：

ALTER TABLE 表名 ADD PRIMARY KEY(列名);

例：alter table emp add primary key(employee_id);

表示：修改 emp 表为 employee_id 添加主键索引。

2.2创建表时指定主键索引：

CREATE TABLE `table` (COLUMN TYPE ,PRIMARY KEY(column));

例：create table emp6(employee_id int primary key

auto_increment,name varchar(20),address varchar(50));

表示：创建 emp6 表，包含 emp_id,name,address 列，同时为 emp_id

列创建主键索引。

四、组合索引：

1.组合索引特点：

组合索引是指使用多个字段创建的索引，只有在查询条件中使用了

创建索引时的第一个字段，索引才会被使用(最左前缀原则)。

2.最左前缀原则：（就是最左优先。）

如：我们使用表中的 name ，address ，salary 创建组合索引，那

么想要组合索引生效，我们只能使用如下组合：

name/address/salary

name/address

name/

如果使用 addrees/salary 或者是 salary 则索引不会生效。

3.基本语法：

3.1修改表添加组合索引：

ALTER TABLE table_name ADD INDEX index_name

(column(length),column(length));

例：alter table emp6 add index emp6_index_n_a(name,address);

表示：修改 emp6 表，为 name ，address 列创建组合索引。

3.2创建表时创建组合索引：

CREATE TABLE `table` (COLUMN TYPE , INDEX index_name (column(length),column(length)));

例：create table emp7(emp_id int primary key auto_increment ,

name varchar(20),address varchar(30),

Index emp7_index_n_a(name,address));

表示：创建 emp7 表，包含 emp_id,name,address 列，同时为

name,address 列创建组合索引。