系列
MySQL实战45讲阅读笔记-MySQL入门
MySQL实战45讲阅读笔记-日志
MySQL实战45讲阅读笔记-锁
MySQL实战45讲阅读笔记-索引
MySQL实战45讲阅读笔记-MVCC

MySQL的使用作为后端必须要掌握的一个技术，还是需要了解下基本的原理，方便在我们工程实践中对数据库的运用有更深入的了解，自己写的SQL背后会发生什么事情，所以还是很有必要学习下基本的原理的；

当然这几篇是对MySQL官方文档、网上博客和MySQL实战45讲的总结，没有翻阅源码，总结的知识点都是来自于别人的和别人的别人的所以可能相当浅显且错误百出，终究只是个阅读笔记；

模块

连接器

连接器负责跟客户端建立连接、获取权限、维持和管理连接。
在收到连接请求后，连接器会到权限表里面查出你拥有的权限。之后这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。

可以通过show processlist或者show full processlist显示当前连接，前者只显示100条后者显示全部，也可以到information_schema的PROCESSLIST表查看；

• 长连接
  在连接在建立之后，就一直打开，后续请求可以重用此连接。基本步骤是：连接→数据传输→维护连接→数据传输..→关闭连接。
  优势：建立连接的过程通常是比较复杂，使用长连接可以避免频繁建立连接的开销；
  劣势：维护长连接是需要消耗内存的。如果使用过多长连接，会消耗大量内存资源，连接太少会影响用户的响应时间。

• 短连接
  执行操作后就关闭连接。基本就是每一次操作数据库，都要打开和关闭数据库连接，基本步骤是：连接→数据传输→关闭连接。
  优势：用完即断，不会占用过多内存；适用高并发情况下的请求，保证效率；
  劣势：建立和销毁连接开销大，在高并发情况下瞬时请求过大容易造成内存资源紧张；

不同场景下有不同的选择，没有绝对正确只有适合项目的，JAVA中配合JDBC的连接池一般情况下是推荐使用长连接；长连接的连接时长是通过wait_timeout控制；

线程管理也是在这个模块，可以使用show variables like 'thread%'查看相关参数；

-------------------------
thread_cache_size      28
thread_handling        one-thread-per-connection
thread_stack           262144
-------------------------

1. thread_handling表示线程配置模型

• One-thread-per-connection
  每个客户端的连接都对应着一个线程，相应的操作也在同一个线程。请求结束后，销毁线程，这种方式在高并发情况下，会导致线程的频繁创建和释放；
• No-Threads
  在主线程上面处理连接
• Pool-of-threads
  使用线程池，会在服务器上维护了一个线程池，避免为每个连接都创建销毁一个线程。

2. thread_cache_size
   服务器最大缓存的线程数以供重用。当客户端断开连接时，如果客户端缓存的线程少于thread_cache_size，则将其放入缓存中 。当客户端有新的连接时先去从高速缓存中获取的线程来满足线程请求，并且仅当高速缓存为空时才创建新线程。
   默认值：8 + (max_connections / 100)

show global status like 'thread%'
-------------------------
Threads_cached         13    当前线程池中缓存有多少空闲线程
Threads_connected      37    当前的线程使用数量
Threads_created    196289    已经创建的线程总数
Threads_running         1    当前激活的线程数
-------------------------

可以通过高峰期查看Threads_created和Threads_created调整thread_cache_size；
根据物理内存推荐值(网上都这么说) 1G->8; 2G->16;3G->32; >3G->64

参考来自MySQL官方文档

查询缓存

Query Cache是用来缓存所执行的SELECT语句以及该语句的结果集，MySql在实现Query Cache的具体技术细节上类似典型的KV存储，就是将SELECT语句和该查询语句的结果集做了一个HASH映射并保存在一定的内存区域中；如果缓存命中，将省略优化器和执行器的相关步骤，效率极高；
查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。从MySQL5.7.20就不被推荐使用，而在MySQL8中已经删除该模块了；只有在特定的静态表中才推荐使用；

show global status like 'Qcache%'
-------------------------
Qcache_free_blocks        1    缓存池中空闲块的个数
Qcache_free_memory  1031832    缓存中空闲内存量
Qcache_hits               0    缓存命中次数
Qcache_inserts            0    缓存写入次数
Qcache_lowmem_prunes      0    因内存不足删除缓存次数
Qcache_not_cached   1329826476 查询未被缓存次数
Qcache_queries_in_cache   0    当前缓存中缓存的SQL数量
Qcache_total_blocks       1    缓存总block数
-------------------------

