wshawk's blog

MySQL之InnoDB Buffer Pool简介在MySQL 服务器启动的时候向操作系统申请了一片连续的内存，这块内存就叫做 Buffer Pool 。 默认情况下 Buffer Pool 只有 128M 大小。 可以在启动服务器的时候配置 innodb_buffer_pool_size 参数的值，来设置Buffer Pool的大小，单位是字节。需要注意的是， Buffer Pool 也不能太小，最小值为 5M (当小于该值时会自动设置成 5M )。 本文内容均来自《MySQL是怎样运行的：从根儿上理解MySQL》 内部组成Buffer Pool 中默认的缓存页大小和在磁盘上默认的页大小是一样的，都是 16KB 。 为了更好的管理这些在 Buffer Pool 中的缓存页，衍生了控制信息，主要包括该页所属的表空间编号、页号、缓存页在 Buffer Pool 中的地址、链表节点信息、一些锁信息以及 LSN 信息。 每个页对应的控制信息占用的一块内存可以称为一个控制块，控制块和缓存页是一 一对应的。 每个缓存页的控制信息的长度是固定大小的，在MySQL5.7.21这个版本中，每个控制块 ...

MySQL之MVCC背景 在前面的MySQL锁文章中有提到过，MyISAM表的读操作与写操作，以及写操作之间是串行的。 并且默认情况下写操作命令的执行优先于读操作执行，即使读请求早于写请求到达，写锁也会插队到读请求前面，因为MySQL认为写请求一般比读请求要重要。 在InnoDB中，就是通过多版本并发控制（MVCC）来解决读请求和写请求的并发问题，提高数据库的并发能力。并且在InnoDB中是默认读不加锁，读写不冲突的。 两个概念快照读 不加锁的select操作就是快照读，即不加锁的非阻塞读。当我们执行select xxx from table_name where xxx=yyy语句时，就是快照读。 不是所有的隔离级别下都支持快照读。 读未提交： 不支持，未提交读总是读取最新的数据行，而不是符合当前事务版本的数据行 读已提交：支持，每次select都生成一个快照读 可重复读：支持，开启事务后执行第一个select语句才生成快照读，而不是一开启事务就生成快照读, 并且整个事务只生成一个快照读 串行化：不支持，在此隔离级别下，读请求都是当前读。 当前读 读取的是最新版本, 并且 ...

MySQL之InnoDB数据页页是InnoDB管理存储空间的基本单位，一个页的大小一般是 16KB 。 InnoDB 为了不同的目的而设计了许多种不同类型的页 ，下文主要讲述存储数据行的页，官方称之为索引页。 页 一般由以下七个部分组成。 File Header (38字节) ： 文件头，存放页的一些通用信息 Page Header (56字节)：页面头，存放数据页专有的一些信息 Infimum + Superemum (26字节)：两个虚拟的行记录，分别指向当前页的最小记录和最大记录 User Records (大小不确定，数据行信息)： 实际存储的行记录内容 Free Space (大小不确定)：空闲空间，页中尚未使用的空间 Page Directory (大小不确定)：页面目录，页中某些记录的相对位置 File Tailer (8字节)：文件尾部，校验页是否完整 Infimum、Superemum Infimum记录（也就是最小记录）的下一条记录就是 本页中主键值最小的用户记录， 本页中主键值最大的用户记录的下一条记录就是 Supremum记录（也就是最大记录） Page D ...

MySQL之InnoDB行记录MySQL 不同的存储引擎中真实数据存放的格式一般是不同的，下文简单介绍InnoDB中的行记录格式。 InnoDB存储引擎目前共支持四种行格式，如下表： 行格式 紧凑存储 增强的可变长度列存储 大索引键前缀支持 支持压缩 REDUNDANT 否 否 否 否 COMPACT 是 否 否 否 DYNAMIC 是 是 是 否 COMPRESSED 是 是 是 是 MySQL 5.0之后的默认行格式为Compact ，MySQL5.7之后的默认行格式为Dynamic。 Compact示意图如下。 记录的额外信息变长字段长度列表 varchar(M) 、 varbinary(M) 、text、blob等不确定数据具体长度的数据类型中，存储多少字节的数据是不固定的，我们在存储真实数据的时候需要把这些数据占用的字节数也存起来，读取数据的时候，才能准确读完整这些不确定长度的数据。所以变长字段实际需要存储一下信息 实际的字段值 实际字段长度（字节长度） 如果该可变字段允许存储的最大字节数超过255字节并且真实存储的字节数超过127字节，则使 ...

MySQL之Redo Log简介重做日志，也就是Redo Log是MySQL中InnoDB存储引擎特有的事务日志，它是一种物理日志，存储着数据被修改的具体的数据值。 Redo Log主要有以下作用 实现事务的持久性，让 MySQL 有 crash-safe 的能力，能够保证 MySQL 在任何时间段突然崩溃，重启后之前已提交的记录都不会丢失； 将写操作从「随机写」变成了「顺序写」，提升MySQL 写入磁盘的性能。 2 万字 + 30 张图 ｜ 细聊 MySQL undo log、redo log、binlog 有什么用？ - 知乎 (zhihu.com) 背景MySQL中更新数据的流程大概如下： 将数据从磁盘中读取到内存中(Buffer Pool) 在内存中修改数据，产生脏页 将脏页刷回磁盘 说明： 脏页：修改后的数据，和磁盘上的数据会不一致，称这种有差异的数据为脏页 页：此处留坑，后面补充。 通过上面的更新流程，大概会产生下列问题： 如果每次有脏页产生就刷回磁盘，就会产生海量IO，严重影响性能 如果在刷回磁盘的过程中出现故障，就会造成数据的丢失 MySQL是怎 ...

