mysql学习笔记(3)之mysql索引与innodb结构

内容来源为六星教育，这里仅作为学习笔记

innodb存储引擎结构

mysql读写IO操作过程

https://www.cnblogs.com/drizzle-xu/p/9869406.html

1. 数据存储在表空间里
2. 当一条SQL来临后，有解析器和优化器生成一个执行计划
3. 执行计划会进入到电脑缓存中
4. 电脑缓存执行这个计划，mysql会根据索引定位数据在磁盘中位置，取出数据返回给电脑缓存中

SQL对于新增是一个规则 =》依赖于索引 主键索引

结构

innodb存储引擎分为独享和共享

独享：一个表一个表空间
共享：多个表公用一个空间

innodb是由多个段空间组成

叶子段：索引b+tree信息
非叶子段：索引btree的信息
回滚段：回滚的数据
索引段：总的索引位置记录

一个段空间是有多个区空间组成，一个区大概是1mb
一个区中有64个页空间，不满足就会重新申请一个区空间
一个页空间16kb，由多个行空间组成
行空间存储数据的长度大小，实际的表数据，文件头信息等主键索引

innodb缓存池

innodb缓存池 innodb_buffer_pool
其中有数据页和索引页

数据页：实际的操作记录，锁住的数据信息
索引页：记录使用的索引信息

新来的一条SQL会缓存到缓存池中的中间位置，最前端的是访问最频繁的数据，尾端是访问不频繁的数据
8.0之后是查询缓存没有了，而不是innodb的缓存池

sql-IO-执行流程

1. 首先就是用户发送一条SQL通过客户端接收之后，交由解析器解析SQL创建对应的解析树之后
2. 然后优化获取对应的数据表的信息-结构
3. 获取表中对应的数据表，首先就会去缓存中读取索引的如果没有就会通过IO读取在磁盘中记录索引的信息并返回
4. 选择合适的索引：因为一个表会有很多的索引，MySQL会对于每一个索引进行相应的算法推敲然后再做相应的删选留下最为合适的索引，所以如果说索引的 数量多的话会给查询优化器带来一定的负担。
5. 因为在当前的索引为二级索引所以这个时候就会根据二级索引的btree获取到对应的id
6. 读取到所对应的id之后再通过回表查询
7. 根据主键索引获取到对应的数据的页在磁盘中的位置
8. 在获取数据之前会判断索引缓存的数据是否满足查询，然后再判断数据库缓冲池以及读缓冲区中是否有缓冲，如果有就返回。没有就会去执行对应的执行计 划，从磁盘中获取数据信息 Hint：可以理解为SQL中的一个优化标识，在优化器中如果对于一条语句分析完了

索引

索引是数据库相关优化的重要手段，可以大大提高MySQL的检索速度
缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度

索引类型介绍

主键索引：一种特殊的唯一索引，就是我们的主键。一个表只能有一个主键
唯一索引：一个唯一字段建立的索引
单索引：单个字段建立的索引
联合索引：多个字段联合建立的索引
全文索引：针对于中文进行分词的搜索
覆盖索引：是所有查询sql所追求的 索引效率的完美使用

二叉树的解析

innodb存储引擎支持的哈希索引是自适应的，innodb存储引擎会根据标的使用情况自动为表生成哈希索引，不能认为干预是否在一张表中生成哈希索引。

b+树索引就是传统意义上的索引，这是目前关系型数据库系统中查找最为常用和最为有效的索引。B+树结构构造类似于二叉树

二分查找法也称为折半查找法，用来查找一组有序的记录数组中的某一记录，器基本思想是：将记录按有序化排列，在查找过程中采用跳跃式方式查找，既先以 有序数列的中点位置为比较对象，如果要找的元素直销与该重点元素，则讲待查序列缩小为左半部分，否则为右半部分 。

比如：如下的数据

5,10,19,22,33,44,48,55,60,68

现在打算查找60所在的位置。如果说我们通过顺序查找法的话，那么我们可能就需要查找9次也就是一个个去匹配的方式查找。那么二分法则是3次查找方式如下 会先从 5,10,19,22,33,44,48,55,60,68 这个数组中查找中间件值取整数也就是 （10 - 1） / 2 = 4.5 =》4然后用60与 33 对比发现比33大这个时候需要去查找的区间就在 44,48,55,60,68 这一范围内；取中间件值（5 - 1）/ 2 = 2 =》 2也就是与55进行对比；然后发现还是大于，那么继续往右找 （2 - 1） / 2 = 0.5 =》 0然后匹配到了，这个时候总共执行了3次；

btree结构

b-tree结构

B-Tree是为磁盘等外存储设备设计的一种平衡查找树。因此在讲B-Tree之前先了解下磁盘的相关知识。

系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。

InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB，可通过参数innodb_page_size将页的大小设置为4K、8K、16K

而系统一个磁盘块的存储空间往往没有这么大，因此InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16KB。InnoDB在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述B-Tree，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据。对于不同的记录，key值互不相同。

每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

b+tree结构

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同：

非叶子节点只存储键值信息。
所有叶子节点之间都有一个链指针。
数据记录都存放在叶子节点中。

将上一节中的B-Tree优化，由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示：

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

数据不是存在非叶子节点，所有的数据是存储在叶子节点上的，根据数据的情况进行数据计算会尽量均衡分配

不同的页中 =》基本的浮动范围 =》浮动值 =》 根据最大和最小创建不同的页

分页优化

select title,url,author from article limit 9999999,10;

1. 可以通过子查询
   select * from article where id >= (select id from article limit 9999999,1) limit 10;
2. 先查询id，在查询数据
   select id from article limit 9999999,10;
   select * from article where id in ();
3. 先加索引在查询
   arter table article add index idx_title_url_author(title,url,author);
   select title,url,author from article where id >=(select id from article limit 9999999,1) limit 10;