mysql数据据索引

刺骨的言语ヽ痛彻心扉 2022-05-18 09:40 258阅读 0赞

# 索引 #

#### 1.索引简介 ####

索引是一个单独的存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。索引用于快速找出在某个列中有一特定值的行。不使用索引, MySQL必须从第1条记录开始读完整个表,直到找出相关的行。  
索引是在存储引擎中实现的,因此,每种存储引擎的索引都不一定完全相同,并且每种存储引擎也不一定支持所有索引类型。 MySQL中索引的存储类型有两种: BTREE和HASH：MyISAM和 InnoDB存储引擎只支持 BTREE索引； MEMORY/HEAP存储引擎可以支持HASH和 BTREE索引。  
**索引的优点：**

> **(1)** 通过创建唯一索引,可以保证数据库表中每一行数据的唯一性。  
> **(2)** 可以大大加快数据的查询速度,也加速表和表之间的连接。  
> **(3)** 在使用分组和排序子句进行数据查询时,也可以显著减少查询中分组和排序的时间。

**增加索引的缺点：**

> **(1)** 创建索引和维护索引要耗费时间,当对表中的数据进行增加、删除和修改的时候,索引也要动态地维护。  
> **(2)** 索引需要占磁盘空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间。

##### 1.1 索引的分类(只仅Mysql Innodb存储引擎索引) #####

**1.普通索引和唯一索引**  
普通索引是 MySQL中的基本索引类型,允许在定义索引的列中插入重复值和空值  
唯一索引是列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一  
主键索引是一种特殊的唯一索引,不允许有空值。

###### 注: 约束和索引: 前者是用来检查数据的正确性，后者用来实现数据查询的优化。 ######

> （1）.创建唯一约束时，也会创建一个对应的唯一索引。  
> （2）.创建唯一索引时，只会创建一个唯一索引。(可以多次创建)  
> 也就是说其实唯一约束是通过创建唯一索引来实现的。删除唯一约束时可以只删除约束而不删除对应的索引，所以对应的列还是必须唯一的，而删除了唯一索引的话就可以插入不唯一的值。  
> 在创建唯一性约束和主键约束时可以创建聚集索引和非聚集索引，但在 默认情况下主键约束产生聚集索引，而唯一性约束产生非聚集索引

**2.单列索引和组合索引**  
单列索引即一个索引只包含单个列,一个表可以有多个单列索引。  
组合索引指在表的多个字段组合上创建索引,只有在查询中使用了这些字段的左边字段时,索引才会被使用。（最左前缀）

##### 1.2 索引的设计原则 #####

索引设计不合理或者缺少索引都会影响对数据库和应用程序的性能。**设计索引时,应该考虑以下准则：**

> **(1)** 索引不仅占用磁盘空间,而且会影响INSERT、 DELETE、 UPDATE等语句时，索引也会进行调整和更新。  
> **(2)** 数据量小的表最好不要使用索引，避免对经常更新的表进行过多的索引,并且索引中的列尽可能少。  
> **(3)** 在条件表达式中经常用到的不同值较多的列上建立索引,在不同值很少的列上不要建立索引，比如“性别”。  
> **(4)** 当唯一性是某种数据本身的特征时,指定唯一索引。  
> **(5)** 在频繁进行排序或分组的列上建立索引,如果待排序的列有多个,可以在这些列上建立组合索引。

#### 2.创建索引 ####

MySQL支持多种方法在单个或多个列上创建索引:在创建表的定义语句 CREATE TABLE中指定索引列,使用 ALTER TABLE语句,或者使用 CREATE INDEX语句在已存在的表上添加索引。

##### 2.1 创建表的时候创建索引 #####

使用 CREATE TABLE创建表时,除了可以定义列的数据类型,还可以定义主键约束或者唯一性约束,而不论创建哪种约束,在定义约束的同时相当于在指定列上创建了个索引。创建表时创建索引的基本语法格式如下:

CREATE TABLE table_name [col_name data_type] [UNIQUE | FULLTEXT | SPATIAL) [INDEX | KEY] [index_name] (col_name [length])[ASC | DESC]

UNIQUE、 FULLTEXT和 SPATIAL为可选参数,分别表示唯一索引、全文索引和空间索引。 INDEX与KEY为同义词,两者作用相同,用来指定创建索引； col name为需要创建索引的字段列,该列必须从数据表中定义的多个列中选择； index name指定索引的名称,为可选参数,如果不指定, MySQL默认 col name为索引值；length为可选参数,表示索引的长度,只有字符串类型的字段才能指定索引长度（查询时，只需要检索length个字符）；ASC或DESC指定升序或者降序的索引值存储。  
建表语句:

