Mysql数据库 索引，事务

Mysql数据库 索引，事务

一.索引

简介

索引是数据库中用于提高查询效率的一种数据结构，它通过预先排序和存储特定列的值，帮助数据库快速定位符合条件的数据行，避免全表扫描。以下是关于索引的核心简介：

1. 核心作用

• 加速查询：通过索引直接定位数据，减少 IO 和 CPU 开销。
• 强制约束：如主键索引和唯一索引确保数据的唯一性。
• 优化排序：避免ORDER BY和GROUP BY时的额外排序操作。

2. 工作原理

• 类似书籍目录：索引存储了列值与数据行位置的映射关系，查询时直接通过索引定位数据，无需逐行扫描。

• 常见数据结构

  ：

  • B-Tree/B+Tree：最常用的索引结构，支持范围查询和有序访问（MySQL 默认）。
  • 哈希索引：基于哈希表，仅支持精确匹配（如 Memory 引擎）。
  • 全文索引：专门用于文本搜索，支持关键词匹配。

3. 常见类型

类型	特点	适用场景
普通索引	加速查询，无特殊限制	经常用于WHERE条件的列
唯一索引	列值唯一（允许 NULL）	邮箱、身份证等唯一性字段
主键索引	特殊的唯一索引，不允许 NULL	唯一标识表中每行数据
复合索引	多列组合，遵循最左前缀原则	多条件查询（如WHERE a=1 AND b=2）
全文索引	支持文本搜索（MATCH AGAINST）	文章、评论等大文本字段
空间索引	优化地理空间数据查询	地图位置查询（如附近的店铺）

4. 使用场景

• 高频查询条件：为WHERE、JOIN、ORDER BY中的列创建索引。
• 唯一性约束：通过唯一索引或主键保证数据不重复。
• 大表优化：数据量超过数万行时，索引效果显著。

5. 注意事项

• 双刃剑：索引会提升查询速度，但增加写操作（INSERT/UPDATE/DELETE）的开销，并占用额外存储空间。
• 最左前缀原则：复合索引必须从最左侧列开始使用（如索引(a,b,c)支持WHERE a=1，但不支持WHERE b=1）。
• 避免过度索引：冗余索引会降低性能，需定期清理。

示例

直接创建普通索引

create index 索引名 on 表名（列名）

mysql> create index id_index on users(id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> show index from users \G        ####查看索引 
*************************** 1. row ***************************Table: usersNon_unique: 1Key_name: id_index   ###索引名Seq_in_index: 1Column_name: idCollation: ACardinality: 3Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment: 
Index_comment: Visible: YESExpression: NULL
1 row in set (0.00 sec)

使用第二种方式创建索引

alter table 表名 add index 索引名 (列名)

mysql> alter table users add index name_index (name) \G
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> show index from users\G
*************************** 1. row ***************************Table: usersNon_unique: 1Key_name: name_indexSeq_in_index: 1Column_name: nameCollation: ACardinality: 3Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment: 
Index_comment: Visible: YESExpression: NULL
1 row in set (0.01 sec)

使用第三种方式创建索引

mysql> create table  users (id int(10),name char(20),age int(10) ,index id_index (id));
Query OK, 0 rows affected, 2 warnings (0.02 sec)mysql> create table  users (id int(10),name char(20),age int(10) ,index id_index (id));
Query OK, 0 rows affected, 2 warnings (0.02 sec)mysql> desc users;    ###该命令也可以看到那个是索引
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | YES  | MUL | NULL    |       |
| name  | char(20) | YES  |     | NULL    |       |
| age   | int      | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)

删除普通索引唯一索引

drop index 索引名 on 表名

mysql> drop index id_index on users;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> show index from users;
Empty set (0.00 sec)

创建主键索引

这个方式跟上面创建普通方式索引方式一样都有3种

mysql> alter table users add primary key (id) \G    ###修改users表中的id为primary key 
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> show index from users \G     ####查看来自users表的索引
*************************** 1. row ***************************Table: usersNon_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: idCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: Index_type: BTREEComment: 
Index_comment: Visible: YESExpression: NULL
1 row in set (0.00 sec)

创建索引原则

高频查询条件列

• 为经常出现在WHERE、JOIN、ORDER BY和GROUP BY子句中的列创建索引。

选择性高的列

• 索引列的值越唯一（选择性高），索引效率越高。
• 避免对低选择性列（如性别、状态码）创建索引，除非表数据量极大。

总结

索引是数据库性能优化的重要手段，但需根据业务需求和查询模式合理设计。通过分析查询语句和数据特征，针对性地创建索引，可显著提升数据库响应速度。

引类型	创建语法示例
普通索引	CREATE INDEX idx_col ON table (column);
唯一索引	CREATE UNIQUE INDEX uniq_col ON table (column);
主键索引	CREATE TABLE t (id INT PRIMARY KEY); 或 ALTER TABLE t ADD PRIMARY KEY;
复合索引	CREATE INDEX idx_col1_col2 ON table (col1, col2);
全文索引	CREATE FULLTEXT INDEX ft_text ON table (text_col);
空间索引	CREATE SPATIAL INDEX sp_geo ON table (geo_col);

二.事务

MySQL 的事务（Transaction）是数据库操作的基本单元，用于保证一组数据库操作要么全部成功执行，要么全部失败回滚，确保数据的一致性和完整性。以下从概念、核心特性、实现机制、使用方法等方面全面解析 MySQL 事务：

一、事务的核心概念与 ACID 特性

1. 事务的定义

事务是一组不可分割的数据库操作集合，例如转账场景中：

