mysql-索引特性和事务管理

mysql索引特性

---

一、索引基础与问题

• 索引作用：

  • 提高查询效率（减少磁盘io次数），但会降低插入、更新、删除速度（需维护索引结构）。
  • 常见索引类型：
    • 主键索引（primary key）：唯一且非空，一个表只能有一个。
    • 唯一索引（unique）：列值唯一，可为空，一个表可有多个。
    • 普通索引（index）：允许重复，用于加速非唯一字段查询。
    • 全文索引（fulltext）：针对文本内容检索（仅myisam引擎支持）。

• 无索引的问题：

  • 海量数据下，全表扫描导致查询极慢（如800万条数据查询耗时数秒）。
  • 高并发场景下可能引发性能瓶颈甚至系统崩溃。

---

二、磁盘与mysql交互机制

1. 磁盘基本结构：

   • 数据存储在扇区（默认512字节，部分支持4k）。
   • 定位数据需磁头、柱面、扇区信息（chs），但mysql使用逻辑地址（lba）。

2. mysql与磁盘交互单位：

   • page（页）：mysql的io基本单位为16kb（innodb_page_size=16384）。
   • 优势：减少io次数，利用局部性原理提升效率（如一次加载多条相邻记录）。

3. buffer pool：

   • mysql在内存中开辟缓存池，用于暂存频繁访问的page，减少直接磁盘操作。

---

三、索引数据结构（b+树）

1. b+树核心特性：

   • 分层结构：目录页（存最小键值+指针）与数据页（存实际数据）。
   • 叶子节点链表：支持高效范围查询。
   • 非叶子节点不存数据：单节点可存更多键值，树更矮，减少io次数。

2. b+树 vs b树：

   特性	b+树	b树
   数据存储位置	仅叶子节点存数据	所有节点均可存数据
   叶子节点连接	叶子节点通过链表相连	无连接
   范围查询效率	高（直接遍历链表）	低（需跨节点回溯）

3. 为何选择b+树：

   • 更适合磁盘io场景（减少访问次数）。
   • 支持高效范围查询和排序操作。

---

四、聚簇索引 vs 非聚簇索引

特性	聚簇索引（innodb）	非聚簇索引（myisam）
数据与索引存储	索引与数据共存于.ibd文件	索引（.myi）与数据（.myd）分离
主键索引	叶子节点存完整数据	叶子节点存数据地址
辅助索引	叶子节点存主键值（需回表查询）	叶子节点存数据地址

• 回表查询：
  • innodb中通过辅助索引找到主键，再通过主键索引查找数据，需两次索引扫描。
  • 示例：

    select * from user where name='alice'; -- 先查name的辅助索引，再回表查主键索引  
    

---

五、索引操作细节

1. 创建索引：

   • 主键索引：

     -- 创建表时指定  
     create table user1(id int primary key, name varchar(30));  
     -- 修改表添加  
     alter table user3 add primary key(id);  
     

   • 唯一索引：

     create table user4(id int primary key, name varchar(30) unique);  
     alter table user6 add unique(name);  
     

   • 普通索引：

     create table user8(id int primary key, name varchar(20), index(name));  
     create index idx_name on user10(name);  
     

   • 全文索引（仅myisam）：

     create table articles(id int primary key, title varchar(200), fulltext(title, body)) engine=myisam;  
     

2. 查询索引：

   show keys from user; -- 显示索引详细信息  
   show index from user; -- 简略信息  
   

3. 删除索引：

   alter table user drop primary key; -- 删除主键索引  
   alter table user drop index idx_name; -- 删除普通索引  
   drop index idx_name on user;  
   

4. 全文索引使用：

   select * from articles where match(title, body) against('database'); -- 正确用法  
   

---

六、索引创建原则

• 推荐场景：
  • 频繁作为查询条件的字段（如where、order by）。
  • 唯一性较高的字段（如主键、唯一约束字段）。
• 不推荐场景：
  • 唯一性差的字段（如性别、状态标志）。
  • 更新频繁的字段（维护索引成本高）。
  • 不参与查询的字段。

---

七、实战注意事项

1. 避免全表扫描：

   • 对where条件中的字段加索引。
   • 避免在索引列上使用函数或运算（如where year(date_column)=2023）。

2. 复合索引最左匹配：

   • 索引index(a, b, c)仅对查询条件包含a、a+b、a+b+c有效。

3. 覆盖索引优化：

   • 查询字段全部在索引中时，无需回表（如select id from user where name='alice'）。

---

八、重难点总结

• b+树结构：理解目录页与数据页的分层设计，及其对查询效率的提升。
• 聚簇索引与非聚簇索引：掌握存储差异及回表查询的影响。
• 索引创建与维护：根据业务场景合理选择索引类型，避免过度索引。
• 全文索引限制：仅myisam支持，且对中文不友好，需结合第三方工具（如coreseek）。

---

事务管理

---

1. curd不加控制的问题

• 问题场景：
  客户端a和客户端b同时操作火车票表（tickets），执行以下流程：
  1. 客户端a检查票数 nums > 0，准备卖票，但未执行 update。
  2. 客户端b同时检查票数，同样认为 nums > 0，执行卖票并更新 nums-1。
  3. 客户端a随后执行 update，导致同一张票被卖出两次。
• 根本原因：非原子操作（检查与更新分离），未使用事务保证操作的原子性。

---

2. 事务的acid属性

• atomicity（原子性）：
  • 事务内的操作要么全部成功（提交），要么全部失败（回滚）。
  • 实现方式：通过 undo log 记录操作前的数据状态，失败时回滚到初始状态。
