MySQL学习（执行流程、索引概念、存储引擎结构、区、段与碎片区）

SQL执行流程：
建立链接 -> 发送SQL语句 -> 查询缓存 -> 解析器(词法解析、语法解析，生成解析树)
-> 优化器(逻辑优化、物理优化，生成执行计划)
-> 执行器（执行打开表判断权限后，根据表中的存储引擎定义调用引擎的API进行操作，是抽象接口
实际是看具体的存储引擎与文件系统交互）

MySQL存储引擎！！！
Innodb：具备外键支持功能的事务存储引擎
MyISAM：主要的非事务处理存储引擎
Innodb表的优势：操作便利、提高数据库的性能、维护成本低。且在mysql服务挂掉后重启不需要进行额外的操作
，崩溃自动恢复功能将之前提交的内容定型，然后撤销没有提交的进程，重启之后从崩溃点开始执行！！（基于事务特征操作的）

区别：
    1.Innodb支持事务和外键！！实际开发不推荐使用外键！
    2.在增查方面推荐使用MyISAM引擎，但是如果涉及到改删操作，那么推荐使用InnoDB引擎，因为能体系事务特征
    3.并发量大时也推荐使用Innodb，因为Innodb支持行锁，而MyISAM仅支持表锁！
    4.对比MyISAM存储引擎，Innodb写的处理效率差一点，并且占用更多的磁盘空间来保存数据和索引。

    5.MyISAM不支持事务、外键、行级锁！且崩溃后无法安全恢复

Innodb特点：Innodb为处理大数据量的最大性能设计。移除了.opt,.frm等元数据文件。

Innodb缺点：1.Innodb写入的效率低一些
            2.Innodb对内容要求较高，MyISAM只缓存索引，不缓存真实数据，Innodb不仅缓存索引还缓存真实数据
Innodb关注点：事务：并发写、事务、更大的资源
            
MyISAM特点：5.5之前默认的存储引擎
MyISAM优势：访问数据快，以select 和insert为主的应用，count(*)查询效率很高
MyISAM关注点：性能：节省资源、消耗少、业务简单

MySQL索引的数据结构！！！
官方定义：索引(index)是帮助MySQL高效获取的数据的数据结构！相当于书的目录！！
进行数据查找时，首先查看数据条件是否命中某条索引，符合则“通过索引查找”。不符合则需要“全表扫描”。
创建索引的目的就是为了减少磁盘的I/O次数！加快查询速率！
索引的本质就是数据结构！可以理解为排好序的快速查找数据结构！

二叉搜索树结构：（红黑树属于特殊的二叉搜索树）
                              根节点
       左子节点（比根节点小）        右子节点（比根节点大）

索引是在存储引擎中实现的，每个存储引擎的所有不一定完成相同，且每个存储引擎不一定支持所有的索引类型！

同时，存储引擎可以定义每个表的“最大索引数” 和“最大索引长度”！
所有存储引擎支持每个表至少16个索引！中索引长度至少为256字节。有些存储引擎支持更多的索引数和更大的索引长度。

优点：
    1、减少磁盘的I/O次数
    2、通过创建唯一索引，保证数据库表中的每一行数据的唯一性
    3、可以加速表和表之间的连接，对有依赖关系的子表和父表联合查询时，提高查询速度
    4、减少查询中分组和排序的时间，降低了CPU的消耗
缺点：
    1.创建索引和维护索引要耗费时间，数据量的增加，所耗费的时间也会增加
    2.索引需要占磁盘空间，每个索引要占一定的物理空间，存储在磁盘上，如果有大量索引，索引文件就可能比
数据文件更快达到最大文件尺寸
    3.虽然索引提高了查询速度，但同时也降低更新表的速度，因为当对表中的数据进行增加、删除、修改时，
索引也要动态的维护，这样降低了数据的维护速度

说明：！
索引可以提高查询数据，但会影响插入记录的速度，这情况下最好的办法是先删 除表中的索引后插入数据再创建索引

