Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。 而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。 连接器
第一步，你会先连接到这个数据库上，这时候接待你的就是连接器。连接器负责跟客户端建立连接、获取权限、维持和管理连接 一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。 全部使用长连接后，你可能会发现，有些时候 MySQL 占用内存涨得特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。
定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。 如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。 查询缓存：MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。
但是大多数情况下我会建议你不要使用查询缓存，为什么呢？因为查询缓存往往弊大于利。查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。 MySQL 8.0 版本查询缓存已废弃 分析器：如果没有命中查询缓存，就要开始真正执行语句了。分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。 优化器：经过了分析器，MySQL 就知道你要做什么了。在开始执行之前，还要先经过优化器的处理。优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。 执行器
开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误，如下所示 (在工程实现上，如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。查询也会在优化器之前调用 precheck 验证权限) 如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。 在数据库的慢查询日志中看到一个 rows_examined 的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。 执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟 rows_examined 并不是完全相同的。 MySQL 里经常说到的 WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘 InnoDB 引擎就会先把记录写到 redo log 里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面 有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为 binlog（归档日志）。它们的差别
redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。 redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。 redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。 redo log 用于保证 crash-safe 能力。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。这个参数我建议你设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失。 sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个参数我也建议你设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失。 在MySQL中，binlog还不能去掉。
一个原因是，redo log只有InnoDB有，别的引擎没有。 另一个原因是，redo log是循环写的，不持久保存，binlog的“归档”这个功能，redo log是不具备的。 SQL 标准的事务隔离级别包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。 读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。 读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。 可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。 串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。 MVCC:在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。这里需要注意的是，“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；而“串行化”隔离级别下直接用加锁的方式来避免并行访问。\ mysql> show variables like 'transaction_isolation' ;
+
| Variable_name | Value |
+
| transaction_isolation | REPEATABLE - READ |
事务隔离的实现：每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。同一条记录在系统中可以存在多个版本，这就是数据库的多版本并发控制（MVCC）。 系统会判断当没有事务需要用到这些回滚日志的时候，回滚日志会被删除。 回滚日志删除时机：当系统里么有比这个回滚日志更早的read-view的时候。 为什么尽量不要使用长事务。长事务意味着系统里面会存在很老的事务视图，在这个事务提交之前，回滚记录都要保留，这会导致大量占用存储空间。除此之外，长事务还占用锁资源，可能会拖垮库。回滚日志占用的空间释放后，文件并不会减少。 在 information_schema 库的 innodb_trx 这个表中查询长事务，比如下面这个语句，用于查找持续时间超过 60s 的事务。 select * from information_schema. innodb_trx where TIME_TO_SEC( timediff( now ( ) , trx_started) ) > 60
所以，哈希表这种结构适用于只有等值查询的场景，比如 Memcached 及其他一些 NoSQL 引擎。 有序数组在等值查询和范围查询场景中的性能就都非常优秀。有序数组索引只适用于静态存储引擎， 以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200。这棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了。考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。 在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为前面我们提到的，InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。每一个索引在 InnoDB 里面对应一棵 B+ 树。 主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。 主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。 表重建：alter table T engine=InnoDB。回到主键索引树搜索的过程，我们称为回表 覆盖索引：索引“覆盖了”我们的查询需求。由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。 最左前缀原则：B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。索引项是按照索引定义里面出现的字段顺序排序的。 索引下推：MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。 下面两条语句有什么区别，为什么都提倡使用2: 1. select * from T where k in ( 1 , 2 , 3 , 4 , 5 )
2. select * from T where k between 1 and 5
第一个要树搜素5 次
第二个搜索一次
全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。 全局锁的典型使用场景是，做全库逻辑备份。 逻辑备份工具是 mysqldump：当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。 表级锁：MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。 表锁的语法是 lock tables … read/write。与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。 另一类表级的锁是 MDL（metadata lock)。MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。 在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。
读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。 行锁：在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放 当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。 当出现死锁以后，有两种策略：
一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。 每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。 减少死锁的主要方向，就是控制访问相同资源的并发事务量。
一种头痛医头的方法，就是如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。 另一个思路是控制并发度 你可以考虑通过将一行改成逻辑上的多行来减少锁冲突。 begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动。如果你想要马上启动一个事务，可以使用 start transaction with consistent snapshot 这个命令。
第一种启动方式，一致性视图是在执行第一个快照读语句时创建的； 第二种启动方式，一致性视图是在执行 start transaction with consistent snapshot 时创建的。 InnoDB 的行数据有多个版本，每个数据版本有自己的 row trx_id，每个事务或者语句有自己的一致性视图。普通查询语句是一致性读，一致性读会根据 row trx_id 和一致性视图确定数据版本的可见性。
对于可重复读，查询只承认在事务启动前就已经提交完成的数据； 对于读提交，查询只承认在语句启动前就已经提交完成的数据； 而当前读，总是读取已经提交完成的最新版本。 InnoDB 利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能力。 更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。 可重复读的核心就是一致性读（consistent read）；而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。 读提交的逻辑和可重复读的逻辑类似，它们最主要的区别是：
在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图； 在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图。 对于普通索引来说，查找到满足条件的第一个记录后，需要查找下一个记录，直到碰到第一个不满足条件的记录。对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。 InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。 change buffer:当需要更新一个数据页(二级非唯一普通索引页)时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。 change buffer是可以持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上。 merge：除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。 显然，如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。 redo log 主要节省的是随机写磁盘的 I/O 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 I/O 消耗。由于唯一索引用不上 change buffer 的优化机制，因此如果业务可以接受，从性能角度出发我建议你优先考虑非唯一索引。多索引选择：采用采样统计Cardinality来预估索引。analyze table t 命令，可以用来重新统计索引信息。字符串索引：
前缀索引的使用
占用空间少，但是会增加额外的记录扫描次数 使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。 使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。我们在建立索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少，扫描次数越少。区分度查询可用下面的sql对比。 mysql> select
count ( distinct phone) l0,
count ( distinct left ( phone, 4 ) ) l4,
count ( distinct left ( phone, 5 ) ) l5,
count ( distinct left ( phone, 6 ) ) l6,
count ( distinct left ( phone, 7 ) ) l7
from suser;
+
| l0 | l4 | l5 | l6 | l7 |
+
| 105589 | 467 | 3490 | 25532 | 74249 |
+
倒序存储：如身份证号。reverse()。倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题； hash字段：然后每次插入新记录的时候，都同时用 crc32() 这个函数得到校验码填到这个新字段。alter table t add id_card_crc int unsigned, add index(id_card_crc);查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。 当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。 脏页刷新时机
InnoDB 的 redo log 写满了。这时候系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间可以继续写。 系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。 MySQL 认为系统“空闲”的时候。 MySQL 正常关闭的情况。这时候，MySQL 会把内存的脏页都 flush 到磁盘上。 InnoDB 的刷盘速度就是要参考这两个因素：一个是脏页比例，一个是 redo log 写盘速度。
innodb_io_capacity 这个参数了，它会告诉 InnoDB 磁盘IO能力，这样 InnoDB 才能知道需要全力刷脏页的时候，可以刷多快。 参数 innodb_max_dirty_pages_pct 是脏页比例上限，默认值是 75%。 脏页比例是通过 Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total 一旦一个查询请求需要在执行过程中先 flush 掉一个脏页时，这个查询就可能要比平时慢了。而 MySQL 中的一个机制，可能让你的查询会更慢：在准备刷一个脏页的时候，如果这个数据页旁边的数据页刚好是脏页，就会把这个“邻居”也带着一起刷掉；而且这个把“邻居”拖下水的逻辑还可以继续蔓延，也就是对于每个邻居数据页，如果跟它相邻的数据页也还是脏页的话，也会被放到一起刷。
找“邻居”这个优化在机械硬盘时代是很有意义的，可以减少很多随机 IO。机械硬盘的随机 IOPS 一般只有几百，相同的逻辑操作减少随机 IO 就意味着系统性能的大幅度提升。 SSD 这类 IOPS 比较高的设备的话，我就建议你把 innodb_flush_neighbors 的值设置成 0。因为这时候 IOPS 往往不是瓶颈，而“只刷自己”，就能更快地执行完必要的刷脏页操作，减少 SQL 语句响应时间。 在 MySQL 8.0 中，innodb_flush_neighbors 参数的默认值已经是 0 了。 一个 InnoDB 表包含两部分，即：表结构定义和数据。在 MySQL 8.0 版本以前，表结构是存在以.frm 为后缀的文件里。而 MySQL 8.0 版本，则已经允许把表结构定义放在系统数据表中了。因为表结构定义占用的空间很小，所以我们今天主要讨论的是表数据。 表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数 innodb_file_per_table 控制的：
这个参数设置为 OFF 表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起； 这个参数设置为 ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。 从 MySQL 5.6.6 版本开始，它的默认值就是 ON 了。 一个表单独存储为一个文件更容易管理，而且在你不需要这个表的时候，通过 drop table 命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。 将 innodb_file_per_table 设置为 ON，是推荐做法，我们接下来的讨论都是基于这个设置展开的。 数据删除流程：delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也就是说，通过 delete 命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。
删掉一条记录，InnoDB 引擎只会把这条记录标记为删除，以后复用 如果我们删掉了一个数据页上的所有记录，整个数据页就可以被复用了。 数据页的复用跟记录的复用是不同的。记录的复用，只限于符合范围条件的数据。而当整个页从 B+ 树里面摘掉以后，可以复用到任何位置。如果相邻的两个数据页利用率都很小，系统就会把这两个页上的数据合到其中一个页上，另外一个数据页就被标记为可复用。 如果我们用 delete 命令把整个表的数据删除，所有的数据页都会被标记为可复用。但是磁盘上，文件不会变小。 插入数据：不止是删除数据会造成空洞，插入数据也会。
如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂。 更新索引上的值，可以理解为删除一个旧的值，再插入一个新值。不难理解，这也是会造成空洞的。 经过大量增删改的表，都是可能是存在空洞的。所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。而重建表，就可以达到这样的目的。可以使用 alter table A engine=InnoDB 命令来重建表 引入了 Online DDL 之后，重建表的流程：
建立一个临时文件，扫描表 A 主键的所有数据页； 用数据页中表 A 的记录生成 B+ 树，存储到临时文件中； 生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中，对应的是图中 state2 的状态； 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件，对应的就是图中 state3 的状态； 用临时文件替换表 A 的数据文件。 alter 语句在启动的时候需要获取 MDL 写锁，但是这个写锁在真正拷贝数据之前就退化成读锁了。
为什么要退化呢？为了实现 Online，MDL 读锁不会阻塞增删改操作。 那为什么不干脆直接解锁呢？为了保护自己，禁止其他线程对这个表同时做 DDL。 optimize table、analyze table 和 alter table 这三种方式重建表的区别：
从 MySQL 5.6 版本开始，alter table t engine = InnoDB（也就是 recreate）的流程了； analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了 MDL 读锁； optimize table t 等于 recreate+analyze。 MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高； 而 InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。 那为什么 InnoDB 不跟 MyISAM 一样，也把数字存起来呢？
这是因为即使是在同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的。 InnoDB 是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。所以，普通索引树比主键索引树小很多。对于 count(*) 这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。因此，MySQL 优化器会找到最小的那棵树来遍历。在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。 ABLE_ROWS用于显示这个表当前有多少行，但这是个采样估算值，因此它也很不准。有多不准呢，官方文档说误差可能达到 40% 到 50%。所以，show table status 命令显示的行数也不能直接使用。 如果你现在有一个页面经常要显示交易系统的操作记录总数，到底应该怎么办呢？答案是，我们只能自己计数。所以结论是：按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count( * )，所以我建议你，尽量使用 count( * )。 Extra 这个字段中的“Using filesort”表示的就是需要排序，MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。 sort_buffer_size，就是 MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。 select city,name,age from t where city=‘杭州’ order by name limit 1000;查询流程(city有索引)：
1.初始化 sort_buffer，确定放入 name、city、age 这三个字段； 2.从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X； 3.到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中； 4.从索引 city 取下一个记录的主键 id； 5.重复步骤 3、4 直到 city 的值不满足查询条件为止，对应的主键 id 也就是图中的 ID_Y； 6.对 sort_buffer 中的数据按照字段 name 做快速排序； 7.按照排序结果取前 1000 行返回给客户端。 max_length_for_sort_data，是 MySQL 中专门控制用于排序的行数据的长度的一个参数。当行数据大于此参数时，排序变为rowid排序，对应上述3，只取name和id这两个字段，存入sort_buffer中。第7步需要再回表拿出需要的字段，多了一次遍历磁盘读。mysql> select @@max_length_for_sort_data ;
+
| @@max_length_for_sort_data |
+
| 1024 |
+
如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。 如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer 中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。 这也就体现了 MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。 对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择。 并不是所有的 order by 语句，都需要排序操作的。从上面分析的执行过程，我们可以看到，MySQL 之所以需要生成临时表，并且在临时表上做排序操作，其原因是原来的数据都是无序的。 Extra 字段中
Using filesort ：需要排序 Using index ：覆盖索引 Using temporary：临时表 条件字段函数操作
对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。需要注意的是，优化器并不是要放弃使用这个索引。 mysql> select count ( * ) from tradelog where month ( t_modified) = 7 ;
在这个例子里，放弃了树搜索功能，优化器可以选择遍历主键索引，也可以选择遍历索引 t_modified，优化器对比索引大小后发现，索引 t_modified 更小，遍历这个索引比遍历主键索引来得更快。因此最终还是会选择索引 t_modified。 隐式类型转换
在 MySQL 中，字符串和数字做比较的话，是将字符串转换成数字。
mysql> select * from tradelog where tradeid= 110717 ;
mysql> select * from tradelog where CAST( tradid AS signed int ) = 110717 ;
隐式字符编码转换：两表字符集不同导致的隐式转换，而不能使用索引树搜索
tradelog的tradeid为utf8 ，trade_detail的tradeid为utf8mb4，以tradelog为驱动表，先执行l.id=2，接着执行连表操作 CONVERT(l.traideid USING utf8mb4)=d.tradeid，这就触发隐式函数转换了。 mysql> select d. * from tradelog l, trade_detail d where d. tradeid= l. tradeid and l. id= 2 ;
SELECT ` nid` , COUNT ( cid) c FROM ` novel` n
LEFT JOIN ` chapter` c ON c. ` c, nid` = n. ` nid` AND ` c` . ` deleted_at` = 0
GROUP BY n. nid
SELECT ` nid` , COUNT ( cid) c FROM ` novel` n
LEFT JOIN ` chapter` c ON c. ` c, nid` = n. ` nid`
WHERE ` c` . ` deleted_at` = 0 GROUP BY n. nid
幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。 在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。幻读仅专指“新插入的行”，不包括更改。 幻读产生的原因：即使给所有行加上了锁，也避免不了幻读，这是因为给行加锁的时候，这条记录还不存在，没法加锁 为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。 数据行是可以加上锁的实体，数据行之间的间隙，也是可以加上锁的实体。但是间隙锁跟我们之前碰到过的锁都不太一样。 跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。间隙锁是在可重复读隔离级别下才会生效的。所以，你如果把隔离级别设置为读提交的话，就没有间隙锁了。但同时，你要解决可能出现的数据和日志不一致问题，需要把 binlog 格式设置为 row。 在删除数据的时候尽量加 limit。这样不仅可以控制删除数据的条数，让操作更安全，还可以减小加锁的范围。 可重复读隔离级别遵守两阶段锁协议，所有加锁的资源，都是在事务提交或者回滚的时候才释放的。 由于锁是一个个加的，要避免死锁，对同一组资源，要按照尽量相同的顺序访问； 在发生死锁的时刻，for update 这条语句占有的资源更多，回滚成本更大，所以 InnoDB 选择了回滚成本更小的 lock in share mode 语句，来回滚。 binlog 的写入逻辑比较简单：事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。 一个事务的 binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。这就涉及到了 binlog cache 的保存问题。
系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。 事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 中，并清空 binlog cache。 每个线程有自己 binlog cache，但是共用同一份 binlog 文件。
write，指的就是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快。 fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为 fsync 才占磁盘的 IOPS。 write 和 fsync 的时机，是由参数 sync_binlog 控制的：
sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync； sync_binlog=1 的时候，表示每次提交事务都会执行 fsync； sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。 redo log
存在 redo log buffer 中，物理上是在 MySQL 进程内存中 写到磁盘 (write)，但是没有持久化（fsync)，物理上是在文件系统的 page cache 里面 持久化到磁盘，对应的是 hard disk 日志写到 redo log buffer 是很快的，wirte 到 page cache 也差不多，但是持久化到磁盘的速度就慢多了。 InnoDB 提供了 innodb_flush_log_at_trx_commit 参数，它有三种可能取值：
设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ; 设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘； 设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache。 InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。 redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动写盘。注意，由于这个事务并没有提交，所以这个写盘动作只是 write，而没有调用 fsync，也就是只留在了文件系统的 page cache。 并行的事务提交的时候，顺带将这个事务的 redo log buffer 持久化到磁盘。假设一个事务 A 执行到一半，已经写了一些 redo log 到 buffer 中，这时候有另外一个线程的事务 B 提交，如果 innodb_flush_log_at_trx_commit 设置的是 1，那么按照这个参数的逻辑，事务 B 要把 redo log buffer 里的日志全部持久化到磁盘。这时候，就会带上事务 A 在 redo log buffer 里的日志一起持久化到磁盘。 事务执行中间过程的 redo log 也是直接写在 redo log buffer 中的，这些 redo log 也会被后台线程一起持久化到磁盘。也就是说，一个没有提交的事务的 redo log，也是可能已经持久化到磁盘的。 时序上 redo log 先 prepare， 再写 binlog，最后再把 redo log commit。如果把 innodb_flush_log_at_trx_commit 设置成 1，那么 redo log 在 prepare 阶段就要持久化一次，因为有一个崩溃恢复逻辑是要依赖于 prepare 的 redo log，再加上 binlog 来恢复的。 组提交（group commit）机制
一次组提交里面，组员越多，节约磁盘 IOPS 的效果越好。但如果只有单线程压测，那就只能老老实实地一个事务对应一次持久化操作了。 在并发更新场景下，第一个事务写完 redo log buffer 以后，接下来这个 fsync 越晚调用，组员可能越多，节约 IOPS 的效果就越好。 为了让一次 fsync 带的组员更多，MySQL 有一个很有趣的优化：拖时间 如果你的 MySQL 现在出现了性能瓶颈，而且瓶颈在 IO 上，可以通过哪些方法来提升性能呢？
设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，减少 binlog 的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。 将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000）。这样做的风险是，主机掉电时会丢 binlog 日志。 将 innodb_flush_log_at_trx_commit 设置为 2。这样做的风险是，主机掉电的时候会丢数据。 备库 B 跟主库 A 之间维持了一个长连接。主库 A 内部有一个线程，专门用于服务备库 B 的这个长连接。一个事务日志同步的完整过程是这样的：
在备库 B 上通过 change master 命令，设置主库 A 的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量。 在备库 B 上执行 start slave 命令，这时候备库会启动两个线程，就是图中的 io_thread 和 sql_thread。其中 io_thread 负责与主库建立连接。 主库 A 校验完用户名、密码后，开始按照备库 B 传过来的位置，从本地读取 binlog，发给 B。 备库 B 拿到 binlog 后，写到本地文件，称为中转日志（relay log）。 sql_thread 读取中转日志，解析出日志里的命令，并执行。 binlog格式：binlog_format
statement：binlog 里面记录的就是 SQL 语句的原文。缺点：可能会导致主备不一致 row ：记录真正的数据变更。缺点：很占空间 mixed 格式的意思是，MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式。 现在越来越多的场景要求把 MySQL 的 binlog 格式设置成 row。恢复数据。主备延迟：在备库上执行 show slave status 命令，它的返回结果里面会显示 seconds_behind_master，用于表示当前备库延迟了多少秒。 主备延迟来源：
首先，有些部署条件下，备库所在机器的性能要比主库所在的机器性能差。 第二种常见的可能了，即备库的压力大。 第三种可能，即大事务。
不要一次性地用 delete 语句删除太多数据。其实，这就是一个典型的大事务场景。 大表 DDL。 主备切换策略：
可靠性优先策略
判断备库 B 现在的 seconds_behind_master，如果小于某个值（比如 5 秒）继续下一步，否则持续重试这一步； 把主库 A 改成只读状态，即把 readonly 设置为 true； 判断备库 B 的 seconds_behind_master 的值，直到这个值变成 0 为止； 把备库 B 改成可读写状态，也就是把 readonly 设置为 false； 把业务请求切到备库 B。 可用性优先策略：这个切换流程的代价，就是可能出现数据不一致的情况。
把备库 B 改成可读写状态，也就是把 readonly 设置为 false； 把业务请求切到备库 B。 在满足数据可靠性的前提下，MySQL 高可用系统的可用性，是依赖于主备延迟的。延迟的时间越小，在主库故障的时候，服务恢复需要的时间就越短，可用性就越高。 mysql5.7 从库并行执行binlog 基于 GTID 的主备切换 CHANGE MASTER TO
MASTER_HOST= $host_name
MASTER_PORT= $port
MASTER_USER= $user_name
MASTER_PASSWORD= $password
master_auto_position= 1
读写分离解决方案
强制走主库方案； sleep 方案； 判断主备无延迟方案；
每次从库执行查询请求前，先判断 seconds_behind_master 是否已经等于 0。如果还不等于 0 ，那就必须等到这个参数变为 0 才能执行查询请求。 第二种方法，对比位点确保主备无延迟
Master_Log_File 和 Read_Master_Log_Pos，表示的是读到的主库的最新位点； Relay_Master_Log_File 和 Exec_Master_Log_Pos，表示的是备库执行的最新位点。 如果 Master_Log_File 和 Relay_Master_Log_File、Read_Master_Log_Pos 和 Exec_Master_Log_Pos 这两组值完全相同，就表示接收到的日志已经同步完成 第三种方法，对比 GTID 集合确保主备无延迟
Auto_Position=1 ，表示这对主备关系使用了 GTID 协议。 Retrieved_Gtid_Set，是备库收到的所有日志的 GTID 集合； Executed_Gtid_Set，是备库所有已经执行完成的 GTID 集合。 如果这两个集合相同，也表示备库接收到的日志都已经同步完成。 配合 semi-sync 方案；
事务提交的时候，主库把 binlog 发给从库； 从库收到 binlog 以后，发回给主库一个 ack，表示收到了； 主库收到这个 ack 以后，才能给客户端返回“事务完成”的确认。 如果启用了 semi-sync，就表示所有给客户端发送过确认的事务，都确保了备库已经收到了这个日志。 等主库位点方案：下面函数返回大于等于0，表示已同步 select master_pos_wait(file, pos[, timeout]);
1.它是在从库执行的；
2.参数 file 和 pos 指的是主库上的文件名和位置；
3.timeout 可选，设置为正整数 N 表示这个函数最多等待 N 秒。
4.正常返回的结果是一个正整数 M，表示从命令开始执行，到应用完 file 和 pos 表示的 binlog 位置，执行了多少事务。
select wait_for_executed_gtid_set(gtid_set, 1);
等待，直到这个库执行的事务中包含传入的 gtid_set，返回 0；
超时返回 1。
并发查询 innodb_thread_concurrency 并发连接和并发查询，并不是同一个概念。你在 show processlist 的结果里，看到的几千个连接，指的就是并发连接。而“当前正在执行”的语句，才是我们所说的并发查询。并发连接数达到几千个影响并不大，就是多占一些内存而已。我们应该关注的是并发查询，因为并发查询太高才是 CPU 杀手。这也是为什么我们需要设置 innodb_thread_concurrency 参数的原因。在线程进入锁等待以后，并发线程的计数会减一数据库保活查询
select 1 判断 查表判断：在系统库（mysql 库）里创建一个表，比如命名为 health_check，里面只放一行数据，然后定期执行。缺点，如果磁盘满了，不能更新，但是查询并不影响 更新判断：既然要更新，就要放个有意义的字段，常见做法是放一个 timestamp 字段，用来表示最后一次执行检测的时间。 可以用 Flashback 工具通过闪回把数据恢复回来。Flashback 恢复数据的原理，是修改 binlog 的内容，拿回原库重放。而能够使用这个方案的前提是，需要确保 binlog_format=row 和 binlog_row_image=FULL。 恢复数据比较安全的做法，是恢复出一个备份，或者找一个从库作为临时库，在这个临时库上执行这些操作，然后再将确认过的临时库的数据，恢复回主库。 我们不止要说误删数据的事后处理办法，更重要是要做到事前预防。我有以下两个建议：
把 sql_safe_updates 参数设置为 on。这样一来，如果我们忘记在 delete 或者 update 语句中写 where 条件，或者 where 条件里面没有包含索引字段的话，这条语句的执行就会报错。代码上线前，必须经过 SQL 审计。 delete 全表是很慢的，需要生成回滚日志、写 redo、写 binlog。所以，从性能角度考虑，你应该优先考虑使用 truncate table 或者 drop table 命令。 使用 delete 命令删除的数据，你还可以用 Flashback 来恢复。而使用 truncate /drop table 和 drop database 命令删除的数据，就没办法通过 Flashback 来恢复了。为什么呢？这是因为，即使我们配置了 binlog_format=row，执行这三个命令时，记录的 binlog 还是 statement 格式。binlog 里面就只有一个 truncate/drop 语句，这些信息是恢复不出数据的。 这种情况下，要想恢复数据，就需要使用全量备份，加增量日志的方式了。这个方案要求线上有定期的全量备份，并且实时备份 binlog。 延迟复制备库 预防误删库 / 表的方法
第一条建议是，账号分离。这样做的目的是，避免写错命令 第二条建议是，制定操作规范。这样做的目的，是避免写错要删除的表名。
在删除数据表之前，必须先对表做改名操作。然后，观察一段时间，确保对业务无影响以后再删除这张表。 改表名的时候，要求给表名加固定的后缀（比如加 _to_be_deleted)，然后删除表的动作必须通过管理系统执行。并且，管理系删除表的时候，只能删除固定后缀的表。 其实，对于一个有高可用机制的 MySQL 集群来说，最不怕的就是 rm 删除数据了。只要不是恶意地把整个集群删除，而只是删掉了其中某一个节点的数据的话，HA 系统就会开始工作，选出一个新的主库，从而保证整个集群的正常工作。 在 MySQL 中有两个 kill 命令：一个是 kill query + 线程 id，表示终止这个线程中正在执行的语句；一个是 kill connection + 线程 id，这里 connection 可缺省，表示断开这个线程的连接，当然如果这个线程有语句正在执行，也是要先停止正在执行的语句的。 其实是因为发送 kill 命令的客户端，并没有强行停止目标线程的执行，而只是设置了个状态，并唤醒对应的线程。而被 kill 的线程，需要执行到判断状态的“埋点”，才会开始进入终止逻辑阶段。并且，终止逻辑本身也是需要耗费时间的。 实现上，当用户执行 kill query thread_id_B 时，MySQL 里处理 kill 命令的线程做了两件事：
把 session B 的运行状态改成 THD::KILL_QUERY(将变量 killed 赋值为 THD::KILL_QUERY)； 给 session B 的执行线程发一个信号。 解释：
一个语句执行过程中有多处“埋点”，在这些“埋点”的地方判断线程状态，如果发现线程状态是 THD::KILL_QUERY，才开始进入语句终止逻辑； 如果处于等待状态，必须是一个可以被唤醒的等待，否则根本不会执行到“埋点”处； 语句从开始进入终止逻辑，到终止逻辑完全完成，是有一个过程的。 kill无效的情况
线程没有执行到判断线程状态的逻辑。 终止逻辑耗时较长
超大事务执行期间被 kill。这时候，回滚操作需要对事务执行期间生成的所有新数据版本做回收操作，耗时很长。 大查询回滚。如果查询过程中生成了比较大的临时文件，加上此时文件系统压力大，删除临时文件可能需要等待 IO 资源，导致耗时较长。 DDL 命令执行到最后阶段，如果被 kill，需要删除中间过程的临时文件，也可能受 IO 资源影响耗时较久。 第一个误解是：如果库里面的表特别多，连接就会很慢。
当使用默认参数连接的时候，MySQL 客户端会提供一个本地库名和表名补全的功能。为了实现这个功能，客户端在连接成功后，需要多做一些操作：
1.执行 show databases； 2.切到 db1 库，执行 show tables； 3.把这两个命令的结果用于构建一个本地的哈希表。 在这些操作中，最花时间的就是第三步在本地构建哈希表的操作。所以，当一个库中的表个数非常多的时候，这一步就会花比较长的时间。 我们感知到的连接过程慢，其实并不是连接慢，也不是服务端慢，而是客户端慢。如果在连接命令中加上 -A，就可以关掉这个自动补全的功能，然后客户端就可以快速返回了。 加–quick(或者简写为 -q) 参数，也可以跳过这个阶段。但是，这个–quick 是一个更容易引起误会的参数，也是关于客户端常见的一个误解。 MySQL 客户端发送请求后，接收服务端返回结果的方式有两种：
一种是本地缓存，也就是在本地开一片内存，先把结果存起来。如果你用 API 开发，对应的就是 mysql_store_result 方法。 另一种是不缓存，读一个处理一个。如果你用 API 开发，对应的就是 mysql_use_result 方法。 MySQL 客户端默认采用第一种方式，而如果加上–quick 参数，就会使用第二种不缓存的方式。 采用不缓存的方式时，如果本地处理得慢，就会导致服务端发送结果被阻塞，因此会让服务端变慢。 当进行大表查询时，服务端并不需要保存一个完整的结果集。取数据和发数据的流程是这样的：
获取一行，写到 net_buffer 中。这块内存的大小是由参数 net_buffer_length 定义的，默认是 16k。 重复获取行，直到 net_buffer 写满，调用网络接口发出去。 如果发送成功，就清空 net_buffer，然后继续取下一行，并写入 net_buffer。 如果发送函数返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer）写满了，进入等待。直到网络栈重新可写，再继续发送。 一个查询在发送过程中，占用的 MySQL 内部的内存最大就是 net_buffer_length 这么大 socket send buffer 也不大（默认定义 /proc/sys/net/core/wmem_default），如果 socket send buffer 被写满，就会暂停读数据的流程。 MySQL 是“边读边发的”，这个概念很重要。这就意味着，如果客户端接收得慢，会导致 MySQL 服务端由于结果发不出去，这个事务的执行时间变长。如果你看到 State 的值一直处于“Sending to client”，就表示服务器端的网络栈写满了。 “Sending data”并不一定是指“正在发送数据”，而可能是处于执行器过程中的任意阶段。 也就是说，仅当一个线程处于“等待客户端接收结果”的状态，才会显示"Sending to client"；而如果显示成“Sending data”，它的意思只是“正在执行”。 大表扫描对buffer pool的影响：
在 InnoDB 实现上，按照 5:3 的比例把整个 LRU 链表分成了 young 区域和 old 区域。 访问一个新的不存在于当前链表的数据页，新插入的数据页放在 old 头处。 处于 old 区域的数据页，每次被访问的时候都要做下面这个判断：
若这个数据页在 LRU 链表中存在的时间超过了 1 秒，就把它移动到链表头部； 如果这个数据页在 LRU 链表中存在的时间短于 1 秒，位置保持不变。1 秒这个时间，是由参数 innodb_old_blocks_time 控制的。其默认值是 1000，单位毫秒。 对于大表扫描
扫描过程中，需要新插入的数据页，都被放到 old 区域 ; 一个数据页里面有多条记录，这个数据页会被多次访问到，但由于是顺序扫描，这个数据页第一次被访问和最后一次被访问的时间间隔不会超过 1 秒，因此还是会被保留在 old 区域； 再继续扫描后续的数据，之前的这个数据页之后也不会再被访问到，于是始终没有机会移到链表头部（也就是 young 区域），很快就会被淘汰出去。 Index Nested-Loop Join ：可以用被驱动表的索引，简称 NLJ。
select * from t1 straight_join t2 on (t1.a=t2.a); 1.从表 t1 中读入一行数据 R； 2.从数据行 R 中，取出 a 字段到表 t2 里去查找； 3.取出表 t2 中满足条件的行，跟 R 组成一行，作为结果集的一部分； 重复执行步骤 1 到 3，直到表 t1 的末尾循环结束。 Simple Nested-Loop Join：被驱动表关联字段无索引，只能取一条驱动表数据，扫描一遍被驱动表全表，当前没有采用 Block Nested-Loop Join (BNL)：被驱动表无索引，采用缓冲来存储驱动表，全表扫描被驱动表，流程如下
把表 t1 的数据读入线程内存 join_buffer 中，由于我们这个语句中写的是 select *，因此是把整个表 t1 放入了内存； 扫描表 t2，把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回。 join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t1 的所有数据话，策略很简单，就是分段放。每一个段全表扫描被驱动表 在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。Batched Key Access 流程,NLJ优化：
设置 set optimizer_switch=‘mrr=on,mrr_cost_based=off,batched_key_access=on’; 把NLJ每次查询都回表改为暂存驱动表数据到join_buffer，排序，再依次获取，这样把随机读变为顺序读。依赖于MRR 如果一个使用 BNL 算法的 join 语句，多次扫描一个冷表，而且这个语句执行时间超过 1 秒，就会在再次扫描冷表的时候，把冷表的数据页移到 LRU 链表头部。这样会污染缓存，使数据库性能下降。 大表 join 操作虽然对 IO 有影响，但是在语句执行结束后，对 IO 的影响也就结束了。但是，对 Buffer Pool 的影响就是持续性的，需要依靠后续的查询请求慢慢恢复内存命中率。 BNL 算法对系统的影响主要包括三个方面：
可能会多次扫描被驱动表，占用磁盘 I/O 资源； 判断 join 条件需要执行 M*N 次对比（M、N 分别是两张表的行数），如果是大表就会占用非常多的 CPU 资源； 可能会导致 Buffer Pool 的热数据被淘汰，影响内存命中率。 临时表就是内存表。但是，这两个概念可是完全不同的。
内存表，指的是使用 Memory 引擎的表，建表语法是 create table … engine=memory。这种表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在。除了这两个特性看上去比较“奇怪”外，从其他的特征上看，它就是一个正常的表。 而临时表，可以使用各种引擎类型 。如果是使用 InnoDB 引擎或者 MyISAM 引擎的临时表，写数据的时候是写到磁盘上的。当然，临时表也可以使用 Memory 引擎。 临时表特点
建表语法是 create temporary table …。 一个临时表只能被创建它的 session 访问，对其他线程不可见。所以，图中 session A 创建的临时表 t，对于 session B 就是不可见的。 临时表可以与普通表同名。 session A 内有同名的临时表和普通表的时候，show create 语句，以及增删改查语句访问的是临时表。 show tables 命令不显示临时表。 临时表应用：由于不用担心线程之间的重名冲突，临时表经常会被用在复杂查询的优化过程中。对于多表查询聚合
例如在1024个表上查询最新的100条数据，可以在每一个表上查询最新的100条，插入临时表，最后汇总之后排序取前100条 在 binlog_format='row’的时候，临时表的操作不记录到 binlog 中，也省去了不少麻烦，但是其他两种模式会记录binlog。 内部临时表 union 执行流程 group by 执行流程 group by 优化
如果对 group by 语句的结果没有排序要求，要在语句后面加 order by null； 尽量让 group by 过程用上表的索引，确认方法是 explain 结果里没有 Using temporary 和 Using filesort； 如果 group by 需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大 tmp_table_size 参数，来避免用到磁盘临时表； 如果数据量实在太大，使用 SQL_BIG_RESULT 这个提示，来告诉优化器直接使用排序算法得到 group by 的结果。 InnoDB 和 Memory 引擎的数据组织方式是不同的：
InnoDB 引擎把数据放在主键索引上，其他索引上保存的是主键 id。这种方式，我们称之为索引组织表（Index Organizied Table）。 而 Memory 引擎采用的是把数据单独存放，索引上保存数据位置的数据组织形式，我们称之为堆组织表（Heap Organizied Table）。 从中我们可以看出，这两个引擎的一些典型不同：
InnoDB 表的数据总是有序存放的，而内存表的数据就是按照写入顺序存放的； 当数据文件有空洞的时候，InnoDB 表在插入新数据的时候，为了保证数据有序性，只能在固定的位置写入新值，而内存表找到空位就可以插入新值； 数据位置发生变化的时候，InnoDB 表只需要修改主键索引，而内存表需要修改所有索引； InnoDB 表用主键索引查询时需要走一次索引查找，用普通索引查询的时候，需要走两次索引查找。而内存表没有这个区别，所有索引的“地位”都是相同的。 InnoDB 支持变长数据类型，不同记录的长度可能不同；内存表不支持 Blob 和 Text 字段，并且即使定义了 varchar(N)，实际也当作 char(N)，也就是固定长度字符串来存储，因此内存表的每行数据长度相同。 生产环境不建议使用内存表，因为存在重启删除和主从切换问题。 由于自增主键可以让主键索引尽量地保持递增顺序插入，避免了页分裂，因此索引更紧凑。 不同的引擎对于自增值的保存策略不同。
MyISAM 引擎的自增值保存在数据文件中。 InnoDB 引擎的自增值，其实是保存在了内存里，并且到了 MySQL 8.0 版本后，才有了“自增值持久化”的能力，也就是才实现了“如果发生重启，表的自增值可以恢复为 MySQL 重启前的值”，具体情况是：
在 MySQL 5.7 及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值 max(id)，然后将 max(id)+1 作为这个表当前的自增值。举例来说，如果一个表当前数据行里最大的 id 是 10，AUTO_INCREMENT=11。这时候，我们删除 id=10 的行，AUTO_INCREMENT 还是 11。但如果马上重启实例，重启后这个表的 AUTO_INCREMENT 就会变成 10。也就是说，MySQL 重启可能会修改一个表的 AUTO_INCREMENT 的值。 在 MySQL 8.0 版本，将自增值的变更记录在了 redo log 中，重启的时候依靠 redo log 恢复重启之前的值。 在 MySQL 里面，如果字段 id 被定义为 AUTO_INCREMENT，在插入一行数据的时候，自增值的行为如下：
如果插入数据时 id 字段指定为 0、null 或未指定值，那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段； 如果插入数据时 id 字段指定了具体的值，就直接使用语句里指定的值。 自增主键 id 不连续的原因：
唯一键冲突是导致自增主键 id 不连续的第一种原因。 事务回滚也会产生类似的现象，这就是第二种原因。 对于批量插入数据的语句，MySQL 有一个批量申请自增 id 的策略：
语句执行过程中，第一次申请自增 id，会分配 1 个； 1 个用完以后，这个语句第二次申请自增 id，会分配 2 个； 2 个用完以后，还是这个语句，第三次申请自增 id，会分配 4 个； 依此类推，同一个语句去申请自增 id，每次申请到的自增 id 个数都是上一次的两倍。 这是主键 id 出现自增 id 不连续的第三种原因。 MySQL 5.1.22 版本引入了一个新策略，新增参数 innodb_autoinc_lock_mode，默认值是 1。
这个参数的值被设置为 0 时，表示采用之前 MySQL 5.0 版本的策略，即语句执行结束后才释放锁； 这个参数的值被设置为 1 时：
普通 insert 语句，自增锁在申请之后就马上释放； 类似 insert … select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放； 这个参数的值被设置为 2 时，所有的申请自增主键的动作都是申请后就释放锁。 insert … select 是很常见的在两个表之间拷贝数据的方法。你需要注意，在可重复读隔离级别下，这个语句会给 select 的表里扫描到的记录和间隙加读锁 。而如果 insert 和 select 的对象是同一个表，则有可能会造成循环写入。这种情况下，我们需要引入用户临时表来做优化。 insert 语句如果出现唯一键冲突，会在冲突的唯一值上加共享的 next-key lock(S 锁)。因此，碰到由于唯一键约束导致报错后，要尽快提交或回滚事务，避免加锁时间过长。 物理拷贝的方式速度最快，尤其对于大表拷贝来说是最快的方法。如果出现误删表的情况，用备份恢复出误删之前的临时库，然后再把临时库中的表拷贝到生产库上，是恢复数据最快的方法。但是，这种方法的使用也有一定的局限性
必须是全表拷贝，不能只拷贝部分数据； 需要到服务器上拷贝数据，在用户无法登录数据库主机的场景下无法使用； 由于是通过拷贝物理文件实现的，源表和目标表都是使用 InnoDB 引擎时才能使用。 用 mysqldump 生成包含 INSERT 语句文件的方法，可以在 where 参数增加过滤条件，来实现只导出部分数据。这个方式的不足之一是，不能使用 join 这种比较复杂的 where 条件写法。 用 select … into outfile 的方法是最灵活的，支持所有的 SQL 写法。但，这个方法的缺点之一就是，每次只能导出一张表的数据，而且表结构也需要另外的语句单独备份。 grant 语句会同时修改数据表和内存，判断权限的时候使用的是内存数据。因此，规范地使用 grant 和 revoke 语句，是不需要随后加上 flush privileges 语句的。 flush privileges 语句本身会用数据表的数据重建一份内存权限数据，所以在权限数据可能存在不一致的情况下再使用。而这种不一致往往是由于直接用 DML 语句操作系统权限表导致的，所以我们尽量不要使用这类语句。 分区不建议使用 MySQL 在第一次打开分区表的时候，需要访问所有的分区； 在 server 层，认为这是同一张表，因此所有分区共用同一个 MDL 锁； 在引擎层，认为这是不同的表，因此 MDL 锁之后的执行过程，会根据分区表规则，只访问必要的分区。 表定义自增值 id：表定义的自增值达到上限后的逻辑是：再申请下一个 id 时，得到的值保持不变。 InnoDB 系统自增 row_id：如果你创建的 InnoDB 表没有指定主键，那么 InnoDB 会给你创建一个不可见的，长度为 6 个字节的 row_id。在代码实现时 row_id 是一个长度为 8 字节的无符号长整型 (bigint unsigned)。但是，InnoDB 在设计时，给 row_id 留的只是 6 个字节的长度，这样写到数据表中时只放了最后 6 个字节，所以 row_id 能写到数据表中的值，就有两个特征：
row_id 写入表中的值范围，是从 0 到 248-1； 当 dict_sys.row_id=248时，如果再有插入数据的行为要来申请 row_id，拿到以后再取最后 6 个字节的话就是 0。 也就是说，写入表的 row_id 是从 0 开始到 2^48-1。达到上限后，下一个值就是 0，然后继续循环。 从这个角度看，我们还是应该在 InnoDB 表中主动创建自增主键。因为，表自增 id 到达上限后，再插入数据时报主键冲突错误，是更能被接受的。 MySQL 内部维护了一个全局变量 global_query_id，每次执行语句的时候将它赋值给 Query_id，然后给这个变量加 1。如果当前语句是这个事务执行的第一条语句，那么 MySQL 还会同时把 Query_id 赋值给这个事务的 Xid。
而 global_query_id 是一个纯内存变量，重启之后就清零了。所以你就知道了，在同一个数据库实例中，不同事务的 Xid 也是有可能相同的。 但是 MySQL 重启之后会重新生成新的 binlog 文件，这就保证了，同一个 binlog 文件里，Xid 一定是惟一的。 Xid 是由 server 层维护的。InnoDB 内部使用 Xid，就是为了能够在 InnoDB 事务和 server 之间做关联。但是，InnoDB 自己的 trx_id，是另外维护的。 InnoDB 内部维护了一个 max_trx_id 全局变量，重启会保存起来，不清零，每次需要申请一个新的 trx_id 时，就获得 max_trx_id 的当前值，然后并将 max_trx_id 加 1。 InnoDB 数据可见性的核心思想是：每一行数据都记录了更新它的 trx_id，当一个事务读到一行数据的时候，判断这个数据是否可见的方法，就是通过事务的一致性视图与这行数据的 trx_id 做对比。 thread_id：系统保存了一个全局变量 thread_id_counter，每新建一个连接，就将 thread_id_counter 赋值给这个新连接的线程变量。
233
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
