1、数据库连接池
- 我们的java系统，要跟mysql打交道，就必须通过mysql驱动与数据库建立网络连接，才能发送请求到数据库。然后我们的java代码才能基于建立的连接去执行SQL语句。
- Mysql是基于数据库连接池机制实现多线程并发的使用数据库连接去执行SQL语句的。
2、Mysql架构设计
- Mysql通过线程监听网络连接请求，读取并解析SQL语句；
- 工作线程接收到SQL语句后，会转交给SQL接口去执行；
- SQL接口接收到SQL语句之后，会通过查询解析器按照既定的SQL语法规则对语句进行解析，解析成Mysql系统能够理解的语句；
- 接着会通过查询优化器来选择一条最优查询路径；
- 知道了执行计划之后，然后通过执行器去调用存储引擎接口，去真正的执行SQL语句
3、InnoDB存储引擎架构设计
- Buffer Pool缓冲池：
-
Buffer Pool实际上是一片内存数据结构。它的默认大小是128M，可以通过 innodb_buffer_pool_size 参数来设置。
-
Mysql在执行查询、更新等操作时，会检查Buffer Pool是否存在该条数据，如果存在就不用去磁盘加载数据了，直接基于Buffer Pool进行操作，同时更新操作会对数据加独占锁。
-
表空间
- 对应磁盘上的 “表名.ibd” 的数据文件。
- 一个表空间分为很多组数据区，256个数据区分为一组，一个数据区对应64个数据页
- 第一组数据区的第一个数据区的头三个数据页和其他组数据区的第一个数据区的头两个数据页，都是存放特殊信息的。
-
数据页
-
Buffer Pool中是通过一个一个数据页来存储一行行的数据的。
-
数据页的大小是16k，它与磁盘中的数据页是一一对应的。
-
每个数据页都包含一个描述信息，包括表空间、数据页的编号、在Buffer Pool中的地址和其它的一些东西。每个描述信息大概800字节。
-
存储结构
- 每个数据页在磁盘里都是以DataPage开头，|| 结束的一段连续数据。
- 每个数据页，都有一个指针 linked_list_pre_pointer 指向上一个数据页的物理位置，也有一个指针 linked_list_next_pointer 指向下一个数据页的物理位置。
- 每个数据页内部会存储一行行的数据，每一行数据都会按照主键从小到大排序
- 每行数据都有指针指向下一行数据，形成单项链表。
- 每个数据页都有一个页目录，里面是主键（一组数据行的最小主键）和槽位（一组数据行）的映射关系。
- 如果根据主键查找一条数据，先到数据页根据主键进行二分查找，定位到主键对应的槽位，然后遍历槽位，就能找到那条数据。
- 如果根据非主键字段进行查找，就要进行全表扫描。
-
页分裂
- 随着一个数据页中不断插入数据，一个数据页放不下的时候，就会再搞一个数据页。
- 如果后一个数据页的主键值比前一个数据页的主键值大，就会把主键值较小的数据移到前一个数据页，主键值较大的数据移到后一个数据页，这个过程就叫做页分裂
-
缓存页是采用随机读写的方式，性能比较差。
-
-
free链表
- 它是一个双向链表，每个节点都会链接到自己的前后节点
- 每个节点都是一个空闲的缓存页的描述数据块的地址
- 它有一个基础节点，它是不属于Buffer Pool的一个40字节大小的节点，它引用了链表的头结点和尾节点，存储了描述数据块的数量
- 当需要把磁盘中的数据页读取到Buffer Pool中的缓存页的时候，就会从free链表中获取一个描述数据块，就可以获取到这个描述数据块对应的空闲缓存页，然后把磁盘中的数据页读取到对应的缓存页中，同时把对应的描述数据写入对应的缓存页中的描述数据块，最后把该描述数据块从free链表中去除。
-
哈希表
- key为表空间号+数据页号，value为缓存页地址
- 要使用一个数据页的时候，把“表空间号+数据页号”作为key去哈希表里查询，没有就查询数据页，否则说明数据页已经被缓存了。
-
flush链表
- 结构跟free链表是一样的，只不过里面放的是被修改过的缓存页，后面要flush到磁盘文件的。
-
LRU链表（Least Recently Used，最近最少使用）
- 从磁盘加载一个数据页到磁盘的时候，就把这个数据页的描述数据块放到LRU链表头部。
- 只要查询或修改了某个缓存页的数据，就把他放到链表头部，最近访问的就一定在链表头部了。
- Mysql预读机制和全表扫描会导致频繁被访问的缓存页被淘汰。
- 采用冷热数据分离机制来来优化LRU算法，解决频繁被访问的缓存页被淘汰的问题。
-
- undo log日志文件
- 为了便于数据回滚，会把更新前的数据写入undo log日志文件。
- 一条insert语句undo log日志
- 它的类型是TRX_UNDO_INSERT_REC。
- 内容
- 日志开始位置
- 主键各列长度和值
- 表id
- undo log日志编号
- undo log日志类型
- 日志结束位置
- redo log日志文件
- 为了预防因机器宕机而导致数据丢失，mysql会把对Buffer Pool所做的修改写入redo log日志，这些日志是放在Redo Log Buffer缓冲区的。当事务提交的时候，会把这些redo log日志基于一定的策略刷入到磁盘文件。
- 采用的是顺序读写的方式，速度是很快的，性能远高于缓存页的随机读写。
- 记录的是对某个表空间某个数据页的某个偏移量的地方修改了多少字节的值，具体修改的值是什么，即表空间号+数据页号+偏移量+修改几个字节的值+具体值。所以redo log大致就是：日志类型（类似MLOG_1BYTE之类的），表空间ID，数据页号，数据页中的偏移量，修改数据长度，具体修改的数据
- redo log block
- 它是用来存放一行一行的redo log 日志文件的。它有512个字节，分为三个部分，分别为12字节的header块头，496字节的body块体和4字节的trailer快尾。header头又分为4个部分，分别为4字节的block no（块唯一编号），2字节的data length（block写入的字节数据长度），2字节的first record group（block里的第一组redo log偏移量）和4字节的checkpoint on。
- 我们执行完增删改之后，redo log其实是先进入redo log block，再进入磁盘文件的。
- 刷盘时机
- 写入redo log buffer的日志超过总容量的一半；
- 事务提交的时候；
- 后台线程定时刷盘；
- Mysql关闭的时候
- redo log buffer：顾名思义，它是用来缓冲redo log的。它被划分为多个空的redo log block，大小16M，可以通过参数 innodb_log_buffer_size 来设置。
- binlog日志：（binlog不是InnoDB特有的日志文件，而是属于mysql的。只是和undo log和redo log放在一起做一个区分）提交事务的时候，把redo log刷入磁盘文件的同时，也会基于一定的刷盘策略把binlog刷入到磁盘。
- redo log刷盘策略：
- 可以通过 innodb_flush_log_at_trx_commit 参数来设置
- 参数值为0时，提交事务不会把redo log刷入磁盘；
- 参数值为1时，就会把redo log刷入磁盘；
- 参数值为2时，会把redo log写入os cache中，可能过1秒后才会刷入磁盘；
- binlog刷盘策略：
- 可以通过 sync_binlog 参数来设置
- 参数值为0：（默认为0）binlog会把进入os cache内存缓存，而不是直接刷入磁盘；
- 参数值为1：直接刷入磁盘
- commit标记：完成事务提交之后，会把binlog文件名和binlog在文件中的位置写入redo log日志文件，同时写入一个commit标记；
- 后台io线程随机将内存中更新的脏数据刷会磁盘；
- 一行数据在磁盘中的存储方式：
- 变长字段长度列表 NULL值列表 数据头 列1 列2 DB_ROW_ID DB_TRX_ID DB_ROL_PTR
- 变长字段长度列表放的是VARCHAR变长字段类型的长度，它是采用十六进制，按照逆序进行存放的。比如one five three 三个字段的长度的十六机制分别是0x03、0x04、0x05，实际存储的时候就是0x05、0x04、0x03 …
- NULL值列表存储的是所有允许为空的字段，它是以二进制的方式逆序存放的。例如name，sex，address，remark四个字段都允许为空，那么实际存储的时候就是0110，0表示不为null，1表示null。不足8位就高位补0，所以实际就是00000110
- 数据头是用来描述这行数据的，它有40bit个位
- DB_ROW_ID为一行的唯一标识
- DB_TRX_ID为事务id
- DB_ROL_PTR为回滚指针，用来回滚事务的
4、事务
- 多个事务并发查询和更新引发的问题
- 脏写：事务B修改了事务A修改过的值，但事务A还没提交，一旦事务A回滚，就会导致事务B修改过的值没了。
- 脏读：事务B查询了事务A修改过的值，但事务A还没提交，一旦事务A回滚，就会导致事务B读不到事务A修改过的值了。
- 不可重复度：一个事务多次读取的值都不一样。
- 幻读：一个事务多次查询，结果读取到了之前没看到过的数据。
- SQL标准规定的事务隔离级别
- read uncommitted（读未提交）：解决了脏写问题，但是可能会发生脏读，不可重复读和幻读
- read committed（读已提交）：解决了脏写和脏读问题，但是会发生不可重复度和幻读
- repeatable read（可重复读）：解决了脏写，脏读和不可重复读问题，但是会发生幻读（这个是Mysql默认隔离级别，在该隔离级别下不会发生幻读）
- serializable（串行化）：多个事务串行执行，肯定就解决了以上所有问题。
- MVCC机制
-
undo log版本链
- trx_id：最近一次更新这条数据的事务id
- roll_pointer：指向更新这个事务之前生成的undo log
- 多个事务串行执行的时候，每个事务修改了一行数据，都会更新trx_id和roll_pointer两个隐藏字段，同时之前多个数据快照对应的undo log，会通过roll_pointer指针串联起来，形成一个版本链。
-
ReadView机制
- 执行一个事务的时候，会生成一个ReadView
- 结构
- m_ids：此时正在执行，没有提交的事务
- min_trx_id：m_ids里的最小值
- max_trx_id：下一个要生成的事务id
- creator_trx_id：当前事务id
- 一个事务只能读到当前事务开启前，别的提交事务和当前事务更新的值
- 工作原理
- 基于ReadView机制实现RC隔离级别的关键是每次查询都生成一个ReadView
-
5、锁
事务A更新完数据，释放锁。然后发现事务B也上锁了，就把事务B锁的等待状态改为true，唤醒事务B
- 锁分类
- 行级锁
- 行级锁是Mysql中最细粒度一种锁，能大大减少数据库操作的冲突，但是，由于其粒度最小，加锁的开销最大。
- 共享锁：也叫读锁，S锁。加锁用 select lock in share mode
- 排他锁：也叫写锁，X锁。加锁用 select for update
- 表锁
- 表锁
- 共享锁：加锁用 lock tables xxx read
- 排他锁：加锁用 lock tables xxx write
- 意向锁
- 当给某一行加共享锁或排他锁时，数据库会自动给这一行所处的表添加意向共享锁或意向排他锁
- 意向锁的作用是，如果有了意向锁，只需要判断该意向锁与即将添加的表级锁是否兼容即可。因为意向锁的存在代表了，有行级锁的存在或者即将有行级锁的存在。因而无需遍历整个表，即可获取结果
- 意向共享锁和意向排他锁都是系统自动添加和自动释放的，整个过程无需人工干预
- 意向共享锁
- 意向排他锁
- 表锁
- 行级锁
| 锁类型 | 排他锁 | 意向排他锁 | 共享锁 | 意向共享锁 |
|---|---|---|---|---|
| 排他锁 | 互斥 | 互斥 | 互斥 | 互斥 |
| 意向排他锁 | 互斥 | 不互斥 | 互斥 | 不互斥 |
| 共享锁 | 互斥 | 互斥 | 不互斥 | 不互斥 |
| 意向共享锁 | 互斥 | 不互斥 | 不互斥 | 不互斥 |
6、索引
- B+树实现索引
- 索引页（叶子节点）：存放的是数据页的最小主键值和数据页号，指针指向对应数据页。
- 索引页（非叶子节点）：上层索引页存放的是下层索引页的最小主键值和索引页号。
- 同层级索引页之间相互有指针引用，组成双向链表。
- 如果根据主键查找数据，首先到最上层索引页根据二分查找定位到下一层索引页，下一层索引页也是根据二分查找定位到更下一层索引页，以此类推，定位到最下层索引页，还是根据二分查找就可以定位到数据在哪一个数据页里。
- 如果是根据非主键字段建立的索引，非叶子节点索引页存放的就是索引字段最小值和索引页号，叶子节点索引页存放的就是索引字段最小值和数据页号，数据页里存放了索引字段值和主键值。查询过程和根据主键查询类似，只不过在数据页中是根据索引字段找到的是主键值，再根据主键值到聚簇索引里去搜索，多了一次“回表”过程。
- 聚簇索引：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据。
- 非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置
- 联合索引
- 依次按照各个字段进行二分查找，先定位第一个字段对应的值在哪个数据页，如果第一个字段有多条数据一样，就根据第二个字段来找，直到定位到数据。
- 使用规则
- 等值匹配：where条件字段基于等值来查询，且名称和顺序和索引完全一样。
- 最左列匹配：假如有联合索引（A，B，C），A and B是可以使用到索引的，B and C是使用不到索引的，A and C，A 可以用到索引，C用不到。总之，尽可能从联合索引最左侧字段开始使用，就可以用到索引。
- 最左前缀匹配：如果模糊查询是后置百分号，如A like ‘a%’，是可以用到索引的，但是前置百分号就不可以，如A like ‘%a’。
- 范围查找规则：
- 如果是范围查找，只有联合索引最左侧的列进行范围查找才可以用到索引。
- 如，A > 2 and A < 5，是可以用到索引的。
- 如，A > 2 and A < 5 and B > 10，A是可以用到索引的，B就不可以。
- 等值匹配+范围匹配：如，A = ‘a’ and B > 2 and C > 10，则A 和 B是可以用到索引的，C就不可以。
- 排序（order by）如何才能用上索引
- 尽量按照联合索引的字段顺序来排序，就可以利用索引树的有序性，快速获取到数据。
- 如果排序字段都是升序或者都是降序，是可以用到索引的。但是，如果是既有升序也有降序，是不会用到索引的。
- 如果排序字段不是联合索引列，或排序字段用了复杂函数，是不会用到索引的。
- 分组（group by）如何才能用上索引
- 尽量按照联合索引字段顺序排列。
- 覆盖索引：如果查询字段刚好就是联合索引的字段，则只需要扫描联合索引的索引树就可以了，不再需要回表到聚簇索引去查询其他字段。
- 索引设计规则
- 针对SQL语句where、order by和group by条件来设计索引，每一个联合索引尽量包含where、order by和group by条件的字段，尽量符合最左匹配原则等。
- 只对基数比较大（值较多）的列建立索引。
- 如果一个字段的值很大，如VARCHAR(255)这种字段，可以设计为前缀索引，即只对字段前20个字符建立索引。但是此时order by和group by就用不到索引了。
- 不要在索引列用函数
- 不要设计过多索引，两三个索引就应该要覆盖全部查询。
- 尽量用自增主键，这样就不会导致聚簇索引频繁分裂。
7、执行计划
- const
- 直接通过索引定位到数据。
- 二级索引必须是唯一索引。
- ref
- 通过普通二级索引查询数据。
- 如果是包含多列的联合索引，则必须从索引最左侧开始连续多个列都是等值比较。
- 如果有name is null这种语法，即使name是主键或唯一索引，也只能走ref方式。
- ref_or_null
- 如果字段是二级索引，同时比较了一个值且限定了NULL，类似于name = xxx or name is null。
- range：基于索引进行范围查找
- index：只需要遍历二级索引，不需要回表就能拿到数据。
- all：全表扫描
- 查看执行计划
- explain + sql语句
- 字段分析
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra- id：执行计划唯一标识
- select_type：执行计划对应的语句查询类型
- table：表名
- partitions：表分区
- type：访问方法，如上面的const、ref、ref_or_null等
- possible_keys：可供选择和使用的索引
- key：实际选择的索引
- key_len：索引长度
- ref：使用某个字段索引进行等值匹配时，跟索引列进行等值匹配的那个目标值得欣喜
- rows：预估读取到多少数据
- filtered：经过搜索条件过滤之后，剩余数据的百分比
- Extra：额外欣喜
413
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
