MySQL管理之索引

淡淡的烟草味﹌ 2023-10-02 10:01 6阅读 0赞

<table> 
 <thead> 
  <tr> 
   <th>序号</th> 
   <th>类型</th> 
   <th align="left">地址</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/weixin_44624117/article/details/96165881" rel="nofollow">MySQL操作之概念、SQL约束（一）</a></td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/weixin_44624117/article/details/96339284" rel="nofollow">MySQL操作之数据定义语言（DDL)（二）</a></td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/weixin_44624117/article/details/96445064" rel="nofollow">MySQL操作之数据操作语言（DML)（三）</a></td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/weixin_44624117/article/details/96726772" rel="nofollow">MySQL操作之数据查询语言：(DQL)（四-1）（单表操作）</a></td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/weixin_44624117/article/details/96729083" rel="nofollow">MySQL操作之数据查询语言：(DQL)（四-2）（多表查询）</a></td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/120254916" rel="nofollow">MySQL操作之数据控制语言：(DC)（五）</a></td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/weixin_44624117/article/details/122593057" rel="nofollow">MySQL操作之数据库函数</a></td> 
  </tr> 
  <tr> 
   <td>8</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/120383272" rel="nofollow">MySQL管理之数据类型</a></td> 
  </tr> 
  <tr> 
   <td>9</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/120393164" rel="nofollow">MySQL管理之索引</a></td> 
  </tr> 
  <tr> 
   <td>10</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/120240970" rel="nofollow">MySQL管理之事务管理</a></td> 
  </tr> 
  <tr> 
   <td>11</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/120383054" rel="nofollow">MySQL管理之存储过程</a></td> 
  </tr> 
  <tr> 
   <td>12</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/120251668" rel="nofollow">MySQL管理之视图</a></td> 
  </tr> 
  <tr> 
   <td>13</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/120383282" rel="nofollow">MySQL管理之数据备份与还原</a></td> 
  </tr> 
  <tr> 
   <td>14</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/95240161" rel="nofollow">Linux（centos 7.5）服务器安装MySQL</a></td> 
  </tr> 
  <tr> 
   <td>15</td> 
   <td>MyBatis</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/119246809" rel="nofollow">MyBatis从入门到多表关联</a></td> 
  </tr> 
  <tr> 
   <td>16</td> 
   <td>MyBatis</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/120823825" rel="nofollow">MyBatis常用方法</a></td> 
  </tr> 
  <tr> 
   <td>17</td> 
   <td>MyBatis</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/98309342" rel="nofollow">Mybatis逆向工程的使用（附文件地址）</a></td> 
  </tr> 
  <tr> 
   <td>18</td> 
   <td>MyBatis</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/106430172" rel="nofollow">spring boot连接Mybatis数据库的配置文件(MySql、SQLserver、Oracle)</a></td> 
  </tr> 
  <tr> 
   <td>19</td> 
   <td>MyBatis-Plus</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/119060130" rel="nofollow">Mybatis-Plus使用案例(包括初始化以及常用插件)</a></td> 
  </tr> 
  <tr> 
   <td>20</td> 
   <td>MyBatis-Plus</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/124972178" rel="nofollow">Mybatis-Plus(Service CRUD 接口)</a></td> 
  </tr> 
  <tr> 
   <td>21</td> 
   <td>MyBatis-Plus</td> 
   <td align="left"><a href="http://t.csdn.cn/3mdPc" rel="nofollow">Mybatis-plus 4种条件构造器方式</a></td> 
  </tr> 
  <tr> 
   <td>22</td> 
   <td>MyBatis-Plus</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/124936692" rel="nofollow">Mybatis-Plus 执行自定义SQL</a></td> 
  </tr> 
  <tr> 
   <td>23</td> 
   <td>MyBatis-Plus</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/124971821" rel="nofollow">MyBatis-plus配置自定义SQL(执行用户传入SQL)</a></td> 
  </tr> 
  <tr> 
   <td>24</td> 
   <td>MyBatis-Plus</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/124973383" rel="nofollow">Mybatis-Plus(连接Hive)</a></td> 
  </tr> 
  <tr> 
   <td>25</td> 
   <td>MyBatis-Plus</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/122095848" rel="nofollow">Mybatis-Plus 代码生成器</a></td> 
  </tr> 
 </tbody> 
</table>

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

### 文章目录 ###

*   *  一、简介
     *  二、分类
     *  三、创建索引
     *   *  3.1 创建表时创建索引
         *  3.2 使用Create Index语句在已经存在的表上创建索引
         *  3.3 使用 Alter Table 语句在已经存在的表上创建索引
     *  四、删除索引
     *   *  4.1使用 Alter Table 删除索引
         *  4.2使用 Drop Index 删除索引
     *  五、Xmind整理

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

## 一、简介 ##

数据索引就好比新华字典的音序表。它是对数据表中一列或者多列的值进行排序后的一种结构，其作用就是提高表中数据的查询速度。

虽然索引可以提高数据的查询速度，但索引会占用一定的磁盘空间，并且在创建和维护索引时，其消耗的时间随着数据量的增加而增加的。

## 二、分类 ##

**1.普通索引**

普通索引是由KEY或INDEX定义的索引。是MySQL中的基础索引类型，可以创建在任何数据类型中，其值是否唯一和非空由字段本身的约束条件所决定。

**2.唯一性索引**

是由`UNIQUE`定义的索引，该索引所在的值必须是唯一的。

**3.全文索引**