B+Tree:
一个B+树的节点其实分成好多层，规定下边的那层，也就是存放用户记录的那层为第0层（页子节点）。其他的称为目录项/页
或者内节点，最上层的节点称为根节点！以后依次往上加，一般情况下，我们用到的B+树不会超过4层！层数也少，I/O次数也越少！

索引的几个概念！！
聚簇（聚集）索引 和 非聚簇（聚集）索引（二级索引/辅助索引）

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式！（所有的用户记录都存储在了叶子节点），也就是

所谓的索引即数据，数据即索引！ 聚簇表示 数据行和相邻的键值聚簇的存储在一起。
聚簇索引不需要显示的使用INDEX语句去创建，Innodb引擎会自动为我们创建聚簇索引！

聚簇索引的优点：
        1.数据访问更快，因为索引和数据都是在一个B+Tree中的，因此聚簇索引中获取数据比非聚簇索引更快！
        2.聚簇索引对于主键的“排序查找”和“范围查找”速度更快！
        3.按照聚簇索引排列顺序，查询显示一定的范围数据时，由于数据紧密相连，数据库不用从多个数据块中
    提取数据，所以 节省了大量的I/O操作
         
         缺点：
        1.插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方法，否则会出现页分裂，严重影响性能
        2.更新主键的代价很高，因为将会导致被更新的行移动
        3.二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据
        
        限制：
        1.对应MySQL数据库目前只有Innodb数据引擎支持聚簇索引
        2.每个表只能有一个聚簇索引，一般是该表的主键
        3.如果没有定义主键，Innodb会选择非空的唯一索引代替，如果没有这样的所有，InnoDb会隐式定义一个主键来作为聚簇索引
        4.为了充分的利用聚簇索引的聚簇特性，所以Innodb表的主键列尽量“选用有序的顺序id”

非聚簇索引（二级索引/辅助索引）：
        存储方式和聚簇索引相当，唯一区别在于，叶子节点不存储用户记录，而是存储创建的key + 聚簇索引值
   所以在使用非聚簇索引时，有一个回表的过程！
        回表：二级索引访问需要两次索引查找第一次找到主键值，第二次根据主键值找到行数据！

   问:为什么还需要一次回表操作呢？直接把完整数据都放入叶子节点不行吗？
   答：把完整记录都放到叶子节点是可以不用回表，但是“太占地方”了！相当于每建立一颗B+树都需要把所有的
用户记录都要拷贝一遍，这就太浪费存储空间了。

B+树的注意事项！！！
    1.根页面位置万年不动！因为是不变的，超出的部分是将内容重新复制给一个新的页，然后在创建新的页进行关联
    2.内节点中目录项记录的唯一性！二级索引的目录项记录页也是记录了主键值的！因为要考虑到唯一性！
    3.一个页面最少存储2条记录！

MyISAM引擎中的索引！：
    MyISAM也是B+Tree树结构，和Innodb的区别就是在与，MyISAM引擎的索引叶子节点的data域存放的是“主键值+数据记录地址”！
    所以MyISAM中的索引是分开存储的！索引存储在.MYI文件中
    MyISAM是没有聚簇索引的！所有的索引都是非聚簇索引（二级索引）！

