MySQL索引篇之索引是什么？

Dear 丶 2022-12-19 11:56 258阅读 0赞

通过几篇文章我们来讨论下索引相关的内容，本文我们先来简单的了解下索引的相关概念。 数据库的版本是 8.0.2

# 索引是什么？ #

现在我们数据库中有一张表，表中的数据有500W条，先在我们没有使用索引的情况下根据`name`来查询

select * from user_innodb where name='波波'

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4NTI2NTcz_size_16_color_FFFFFF_t_70_pic_center]  
  我们看到的查询时间是2.214秒。那如果我们给这张表的`name`字段加上索引会怎么样呢？

ALTER TABLE `user_innodb`
    ADD INDEX `idx_user_name` (`name`) USING BTREE ;

创建索引会比较耗时间，耐心等待会~ 创建成功后我们再次执行相同的查询语句效果如下

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4NTI2NTcz_size_16_color_FFFFFF_t_70_pic_center 1]  
  通过对比发现，有索引的请求比没有索引的情况，效果相差了几十倍上百倍。通过这个案例大家应该可以非常直观地感受到，索引对于数据检索的性能改善是非常大的。  
  那么索引到底是什么呢？为什么可以对我们的查询产生这么大的影响？创建索引的时候发生了什么事情？

## 1.索引的定义 ##

维基百科对数据库索引的定义：

> 数据库索引，是数据库管理系统（DBMS）中一个`排序`的`数据结构`，以协助快速查询、更新数据库表中数据。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4NTI2NTcz_size_16_color_FFFFFF_t_70_pic_center 2]  
  数据是以文件的形式存放在磁盘上面的，每一行数据都有它的磁盘地址。如果没有索引的话，我们要从500万行数据里面检索一条数据，只能依次遍历这张表的全部数据，直到找到这条数据。  
  但是我们有了索引之后，只需要在索引里面去检索这条数据就行了，因为它是一种特殊的专门用来快速检索的`数据结构`，我们找到数据存放的磁盘地址以后，就可以拿到数据了。

## 2.索引的类型 ##

在MySQL的所有中支持三种索引类型

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th align="left">描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Normal</td> 
   <td align="left">普通索引</td> 
  </tr> 
  <tr> 
   <td>Unique</td> 
   <td align="left">唯一索引</td> 
  </tr> 
  <tr> 
   <td>Fulltext</td> 
   <td align="left">全文索引</td> 
  </tr> 
 </tbody> 
</table>

### Normal ###

也叫非唯一索引，是最普通的索引，没有任何的限制

# 直接创建索引
    CREATE INDEX index_name ON table(column(length))
    
    # 修改表结构的方式添加索引
    ALTER TABLE table_name ADD INDEX index_name ON (column(length))
    
    # 创建表的时候同时创建索引
    CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
    `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (title(length))
    )
    
    # 删除索引
    DROP INDEX index_name ON table

### Unique ###

唯一索引要求键值不能重复。另外需要注意的是，主键索引是一种特殊的唯一索引，它还多了一个限制条件，要求键值不能为空。主键索引用primary key创建。

#创建唯一索引
    CREATE UNIQUE INDEX indexName ON table(column(length))
    
    #修改表结构
    ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))
    
    #创建表的时候直接指定
    CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
    `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    UNIQUE indexName (title(length))

### Fulltext ###

MySQL从3.23.23版开始支持全文索引和全文检索，FULLTEXT索引仅可用于 MyISAM 表；他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加。对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

#创建表的适合添加全文索引
    CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
    `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT (content)
    );
    
    #修改表结构添加全文索引
    ALTER TABLE article ADD FULLTEXT index_content(content)
    
    #直接创建索引
    CREATE FULLTEXT INDEX index_content ON article(content)

~好了通过本文的介绍，相信大家对于索引应该有了个基本的认知，那么我们下篇文章就来给大家分析下索引的具体的存储结构！

最后感觉波波给大家分享的文章对你有帮助麻烦点赞加关注哦~ ^ \_ ^

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4NTI2NTcz_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/8a143cb3d0414277974dd87aafdae66a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4NTI2NTcz_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221120/2dd389a03a5a4a2997bb0ac1d42f5d57.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4NTI2NTcz_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221120/2f49ad8a87854857b995e257d7d3c4f0.png