MySQL database indexes and using unique indexes to achieve idempotence

Foreword

This article is mainly about MySQL database index and the use of unique indexes to achieve idempotence. If there is anything that needs improvement, please point it out

About the author: Hello everyone, I am Qing Jin
Blog homepage: CSDN homepage to tell stories
Quote of the day: Work harder and be better

Article directory

Foreword
**Table of contents**
- 1. MySQL index
- - 1.1 The concept of index
  - - Advantages of indexing
    - Disadvantages of indexing
  - 1.2 Classification of indexes
  - 1.3 Index usage scenarios
  - 1.4 Index failure scenario
- 2. Interface idempotence
- What is idempotence
- Usage scenarios for idempotence
- Design idempotent services
- - - Optimistic locking (can only be used for update operations, additional fields need to be added to the table)
    - - Database unique primary key (can only be used for insert operations; can only be used in scenarios where a unique primary key exists)
End of article

1. MySQL index

1.1 The concept of index

What is an index? An index is a sorted, fast search data structure.

Advantages of indexing

1. Improve the efficiency of data retrieval and reduce the IO cost of the database.

2. Sort data through index columns to reduce the cost of data sorting and reduce CPU consumption.

Disadvantages of Index

1. Although the index greatly improves the query speed, it also reduces the speed of updating the table, such as INSERT, UPDATE and DELETE on the table. Because when updating the table, MySQL not only needs to save the data, but also save the index file. Every time a field that adds an index column is updated, the index information will be adjusted after the key value changes caused by the update.

2. In fact, the index is also a table. This table saves the primary key and index fields and points to the records of the entity table, so the index column also takes up space.

1.2 Classification of indexes

Divided from the data structure: hash index, B-tree index, B + tree index.

Divided from the functional level: ordinary index, unique index, primary key index, joint index.

Ordinary index: that is, an index only contains a single column, and a table can have multiple single-column indexes.

Unique index: The value of the index column must be unique, but null values are allowed.

Primary key index: A special unique index that does not allow null values. Generally, a primary key index is created at the same time when creating a table;

Union index: Multiple column values form an index, specifically used for combined searches.
\t
Divided from physical storage: clustered index, non-clustered index.

1.3 Index usage scenarios

Suitable for index creation
1. The primary key automatically creates a unique index;
2. Fields that are frequently used as query conditions should be indexed
3. Fields associated with other tables in the query and foreign key relationships are indexed
4. The choice of single key/combination index, the combination index is more cost-effective
5. Sorting fields in queries. If the sorting fields are accessed through indexes, the sorting speed will be greatly improved.
6. Statistics or grouping fields in queries

Situations not suitable for creating an index
1. There are too few table records
2. Tables or fields that are frequently added, deleted or modified
3. No index will be created for fields that are not used in the Where condition.
4. Those with poor filtering properties are not suitable for index building.

1.4 Index failure scenario

Index failure caused by fuzzy search
When % is placed before the matching field, the index will not be used, and it will be indexed when it is placed after it.
Index failure caused by OR
The index is not used before and after the or statement, or the fields connected before and after or are not the same.
Index failure caused by operator
If you perform (+, -, *, /,!) on the column, then the index will not be used.
Use not, <>, ! = on the index field (this handles a full table scan)
Index failure caused by type inconsistency
For example, if the column type is a string, the data must be quoted in quotes, otherwise it will be invalid (implicit conversion type)
Index failure caused by function
If you use a function on an indexed column, it will not be indexed.
NOT IN, NOT EXISTS, in, exists causes index failure

2. Interface idempotence

What is idempotence

Definition of idempotence:
- Requesting a resource once and multiple times should have the same result for the resource itself.
- Any multiple executions have the same impact on the resource itself as one execution

Usage scenarios of idempotence

For example:
The front-end submits the form repeatedly:
When filling in some forms, the user completes the submission. In many cases, due to network fluctuations, the user does not receive a successful submission response in time, causing the user to think that the submission was not successful, and then keeps clicking the submit button. At this time, repeated submission of form requests will occur.
Users maliciously commit fraud:
For example, when implementing the function of user voting, if the user repeatedly submits a vote for a user, this will cause the interface to receive the voting information repeatedly submitted by the user, which will cause the voting results to be seriously inconsistent with the facts.
Repeated consumption of messages: When using MQ message middleware, if an error occurs in the message middleware and consumption information is not submitted in time, repeated consumption will occur.

Design idempotent services

Optimistic locking (can only be used for update operations, additional fields need to be added to the table)

First, we create an orders table named “orders” with the following structure:

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

In this table, we define the order’s ID, order number, status, and version number fields. Among them, the version number field is used to implement optimistic locking.

Next, assume that the SQL statement we need to update the order status is as follows:

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

Since the ABA problem will cause optimistic locking to fail, as long as the version value is guaranteed to increase automatically, ABA problems will not occur.

The unique primary key of the database (can only be used for insert operations; can only be used in scenarios where a unique primary key exists)

Suppose we have an entity class called “Product” that represents product information. In this entity class, we define a distributed ID as the unique primary key. Assuming the database is MySQL, we can use a distributed ID of type Long. Here is sample code:

@Entity
public class Product {<!-- -->
    private Long id; // Use auto-incrementing primary key

    private Long distributedId; // Distributed ID as the unique primary key
    
}

Next, we need to create a database table to store product information. In MySQL, you can use the following SQL commands to create the corresponding table structure:

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

Ensure the uniqueness of distributed_id through UNIQUE constraint. This ensures that the value of this field is globally unique in a distributed environment.

Finally, in the business logic of Spring Boot, when we need to insert product information, we can first pass distributed_id
Query whether the record already exists, perform an update operation if it exists, and perform an insert operation if it does not exist, thereby achieving idempotence.