深入浅出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

页的结构中设计者为了能够得到