Mysql

6.1 SQL执行过程 https://xiaolincoding.com/mysql/base/how_select.html#mysql-%E6%89%A7%E8%A1%8C%E6%B5%81%E7%A8%8B%E6%98%AF%E6%80%8E%E6%A0%B7%E7%9A%84 MySQL架构分为两层：server层和存储引擎层 Server层负责建立连接、分析和执行SQL MySQL大多数核心功能模块都在这里：连接器、查询缓存、解析器、预处理器、优化器、执行器等 还有所有的内置函数 所有跨存储引擎的功能 存储引擎层负责数据的存储和提取 支持InnoDB、MyISAM、Memory等多个存储引擎 6.1.1 连接器 MySQL是基于TCP协议进行传输的，所以在连接MySQL的时候需要先进行TCP三次握手，在命令行使用命令进行连接 mysql -h $ip -u$user -p 用户通过用户密码成功连接后，连接器会获取用户的权限，然后保存起来，在后续的此连接的任何操作，都会基于连接开始的时候读取到的权限逻辑进行判断 建立连接后，即使修改了该用户的权限，也不影响已连接的权限。只有新建的连接才会有新的权限设置 6.1.1.1 查看MySQL服务的客户端连接 可以执行show processlist 命令进行查看 6.1.1.2 空闲连接会一直占着 不会，MySQL定义了空闲连接的最大空闲时长，由wait_timeout 参数控制，默认值是8小时，超过这个时间，连接器就会把这个连接断开 使用命令可以查看该值 show variables like 'wait_timeout'; 可以手动断开空闲的连接，使用的是 kill connection + id 当空闲的连接被服务端主动断开后，这个客户端并不会马上知道，等到客户端在发起下一个请求时，才会收到报错 “ERROR 2013 (HY000): Lost connection to MySQL server during query” 6.1.1.3 MySQL的连接限制 MySQL服务支持的最大连接数由max_connections 参数控制 show variables like 'max_connections'; MySQL的连接跟HTTP一样，有短连接和长连接的概念 // 短连接 连接 mysql 服务（TCP 三次握手） 执行sql 断开 mysql 服务（TCP 四次挥手） // 长连接 连接 mysql 服务（TCP 三次握手） 执行sql 执行sql 执行sql .... 断开 mysql 服务（TCP 四次挥手） 一般推荐长连接，但是使用长连接可能会占用内存增多，因为_MySQL在执行查询过程中临时使用内存管理连接对象__，_只有在连接断开的时候才会释放 ...

为什么要有Buffer Pool MySQL的数据存储在磁盘的，如果每次都从磁盘里面读取数据，这样性能是很差的 提高性能，就需要加入缓存。当数据从磁盘中取出来之后，缓存内存中，下次查询同样的数据，直接从内存中读取 为此InnoDB存储引擎设计了一个缓存池（Buffer Pool），来提高数据库的读写性能 有了缓冲池后： 读取数据时，如果数据存在于Buffer Pool中，客户端就会直接读取Buffer Pool中的数据，否则再去磁盘中读取 当修改数据时，首先修改Buffer Pool中数据所在的数据页，然后将该页设置为脏页，最后由后台线程将脏页写入到磁盘 Buffer Pool有多大？ Buffer Pool在MySQL启动的时候，向操作系统申请的一片连续的内存空间，默认配置下Buffer Pool只有128MB 可以通过调整innodb_buffer_pool_size 参数来设置Buffer Pool的大小，一般建议设置为可用物理内存的60%~80% Buffer Pool缓存什么？ InnoDB会把存储的数据分为若干个页，以页作为磁盘和内存交互的基本单位，一个页的默认大小为**16kb，**因此Buffer Pool同样需要按页来划分 在MySQL启动的时候，**InnoDB会为Buffer Pool申请一片连续的内存空间，然后按照默认的16kb的大小划分出一个个的页，Buffer Pool中的页就叫做缓存页。**这些缓存页都是空的，之后随着程序的运行，才会有磁盘上的页被缓存到Buffer Pool中 所以，MySQL刚启动的时候，其使用的虚拟内存空间很大，而使用到的物理内存空间很小，这时因为这些虚拟内存被访问后，操作系统才会触发缺页中断，接着将虚拟地址和物理地址建立映射关系 Buffer Pool缓存了以下的： 索引页 数据页 插入缓存页 Undo页 自适应哈希索引 锁信息 为了更好管理Buffer Pool中的缓存页，InnoDB为每一个缓存页都创建了一个**控制块，**控制块包括缓存页的表空间，页号，缓存页地址，链表节点等，控制块也占据内存空间，它是在Buffer Pool的最前面，接着才是缓存页 暂时无法在飞书文档外展示此内容 上面的控制块和缓存页之间的空白空间称为碎片空间 碎片空间：每一个控制块对应一个缓存页，在分配足够多的控制块和缓存页后，可能剩余的空间不足够一个控制块和缓存页的大小，那么这块空间就不被使用，剩下的这块空间就被称为碎片 当Buffer Pool的大小设置的刚刚好，就不会产生碎片 查询一条记录时，InnoDB会把整个页的数据加载到Buffer Pool中，通过索引只能定位到磁盘中的页，而不能定位到页中一条记录。 mp.weixin.qq.com(从数据页的角度看B+树——InnoDB存储引擎) 记录是按照行来存储的，但是数据库的读取并不是以行为单位，否则一次读取（一次IO操作）只能处理一行数据，效率会非常低，因此，InnoDB的数据是按照数据页为单位来读写的 数据页的结构分为7个部分 File Header(38) 文件头，表示页的信息 Page Header(56) 页头，表示页的状态信息 infimum+supermun(26) 两个虚拟伪记录，分别表示页中最小记录和最大记录 User Records(unclear) 存储行记录内容 Free Space(unclear) 页中还没被使用的 Page Directory(unclear) 页目录，存储用户记录的相对位置，对记录起索引作用 File Tailer(8) 校验页是否完整 其中，行记录由infimum+supremum 和 User Records构成 ...

