MySQL数据库索引以及使用唯一索引实现幂等性

放风讲故事

已于 2023-11-10 19:29:17 修改

阅读量1.1k

点赞数 45

文章标签：数据库 mysql

于 2023-11-09 09:06:50 首次发布

本文链接：https://blog.csdn.net/2301_80092713/article/details/134294320

版权

📑前言

本文主要是MySQL数据库索引以及使用唯一索引实现幂等性的文章，如果有什么需要改进的地方还请大佬指出⛺️

🎬作者简介：大家好，我是青衿🥇
☁️博客首页：CSDN主页放风讲故事
🌄每日一句：努力一点，优秀一点

在这里插入图片描述

1.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、 UPDATE和DELETE。因为更新表时， MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

2.实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。

1.2 索引的分类

从数据结构上来划分：哈希索引，B树索引，B+树索引。

从功能层次上来划分：普通索引，唯一索引，主键索引，联合索引。

普通索引：即一个索引只包含单个列，一个表可以有多个单列索引。

唯一索引：索引列的值必须唯一，但允许有空值。

主键索引：一种特殊的唯一索引，不允许有空值，一般在建表时同时创建主键索引;

联合索引：多列值组成一个索引，专门用于组合搜索。
	
从物理存贮上来划分：聚簇索引，非聚簇索引。

1.3 索引的使用场景

适合创建索引的情况
1.主键自动建立唯一索引；
2.频繁作为查询条件的字段应该创建索引
3.查询中与其它表关联的字段，外键关系建立索引
4.单键/组合索引的选择问题，组合索引性价比更高
5.查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
6.查询中统计或者分组字段

不适合创建索引的情况
1.表记录太少
2.经常增删改的表或者字段
3.Where 条件里用不到的字段不创建索引
4.过滤性不好的不适合建索引

1.4 索引失效场景

模糊搜索导致的索引失效
当%放在匹配字段前是不走索引的，放在后面才会走索引。
OR引起的索引失效
or语句前后没有同时使用索引，或者or前后连接的不是同一个字段。
运算符导致的索引失效
如果你对列进行了（+，-，*，/，!）, 那么都将不会走索引。
在索引字段上使用not，<>， ! =  (这样处理的是全表扫描)
类型不一致导致的索引失效
比如列类型是字符串，一定要在条件中将数据用引号引用，否则失效（隐式转换类型)
函数导致的索引失效
如果使用函数在索引列，这是不走索引的。
NOT IN、NOT EXISTS、 in 、exists 导致索引失效

2.接口幂等性

什么是幂等性

幂等性定义:
- 一次和多次请求某一个资源对于资源本身应该具有同样的结果
- 任意多次执行对资源本身所产生的影响均与一次执行的影响相同

幂等性的使用场景

例如：
前端重复提交表单：
在填写一些表格时候，用户填写完成提交，很多时候会因网络波动没有及时对用户做出提交成功响应，致使用户认为没有成功提交，然后一直点提交按钮，这时就会发生重复提交表单请求。
用户恶意进行刷单：
例如在实现用户投票这种功能时，如果用户针对一个用户进行重复提交投票，这样会导致接口接收到用户重复提交的投票信息，这样会使投票结果与事实严重不符。
消息进行重复消费：当使用 MQ 消息中间件时候，如果发生消息中间件出现错误未及时提交消费信息，导致发生重复消费。

设计幂等性服务

乐观锁（只能用于更新操作，表中需要额外添加字段）

首先，我们创建一个名为 “orders” 的订单表，结构如下：

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(100) NOT NULL,
    status VARCHAR(20) NOT NULL,
    version INT NOT NULL );

在这个表中，我们定义了订单的 ID、订单号、状态和版本号字段。其中，版本号字段用于实现乐观锁。

接下来，假设我们需要更新订单状态的 SQL 语句如下：

UPDATE orders
SET status = 'NEW_STATUS', version = version + 1
WHERE id = <orderId> AND version = <currentVersion>;

由于ABA问题会导致乐观锁存在失效的情况,只要保证version值自增就不会出现ABA的问题

数据库唯一主键(只能用于插入操作；只能用于存在唯一主键场景）

假设我们有一个名为 “Product” 的实体类，代表产品信息。在这个实体类中，我们定义一个分布式 ID 作为唯一主键。假设数据库为 MySQL，我们可以使用 Long 类型的分布式 ID。以下是示例代码：

@Entity
public class Product {
    private Long id;  // 使用自增主键

    private Long distributedId;  // 分布式 ID，作为唯一主键
    
}

接下来，我们需要创建一个数据库表来存储产品信息。在 MySQL 中，可以使用如下的 SQL 命令来创建对应的表结构：

CREATE TABLE product (
    id INT AUTO_INCREMENT PRIMARY KEY,
    distributed_id BIGINT UNIQUE,
);

通过 UNIQUE 约束来保证 distributed_id 的唯一性。这样就能够保证在分布式环境下，该字段的值是全局唯一的。

最后，在 Spring Boot 的业务逻辑中，当需要插入产品信息时，我们可以先通过 distributed_id
查询是否已经存在该记录，如果存在则进行更新操作，如果不存在则进行插入操作，从而实现幂等性。