MyISAM和InnoDB的对比小结！！！：
1.Innodb引擎，只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录！而MyISAM中，需要进行一次回表操作
所以MyISAM中建立的索引都是二级索引（非聚簇索引）！
2.Innodb的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是分离的！
3.Innodb的非聚簇索引（二级索引），data域存储相应记录“主键的值”！而MyISAM索引记录的是“数据记录地址”
4.MyISAM的回表操作是十分快速的，因为是拿着地址偏移量直接到文件中取数据，而Innodb是通过获取主键后在去
聚簇索引中找记录
5.Innodb要求表“必须有主键”！而MyISAM可以没有！
6.知道了innodb的索引实现后，就明白了“为什么不建议使用过长的字段作为主键”，因为所有的二级索引都引用主键索引！
过长的主键索引会令二级索引变的过大。
7.用非单调的字段作为主键在Innodb中不是很好，因为数据文件本身是一颗B+Tree树，非单调的主键会造成在插入新
记录时，为了维护B+Tree的特性而频繁的分裂调整，十分的低效！而使用“自增字段为主键是一个很好的选择”！
8.一个表上的索引越多，就会占用越多的存储空间！在增删改记录的时候性能就越差！
补充！！！！
InnoDB不支持hash索引，但是提供“自适应Hash索引”，当某个数据经常被访问，满足一定的条件后，会将这个数据页
的地址存放到Hash表中，这样下次查询时，就可以直接找到这个页面的所在位置！这样B+Tree树也具备了Hash索引的优点！

InnoDB数据存储结构！！！！
1.数据库的存储结构：页（数据页）！
2.磁盘与内存交互基本单位：页！

MySQL Innodb将数据划分为若干个页！默认为是16kb大小
SQL Server中的页大小默认是8KB
页和页之间不一定是连续的，只要通过“双向链表”相关联即可
页中的记录会按照主键大小的顺序组成一个“单向链表”

区：是比页大一级的存储结构，一个区会分配64个连续的页！ 一个区的默认大小是1MB
段：由一个区或多个区组成，区在文件系统是一个连续分配的空间（在Innodb中是连续的64个页），在段中，不要求

区与区之间相邻！段是数据库中的分配单位，不同类型的数据库对象以不同的段形式存在。
表空间：是一个逻辑容器，表空间存储的对象是段，在一个表空间有一个段或多个段，但是一个段只能属于一个表空间
从管理上可以划分为：系统表空间，独立表空间，撤销表空间、临时表空间

页的内部结构：
    页按类型划分，常见的有：数据页（B+Tree树节点），系统页，Undo页，事务数据页等

    数据页的16KB大小被划分成7个部分：
        1.文件头（File Header 38字节）：描述页的信息
        2.页头（Page Header 56字节） ：页的状态信息
        3.最大和最小记录（Infimum+Supremum 26字节） ：两个虚拟的行记录
        4.用户记录（User Records 不确定大小） ：存储行记录内容
        5.空闲记录（Free Space 不确定大小） ：页中还没有被使用的空间
        6.页目录（Page Directory 不确定大小）：存储用户记录的相对位置
        7.文件尾（File Trailer 8字节）：校验页是否完整

    我们可以把这7个结构分成3部分解析：
        文件通用部分：文件头+文件尾
        记录部分：最大最小记录 + 用户记录 + 空闲记录
        第三部分：页头 + 页目录

    1.文件头：描述页的信息（比如页的编号、上一页、下一页是谁等）
        1.Fil_Page_OFFSET(4字节)：单独的页号，InnoDB通过页号定位
        2.Fil_Page_Type(2字节)：当前页的类型
        3.Fil_Page_Prev(4字节)和Fil_Page_Next（4字节）：表示上一页和下一页的页号，这样就通过双向链接串联起来
        
    2.文件尾（文件头也有的内容）：
        1.Fil_Page_SPACE_OR_CHKSUM（4字节）：代表当前页面的校验和（CHKSUM），用途是比较两个页是否相同，
     内存刷盘到磁盘中时，如果头和尾的校验和相等说明页刷盘是完整的，否则就要回滚。
        2.Fil_Page_LSN（8字节）：页面最后修改时的序列号
       
   3.用户记录：
        需要看定义的行格式信息，这里使用Compact行格式中的记录头信息（5字节）：
            1.delete_mask：标记该记录是否被删除，0改成1，会构成一个“垃圾链表”，可重用空间，新记录可将其覆盖
            2.min_rec_mask：B+Tree的每层非叶子节点中最小记录都会添加该标记，
            3.record_type：当前记录的类型，0普通记录，1 B+tree树非叶子节点，2表示最小记录，3表示最大记录
            4.heap_no：表示当前记录在本页中的位置信息，Mysql会自动添加两个记录，称为虚拟记录，代表最小和最大记录
            5.n_owned：表示当前记录拥有的记录数：
            6.next_recode：下一条真实数据的偏移量，被删除的记录为0，
   4.页目录：
       1.将所有的记录分成几个组，用于二分查找，不包含标记删除的记录
       2.第一组，也就是最小记录所在的分组只有1个记录
        最后一组，最大记录所在的一组，会有1-8条记录
      其余的组，记录数量在4-8条之间
         这样的好处，除了第1组，其他的组尽量平分，
       3.每个组的最后一个记录的头信息会存储该组一共有多少条记录（n_owned）
       4.每组的地址偏移量也称之为槽(slot)，每个槽相当于指针指向了不同组的最后一个记录

