MySQL笔记

逻辑架构图

引擎类型

Memory引擎

• 索引和数据是分开的，数据部分以数组的形式单独存放，主键Id索引里面，存放的是每个数据的位置；由于主键id是hash索引，因此索引上的key并不是有序的

• 堆组织表，对于数据的插入，memory是找到空位就可以执行插入操作，如果数据位置发生了变化，内存表需要修改所有的索引信息

• 也是支持B-Tree的

• 

• 存在锁细粒度的问题：内存表不支持行锁，只支持表锁，因为如果有数据更新，会将整张表锁住，并发能力不强

• 数据持久化问题：数据库重启后，内存表都会被清空

Innodb引擎

• 使用了大量的AIO处理IO请求，同时使用IOThread负责IO事件的回调处理
• 缓冲池

  • 读取时先对页进行判断（对缓存页的存在进行判断，降低IO操作），若命中则直接从内存中读取该页，否则转去读取磁盘
    

    • 在启动时，MySQL需要完成对Buffer Pool的初始化操作，为了记录Buffer Pool中哪些缓存页是可用的，这个时候缓存页对应的控制块就派上用场了，可以把所有空闲的缓存页对应的控制块作为一个节点放在链表中（作为free链表），每当需要从磁盘中加载一个数据页时，就从Buffer Pool中申请一个空闲的缓存页，填写对应的控制块信息，在将此控制块从free链表中移除
      -

    • 为了解决并发场景下对于Buffer Pool资源的竞争，可以将一个Buffer Pool拆分为多个小的Buffer Pool（推荐当Buffer Pool size >= 1G时进行此项设置）

    • 对于MySQL的InnoDB引擎的预读服务或者全表扫描，可能导致Buffer Pool内存储的数据都被替换过了，导致Cache的命中率下降，系统性能降低

    • LRU缓存机制的实现

      • 将LRU存储分为了两类（按照一定的比例），一类是热数据（存储使用频率非常高，young），一类是冷数据（存储使用频率非常低，old）
      • 同时，为了解决全表扫描时，每读一条记录就算做一次页面访问，因此为了解决这种情况，设置了一个系统参数，如果访问的间隔时间小于系统参数设置的时间，那么缓存页就不会从old区域移动到young区域的头部

    • InnoDB的预读服务

      • InnoDB认为执行当前的请求可能会读取某些页面，就预先把他们加载到Buffer Pool中，根据其触发的方式可分为如下几种
        • 线性预读：如果顺序访问了某个区（extent）的页面超过这个系统变量的值，就会触发一次异步读取本区中全部的页面到Buffer Pool的请求
        • 随机预读：如果Buffer Pool中已经缓存了某个区的13个连续的页面，不论这些页面是不是顺序读取的，都会触发一次异步读取本区中所有其他的页面到Buffer Pool的请求

  • 修改页的时候，首先修改缓存池中的页，然后以一定的频率将缓存池中的页刷新到磁盘上（采用checkpoint机制）；缓存池中的页类型有：索引页、数据页、undo页、插入缓冲页、自适应哈希索引、InnoDB存储的锁信息、数据字典信息

    • InnoDB的内存结构和特性（https://juejin.im/post/5b82b62e6fb9a019d80a9709）
    • 缓冲池：InnoDB存储引擎是基于磁盘存储的，记录按照页的方式进行管理；数据库进行读取操作时，将磁盘中读到的页放在缓冲池中（缓冲池的大小直接影响数据库的整体性能，可以进行配置：innodb_buffer_pool_size）
    • 数据页与索引页：Page是InnoDB中最基本的结构，也是InnoDB磁盘管理最小的存储单位
    • 插入缓冲（二级索引的数据操作缓存）
      • 行记录的插入通常是按照主键递增的顺序进行插入的，但是如果主键的类型类似于UUID的话，插入和辅助索引一样，随机插入的；就算主键是自增类型，但是插入的值是指定的值话，那么插入也是随机的
      • 对于非聚集索引的插入或者更新操作，不是每一次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓存池中，若在，则直接插入，否则先放入到insert buffer中，随后按照一定的频率进行insert buffer和辅助索引页子节点的merge操作
        • Insert buffer（change buffer）使用需要满足的条件：索引是辅助索引，索引不是唯一的（如果是唯一的话，每次插入必须执行io操作，用于判断插入数据的唯一性，反而需要频繁读取数据页，增加了IO的消耗）
    • 自适应哈希索引（针对二级索引的优化，通过缓冲池的B+树页构建而来）
      • InnoDB引擎根据访问的频率以及模式来自动的为某些热点页建立哈希索引
      • 只可用来进行等值搜索的查询，对于范围查询，Hash索引是无法实现的（或者说需要全表扫描获取数据然后筛选）
    • 锁信息：InnoDB存储引擎会在行级别上对表数据进行上锁。不过InnoDB也会在数据库内部其他很多地方使用锁，从而允许对多种不同资源提供并发访问
    • 数据字典信息：数据字典是对数据库中的数据、库对象、表对象等的元信息的集合。在MySQL中，数据字典信息内容就包括表结构、数据库名或表名、字段的数据类型、视图、索引、表字段信息、存储过程、触发器等内容。MySQL INFORMATION_SCHEMA库提供了对数据元数据、统计信息、以及有关MySQL server的访问信息（例如：数据库名或表名，字段的数据类型和访问权限等）。该库中保存的信息也可以称为MySQL的数据字典。
      -

  • checkpoint技术

    • 缩短数据库的恢复时间；缓冲池不够用，将脏页刷新到磁盘；重做日志不够用，刷新脏页
    • 当数据库宕机时，无需重做所有的日志，仅仅需要对checkpoint点之后的数据重做即可，因为可以保证checkpoint点之前的数据已写入磁盘中了
    • 两种checkpoint（sharp checkpoint以及fuzzy checkpoint）
      • Sharp checkpoint：发生在数据库关闭时将所有的脏页都刷新回磁盘
      • uzzy checkpoint：数据库运行时采用，刷新部分脏页数据

  • 两次写（如果OS的文件系统是ZFS的话是可以不开启两次写功能的）

    • 对缓冲池的脏页进行刷新时，并不直接写磁盘，而是通过memcpy函数将脏页先复制到内存中的double write buffer中，然后顺序写入共享表空间的物理磁盘上，然后马上调用fsync函数，同步磁盘，避免操作系统缓冲写带来的问题；然后double write页的写入之后，再将double write buffer中的页写入各个表空间文件中去；如果操作系统在将页写入磁盘的过程中发生了崩溃，在恢复过程中，InnoDB存储引擎可以从共享表空间中的doublewrite中找到该页的一个副本，将其复制到表空间文件中，再应用重做日志
      