• consistency（一致性）：
  • 事务执行前后，数据库必须保持合法的业务逻辑状态（如账户余额不能为负）。
  • 依赖业务逻辑和事务的原子性、隔离性共同保障。
• isolation（隔离性）：
  • 多事务并发时，操作互不干扰，通过锁和mvcc机制实现。
  • 支持四种隔离级别（见下文）。
• durability（持久性）：
  • 事务提交后，数据永久存储，即使系统崩溃也不丢失。
  • 实现方式：通过 redo log 持久化数据变更。

---

3. 事务引擎支持与操作

• 支持事务的引擎：仅innodb支持，myisam不支持。

• 查看引擎：

  show engines;          -- 表格形式显示引擎信息  
  show engines \g;       -- 行格式显示（更清晰）  
  

  输出示例：

  engine: innodb  
  support: default  
  transactions: yes      -- 支持事务  
  

• 事务提交方式：

  • 自动提交：默认开启（autocommit=1），每条sql独立提交。
  • 手动提交：关闭自动提交后需显式提交或回滚。

    set autocommit=0;    -- 关闭自动提交  
    set autocommit=1;    -- 开启自动提交  
    show variables like 'autocommit';  -- 查看当前状态  
    

---

4. 事务基本操作

• 开始事务：

  start transaction;   -- 或 begin;  
  

• 保存点（savepoint）：

  savepoint save1;             -- 创建保存点  
  rollback to save1;           -- 回滚到保存点  
  release savepoint save1;     -- 删除保存点  
  

• 提交与回滚：

  commit;     -- 提交事务，数据持久化  
  rollback;   -- 回滚到事务起点  
  

• 实验示例：

  -- 创建测试表  
  create table account (  id int primary key,  name varchar(50),  balance decimal(10,2)  
  ) engine=innodb;  -- 事务操作  
  begin;  
  insert into account values (1, '张三', 100);  
  savepoint s1;  
  insert into account values (2, '李四', 200);  
  rollback to s1;    -- 回滚后仅保留张三的记录  
  commit;  
  

---

5. 隔离级别与并发问题

• 四种隔离级别：

  隔离级别	脏读	不可重复读	幻读	加锁方式
  read uncommitted	✔	✔	✔	不加锁
  read committed	✘	✔	✔	行锁（仅提交后释放）
  repeatable read（默认）	✘	✘	✘	行锁 + 间隙锁（防止幻读）
  serializable	✘	✘	✘	表锁（完全串行）

• 设置与查看隔离级别：

  -- 设置全局隔离级别  
  set global transaction isolation level read committed;  -- 设置会话级隔离级别  
  set session transaction isolation level repeatable read;  -- 查看当前隔离级别  
  select @@tx_isolation;  
  

• 实验验证脏读（read uncommitted）：

  1. 终端a：

     set session transaction isolation level read uncommitted;  
     begin;  
     update account set balance=500 where id=1;  -- 未提交  
     

  2. 终端b：

     select * from account;  -- 可读到终端a未提交的数据（脏读）  
     

---

6. mvcc机制与read view

• 实现原理：

  • 隐藏字段：
    • db_trx_id：最后一次修改该记录的事务id。
    • db_roll_ptr：指向undo log中历史版本的指针。
  • undo log：存储历史版本数据，形成版本链。
  • read view：快照读时生成，判断数据可见性。

• read view生成规则：

  • 活跃事务列表（m_ids）：生成read view时所有未提交的事务id。
  • 低水位（up_limit_id）：m_ids中的最小事务id。
  • 高水位（low_limit_id）：当前系统最大事务id + 1。

• 可见性判断逻辑：

  1. 若记录的 db_trx_id < up_limit_id：可见（事务已提交）。
  2. 若 db_trx_id >= low_limit_id：不可见（事务在read view之后启动）。
  3. 若 db_trx_id 在m_ids中：不可见（事务未提交）。

• rr与rc的区别：

  • rr（可重复读）：事务首次快照读生成read view，后续沿用，保证多次读取一致性。
  • rc（读提交）：每次快照读生成新read view，可能读到其他事务已提交的数据。

---

7. 关键实验与结论

• 实验1：未提交事务崩溃

  -- 终端a  
  begin;  
  insert into account values (3, '王五', 300);  
  -- 异常终止（未commit）  -- 终端b  
  select * from account;  -- 数据自动回滚，无王五记录  
  

• 实验2：已提交事务持久化

  -- 终端a  
  begin;  
  insert into account values (3, '王五', 300);  
  commit;  
  -- 异常终止  -- 终端b  
  select * from account;  -- 数据持久化，王五记录存在  
  

• 实验3：rr级别下的幻读解决

  -- 终端a（rr级别）  
  begin;  
  select * from account;  -- 首次快照读生成read view  -- 终端b插入新记录并提交  
  insert into account values (4, '赵六', 400);  
  commit;  -- 终端a再次查询  
  select * from account;  -- 仍看不到赵六（rr级别通过间隙锁阻止幻读）  
  

---

8. 注意事项与最佳实践

1. 避免长事务：长时间未提交的事务会占用锁资源，影响并发性能。
2. 引擎选择：高并发场景务必使用innodb，myisam不支持事务。
3. 隔离级别设置：默认使用repeatable read，仅在需要时调整。
4. 显式加锁：

   select * from account for update;   -- 当前读，加写锁  
   select * from account lock in share mode;  -- 当前读，加读锁