2022年MYSQL知识积累（一）：索引失效案例分析以及MYSQL事务

前言

1. 在《SQL优化2020最全干货总结—MySQL》中有列举了很多优化策略，下面对其中优化策略进行验证分析
2. 研究Mysql事务，注重分析了MVCC实现原理以及介绍Spring事务传播机制

索引失效

模糊查询导致索引失效分析

1. 尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描
   1.1 explain select * from user where name like ‘%da%’;
   
2. 如果索引关键字的类型是String类型，索引的排列顺序是根据比较字符串的首字母排序的（如果首字母相同，就根据比较第二个字母进行排序，以此类推）
   
3. 采用覆盖索引的方式可以解决左匹配索引失效的问题
   3.1 explain select name from user where name like ‘%da%’;
   
   3.2 普通索引需要扫两遍索引树，过程如下。覆盖索引是一种避免回表查询的优化策略
   

where条件中等号的函数操作

1. explain select * from user where age/2 = 11
2. 将表达式、函数操作移到等号右侧
   2.1 explain select * from user where age = 11 * 2
3. 因为对索引字段做函数操作，破坏索引值的有序性

MySQL事务

1. 事务的ACID特性
   
2. 不可重复读与幻读有什么区别？
   2.1 不可重复读的重点是修改：在同一事务中，同样的条件，第一次读的数据和第二次读的「数据不一样」。（因为中间有其他事务提交了修改）
   2.2 幻读的重点在于新增或者删除：在同一事务中，同样的条件，第一次和第二次读出来的「记录数不一样」。（因为中间有其他事务提交了插入/删除）

SQL的隔离级别

1. MVCC的演变过程
   1.1 锁来实现隔离性，不适用于读多写少的场景
   1.2 读写锁让读锁和读锁不互斥，但读锁与写锁互斥
   1.3 读取数据通过类似快照的方式解决读锁和写锁的互斥

2. MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作

3. MVCC 如何实现？
   3.1 InnoDB中通过undo log实现了数据的多版本，而并发控制通过锁来实现
   （1）undo log用于数据的撤回操作，它记录了修改的反向操作

   3.2 InnoDB行记录关键点：rowid、trx_id、db_roll_ptr
   （1） trx_id表示最近修改的事务的id
   （2） db_roll_ptr指向undo segment中的undo log
   
   3.3 ReadView：存储我们系统中当前活跃着的读写事务
   （1）up_limit_id：当前已经提交的事务号 + 1，之前已经提交的事务对于该事务肯定是可见的
   （2）low_limit_id：当前最大的事务号 + 1，创建Read View视图之后创建的事务对于该事务肯定是不可见的
   （3）trx_ids：活跃事务id列表

   3.4 已提交读和可重复读的区别就在于它们生成ReadView的策略不同
   （1）READ_COMMITED：每次查询会重新生成一个ReadView
   （2）REPEATABLE_READ：ReadView始终是第一次Select时生成的

Spring传播机制

1. PROPAGATION_REQUIRED (默认)
   1.1 支持当前事务，如果当前没有事务，则新建事务
   1.2 如果当前存在事务，则加入当前事务，合并成一个事务
2. REQUIRES_NEW
   2.1 新建事务，如果当前存在事务，则把当前事务挂起
   2.2 这个方法会独立提交事务，不受调用者的事务影响，父级异常，它也是正常提交