Mysql扫盲

锁

InnoDB行锁实现

InnoDB行锁是通过给索引加锁实现的。与Oracle，Oracle是直接对数据行加锁来实现的。InnoDB这种行锁实现方式意味着：只有通过索引检索数据才使用行锁，否则，InnoDB使用表锁。

索引

索引失效场景

• 组合索引未使用第一列或左侧相邻列
  如：abc索引。where b = ？，where a = ? and c = ?
• 范围查询列可以用到索引，后续列无法用到（组合索引）
  如：(>、<、between、like) 其后索引失效
• 隐式类型转换
• like “%张”，索引失效。like “张%”索引生效
• or 语句前后没有同时使用索引
• where中索引列有运算，或者索引列使用了函数
• where中在索引字段上使用not，<>，!=
  优化：key<>0 改为 key>0 or key<0，可使用索引
• 在索引列上使用 IS NULL 或 IS NOT NULL操作
  优化：例如，数字类型，判断大于0，字符串类型设置一个默认值，判断是否等于默认值即可
  如果一个 Like 语句的查询条件以通配符%起始，则索引失效。

如：
%zhang 或 %zhang% 不使用索引
zhang% 使用索引

复合索引：

当创建a,b,c联合索引时，相当于创建了a单列索引，a,b联合索引以及a,b,c联合索引三种
使用（a，c）检索时，a使用索引，c普通（无论条件顺序，mysql优化器会先检索有索引的条件）

事务

脏读、不可重复读、幻读

脏读： 读取到了其他事务未提交的数据
不可重复度： 并发更新时，另一事务前后查询相同数据时的结果不符合预期
幻读： 并发新增、删除这种会产生数量变化的操作时，另一事务前后查询相同数据时，结果不符合预期

事务隔离级别对比

读未提交、读已提交、可重复度、串行化

事务类型	脏读可能性	不可重复度读可能性	幻读可能性	加锁读
READ UNCOMMITTED	✔	✔	✔	✔
READ COMMITTED	×	✔	✔	✔
REPEATABLE READ	×	×	✔	✔
SERIALIZABLE	×	×	×	✔

MVCC（多版本并发控制）

• Mysql InnoDB 引擎下，读已提交（RC）、可重复读（RR）基于MVCC进行并发事务控制
• MVCC是基于UNDO_LOG版本链中数据版本对并发事务进行访问
• InnoDB会在表中增加TRX_ID、DB_ROLL_PTR两个字段，分别记录当前事务的编号、执行上一版本的指针
• UNDO_LOG版本链不是立即删除，确保版本链数据不再被引用后才进行删除

ReadView

ReadView是一种数据结构，包含4个字段：

• m_ids：当前活跃的事务编号集合
• min_trx_id：最小活跃事务编号
• max_trx_id：预分配事务编号，当前最大事务编号+1
• creator_trx_id：ReadView创建者的事务编号

快照读、当前读：

• ReadView 是“快照度”时MVCC提取数据的依据
• “快照度”，普通Select查询语句
• “当前读”，执行下列语句时读取数据的方式
  Insert、Update、Delete、Select…for update、Select…lock in share mode

异同：

• 读已提交（RC）：每一次执行快照读时，生成ReadView
• 可重复读（RR）：仅在第一次执行快照读时生成ReadView，后续快照读复用
  存在特殊情况（两个快照读之间存在“当前读”时，会重新生成ReadView）
• 快照读之间的“当前读”包含其他事务新增、删除的数据，则会出现幻读
  “RR” 解决了不可重复读，未解决幻读

SQL优化

执行计划

说明：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

说明：

（待续。。。）

• 普通索引关联主键索引，最后根据主键索引查找数据