MySQL 索引概念聚簇索引 将数据存储和索引放到了一块，找到了索引也就找到了数据。 ps： MySQL的InnoDB引擎中，索引数据结构是B+树，主键索引叶子节点的值存储的就是MySQL的数据行，普通索引的叶子节点的值存储的是主键值。 总结： 定义了主键，则该主键就是聚簇索引 未定义主键，第一个not NULL unique列是聚集索引 所有字段均可为NULL，InnoDB会创建一个隐藏的row-id作为聚集索引 非聚簇索引 以主键以外的列值作为键值构建的 B+ 树索引，我们称之为非聚集索引。 在InnoDB引擎中，一般一个表只能有一个聚簇索引，但可以有多个非聚簇索引，每一个非聚簇索引的叶子节点都存储了主键的值。 唯一索引 索引列的值必须唯一，但允许有空值，且空值只能有一个 全文索引 MySQL5.7版本之前只在MyISAM引擎上支持，全文索引可以在Char、Varchar、Text类型的列上创建。 联合索引 多列联合组成索引。 最左前缀优先原则 最左前缀原则（假设索引为a, b, c） 则有效索引为: a a,b a,b,c 覆盖索引 查询字段中，索 ...

MySQL基础架构 MySQL大致可以分为客户端层、Server层和存储引擎两层。 Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。 存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。 不同的存储引擎，是共用同一个Server层的。 组件关系图如下： 客户端层 用户访问MySQL的接口。在客户端，用户可以输入SQL语句，通过SQL语句，实现对数据库的访问、操作和控制。 MySQL Server 层连接器 连接器负责跟客户端建立连接、获取权限、维持和管理连接。 注意点： 连接器在用户登录时读取用户权限并用于后续判断，所以管理员修改密码对已登录用户无效（除非该用户重新登录） 客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wa ...

MySQL锁[TOC] 按照锁思想分类乐观锁 需要应用程序实现，MySQL自身并未实现。 悲观锁按照锁类型分类读锁（共享锁、S锁） 加了读锁的事务，允许该事务进行读，不允许其他事务进行修改 同一行数据，可以被多个事务获得读锁 其他事务不能再获取写锁，只能等获得读锁的所有事务释放后，才能加写锁 写锁（排他锁、X锁） 一个事务获取了写锁，那么该事务可以进行数据的查看及修改 在该事务提交（释放锁）之前，不允许其他事务对该数据进行读操作或者写操作，会被阻塞住 即添加了写锁之后，只允许拥有该锁的事务进行读、写 意向锁 意向锁的主要用途是显示某人正在锁定一行，或者将要锁定表中的一行 想获取读锁或者写锁，必须先获取意向锁 申请意向锁的动作是数据库自动完成的 意向共享锁（IS Lock）意向排他锁（IX Lock）按照锁级别分类全局锁（数据库级别） FTWRL主要包括3个步骤： 上全局读锁(lock_global_read_lock) 清理表缓存(close_cached_tables) 上全局COMMIT锁(make_global_read_lock_block_commit ...

SQL优化笔记（MySQL）目标 减少IO次数 降低CPU的计算 基本原则 尽量少 join 　　MySQL 的优势在于简单，但这在某些方面其实也是其劣势。MySQL 优化器效率高，但是由于其统计信息的量有限，优化器工作过程出现偏差的可能性也就更多。对于复杂的多表 Join，一方面由于其优化器受限，再者在 Join 这方面所下的功夫还不够，所以性能表现离 Oracle 等关系型数据库前辈还是有一定距离。但如果是简单的单表查询，这一差距就会极小甚至在有些场景下要优于这些数据库前辈。 尽量少排序 　　排序操作会消耗较多的 CPU 资源，所以减少排序可以在缓存命中率高等 IO 能力足够的场景下会较大影响 SQL 的响应时间。 　　对于MySQL来说，减少排序有多种办法，比如： 　　上面误区中提到的通过利用索引来排序的方式进行优化 　　减少参与排序的记录条数 　　非必要不对数据进行排序 　　… **尽量避免 select *** 　　很多人看到这一点后觉得比较难理解，上面不是在误区中刚刚说 select 子句中字段的多少并不会影响到读取的数据吗? 　　是的，大多数时候并不会影 ...

MySQL事务(InnoDB)事务的概念事务就是一个不可分割的操作单元，其中的多个操作被认为是一个整体，要么全部执行成功，要么执行失败。 事务的特性通常来说，我们一般认为事务具有四个特性。 隔离性 不同的事务之间，是互相隔离的，不会互相影响。 通过锁和MVCC来实现。 持久性 一旦事务执行完成，那么对事务中影响到的数据的变化，是永久的，一般是指持久化到磁盘上或者日志文件中。 通过redo log来实现 原子性 一个事务中的多个操作，被认为是一个整体，不可再分割，只要其中一个操作执行失败，就认为整个事务执行失败了。 基于undo log。 一致性 事务执行完成后，对于数据的变化，要求要一致。举例来说，转帐，A向B转账，B向C转账，在两个操作结束之后，数据结果要求一致。 通过回滚，以及恢复，和在并发环境下的隔离做到一致性，也就是基于上述的 redo log、undo log、MVCC等一起实现。 并发事务可能产生的问题数据丢失 多个事务选择同一行数据进行更新，最后一个提交的事务所做的修改覆盖了前面其他事务提交的更新。 脏读 事务A可以读到事务B未commit的数据 不可重复读 ...