MySQL

极客时间《MySQL实战45讲》笔记 MySQL的基本结构 大体来说，MySQL 可以分为 Server 层和存储引擎层两部分。 Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。 而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。 现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。 从一条查询语句看各个模块的执行过程 对于如下语句： mysql> select * from T where ID=10； 连接器 第一步，你会先连接到这个数据库上，这时候接待你的就是连接器。连接器负责跟客户端建立连接、获取权限、维持和管理连接。连接命令一般是这么写的： mysql -h$ip -P$port -u$user -p 这里要注意的一个问题是：一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。 因为一个连接的权限实在用户名密码认证通过后，连接器到权限表中查出的，之后这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。 查询缓存 连接建立完成后，你就可以执行 select 语句了。执行逻辑就会来到第二步：查询缓存。 之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。如果在缓存中可以找到查询结果，可以直接返回结果，如果找不到，就会继续后面的执行阶段。 但大多数情况下缓存往往弊大于利，因为查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。 可以将query_cache_type参数设置为DEMAND,这样对于默认的SQL查询语句都不使用缓存。 需要注意的是，MySQL 8.0 版本直接将查询缓存的整块功能删掉了，也就是说 8.0 开始彻底没有这个功能了。 分析器 如果没有命中查询缓存，就要开始真正执行语句了。首先，MySQL 需要知道你要做什么，因此需要对 SQL 语句做解析。 分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。 做完了这些识别以后，就要做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。 优化器 经过了分析器，MySQL 就知道你要做什么了。在开始执行之前，还要先经过优化器的处理。...

极客时间《MySQL实战45讲》笔记 MySQL中的隔离级别 见本地事务的隔离 事务隔离的实现 事务之间的隔离是如何实现的? 在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。 假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录。 当前值是 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。如图中看到的，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。对于 read-view A，要得到 1，就必须将当前值依次执行图中所有的回滚操作得到。 但这种回滚日志不能一直保留，当系统判断，没有事务再需要用到这些回滚日志时，回滚日志会被删除，也即当系统中没有比这个回滚日志更早的read-view时。 如何尽量避免长事务 为什么要避免长事务？ 长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。 在 MySQL 5.5 及以前的版本，回滚日志是跟数据字典一起放在 ibdata 文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小。 除了对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库。 如何避免长事务？ MySQL 的事务启动方式有以下几种： 显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。 set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。 因此，建议总是使用 set autocommit=1, 通过显式语句的方式来启动事务。 但这样会多一次“交互”，针对这个问题，使用commit work and chain 语法。 在 autocommit 为 1 的情况下，用 begin 显式启动的事务，如果执行 commit 则提交事务。如果执行 commit work and chain，则是提交事务并自动启动下一个事务，这样也省去了再次执行 begin 语句的开销。同时带来的好处是从程序开发的角度明确地知道每个语句是否处于事务中。...

极客时间《MySQL实战45讲》笔记 什么是change buffer，有什么作用 当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。这个操作称为merge. 这里需要注意的是，change buffer看起来像是内存缓存一类的东西，但是change buffer是可以持久化的数据。也就是说，change buffer在内存中有拷贝，也会被写入到磁盘上。 如果将change buffer也存在磁盘上，而数据也是存储在磁盘上，那么change buffer相比直接读取磁盘数据快在哪里呢？ 从磁盘读取一条记录，是随机读写，而写change buffer，是顺序读写。这二者的速度存在较大差异。随机读写由于存在磁头移道等物理操作，因此比较慢，但顺序读写比较快速。 change buffer的限制 change buffer只是暂时的将更新操作保存下来，而并没有去读取真正的数据。考虑以下情况，表中要求某一字段为唯一的，而在更新时不小心插入了一个与原有某数据重复的条目，这显然是不被允许的。 因此，当表中存在唯一索引、唯一值等限制，这时候就不能用change buffer了。只有普通索引和不存在值唯一性约束的列，才可以用change buffer。 change buffer用的是buffer pool中的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。 change buffer的使用场景 merge 的时候是真正进行数据更新的时刻，而 change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。 因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。 反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用。...

极客时间《MySQL实战45讲》笔记 主备的基本原理 在状态 1 中，客户端的读写都直接访问节点 A，而节点 B 是 A 的备库，只是将 A 的更新都同步过来，到本地执行。这样可以保持节点 B 和 A 的数据是相同的。 当需要切换的时候，就切成状态 2。这时候客户端读写访问的都是节点 B，而节点 A 是 B 的备库。 那么从状态1切换到状态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 的三种格式对比 主备复制依赖于bin log，那么bin log中是什么内容。...

极客时间《MySQL实战45讲》笔记 索引是什么，为何存在，以什么样的结构组织或存储索引? 简单来说，索引是一个目录，用来对数据进行快速的查找。就像一本厚厚的字典，想要查询某个字或者词语，我们固然可以一页页翻阅整本词典，但更好的方式是通过拼音索引或者笔画索引到这条记录。 索引可以有效减小查询的资源消耗，但索引不是毫无代价的，大量的创建索引会造成存储空间的损耗，我们要根据业务需求，有目的的创建对业务有帮助的索引。 在MySQL的InnoDB引擎中，索引是以B+树的形式存在的。B+树的节点存储在物理页上。 根据叶子节点的内容，索引类型分为主键索引和非主键索引。 主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。 非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。 为什么更推荐使用自增主键? 自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的： NOT NULL PRIMARY KEY AUTO_INCREMENT。 B+树天然就是有序的，当我们想在上图中插入一个ID=400的记录，那么可能需要进行页分裂操作，这就需要挪动后面的数据。但如果想插入一个ID=700的值，只需要在最后附加一条记录就可以，不需要对前面的值就行操作。 自增主键的插入数据模式，正符合了我们前面提到的递增插入的场景。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。 除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约 50%。 使用索引查询的过程是怎么样的，什么叫回表、覆盖索引？ 对于如下表： mysql> create table T ( ID int primary key, k int NOT NULL DEFAULT 0, s varchar(16) NOT NULL DEFAULT '', index k(k)) engine=InnoDB; insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg'); 当执行select * from T where k between 3 and 5时，过程如下： 在 k 索引树上找到 k=3 的记录，取得 ID = 300； 再到 ID 索引树查到 ID=300 对应的 R3； 在 k 索引树取下一个值 k=5，取得 ID=500； 再回到 ID 索引树查到 ID=500 对应的 R4； 在 k 索引树取下一个值 k=6，不满足条件，循环结束。 可以看到，MySQL先在k索引树上查找满足条件的记录，拿到主键，然后再到主键索引树上去取整条记录。这个用主键去主键索引上取数据的操作就叫做回表。...