MySQL数据库操作补坑(四)索引

心已赠人 2022-11-21 15:09 245阅读 0赞

### 文章目录 ###

*  索引简介
 *   *  1）、索引的作用和特点
     *  2）、索引的分类
     *  3）、索引设计原则
 *  创建索引
 *   *  1）、创建表的时候创建索引
     *  2）、在已存在的表上创建缩影
 *  删除索引
 *  问题补充：
 *   *   *   *  MySQL中索引、主键和唯一性的区别是什么
             *  表中建立了索引以后，导入大量数据为什么会慢

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

# 索引简介 #

## 1）、索引的作用和特点 ##

**定义**：

*  索引是创建在表上的，对数据库表中一列或多列的值进行排序的一种结构。

**作用及特点**：

*  通过索引，查询数据时可以不必读完记录的所有信息，而只是查询索引。可以很大程度上提高数据库系统的查询速度，提高数据库的性能。
 *  不同的存储引擎定义了每个表的最大缩阴术和最大索引长度。所有存储引擎对每个表至少支持16个索引，总索引长度至少为256字节。
 *  索引有两种存储类型：
    
     *      B型树(BTREE)索引：InnoDB引擎、MyISAM引擎、MEMORY引擎（MEMORY默认为哈希）
     *      哈希索引(HASH)：MEMORY引擎（默认为哈希）

**优缺点**：

*  优点：
    
    1.  可以提高检索数据的速度（创建索引的主要原因）
    2.  对于有依赖关系的子表和附表之间的联合查询时，可以提高查询速度
    3.  使用分组和排序子句进行数据查询时，同样可以显著节省查询中分组和排序的时间。
 *  缺点：
    
    1.  创建和维护索引需要耗费时间，长度随数据量增加而增加
    2.  索引需要占用物理空间
    3.  增加、删除、修改数据时，要动态的维护索引。维护速度降低。

提示:

> 解决数据维护速度降低的方法：维护前先删除表中的索引，维护完成后再创建索引。（但需考虑创建索引的时间）

## 2）、索引的分类 ##

**1. 普通索引**

*  在创建索引时，不附加任何限制条件。这类索引可以创建在任何数据类型中，其值是否唯一和非空字段本身的完整性约束条件决定。

**2. 唯一性索引**

*  使用UNIQUE参数可以设置索引为唯一性索引、在创建该索引的值必须是唯一的。主键就是一种特殊唯一性索引。

**3. 全文索引**

*  使用FULLTEXT参数可以设置索引为全文索引。全文索引只能创建在CHAR、VARCHAR、或TEXT类型的字段上。查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。MySQL从3.23.23版本开始支持全文索引，但只有MyISAM引擎支持全文检索。默认全文索引的搜索执行方式不区分大小写。但索引的列使用二进制排序后，可以执行区分大小写的全文索引。

**4. 单列索引**

*  在表中的单个字段上创建索引。单列索引只根据该字段景行索引。单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引，只要保证该索引只对应一个字段即可。

**5. 多列索引**

*  多列索引是在表的多个字段上创建的一个索引该索引指向创建是对应的多个字段，可以通过这几个字段进行查询，只有查询条件使用了id字段时该索引才会被引用。

**6. 空间索引**

*  使用SPATIAL参数可以设置索引为空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。MySQL中的空间数据类型包括GEOMETRY、POINT、LINESTRING、POLYGON等。目前只有MyISAM存储引擎支持空间检索，而且索引字段不能为空值。

## 3）、索引设计原则 ##

**1. 选择唯一性索引**

*  唯一性索引个以更快的通过该索引来确定某条记录。（通过其唯一性）

**2. 为经常需要排序、分组、、联合操作的字段建立索引**

*  经常需要ORDER BY、GROUP BY、DISTINCT、UNION等操作的字段，为其添加索引避免排序操作。

**3. 为常作为查询条件的字段建立索引**

*  因为常进行查询操作的字段查询速度会影响整个表的查询速度，所以适合加上索引。

**4. 限制索引的数目**

*  索引不是越用越多好，每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大，缩印的重构和更新很麻烦，越多的索引会使更新表变得很费时间。

**5. 尽量使用数据量少的索引**

*  如果索引的值很长，那么查询的速度会受到影响。对一个CHAR(100)类型的字段进行全文检索需要的时间肯定比对CHAR(10)类型的字段需要的时间更多。

**6. 尽量使用前缀来索引**

*  例如TEXT和BLOG类型的字段，检索前缀可以提高检索速度

**7. 删除不再使用或很少使用的索引**

*  表中的数据被大量更新或数据的使用方式被改变后，原有的一些不再需要的索引应该被删除，从而减少索引对更新操作的影响。

> 选择索引的最终目的是为了是查询的速度变快，上面给出的是最基本的准则，还应结合实际情况进行适当更改或选择。

# 创建索引 #