query_cache_type可以设置为0(缓存禁用)或者2(通过SQL_CACHE指定需要缓存的查询)；
打开查询缓存对读和写操作都会带来额外消耗：读查询在开始之前必须先检查是否命中缓存；如果这个读查询可以被缓存，那么当完成执行后，需要将结果存入缓存；每次写入操作时，需要将对应表的所有缓存都设置失效。如果缓存较大或碎片很多，则会带来很大消耗。

优化器

在创建内部分析树之后，MySQL应用了各种优化技术。这些技术可以包括，重写查询，扫描表的顺序以及选择要使用的正确索引。对于连接查询，MySQL优化器调查的可能计划数量随着查询中引用的表的数量呈指数增长。对于少量表（通常小于7到10），这不是问题。但是，当提交较大的查询时，在查询优化中花费的时间可能很容易成为服务器性能的主要瓶颈。

更灵活的查询优化方法使用户能够控制优化器在搜索最佳查询评估计划时的详尽程度。一般的想法是，优化器调查的计划越少，编译查询所花费的时间就越少。另一方面，由于优化器会跳过某些计划，因此可能无法找到最佳计划。

查询语句更新流程

InnoDB

InnoDB是一种平衡高可靠性和高性能的通用存储引擎。在MySQL 5.7中，InnoDB是默认的MySQL存储引擎。
优势
DML操作遵循ACID(原子性，一致性，隔离性和持久性)模型，具有提交，回滚和崩溃恢复功能的事务来保护用户数据。
行级锁定和Oracle风格的一致性读取可提高多用户并发性和性能。
InnoDB表格将数据排列在磁盘上，以根据主键优化查询 。每个InnoDB表都有一个称为聚簇索引的主键索引 ，用于组织数据以最小化主键查找的I / O.
InnoDB表的DML语句在事务的上下文中操作，因此它们的效果可以作为单个单元提交或回滚。

• DML
  数据操纵语言，一组 SQL执行语句 INSERT、UPDATE、 DELETE和SELECT ... FOR UPDATE;

innodb架构

InnoDB内存结构

缓冲池 Buffer Pool

缓冲池是主存储器中的一个区域，用于在访问时缓存表和索引数据；所有的对数据库的操作都是先对缓冲池中的操作，有个后台线程定期修改到磁盘里面；为了提高缓存管理的效率，缓冲池是由多个数据页(页面可以包括一行或多行数据，每个页16k默认innodb_page_size=16k)构成的一个链表，使用LRU(least recently used)算法管理链表；

如果对一个数据页进行修改后需要读取其内容，则直接在内存中读取，加速了读取数据的效率，通过show engine innodb status可以直接查看Buffer Pool的命中率Buffer pool hit rate，所谓的命中率就是访问的数据页在内存中不需要到磁盘加载的；

数据页淘汰算法-LRU

• InnoDB缓冲池里面有什么？
  数据缓存 - InnoDB数据页面；
  索引缓存 - 索引数据；
  缓冲数据 - 脏页： 在内存中修改但尚未刷新（写入）到数据磁盘的数据；
  内部结构 - InnoDB缓冲池还存储内部结构，如自适应哈希索引，行级锁等；

相关配置：

show global status like 'innodb_buffer_pool_read%'
------------------------------------------------
Innodb_buffer_pool_read_requests    57119197605 表示从内存中读取逻辑的请求数
Innodb_buffer_pool_reads              471363395 表示InnoDB缓冲池无法满足的请求数；需要从磁盘中读取
------------------------------------------------
show global status like 'innodb_buffer_pool_page%'
------------------------------------------------
Innodb_buffer_pool_pages_data          8183 显示脏和干净的数据和索引页面的数量
Innodb_buffer_pool_pages_dirty           26 显示在内存中修改但尚未写入数据文件的InnoDB缓冲池数据页数
Innodb_buffer_pool_pages_flushed    2729397 表示从InnoDB缓冲池中刷新脏页的请求数
Innodb_buffer_pool_pages_free             0 显示InnoDB缓冲池中的空闲页面
Innodb_buffer_pool_pages_misc             8 显示繁忙的页面数
Innodb_buffer_pool_pages_total         8191
------------------------------------------------

推荐的计算公式 Performance =  innodb_buffer_pool_reads / innodb_buffer_pool_read_requests * 100 ,值当然是越小越好；

相关参数：

