MySQL 数据库 基础知识（二） MVCC

1.Buffer pool 除了索引之外，可以优化：合理设置 Buffer pool，为内存的 70%~80%，Buffer pool 是缓存页的，使用很多链表管理 buffer pool，比如 free 链表，每个节点表示空闲的缓存页，还有 LRU least recently used 最近最少使用链表，使用到某个缓存页时，会把该缓存页调整到 LRU 头部，分为 young 和 old 两个区域，这个比例是可以调整的，每次没有空闲的缓存页时就会释放 old 区域的页。 海量数据时关闭 innodb_buffer_pool_at_shutdown 和 innodb_buffer_pool_at_startup，这样的话在数据库关闭的时候也不会 dump 出 buffer pool 的数据，数据库开启时 也不会载入 buffer pool 的数据。 2.统计数据的存储方式 存在内存中，服务器关闭后再次登录，数据就不存在了；而如果存在磁盘中，重启后还可以看到，默认下是存在磁盘中的。 3.对 NULL 值的处理 （1）默认 认为 NULL 值是相等的。 （2）not_equal，认为所有的 NULL 都是不相等的。 （3）直接把 NULL 值忽略掉。 4.页、区、段 页 是数据库磁盘和内存交互的单位，一般是 16 KB。 连续的 64 个页是一个区，叶子节点 和 非叶子节点 是区别对待的，叶子节点会有一个区，非叶子节点会有一个区。 段是区的集合，也就是说，一个索引会有叶子节点段和非叶子节点段 5.排查慢查询 （1）查看慢查询日志，先看是否启动，没有的话可以通过 系统变量 或 配置文件开启慢查询日志。可以在 MySQL 安装路径中的 data 下看到慢查询日志的文本文档，可以看到具体的 SQL 语句，可以修改时间，默认是 10s 以上是慢查询。 （2）show prossecorlist 看进程列表，会显示持续时间，出现等待锁、死锁之类的问题，对 SQL 语句进行优化。 （3）explain 执行计划。会显示 驱动表、被驱动表、in 有无转化成 exists 、会有 state 状态表示有无死锁之类的、如果执行了全表扫描，预计扫描的行数、排序是使用索引还是文件排序。 MySQL 有查询优化器，会根据搜索条件，找到所有可能使用的索引 和 使用不同索引的代价，计算全表扫描的代价，选择成本最低的方案——执行计划。 6.undo 日志 与 MVCC 为了事务回滚操作，每个记录有隐藏的两个列：trx_id 生成这个记录的事务 ID，roll_pointer 相当于指针，会指向记录的 undo 日志，会把这个记录的历史版本连起来。 对于不同的操作 undo 日志的内容是不同的，比如说 insert 需要存插入记录的 key 值，之后需要回滚根据 key 值找到记录再删除即可， delete 需要存储完整的记录，回滚时再插入这条记录即可。（删除是分两步的：先 delete mark，把记录链到垃圾链表中，再用 purge 线程真正删除记录，并调整一些页的信息。） MVCC 是指 在 读已提交、可重复读隔离级别下，执行普通的 select 语句时，会生成 ReadView,遍历记录的版本链，与 ReadView 比较，把对当前事务可见的版本的记录作为结果。这两个隔离级别生成 ReadView 的时机不同，读已提交是在每次 select 时生成 ReadView，可重复读隔离级别是第一次 select 时生成，之后都用这个 ReadView。 ReadView 主要有 4 个属性： （1）生成这个 ReadView 的事务ID：creator_id （2）生成这个 ReadView 时，系统中还活跃的读写事务的 id 列表 （3）id 列表中，最小的事务 ID：min_id （4）系统要给下一个事务分配的 ID:max_id 遍历版本链，如果当前版本的 id 等于 creator_id，就说明事务访问的是自己修改过的记录，记录对当前事务是可见的；如果当前版本的 id 小于 min_id,说明 ReadView 生成之前记录已经提交了，说明记录对当前事务是可见的；如果当前版本的 id 大于 max_id,说明 ReadView 生成之后 记录还没有提交，记录对当前事务是不可见的；如果当前版本的 id 大于 min_id,而 小于 max_id，就要看是否在活跃列表中，如果在，说明 ReadView 生成时记录还没有提交，是不可见的，否则是可见的。遍历版本链，相当于从最新的版本开始比较，遇到可见的就放到结果中，如果遍历完毕还没有可见的，说明这个记录任何版本都不会放到结果中。 MVCC 用于 读已提交 可以避免脏读；用于 可重复读 可以避免不可重复读，但是没有解决幻读。比如：在可重复读隔离级别下，事务 A 第一次 select 时 生成了 ReadView，这个 ReadView 的 creator_id 是 事务 A，然后接下来 事务 B 插入一条记录并提交，然后 事务 A 对 记录做 update ，那么这个记录的 trx_id 就变成了 事务 A ,那事务 A 继续进行 select 时，遍历新插入的这条记录的版本链，最新版本的记录，trx_id 等于 creator_id，说明新插入的记录对于事务 A 是可见的，就会把它作为结果，这样就读到了幻影记录，出现了幻读的问题。 MVCC 的优点：使不同事务的 读-写 操作并发执行，从而提升系统性能。 7.数据库分库、分表 出现海量数据的问题，就需要进行分库、分表。 分库:把不同业务的表放置在不同的数据库中，这样可以解耦，表与表之间不会互相影响，而且分治管理。 分表：垂直分表：比如把不常使用的列放置到额外表中。但是仍没有解决一个表中有海量数据的问题。 水平分表：把记录分到不同的表中， 比如：根据数值取模，比如 4 个表，就 对 4 取余，相对均匀；但扩容时需要把数据进行迁移。 根据数值范围，比如 0~200、201~400,这样插入新纪录时只需要放置到对应的区间即可，但是如果是按时间作为范围，而且访问最新的数据的频率高，就会出现最新的表并发量大，而旧表很少访问的问题。