server层和存储引擎
server层：功能层面的引擎层：负责存储相关的具体事宜Mysql逻辑架构图:
建立连接
长连接：客户端有持续的请求使用同一个连接短连接：每次连接执行完很少的查询动作后断开连接建立连接的过程是比较复杂并消耗资源的，因此需要避免频繁的建立、关闭连接，尽量使用长连接，使用连接池使用过多的长链接可能会导致内存涨的特别快，这是因为mysql在执行过中临时使用的内存是管理在连接对象里面，而这部分资源只有在连接断开的时候才会被释放，当内存太大，内存溢出的时候mysql就会被Kill掉，从而出现mysql异常重启的情况查询缓存
由于数据库更新频繁（刚维护好的缓存还未使用就被另一个update动作清空），查询缓存的命中率往往很低、弊大于利，一般不推荐使用，静态表 可以使用，query_cache_type=demand可关闭查询缓存分析器 词法分析，主要用来验证SQL语句的正确性优化器
索引的选择，选择一种mysql“自认为”最佳的执行方案执行 调用引擎接口读取数据，并返回给客户端update语句的执行流程如下：（浅色表示在Inodb内部执行，深色表示在执行器执行）
redo log 分割成prepare和commit两阶段提交的意义 目的：让两份日志之间的逻辑一致怎样让数据恢复到半个月前任意一秒的状态？拉取备份的binlog,前提是你需要有近半个月的binlog如果分开写，第一个日志写完准备写第二个日志的期间发生crash 先写redo log 再写binlog(期间crash) 崩溃恢复后redo log的crash-safe保证了动作会被恢复，但是binlog并没有记录这个动作，当我们从备份里面拉取binlog进行恢复的时候这个动作就丢失了，这和我们期望的不符先写binlog再写redo log(期间crash) 崩溃恢复后这个动作并未被捕捉，但是binlog被写入了，当我们从备份拉取binlog进行恢复的时候，该动作也会被捕获， 这和我们期望的不符sync_binlog这个参数设置成1表示每次事务的binlog都持久化到磁盘，设置成1可以保证异常重启不丢失binlog
事务：ACID 原子性，一致性，隔离性，持久性
当前读：更新数据都是先读后写的，而这个读只能读当前值，称为：“当前读”（current read）
多个事务同时执行的时候就可能会出现：脏读，不可重读，幻读的问题，而隔离性正是为了解决这些问题 --- 隔离级别 什么是幻读？ 幻读指的是一个事务在前后两次在同一个范围查询的时候，后一次查询看到了前一次查询没看到的行。也就是说一个事务看到了别的事务进行的动作“当前读”才会出现幻读幻读仅专指“新插入的行”幻读有什么问题？ 一是语义问题二是数据一致性问题，包含数据和日志两个维度即使把所有的记录都加上锁还是阻止不了新插入的记录那么如何解决幻读呢？ 幻读的根本原因是行锁只能锁住行，但是新插入记录这个动作要更新的是记录之间的“间隙”，因此，为了解决幻读问题，Inodb只能引入了“间隙锁(Gap lock)”,间隙锁锁的就是两个值之间的空隙事务的启动方式 update语句本身就是一个事务，语句完成的时候会自动提交事务显式启动事务语句 begin/start transaction 并不是一个事务的起点，执行到第一个操作Inodb表语句的时候事务才真正开始start transaction with consistant snapshot 马上启动一个事务commitrollbackset autocommit=0 该命令会把线程的自动提交关掉，意味着只执行一个select语句这个事务就启动了，并且不会自动提交，这个事务持续存在知道主动commit或rollback或断开连接，因此建议使用autocommit=1，通过显式语句的的方式启动事务多一次交互的问题：使用commit work and chain查询长事务的语法(持续时间超过60s的事务)： select * from t where TIME_TO_SEE(timediff(NOW(),trx_started))>60mysql的隔离级别包括： 读未提交：一个事务还未提交时它所做的变更就能被别的事务看到读提交：一个事务提交过后它所做的变更才会被别的事务看到可重复读：一个事务在执行过程中看到的数据总是和这个事务启动的时候看到的是一致的串行化：对于同一行记录，“写”会加“写锁”，“读”会加“读锁”隔离级别越高执行的效率就越低事务隔离的实现 回滚日志：undo log 在MYSQL中每条记录在更新的同时都会记录一个回滚操作，记录上的最新值，通过回滚操作都能得到前一个状态的值什么是一致性视图？ consistent read view 用来支持RC（read commit 读提交）和 RR（reaptable read 可重复读）隔离级别的实现假设一个数从1被按顺序修改成2,3,4那么在回滚日志里面就会有如下记录：MVCC:不同时刻启动的事务会有不同的read-view,同一条记录在系统中可以存在多个版本，这就是数据库的多版本并发控制回滚日志什么时候会被删除？ 由系统判定，当没有事务再需要用到这些回滚记录的时候回滚日志就会被删除什么时候才不需要了呢？ 当系统里没有比这个更早的read view的时候为什么不建议使用长事务？
长事务以为着系统里面会存在很老的事务视图，由于这些事务随时可能访问数据库里的其他数据，所以在这个事务提交之前，数据库里面它可能用到的回滚记录都需要保留，从而导致大量的存储空间被占用长事务还占用锁资源，可能会拖垮整个库快照在MVCC里是怎么工作的？ 事务在启动的时候会基于整个库“拍照”快照是怎么实现的？
InnoDB利用了“所有数据都有多个版本”的特性，实现了“秒级创建快照”的能力InnoDB里每一个事务都有唯一的ID，叫做transaction id,在事务开启的时候向InnoDB事务系统申请，是按申请顺序严格递增的每行数据都有多个版本，每次事务更新数据的时候都会生成一个新的数据版本，并且把对应的事务ID赋给这个数据版本的事务ID，记为：row trx_id,同时旧版本的数据需要保留，并且在新版本中能够有信息直接拿到它按照可重复读的定义，一个事务只需要在启动的时候声明：以我启动的时刻为准，如果一个数据版本在我启动前生成，就认；如果在我启动之后生成我就不认，必须找到它的上一个版本（如果上一个版本也不可见就找上上一个版本），当然这个事务自己更新的数据它还是要认的InnoDB为每一个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在活跃的所有事务ID，活跃指的是那些启动了但是还没有提交的事务数据版本可见性规则：低水位：数据里面事务ID最小的值高水位：数组里面事务ID最大值加1视图数组和高水位组成了当前事务的一致性视图这样对于当前事务的启动瞬间来说，一个数据版本的row trx_id就有可能有以下几种情况：
绿色部分：表示这个版本是已经提交了的或者是当前事务自己生成的，可见红色部分：将来事务生成的，不可见黄色部分 若 row trx_id在数组中，表示这个版本是由还没提交的事务生成的，不可见若row trx_id不在数组中，表示这个版本是已经提交了的事务生成的，可见再来看可重复读是怎么实现的？ 核心：一致性读（consistent read）,而事务更新数据的时候只能用当前读，如果当前记录的行锁被其他事务占用的话就需要进入锁等待在可重复读隔离级别下，只需要在事务开始的时候创建一个一致性视图，之后事务里的其他查询都共用这个视图在读提交隔离级别下，每一个语句执行前都会重新算出锁的初衷：解决并发问题
锁分类：全局锁，表锁，行锁全局锁：对整个数据库实例加锁
Flush tables with read lock:添加全局读锁，此时整个库处于只读状态全局锁的典型使用场景：做全库逻辑备份但是整个库处于只读很危险，如果在主库上备份，不能进行更新等操作业务基本停摆，在备份上操作不能及时处理主库同步来的binlog导致主从延迟另一个方案：在可重复读隔离级别下开启一个事务官方自带的逻辑备份工具：mysqldump一致性读这么好为什么还需要FTWRL?前提是引擎需要支持这个隔离级别，MyISAM这种不支持事务的引擎就需要用到FTWRL了表级锁mysql表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock MDL）
表锁元数据锁
不需要显示使用，访问表时会自动加上，MDL锁保证读写的正确性所有的增删改查操作都会获取元数据锁读锁不互斥，读写锁之间、写锁之间互斥为什么给小表加字段也会导致数据库挂了？ 当前正在读，add被阻塞，add之后的读也被阻塞，如果请求很频繁，那么数据库的线程很快就会爆满如何安全的给小表加字段？ 避免长事务alter table语句里加上等待时间，超时放弃行锁功过 行锁是引擎层实现的，并不是所有引擎都支持行锁，比如：MyISAM两阶段锁协议：在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放知道两阶段锁有什么意义？
如果事务中需要锁多个行，那应该把最有可能造成锁冲突，最有可能影响并发度的事务往后放死锁以及死锁检测出现死锁的解决策略：
直接进入等待，直到超时。超时时间可以通过inodb_lock_wait_timeout（默认50s） 来设置，太长无法接受，太短会有误伤，所以尽量采用第二种方案发起死锁检测：发现死锁后主动回滚死锁链条中的某一事务，让其他事务得以继续进行。将inodb_deadlock_detect设置为on表示开启这个逻辑怎么解决热点行更新导致的性能问题？ 问题的症结：死锁检测耗费了大量的CPU如果能确保业务不会出现死锁，那么可以把死锁检测关闭控制并发度：做在数据库服务端（修改mysql源码）索引的维护是有代价的
就像书本的目录一样索引就是为了加快查询的速度
索引模型： 哈希表 范围查询需要扫描全表，适用于只有等值查询的场景有序数组 等值查询和范围查询性能优异，但更新数据代价很大，适用于静态存储引擎搜索树 InnoDB使用B+树InoDB索引组织结构：
索引分为主键索引和非主键索引 主键索引(InnoDB中也称为聚簇索引)叶子节点存的是整行数据非主键索引(InnoDB中也称为二级索引)叶子节点存的是主键ID（回表） 覆盖索引可以避免回表，减少树的搜索次数，显著提升查询性能，因此覆盖索引是一个常用的性能优化手段联合索引也能有效规避回表由以上两点得出结论：非主键索引需要多扫描一棵索引树，因此在实际应用中应该尽量用主键查询索引维护 主键长度越小，普通索引的叶子节点占用的空间就会越小，普通索引占用的空间也就越小普通索引和唯一索引怎么选择？ 查询过程： 普通索引：需要在查到满足条件的记录后查找下一条记录，知道碰到第一个不满足条件的记录唯一索引：由于索引定义了唯一性，查找到第一个满足条件的记录后就会停止往下检索上面两个“不同”带来的性能差距是多少呢？答案是微乎其微InnoDB的数据是按数据页来读写的，也就是当需要读取一条数据的时候，并不是仅仅只从磁盘读一条数据，而是以页为单位将其整体读入内存中,数据页默认大小16KB更新过程：change buffer:
目的：减少随机磁盘访问（数据库里面成本最高的操作之一）工作机制：更新数据页时，如果数据页在内存中则直接更新，如果不在内存中，在不影响数据一致性的前提下，InnoDB会把这些操作缓存在change buffer中，这样就不需要从磁盘读取数据页了。在下次查询需要访问这个数据页的时候再把数据页读入内存中，然后执行change buffer中和这个数据页有关的操作change buffer中的操作应用到原数据页的操作叫做：merge 访问这个数据页会触发merge系统后台有线程会定期merge数据库正常关闭（shutdown）的过程中也会merge只适用于普通索引， 唯一索引在更新操作前都要判断唯一键是否冲突，而这必须把数据读入内存中才能判断，既然数据读进来了那直接更新即可不需要再使用cahnge buffer使用的是buffer pool的空间，在内存中有拷贝，也会被写到磁盘上change buffer使用场景：写多读少，并且页面在写完后马上被访问到的概率较小，此时change buffer效果最好，例如：账单、日志系统，如果更新后立马就会进行查询反而会增加change buffer的维护代价
普通索引和唯一索引在查询能力上是没什么差别的，主要考虑对更新性能的影响，因此建议选择普通索引（业务允许前提下） change buffer 更新流程redo log主要省的是随机写磁盘的IO消耗（转成顺序写），change buffer主要省的是随机读磁盘的IO操作mysql为什么有的时候会选错索引？
优化器的逻辑 判断是否需要创建临时表、是否需要排序、扫描行数扫描行数出了问题？索引区分度
一个索引上不同的值的个数-->索引基数mysql是怎样得到索引的基数
采样统计analyze table t 重新统计索引信息
索引选择异常处理 使用force index 表结构调整时如果没有修改代码就会出问题，而且开发的时候都不会考虑选错索引的情况，用到它说明已经出问题了修改SQL语句，引导优化器选适当的索引新建一个合适的索引供优化器选择或删掉误用索引为什么mysql会“抖”一下？
脏页：内存中的数据页和磁盘的数据不一致的时候称为脏页干净页：内存写入到磁盘后数据一致了，叫做干净页
“赊账”更新和flush过程
那么到底为什么会“抖”一下？ 平时很快的操作是在更新内存和写日志，“抖”一下的瞬间可能正在刷脏页（flush）什么时候会引发flush的过程？ 第一种情况：redo log满了，mysql只能停下其他工作推进check point系统内存不足：当需要新的数据页，而内存又不足的时候就需要淘汰一些数据页来空出内存给别的数据页使用，如果淘汰的是脏页，就要先将脏页写到磁盘系统空闲的时候： 闲着也没事就刷一刷脏页mysql正常关闭：脏页都flush到磁盘上出现以下两种情况都是会明显影响性能的: 一个查询要淘汰的脏页数量太多，导致响应时间明显变长日志写满，更新全部堵住，此时 写的性能为零所以要使用脏页控制策略来控制脏页比例从而避免以上两种情况发生InnoDB刷脏页控制策略： 告诉InnoDB能，让InnoDB知道全力刷脏页的时候能刷多快，使用innodb_io_capacity=IOPS（建议）参数告诉InnoDB磁盘能力另一个有趣的策略： 当一个查询需要flush脏页的时候这个查询就会比平常慢，而mysql中的一个机制会让这个查询更慢：在准备刷一个脏页的时候，如果这个数据页旁边的数据页刚好也是脏页，就会把这个邻居一起刷掉，而这个把邻居一起“拖下水”的逻辑还可以继续蔓延innodb_flush_neighbors这个参数就是来控制这个行为的，1为连坐机制，0为自己刷自己的，MYSQL 8.0中这个参数已经默认为0为什么逻辑相同的SQL执行效率相差距大？ 条件字段做函数操作，对索引字段做函数操作，可能会破坏索引值的有序性，优化器会放弃走搜索树隐式类型转换 数据类型转换的规则是什么？select "10" > 9 返回值为1 说明是将字符串转化成数字为什么有数据类型转换就需要走全表扫描隐式字符编码转换 utf8mb4是utf8的超集，Mysql内部会把utf8转成utf8mb4为什么只查一行的语句也这么慢？ MySQL在压力很大情况下（CPU占用率或IO利用率很高），所有语句的执行都有可能变慢查询长时间不返回 表被锁 show processlist命令查看当前语句处于什么状态等MDL锁 找到谁持有MDL锁 kill掉等flush等行锁查询慢 坏查询不一定是慢查询 一致性读需要undo log ，lock in share mode 当前读为什么只改一行的语句锁这么多？ 删除数据的时候尽量加Limit，可以控制删除的条数还可以减少加锁的范围查询结果发送流程：
获取一行，写到net_buffer，这块内存的大小由net_buffer_length参数定义。默认16K重复获取行，直到net_buffer写满，调用网络接口发送出去如果发送成功就清空net_buffer进行下一回合如果发送函数返回EAGAIN或WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer）写满了，进入等待，直到网络栈可写了再继续发送在整个过程中占用内存最大就是net_buffer_length这么大，并不会达到200G
MySQL是“边读边发”的 如果客户端接收很慢，会导致服务端由于结果发不出去，事务的执行时间变得很长全表扫描对InnoDB的影响
InnoDB的内存管理：Leastly Recently Used,LUR算法，即淘汰最久未使用数据页LUR算法基本模型： 说明：使用链表实现，最新访问的放在链表头部，当内存不足时淘汰tail节点使用该方案扫描的问题：会把当前buffer pool里面的数据全部淘汰，存入扫描过程中访问到的数据页，也就是说buffer pool里面放的主要是这个表的历史数据，那么问题来了，如果是一个线上作业的服务，Buffer pool的内存命中率会急剧下降，磁盘压力增加，SQL响应变慢
基于以上原因InnoDB未直接使用上面的LUR算法，而是对其做了改进改进后的LUR算法： 基于5:3的比例把LRU链表分成了young区域和old区域，靠近链表头部的5/8是young区，靠近尾部的3/8是old区执行流程： 状态1：访问数据页p3,由于p3在young区因此和优化前的LUR算法一样把p3放到young区头部，变成状态2状态2：之后要访问一个不存在于当前链表的数据页，这时候淘汰pm页，新插入的数据页px放在LUR_old区头部处于Old区的数据页每次被访问的时候作如下判断： 若这个数据在LUR链表中存在时间超过1秒，就把它移到链表头部存在时间少于1秒则位置不变 一秒这个时间由innodb_old_blocks_time控制 默认1000ms 这个策略就是为处理类似全表扫描做定制的，扫描的时候对young区没有影响，从而保障了buffer pool查询命中率注意：不要在主库上进行该操作，避免对数据造成二次破坏
drop/truncate语句误删表数据表 预防 账号分离 开发人员只分配DML权限，不分配truncate/drop权限，有DDL需求则找DBA支持即使是DBA人员，日常也只是用只读账号，有特殊需求的时候再使用特殊账号制定规范 在删除数据表之前先对表进行改名操作，观察一段时间，确保对业务没有影响再进行删除操作改表名的时候给表加上固定的后缀（比如：_to_be_deleted）,删除的动作必须通过管理系统执行，并且在删除的时候只能删除固定后缀的表治疗 使用全量备份，加增量备份的方式，该方案要求线上有定期全量备份，以及实时备份binlog mysql binlog数据恢复流程 取最近一次的全量备份，假设上次备份是0点用备份恢复出一个临时库从日志备份里面取出0点以后的日志把这些日志除了删除数据的语句外全部应用到临时库另一种更为快速的方案： 用备份恢复出临时实例后，将该临时实例设置成线上备库的从库延迟复制备库：保持和主库有N秒的延迟，一般的的主备存在的问题是命令很快就会被发给从库rm命令删除数据库实例 一个有高可用的MYSQL集群最不怕的就是rm删除数据了，只要不是整个集群被删除，只要恢复这个节点的数据再整入集群批量下线机器的操作会让mysql集群全军覆没，应对这种情况可以备份跨机房、跨城市保存主备延迟，导致“过期读”
强制走主库sleep方案判断主备无延迟方案 判断seconds_behind_master是否等于0对比位点确保主备无延迟对比GTID集合确保主备无延迟转载于:https://www.cnblogs.com/llgogo/p/11289350.html
相关资源：尚硅谷MySQL高级教程笔记Xmind版