锁

• latch与lock
  • latch锁用于保证并发线程下对临界资源操作的正确性，通常没有死锁检测机制；读写锁
  • ock的对象是事务，用来锁定数据库中的对象，如表、页、行；并且lock的对象仅在事务commit或者rollback后进行释放
• 全局锁
  • 命令：Flush tables with read lock
  • 用于备份业务场景，但是由于全局锁的开启，相当于将整个业务置于停摆位置（优化：读提交隔离级别可开启一个事务进行数据备份，此时该线程session的数据为开启时的数据快照信息，此时间点之后的数据插入、更新是“不可见”的）
  • FTWRL 前有读写的话 ,FTWRL 都会等待 读写执行完毕后才执行
• 表锁
  • 意向锁
    • 不会阻塞除全表扫描以外的任何请求；为了在一个事务中揭示下一行将被请求的锁类型
    • 意向共享锁：当事务准备在某条记录上加S锁时，需要先在表级别加一个IS锁
    • 意向独占锁：当事务准备在某条记录上加X锁时，需要先在表级别加一个IX锁
  • 表锁与元数据锁（MDL），限制了别的线程的读写，也限制了本线程接下去的操作对象
  • 命令：lock table … read/write
  • 元数据锁无需显示使用、确保读写的正确性
  • 所有对表的增删改查操作都需要先申请 MDL 读锁，MDL直到事务提交才会释放
  • MDL：对表的增删改查加的是读锁，对表结构的修改加的是写锁，读锁不互斥（http://mysql.taobao.org/monthly/2015/11/04/）
    • 存在锁的升降级
    • 对于drop table test.t1操作会一次性加上三个锁操作（一次性加多个排它MDL锁）
    • 对于alter table和create table场景中，存在锁升级，从共享锁升级为互斥锁
      • alter table中，又存在两种算法机制
        • 如果是copy算法，则整个MDL顺序如下
          • 刚开始打开表的时候，用的是 MDL_SHARED_UPGRADABLE 锁；
          • 拷贝到临时表过程中，需要升级到 MDL_SHARED_NO_WRITE 锁，这个时候其它连接可以读，不能更新；
          • 拷贝完在交换表的时候，需要升级到是MDL_EXCLUSIVE，这个时候是禁止读写的
          • 因此，copy算法存在两次锁的升级操作
        • 如果是inplace算法，则整个MDL顺序如下
          • 和copy算法一样，刚开始打开表的时候，用的是 MDL_SHARED_UPGRADABLE 锁；
          • 在prepare前，升级到MDL_EXCLUSIVE锁；
          • 在prepare后，降级到MDL_SHARED_UPGRADABLE（其它线程可以读写）或者MDL_SHARED_NO_WRITE（其它线程只能读不能写），降级到哪种由表的引擎决定；
          • 在alter结束后，commit前，升级到MDL_EXCLUSIVE锁，然后commit
          • 因此，inplace算法存在两次锁升级，一次锁降级
  • 行锁（共享锁：允许事务读一行锁、排他锁：允许事务删除或更新一行数据）

    • 行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放

      • 什么要等到事务结束才会释放锁
        • 事务的ACID性质中的隔离性要求，如果选择语句执行完就释放锁，那么事务B的操作结果对A可见，违反了隔离性的要求
        • 不允许自私的存在：如果事务A是读己提交，而事务B为读未提交；B事务在执行完update语句后，直接释放锁，这个时候事务A读到了B的数据，结果造成A的实际操作结果与对应的事务隔离级别不符合
      • 示例图片
        -

    • InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁

    • 自动加锁。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁；对于普通SELECT语句，InnoDB不会加任何锁

    • 三种行锁算法

      • Record Lock：单个记录上的锁，总是会去锁住索引记录

      • Gap Lock：间隙锁，锁定一个范围，但是不包含记录本身，其目的就是为了阻止多个事务将记录插入到同一范围内，避免幻读的发生

        • 阻止多个事务将记录插入到同一个范围内
        • 间隙锁之间并不存在互斥现象，而是间隙锁与插入的动作存在互斥现象
        • 为了解决幻读问题
        • 数据更新时会在每两个数据之间加入间隙锁
        • 如果对于最后一条记录之后的间隙，间隙锁如何工作？
          • 数据页有两条特殊的记录：Infimum记录，表示该页面中最小的记录、Supremum记录，表示该页面中最大的记录
          • 可以给索引中的最后一条记录，那条记录所在页面的Supremum记录加上一个gap锁
            

      • Next-Key Lock：与Gap Lock锁定一个范围，并且锁定记录本身（左开右闭的一个区间）

        • 对于唯一索引的情况下，会退化为record lock锁，直接锁定某条记录，而不在是一个范围区间（锁唯一索引和非唯一索引的区别）

      • 加锁的几个优化的点

        • 原则一：加锁的基本单位是next-key-lock
        • 原则二：只有访问到的对象才会加锁
        • 优化一：索引上的等值查询，给唯一索引加锁时，next-key-lock退化成行锁
        • 优化二：索引上的等值查询，当向右遍历时且最后一个值不满足等值查询条件时，next-key-lock退化成间隙锁