先理解执行一条sql语句，在mysql内部会发生什么？ 以执行一条update 语句为例： 客户端会先通过连接器建立连接，连接器会判断用户身份 这里是一条update语句，所以不需要经过查询缓存（注意，当表上有更新语句，会把整个查询缓存清空，所以在Mysql8.0这个功能就被移除了） 解析器会通过词法分析识别出关键字，构建出语法树，接着做语法分析，判断输入的语句是否符合MySQL语法 预处理器会判断表和字段是否存在 优化器确定执行计划（使用索引或者全表查询） 执行器负责具体执行，找到这一行然后更新 不过，更新语句的流程会涉及到undo log**，redo log，binlog**三种日志： undo log（回滚日志）：是InnoDB存储引擎生成的日志，实现了事务中的原子性，主要用于事务回滚和MVCC redo log（重做日志）：是InnoDB存储引擎生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复 bing log（归档日志）：是Server层生成的日志，主要用于数据备份和主从复制 1.为什么需要undo log？ 在执行一条“增删改”语句的时候，MySQL会隐式开启事务，执行完后自动提交事务 MySQL中执行一条语句后是否自动提交事务，是由autocommit 参数来决定的，默认是开启的 当事务执行过程中，都记录下回滚时需要的信息到一个日志中，那么在事务执行过程中发生MySQL崩溃后，可以通过这个日志回滚到事务之前的数据 实现这一机制就是 undo log**（回滚日志），它保证了事务的ACID特性中的原子性** 每当InnoDB引擎对每种操作进行回滚时，进行相反操作就行： 插入 - 删除 删除 - 插入 更新 - 更新为旧值 一条记录每次进行操作产生的undo log格式都有一个roll_pointer和一个trx_id事务id： trx_id：记录该记录是被哪些事务修改的 roll_pointer：指针可以将这些undo log串成一个链表，这个链表被称为版本链 另外，undo log可以跟Read View一起实现MVCC（多版本并发控制）： 对于 读提交 和 可重复读 隔离级别的事务来说，它们的快照读（普通select语句）是通过Read View + undo log来实现的，区别在于创建Read View的时机不同 读提交：是在每一个select都会生成一个新的Read View，也意味着事务期间的多次读取同一数据，前后两次读的数据可能会出现不一致（不可重复读） 可重复读：是在启动事务时生成一个Read View，然后整个事务期间都在用这个Read View，这样保证了事务期间读到的数据都是事务启动时的记录 这两个隔离级别实现是通过事务的Read View里的字段和记录两个隐藏列trx_id和roll_pointer的对比 事务隔离级别是怎么实现的？ 因此，undo log两大作用： 实现事务回滚，保障事务的原子性 实现MVCC（多版本并发控制）关键因素之一 Undo log是如何刷盘？ ...

