mysql 学习记录-基础

了解mysql

是一个关系型数据库，mysql默认的最大链接数为 151 ，一条语句的执行过程主要为：词法解析 -> 语法解析 -> 预处理器 -> 优化器 -> 执行器，这么一个流程。
oracle中的sql执行流程（了解），oracle中采用了共享池来判断sql中是否存在缓存和执行计划，通过这一步骤我们可以知道应当采用硬解析还是软解析，执行过程：语法检查 -> 语义检查 -> 权限检查 -> 共享池检查（软解析 /硬解析） -> 优化器，如下图

数据库缓冲池（buffer pool）

innoDB存储引擎数据存储的单位为page，操作系统的默认大小为4KB，mysql的为16kb,我们在进行增删改的操作本质上是访问页，磁盘I/O需要消耗很多时间，如果在内存中操作效率就会高很多，为了让数据表或索引随时能够使用，dbms(数据库管理系统)会神奇占用内存来作为数据缓冲池，在真正访问页面之前，需要把磁盘上的页缓存到内存中的Buffer Pool之后才可以访问。
这样的好处是让磁盘活动最小化，从而减小与磁盘之间的交互，访问成本就降低很多。
缓冲池中包含数据页、索引页、插入缓存、自适应索引哈希、锁信息、数据字典信息等

缓存的原则

位置 * 频次，位置决定了效率，可以帮助我们对I/O访问的效率进行优化，频次代表优先级顺序，因为缓冲池的大小是有限的，所以会优先对频次高得热数据进行加载

缓冲池内存大小设置

如设置大小为256M
set global  innodb_buffer_pool_size = 268435456; 
多个buffer pool实例设置
innodb_buffer_pool_instances = 2

一条记录在mysql中式怎么存储的

目前InnoDB定义了4种行格式、compact、redundant、dynamic、compressed

compact行格式

关键在于记录头信息的设计，记录了当前行的删除状态，在B+树上的信息，指向下一条记录的相对位置信息等，下图为二进制位代表的含义

记录的真实数据的列还包含3个隐藏的列分别式row_id（唯一标识）、transaction_id（事务id）、roll_pointer（回滚指针）

redundant行格式

与compact行格式相比较 redundant行格式多了n_field和1byte_offs_flag两个属性，没有record_type属性
n_field 表示记录中列的数量 1byte_offs_flag  对应的偏移量是使⽤1字节还是2字节

Dynamic和Compressed行格式

mysql5.0默认的行格式位compact，mysql8.0默认的行格式位dynamic
这两种与compact行格式相似，只不过在处理行数据溢出时有点分歧，它们不会记录字符串的前768个字符串，而是把所有的字符串转移到另一个页面中，只记录另一个页面的真是数据存储地址，另外compressed会采用压缩算法对页面进行压缩
行溢出：一个页一般是16kb，当记录中的数据太多，当前页面放不下时，会把多余的数据存储到其他页面，这种现象称为行溢出。

索引

基本概念

索引本质上也是一种数据结构，innoDB默认使用的时b+树索引，支持hash、全文索引
索引的优点：提升查询效率，减少磁盘I/O，减少锁等待和死锁，减少主从赋值的时间
缺点:占用额外的磁盘内存空间，增加维护的成本（索引内部的排序、分页、页的合并），过多的索引会增加优化器的负担

索引的分析

mysql存储单位：页：16KB
页的结构中设计者为了能够得到一个页中存储的记录状态信息，比如当前页有多少条记录、第一条地址、页目录中存储多少个槽等，在页中设计了一个Page Header的部分，这个部分占用56个字节，存储各种状态信息 

在页的设计中有一个页目录的定义，page Directory，页目录中存储页记录中划分的每个小组中最后一条记录的地址偏移量，这些偏移量称之为槽（slot）,这样查找数据时就不用遍历页中的每一条记录，只需要通过二分法遍历槽中的位置，同理很多页之间也会进行一个归类将页号、偏移量等信息归类用一个页去存储（这样就形成了一个树）从而实现快速查询，如下图所示

B+树放到下一章