MySQL数据库之索引

傷城~ 2023-06-27 11:25 34阅读 0赞

### 文章目录 ###

*   *  一、MYSQL的索引概述
     *   *  1.1、索引的作用
         *  1.2、索引的分类
         *  1.3、什么情况下创建索引？
     *  二、怎么去创建索引？
     *   *  2.1、普通索引
         *  2.2、唯一性索引
         *  2.3、主键索引
         *  2.4、全文索引
     *  三、查看索引
     *  四、删除索引

## 一、MYSQL的索引概述 ##

### 1.1、索引的作用 ###

*  设置了合适的索引之后，数据库利用各种快速的定位技术，能够大大加快查询速率。
 *  特别是当表很大时，或者查询涉及到多个表时，使用索引可使查询加快成干倍。
 *  可以降低数据库的IO成本，并且索引还可以降低数据库的排序成本
 *  通过创建唯一性索引保证数据表数据的唯一性
 *  可以加快表与表之间的连接
 *  在使用分组和排序时，可大大减少分组和排序时间

### 1.2、索引的分类 ###

*  **普通索引**
    
     *  这是最基本的索引类型，而且它没有唯一性之类的限制。
 *  **唯一性索引**
    
     *  这种索引和前面的”普通索引”基本相同，但有一个区别: 索引列的所有值都只能出现一次，即必须唯一。
 *  **主键**
    
     *  主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”。

**注意：一个表中只能有一个主键，但可以在多个字段，一个主键对应多个字段。**

*  **全文索引**
    
     *  在MySQL中，全文索引的索引类型为FULLTEXT，全文索引可以在VARCHAR或者TEXT类型的列上创建。
 *  **单列索引与多列索引**
    
     *  索引可以是单列上创建的索引，也可以是在多列上创建的索引

### 1.3、什么情况下创建索引？ ###

*  表的主键，外键必须有索引
 *  数据量超过 300 行的 表 应该有索引
 *  经常与其他表进行连接的表，在连接字段上应该建立索引
 *  经常出现在Where子句中的字段，特别是大表的字段,应该建立索引
 *  索引应该建在选择性高的字段上

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

*  唯一性太差的字段不适合建立索引
 *  更新太频繁地字段不适合创建索引
 *  索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引

## 二、怎么去创建索引？ ##

*  根据企业需求选择了合适的索引之后，可使用 create index 创建索引
 *  create index 加上各个索引关键字，便可创建各个类型的索引。
 *  创建索引有三种方式：
    
     *  1、创建表时直接定义。
     *  2、create index 索引名称 on 表名(列名1，列名2) ;
     *  3、alter table 表名 add index 索引名称 (列名)；

### 2.1、普通索引 ###

create index <索引的名字> on tablename (列的列表);
    例如：
    1、 create table stu02(id int(4) not null,name char(10) not null,score int (4),index index_score(score));
    
    2、create index index_score on stu01(score);
    
    3、alter table stu01 add index index_name(name);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70 1]

### 2.2、唯一性索引 ###

格式：create unique index <索引的名字> on tablename (列的列表);
    例：
    create unique index index_name on stu01(name);
    
    alter table stu02 add unique index index_name(name);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70 2]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70 3]

### 2.3、主键索引 ###

#创建主键索引有两种方法。
    1、create table 表名 ( [..]... primary key (列的列表) );  #在创建表时设定的
    
    2、alter table 表名 add primary key (列的列表);

![在这里插入图片描述][20200106172405180.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70 4]

### 2.4、全文索引 ###

格式：create fulltext index 索引名 on 表名(字段);
    create fulltext index full_index_name on stu02(name);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70 5]

## 三、查看索引 ##

1、索引查看
    show index from 表名;
    show keys from 表名;
    例：
    show index from stu02;
    show keys from stu02;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70 6]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70 7]

## 四、删除索引 ##

*  1、删除索引  
    格式：drop index 索引名 on 表名;  
    或者 alter table 表名 drop index 索引名;  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70 8]  
    ![在这里插入图片描述][20200106182049476.png]
 *  2、删除主键索引

格式：alter table 表名 drop primary key;
    例子：
    alter table stu02 drop primary key;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70 9]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200106172000602.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200106161621197.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200106162242140.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200106172746862.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx,size_16,color_FFFFFF,t_70
[20200106172405180.png]: https://img-blog.csdnimg.cn/20200106172405180.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200106172553540.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/2020010617344172.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20200106181038387.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20200106181112206.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20200106161826160.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx,size_16,color_FFFFFF,t_70
[20200106182049476.png]: https://img-blog.csdnimg.cn/20200106182049476.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx_size_16_color_FFFFFF_t_70 9]: https://img-blog.csdnimg.cn/20200106182731810.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MzYxNTQx,size_16,color_FFFFFF,t_70