是由`FULLTEXT`定义的索引，它只能创建在`CHAR`、`VARCHAR`、`TEXT`类型的字段上。现在只有MyISAM存储引擎支持全文索引。

**4.单列索引**

在表中单个字段上创建索引，可以是普通索引、唯一索引或者全文索引，只要保证该索引只对应表中一个字段既可。

**5.多列索引**

在表中多个字段上创建索引。只有在查询条件中使用了这些字段中的第一个字段时，该索引才会被使用。

**6.空间索引**

是由`SPATIAL`定义的索引，只能创建在空间数据类型的字段上。

MySQL中的空间数据类型有4种。Geometry、Point、LineString和Polygon。

需要注意的是创建空间索引的字段，必须声明其为NOT NULL，并且空间索引只能在存储引擎为MyISAM的表中创建。

## 三、创建索引 ##

创建索引的索引方法有三种，具体为：

*  创建表时创建索引
 *  使用Create Index语句在已经存在的表上创建索引
 *  使用Alter Table语句在已经存在的表上创建索引

### 3.1 创建表时创建索引 ###

CREATE TABLE 表名 (
      字段名1 数据类型 约束条件,
      字段名2 数据类型 约束条件,
      [UUNIQUE|FULLTEXT|SPATIALINDEX|KEY
       	[别名](字段1[(长度)][AES|DESC])

解释：

*  UNIQUE：可选参数，表示唯一索引。
 *  FULLTEXT：可选参数，表示全文索引。
 *  SPATIAL：可选参数，表示空间索引
 *  INDEX和KEY：用来表示字段的索引，二者选一既可。(普通索引)
 *  别名：可选参数，表示创建的索引名称。
 *  字段名1：指定索引对应字段的名称。
 *  长度：可选参数，用于表示索引的长度。
 *  AES和DESC：可选参数，其中，AES表示升序排列。DESC表示倒序排列。

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

CREATE TABLE t1 (
      id INT,
      name VARCHAR (32),
      age INT (16),
      INDEX (id)
    );

查看数据结构

EXPLAIN SELECT * FROM t1 WHERE id =1;

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

其中，possible\_keys和key为id，则证明已经通过id进行查询。

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

CREATE TABLE t2 (
      id INT,
      name VARCHAR (32),
      UNIQUE INDEX unique_id ( id DESC )
    )

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

只有MyIsam存储引擎支持全文索引。

CREATE TABLE t4 (
      id INT,
      name VARCHAR (32), 
      FULLTEXT INDEX fulltext_name ( NAME DESC ) 
    ) ENGINE = Myisam;

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

CREATE TABLE t5 (
      id INT,
      name VARCHAR (32),
      INDEX single_name (name(20))
    )

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

只有在查询条件中使用了第一个字段时，才会走多列索引。

CREATE TABLE t6 (
      id INT,
      name VARCHAR (32),
      INDEX double_name ( id, NAME(20))
    )

**6. 创建空间索引**

在类型为GEOMETRY的字段上创建空间索引。

创建空间索引时，所在字段的值不能为空值，且表的存储引擎为MyISAM。

CREATE TABLE t7 (
      id INT,
      name GEOMETRY NOT NULL,
      SPATIAL INDEX name(name)
    ) ENGINE = MyISAM;

### 3.2 使用Create Index语句在已经存在的表上创建索引 ###

若想在已经存在的表上创建索引，可以使用`CREATE INDEX`语句。

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名
    ON 表名 (字段名[(长度)] [AES|DESC])

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

CREATE INDEX index_id ON book(bookid);

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

CREATE UNIQUE INDEX unique_id ON book(bookid);

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

CREATE INDEX singleidx ON book(bookid);

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

在表book上，`name`、`address`列，创建名为mulitidx的多列索引。

CREATE INDEX mulitidx ON book(name(20),address(20));

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

CREATE FULLTEXT INDEX fulltextidx ON book(bookid);

**6.空间索引**

在book表的bookid字段，创建名为spatidx的空间索引。

CREATE SPATIAL INDEX spatidx ON book(bookid);

### 3.3 使用 Alter Table 语句在已经存在的表上创建索引 ###

若想在已经存在的表上创建索引，可以使用`CREATE INDEX`语句。

ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX 
    	索引名 (字段名[(长度)] [AES|DESC])

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

ALTER TABLE book ADD INDEX index_id(bookid);

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

ALTER TABLE book ADD UNIQUE uniqueidx(bookid);

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

ALTER TABLE book ADD INDEX singleidx(bookid);

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

为book表`authors`和`info`字段，添加名称为multidx的多列索引

ALTER TABLE book ADD INDEX multidx(authors(20),info(20));

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

ALTER TABLE book ADD FULLTEXT INDEX fulltextidx(info);

**6.空间索引**

ALTER TABLE book ADD SPATIAL INDEX spatidx(spae);

## 四、删除索引 ##

### 4.1使用 Alter Table 删除索引 ###

ALTER TABLE 表名 DROP INDEX 索引名

语句：

ALTER TABLE book DROP INDEX fulltextidx;

### 4.2使用 Drop Index 删除索引 ###

DROP INDEX 索引名 ON 表名;

语句：

DROP INDEX spatidx ON book;

## 五、Xmind整理 ##

[SCDN地址][SCDN]

https://download.csdn.net/download/weixin_44624117/23754601

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAaGFfbHlkbXM_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center]: https://img-blog.csdnimg.cn/5a55071dd63d4ae390c997dd50369640.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBAaGFfbHlkbXM=,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center
[SCDN]: https://download.csdn.net/download/weixin_44624117/23754601