• innodb_buffer_pool_size
  直接决定了缓冲池的大小，非常重要的配置项，MySQL5.7以后可以通过
  set global innodb_buffer_pool_size=xx动态配置，在专用数据库服务器上面可以设置成最大内存的50-80%；

show status where variable_name='InnoDB_buffer_pool_resize_status';

可以监控在线缓冲池调整进度，也可以在mysql错误日志中查看；

• innodb_buffer_pool_instances
  buffer pool将划分为多个实例以提高系统并发性, 减少线程间读写缓存的争用。当innodb_buffer_pool_size大于 1GB 时, innodb_buffer_pool_instances默认为 8；

Change Buffer

当对二级索引进行insert、update、delete时，如果目标数据页在内存则直接对内存进行操作，但是如果数据页不在内存时，需要把数据页从磁盘读入内存里面，ChangeBuffer的作用是缓存对二级索引的数据操作（主键索引是用不上的），并且在数据页被加载进buffer pool时将change buffer中的操作合并，这样一来可以避免很多对磁盘的随机IO；另外ChangeBuffer是可以持久化的，意味着数据是会被写在磁盘中，实际上ChangeBuffer是储存在ibdata1（系统表空间）里面的；

磁盘一个I/O请求所花费的时间=寻道时间+旋转延迟+数据传输时间；
顺序IO是指读取和写入操作基于逻辑块逐个连续访问来自相邻地址的数据。在顺序IO访问中，磁盘所需的磁道搜索时间显着减少，因为读/写磁头可以以最小的移动访问下一个块。
随机IO是指读写操作时间连续，但访问地址不连续，磁头在两次IO操作之间需要作比较大的移动动作才能重新开始读/写数据。

SELECT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
       WHERE PAGE_TYPE LIKE 'IBUF%') AS change_buffer_pages, 
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE) AS total_pages,
       (SELECT ((change_buffer_pages/total_pages)*100)) 
       AS change_buffer_page_percentage;
-------------------------------------
change_buffer_pages               44 
total_pages                     8191
change_buffer_page_percentage 0.5372
-------------------------------------

Change Buffer使用的是Innodb buffer pool里面的内存，所以最大值为buffer pool的大小，使用参数innodb_change_buffer_max_size来控制

show variables like 'innodb_change%'
-------------------------------------
innodb_change_buffer_max_size  25    
innodb_change_buffering       all
-------------------------------------

innodb_change_buffer_max_size

25表示max size为innodb buffer pool的25%

innodb_change_buffering

• none：不使用Change Buffer
• inserts：在insert操作的时候使用
• deletes：在delete和update操作的时候使用
• changes：包括inserts和deletes
• purges：在后台使用purges的时候使用
• all：包括inserts、deletes、changes、purges

日志缓存区 log buffer

log buffer储存要写入磁盘上日志文件的数据的内存区域；日志缓冲区大小由innodb_log_buffer_size变量定义 ；默认大小为16MB。日志缓冲区的内容会定期刷新到磁盘。大的日志缓冲区使大的事务能够运行，而无需在事务提交之前将重做日志数据写入磁盘。因此如果有大量更新，插入或删除的事务，增加日志缓冲区的大小可以节省磁盘I / O。
相关配置：

• innodb_flush_log_at_trx_commit
  • 0：每秒写入日志并将其刷新到磁盘一次；未刷新日志的事务可能会在崩溃中丢失。
  • 1：在每次事务提交时写入日志并刷新到磁盘
  • 2：在每次事务提交后写入日志，并每秒刷新一次磁盘；未刷新日志的事务可能会在崩溃中丢失

InnoDB磁盘结构

Innodb是按照表空间（Tablespace）的结构进行储存的；

共享表空间（System Tablespace）

Innodb的所有数据保存在一个单独的表空间里面，而这个表空间可以由很多个文件组成，一个表可以跨多个文件存在，所以其大小限制不再是文件大小的限制，而是其自身的限制。从Innodb的官方文档中可以看到，其表空间的最大限制为64TB；
可以在配置文件中找到datadir所配置的路径，默认的共享表空间文件名叫ibdata1;

独立表空间（File-Per-Table Tablespace）

每个InnoDB表都存储在自己独立的表空间数据文件。.ibd文件储存数据和索引，.frm储存表结构数据；