锁的类型 Mysql的锁，根据加锁的范围可以分为全局锁、表级锁和行锁三类 全局锁 要使用全局锁，执行下面这条命令： flush tables with read lock 执行之后，整个数据库就处于只读状态，这时其他线程执行以下操作，就会被阻塞 对数据的增删改，比如insert、delete、update等 对表结构的更改操作，比如alter table、drop table等 要释放全局锁，执行下面的命令： unlock tables 全局锁的应用场景： 全局锁主要用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或者结构的更新，而出现备份文件的数据与预期的不一样 加全局锁带来的缺点：会导致业务停滞，因为加全局锁之后，整个数据库都只是只读状态，不能更新数据 可以通过开启事务，在可重复读的隔离级别下，即使其他事务更新了表的数据，也不会影响备份数据库时的Read View， 备份数据库的工具是mysqldump ，在使用mysqldump时加上-single-transaction 参数的时候，就会在备份数据库之前开启事务 表级锁 MySQL里面表级锁有以下几种： 表锁 元数据锁（MDL） 意向锁 AUTO-INC锁 表锁 使用下面的命令对表加锁和释放锁 // 加读锁 lock tables <table_name> read; // 写锁 lock tables <table_name> write; // 释放锁 unlock tables; 表锁会影响别的线程和本线程的读写操作 元数据锁（MDL） 对于MDL，我们不需要显示使用，因为当我们在对数据库进行操作时，会自动给这个表上加MDL： 对一张表进行CURD操作时，加的是MDL读锁 对一张表做结构变更操作的时候，加的是MDL写锁 MDL是为了保证当前用户对表执行CRUD操作时，防止其他线程对这个表结构做了变更 MDL是在事务提交之后才会释放，这意味着事务执行期间，MDL是一直持有 需要注意的是，在事务启用之后，如果事务A没有提交，此时如果有表结构的修改请求发起，就会发生阻塞，这个阻塞也会导致其他CURD的请求被阻塞住 这是因为申请MDL锁的操作会形成一个队列，队列中写锁获取优先级大于读锁，一旦出现MDL写锁等待，会阻塞该表后续的CRUD操作 意向锁 在使用InnoDB引擎的表里对某些记录加上共享锁之前，需要先在表级别加上一个意向共享锁 在使用InnoDB引擎的表里对某些记录加上独占锁之前，需要先在表级别加上一个意向独占锁 在执行insert、update、delete操作时，需要先对表上加 意向独占锁，然后对该记录加独占锁 而普通的select是不会加行级锁，普通的select语句是利用MVCC实现一致性读，是无锁的 // select也是可以对记录加共享锁和独占锁， // 先在表上加上意向共享锁，然后对读取的记录加共享锁 select ... lock in share mode; // 先表上加上意向锁，然后再读取记录加独占锁 select ... for update 意向锁的目的是为了快速判断表里是否有记录被加锁 AUTO-INC锁 表里面的主键通常设置成自增的，在插入数据时，可以不指定主键的值，数据库会自动给主键赋值递增的值，这主要是通过AUTO-INC锁实现的 Auto-Inc锁是特殊的表锁机制，不是在一个事务提交后才释放，而是再执行完插入语句后就会立即释放 行级锁 InnoDB引擎是支持行级锁的，而MyISAM引擎并不支持行级锁 行级锁的类型主要有三类： ...

1.事务有哪些特征 原子性，隔离性，一致性，持久性 原子性：要么全做，要么全不做 隔离性：保证其它的状态转换不会影响到本次状态的转 一致性：数据全部符合现实世界的约束 持久性： 更新后的数据存储到磁盘 InnoDB引擎通过以下技术来保证事务的四个特性 持久性是通过 redo log（重做日志）来保证 原子性是通过 undo log（回滚日志）来保证 隔离性是通过 mvcc（多版本并发控制）或者锁机制来保证 一致性是通过持久性+原子性+隔离性来保证 2.并发事务会引发的问题 MySQL服务端是允许多个客户端连接，这意味着MySQL会出现同时处理多个事务的情况 在同时处理多个事务的时候，可能会出现脏读、不可重复读、幻读的问题 脏读：一个事务读到了另一个未提交事务修改过的数据 不可重复读：在一个事务中多次读取同一个数据，出现前后两次读到的数据不一样的情况 幻读：在一个事务中多次查询某个符合查询条件的记录数量，如果出现前后两次查询到的记录数据不一样的情况 以上三个现象，问题的严重性是 脏读 > 不可重复读 > 幻读 3.事务的隔离级别 四种隔离级别： 读未提交：指一个事务还没有提交时，它做的变更就能被其他事务看到 读提交：指一个事务提交之后，它做的变更才能被其他事务看到 可重复读：指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB引擎的默认隔离级别 串行化：对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生读写冲突的时候，后访问的事务必须等前一个事务执行完成 按隔离水平高低排序如下： 串行化 > 可重复读 > 读已提交 > 读未提交 针对不同的隔离级别：并发事务时可能发生的现象也不同 读未提交：脏读、不可重复读、幻读 读提交：不可重复读、幻读 可重复读：幻读 串行化： 可重复读的隔离级别下，可以很大程度上避免幻读现象的发生，所以MySQL不使用串行化隔离级别来避免幻读现象的发生，因为串行化隔离级别会影响性能 InnoDB在默认隔离级别：可重复读的情况下很大程度上解决幻读现象的解决方案有两种： 针对**快照读（普通 select 语句），**是通过MVCC方式解决幻读 针对**当前读（select … for update），**通过next-key lock（记录锁+间隙锁）方式解决了幻读 四种隔离事务是怎么实现的 对于读未提交：可以读到未提交事务修改的数据，所以直接读取就行 对于串行化，通过加读写锁的方式来避免并行访问 对于读提交和可重复读这两种隔离级别的事务，是通过Read View来实现的，它们的区别是在于创建Read View时，读提交隔离级别是在每个语句执行之前都会重新生成一个Read View；而可重复读隔离级别是启动事务时生成一个Read View，然后整个事务都在用这个Read View 在执行开启事务命令，并不意味着启动了事务： 在MySQL中，开启事务有两种命令，分别是： ...