MySQL索引与事务

‌什么是索引？‌
索引是数据库中一种特殊的‌数据结构‌，其核心作用是通过预排序和快速定位机制，加速数据的检索效率。从实现层面看，索引是独立于表数据之外的结构（如InnoDB的.ibd文件中的独立页），但它与表数据存在强关联，通过指针映射到实际数据行。
‌索引的底层原理‌
- ‌磁盘I/O优化‌：数据库存储的最小单位是页（通常16KB），索引通过减少磁盘寻址次数提升效率。例如，全表扫描需要逐页读取，而索引通过树形结构可将随机I/O转为顺序I/O。
- ‌数据有序性‌：B+Tree索引通过叶子节点的双向链表实现范围查询优化，非叶子节点存放键值和子节点指针，形成多级目录结构。
- ‌时间复杂度对比‌：无序数据查询时间复杂度为O(n)，哈希索引为O(1)（仅精确匹配），B+Tree索引为O(log n)（支持范围查询）。

二、索引的核心作用解析

‌查询加速器‌
- ‌精确匹配‌：WHERE子句中的等值查询（如id=100）通过索引直接定位数据页。
- ‌范围查询‌：BETWEEN、>等操作利用B+Tree的排序特性快速缩小扫描范围。
- ‌避免全表扫描‌：当选择性（Cardinality）超过30%时，优化器可能选择全表扫描，索引此时失效。
‌排序与分组优化‌
- ‌ORDER BY优化‌：索引天然有序，可避免Filesort操作。例如对(last_name, first_name)的联合索引，可实现ORDER BY last_name, first_name的无排序查询。
- ‌GROUP BY加速‌：分组操作本质上需要排序，索引可消除临时表的创建。
‌覆盖索引（Covering Index）‌
当查询字段全部包含在索引中时，引擎直接从索引页获取数据，无需回表。如索引(a,b)，查询SELECT a,b FROM table可完全由索引覆盖。
‌唯一性约束‌
唯一索引除了加速查询，还通过B+Tree的键值唯一性保证数据完整性，其实现机制是在插入时执行重复值检查。

‌三、索引分类‌

MySQL 索引可从 ‌功能逻辑‌、‌物理实现‌、‌字段特性‌ 和 ‌数据结构‌ 四个维度分类：

分类维度	类型	定义与特性
‌功能逻辑‌	普通索引	无约束，仅加速查询，允许重复值和空值。
	唯一索引	用 `UNIQUE` 定义，列值唯一但允许空值，可存在多个
	主键索引	`PRIMARY KEY` 定义，唯一且非空，每表仅一个，默认聚簇索引
	全文索引	`FULLTEXT` 定义，基于文本内容分词搜索，适用于 `CHAR`/`VARCHAR`/`TEXT` 字段
‌物理实现‌	聚簇索引	数据与索引存储在一起（如 InnoDB 主键索引），检索效率高
‌物理实现‌	非聚簇索引	索引与数据分离存储（如 MyISAM），需二次查找
‌字段个数‌	单列索引	单字段创建索引，支持任意索引类型
‌字段个数‌	联合索引（复合索引）	多字段组合创建索引，遵循最左前缀匹配原则
‌数据结构‌	B+树索引	默认索引结构，支持范围查询、排序和高效读取
	哈希索引	仅支持精确查询（如 `MEMORY` 引擎），不排序，不支持部分匹配
	空间索引	`SPATIAL` 定义，用于地理空间数据类型（如 `POINT` 或 `GEOMETRY`）

‌四、配置命令与语法解析‌

‌1. 基础索引类型‌

索引类型	配置语法	说明
‌主键索引‌	`sql<br>CREATE TABLE t (<br> id INT PRIMARY KEY,<br> ...<br>);`	建表时指定，或通过 `ALTER TABLE t ADD PRIMARY KEY (col);` 添加
‌唯一索引‌	`sql<br>CREATE UNIQUE INDEX idx_name ON t (col);`	字段值必须唯一但可为空
‌普通索引‌	`sql<br>CREATE INDEX idx_name ON t (col);`	无约束，仅加速查询
‌全文索引‌	`sql<br>CREATE FULLTEXT INDEX idx_content ON t (content);`	需支持全文检索的字段类型（如 `TEXT`）
‌空间索引‌	`sql<br>CREATE SPATIAL INDEX idx_geo ON t (geo_col);`	仅支持 `GEOMETRY` 或 `POINT` 等空间数据类型，且字段必须为 `NOT NULL`

‌2. 复合索引与单列索引‌

类型	配置语法	说明
‌单列索引‌	`sql<br>CREATE INDEX idx_email ON users (email);`	单字段加速查询
‌复合索引‌	`sql<br>CREATE INDEX idx_name_age ON users (name, age);`	查询条件需包含最左字段（如 `WHERE name='A' AND age=20`）

