关于mysql的事务和索引

1. 事务四大特性（ACID）

• 原子性：事务的操作要么全部成功，要么全部失败回滚，不可分割。

• 一致性：事务执行前后，数据必须满足业务规则（如账户总额不变）。

• 隔离性：多个并发事务之间互不干扰。

• 持久性：事务提交后，对数据的修改永久保存，即使系统故障也不丢失。

2. 并发事务的问题

• 脏读：读到其他事务未提交的数据，可能被回滚。

• 不可重复读：同一事务内多次读取同一数据，结果不一致（数据被其他事务修改）。

• 幻读：同一事务内多次查询同一范围，结果集不同（其他事务新增或删除了数据）。

3. 索引优缺点

• 优点：

  • 大幅提高查询速度（尤其WHERE、JOIN、ORDER BY）。

  • 唯一索引保证数据唯一性。

• 缺点：

  • 占用额外存储空间。

  • 增删改操作需维护索引，可能降低写性能。

  • 不合理的索引设计可能拖慢查询（如过多索引导致优化器选择困难）。

4. 索引类型

• 主键索引：唯一且非空，表自动创建。

• 唯一索引：列值唯一，允许NULL。

• 普通索引：无唯一性约束，加速查询。

• 全文索引：支持文本内容的模糊搜索（如LIKE '%keyword%'）。

• 组合索引：多列联合索引，遵循最左前缀原则。

5. 索引存储形式分类

聚集索引 ： 必须有 ,而且只 有一个

二级索引： 可以存在多个

6. SQL分析工具

• EXPLAIN：分析执行计划，关注type（扫描方式）、key（使用的索引）、rows（扫描行数）等字段。

• 慢查询日志：记录执行时间超过阈值的SQL。

• Profiler工具：如MySQL的SHOW PROFILE，查看SQL执行各阶段耗时。

• 第三方工具：如Percona Toolkit、pt-query-digest分析慢查询。

7. SQL优化实践

• 索引优化：

  • 为高频查询条件建索引，避免全表扫描。

  • 使用覆盖索引（索引包含查询字段，减少回表）。

• 避免SELECT *：只取必要字段，减少I/O和内存消耗。

• 分页优化：

  • 大分页用延迟关联：SELECT * FROM table JOIN (SELECT id FROM table LIMIT 100000,10) t USING(id)。

• 慎用函数操作索引列：如WHERE YEAR(create_time)=2023改为范围查询。

• JOIN替代子查询：减少嵌套查询的临时表开销。

• 事务拆分：大事务拆小，减少锁竞争和回滚开销。

• 定期统计信息更新：如ANALYZE TABLE确保优化器选择正确索引。

示例场景： 某分页查询SELECT * FROM orders LIMIT 100000,10执行缓慢，优化步骤：

1. 用EXPLAIN发现全表扫描。

2. 改为覆盖索引+延迟关联：

   sql

   复制

   下载

   SELECT * FROM orders  
   JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000,10) AS tmp  
   ON orders.id = tmp.id;  

3. 执行时间从2秒降至0.1秒。