12 MySQL索引

悠悠 2023-09-28 12:12 144阅读 0赞

**目录**

一、索引简介及优缺点

一、索引的作用：

二、索引优点：

三、索引缺点：

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

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

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

1.普通索引：

2.唯一索引：

3.主键索引：

4.联合索引：

一、普通索引：

1.普通索引特点:

2.基本语法：

2.1查询索引：

2.2直接创建索引：

2.3添加索引：

2.4删除索引：

二、唯一索引：

1.唯一索引特点：

2.基本语法：

2.1创建唯一索引：

2.2修改表添加唯一索引：

2.3创建表指定唯一索引：

三、主键索引：

1.主键索引特点：

2.基本语法：

2.1修改表添加主键索引：

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

四、组合索引：

1.组合索引特点：

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

3.基本语法：

3.1修改表添加组合索引：

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

--------------------

> ## **一、索引简介及优缺点** ##
> 
> ### **一、索引**的作用： ###
> 
> 索引相当于图书上的目录，可以根据目录上的页码快速找到所需的
> 
> 内容，**提高性能（查询速度）**。
> 
> ### **二、索引优点：** ###
> 
> 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 列创建组合索引。