MySQL知识整理

基础

第一讲：基础架构：一条SQL查询语句是如何执行的？

架构

MySQL分为Server层和存储引擎层两部分。
Server层包括连接器、查询缓存（在MySQL8.0被移除）、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎。从MySQL5.5.5版本开始InnoDB成为了默认存储引擎。InnoDB提供了事务支持、行级锁定和外键约束等功能，非常适合处理大量数据的应用程序。
可以在创建表的时候选择不同的存储引擎，例如CREATETABLEmy_table(…)ENGINE=InnoDB;

从上图可以看出，不同的存储引擎共用一个Server层，也就是从连接器到执行器的部分。

尽量减少长连接的原因和方案

长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。
短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。
长连接可以减少频繁建立连接的开销，但它可能导致随着时间和数据交换的增加，数据库占用的内存越来越大，因为每个连接都会消耗资源。如果不及时释放资源，确实可能会出现内存溢出的情况，导致操作系统为了保护系统稳定性，而触发OOM（OutOfMemory）机制杀掉占用大量内存的进程，MySQL就可能会因此异常重启。
1.定期断开长连接或者定期重置连接，通过执行mysql_reset_connection()来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。
2.设置非交互式连接（编程）在关闭之前等待活动的秒数wait_timeout
3.设置交互式连接（shell）未发出任何请求并处于空闲状态时，超过interactive_timeout设置的时间后，MySQL服务器会自动关闭该连接
4.通过定期执行FLUSHHOSTS来清理错误的连接
5.在编程中，使用连接池的策略。连接池可以提供自动的连接管理功能，包括最大连接数的限制、连接的创建和销毁、连接的获取和释放，并且可以自动处理长时间不活跃连接的断开和重置。

为什么尽量不要依赖查询缓存

查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。MySQL也提供了这种“按需使用”的方式。你可以将参数query_cache_type设置成DEMAND，这样对于默认的SQL语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，用SQL_CACHE显式指定

select SQL_CACHE * from T where ID = 10；

MySQL8.0开始没有查询缓存功能

索引

第四讲：深入浅出索引（上）

主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引。
非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引。

第五讲：深入浅出索引（下）

回表：非主键索引树回到主键索引树的过程

对覆盖索引的理解

覆盖索引是数据库查询优化中的一个概念，指的是一个索引包含了查询所需要的所有数据，因此查询可以直接通过索引来获取结果，而无需再回表查询实际的数据行。在InnoDB存储引擎中，使用覆盖索引可以大大提高查询效率，由于数据可以直接从索引中获取，数据库不必执行额外的I/O操作去读取数据表中的行。

最左前缀原则

在建立联合索引的时候，如何安排索内的字段顺序
第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。
再就是需要考虑索引的空间了，（如果既有联合查询，又有基于a、b各自的查询，将字段小的单独建索引）

索引下推

在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

第九讲：普通索引和唯一索引，应该怎么选择？

changebuffer及其应用场景

Changebuffer是InnoDB存储引擎中的一个特殊数据结构，其主要用途是缓存对二级索引的修改操作（非唯一索引的插入、更新或删除操作，减少磁盘I/O操作并提升性能）。当对二级索引进行修改时，如果对应的索引页不在缓冲池中（比如，在磁盘上），使用changebuffer可以避免立即从磁盘读取索引页，从而提高性能。
在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行changebuffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。
将changebuffer中的操作应用到原数据页，得到最新结果的过程称为merge。除了访问这个数据页会触发merge外，系统有后台线程会定期merge。在数据库正常关闭（shutdown）的过程中，也会执行merge操作。

当表中的非唯一索引用得比较少，或者是I/O系统非常快时，changebuffer带来的性能提升可能就不那么明显。

普通索引和唯一索引，应该怎么选择？

两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。普通索引和changebuffer的配合使用，对于数据量大的表的更新优化还是很明显的。

第十讲：MySQL为什么有时候会选错索引？

MySQL优化器选错索引的解决方法

1.对于由于索引统计信息不准确导致的问题，你可以用analyzetable来解决。
2.对于其他优化器误判的情况，你可以在应用端用forceindex来强行指定索引，

第十一讲：怎么给字符串字段加索引？

字符串应该使用什么类型索引

1.直接创建完整索引，这样可能比较占用空间；
2.创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
3.倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；例如身份证
4.创建hash字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

第十六讲：“orderby”是怎么工作的？