区、段与碎片区
1.加载页的方式：
1.内存读取，读取速度平均是1ms
2.随机读取，读取数据平均是10ms：排队3ms、寻道4ms、半圈旋转2ms、传输1ms
3.顺序读取。读取速度平均是0.4ms

区：是比页大一级的存储结构，一个区会分配64个连续的页！ 一个区的默认大小是1MB，方便顺序读取！
段：是一个逻辑上的概念，由一个区或多个区组成，区在文件系统是一个连续分配的空间（在Innodb中是连续的64个页），在段中，不要求

区与区之间相邻！段是数据库中的分配单位，不同类型的数据库对象以不同的段形式存在。比如：一段存放叶子节点，
一段存放目录页
1.数据段是叶子节点，索引段是非叶子节点

区：大体上分为4种类型：
    1.空闲的区：现在还没有用到这个区中的任何页面
    2.有剩余空间的碎片区：表示碎片区还有可用页面
    3.没有剩余的空间的碎片区：表示碎片区中所有的页面都被使用，没有空闲页面
    4.附属于某个段的区：每个索引都可以分为叶子节点段和非叶子节点段（属于某个段的区，其他三个独立的，直属表空间）

碎片区：直属于表空间，不属于任何一个段，

此后为某个段分配存储空间的策略是这样的：
    1.在刚开始插入数据时，段是从某个碎片区以单个页面为单位来分配存储空间的
    2.当段中占用了32个碎片区之后，就会申请以完整的区为单位来分配存储空间

表空间：是一个逻辑容器的最上层，表空间存储的对象是段，在一个表空间有一个段或多个段，但是一个段只能属于一个表空间

从管理上可以划分为：系统表空间，独立表空间，撤销表空间、临时表空间。

默认提供的内部系统表：
    1.innodb_sys_tables:查看innodb所有的表信息
    2.innodb_sys_columns:查看innodb所有的列信息
    3.innodb_sys_indexes：查看innodb所有索引信息
    4.innodb_sys_fields:查看innodb所有索引对应的列的信息
    5.innodb_sys_tablespaces:查看innodb所有表空间信息
    6.innodb_sys_datafiles:查看innodb所有表空间对应的文件系统文件路径信息
    7.innodb_sys_vartual:查看innodb中所有的虚拟生成列信息
    行格式: 主要有4中
1.Compact:紧凑的
2.Dinamic:动态的
3.Compressed:压缩的
4.Redundant:冗余的

compact：一条完整的记录由“真实记录”和 “额外信息”组成
额外信息包括：变长字段列表、NULL值列表，记录头信息
Dinamic 和 Compact格式区别就在于 “行溢出”，
Dinamic和Compressed格式处理行溢出在于：采用全部数据存储在溢出页，当前字段只保留20个字节的指针指向溢出页
Compact和Redundant格式处理行溢出在于：将数据存储一部分（768个前字节内容），剩余的存储在溢出页
Compressed格式对BLOB、Text、Varchar大长度类型的数据能够有效的存储，是因为使用了“zlib”压缩算法