‌‌3. 聚簇索引与非聚簇索引

类型	配置方式	说明
‌聚簇索引‌	默认由主键生成，或唯一非空索引替代（若无主键）	数据按索引顺序存储，减少磁盘 I/O
‌非聚簇索引‌	普通索引或唯一索引	索引与数据分离，需回表查询

‌五、索引配置示例‌

‌1. 建表时定义索引‌

CREATE TABLE employees (
 id INT PRIMARY KEY, -- 主键索引（聚簇索引）
 name VARCHAR(50),
 email VARCHAR(100) UNIQUE, -- 唯一索引
 bio TEXT,
 FULLTEXT (bio), -- 全文索引
 INDEX idx_name (name) -- 普通单列索引 
);

‌2. 后期添加索引‌

-- 添加复合索引 
CREATE INDEX idx_dept_salary ON employees (department, salary); 

-- 添加空间索引（需字段为非空空间类型） 
ALTER TABLE locations ADD SPATIAL INDEX idx_coords (coordinates);

六、创建索引的原则依据

数据库建立索引的原则：

确定针对该表的操作是大量的查询操作还是大量的增删改操作；
尝试建立索引来帮助特定的查询。检查自己的sql语句，为那些频繁在where语句中出现的字段建立索引；
尝试建立复合索引来进一步提高系统性能。修复复合索引将消耗更长时间，同时复合索引也占磁盘空间；
对于小型的表，建立索引可能会影响性能；
应该避免对具有较少值的字段进行索引；
避免选择大数据类型的列作为索引。

索引建立的原则：

在经常用作‘过滤器’的字段上建立索引；
在SQL语句中经常进行GROUP BY、ORDER BY 的字段上建立索引；
在不同值较少的字段上不必要建立索引；
对于经常存取的列避免建立索引；
用于联结的列（主键/外键）上建立索引；
在经常存取的多个列上建立复合索引，但要注意复合索引的建立顺序要按照使用的频度来确定；

七、查看索引

语法：mysql>show index from 表名；

mysql>show keys from 表名；

以 renyuan 表为例，查看 renyuan 表的索引内容：

mysql> show index from renyuan\G
******************************************1.row********************************************
         Table:renyuan
    Non unique:0
      Key name: id
  Seq_in_index:1Column name: id
     Collation: ACardinality:0
      Sub part: NULL
        Packed: NULL
          Nu11:
    Index type: BTREE
      Comment :
 Index comment:
       Visible: YEs
    Expression:NULL
1 row in set(0.00 sec)

字段解析：

Table:表的名称。
Non_unique:如果索引不能包括重复词，则为0:如果可以，则为1。
Key_name:索引的名称。
Seq in index:索引中的列序号，从 1 开始。
Column name:列名称。
Collation:列以什么方式存储在索引中。在 MySQL 中，有值'A’(升序)或 NULL(无分类)。
Cardinality:索引中唯一值数目的估计值。通过运行 ANALYZE TABLE 或myisamchk -a 可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。
Sub_part:如果列只是被部分地编入索引，则为被编入索引的字符的数目如果整列被编入索引，则为 NUIL。
Packed:指示关键字如何被压缩。如果没有被压缩，则为NULL。
Nu11:如果列含有 NULL，则含有 YES。如果没有，则该列含有 NO。
Index_type:用过的索引方法(BTREE,FULLTEXT, HASH,RTREE)。
Comment:备注。

八、删除索引

语法：mysql>drop index 索引名 on 表名；

mysql>alter table 表名 drop index 索引名；

MySQL事务

一、核心控制语句详解

1. 事务基础定义

事务(Transaction)是数据库管理系统中的最小逻辑工作单元，由一组原子性操作的SQL语句构成，主要用于处理操作量大，复杂性高的数据。具有"全成功则提交，中途失败则回滚"的核心特性。该机制通过UNDO日志记录操作前镜像实现回滚，REDO日志记录操作后镜像保障持久性。

2. ACID特性深度解析

特性	实现机制	技术保障
原子性(Atomicity)	UNDO日志记录事务前数据状态	事务管理器自动回滚未完成事务
一致性(Consistency)	数据库约束(主键/外键/唯一性)	触发器、存储过程等技术协同工作
隔离性(Isolation)	MVCC多版本并发控制	通过read-view机制实现快照隔离
持久性(Durability)	REDO日志持久化	双写缓冲(doublewrite buffer)确保数据页完整性

3.控制语句

1. 显式事务控制