MySQL会给每个线程分配一块内存用于排序，称为sort_buffer。sort_buffer_size，就是MySQL为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序

全字段排序 VS rowid排序

1.如果MySQL认为内存足够大，会优先选择全字段排序，把需要的字段都放到sort_buffer中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。
2.如果MySQL实在是担心排序内存太小，会影响排序效率，才会采用rowid排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。

第十八讲：为什么这些SQL语句逻辑相同，性能却差异巨大？

索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。
字符集utf8mb4是utf8的超集，所以当这两个类型的字符串在做比较的时候，MySQL内部的操作是，先把utf8字符串转成utf8mb4字符集，再做比较。

事务

第三讲：事务隔离：为什么你改了我还看不见？

事务就是要保证一组数据库操作，要么全部成功，要么全部失败。在MySQL中，事务支持是在引擎层实现的。

事务隔离级别

1.读未提交（readuncommitted）：一个事务还没提交时，它做的变更就能被别的事务看到。最低的隔离级别，可能会导致脏读、幻读或不可重复读；
2.读提交（readcommitted）：一个事务提交之后，它做的变更才会被其他事务看到。（Oracle默认隔离级别）
3.可重复读（repeatableread）：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。（InnoDB默认隔离级别）
4.串行化（serializable）：对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。

第八讲：事务到底是隔离的还是不隔离的？

begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动。
在可重复读隔离级别下，事务在启动的时候就“拍了个快照”。注意，这个快照是基于整库的。
更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（currentread）。

锁

第六讲：全局锁和表锁 ：给表加个字段怎么有这么多阻碍？

第七讲：行锁功过：怎么减少行锁对性能的影响？

全局锁：

对数据库实例加锁。应用于全库逻辑备份。命令是 Flush tables with read lock (FTWRL)。整个库处于只读状态，其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括 建表、修改表结构等）和更新类事务的提交语句。

表级锁：

表锁一般是在数据库引擎不支持行锁的时候才会被用到的。在MySQL中，表级别的锁主要有两种类型：
**表锁可以通过LOCK TABLES … READ/WRITE语法来实现，这个命令可以限制其他线程对特定表的读写操作。**比如，当你在一个线程中使用LOCK TABLES t1 READ, t2 WRITE;时，别的线程对t1的写操作或对t2的读写操作都会被阻塞。同时，持有锁的线程也受到限制，它只能对t1进行读操作，对t2进行读写操作。
元数据锁（Metadata Lock, MDL）是另一种表级锁，它被自动加上，而不需要显式的操作。**MDL的目的是确保在表被访问时，其结构不会发生变化。**例如，在读取或写入表数据时，会加上MDL读锁；而在执行表结构变更操作时，会加上MDL写锁。这样可以确保数据的一致性和安全性。MDL的读锁之间不会相互阻塞，而MDL的读锁和写锁之间、写锁和写锁之间则是互斥的。

行锁：

InnoDB存储引擎的行锁是自动加上的。当你执行一个涉及数据修改的语句，比如UPDATE或DELETE，InnoDB会对所涉及的数据行自动加锁，以防止其他事务并发修改这些同一行的数据。

如果你想显式地设置某些锁的级别，可以在事务中设置隔离级别，例如：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

这样可以控制锁的行为，防止其他的事务干扰当前事务。
还有一些特殊的锁定读操作，比如SELECT … FOR UPDATE和SELECT … LOCK IN SHARE MODE，这些操作会在读取数据的同时加上行锁或共享锁，以防止数据在事务结束前被其他事务修改或获取不一致的数据。
总的来说，InnoDB行锁的使用大部分是自动的，依赖于InnoDB的事务和隔离级别机制，而无需手动控制。

两阶段锁协议：

两阶段锁协议是一种在事务性数据库系统中维持数据一致性和事务隔离的锁定协议。它规定了一个事务在其生命周期内如何对数据加锁和解锁的标准过程，分为两个阶段：

加锁阶段（Growing Phase）：在这个阶段中，事务可以获得所需的任何锁，但它不能释放任何锁。
解锁阶段（Shrinking Phase）：在这个阶段，事务可以释放所持有的锁，但不可以再获得新的锁。
应用两阶段锁协议的关键要点在于，一旦事务开始释放它所持有的锁，它就不能再获得新的锁。这个协议可以防止多个事务同时更新同一数据而导致的数据不一致问题，并且帮助避免死锁的发生。不过，需要注意的是，即使遵守了两阶段锁协议，死锁的情况也是有可能发生的，这时候就需要额外的死锁检测机制来处理。

