Delete、Drop 和 Truncate
- delete、truncate 仅仅删除表里面的数据，drop会把表的结构也删除
- delete 是 DML 语句，操作完成后，可以回滚，truncate 和 drop 是 DDL 语句，删除之后立即生效，不能回滚
- 执行效率：drop > truncate > delete
MyISAM 与 InnoDB
- InnoDB 支持事务，MyISAM 不支持
- InnoDB 支持外键，MyISAM 不支持
- InnoDB 是聚集索引，数据文件是和索引绑定一起的
- MyISAM 是非聚簇索引，索引和数据文件是分离的，索引保存的是数据的指针
- InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描
- MyISAM 用一个变量保存整个表的行数，执行上述语句时只需要读出改变量即可，速度很快
- InnoDB 支持表、行（默认）级锁，MyISAM 支持表级锁
Join 语句
left join、right join、inner join 的区别：
left join（左连接）：
- 返回包括左表中的所有记录和右表中联结字段相等的记录
- 左表是驱动表，右表是被驱动表
right join（右连接）：
- 返回包括右表中的所有记录和左表中联结字段相等的记录
- 右表是驱动表，左表是被驱动表
innner join（等值连接）：
- 只返回两个表中联结字段相等的行
- 数据量比较小的表作为驱动表，大表作为被驱动表
join 查询在有索引条件下：
- 驱动表有索引不会使用到索引
- 被驱动表建立索引会使用到索引、
所以在以小表驱动大表的情况下，给大表建立索引会大大提高查询效率
Join 原理：
Simple Nested-Loop：
- 驱动表中的每一条记录与被驱动表中的记录进行比较判断（笛卡尔积）
- 对于两表联结来说，驱动表只会被访问一遍，但驱动表却要被访问到好多遍
Index Nested-Loop：
- 基于索引进行连接的算法
- 他要求被动表驱动表上有索引，可以通过索引来加速查询
Block Nested-Loop：
- 它使用 Join Buffer 来减少内部循环读取表的次数
- Join Buffer 用以缓存联接需要的列
选择 Join 算法优先级：
- Index Nested-LoopJoin > Block Nested-Loop Join > Simple Nested-Loop Join
当不使用 Index Nested-Loop Join 的时候，默认使用 Block Nested-Loop Join
分页查询优化
select * from table
where
type = 2
and level = 9
order by id asc
limit 190289,10;
延迟关联：
通过 where 条件提取出主键，再将该表与原数据表关联，通过主键 id 提取数据行，而不是通过原来的二级索引提取数据行
select a.* from table a, ( select id from table where type = 2 and level =9 order by id asc limit 190289,10 ) b where a.id = b.id;书签方式：
找到 limit 第一个参数对应的主键值， 在根据这个主键值再去过滤并 limit
select * from table where id > ( select * from table where type = 2 and level = 9 order by id asc limit 190289, 1 ) limit 10;
事务
ACID
- 原子性（atomicity）：指事务是一个不可分割的组织，要么全部提交，要么全部失败回滚
- 一致性（consistency）：指事务执行前后，数据从一个合法性状态变换到另外一个合法性状态（状态与业务相关）
- 隔离型（isolation）：指一个事物的执行不能被其他事务干扰；一个事物内部的操作 及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰
- 持久性（durability）：指一个事务一旦被提交，ta 对数据库中数据的改变就是永久性的，其他操作和数据库故障并应该对其有任何影响
数据并发产生的问题
丢失更新（Lost Update）：
- 两个事务，如 Session A、Session B，如果事务 Session A 修改了另一个未提交事务 Session B 修改过的数据，意味着发生了丢失更新
脏读（Dirty Read）：
- 两个事务，如 Session A、Session B，Session A读取了已经被 Session B更新但还没有被提交的字段，如果后来 Session B 回滚，那么 Session A 读取的内容就是临时且无效的
不可重复读（Non-Repeatable Read）：
- 两个事务，如 Session A、Session B，Session A 读取了一个字段，然后 Session B更新了该字段，如果后来 Session A 再次读取同一个字段，该字段的值发生了变化
幻读（Phantom）：
- 两个事务 Session A、Session B，Session A 从一个表中读取了一个字段，然后 Session B在该表中插入了一些新的行，如果后来 Session A 再次读取同一个表，就会多出几行
事务隔离级别
READ UNCOMMITTED（读未提交）：
- 所有事务都可以看到其他未提交事物的执行结果， 不能避免脏读、不可重复读、幻读
READ COMMITTED（读已提交）：
- 一个事务只能看见已经提交的事务所做的改变，可以避免脏读，但不可重复读、幻读问题仍然存在
REPEATABLE READ（重复读）：
- 事务 A 在读到一条数据后，此时事务 B 对该数据进行了修改并提交，事务 A 在读该数据，读取到的还是原来的内容，可以避免脏读、不可重复读，但幻读问题仍然存在，且是 MySQL 默认隔离级别
SERIALIZABLE（可串行化）：
- 确保事务可以从一个表中读取相同的行
| 隔离级别 | 丢失更新 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|---|
| Read Uncommitted（RU） | × | √ | √ | √ |
| Read Committed（RC） | × | × | √ | √ |
| Repeatable Read（默认） | × | × | × | √ |
| Serializable | × | × | × | × |
MySQL 默认隔离级别为什么是可重复读（RR）
MySQL 在 5.0 之前，binlog 只支持 STATEMENT 这中格式，而这种格式在读已提交（RC）这个隔离级别下主从复制是有 bug 的，因此 MySQL 将可重复读（RR）作为默认的隔离级别
STATEMENT 主从复制的 bug：
- 在 master 上执行的顺序为先删后插，而此时 binlog 为 STATEMENT 格式，ta 记录的顺序为先插后删
- 从（slave）同步的是 binlog，如果从机执行的顺序 和主机不一致，就会出现主从不一致
隔离级别设为可重复读（RR），在该隔离级别下引入间隙锁：
- 当 Session A 执行 delete 语句时，会锁住间隙
- 那么，Session B 执行插入语句就会阻塞住
为什么大家将隔离级别设为读已提交(RC)？
在 RR 隔离级别下，存在间隙锁，导致出现死锁的几率比 RC 大的多
在 RR 隔离级别下，条件为命中索引会锁表，而在 RC 隔离级别下，只锁行
在 RC 隔离级别下，半一致性读（semi-consistent）特性增加了 update 操作的并发性
半一致性读：一个 update 语句，如果读到一行已经加锁的记录，此时 InnoDB 返回记录最近提交的版本，有MySQL 上层判断此版本是否满足 update 的 where 条件
- 若满足（需求更新），则 MySQL 会重新发作一次读操作，此时会读取行的最新版本（并加锁）
如下 Session A，Session B代码：
Sesssion A 执行：
update test set color = 'blue' where color = 'red';先不 Commit 事务；
与此同时，Session B 执行：
update test set color = 'blue' where color = 'white';Session B 尝试加锁的时候，发现已经存在锁，InnoDB 会开启 semi-consistent read，返回最新的 committed 版本（1,red），（2,white），（5,red）,（7,white）；
MySQL 会重新发起一次读操作，此时会读取行的最新版本（并加锁）;
而在 RR 隔离级别下， Session B 只能等待
互联网项目推荐使用：读已提交（Read Commited）隔离级别
事务实现原理
原子性：使用 undo log（回滚日志），undo log 记录了回滚需要的信息
- 当实物执行失败或调用了 rollback，导致事务需要回滚，便可以 undo log 中的信息将数据回滚到修改前的样子
隔离性：使用悲观锁和乐观锁对事务处理
持久性：使用 redo log （重写日志）
一致性：通过原子性、隔离性、持久性来保证一致性
MVCC
MVCC 是为了解决 读-写 之间阻塞的问题（排它锁会阻塞读操作），写操作还是需要加锁（Next-Key Lock）；
如果没有 MVCC，那么修改数据的操作会加排它锁，其他的读写操作都会阻塞，这样的话效率会比较低。
MVCC 通过 Undo Log + Read View 进行数据读取
- Undo Log 保存了历史快照
- Read View 规则判断当前版本的数据是否可见
快照读与当前读
快照读：
快照读读取的是快照数据：
- 不加锁的简单的 SELECT 都属于快照读，即不加锁的非阻塞读
快照读的实现是基于 MVCC，ta 在很多情况下，避免了加锁操作，降低了开销：
- 既然是基于多版本，那么快照读读取到的并不一定是数据的最新版本，而有可能是之前的历史版本
快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读
select * from goods where ...
当前读：
读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁
加锁的 SELECT ,或者对数据进行增删改都会进行当前读
select * from goods lock in share mode; # 共享锁 select * from goods for update： # 排它锁 insert into goods values ... # 排它锁 delete from goods where ... # 排它锁 update goods set ... where ... # 排它锁
Undo Log 版本链
InnoDB 聚簇索引记录中包含 3 哥隐藏的列：
- DB_ROW_ID（隐藏的自增 ID）：
如果表没有主键，InnoDB 会自动按 ROW ID 产生一个聚集索引树 - DB_TRX_ID（事务 ID）：
记录最近更新这条记录的事务 ID，大小为 6 个字节 - DB_ROLL_PTR（回滚指针）：
指向该行回滚段的指针，大小为 7 个字节;该行记录上所有旧版本，在 undo 中都通过连败哦的形式组织
每次对该记录进行改动，都会记录一条 undo 日志，每条 undo 日志都已一个 roll_pointer 属性
会将这些 undo 日志都连起来，串成一个链表，这个链表被称为版本链
- 版本链的头节点，就是当前记录最新的值
- 每个版本中包含生成该版本时对应的 事务 id
ReadView
ReadView 就是事务在使用 MVCC 机制进行快照读操作时产生的读视图；
判断版本链中的哪个版本是当前事务可见的，这是 ReadView 要解决的主要问题
ReadView 主要包含 4 个内容：
- creator_trx_id：创建这个 Read View 的事务 ID
- trx_ids：在生成 ReadView 时当前系统中活跃的读写事务的事务 id 列表（活跃：启动了但还没提交）
- up_limit_id：活跃的事务最小的事务 ID
- low_limit_id：表示生成 ReadView 时系统中应该分配给下一个事务的 id 值
ReadView 规则：
- 若被访问版本 trx_id 属性值与 ReadView 中的 creator_trx_id 值相同：
则当前事务在访问 ta 自己修改的记录，所以该版本 可以 被当前事务访问 - 若被访问版本 trx_id 属性值 小于 ReadView 中的 up_limit_id 值：
则生成该版本的事务与当前事务生成 ReadView 前已经提交，所以该版本 可以 被当前事务访问 - 若被访问版本的 trx_id 属性值 大于或等于 ReadView 中的 low_limit_id 值：
则生成该版本的事务在当前事务生成 ReadView 后才开启，所以该版本 不可以 被当前事务访问 - 若访问版本的 trx_id 属性值在 ReadView 的 up_limit_id 和 low_limited 之间，
需要判断一下 trx_id 属性值是不是在 trx_ids 列表：- 若在，则创建 ReadView 时生成该版本的事务还是活跃的，该版本不可以被访问
- 若不在，说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问
MVCC整体操作流程
首先 获取事务自己的版本号，也就是事务 ID；
获取 ReadView ；
查询到的数据，然后与 ReadView 中的事务版本号进行对比；
如果不符合 ReadView 规则，就需要从 Undo Log 中获取历史快照；
最后 返回不符合规则的数据；
如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性，知道版本链中的最后一个版本；
如果最后一个版本也不可见的话，那么就意味着该条记录对该事务不完全不可见，查询结果就不包含该记录；
READ COMMITTED 事务，在每次查询开始时都会生成一个独立的 ReadView；
REPEATABLE READ 事务，只会在第一次执行查询语句时生成一个 ReadView，之后的查询就不会重复生成了；
MVCC 幻读被彻底解决了嘛
可重复读隔离级别（默认隔离级别），根据不同的查询方式，分别提出了避免幻读的方案：
- 针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读
- 针对当前读（select … for update 等语句），是通过 next-key lockt（记录锁 + 间隙锁）方式解决幻读
![表情[baoquan]-拾光赋](https://blogs.ink/wp-content/themes/zibll/img/smilies/baoquan.gif)
暂无评论内容