该文章下的 mysql 题目取自网络,我是二次进行对应延伸的思考

1.什么是死锁

解析

是指多个事务在执行过程中, 因资源争用造成了循环等待,事务 A 持有资源 X 并等待 Y，事务 B 持有资源 Y 并等待 X，这就形成了死锁
死锁的产生原因:

  • 1.多个事务以不同的顺序访问相同的资源
  • 2.事务设计不当,事务的执行时间过长，导致资源放不出来

    ET: 那如何解决呢

    1.InnoDB存储引擎会自动检测死锁进行回滚

    2.调整锁等待超时参数

    1.固定资源的访问顺序 2.优化索引设计 3.合理选择隔离级别

2.创建索引的方式

解析 
CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `sex` int(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE employee ADD INDEX index_name (column);

CREATE INDEX index_name ON table_name (column);

3.InnoDB 内存结构包含哪四大核心组件

这个是看的沈健老师的公众号文章
(缓冲池)[https://mp.weixin.qq.com/s?__biz=MjM5ODYxMDA5OQ==&mid=2651962450&idx=1&sn=ce17c4da8d20ce275f75d0f2ef5e40c9&chksm=bd2d098e8a5a809834aaa07da0d7546555385543fb6d687a7cf94d183ab061cd301a76547411&scene=21#wechat_redirect]
(写缓冲)[https://mp.weixin.qq.com/s?__biz=MjM5ODYxMDA5OQ==&mid=2651962467&idx=1&sn=899ea157b0fc6f849ec80a4d055a309b&chksm=bd2d09bf8a5a80a972a2e16a190ed7dffe03f89015ead707bdfcc5aeb8388fb278f397c125f1&scene=21#wechat_redirect]

解析
缓冲池

  • 缓冲池: 缓存表数据和索引数据,把磁盘的数据放在缓冲池,避免每次访问都进行磁盘 IO
    而磁盘读写并不是需要啥读啥，而是一种预读策略，按页进行读取，一次至少读 4k 数据(一页),如果未来要读取的数据在这页中，能减少磁盘 IO 的次数
    局部性原理:数据访问，通常都遵循“集中读写”的原则，使用一些数据，大概率会使用附近的数据
    缓冲池一般也是按页缓存数据

    LRU算法(最近最少算法)

    把入缓冲池的页放到LRU的头部，作为最近访问的元素，从而最晚被淘汰，一般有两种情况

    1.页已经在缓冲池里，那就只做“移至”LRU头部的动作，而没有页被淘汰

    2.页没在缓冲池里，将页放在 LRU 的头部

    为啥mysql不使用这种算法呢

    1. 预读失效：MYSQL 提前把页放入缓冲池，但是并没有从页中读取到数据

    如何优化预读失效呢} {% p green 将LRU分为两个部分：新生代(new sublist) 老生代(old sublist)，新生代占70%，；老生代占30%，新生代尾部连接老生代的尾部，每次预读进缓冲池的页先进入老生代的头部，如果真正读取的话就进入新生代的头部，预读失效的话放在老生代里也能更快的淘汰

    2. 缓冲池污染: 当某一个SQL语句，要批量扫描大量数据时，可能导致把缓冲池的所有页都替换出去，导致大量热数据被换出，MySQL性能急剧下降，这种情况叫缓冲池污染

    select * from user where name like "%shenjian%";

    虽然结果集可能只有少量数据，但这类 like 不能命中索引，必须全表扫描，就需要访问大量的页
    如此一来，所有的数据页都会被加载到新生代的头部，但只会访问一次，真正的热数据被大量换出

    MySQL缓冲池加入了一个“老生代停留时间窗口”的机制

    插入老生代头部的页，即使立刻被访问，并不会立刻放入新生代头部

    只有满足“被访问”并且“在老生代停留时间”大于 T，才会被放入新生代头部

    InnoDB 里面有哪些参数

    • innodb_buffer_pool_size:配置缓冲池的大小，在内存允许的情况下，DBA 往往会建议调大这个参数，越多数据和索引放到内存里，数据库的性能会越好
    • innodb_old_blocks_time: 老生代停留时间窗口，单位是毫秒，默认是 1000，即同时满足“被访问”与“在老生代停留时间超过 1 秒”两个条件，才会被插入到新生代头部
    • innodb_old_blocks_pct: 老生代占整个 LRU 链长度的比例，默认是 37，即整个 LRU 中新生代与老生代长度比例是 63:37
写缓冲

  • 写缓冲:它是一种应用在非唯一普通索引页(non-unique secondary index page)不在缓冲池中，对页进行了写操作，并不会立刻将磁盘页加载到缓冲池，而仅仅记录缓冲变更(buffer changes)，等未来数据被读取时，再将数据合并(merge)恢复到缓冲池中的技术

    普通情况下

    • 情况一:假如要修改页号为 4 的索引页，而这个页正好在缓冲池内
      1.直接修改缓冲池中的页, 一次内存操作
      2.写入 redo log, 一次磁盘操作

    是否会出现一致性问题

    答案是不会，因为读取会命中缓存池里的页，缓冲池LRU数据淘汰，会将“脏页”刷回磁盘，数据库异常奔溃，能够从redo log中恢复数据

    **什么时候缓冲池中的页，会刷到磁盘上呢？****定期刷磁盘(批量写)，而不是每次刷磁盘，能够降低磁盘 IO，提升 MySQL 的性能**- **情况二:修改页号为 40 的索引页，而这个页不在缓冲池里** **1.先把页号为 40 的页从磁盘读取到缓冲池中，一次磁盘读操作** **2.修改缓存池中的页，一次内存操作** **3.写入 redo log, 一次磁盘操作**

    没有命中缓冲池的时候，至少产生一次磁盘IO，对于写多读少的业务场景，是否还有优化的空间呢

    我们这时就要用到写缓冲，那现在的情况二是什么样的呢

    **1.在写缓冲记录中写入这个操作,一次内存操作****2.写入 redo log 日志里, 一次磁盘顺序写操作**

    那现在40这一页并没有进入缓冲池中，是否会出现不一致问题

    1.数据库异常奔溃，能够从redo log中恢复数据

    2.数据库异常奔溃，能够从redo log中恢复数据

    3.写缓冲不只是一个内存结构，它也会被定期刷盘到写缓冲系统表空间

    **假如稍后的一个时间,有一个请求查询索引 40 页的数据呢，会出现什么操作呢****1.载入索引页，缓冲池未命中****2.从写缓冲记录中读取相关信息****3.恢复索引页，放到缓冲池 LRU 里**

    除了数据页被访问，还有哪些场景会触发刷写缓冲中的数据呢？

    有一个后台线程，会认为数据库空闲时

    数据库缓冲池不够用时

    数据库正常关闭时

    redo log写满时

    什么业务场景，适合开启 InnoDB 的写缓冲机制？先说什么时候不适合，如上文分析，当：（1）数据库都是唯一索引；（2）或者，写入一个数据后，会立刻读取它；这两类场景，在写操作进行时（进行后），本来就要进行进行页读取，本来相应页面就要入缓冲池，此时写缓存反倒成了负担，增加了复杂度。什么时候适合使用写缓冲，如果：（1）数据库大部分是非唯一索引；（2）业务是写多读少，或者不是写后立刻读取；（账单流水业务）可以使用写缓冲，将原本每次写入都需要进行磁盘 IO 的 SQL，优化定期批量写磁盘。

    上述原理，对应InnoDB里哪些参数？

    innodb_change_buffer_max_size:配置写缓冲的大小，占整个缓冲池的比例，默认值是25%，最大值是50%

    innodb_change_buffering: 配置哪些写操作启用写缓冲，可以设置成all/none/inserts/deletes等

自适应哈希索引

有的说InnoDB支持哈希索引，有的说不支持，到底哪个是正确的呢

1.InnoDB用户无法手动创建哈希索引，这一层上说，InnoDB确实不支持哈希索引} {% p green

**InnoDB 会在主键 id 建立聚簇索引, 叶子存储记录本身, 在 name 上建立普通索引，叶子存储主键值****发起主键 id 查询时，能够通过聚簇索引，直接定位到行记录****通过 name 进行查询的时候，先会从普通索引查询到主键，再有主键，从聚集索引上二次遍历定位到记录****在 MySQL 运行的过程中，如果 InnoDB 发现，有很多 SQL 存在这类很长的寻路，并且有很多 SQL 会命中相同的页面(page)，InnoDB 会在自己的内存缓冲区(Buffer)里，开辟一块区域，建立自适应哈希所有 AHI，以加速查询****InnoDB 的自使用哈希索引，更像“索引的索引”，毕竟其目的是为了加速索引寻路****哈希 key 是索引键值，value 是索引记录页面位置**

业务场景有哪些呢

很多单行记录查询（例如passport，用户中心等业务

索引范围查询（此时AHI可以快速定位首行记录

所有记录内存能放得下

日志缓冲

MySQL崩溃，重启后发现有些已经提交的事务对数据的修改丢失了，不是说事务能保证ACID特性么，想问下什么情况下可能导致“事务已经提交，数据却丢失”呢

redo log 有什么用?

事务提交后，必须将事务对数据页的修改刷(fsync)到磁盘上，才能保证事务的ACID特性

这个刷盘，是一个随机写，随机写性能较低，如果每次事务提交都刷盘，会极大影响数据库的性能

有什么解决方法呢

  • 第一种是先写入日志，将随机写变成顺序写
  • 第二种是将每次写改为批量写

假如出现数据库崩溃的情况，这时候数据页还没进行刷盘，数据库重启时，就会重做redo log 里面的内容

redo log 的三层架构

  • 最上面一层是 log buffer 缓冲区
  • 中间是 os 的缓冲区
  • 最下面时落盘的日志文件

redo log 最终落盘的顺序
1.先调用 mysql 的 write 函数写入 log buffer
2.*然后调用系统的 write 函数将 log buffer 的内容写入 os cache，mysql 系统调用完 write 后，会默认文件已经写完，什么时候落盘是操作系统决定的
3.最后，由操作系统（当然，MySQL 也可以主动 flush）将 OS cache 里的数据，最终 fsync 到磁盘上

操作系统为什么要缓冲数据到OS cache里，而不直接刷盘呢

将每次写优化成批量写，提高性能，同理 mysql 缓冲数据到 log buffer 里也是这个目的

那这样有什么缺点呢

缺点就是可能会丢失数据


（1）事务提交时，将 redo log 写入 Log Buffer，就会认为事务提交成功；

（2）如果写入 Log Buffer 的数据，write 入 OS cache 之前，数据库崩溃，就会出现数据丢失；

（3）如果写入 OS cache 的数据，fsync 入磁盘之前，操作系统奔溃，也可能出现数据丢失

如何根据业务方案去选择刷 redo log 的策略
mysql 里有这样一个参数 innodb_flush_log_at_trx_commit:能够控制事务提交时，刷 redo log 的策略

  • 1.策略一:最佳性能(批量写)
    每隔一秒，才将 Log Buffer 中的数据批量 write 入 OS cache，同时 MySQL 主动 fsync。这种策略，如果数据库奔溃，有一秒的数据丢失。
  • 2.策略二:强一致
    每次事务提交，都将 Log Buffer 中的数据 write 入 OS cache，同时 MySQL 主动 fsync。这种策略，是 InnoDB 的默认配置，为的是保证事务 ACID 特性
  • 3.策略三:折中
    每次事务提交，都将 Log Buffer 中的数据 write 入 OS cache；每隔一秒，MySQL 主动将 OS cache 中的数据批量 fsync。画外音：磁盘 IO 次数不确定，因为操作系统的 fsync 频率并不是 MySQL 能控制的这种策略，如果操作系统奔溃，最多有一秒的数据丢失。画外音：因为 OS 也会 fsync，MySQL 主动 fsync 的周期是一秒，所以最多丢一秒数据
    高并发业务，行业内的最佳实践，是：innodb_flush_log_at_trx_commit=2（折中方案）
    高并发业务，行业最佳实践，是使用第三种折衷配置（=2），这是因为：
    （1）配置为 2 和配置为 0，性能差异并不大，因为将数据从 Log Buffer 拷贝到 OS cache，虽然跨越用户态与内核态，但毕竟只是内存的数据拷贝，速度很快；
    （2）配置为 2 和配置为 0，安全性差异巨大，操作系统崩溃的概率相比 MySQL 应用程序崩溃的概率，小很多，设置为 2，只要操作系统不奔溃，也绝对不会丢数据

到这里教程二就已圆满结束了，感谢你的浏览，thank you