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

MySQL 索引和事务

ai 2025/7/1 17:50:57

目录

一、MySQL 索引介绍

1. 索引概述

2. 索引作用

3. 索引的分类

（1）普通索引（INDEX）

（2）唯一索引（UNIQUE）

（3）主键索引（PRIMARY KEY）

（4）组合索引（复合索引，最左前缀原则）

（5）全文索引（FULLTEXT）

（6）创建索引的原则

（7）查看索引

（8）删除索引

（9）添加索引

二、MySQL 事务

1. 事务概述

2. 事务特性（ACID）

3. 事务的控制语句

(1) 开启事务

(2) 提交事务

(3) 回滚事务

(4) 保存点（Savepoint）

(5) 自动提交模式

4. 事务的处理方法

(1) 基于锁的并发控制

(2) 多版本并发控制（MVCC）

(4) 避免长事务

一、MySQL 索引介绍

1. 索引概述

索引是数据库中用于快速查找数据的一种 数据结构（类似书籍的目录）。通过索引，MySQL 可以跳过逐行扫描，直接定位到目标数据，显著提高查询效率。

  • 本质：通过减少磁盘 I/O 操作，提升查询效率

  • 存储位置：索引与表数据分离存储（通常存储在磁盘或内存）。

  • 代价：索引会占用额外存储空间，且对增、删、改操作有一定性能影响（需维护索引结构）。

2. 索引作用

  • 加速查询：快速定位符合 WHERE 条件的数据。

  • 加速排序与分组：优化 ORDER BY、GROUP BY 操作。

  • 实现唯一约束：唯一索引保证列值的唯一性。

  • 减少表扫描：避免全表扫描，降低服务器负载。

3. 索引的分类

（1）普通索引（INDEX）

  • 定义：最基本的索引类型，无唯一性限制。

  • 适用场景：频繁作为查询条件的列

  • 创建语法：

    create index 索引名 on 表(目标做索引列);   #创建索引（默认普通索引）
alter table 表名 add index 索引名(目标做索引列);   #添加索引

  • 示例

    create index aaa on users(user_name(20));
alter table users add index bbb(user_pass(50));
（2）唯一索引（UNIQUE）

  • 定义：索引列的值必须唯一（允许 NULL，但只能出现一次 NULL）。

  • 适用场景：避免重复值的列（如邮箱、手机号）。

  • 创建语法：

    create unique index 索引名 on 表名（列);  #创建唯一索引
alter table 表名 add unique 索引名(索引列);   #添加唯一索引
（3）主键索引（PRIMARY KEY）

  • 定义：特殊的唯一索引，不允许 NULL，每张表只能有一个主键。

  • 适用场景：唯一标识记录的列（如 id）。

  • 创建语法：

    primary key (列)   #主键索引选项
alter table 表名 ADD primary key (列);   #语法#直接创建索引，索引可以同时创建
create table t1 (id int(10),title char(25),time int(10),primary key (id),index aaa (title(25)));
（4）组合索引（复合索引，最左前缀原则）

  • 定义：对多个列联合创建的索引，查询时需遵循 最左前缀原则。

  • 创建语法：

    #示例：
create table t2 (name char(10),age int(3),sex tinyint(1),index ddd(name,age,sex));  #组合索引select * from t2 where name='zhangsan' and age=20 and sex=0;  #最左前缀搜索的顺序必须与索引顺序保持一致
（5）全文索引（FULLTEXT）

  • 定义：针对文本内容（如 TEXT 类型字段）的索引，支持全文搜索。

  • 适用场景：模糊搜索大段文本（如文章内容）。

  • 创建语法：

    create fulltext index 索引名 on 表名(目标索引列)；  #全文索引
alter table 表名 add fulltext 索引名 (目标列);   #添加索引
（6）创建索引的原则

  • 选择性高的列：列值重复率低（如用户 ID）。

  • 频繁查询的列：WHERE、JOIN、ORDER BY 涉及的列。

  • 避免过多索引：索引会占用磁盘空间，降低写操作性能。

  • 小字段优先：减少索引体积，提高查询效率。

    • 数据库输入四大约束原则：
      1.实体完整性（主键）
      2.域完整性（int）
      3.引用完整性（外键--》主键）
      4.用户自定完整性（len (身份证号) =18）

（7）查看索引
#查看索引内容
show create table 表名\G
show keys from 表名\G
show index from 表名\G

        

字段解析： 

 - “Non_unique”为1，表示该索引不是唯一索引，即索引列的值可以重复。
 - “Key_name”为“aaa”，即索引的名称是“aaa”。
 - “Seq_in_index”为1，说明此列在索引中的顺序是第1位。
 - “Column_name”为“user_name”，表示该索引是基于“user_name”列创建的。
 - “Collation”为“A”，代表索引的排序规则，“A”可能表示升序排序。
 - “Cardinality”为0，指索引中唯一值的估计数，这里为0。
 - “Sub_part”为“NULL”，说明没有对列的部分进行索引。
 - “Packed”为“NULL”，表示索引没有被压缩。
 - “Null”为“YES”，意味着“user_name”列可以包含空值。
 - “Index_type”为“BTREE”，表明该索引的类型是B树索引。
 - “Comment”为空，无额外注释。
 - “Index_comment”为空，无索引相关注释。
 - “Visible”为“YES”，表示该索引是可见的。
 - “Expression”为“NULL”，说明不是基于表达式的索引。最后“1 row in set (0.00 sec)”表示查询返回了1行数据，查询耗时0.00秒。 