索引（对索引字段做函数操作，可能会破坏索引值的有序性（相当于索引值是一个未知的值，需要全部取出计算），进而导致无法使用索引）

• MyISAM引擎，由于采用了压缩（前缀压缩）索引，虽然说减少了索引存储的使用空间，但是由于压缩后，每个值的压缩前缀都依赖于前面的值，因此无法采用二分查找加速查询，只能从头扫描
• 使用时的代价
  • 空间上的代价：每建立一个索引就需要建立一棵B+树
  • 时间上的代价：每次对表的数据进行数据更改操作时，都需要去修改各自对应的B+树索引；可能需要做额外的页分裂、记录移位、页面回收操作
• Hash（适用于等值查询）
  • 对于“=”与“<=>”(<=>可以比较NULL值，而=不可以)
  • 优化器无法使用其加速order by
  • 对于范围操作不适合
• B+树

  • 对于“>”、“<”、“>=” 、“<=”、“between”、“!=”、“<>”、“like”

  • 范围操作查询

  • 什么是B树

    • 一棵m阶的B树，其每个节点最多含有m个孩子（m >= 2）；所有叶子节点均出现在同一层

  • 为什么说B+数更适合操作系统的文件索引 or 数据库索引

    • 高扇出性：数据库中的B+树高度基本在2～4层，因此IO操作次数一般都在2～4次
    • 根据B+树索引并不能找到一个给定值的具体行，B+树索引能找到的只是查找数据行所在的页。然后数据库通过把数据页读入内存，再在内存中进行查找，最后得到查找的数据
    • B+树的分支节点只是叶子节点的索引而已，而B树的分支节点也存数据；正因如此，每次读入内存的关键字B+树多于B树，一次性读内存中的需要查找的关键字越多。相对来说IO读写次数就降低了
    • B树结构（查找顺序的话还需要进行中序遍历，相比B+树，IO的开销更多）
      

  • B+树结构
    

    • 数据页之间是双向链表，加快了搜索速度