## 1）、创建表的时候创建索引 ##

*  语法：
    
        CREATE TABLE 表名 (属性名 数据类型 [完整性约束条件],
        				   属性名 数据类型 [完整性约束条件]
        			       ...
        			       属性名 数据类型
        			       [UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY
        			        	[别名] (属性名1 [(长度)] [ASC|DESC])
        			        );
    
     *  `UNIQUE`是可选参数，表示索引为唯一性索引
     *  `FULLTEXT`是可选参数，表示索引为全文索引
     *  `SPATIAL`也是可选参数，表示索引为空间索引
     *  `INDEX`和`KEY`参数用来制定字段为索引的，两者选择其中之一就可以了，作用是一样的
     *  “别名”是可选参数，用来给创建的索引取的新名称
     *  “属性1”参数指定索引对应的字段的名称，该字段必须为前面定义好的字段;
     *  “长度”是可选参数，其指索引的长度，必须是字符串类型才可以使用；
     *  “ASC”和“DESC”都是可选参数，“ASC”参数表示升序排序，“DESC”参数表示降序排列

**1. 创建普通索引**  
不需要添加任何UNIQUE、FULLTEXT或者SPATIAL参数。

CREATE TABLE index1 (id INT,
    					 name VARCHAR(20),
    					 sex BOOLEAN,
    					 INDEX(id)
    					 );

使用EXPLAN语句可以查看索引是否被引用。  
`EXPLAN SELECT * FROM index1 where id = 1 \G`

possible_keys:index1_id
    			   keys:index1_id

当possible\_keys和key处的值都为index1\_id，说明index1\_id索引已经存在，而且已经开始起作用。  
**2.创建唯一性索引**  
需要使用UNIQUE参数进行约束。

*  语法：
    
        CREATE TABLE index2(id INT UNIQUE,
        					name VARCHAR(20),
        					UNIQUE INDEX index2_id(id ASC)
        					);

**3.创建全文索引**  
只能创建在`CHAR/VARCHAR/TEXT`类型的字段上。现在只有MyISAM存储引擎支持全文索引。

*  语法：
    
        CREATE TABLE index3(id INT,
        					info VARCHAR(20)，
        					FULLTEXT INDEX index3_info(info)
        					)ENGINE=MyISAM;
    
    **4.创建单列索引**  
    在表的单个字段上创建索引
 *  语法：
    
        CREATE TABLE index2(id INT,
        						subject VARCHAR(30),
        						UNIQUE INDEX index4_st(subject(10))
        						);
    
    > subject的长度为20，索引的长度为10，目的是为了查询更快。对于字符型数据，可以不用查询全部信息，而只查询前面的字符信息。

**5.创建多列索引**  
在表的多个字段上创建一个索引

*  语法：
    
        CREATE TABLE index5(id INT,
        					name VARCHAR(20),
        					sex CHAR(4),
        					INDEX index5_ns(name,sex)
        					);
    
    只有查询条件使用了这些字段中的第一个字段时，索引才会被使用

**创建空间索引**  
必须使用SPATIAL参数来设置。存储引擎必须是MyISAM类型。字段必须有非空约束

*  语法：
    
        CREATE TABLE index6(id INT,
        					space GEROMETRY NOT NULL,
        					SPATIAL INDEX index6_sp(space)
        					)ENGINE=MyISAM;
    
    GEROMETRY 为空间类型，还包括POINT、LINESTRING、POLYGON

## 2）、在已存在的表上创建缩影 ##

基本语法：

CREATE [ UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
    	ON 表名 (属性名 [(长度)] [ ASC | DESC ]);

*  UNIQUE：可选，表示索引是唯一性索引
 *  FULLTEXT：可选，表示全文索引
 *  SPATIAL：可选，表示空间索引
 *  INDEX：指定字段为索引
 *  索引名：创建的索引的名字
 *  表名：创建索引的表的名称，必须是已存在的
 *  属性名：索引对应的字段名称，该字段必须为前面定义好的字段
 *  长度：可选，索引长度，必须是字符创类型才能使用
 *  ASC：可选，升序排序
 *  DESC：可选，降序排序

**1.创建普通索引**

ALERT TABLE index13 ADD INDEX index13_name(name(20));

**2.创建唯一性索引**

ALERT TABLE index14 ADD UNIQUE INDEX index14_id(course_id);

*  index\_14：为索引的名词；
 *  UNIQUE：用来设置索引为唯一索引；
 *  course\_id：字段可以由唯一性约束；

**3.创建全文索引**

ALERT TABLE index15 ADD FULLTEXT INDEX index15_info(info);

*  FULLTEXT：用来设置索引为全文索引；

**4.创建单列索引**

ALERT TABLE index16 ADD INDEX index16_addr(address(4));

**5.创建多列索引**

ALERT TABLE index17 ADD INDEX index17_na(name,address);