MySQL数据库索引：删除、查看、创建索引

╰半夏微凉° 2022-09-29 14:59 350阅读 0赞

> 索引（index）是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。——百度百科

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

正常的一张表，是无序，如果要进行查询（select）操作，理论上会将表中所有记录进行遍历。并且每次查询都需要从第一条到最后一条挨个查找，以防查漏。这样显然是低效的。  
为了解决这个问题，引入了索引的概念。索引是将记录按照某个关键字段（key）进行排序，因此，**索引的存在是为了便于检索，使得检索查询更加的快速、高效。**

## **0 索引的基本指令** ##

<table> 
 <thead> 
  <tr> 
   <th>操作</th> 
   <th>命令</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>创建索引</td> 
   <td><code>CREATE[UNIQUE] INDEX 索引名 ON 表名(列名 [,列名]);</code></td> 
  </tr> 
  <tr> 
   <td>查看索引</td> 
   <td><code>SHOW INDEX FROM 表名;</code></td> 
  </tr> 
  <tr> 
   <td>删除索引</td> 
   <td><code>DROP INDEX 索引名 ON 表名;</code></td> 
  </tr> 
  <tr> 
   <td>查看索引使用情况</td> 
   <td><code>EXPLAIN SELECT ...;</code></td> 
  </tr> 
 </tbody> 
</table>

## **1 索引的结构** ##

最常用的一种保存索引的结构是B树（Balanced Tree，平衡树），是一种像枝叶扩散的树状结构。

![索引][SouthEast]

图片来自百度百科。  
通过这样的树状结构，原本需要遍历所有的记录，进行n次查询，现在最多只要  *l**o**g*2(*n*) 次查询。

## **2 索引操作指令** ##

**2.1 创建索引**`CREATE INDEX`  
创建索引的代码为：

CREATE INDEX 索引名 ON 表名(列名);

**2.2 查看索引**`show`  
查看索引代码：

SHOW INDEX FROM 表名;

**2.3 删除索引**`DROP INDEX`  
删除索引的代码：

DROP INDEX 索引名 ON 表名;

eg:为表employee的lname\_pinyin创建一个索引：

>CREATE INDEX idx_lpy ON employee(lname_pinyin);

![创建和查看索引][SouthEast 1]

**2.4 创建多列复合索引**

eg:为表employee的lname\_pinyin，fname\_pinyin创建一个复合索引：

>CREATE INDEX idx_lpy ON employee(lname_pinyin,fname_pinyin);

**2.5 创建唯一性索引**  
如果对index制定了UNIQUE关键字，则表示创建了不可重复的索引。  
如为表employee的fname创建一个唯一性索引：

>CREATE UNIQUE INDEX idx_lpy ON employee(fname);

> *当创建了唯一性索引，插入和该列相同的记录，系统会报错。*  
> *如果已有数据重复，创建唯一性索引也会报错*  
> *但是，如果创建了一个复合的唯一性索引，则只要两列不完全相同，数据可以成功插入*

![这里写图片描述][SouthEast 2]  
如图，创建了以fname为关键字的索引，“郭美美”就无法插入，系统会提示报错。  
创建以lname为关键字的索引，由于lname有重复数据，所以也无法创建该索引。  
而如果创建一个复合索引，以fname和lname,则创建成功。  
**2.6 分析索引优势**  
通过EXPLAIN可以确认索引的使用情况；

>EXPLAIN SELECT ...;

eg：查看检索查询lname\_pinyin的情况；  
然后删除索引，再次查看检索查询name\_pinyin 的情况：

>EXPALIN SELECT * FROM employee WHERE lname_pinyin='wang'\G
    ...
    >DROP INDEX idx_pinyin ON employee;
    ...
    >EXPALIN SELECT * FROM employee WHERE lname_pinyin='wang'\G
    >...

![比较索引优劣势][SouthEast 3]  
我们可以看到，当建立索引时，遍历次数为2；而当删除索引后，遍历次数为9，和表中记录数相同。  
因此，回到之前所讲：

> *索引可以大大提高检索的效率。*

[SouthEast]: /images/20220708/24c3e96e050a47c28257ba3f0ce9ecf2.png
[SouthEast 1]: /images/20220708/c70767ef37cb4c6d9d0ff344d2f2d006.png
[SouthEast 2]: /images/20220708/ffa99fa135d6459a9d43883b0d3858ae.png
[SouthEast 3]: /images/20220708/08d3041051644d8ca37df082839d34bb.png