mysql五种索引类型---实操版本

ゞ浴缸里的玫瑰 2023-10-09 15:47 62阅读 0赞

#### mysql索引 ####

*  一：背景
 *   *  1.1 故事背景
     *  1.2 什么是索引
 *  二：前期准备
 *   *  2.1 创建一个一百万条数据的表
 *  三：实操过程
 *   *  3.1 主键索引 primary
     *  3.2 唯一索引 unique
     *   *  3.2.1 对应语句
         *  3.2.2 执行效果：
     *  3.3 普通索引
     *   *  3.3.1 对应语句
         *  3.3.2 执行效果
         *   *  3.2.2 未创建普通索引
             *  3.2.3 创建普通索引
     *  3.4 全文索引 fulltext
     *   *  3.4.1 对应语句
         *  3.4.2 执行效果
     *  3.5 组合索引
     *   *  3.5.1 对应语句
         *  3.5.2 执行效果
 *  四：总结&提升
 *   *  4.1 如何选择索引
     *  4.2 索引的好处
     *  4.3 总结

## 一：背景 ##

### 1.1 故事背景 ###

最近学习了Mysql的索引，索引对于Mysql的高效运行是非常重要的，正确的使用索引可以大大的提高MySql的检索速度。通过索引可以大大的提升查询的速度。不过也会带来一些问题。比如会降低更新表的速度（因为不但要把保存数据还要保存一下索引），占用磁盘空间等问题。

### 1.2 什么是索引 ###

索引是数据库中的一种数据结构，用于提高查询速度和效率。它在表的一个或多个列上建立索引结构，使得数据库可以快速定位和访问数据，而无需扫描整个数据集。

## 二：前期准备 ##

### 2.1 创建一个一百万条数据的表 ###

1.  创建一个表，如果有测试的表也可以用(把主键id设置为自增)
2.  如果给字段添加的值有中文 需要将字符集改为UTF-8的格式
3.  保存存储过程，用来添加100w条数据（字段和数据可以自己修改）

delimiter $$ # 定义结束符
    drop procedure if exists addTestData; # 存储过程名叫：addTestData
    create procedure addTestData()
    begin
    declare number int;
    set number = 1;
    while number <= 1000000 #插入N条数据
    do
    insert into test(id,user_name,`password`,text) #需要添加值得字段
    values(null,concat('姓名_',number),concat('密码：',number),concat('账号',number,'密码',number)); # 对应字段的值 
    set number = number + 1;
    end
    while;
    end $$

1.  调用存储过程来添加100w条数据  
    call addTestData();
2.  执行的过程中会比较慢，大家可以使用select语句 查看创建了多少条数据了

select count(id) from test

## 三：实操过程 ##

### 3.1 主键索引 primary ###

mysql提前给我们创建好了，不需要我们自己创建主键索引。  
主键索引（Primary Key Index）：主键索引是一种用于唯一标识表中每一行数据的索引。它要求索引列的值是唯一的且不为空，每个表只能有一个主键索引。主键索引可以加速对表中数据的唯一标识和访问，以及支持表的引用完整性约束。

### 3.2 唯一索引 unique ###

唯一索引（Unique Index）：唯一索引要求索引列的值是唯一的，不允许重复值存在。与主键索引不同，一个表可以有多个唯一索引。唯一索引可以加速对数据的唯一性检查，提高查询效率。

#### 3.2.1 对应语句 ####

查询语句：
    SELECT * FROM test where user_name ='姓名_19'
    创建索引语句：
    create unique index username ON test(user_name)

#### 3.2.2 执行效果： ####

![在这里插入图片描述][e56788d094884131a8d8a332c15a9cdf.png]  
![在这里插入图片描述][c9c579119dfd45619a58fea4d143558b.png]

### 3.3 普通索引 ###

普通索引（Non-Unique Index）：普通索引（也称为非唯一索引）是最基本的索引类型。它允许重复值存在，并且一个表可以有多个普通索引。普通索引可以加速对数据的搜索和过滤，提高查询性能。

#### 3.3.1 对应语句 ####

查询语句
    SELECT * FROM test WHERE `password`='密码：55'
    创建普通索引语句
    create index password on test( password )

#### 3.3.2 执行效果 ####

##### 3.2.2 未创建普通索引 #####