在InnoDB事务中，只要修改了数据，就会对相关的行加行锁，而这些锁会一直持有到事务结束（即执行COMMIT或ROLLBACK）才释放。这是遵循两阶段锁协议的一部分，确保在整个事务期间数据的一致性。
因此，在设计事务时，你应该注意锁的顺序。理想情况下，你应该尽量推迟那些最有可能导致锁冲突和影响并发度的锁的获得，这样可以最大限度地减少它们持有锁的时间，从而提高系统的并发性能。
例如，对于需要更新多行的业务逻辑，将对竞争最激烈的行的更新放在事务的最后一部分，可以减少这些行被锁定的时间，从而减少其他事务需要等待的时间，这对于性能是有利的。

死锁

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。
解决策略：

1. 设置超时时间（innodb_lock_wait_timeout）: 如果出现死锁，系统会在超时后中断其中一个事务，从而使其他事务继续执行。但是，超时时间不能设置得太短，以避免误中断那些只是简单等待锁释放的正常事务。。
2. 启用死锁检测（innodb_deadlock_detect）: 当设置为ON时，会在事务被锁的时候检测是否有死锁发生。如果检测到死锁，数据库会自动回滚死锁链中的某个事务，来解开死锁。

第二十讲：幻读是什么，幻读有什么问题？

并发事务中存在的问题

赃读：一个事务读到另外一个事务还没有提交的数据。
不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
幻读与不可重复读类似：一个事务A查询数据时，另一个事务在插入数据时，事务A后续的查询中,发现了不该存在的记录,就是所谓的幻读

如何解决幻读？

产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记 录之间的 “ 间隙 ” 。因此，为了解决幻读问题， InnoDB 只好引入新的锁，也就是 间隙锁(Gap Lock)。
但是间隙锁的引入会影响系统的并发度，也增加了锁分析 的复杂度

间隙锁

间隙锁（Gap Lock）不锁定具体的数据行，而是锁定两个数据行之间的间隙，主要目的是防止幻读。

例如，假设有一个表里的数据行是按照某一列的值顺序排列的，现在有一个事务A想要在值10和20之间插入一条数据。如果同时有另一个事务B正在执行涉及到这个范围的查询操作，并对这个范围加了间隙锁，那么事务A就不能在10和20之间插入行，直到事务B释放间隙锁为止。这就有效防止了在事务B的查询操作中出现由于事务A的插入操作而导致的幻读问题。

在REPEATABLE READ（可重复读）下，InnoDB默认会使用间隙锁。在进行范围查询时，不仅对查询到的数据行加锁，还会对查询范围内的间隙加锁。这种机制可以有效防止幻读，但是也可能会导致资源锁定的范围更大，进而影响到数据库的并发处理能力。
在READ COMMITTED（读已提交）下，InnoDB不使用间隙锁，除非是显式地通过某些SQL语句要求锁定某个范围，例如使用SELECT … FOR UPDATE进行加锁查询。这样做降低了资源的锁定范围，提高了数据库操作的并发性，但是牺牲了一部分事务的隔离性能，允许了幻读的发生。

临键锁（Next-Key Lock）

行锁和间隙锁的结合体
前提：间隙锁只能阻止行的插入，并不能解决所有的幻读问题。比如说，如果只是锁住了两个存在数据行之间的空隙，那么对这些实际存在的行的修改还是可以发生的，导致可能仍然会发生幻读现象。
临键锁锁定的是一个行记录以及前面的间隙（即区间）

特点
REPEATABLE READ下，对于普通的SELECT语句，InnoDB不会使用临键锁。但对于使用了FOR UPDATE或LOCK IN SHARE MODE的查询，InnoDB会对符合条件的索引记录以及在这些记录之间的间隙设置临键锁。
当一个事务通过一个索引来查找并锁定一行时，不仅这一行记录会被锁定（防止其他事务进行修改或删除），它之前的间隙也会被锁定（防止其他事务插入新的行）。
当对表进行范围查询并加锁时，比如使用SELECT ... FOR UPDATE命令跟一个范围条件，InnoDB会为查询范围内的所有存在的行以及所有间隙加上临键锁。

日志与主备

第二讲：日志系统：一条SQL更新语句是如何执行的？

redo log 和 binlog 的不同