命令	语法示例	作用	注意事项
开启事务	`START TRANSACTION;`	显式开启一个新事务	等同`BEGIN`命令，但在存储过程中只能使用`START TRANSACTION`
提交事务	`COMMIT;`	持久化所有数据变更	执行后自动释放所有锁资源，注意`COMMIT AND CHAIN`可开启新事务
回滚事务	`ROLLBACK;`	撤销当前会话未提交的所有操作	对DDL操作无效（如ALTER TABLE）
保存点操作	`SAVEPOINT sp1;`	创建事务内部检查点	最大支持128层嵌套，超出后自动覆盖旧保存点
回滚到保存点	`ROLLBACK TO SAVEPOINT sp1;`	撤销指定保存点后的操作	保存点前的操作仍有效，需手动继续提交或回滚

2. 自动提交模式

命令	语法示例	作用	注意事项
查看自动提交状态	`SHOW VARIABLES LIKE 'autocommit';`	显示当前自动提交模式	默认值为1（开启）
关闭自动提交	`SET autocommit=0;`	关闭语句级自动提交	需配合显式`COMMIT`使用，会话级别有效
开启自动提交	`SET autocommit=1;`	恢复每条SQL独立提交	DDL操作仍会隐式提交事务

二、锁控制语句

1. 表级锁操作

命令	语法示例	作用
显式表锁	`LOCK TABLES table_name READ;`	锁定整张表（读模式）
解锁	`UNLOCK TABLES;`	释放当前会话所有表锁

‌锁类型对比‌：

-- 读锁（共享锁） 
LOCK TABLES orders READ; 

-- 写锁（排他锁） 
LOCK TABLES inventory LOW_PRIORITY WRITE;

2. 隐式行级锁

‌InnoDB自动实现‌：通过SELECT ... FOR UPDATE实现排他锁
‌间隙锁‌：防止幻读现象，锁定索引记录间隙
‌死锁检测‌：自动检测并回滚代价较小的事务

三、隔离级别控制

1. 隔离级别设置命令

命令	语法示例	作用
全局隔离级别设置	`SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;`	影响后续所有新连接
会话隔离级别设置	`SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;`	仅当前会话生效

2. 隔离级别对照表

隔离级别	脏读	不可重复读	幻读	锁机制特点
READ UNCOMMITTED	√	√	√	无锁
READ COMMITTED	×	√	√	语句级快照
REPEATABLE READ	×	×	√	事务级快照（默认）
SERIALIZABLE	×	×	×	全表间隙锁

四、事务状态监控

1. 实时监控命令

-- 查看活跃事务 
SELECT * FROM information_schema.INNODB_TRX; 

-- 显示锁等待信息 
SHOW ENGINE INNODB STATUS; 

-- 查看事务隔离级别 
SELECT @@transaction_isolation;

2. 关键性能指标

‌事务吞吐量‌：Com_commit和Com_rollback状态变量
‌锁等待时间‌：Innodb_row_lock_time_avg指标
‌死锁频率‌：Innodb_deadlocks计数器

五、MySQL 事务处理详解

事务是数据库操作的最小逻辑单元，用于确保一组操作要么全部成功，要么全部失败。MySQL 通过事务处理实现 ACID 特性（原子性、一致性、隔离性、持久性），适用于需要数据完整性的场景（如支付、订单处理等）。

‌1、事务处理的核心方法‌

方法/命令	语法/操作	说明
‌开启事务‌	`START TRANSACTION;` 或 `BEGIN;`	显式开始一个新事务，后续操作暂不提交。
‌提交事务‌	`COMMIT;`	确认所有操作生效，数据永久保存。
‌回滚事务‌	`ROLLBACK;`	取消所有未提交的操作，恢复到事务开始前的状态。
‌设置保存点‌	`SAVEPOINT savepoint_name;`	在事务内创建标记点，用于部分回滚。
‌回滚到保存点‌	`ROLLBACK TO savepoint_name;`	回滚到指定保存点，后续操作仍可继续。
‌释放保存点‌	`RELEASE SAVEPOINT savepoint_name;`	删除已定义的保存点。
‌设置自动提交‌	`SET autocommit = 0;` (关闭)	关闭自动提交模式，需手动提交事务（默认为 `1`，即自动提交）。

‌2、事务的隔离级别‌

隔离级别决定了事务之间的可见性和影响程度，MySQL 支持以下四种（默认：REPEATABLE READ）：

隔离级别	脏读	不可重复读	幻读	说明
‌READ UNCOMMITTED‌	✔️	✔️	✔️	最低隔离，事务可读取未提交的数据。
‌READ COMMITTED‌	✖️	✔️	✔️	只能读取已提交的数据（解决脏读）。
‌REPEATABLE READ‌ (默认)	✖️	✖️	✔️	同一事务多次读取结果一致（解决不可重复读）。
‌SERIALIZABLE‌	✖️	✖️	✖️	最高隔离，事务串行执行（解决幻读，但性能最低）。

‌设置隔离级别语法‌：

SET TRANSACTION ISOLATION LEVEL [级别]; 

-- 示例：
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;