• 主键是逻辑上的、索引是物理上的
  • 主键索引直接扫描B+索引树即可
  • 非主键索引需要先扫描索引树，根据得到的数据再次扫描另一个树才算完成查找（回表）
• 聚簇索引
  • 索引文件和数据为同一份文件，B+tree的叶子节点的data就是数据本身
  • 一张表只能有一个聚集索引（聚集索引决定了表中数据的物理存储顺序，而物理存储顺序只能存在一种，因此又反过来限制了只能存在一个聚簇索引）
  • 聚集索引查询走内存的次数：与节点所在的层数有关
• 非聚簇索引（包含索引列+主键）
  • B+tree的叶子节点的data是数据存放的地址信息，查找时需要多一次指针定位信息（回表操作，由于非聚簇索引树存储的只是索引的地址信息，并未记录整行记录信息，因此需要一个回表操作将行记录信息取出）
  • 并不影响数据在聚集索引中的组织，因此每个表可以有多个非聚簇索引
  • 非聚集索引查询走内存的次数：与节点所在的层数+1
• 联合索引
  • 本质也是一棵B+树，唯一不同的是联合索引的键值数量大于等于2
  • 在第一个键值有序的情况下对第二个键值做了排序处理
  • 覆盖索引：辅助索引中就可以查询信息，则不会查询聚集索引中的记录（由于辅助索引不包含整行记录的所有信息，因此大小远远小于聚集索引，减少大量的IO操作）
• 全文索引
  • 全文索引的实现是采用了倒排索引，在辅助表中存储了单词与单词自身在一个或多个文档中所在位置之间的映射
    • {单词，单词所在文档的ID}、{单词，（单词所在文档的ID、在具体文档中的位置）}
    • 文档中的删除操作并不会删除全文索引中的数据，相反是会在对应的DELETE表中插入记录，因此索引会变得非常大，此时需要采用命令：OPTIMIZE TABLE进行优化，将删除的记录从索引中彻底删除
    • 每张表只能有一个全文检索的索引，由多列组合而成的全文索引列必须使用相同的字符集与排序规则
• Multi-Range Read优化（比如根据辅助索引查出来的主键信息进行排序操作，尽可能降低随机IO操作）
  • 减少磁盘的随机访问，将随机访问转化为较为顺序的数据访问（对 IO-bound类型的sql查询带来优化，用于range、ref、eq_ref类型的查询）
  • 随机访问转为较为有序的数据访问：查询辅助索引时，首先根据得到的查询结果，按照主键进行排序，并按照主键排序的顺序进行书签查找（回表查询），减少缓冲池中页的替换次数（主键排序，使得数据在同一个页中的可能性更高）
• ICP优化
  • 可在根据索引查找记录的同时根据where条件对记录进行过滤，降低IO操作的性能消耗

临时表（典型使用场景：分库分表查询）

• 临时表不等同于内存表，内存表是指使用引擎为memory的表
• 临时表可以使用各种引擎类型；一个临时表只能被创建它的的session访问，对其他线程不可见；临时表可以和普通表同名
  • 创建一个frm文件保存在临时文件目录下，文件名是#sql{进程id}_{线程id}_序列号
  • 每个表都有一个table_def_key（临时表后面再接上 server_id+thread_id）
  • 以上两个原因使得临时表可以和普通表同名而不出错（表面上的表同名，而实际并未表同名）
• 临时表也会被同步写入binlog中（主备复制、同时还要根据binlog的格式设置）
  • Binlog_format=“row”的时候，是不会将临时表的操作记录到bin log中的
  • 磁盘临时表：磁盘临时表使用的引擎默认是InnoDB，根据tmp_table_size限制内存临时表的大小，临时表大小超过设定的参数，就直接转换为磁盘临时表（存在索引B树）
  • 内存临时表：排序时采用优先队列排序算法（不需要额外的排序空间）
  • 因此临时表索引的不同可能导致最终排序结果存在细微的不同（堆排序可能导致本该在第一个的元素出现在最后的位置）
