MySQL索引与表结构

MySQL索引

索引的作用

1. 提高查询速度：索引类似于书的目录，通过索引可以快速找到所需数据，而不必扫描整个表。
2. 加速排序和分组操作：索引可以加速ORDER BY和GROUP BY操作。
3. 提高连接操作的性能：在多表连接查询中，索引可以显著提高连接的效率。

索引的类型

1. 主键索引（PRIMARY KEY）：唯一标识表中的每一行，不能包含空值。
2. 唯一索引（UNIQUE INDEX）：保证列中的所有值都是唯一的，可以包含空值。
3. 普通索引（INDEX）：用于加速查询，不要求唯一性。
4. 全文索引（FULLTEXT INDEX）：用于全文搜索，仅适用于CHAR、VARCHAR和TEXT列。
5. 复合索引（Composite Index）：由多个列组成的索引。

索引的使用

• 创建索引：CREATE INDEX index_name ON table_name(column1, column2, ...)
• 删除索引：DROP INDEX index_name ON table_name

索引的代价

• 存储空间：索引需要额外的存储空间。
• 维护开销：每次数据插入、更新或删除时，索引也需要相应更新。

MySQL表结构

表的类型

1. InnoDB：支持事务处理、外键、行级锁，适用于高并发写操作。
2. MyISAM：不支持事务处理和外键，读写速度快，适用于只读或读多写少的场景。
3. MEMORY：数据存储在内存中，速度快，但数据易丢失，适用于临时数据或缓存。
4. CSV：数据以CSV格式存储，易于导出和导入，不适合高并发环境。

表的组成部分

1. 表的定义：表的名称、存储引擎、字符集等。
2. 列的定义：列的名称、数据类型、约束（如NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY）。
3. 数据行：实际存储的数据，每行代表一个记录。

表的操作

• 创建表：CREATE TABLE table_name (column1 datatype constraints, column2 datatype constraints, ...)
• 修改表：ALTER TABLE table_name ADD column_name datatype、ALTER TABLE table_name MODIFY column_name datatype、ALTER TABLE table_name DROP column_name
• 删除表：DROP TABLE table_name
• 插入数据：INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...)
• 查询数据：SELECT column1, column2 FROM table_name WHERE condition
• 更新数据：UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition
• 删除数据：DELETE FROM table_name WHERE condition

事务的ACID特性

1. 原子性（Atomicity）

   • 原子性确保事务中的所有操作要么全部完成，要么全部不完成。如果事务在执行过程中发生错误，所有已执行的操作将被撤销，数据库恢复到事务开始前的状态。

2. 一致性（Consistency）

   • 一致性确保事务在完成后，数据库从一个一致性状态转变到另一个一致性状态。事务的执行不能破坏数据库的完整性约束。

3. 隔离性（Isolation）

   • 隔离性确保同时进行的事务彼此之间不受干扰。一个事务的中间状态对其他事务是不可见的。隔离级别有多种，包括读未提交（Read Uncommitted）、读已提交（Read Committed）、可重复读（Repeatable Read）和串行化（Serializable）。

4. 持久性（Durability）

   • 持久性确保事务一旦提交，其结果将永久保存，即使系统发生故障也不会丢失数据。

事务的操作

• 开始事务：START TRANSACTION或BEGIN
• 提交事务：COMMIT
• 回滚事务：ROLLBACK

示例

假设有一个银行转账操作，需要从账户A转账100元到账户B，这一操作可以用一个事务来保证其原子性和一致性：

START TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A'; UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B'; COMMIT;

如果在两条UPDATE语句之间发生了错误，例如系统崩溃，事务将被回滚：

START TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A'; -- 假设此处发生错误或系统崩溃
 
ROLLBACK;

这样，账户A的余额将恢复到最初的状态，保证数据一致性。

隔离级别

不同的隔离级别提供不同程度的数据隔离，影响并发事务的执行效果：

1. 读未提交（Read Uncommitted）：事务可以读取其他未提交事务的数据，可能导致脏读。
2. 读已提交（Read Committed）：事务只能读取其他已提交事务的数据，防止脏读，但可能导致不可重复读。
3. 可重复读（Repeatable Read）：在一个事务内多次读取同一数据结果一致，防止脏读和不可重复读，但可能导致幻读。
4. 串行化（Serializable）：最高的隔离级别，通过强制事务串行执行，防止脏读、不可重复读和幻读。

高级事务概念

1. 保存点（Savepoint）

   • 保存点允许在一个事务内设置多个回滚点，从而可以部分回滚事务而不是全部回滚。
   • 创建保存点：SAVEPOINT savepoint_name
   • 回滚到保存点：ROLLBACK TO SAVEPOINT savepoint_name
   • 释放保存点：RELEASE SAVEPOINT savepoint_name

2. 事务隔离级别（Isolation Levels）

   • 脏读（Dirty Read）：一个事务可以读取另一个未提交事务修改的数据。
   • 不可重复读（Non-Repeatable Read）：一个事务在读取相同记录时，可能会因为另一个已提交的事务修改而读取到不同的值。
   • 幻读（Phantom Read）：一个事务在执行两次相同的查询时，结果集不同，因为另一个已提交的事务在这期间插入或删除了数据。

3. 分布式事务（Distributed Transaction）

   • 涉及多个数据库或其他资源管理器的事务，通常需要一个协调者来管理事务的原子性和一致性。
   • 两阶段提交协议（2PC）：第一阶段准备提交（Prepare to Commit），第二阶段正式提交（Commit）。

事务使用的最佳实践

1. 使用合适的隔离级别

   • 根据应用的需求选择合适的隔离级别，以在性能和一致性之间取得平衡。
   • 对于大多数应用，Read Committed 是一个常见的选择。

2. 事务的大小

   • 尽量将事务保持在一个合理的大小范围内，以减少锁的持有时间和资源消耗。
   • 避免长时间运行的事务，因为它们可能会持有锁很长时间，影响并发性能。

3. 错误处理

   • 在事务操作中，处理可能发生的错误，并在需要时进行回滚以确保数据一致性。
   • 使用异常处理机制来捕获错误并执行适当的回滚操作。

4. 日志记录

   • 记录事务的开始和结束时间、操作的类型和影响的行数等信息，以便在出现问题时进行排查。