MySQL实战45讲笔记（五）

第十四讲：count(*)这么慢，我该怎么办？ 

 

1. 多版本并发控制（MVCC） ：

是通过数据行的多个版本管理来实现数据库的 并发控制 。这项技术使得在InnoDB的事务隔离级别下执行 一致性读 操作有了保证。

MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理 读-写冲突 ，做到即使有读写冲突时，也能做到 不加锁 ， 非阻塞并发读 ，而这个读指的就是 快照读 , 而非 当前读 。当前读实际上是一种加锁的操作，是悲观锁的实现。而MVCC本质是采用乐观锁思想的一种方式。

在MVCC机制中，多个事务对同一行记录进行更新会产生多个历史版本，这些历史版本快照保存在undo log里。如果一个事务想要查询这行记录，需要读取哪个版本的行记录，此时就需要read view，它可以解决行的可见性问题。

2. 执行select count(*) from t 语句

• MyISAM表虽然count(*)很快，但是不支持事务；
• showtable status命令虽然返回很快，但是不准确；采样并计算，得到的是估计值。
• InnoDB表直接count(*)会遍历全表，虽然结果准确，但会导致性能问题。

3. 在数据库保存count(*)计数 

InnoDB引擎支持事务，我们利用好事务的原子性和隔离性：虽然会话B的读操作仍然是在T3执行的，但是因为这时候更新事务还没有提交，所以计数值加1这个操作对会话B还不可见。因此，会话B看到的结果里， 查计数值和“最近100条记录”看到的结果，逻辑上就是一致的。

第十五讲： 日志和索引相关问题

1. MySQL 中的WAL机制

WAL，全称是Write-Ahead Logging， 预写日志系统。指的是 MySQL 的写操作并不是立刻更新到磁盘上，而是先记录在日志上，然后在合适的时间再更新到磁盘上。 日志主要分为 undo log、redo log、binlog

1.undo log:完成MVCC从而实现 MySQL 的隔离级别

undo log 主要用于实现 MVCC，从而实现 MySQL 的 ”读已提交“、”可重复读“ 隔离级别。在每个行记录后面有两个隐藏列，"trx_id"、"roll_pointer"，分别表示上一次修改的事务id，以及 "上一次修改之前保存在 undo log中的记录位置 "。在对一行记录进行修改或删除操作前，会先将该记录拷贝一份到 undo log 中，然后再进行修改，并将修改事务 id，拷贝的记录在 undo log 中的位置写入 "trx_id"、"roll_pointer"。

而 MVCC 最核心的就是 版本链 和通过版本链生成的 Read View。

1、版本链：通过 "roll_pointer" 字段指向的上一次修改的值，使每行记录变化前后形成了一条版本链。

2、Read View：Read View 表示可见视图，用于限制当前事务查询数据的，通过与版本链的配合可以实现对数据的 “快照读” 。Read View 内部主要有四个部分组成，第一个是创建当前 Read View 的事务 id creator_trx_id，第二个是创建 Read View 时还未提交的事务 id 集合trx_ids，第三个是未提交事务 id 集合中的最大值up_limit_id，第四个是未提交事务 id 集合中的最小值low_limit_id。

读已提交和可重复读之所以不同就是它们 Read View 生成机制不同，读已提交是每次 select 都会重新生成一次，而可重复读是一次事务只会创建一次且在第一次查询时创建 Read View。事务启动命令begin/start transaction不会创建Read View，但是通过 start transaction with consistent snapshot 开启事务就会在开始时就创建一次 Read View。

2.redo log: 降低随机写的性能消耗（转成顺序写），同时防止写操作因为宕机而丢失

redo log 是搭配缓冲池、change buffer 使用的。缓冲池的作用是缓存磁盘上的数据页，减少磁盘的IO；change buffer 的作用是将写操作先存在内存中，等到下次需要读取这些操作涉及到的数据页时，就把数据页加载到缓冲池中，然后在缓冲池中更新

在将写操作写入 redo log 的过程中并不是直接就进行磁盘IO来完成的，而是分为三个步骤。

1、写入 redo log buffer 中，这部分是属于MySQL 的内存中，是全局公用的。

2、在事务编写完成后，就可以执行 write 操作，写到文件系统的 page cache 中，属于操作系统的内存，如果 MySQL 崩溃不会影响，但如果机器断电则会丢失数据。

3、执行 fsync（持久化）操作，将 page cache 中的数据正式写入磁盘上的 redo log 中，也就是图中的 hard disk。

3.binlog:写操作的备份，保证主从一致

第十八讲： SQL语句逻辑相同，性能却差异巨大？

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。
第二个例子是隐式类型转换，第三个例子是隐式字符编码转换，它们都跟第一个例子一样，因为
要求在索引字段上做函数操作而导致了全索引扫描。
MySQL的优化器确实有“偷懒”的嫌疑，即使简单地把where id+1=1000改写成where id=1000-1就能够用上索引快速查找，也不会主动做这个语句重写。