1. redo log 是 InnoDB 引擎特有的； binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
2. redo log 是物理日志，记录的是 “ 在某个数据页上做了什么修改 ” ； binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如 “ 给 ID=2 这一行的 c 字段加 1 ” 。
3. redo log 是循环写的，空间固定会用完； binlog 是可以追加写入的。 “ 追加写 ” 是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
   当有一条记录需要更新的时候， InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了。同时， InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，有了 redo log ， InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash - safe

第十五讲：答疑文章（一）：日志和索引相关问题

MySQL崩溃恢复时的判断规则

1.如果redolog里面的事务是完整的，也就是已经有了commit标识，则直接提交；
2.如果redolog里面的事务只有完整的prepare，则判断对应的事务binlog是否存在并完整：
a.如果是，则提交事务；
b.否则，回滚事务。

两阶段提交的不同时刻，在MySQL异常重启会出现什么现象

1.如果在图中时刻A的地方，也就是写入redolog处于prepare阶段之后、写binlog之前，发生了崩溃（crash），由于此时binlog还没写，redolog也还没提交，所以崩溃恢复的时候，这个事务会回滚。这时候，binlog还没写，所以也不会传到备库。

2.在时刻B，binlog写完，redolog还没commit前发生crash，崩溃恢复过程中事务会被提交。

临时表

第十三讲：为什么表数据删掉一半，表文件大小不变？

一个 InnoDB 表包含两部分，即：表结构定义和数据。
因为表结构定义占用的空间很 小，所以我们今天主要讨论的是表数据。

数据库中收缩表空间的方法

如果要收缩一个表，只是 delete 掉表里面不用的数据的话，表文件的大小是不会变的，你还要通过 alter table 命令重建表，才能达到表文件变小的目的。

第三十四讲： 到底可不可以使用join？

1. 如果可以使用被驱动表的索引， join 语句还是有其优势的；
2. 不能使用被驱动表的索引，只能使用 Block Nested-Loop Join 算法，这样的语句就尽量不要使用；
3. 在使用 join 的时候，应该让小表做驱动表。

第三十五讲：join语句怎么优化？

1. BKA 优化是 MySQL 已经内置支持的，建议你默认使用；
2. BNL 算法效率低，建议你都尽量转成 BKA 算法。优化的方向就是给被驱动表的关联字段加上索引；
3. 基于临时表的改进方案，对于能够提前过滤出小数据的 join 语句来说，效果还是很好的；
4. 如果需要 left join 的语义，就不能把被驱动表的字段放在 where 条件里面做等值判断或不等值判断，必须都写在 on 里面。

实用性

第十四讲：count(*)这么慢，我该怎么办？

count(*)在不同引擎中的实现方法

1.MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count() 的时候会直接返回这个数，效率很高；
2. InnoDB 引擎执行 count() 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。（因为 InnoDB的事务默认的隔离级别是可重复读，在代码上就是通过多版本并发 控制，也就是 MVCC 来实现的。每一行记录都要判断自己是否对这个会话可见，因此对于 count(*) 请求说， InnoDB 只好把数据一行一行地读出依次判断，可见的行才能够用于计算 “ 基 于这个查询 ” 的表的总行数。 ）

不同count的性能问题select count(?) from t

count() 的语义： count() 是一个聚合函数，对于返回的结果集，一行行地 判断，如果 count 函数的参数不是 NULL ，累计值就加 1 ，否则不加。最后返回累计值。
count() 、 count( 主键 id) 和 count(1) 都表示返回满足条件的 结果集的总行数 ；而 count( 字 段），则表示返回满足条件的 数据行里面，参数“字段”不为NULL的总个数 。
按照效率排序的话， count( 字段 )<count( 主键 id)<count(1)≈count() ，所以我建议
你，尽量使用 count(*) 。

第三十二讲：为什么还有kill不掉的语句？

在 MySQL 中有两个 kill 命令：
kill query+线程id ，表示终止这个线程中正在执行的语句；
kill connection +线程id ，这里 connection 可缺省，表示断开这个线程的连接，当然如果这个线程有语句正在执行，也是要先停止正在执行的语句的。

参考
MySQL实战45讲总结（https://blog.csdn.net/liyifan687/article/details/114224787）
极客时间MySQL实战45讲学习笔记（https://blog.csdn.net/weixin_47887421/article/details/124065427）
MySQL 实战 45 讲（https://time.geekbang.org/column/intro/139）