• 分库分表查询
  • 如果给的select语句没有使用到分区字段，需要去所有分区中查找，然后统一操作
    • 第一种，在proxy层去做，处理速度快，拿到分库数据后，直接在内存中参与计算
      • 复杂语句处理是一个难点，如果数据量太大，又不容易进行计算
    • 各个分库拿到数据后，汇总到一个mysql实例的表中，然后在该实例上进行逻辑操作
• 数据库表分区的作用
  • 表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他是均是历史数据
  • 分区表的数据更容易维护，想批量删除大量数据可以采取清楚整个分区的方式
  • 分区表的数据可以分布在不同的物理设备上
  • 可以使用分区表避免某些特殊的瓶颈，例如单个索引的互斥访问
  • 可以备份和恢复独立的分区
  • 访问分区表时，MySQL需要打开并锁住所有的底层表，这是分区表的另一个开销，此操作发生在分区过滤之前
• 分区表的限制
  • 所有分区必须都使用相同的存储引擎
  • 分区函数所能使用的函数和表达式也有一些限制
  • 某些存储引擎不支持分区

sql语句

• 解析顺序
  • from -> on -> join -> where -> group by -> avg、sum… -> having -> select -> distinct -> order by -> limit
• 查询语句中索引的使用：use index 建议数据库引擎使用某某索引；force index则是强制数据库引擎使用该索引

数据库隔离级别

• Read Uncommitted（读写均不使用锁、数据一致性最差、脏读）、读取到了尚未commit的数据，即缓冲池中被修改的页还未刷新到磁盘中，数据库实例内存中的页和磁盘中的页的数据是不一致的
• Read Commited（使用写锁、读数据出现数据不一致、出现不可重读）、存在读出来的数据值前后不一致（非锁定一致性读）
• Repeatable Read（使用读锁和写锁、间隙锁）、解决了同一条记录读出来前后不一致的问题
• Serializable（事务串行化调度、避免数据插入无法加锁而导致的幻读、最高隔离级别、强制事务排序）

事务

• CID的实现对应
  • 事务的隔离性采用锁机制实现，对于其他的三个：原子性、一致性、持久性，则是通过undo、redo来完成
• 事务执行的简化流程
  • 记录 redo log/undo log日志，确保日志刷新到磁盘上永久存储
  • 更新数据记录，缓存操作并异步刷新到磁盘
  • 提交事务，redo log日志中写入commit记录
    • commit操作主要做的事情
      • 清理undo log信息
      • 释放锁资源
      • 刷新redo日志，确保redo落盘
      • 清理savepoint列表
• 事务类型
  • 扁平型事务
  • 带有保存点的扁平型事务：可以回滚至任意保存点
  • 链事务：事务只能回滚到上一保存点
  • 嵌套事务：任何子事务都在顶层事务提交后才真正进行提交；并且任意一个事务的回滚，其子事务均会回滚
• 事务 T 启动的 时候会创建一个视图 read-view，之后事务 T 执行期间，即使有其他事务修改了数据，事务 T看到的仍然跟在启动时看到的一样（此情况除非这是的事务隔离级别为可重复读），InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view，用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现
• InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的（与后面的视图高低水位存在联系、因此在一致性视图中的高水位根据这个特性能够知道新的事务在不在此时的一致性视图中）
• 每次事务更新数据的时候，都会生成一个新的数据版本，并且 把transaction id赋值给这个数据版本的事务 ID，记为 row trx_id
• InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务ID。“活跃”指的就是，启动了但还没提交
• 快速创建快照的原因——>所有数据都有多个版本（每个数据版本有自己的row trx_id）
• 更新数据都是先读后写，读是当前读（只能读取当前值），因为更新数据时不能在历史版本上更新，否则可能造成更新丢失
• 事务
  • 保存现在正在执行的所有事务ID列表，同时获取高水位（保存的事务ID集合的ID最大值+1）——>构成了一致性视图
  • 上面的获取步骤，在代码层面上是在事务系统的锁保护下做的，可以认为是原子操作
  • 视图的级别
    • 可重复读隔离级别下，事务开始的时候创建一致性视图，接下来该事务里的所有查询都共用这个一致性视图
    • 读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图
  • 事务被阻塞时，查询sys_innodb_lock_waits表，根据lock_table=schame.table查询，查看锁的占用情况
  • Select 语句结尾加上 lock in share mode为当前读
  • Redo log记录的是事务修改后的数据，Undo log记录的是事务修改前的数据；由于事务随时存在回滚，因此这两个文件都会被写入数据
