当前位置：首页 > ai >正文

MySQL索引和事务

ai 2025/7/3 21:13:55

一.索引

1.作用

索引可以理解为数据库表中数据的目录，就像书籍的目录能让你快速找到想要的内容一样，数据库索引能帮助数据库系统快速定位到表中符合查询条件的数据行，从而显著减少数据库的查询时间，提高查询性能。

CREATE INDEX idx_name ON table_name (column_name);

DROP INDEX idx_name ON table_name;

2.案例

案例一：普通索引提高查询效率

场景描述：

假设有一个电商系统的订单表 orders，该表存储了大量的订单信息，其中包含 order_date 列，业务中经常需要根据订单日期进行查询。

- 创建订单表
CREATE TABLE orders (order_id INT PRIMARY KEY AUTO_INCREMENT,customer_id INT,order_date DATE,total_amount DECIMAL(10, 2)
);- 插入一些示例数据
INSERT INTO orders (customer_id, order_date, total_amount)
VALUES 
(1, '2024-01-01', 100.00),
(2, '2024-01-02', 200.00),
(3, '2024-01-03', 300.00);- 未创建索引时执行查询，使用 EXPLAIN 分析查询执行计划
EXPLAIN SELECT * FROM orders WHERE order_date = '2024-01-02';- 创建普通索引
CREATE INDEX idx_order_date ON orders (order_date);- 创建索引后再次执行查询并分析
EXPLAIN SELECT * FROM orders WHERE order_date = '2024-01-02';

分析：

未创建索引时，数据库可能需要进行全表扫描来查找符合条件的记录，效率较低。
创建索引后，数据库可以利用索引快速定位到 order_date 为 2024-01-02 的记录，减少了查询时间。

二.事务

在 MySQL 里，事务是一组不可分割的数据库操作序列，这些操作要么全部成功执行，要么全部不执行，以此保证数据的一致性和完整性。

1.事务的控制语句

start transaction：用于显式地开始一个事务。
commit：提交事务，将事务中所有的操作结果永久保存到数据库中。
roll back：回滚事务，撤销事务中所有未提交的操作，使数据库恢复到事务开始前的状态。
save point：在事务中设置一个保存点，允许部分回滚事务。
rollback to savepoint：将事务回滚到指定的保存点

2.案例

2.1 案例一：银行转账

场景描述：

在银行系统中，转账操作涉及从一个账户扣款并向另一个账户存款，这两个操作必须作为一个整体执行，要么都成功，要么都失败，以保证账户余额的一致性。

-- 创建账户表
CREATE TABLE accounts (account_id VARCHAR(10) PRIMARY KEY,balance DECIMAL(10, 2)
);-- 插入示例数据
INSERT INTO accounts (account_id, balance)
VALUES ('A', 1000.00), ('B', 2000.00);-- 开启事务
START TRANSACTION;-- 从账户 A 扣除 100 元
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';-- 模拟可能出现的错误，例如网络中断等
-- 可以在这里添加条件判断，如果出现错误则回滚事务-- 向账户 B 存入 100 元
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';-- 提交事务
COMMIT;-- 如果出现错误，使用 ROLLBACK 回滚事务
-- ROLLBACK;-- 查看转账后的账户余额
SELECT * FROM accounts;

分析：

此事务将两个更新操作封装在一起，确保在转账过程中，若任何一个操作失败（如数据库故障、网络问题），整个事务会回滚，保证账户 A 和账户 B 的总金额不变

2.2 案例二：库存管理

场景描述：

在电商系统中，当用户下单购买商品时，需要同时减少商品库存和增加订单记录。这两个操作必须同时成功，否则会出现数据不一致的情况。

-- 创建商品表
CREATE TABLE products (product_id INT PRIMARY KEY,product_name VARCHAR(100),stock INT
);-- 创建订单表
CREATE TABLE orders (order_id INT AUTO_INCREMENT PRIMARY KEY,product_id INT,quantity INT
);-- 插入示例数据
INSERT INTO products (product_id, product_name, stock)
VALUES (1, 'Product A', 10);-- 开启事务
START TRANSACTION;-- 减少商品库存
UPDATE products SET stock = stock - 2 WHERE product_id = 1;-- 模拟可能出现的错误
-- 例如，库存不足的情况可以在这里进行判断，如果库存不足则回滚事务-- 增加订单记录
INSERT INTO orders (product_id, quantity) VALUES (1, 2);-- 提交事务
COMMIT;-- 如果出现错误，使用 ROLLBACK 回滚事务
-- ROLLBACK;-- 查看更新后的商品库存和订单记录
SELECT * FROM products;
SELECT * FROM orders;

分析：

该事务保证了商品库存的减少和订单记录的增加是一个原子操作。若在减少库存后，插入订单记录时出现错误，事务会回滚，库存数量会恢复到原来的值。

2.3 案例三：使用保存点进行部分回滚

场景描述：

在一个复杂的业务操作中，可能需要对多个表进行更新，当其中某个操作出现问题时，希望能部分回滚到之前的某个状态，而不是整个事务都回滚

-- 创建表
CREATE TABLE table1 (id INT PRIMARY KEY,value VARCHAR(50)
);CREATE TABLE table2 (id INT PRIMARY KEY,value VARCHAR(50)
);CREATE TABLE table3 (id INT PRIMARY KEY,value VARCHAR(50)
);-- 插入示例数据
INSERT INTO table1 (id, value) VALUES (1, 'value1');
INSERT INTO table2 (id, value) VALUES (1, 'value2');
INSERT INTO table3 (id, value) VALUES (1, 'value3');-- 开启事务
START TRANSACTION;-- 更新 table1
UPDATE table1 SET value = 'new_value1' WHERE id = 1;-- 设置保存点
SAVEPOINT my_savepoint;-- 更新 table2
UPDATE table2 SET value = 'new_value2' WHERE id = 1;-- 模拟 table2 更新后出现错误
-- 假设出现错误，需要回滚到保存点
ROLLBACK TO my_savepoint;-- 继续更新 table3
UPDATE table3 SET value = 'new_value3' WHERE id = 1;-- 提交事务
COMMIT;-- 查看更新后的表数据
SELECT * FROM table1;
SELECT * FROM table2;
SELECT * FROM table3;

分析：

通过设置保存点 my_savepoint，当 table2 更新出现问题时，可以回滚到保存点，table1 的更新操作仍然保留，然后继续执行 table3 的更新操作，最后提交事务，实现了部分回滚的功能

查看全文

http://www.xdnf.cn/news/3683.html