不同处：
truncate或者drop储存在独立表空间的表时能回收磁盘空间，而truncate或者drop储存在系统表空间的表时会在ibdata文件内部标记可用空间，并不会缩小文件大小；
独立表空间可以使用optimize table来压缩或重新创建表文件；当运行这条命令时， InnoDB创建一个新的 .ibd具有临时名称的文件，该文件存储的实际数据是该表所需要的空间。优化完成后，InnoDB删除旧.ibd文件并将其替换为新文件。

双写缓存区（Doublewrite Buffer）

首先需要理解一些概念，磁盘物理操作的基本单位是扇区，是真实存在的物理单位，在linux下使用fdisk -l命令可以了解该磁盘的基本信息比如

每个扇区大小为512bytes

而在Innodb里面，page是存储的最基本结构，也是对磁盘管理的最小单位；可以使用show variables like 'innodb_page_size' 查看，一般为16k；
任何数据库IO操作最终都是体现在对扇区的IO操作上面，比如现在需要对一页(16k)的数据写入磁盘，因为磁盘扇区的大小为512bytes，所以是需要对多个扇区进行操作，当这个操作进行到一半时发生数据库宕机或者掉电，就会出现数据页只有部分写入磁盘，这种情况叫做页断裂(partial write);

Doublewrite buffer是Innodb表空间内部分配的一片缓冲区且与数据页一样有物理存储空间，储存在共享表空间中。当脏页的数据flush到磁盘时先将数据写入doublewrite buffer中，然后在fsync到磁盘上，如果期间发生掉电并且导致page数据损坏，可以通过buffer内的数据进行恢复；因为写入doublewrite buffer和数据页落盘的时间点是不一样的所以不会出现两个都是损坏的情况；

当然双写会带来一定的消耗，但是不是两倍于直接落盘，因为数据页的数据是做为一个大的连续的块(chunk)顺序写入双写缓存区中，只需要一次fsync；

重做日志（Redo Log）

InnoDB记录了对数据文件的物理更改，并保证总是日志先行，也就是所谓的WAL(Write-Ahead Logging)，即在持久化数据文件前，保证之前的redo日志已经写到磁盘。redo log默认在磁盘上由两个名为ib_logfile0和ib_logfile1(默认是两个)的文件物理表示，MySQL以循环方式写入这两个文件；

• 为什么需要redo log
  当修改innodb表上某行数据时，如果该行不在内存中则需要将该数据页从磁盘读入到内存去然后在内存中更新该行，现在内存中的数据页与磁盘中就不一致了，把这种内存数据页与磁盘数据页不一致的数据页称为脏页（dirty page），DB需要把脏页数据写入磁盘，但是如果每一次更新数据就会带来一次磁盘操作的话那么机器肯定撑不住；所以说mysql会把标记为脏页的数据页储存在一个flush list里面，用一个专门的后台线程定时刷脏；那么如果在mysql还没有刷脏的时候数据库挂了怎么办呢，所以就需要redo log；
• 储存着什么
  由日志缓存区(redo log buffer)和日志文件(redo log file)构成，redo log是物理日志，保存的是数据页上被修改的值；还有一种是逻辑日志，保存的是SQL语句比如binlog；

redo log的写入过程

其实写redolog file也不是直接写到磁盘，是写到文件系统的page cahce里面，最后才是持久化到磁盘，fsync一般来说是IO性能的瓶颈；

page cache用来缓存文件数据，是属于操作系统的缓存，如果想要持久化到磁盘中需要使用fsync；

除了后台线程会每秒刷一次之外，还有两种情况会主动写盘；

• redo log buffer 即将占用innodb_log_buffer_size的一半的时候，后台线程会主动写盘，但是也只是写入到page cache里面并没有使用fsync；
• 当innodb_flush_log_at_trx_commit是1的时候，如果并行的事务提交，那么会将redo log buffer的所有日志(包括其他线程只写到一半的日志)都会持久化到磁盘，因为redo log buffer是共享的；

show variables like 'innodb_log%'
-------------------------------------
innodb_log_buffer_size  16777216  写入日志文件缓冲区的字节
innodb_log_file_size    50331648  ib_logfile文件的大小
innodb_log_files_in_group      2  控制日志文件数
-------------------------------------

参考

How to allocate innodb_buffer_pool_size in MySQL?
页断裂(partial write)与doublewrite技术
IO系统性能之一：衡量性能的几个指标
磁盘I/O那些事
MySQL · 引擎特性 · InnoDB redo log漫游
详细分析MySQL事务日志(redo log和undo log)
MySQL · 引擎特性 · Innodb change buffer介绍