Mysql数据库索引，事务-EW帮帮网

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: 0

mysql> show index from users \G        ####查看索引 
*************************** 1. row ***************************
        Table: users
   Non_unique: 1
     Key_name: id_index   ###索引名
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 3
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: 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: 0

mysql> show index from users\G
*************************** 1. row ***************************
        Table: users
   Non_unique: 1
     Key_name: name_index
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 3
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: 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: 0

mysql> 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: 0

mysql> show index from users \G     ####查看来自users表的索引
*************************** 1. row ***************************
        Table: users
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: 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;

五、事务的应用场景

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

六、事务最佳实践

保持事务简短：避免长事务占用锁资源，影响并发性能。
先查询后操作：减少事务内的查询操作，降低锁持有时间。
合理选择隔离级别

：
- 读多写少场景：使用Repeatable Read（MySQL 默认）。
- 高并发写场景：考虑Read Committed，并配合乐观锁。
错误处理：确保事务中发生异常时能正确回滚（如程序中捕获异常并执行ROLLBACK）。
避免大事务：对批量数据操作，采用分批提交（如每 1000 条提交一次）。

七、事务与锁的关系

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

总结

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

Mysql数据库索引，事务