• 从账户 A 扣款（操作 1）
• 向账户 B 存款（操作 2）
  这两个操作必须作为一个整体执行，要么都成功，要么都失败。

2. ACID 特性（事务的四大核心属性）

• 原子性（Atomicity）：事务中的操作要么全部完成，要么全部回滚，不会停留在中间状态。
• 一致性（Consistency）：事务执行前后，数据库从一个合法状态转换到另一个合法状态（如转账前后总金额不变）。
• 隔离性（Isolation）：多个事务并发执行时，相互之间不受干扰，如同单线程执行。
• 持久性（Durability）：事务提交后，数据变更会永久保存，即使数据库崩溃也不会丢失。

二、MySQL 事务的实现基础

1. 存储引擎支持

• InnoDB：MySQL 默认支持事务的存储引擎，完全满足 ACID 特性。
• MyISAM：不支持事务，适合只读或简单查询场景。

2. 事务日志（关键实现机制）

• Redo Log（重做日志）：记录事务对数据的修改，用于崩溃恢复时保证持久性。
• Undo Log（回滚日志）：记录事务修改前的数据，用于回滚操作和实现 MVCC（多版本并发控制）。

3. 并发控制：锁与 MVCC

• 锁机制：通过行锁、表锁等避免并发事务冲突（InnoDB 默认使用行锁）。
• MVCC（Multi-Version Concurrency Control）：为每行数据维护多个版本，使读操作无需加锁，提升并发性能（主要用于 RC 和 RR 隔离级别）。

三、事务的隔离级别（Isolation Levels）

事务隔离级别决定了并发事务之间的干扰程度，MySQL 支持 4 种隔离级别（从低到高）：

隔离级别	脏读	不可重复读	幻读	MySQL 默认
Read Uncommitted（读未提交）	允许	允许	允许	否
Read Committed（读已提交）	禁止	允许	允许	否
Repeatable Read（可重复读）	禁止	禁止	允许	是（InnoDB）
Serializable（可串行化）	禁止	禁止	禁止	否

典型问题说明：

• 脏读：事务 A 读取到事务 B 未提交的数据，若 B 回滚，A 读到的数据是无效的。
• 不可重复读：事务 A 多次读取同一数据时，事务 B 修改并提交了该数据，导致 A 前后读取结果不一致。
• 幻读：事务 A 读取符合条件的记录后，事务 B 插入新记录，A 再次查询时发现多了新数据（类似 “幻觉”）。

设置隔离级别（需在事务启动前执行）：

-- 查看当前隔离级别
SELECT @@TRANSACTION_ISOLATION;-- 设置全局隔离级别（重启MySQL后失效）
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;-- 设置当前会话隔离级别（仅对当前连接有效）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

四、事务的操作语法

1. 手动控制事务（推荐方式）

-- 启动事务
START TRANSACTION;  -- 或 BEGIN-- 执行数据库操作
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;-- 检查是否有错误，若无则提交
COMMIT;-- 若发生错误，回滚到事务开始前的状态
ROLLBACK;

2. 自动提交（默认模式）

MySQL 默认开启自动提交（AUTOCOMMIT=1），每条 SQL 语句作为独立事务执行：

-- 查看自动提交状态
SELECT @@AUTOCOMMIT;  -- 1表示开启，0表示关闭-- 临时关闭自动提交（当前会话）
SET AUTOCOMMIT = 0;-- 执行多个操作作为一个事务
UPDATE ...;
UPDATE ...;
COMMIT;  -- 手动提交-- 恢复自动提交
SET AUTOCOMMIT = 1;

3. 保存点（Savepoint）

用于回滚部分事务操作：

START TRANSACTION;UPDATE table1 SET ...;
SAVEPOINT sp1;  -- 设置保存点UPDATE table2 SET ...;
ROLLBACK TO sp1;  -- 回滚到sp1，保留table1的修改
COMMIT;

五、事务的应用场景

1. 金融交易：转账、支付等场景，确保资金变动的一致性。
2. 订单系统：下单时同时扣减库存、生成订单记录，避免库存超卖。
3. 数据批量操作：批量插入、更新时，失败则回滚，避免脏数据。
4. 跨表操作：多表关联更新时，保证数据关联关系的正确性。

六、事务最佳实践

1. 保持事务简短：避免长事务占用锁资源，影响并发性能。

2. 先查询后操作：减少事务内的查询操作，降低锁持有时间。

3. 合理选择隔离级别

   ：

   • 读多写少场景：使用Repeatable Read（MySQL 默认）。
   • 高并发写场景：考虑Read Committed，并配合乐观锁。

4. 错误处理：确保事务中发生异常时能正确回滚（如程序中捕获异常并执行ROLLBACK）。

5. 避免大事务：对批量数据操作，采用分批提交（如每 1000 条提交一次）。

七、事务与锁的关系

• 事务通过锁实现隔离性，但不同隔离级别下锁的行为不同：
  • Serializable：对所有读取的行加锁，完全串行化执行，性能最差。
  • Repeatable Read（InnoDB 默认）：通过 MVCC 和间隙锁（Next-Key Lock）防止幻读，性能较好。
• 锁超时与死锁：
  • 长时间等待锁会触发Lock wait timeout错误，需调整innodb_lock_wait_timeout参数。
  • 死锁时 MySQL 会自动回滚其中一个事务，应用层需做好重试逻辑。

总结

MySQL 事务是保证数据一致性的核心机制，通过 ACID 特性和隔离级别确保并发操作的正确性。在实际开发中，需根据业务场景选择合适的隔离级别，合理控制事务范围，并做好错误处理，以平衡性能与数据可靠性。