文章目录
索引
mysql数据操作的宏观过程
实际上数据库中对数据做的CURD操作，不是直接取访问磁盘，全部都是在内存中进行的，定期的将数据刷新到磁盘上。
在内存中，mysql自己开辟一块空间存储数据，vim /etc/my.cnf
buffer_pool作用
-
LRU算法：buffer pool使用LRU（Least Recently Used，最近最少使用）算法来管理数据页的替换策略。当buffer pool中的内存空间不足时，根据LRU算法，MySQL会将最近最少使用的数据页替换出去，为新的数据页腾出空间。通过LRU算法，MySQL可以尽可能地保留最常用的数据页，提高数据的访问效率。
-
数据页管理：buffer pool负责管理内存中的数据页。它维护了一个数据页的缓存池，用于存储磁盘上的数据页副本。当需要读取或修改数据时，MySQL会在buffer pool中查找相应的数据页。如果数据页在缓存池中不存在，MySQL就需要从磁盘上读取相应的数据页，并将其加载到buffer pool中。通过数据页的缓存管理，MySQL可以有效地利用内存空间，提高数据库的访问效率。
-
buffer pool的主要目的是减少对磁盘的访问次数。
mysql自己的缓冲区调用系统调用接口write()写到系统内核缓冲区，系统会定期刷新到磁盘上。如果你想刷新，调用int syncfs(int fd);进行刷新内核缓冲区到磁盘。
alter table emp add index(empnop);构建索引构建索引之后搜索的效率大大提升。
认识磁盘
数据库文件本质就是存放在扇区中，扇区都是512字节。磁盘通过CHS在硬件上定位哪一个扇区。
MySQL是是基于Linux文件系统之上的管理数据的文件，所以找到文件的就是找到磁盘中的扇区，inode等。
IO浪费时间主要是寻道时间，磁头找在那个磁道时浪费的时间，所以如果OS每次以512字节也就是硬件提供的数据大小进行交互，IO代码就和硬件强相关，磁盘的大小发生变化代码就得改。另外单次IO512字节太小，读取同样的数据内容就意味着更多次的磁盘访问。之前的文件系统就是在磁盘的基本结构下建立的读取的基本单位是数据块不是扇区。
系统读取磁盘是以块为单位的，基本单位是4kb。一方面为了提高效率，另一方面将磁盘读取和OS的操作逻辑解耦，避免硬件发生变化导致OSIO出现错误。
MySQL必须得和OS操作系统先交互实现和磁盘的交互，这里先忽略OS，MySQL在和外设进行交互的时候是16KB，为了提高IO效率。这个基本数据单元，在MYSQL这里叫做page。
单page设计为16KB大小,而MYSQL和系统交互也是16KB,这样单次索引,在局部性原理的情况下就可以大概率读取完成.
不忽略OS的话，实际上就是mysql将16KB的数据刷新到系统内核缓冲区，然后调用刷新函数刷新到磁盘当中；读取就是让OS每次以4KB大小从磁盘中读取，读取够16KB再向上交互给MYSQL。
共识
MySQL中的数据文件是以page为单位保存在磁盘中的。
MySQL的CURD操作需要进行计算，找到对应的插入位置或者数据。所以需要CPU，要把数据调用到内存当中，并不需要将整个数据都加载进来。
内存中修改后的数据刷新到磁盘，涉及到磁盘和内存的交互就是IO,基本单位就是page，磁盘和内存进行交互都是固定数据块大小，即使只改变一个字节都要将整个16KB都加载进来。
减少IO的次数，更好的利用局部性原理，就是为何要用16KBpage进行系统交互的理由。
mysql在内存中共运行时就申请了很大的内存空间buffer_pool，来和磁盘数据进行交互。MySQL数据库中的buffer pool是在内存中申请的一块区域，用于缓存磁盘上的数据页.
IO请求
系统中一定存在是大量IO请求，操作系统管理先描述再组织，->struct request_io{};就是个结构体变量，每个磁盘设备也有自己的等待队列进行阻塞等待。
索引的理解
如果创建表时声明主键了乱序插入主键键值时，插入之后表里面是有序的。
索引结构
MySQL中在任何一个时刻，一定会存在大量的page页存在于MySQL中，MySQL本身也需要对它的page进行管理–>先描述再组织，也会存在结构体struct Page{};然后构建一个链表结构，对于PAGE的管理就变成了对于链表的增删查改。内存层面理解page，预先开设的空间保存着这些page。
- 数据库插入数据时按照主键进行排序，优化查询的效率。
页内目录
提高页内搜索效率，因为页内也是用链表连接起来的，添加目录就是用空间换时间的做法。多添加几个目录结构的字节，来记录。但是在搜索的时候时间会提高很多。就比如我们买的书自带的几页目录换取索引效果的提升。
如果有目录就可以排除很多部分数据记录，查找的过程就是排除的过程。MySQL操作必须在内存当中，然后进行刷盘就行。
- MySQL操作是基于Linux文件之上的软件操作系统。
所以为了方便引入目录，就需要选中一些属性,所以需要进行数据主键,以主键作为所谓目录的key值来构建索引结构.
页目录结构图示
- Page之间也是线性的啊！那如何在这方面提高效率呢？
给page也配置上page目录，就像处理page内部数据记录一样。所以就是增加了页目录中目录项存放的id和所管理的page地址，不保存数据，只是用来给下层page创建目录页。从page最小值作为目录页中记录的主键，构建主键索引。
放弃在叶子结点中进行线性遍历,即使page内部也有页目录,当数据页很多时线性遍历的方式也是很慢的.
如果一个页都用来保存页目录（id+指针）假设是8字节，一个page是16KB，那么一个目录页就可以存放2048个记录（页主键信息）：
如果记录足够大，我们可以再向上多一层，就是一颗B+树。
page内部page和page之间的效率问题
给page内部和外部都形成目录结构->构建一颗B+树。
什么是主键索引？
所有的数据最终可以在磁盘中，也可以根据需要,将数据从磁盘中加载到内存中专属于MySQL的缓冲区bufferpool中。
所有的数据都必须以page为单位进行IO进行组织。
在MySQL内部将热点数据以B+树的形式将所有的page页进行组织，形成的数据结构与其配套的查找算法叫做索引。
将索引路途中的page页加载到内存中就可以了，就不需要像线性遍历一样将前面的page都加载在内存中了。
不需要太多的加载无效的page，减少了外设和内存之间IO的次数。
所以分为目录页和数据页。
为什么得是B+树啊？
-
AVL&&红黑树毕竟是二叉树，相比于多阶层数更高进行IO的次数会更多，查找效率越低。
-
哈希不支持区域性查找，因为HASHFUNC数据都是打散的，而B+树叶子结点也及时数据页是相连的。
-
-
B树叶子结点也不是相连的。
-
B树节点如果是目录页也必须要有数据和指针。而B+树只需要有键值和Page指针。所以在空间上，B+树管理的也会更多，因为数据不用保存。就会更矮更胖，IO次数更少。B+树的非叶子结点中不放数据,只是放主键和page指针,相比于B树非叶子节点其他数据结构占更少的空间,一个目录page就能放更多的主键和page指针映射.因为B树非叶子结点中也有存放数据,希望碰到某结果就可以直接返回,但是增加了每个节点的大小.B树叶子结点并不相连.
-
因为IO对于内存级数据结构是相对最慢的,所以我们希望更矮更胖的结构,减少IO次数,降低时间.
- 由于B+树的性质，进行插入的时候就可能导致树的结构发生调整和改变，所以插入的成本会比较高。
- 只会将叶子结点进行连接，也就是将存储数据的page进行连接。页目录之间是没有连接的。
- 多阶B+树矮胖型导致查找的次数会少，IO次数越少。（相较于红黑树（二叉））
- B+支持范围查找，叶子结点是连接起来的，可横跨多个page进行范围查找，而且遍历查找的都是有效数据。（相较于哈希O(1)）
- B+树非叶子节点并不存放数据，B树节点既有数据也有配置指针。这样B+树空间利用率更高，实现了更矮更胖的效果，经历的page个数更少，并且B树叶子结点并不相连。
- 按需加载的方式建立B+树.因为数据查询时可能只会用到几个page的数据,为了降低内存占用率,没必要一次性将所有的数据从外设加载到内存当中,构建page页及数据结构维护.所以成按需加载的方式.提高查找效率的同时降低了内存占有.
B+树在哪里呢？
在磁盘上有完整的B+和数据，在内存中的buffer pool中有局部高频被访问的B+的核心page
MySQL查找一定伴生着MySQL进行根据B+进行page的换入换出！进行对MySQL的表进行操作，就是对文件的操作，所以，MySQL不就是一个用户级文件系统嘛。树状结构和OS的不就一样嘛，只不过更加偏向于数据的管理。
聚簇索引 VS 非聚簇索引
叶子结点是具体数据页,这种将数据和B+树构建的page索引结构放在一起的就叫做聚簇索引.将数据存储与索引放到了一块，找到索引也就找到了数据.
非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引(或者是主键或者其他指示性数据,既可以是物理地址,也可以是内存缓冲区中的数据地址)，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因.
MyISAM叫做非聚簇索引，数据和索引是分离的。叶子结点也不存放数据，而只是存放数据记录的地址，这样就可以保存更多。
INnoDB叫做聚簇索引。.ibd 叶子结点放数据记录
innodb中，在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值。
辅助索引
一张表不单单只有一个索引结构,就是非主键索引，另外用另一个属性为key值组织page页内目录以及其他页目录节点建立一颗B+树。
主键唯一键设定的选择条件就是辅助索引构建时的选择条件.
myisam因为存放的是数据的地址，另外用另一个属性构建一颗B+树叶子结点放的也都是数据的指针，所以普通索引和主键索引并没有任何的差别。
innodb下的非主键索引，叶子结点并没有有效数据而是你要查找的对应记录的主键。也就是说innodb主键索引，叶子结点是数据；非主键索引叶子结点是主键。根据主键索引得到数据记录，根据非主键索引得到主键，再根据主键索引得到数据记录，叫做回表查询。
就不需要因为多了一个辅助索引构建的B+树叶子结点中都是数据,这样添加主键方式是避免非主键索引出现数据冗余。这里辅助索引就算是对于非聚簇索引的一种参考吧!
索引操作
创建主键索引
主键索引基本都是int，最多只有一个主键不为null
在创建表的时候，直接在字段名后指定 primary key
create table user1(id int primary key, name varchar(30));-- 在创建表的最后，指定某列或某几列为主键索引 create table user2(id int, name varchar(30), primary key(id))create table user3(id int, name varchar(30)); -- 创建表以后再添加主键 alter table user3 add primary key(id);
唯一键索引的创建
(就是普通索引)还是需要回表查询的.
一个表中可以有多个唯一索引，查询效率高。如果指定为notnull等价于主键索引
-- 在表定义时，在某列后直接指定unique唯一属性。 create table user4(id int primary key, name varchar(30) unique);
-- 创建表时，在表的后面指定某列或某几列为unique create table user5(id int primary key, name varchar(30), unique(name));
create table user6(id int primary key, name varchar(30)）； alter table user6 add unique(name);
删除主键索引
删除主键索引： alter table 表名 drop primary key;
删除普通索引
-
其他索引的删除： alter table 表名 drop index 索引名； 索引名就是show keys from 表名中的Key_name 字段
alter table user10 drop index idx_name; -
drop index 索引名 on 表名
mysql> drop index name on user8;
添加普通索引
一个表中可以存在两个索引，就有两个B+ 树。
create table user8(id int primary key, name varchar(20), email varchar(30), index(name) --在表的定义最后，指定某列为索引 );create table user9(id int primary key, name varchar(20), email varchar(30)); alter table user9 add index(name); --创建完表以后指定某列为普通索引create table user10(id int primary key, name varchar(20), email varchar(30)); -- 创建一个索引名为 idx_name 的索引 create index idx_name on user10(name);
给一列起别名
create index 别名 on user1(name);删除的时候就得别名指定名称了
全文索引的创建（了解）
目前的所有索引都是建立在基于一列的，而且我们一列的信息都不大。如果一列信息是一篇文章？就需要进行全文索引，对文章进行分词建立正排倒排索引。默认的全文索引支持英文不支持中文。
只有myisam支持。
创建索引的原则
比较频繁作为查询条件的字段应该创建索引。
更新太频繁的字段不适合穿件索引。
不会出现在where语句中的字段不应该创建索引。(得是筛选条件)
唯一性太差的不适合单独创建索引。
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