• MVCC（Multiversion Concurrency Control、多版本并发控制）
  • 只工作于读提交以及可重复读隔离级别，可用乐观锁以及悲观锁实现
  • 通过保存数据在某个时间点的快照来实现，根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的
  • innodb的MVCC实现
    • 每行记录后面保存两个隐藏的列来实现，一个是创建行的时间，另一个保存行的删除时间（不是真正的时间信息，而是数据的版本号：乐观锁的大致实现）
    • 每开始一个新的事务，系统版本号会自动递增；事务开始时刻的系统版本号作为事务的版本号，和每行记录的版本号进行比较
      • 如果被访问版本的trx_id属性值与ReadView中的creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
      • 如果被访问版本的trx_id属性值小于ReadView中的min_trx_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。
      • 如果被访问版本的trx_id属性值大于ReadView中的max_trx_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。
      • 如果被访问版本的trx_id属性值在ReadView的min_trx_id和max_trx_id之间，那就需要判断一下trx_id属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。
    • 可重复读隔离级别下的MVCC
      • Select
        • 根据两个条件检查每行记录：只查找版本早于当前事务版本的数据行（确保事务读取的行要么是已存在的，要么是当前事务插入或者修改过的），并且行的删除版本要么未定义，要么大于当前事务版本号（确保事务读取到的行在事务开始之前未被删除）
    • 通过这两个隐藏列的设定，降低了大多数读操作的加锁情况，同时保证了只会读取到符合标准的行；但是缺点是消耗了额外的空间

相关命令

• explain、desc：如何执行 SELECT 语句的信息，包括 select 语句执行过程表如何连接和连接的次序。
  • explain几个字段：
    • id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
    • select_type: SELECT 查询的类型。
      • SIMPLE, 表示此查询不包含 UNION 查询或子查询，
      • PRIMARY, 表示此查询是最外层的查询，
      • UNION, 表示此查询是 UNION 的第二或随后的查询，
      • DEPENDENT UNION, UNION 中的第二个或后面的查询语句, 取决于外面的查询，
      • UNION RESULT, UNION 的结果，
      • SUBQUERY, 子查询中的第一个 SELECT，
      • DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果，
    • table: 查询的是哪个表，
    • partitions: 匹配的分区，
    • type: join 类型，
    • possible_keys: 此次查询中可能选用的索引，
    • key: 此次查询中确切使用到的索引，
    • ref: 哪个字段或常数与 key 一起被使用，
    • rows: 显示此查询一共扫描了多少行. 这个是一个估计值，
    • filtered: 表示此查询条件所过滤的数据的百分比，
    • extra: 额外的信息（using index=覆盖索引操作；using index condition=IPC优化）
  • 是用于查询语句执行计划的
• show processlist：查看当前mysql进行的线程信息，实时查看sql执行状态
• 获取表结构
  • desc “表名”
  • show columns from “表名”
• 获取表的索引以及索引的基数
  • show index（keys） from “表名”
• 重新统计索引信息
  • analyze table “表名”
• 查看锁请求信息

更新过程

• Change buffer（可持久化的数据，内存中有拷贝，也会被写入到磁盘上、不适用于唯一索引的更新）
  • 更新操作的缓存
  • 当需要更新一个数据页时，如果数据页在内存中就直接更新；如果数据页还没有在内存中的话，将更新操作缓存在change buffer中；下次访问该数据页时，执行change buffer中与该页有关的操作，将数据merge
  • 唯一索引的更新不使用change buffer，change buffer适用于普通索引以及写多读少的业务
    • 如果写入后又立马需要读取，这个时候需要触发merge，反而增加了IO操作
    • 如果change buffer支持唯一索引的话，由于唯一索引的特性，每一次写入都需要加载所有的数据页（可能不需要所有的数据页）进行唯一索引的唯一性判断，反而增加了读I/O消耗
  • Redo log与change buffer的区别
    • Redo log减少随机写磁盘的IO消耗，change buffer减少的是随机读磁盘的IO消耗
• 于MVCC存在，因此对于update、delete操作，不可能在事务中进行数据删除，所有的数据删除操作均在purge操作中完成

查询缓存