观察下面的图片可以看出，如果我们不创建索引的话，查询密码为55的数据需要0.649s  
![在这里插入图片描述][05772f451d3043e5b92a743dea1c808f.png]

##### 3.2.3 创建普通索引 #####

观察下面图片，我们发现，添加普通索引之后时间降到了0.104s  
![在这里插入图片描述][020cbf8207f347fe8bb71948fa259c63.png]

### 3.4 全文索引 fulltext ###

全文索引（Full-Text Index）：全文索引用于对文本数据进行搜索，例如文章、描述等。它基于词汇分析，可以快速找到包含关键词的文本数据。全文索引支持模糊匹配、词干化、同义词扩展等功能，用于实现高效的全文搜索。

#### 3.4.1 对应语句 ####

查询语句
    SELECT * FROM test where text LIKE '%郝立琢'
    创建全文索引语句
    create fulltext index text on test(text);
    创建全文索引后的查询语句
    select * from test where match(text) against('郝立琢');

#### 3.4.2 执行效果 ####

![在这里插入图片描述][5cf4e148cd2b4f7cbfc05d4e0e78ed00.png]  
![在这里插入图片描述][470aa834479c4668979610d11bb78c3f.png]

### 3.5 组合索引 ###

组合索引（Composite Index）：组合索引是基于多个列创建的索引。它可以包含两个或更多列，并按照指定的列顺序建立索引。组合索引可以加速多列条件查询，并且可以节省存储空间。但需要注意，组合索引的创建和维护成本较高，且只有在查询中涉及到索引的前缀列时才能发挥作用。

#### 3.5.1 对应语句 ####

查询语句
    SELECT * FROM `test` where user_name="姓名_10" AND `password`="密码：10"
    创建索引
    create index username、password on test (user_name, password)

#### 3.5.2 执行效果 ####

![在这里插入图片描述][fa5d40df88ad45a98177c085c7613093.png]  
![在这里插入图片描述][750d7db3e16b4b428b3bb4d9949cb86b.png]

## 四：总结&提升 ##

### 4.1 如何选择索引 ###

在选择索引时需要考虑以下几点：

1.  选择经常被查询的列进行索引，这样可以最大程度地提高查询性能。
2.  对于连接表的列，也应考虑创建索引，以加速表之间的关联操作。
3.  避免过多索引，因为过多的索引会增加存储空间和写操作的开销，并增加索引维护的复杂性。
4.  根据查询的类型选择合适的索引类型。例如，B树索引适用于范围查询和排序操作，哈希索引适用于等值查询，全文索引适用于文本搜索。

### 4.2 索引的好处 ###

*  索引的作用是加快数据库查询的速度，特别是对于大型数据集和复杂查询来说，索引可以显著减少查询的执行时间。
 *  通过索引，数据库可以直接定位到满足查询条件的数据，而不需要逐条遍历整个表。

### 4.3 总结 ###

本文介绍了索引，并且给出了具体的例子，在开发过程中一定要学会并且用好索引，提高我们查询的速度。

[e56788d094884131a8d8a332c15a9cdf.png]: https://img-blog.csdnimg.cn/e56788d094884131a8d8a332c15a9cdf.png
[c9c579119dfd45619a58fea4d143558b.png]: https://img-blog.csdnimg.cn/c9c579119dfd45619a58fea4d143558b.png
[05772f451d3043e5b92a743dea1c808f.png]: https://img-blog.csdnimg.cn/05772f451d3043e5b92a743dea1c808f.png
[020cbf8207f347fe8bb71948fa259c63.png]: https://img-blog.csdnimg.cn/020cbf8207f347fe8bb71948fa259c63.png
[5cf4e148cd2b4f7cbfc05d4e0e78ed00.png]: https://img-blog.csdnimg.cn/5cf4e148cd2b4f7cbfc05d4e0e78ed00.png
[470aa834479c4668979610d11bb78c3f.png]: https://img-blog.csdnimg.cn/470aa834479c4668979610d11bb78c3f.png
[fa5d40df88ad45a98177c085c7613093.png]: https://img-blog.csdnimg.cn/fa5d40df88ad45a98177c085c7613093.png
[750d7db3e16b4b428b3bb4d9949cb86b.png]: https://img-blog.csdnimg.cn/750d7db3e16b4b428b3bb4d9949cb86b.png