（8）删除索引
drop index 索引名 on 表名;   #删除索引
alter table 表名 drop index 索引名；  #删除索引
ALTER TABLE table_name DROP PRIMARY KEY;   #删除主键索引
（9）添加索引
#添加普通索引
alter table 表名 add index 索引名(目标列)；
#添加唯一索引
ALTER TABLE table_name ADD UNIQUE INDEX index_name (column);

二、MySQL 事务

1. 事务概述

        定义：
        事务是数据库操作的逻辑单元，包含一组 SQL 语句，要么全部成功，要么全部失败。

2. 事务特性（ACID）

特性说明
原子性 (Atomicity)事务中的操作要么全部完成，要么全部不执行（通过 Undo Log 实现）。
一致性 (Consistency)事务前后数据库状态必须合法（如约束、外键）。
隔离性 (Isolation)并发事务之间互不干扰（通过锁和 MVCC 实现）。
持久性 (Durability)事务提交后数据永久保存（通过 Redo Log 实现）。

3. 事务的控制语句

(1) 开启事务
BEGIN;          -- 显式开启事务
-- 或
START TRANSACTION;  -- 功能同上
(2) 提交事务
COMMIT;         -- 提交事务，确认所有操作生效
(3) 回滚事务
ROLLBACK;       -- 回滚事务，撤销所有未提交的操作
(4) 保存点（Savepoint）
SAVEPOINT 保存点名称;     -- 定义保存点
ROLLBACK TO 保存点名称;   -- 回滚到指定保存点
RELEASE SAVEPOINT 保存点名称;  -- 删除保存点
(5) 自动提交模式

关闭自动提交：

SET autocommit = 0;  -- 后续操作需手动提交或回滚

开启自动提交：（默认开启状态） 

SET autocommit = 1;

4. 事务的处理方法

（1）用 BEGIN ，ROLLBACK ，COMMIT
begin 开始一个事务
rollback  事务回滚
commit  事务确认
（2）用 SET 来改变 MYSQL 的自动提交模式
set autocommit=0  禁止自动提交
set autocommit=1  开启自动提交
(1) 基于锁的并发控制

通过锁机制实现事务隔离性：

  • 共享锁（S Lock）：读锁，允许其他事务读，禁止写。

    SELECT ... LOCK IN SHARE MODE;

  • 排他锁（X Lock）：写锁，禁止其他事务读写。

    SELECT ... FOR UPDATE;
  • 示例场景（以 账户表 和 订单表 为例）： 
    BEGIN;
-- 对账户加排他锁，防止并发修改
SELECT `余额` FROM `账户表` WHERE `用户编号` = 1 FOR UPDATE;
UPDATE `账户表` SET `余额` = `余额` - 100 WHERE `用户编号` = 1;
COMMIT;
(2) 多版本并发控制（MVCC）

InnoDB 通过 MVCC 实现非锁定读（快照读），提升并发性能：

  • 每个事务看到数据的历史版本，避免读写冲突。

  • 依赖 Undo Log 和 Read View 机制实现。

  • 示例场景（默认隔离级别 REPEATABLE READ，使用 账户表）

    -- 事务A
BEGIN;
SELECT `余额` FROM `账户表` WHERE `用户编号` = 1;  -- 结果为 1000-- 事务B 修改并提交
UPDATE `账户表` SET `余额` = 900 WHERE `用户编号` = 1;
COMMIT;-- 事务A 再次查询（结果仍为 1000，因 MVCC 提供一致性视图）
SELECT `余额` FROM `账户表` WHERE `用户编号` = 1;
COMMIT;

(3) 异常处理与回滚策略

在应用程序中需捕获异常并触发回滚（以 Python 为例）：

# Python 伪代码示例
try:cursor.execute("BEGIN")cursor.execute("UPDATE `账户表` SET `余额` = `余额` - 100 WHERE `用户编号` = 1")cursor.execute("INSERT INTO `订单表`(`用户编号`, `金额`) VALUES (1, 100)")cursor.execute("COMMIT")
except Exception as e:cursor.execute("ROLLBACK")print("事务失败:", e)
(4) 避免长事务

  • 监控长事务：

    SHOW ENGINE INNODB STATUS;  -- 查看事务状态

  • 设置超时：

    SET innodb_lock_wait_timeout = 50;  -- 锁等待超时（单位：秒）
http://www.xdnf.cn/news/5224.html

相关文章：

  • 电子电路：光子是不是粒子？
  • 基于OpenCV的人脸识别：FisherFaceRecognizer算法
  • SolidWork-2023 鼠標工程
  • Java集合
  • Qt中的RCC
  • 如何避免在CMD中分段发送问题导致大模型多段回复的问题？
  • Day115 | 灵神 | 二叉树 | 二叉搜索树中的众数
  • Redis 哨兵
  • DIP依赖倒置原则
  • 第十课认识约数
  • 蓝牙身份证阅读器使用Uniapp调用二次开发demo
  • 逆向学习笔记（代码）
  • Linux `uptime` 指令详解与系统监控指南
  • 计算机体系结构一些笔记
  • C++中的继承与多态
  • 【Redis进阶】持久化
  • SpringMVC面试内容
  • 【无标题】I/O复用（epoll）三者区别▲
  • JS DOM操作与事件处理从入门到实践
  • 无线网络设备中AP和AC是什么？有什么区别？
  • 从零开始实现YOLOv8示例
  • 线性表-顺序表（Sequential List）
  • 【vue】vuex实现组件间数据共享 vuex模块化编码 网络请求
  • GRU网络详解
  • 解决使用宝塔Linux部署前后端分离项目遇到的问题
  • 第三章 Freertos智能小车遥控控制
  • 【Web】LACTF 2025 wp
  • 虚拟机风格
  • OpenLayers根据任意数量控制点绘制贝塞尔曲线
  • 关于甲骨文（oracle cloud）丢失MFA的解决方案