• 缓存形式为<sql语句，结果>，被直接缓存在内存中（可以通过配置query_cache_type为demand，则默认无查询缓存）

日志功能

• undo（实现事务的一致性、帮助事务回滚以及MVCC的实现）
  • 是逻辑日志，用于将数据库物理地恢复到执行语句或事务原来的样子
  • MVCC的辅助实现，用户读取一行记录时，若该记录已经被其他事务占用，当前事务会通过undo日志读取之前的版本信息，以此实现非锁定读取；非锁定读取提高了MySQL的并发性
  • 一般每对一条记录做一次改动，就对应着一条undo日志
  • 不同事务执行过程中产生的undo日志需要被写入到不同的Undo页面链表中
  • 
• Redo log（Innodb所特有，引擎层、重做日志、实现事务的原子性与持久性、物理格式，记录着对于每页的修改）
  • 两个指针：write pos以及checkpoint
  • 对于InnoDB引擎来说，如果redo-log提交完成了，那么事务就无法回滚
  • write-ahead logging，先写日志，在写磁盘（空闲时或必要时）
  • 确保mysql出现异常时，所提交的记录都不会丢失，保证crash safe能力
  • redo log 是物理日志，记录的是“在某个数据页上做了什么修改”
  • 空间固定，循环写；
  • 两段提交（prepared、commit）、两段提交为了保证redo log与bin log所记录事务的一致性
  • 没有记录数据页的完整数据，没有能力自己去更新磁盘数据页
  • Redo log的写入机制

    • 事务执行过程中，生成的redo log是要先写到redo log buffer中的

    • Redo log的三种状态
      

    • 存在redo log buffer中，物理上是mysql进程内存中——即红色部分

    • 写到磁盘（write），但是还没有持久化（fsync），物理上是在文件系统的page cache中——黄色部分

    • 持久化到磁盘，对应的是hard disk——绿色部分

    • 写入策略

      • nnodb_flush_log_at_trx_commit=0时，每次提交事务只是把redo log停留在redo log buffer中
      • Innodb_flush_log_at_trx_commit=1时，每次提交事务将redo log持久化到hard disk中
        • 设置成1时，redo log在prepare阶段就需要持久化一次
        • redo log（prepared）——> B：bin log（finish）为什么能够实现事务崩溃恢复的原因，因此此时的redo log由于持久化到了磁盘，因此是完整的
      • Innodb_flush_log_at_trx_commit=2时，每次提交事务只是把redo log写入到page cache中
      • 同时还有一个后台线程，按固定的时间频率将redo log buffer中的日志调用write写入到文件系统的page cache中，然后在调用fsync持久化到磁盘（虽然说可以提高数据库的事务处理能力，但是可能存在最后一次事务丢失的情况）

    • 当redo log buffer占用的空间达到innodb_log_buffer_size一半的时候，后台线程会主动写盘，但是由于事务并未提交，因此也仅仅是执行write操作，并不会执行fsync，即数据只是停留在了page cache中

    • 并行事务提交的时候，顺带将这个事务的redo log buffer持久化到磁盘

• Bin log（server层、归档日志）
  • 记录所有的逻辑操作
  • binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”
  • 日志记录采用追加形式
  • 有三种日志格式
    • Row：日志中会记录每一行数据被修改的形式，在slave端在对相同的数据进行修改；可以无需记录执行的sql的上下文相关信息，仅仅需要记录哪一条记录被修改了，修改成什么样了；如果遇到alter table语句的话，那就是每一条记录都被修改，都要记录到binlog日志中，容易造成日志内容大量记录，对IO、存储资源是一个巨大的消耗
    • Statement：每一条会修改数据的 SQL 都会记录到 master 的 bin-log 中。slave 在复制的时候 SQL 进程会解析成和原来 master 端执行过的相同的 SQL 再次执行；相比row，减少了日志量，但是需要记录每条语句执行的时候一些相关的信息——上下文信息，才能确保所有语句在slave端执行的时候能够得到和在master端执行时候相同的结果（上下文信息的复制由于mysql的升级可能导致无法恢复sql语句执行的上下文信息）
      • 可能造成主备不一致的情况：在执行delete … limit时，可能主备库的索引不一样，导致删除的数据也不相同
    • Mixed：会根据执行的每一条具体的 SQL 语句来区分对待记录的日志形式，也就是在 statement 和 row 之间选择一种（在主备之间数据同步时，MySQL会自动判断语句插入是否带来数据不一致而自动选择row or statement）
  • Innodb引擎使用的是WAL（write ahead logging，预写式日志，先写内存，等到适合的时机再把内容刷到磁盘中）技术，执行事务的时候，写完内存和日志，事务就算完成了。如果崩溃，要依赖日志来恢复数据页
  • binlog写入机制

    • binlog的写入逻辑：在事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog中；一个事务的binlog是不能被拆开的，无论这个事务多大，也要确保一次性写入（确保事务的完整性，要么成功，要么不成功，原子性）

      • 写binlog的时机：sql语句或者transaction执行完，但任何相关的locks还未释放或事务还未最终commit前，保证了binlog记录的操作时序与数据库实际的数据变更顺序一致（一致性）

    • 系统给binlog cache分配了一片内存，每个线程一个，并且有binlog_cache_szie控制每个线程内binlog_cache所占内存的大小

    • Binglog cache ——> binlog files（os page cache中）——> 落地到磁盘中
      

    • write以及fsync的时机由参数sync_binlog控制

      • Sync_binlog=0的时候，每次事务提交都只write，而不fsync
      • Sync_binlog=1的时候，每次事务提交都会进行fsync
      • Sync_binlog=N(N > 1)的时候，表示每次提交事务都write，但积累N个事务后才fsync
