MySQL 幻读被彻底解决了吗★
-
MYSQL的事务隔离级别：代表当存在多个事务并发冲突时，可能出现的脏读、不可重复读、幻读的问题。 (×：会差生，√：不会产生)
| 事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
|--- |--- | --- | --- |
| Read Uncommitted | √ | √ | √ |
| Read committed | × | √ | √ |
| repeated read | × | × | 很大程度避免 |
| serializable | × | × | × |
-
当同一个查询在不同的时间产生不同的结果集时，事务中就会出现所谓的幻象问题。
-
MySQL InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它很大程度上避免幻读现象（并不是完全解决了），解决的方案有两种：
- 针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
- 针对当前读（select ... for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select ... for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。
快照读是如何避免幻读的？
可重复读隔离级是由 MVCC（多版本并发控制）实现的，实现的方式是启动事务后，在执行第一个查询语句后，会创建一个 Read View，后续的查询语句利用这个 Read View，通过这个 Read View 就可以在 undo log 版本链找到事务开始时的数据，所以事务过程中每次查询的数据都是一样的，即使中途有其他事务插入了新纪录，是查询不出来这条数据的，所以就很好了避免幻读问题。
当前读是如何避免幻读的？
MySQL 里除了普通查询是快照读，其他都是当前读，比如 update、insert、delete，这些语句执行前都会查询最新版本的数据，然后再做进一步的操作。
这很好理解，假设你要 update 一个记录，另一个事务已经 delete 这条记录并且提交事务了，这样不是会产生冲突吗，所以 update 的时候肯定要知道最新的数据。
另外，select ... for update 这种查询语句是当前读，每次执行的时候都是读取最新的数据。
- 小结:
- MySQL InnoDB 引擎的可重复读隔离级别（默认隔离级），根据不同的查询方式，分别提出了避免幻读的方案：
- 针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读。
- 针对当前读（select ... for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读。
举例了两个发生幻读场景的例子。
第一个例子：对于快照读， MVCC 并不能完全避免幻读现象。因为当事务 A 更新了一条事务 B 插入的记录，那么事务 A 前后两次查询的记录条目就不一样了，所以就发生幻读。
第二个例子：对于当前读，如果事务开启后，并没有执行当前读，而是先快照读，然后这期间如果其他事务插入了一条记录，那么事务后续使用当前读进行查询的时候，就会发现两次查询的记录条目就不一样了，所以就发生幻读。
- 所以，MySQL 可重复读隔离级别并没有彻底解决幻读，只是很大程度上避免了幻读现象的发生。
- 要避免这类特殊场景下发生幻读的现象的话，就是尽量在开启事务之后，马上执行 select ... for update 这类当前读的语句，因为它会对记录加 next-key lock，从而避免其他事务插入一条新记录。
- 硬件和操作系统层面的优化
CPU、可用内存大小、磁盘读写速度、网络带宽
- 架构设计层面的优化
MySQL 是一个磁盘 IO 访问量非常频繁的关系型数据库:
- 搭建 Mysql 主从集群
- 读写分离设计
- 分库分表机制
- 热点数据缓存
- MySQL 程序配置优化
my.cnf 中, 比如最大连接数， 开启binlog 日志，bufferPool等
注意：作用域
- SQL 优化
- 4.1 慢 SQL 的定位和排查： 以通过慢查询日志和慢查询日志分析工具得到有问题的 SQL 列表
- 4.2 执行计划分析， 重点关注type key rows filterd 等字段
- 4.3 使用 show profile 工具
备注, 一些常见的sql优化规则：
- SQL 的查询一定要基于索引来进行数据扫描
- 避免索引列上使用函数或者运算,这样会导致索引失效
- where 字句中 like %号,尽量放置在右边
- 使用索引扫描,联合索引中的列从左往右,命中越多越好.
- 尽可能使用 SQL 语句用到的索引完成排序,避免使用文件排序的方式
- 查询有效的列信息即可.少用 * 代替列信息
- 永远用小结果集驱动大结果集。
- MVCC多版本并发控制！无锁化的方式实现对数据的并发访问
- 已提交、可重复读 都是基于MVCC实现的
- 读未提交，每次都读取最新的数据；串行化通过加锁的方式实现
- MVCC的相关知识点：
- 2.1 事务版本号 ： 事务每次开启前，都会从数据库获得一个自增长的事务ID，可以从事务ID判断事务的执行先后顺序。这就是事务版本号。
- 2.2 隐式字段 ：
- row_id： 单调递增的行ID，不是必需的，占用6个字节。
- trx_id： 记录操作该数据事务的事务ID
- roll_pointer：指针，指向回滚段的undo日志
- 2.3 undo log，回滚日志；当delete一条记录时，undo log 中会记录一条对应的insert记录，当update一条记录时，它记录一条对应相反的update记录。
InnoDB中通过undo log实现了数据的多版本，而并发控制通过锁来实现。
- undo logo 作用： 事务回滚时，保证原子性和一致性; 用于MVCC快照读。
- 2.4 版本链
- 多个事务并行操作某一行数据时，不同事务对该行数据的修改会产生多个版本，然后通过回滚指针（roll_pointer），连成一个链表，这个链表就称为版本链。
- 2.5 快照读和当前读
- 快照读： 读取的是记录数据的可见版本（有旧的版本）。不加锁,普通的select语句都是快照读,
- 当前读： 读取的是记录数据的最新版本，显式加锁的都是当前读。 如：
.....for update; .....lock in share mode;
- 2.6** 读视图 Read View**
- 事务执行SQL语句时，产生的读视图。实际上在innodb中，每个SQL语句执行前都会得到一个Read View。
- 作用： 用来做可见性判断的，即判断当前事务可见哪个版本的数据
ReadView 其实就是一个保存事务ID的list列表。记录的是本事务执行时，MySQL还有哪些事务在执行，且还没有提交。(当前系统中还有哪些活跃的读写事务)
它主要包含这样几部分：
m_ids，当前有哪些事务正在执行，且还没有提交，这些事务的 id 就会存在这里；
min_trx_id，是指 m_ids 里最小的值；
max_trx_id，是指下一个要生成的事务 id。下一个要生成的事务 id 肯定比现在所有事务的 id 都大；
creator_trx_id，每开启一个事务都会生成一个 ReadView，而 creator_trx_id 就是这个开启的事务的 id。
一句话说：当trx_id在m_ids中，或者大于m_ids列表中最大的事务id的时候，这个版本就不能被访问。
- MVCC,通过版本链，实现多版本，可并发读-写，写-读。通过ReadView生成策略的不同实现不同的隔离级别。
下面四个优化成本递增
- 表结构与索引
分库分表、读写分离、为字段选择合适的数据类型、适当的反范式设计，适当冗余设计、为查询操作创建必要的索引但是要避免索引滥用、尽可能使用 Not Null。
- SQL 语句优化
通过慢查询分析需要优化的 SQL 进行合理优化、利用 explain、profile 等工具分析 SQL执行计划、避免使用 SELECT *查询。尽可能使用索引扫描来排序。
- Mysql 参数优化
设置 Buffer_pool 的大小，建议占总内存的 70%左右设置刷盘策略，平衡好数据安全性和性能的关系等
- 硬件及系统配置
CPU 核数、磁盘的读写性能（减小寻道时间、旋转时间、传输时间），可以选择 SSD、网卡、内存等方面。
- Mysql 里面的事务，满足 ACID 特性，所以在我看来，Mysql 的事务实现原理，就是InnoDB 是如何保证 ACID 特性的。
- 原子性Atomic是通过 undo log 来实现的： 修改之前的数据快照保存到UNDO_LOG里面，一旦出现错误，就直接从UNDO_LOG
里面读取数据执行反向操作就行
- 持久性性是通过 redo log 来实现的： 理论上来说，事务提交之后直接把数据持久化到磁盘就行了，但是因为随机磁盘 IO 的效率确实很低，所以 InnoDB 设计了Buffer Pool 缓冲区来优化，也就是数据发生变更的时候先更新内存缓冲区，然后在合适的时机再持久化到磁盘。那在持久化这个过程中，如果数据库宕机，就会导致数据丢失，也就无法满足持久性了。所以 InnoDB 引入了 Redo_LOG 文件，这个文件存储了数据被修改之后的值，当我们通过事务对数据进行变更操作的时候，除了修改内存缓冲区里面的数据以外，还会把本次修改的值追加到 REDO_LOG 里面。当提交事务的时候，直接把 REDO_LOG 日志刷到磁盘上持久化，一旦数据库出现宕机，在 Mysql 重启在以后可以直接用 REDO_LOG 里面保存的重写日志读取出来，再执行一遍从而保证持久性。
- 隔离性是通过 (读写锁+MVCC)来实现的： MVCC 机制解决了脏读和不可重复读的问题，然后使用了行锁/表锁的方式解决了幻读的问题。
- 一致性是通过原子性，持久性，隔离性来实现的。
- 行锁、临键锁、间隙锁，都是 Mysql 里面 InnoDB 引擎下解决事务隔离性的一系列排他锁。
- 行锁，也称为记录锁。当我们针对主键或者唯一索引加锁的时候，Mysql 默认会对查询的这一行数据加行锁，避免其他事务对这一行数据进行修改。
select ** from test where id =2 for update
- 间隙锁，顾名思义，就是锁定一个索引区间。在普通索引或者唯一索引列上，由于索引是基于 B+树的结构存储，所以默认会存在一个索引区间。而间隙锁，就是某个事物对索引列加锁的时候，默认锁定对应索引的左右开区间范围。在基于索引列的范围查询，无论是否是唯一索引，都会自动触发间隙锁。
- 临键锁，它相当于行锁+间隙锁的组合，也就是它的锁定范围既包含了索引记录，也包含了索引区间它会锁定一个左开右闭区间的数据范围。
- 总的来说，行锁、临键锁、间隙锁只是表示锁定数据的范围，最终目的是为了解决幻读的问题。而临键锁相当于行锁+间隙锁，因此当我们使用非唯一索引进行精准匹配的时候，会默认加临键锁，它需要锁定匹配的这一行数据，还需要锁定这一行数据对应的左开右闭区间。因此在实际应用中，尽可能使用唯一索引或者主键索引进行查询，避免大面积的锁定造成性能影响。
- binlog 和 redolog 都是 Mysql 里面用来记录数据库数据变更操作的日志。
- 使用场景不同，binlog 主要用来做数据备份、数据恢复、以及主从集群的数据同步；Redo Log 主要用来实现 Mysql 数据库的事务恢复，保证事务的 ACID 特性。当数据库出现崩溃的时候，Redo Log 可以把未提交的事务回滚，把已提交的事务进行持久化，从而保证数据的一致性和持久性。
- 记录的信息不同，binlog 是记录数据库的逻辑变化，它提供了三种日志格式分别是statement，row 以及 mixed；redo log 记录的是物理变化，也就是数据页的变化结果。
- 记录的时机不同， binlog 是在执行 SQL 语句的时候，在主线程中生成逻辑变化写入到磁盘中，所以它是语句级别的记录方式； RedoLog 是在 InnoDB 存储引擎层面的操作，它是在 Mysql 后台线程中生成并写入到磁盘中的，所以它是事务级别的记录方式，一个事务操作完成以后才会被写入到 redo log 中。
- 在索引列上做运算，比如使用函数
- 不符合最左匹配原则
- 索引列存在隐式转化
- 在索引列使用不等于号、not 查询的时候，由于索引数据的检索效率非常低，因此Mysql 引擎会判断不走索引。
- 使用 like 通配符匹配后缀%xxx 的时候（不符合索引的最左匹配原则）
- 使用 or 连接查询的时候，or 语句前后没有同时使用索引，那么索引会失效。只有or 左右查询字段都是索引列的时候，才会生效。
- 多表连接查询的场景中，连接顺序也会影响索引的使用
- 查询条件涉及到大量数据。当查询条件涉及到大量数据时，例如返回表中大部分数据的查询，MySQL 可能会认为使用索引并不高效，因此会放弃使用索引。
- 使用 explain 命令来查看 sql 的执行计划，然后针对性的进行调优即可。
1.** 数据存储的方式不同**，MyISAM 中的数据和索引是分开存储的，而 InnoDB 是把索引和数据存储在同一个文件里面。
2. 对于事务的支持不同，MyISAM 不支持事务，而 InnoDB 支持 ACID 特性的事务处理
3. 对于锁的支持不同，MyISAM 只支持表锁，而 InnoDB 可以根据不同的情况，支持行锁，表锁，间隙锁，临键锁
4. MyISAM 不支持外键，InnoDB 支持外键
在 MVCC 中，通常不需要加锁来控制并发访问。
相反，每个事务都可以读取已提交的快照，而不需要获得共享锁或排它锁。
在写操作的时候，MVCC 会使用一种叫为“写时复制”（Copy-On-Write）的技术，也就是在修改数据之前先将数据复制一份，从而创建一个新的快照。
当一个事务需要修改数据时，MVCC 会首先检查修改数据的快照版本号是否与该事务的快照版本一致，如果一致则表示可以修改这条数据，
否则该事务需要等待其他事务完成对该数据的修改。
另外，这个事物在新快照之上修改的结果，不会影响原始数据，
其他事务可以继续读取原始数据的快照，从而解决了脏读、不可重复度问题。
所以，正是有了 MVCC 机制，让多个事务对同一条数据进行读写时，不需要加锁也不会出现读写冲突。
- MySQL 的 Update 操作既可以锁行，也可以锁表，
- 如果 update 语句中的 where 条件包含了索引列，并且只更新一条数据，那这个时候就加行锁。
- 如果 where 条件中不包含索引列，这个时候会加表锁
- 另外，根据查询范围不同，Mysql 也会选择不同粒度的锁来避免幻读问题。
- 针对主键索引的 for update 操作：
SELECT * FROM t WHERE id = 10 FOR UPDATE; 会增加 Next-Key Lock 来锁定 id=10 索引所在的区间
- 针对于索引区间的查询或者修改:
SELECT * FROM user WHERE id BETWEEN 1 AND 100 FOR UPDATE; 会自动对索引间隙加锁，来解决幻读问题。