DROP TABLE IF EXISTS `index_data`;
    CREATE TABLE `index_data`  (
      `pk_field` 	int(11) NOT NULL COMMENT '索引测试表主键',
      `remark`    varchar(40) CHARACTER SET utf8  COMMENT '备注信息',
    	`operate_id` int(11) NOT NULL COMMENT '操作人id',
    	`operate_name`  varchar(40) CHARACTER SET utf8  COMMENT '操作人姓名',
      `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
      INDEX  multiIndex (`pk_field`,`operate_id`,`operate_name`) USING BTREE
    ) ENGINE = InnoDB  CHARACTER SET = utf8

使用EXPLAIN语句查看  
![这里写图片描述][70]  
**EXPLAIN语句输出结果含义：**

> **(1)** **select type**行指定所使用的 SELECT查询类型,这里值为 SIMPLE,表示简单的 SELECT,其他可能的取值有: PRIMARY、 UNION、 SUBQUERY等。  
> **(2)** **table**指定数据库读取的数据表的名字,它们按被读取的先后顺序排列。  
> **(3)** **type**指定了本数据表与其他数据表之间的关联关系。

> 可能的取值有 system、const、eq\_ref、ref、 range、 index和All；\*\*性能：\*\*由最佳到最差  
> \*\*system：\*\*该表是仅有一行的系统表。这是 const连接类型的一个特例。  
> \*\*const：\*\*数据表最多只有一个匹配行,它将在查询开始时被读取,并在余下的查询优化中作为常量对待 。const常用于使用PRIMARY KEY或 UNIQUE索引的情况。  
> \*\*eq\_ref：\*\*使用的是唯一索引。  
> \*\*ref：\*\*查找条件列使用了非唯一索引扫描或唯一索引前缀扫描，返回单条记录  
> \*\*range：\*\*检索索引值的给定范围。常见于=、<>、>、>=、<、<=、 IS NULL、<=>、 BETWEEN或者IN等操作符  
> \*\*index：\*\*索引全扫描。  
> \*\*all：\*\*进行完整的表扫描。通常可以增加索引来避免使用ALL连接

> **(4)** **possible keys**给出了 MySQL在搜索数据记录时可选用的各个索引。  
> **(5)** **key**是 MySQL实际选用的索  
> **(6)** **key len**给出索引按字节计算的长度, key len数值越小,表示越快。  
> **(7)** **ref**给出了关联关系中另一个数据表里的数据列的名字。  
> **(8)** **rows**是 MySQL在执行这个查询时预计会从这个数据表里读出的数据行的个数  
> **(9)** **extra**提供了与关联操作有关的信息。

\*\*组合索引可起几个索引的作用，但是使用时并不是组合索引中哪个字段都可以使用索引，而是遵从“最左前缀”；\*\**利用索引中最左边的列集来匹配行,这样的列集称为最左前缀*。例如这里由pk\_field、operate\_id和operate\_name，3个字段构成的索引,索引行中按 pk\_field/operate\_id/operate\_name的顺序存放,索引可以搜索下面字段组合:(pk\_field,operate\_id,operate\_name)、(pk\_field,operate\_id)或者pk\_field。如果列不构成索引最左面的前缀。MySQL不能使用局部索引,如(operate\_name)或者(operate\_id,operate\_name)组合则不能使用索引查询。  
例如:  
![这里写图片描述][70 1]  
因为没有遵循“最左原则”，查询语句中的组合索引字段没有生效。

##### 2.2 已存在的表添加索引 (1) #####

**使用 ALTER TABLE语句创建索引：**

ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY ] [index_name](col_name[length],...) [ASC |DESC]

在这里使用了 ALTER TABLE和ADD关键字,ADD表示向表中添加索引。  
在执行添加索引之前，查看当前表已创建的索引。**语法：show index from table\_name** ；  
![这里写图片描述][70 2]  
**其中各个主要参数的含义为:**

> **(1) Table**表示创建索引的表。  
> **(2) Non unique**表示索引非唯一,1代表是非唯一索引,0代表唯一索引。  
> **(3) Key name**表示索引的名称  
> **(4) Seq\_in\_index**表示该字段在索引中的位置,单列索引该值为1,组合索引为每个字段在索引定义中的顺序。  
> **(5) Column\_name**表示定义索引的列字段  
> **(6) Sub\_part**表示索引的长度  
> **(7) Null**表示该字段是否能为空值  
> **(8) Index\_type**表示索引类型。

添加索引：  
![这里写图片描述][70 3]

##### 2.2 已存在的表添加索引 (2) #####

**使用 CREATE INDEX创建索引：**

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    ON table_name(col_name [length],...) [ASCI DESC]

CREATEⅠNDEX语句和 ALTER TABLE语句的语法基本一样只是关键字不同。 Mysql中CREATE INDEX语句在内部被映射到一个 ALTER TABLE语句中。  
![这里写图片描述][70 4]

#### 3.删除索引 ####

MySQL中删除索引使用 ALTER TABLE或者 DROP INDEX语句,两者可实现相同的功能, DROP INDEX语句在内部被映射到一个 ALTER TABLE语句中。  
**使用 ALTER TABLE删除索引：**

ALTER TABLE table_name DROP INDEX index_name

**使用DROP|NDEX语句删除索引：**

DROP INDEX index_name ON table_name:

###### 注:添加 AUTO INCREMENT约束字段的唯一索引不能被删除。删除表中的列时,如果要删除的列为索引的组成部分,则该列也会从索引中删除。如果组成索引的所有列都被删除,则整个索引将被删除。 ######

[70]: /images/20220518/196ea576ca3c492b8978a3bf87cc23ca.png
[70 1]: /images/20220518/b31de75cddab4eb185e1182a0943b2e8.png
[70 2]: /images/20220518/f605ef32492a43e796acf9a86cb0c795.png
[70 3]: /images/20220518/adeafe880d934bedb8e173ebacf3e642.png
[70 4]: /images/20220518/735a097000c0458aad1c91085d91ee35.png