• A：redo log（prepared）——> B：bin log（finish）——> C：redo log（commit）
• redo log和bin log的联系通过XID联系起来的

Order by工作

• 每个线程分配一块内存用于排序——sort_buffer
• 当单行记录的长度超过mysql指定的长度之后，mysql会采取新的排序算法规则：查询主键之后回表查询

常见索引优化器问题

• 代价优化器
  • 一条查询语句的执行成本分为：I/O成本、CPU成本
  • 如果涉及范围查询，那么涉及两个概念：区间最左记录（假设在页a）以及区间最右记录（假设在页b），这个时候涉及计算这两条记录之间的页数，由于每一条目录项记录对应着一个数据页，所以就计算页a与页b他们父节点对应的目录项之间隔着几条记录
• 统计信息：索引的区分度（一个索引上不同的值越多，区分度越好、不同值的个数成为基数）
• 计算索引的基数采用抽样统计的方法，默认选择n个数据页，统计后取平均值然后乘以这个索引的页面数

主备场景

• 主备场景的流程
  

• 数据一致性的保证

  • 备库（从库设置为只读模式：对于同步更新的线程，由于权限是root，可以无视readonly）
  • 备库执行从主库获取到的binlog进行执行即可保证数据一致性

• MySQL的Crash Safe

  • 为什么会出现crash safe：slave存在两个线程（IO-Thread：拉取master的bin-log以及SQL-Thread：执行bin-log中的sql语句），当sql-thread执行完sql语句后，会去执行Update replication info files (master.info, relay_log.info)；但是由于应用bin-log以及更新位点信息到文件并不是原子操作，很可能bin-log应用了四条SQL语句，但是位点记录更新只更新了前面三条
  • MySQL如何应对
    • 在MySQL 5.5版本中，设置了两个参数信息sync_master_info=1 和sync_replay_log_info=1 来保证 Slave 的两个线程每次写一个事务就分别向两个文件同步一次 IO thread 和 SQL thread 当前执行的位点信息。当然同步操作不是免费的,频繁更新磁盘文件需要消耗性能。但是，仍然可能存在crash问题，如果crash时机发生在IO thread以及SQL thread之间，还是存在位点信息同步不等的情况
    • MySQL 5.6版本：将更新位点信息存放到表中，并且和正常的事务一起执行，进而保障 apply binlog 的事务和更新 relay info 信息到 slave_relay_log_info 的原子性；
    • 基于GTID模式实现的crash safe：从库B将自己的GTID信息发送给主库A，主库A去计算自己的事务集合set-a与从库的事务集合set-b的差集（存在于set-a但不存在于set-b），判断主库A中的bin-log是否包含了这些集合所需的事务
      • 如果全部包含，则主库A从bin-log文件中找到第一个不在set-b的事务，发送给从库B，并且从这个事务开始顺序读取bin-log发送给从库B
      • 如果不包含，直接报错
      • 此模式下slave是基于bin-log中的GTID信息进行复制的，因此为了确保bin-log的落地及时性，需要设置双1模式
      • 

数据库性能调优

• 缓冲池大小对的设置（与数据+索引数据的总大小有关，设置的缓冲池大小应该大于数据+索引两种数据的大小总和）