#索引

功能:索引,为了查询更快,但是修改效率降低,因为要更新索引

索引作为文件存储到磁盘中,

磁盘单位为页,4k为1页.

磁盘预读:最好将相邻的数据都加载进内存以备访问,加载的数据一般为一页的整数倍,内存与磁盘以页为单位交换数据

磁盘io为数据库瓶颈

索引是什么

索引是帮助mysql高效查找的数据结构,如某索引记录了当前索引的数据在哪一个文件中,文件中的偏移量

索引存储在文件系统中,索引的文件存储形式与存储引擎有关

索引的数据结构

b+树(叶子节点不存数据实例,可以存很多索引,底层节点用来存储数据,并将数据进行排序)hash(不可以范围查找,数据为无序,使用时需要将所有数据都加载到内存中,适合场景为等值查询)二叉树(树过深)b树(叶子节点过大,存储的数据少)

数据越大,索引也就越大,有时内存无法存下这么大的索引,所以就存储一部分,用到时再io查询

存储引擎

表空间:把表所有数据都存放在一起

代表不同的文件格式,不同的存放方式(以下两种都是以本地文件系统)

innodbmyisam

不同的存储引擎,数据文件和索引文件存放的位置是不同的,因此有了分类

聚簇索引:数据文件和索引文件放一起,innodb、frm文件,存放表结构,ibd文件,存放表数据和索引

聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页

一般建表会用一个自增主键做*聚簇索引，没有的话MySQL会默认创建*

根据实际情况自行添加的索引都是辅助索引，辅助索引就是一个为了需找主键索引的二级索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式

Innodb通过主键聚集数据，如果没有定义主键，innodb会选择非空的唯一索引代替。如果没有这样的索引，innodb会隐式的定义一个主键来作为聚簇索引。

优点：

1.数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快

2.聚簇索引对于主键的排序查找和范围查找速度非常快

缺点：

1.插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键 　　　　2.更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。 　　　　3.二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

辅助索引（非聚簇索引）:数据文件和索引文件分开存放,myisam、myi 存放索引数据\ myd存放实际数据

在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。

innodb存储引擎

mysql的innodb引擎会默认将表数据存放于表空间中,不会为单独的表存放一份数据文件,如果要为单独的表存放数据文件,则需要设置set global innodb_file_per_table = on

存储结构:

表空间 段区页行

从包含分

表空间-段-区-页-行

索引文件(默认创建主键索引)是b+树的数据结构,每次读取一小块,找到指定页

b+树先将数据所在的页查询出来,再将页放到内存中查询行

树:左子树必须小于根节点,右子树必须大于根节点

二叉树:二分查找法,查找效率高.但是可能会出现左右不平衡的问题

avl树:二叉树可能出现左树过长成为链表,于是出现平衡树,平衡树会频繁进行旋转,最大层与最小层不会超过1,虽然树已经平衡,但是每插入一个数据需要进行旋转,对性能影响极大

红黑树:基于avl树的升级(不是一个严格意义上的平衡树),损失部分查询性能,提升插入性能.而且加入了变色特性,满足插入和查询性能的平衡

二叉树及其诸多变种无法支撑索引,因为树的深度无法控制树过深,插入数据性能拉垮.

b树:多叉树(平衡多路查找树),一个分支会有多个节点,每个节点对应一个磁盘块(4k的整数倍),磁盘块内有指针指向其他磁盘块地址.,最终如果某指针指向了最底层叶子节点,那么索引指向数据就在其中

左右子树的排序标准为索引的值,左边为小于索引的值,右边为大于索引的值,b+树可以选择每一个节点,每一个磁盘块可以理解为结构中的一个节点,这个节点内部结构为以下(磁盘块为单位)

P1 16 p2 34 p3 pn(可以有非常多个)

​ data data ----->一个磁盘块

P1,p2,p3(指针)再指向下一层,每一个磁盘块中的指针,是有规定最多几个的,当前这个例子中一个磁盘块最多存放3个下层节点地址

16与34为建立索引的键值,如一值24在16-24中间,中间是p2指针,于是查找p2指针指向的磁盘块,于是p2块重复查找

索引数据的查找性能瓶颈为磁盘的io

每个磁盘块内都存储着索引对应的数据,如果数据很大则每一个磁盘块中的能存的数据就少,需要非常多的磁盘块才能存储所有索引,

key值过于分散,磁盘读取次数过多,由于树的深度如果很深,则io次数增加,性能越拉垮

b+树结构(平衡多路查找树),:在b树上作了优化,因为如果data较大,则b树无法存过多的key值,key值过于分散,io次数过多...优化为将存储在非叶子结点上的data数据,全部抽取出来存放到叶子结点上,最终数据全都在叶子结点上,磁盘块只用来计算索引在哪个区间中如 p1-16-p2-38-p3,查找索引为15的值就查看比16小则找p1,再查看叶子结点中的15索引,返回值,如果值等于16,则找p2结点,p1索引值永远小于16!

降低b树的高度每个非叶子节点可以存放更多的key,p指针变得更多,数据检索更快(每一个非叶子节点对应一个磁盘块)每个磁盘块能放多少主键值(索引)等值查询时可以直接定位目标,范围查询时,叶子节点之间是有指针互相指向的.在b+上有两个指针,一个指向根节点,一个指向索引最小的叶子节点,同时所有叶子节点是一个闭环.于是就有两种查询方式了,一种是从根节点(主键)开始查询,一种是从叶子节点开始随机查询

索引数据:

在b+树中,key为索引列,value为页

聚簇索引:聚集索引中存放表的全部信息辅助索引:辅助索引只包含索引列和一个行记录书签

索引:如果是内部列建立索引,则b+树的叶子节点存储列的主键id,得到主键id后再去主键索引树中查找(回表)

innodb有回表操作

mysaim

结构也是b+树

mysaim没有回表,直接找到数据

他的b+树叶子结点存储的是数据地址,地址存储着整条数据信息.

而MyISAM的B+树主键索引和辅助索引的叶子节点是数据文件的地址指针

索引分类

在决定为某个列创建索引时,要思考这个列创建索引时的b+树结构会是什么样的

主键索引

主键是一种唯一索引,必须指定为primary key

唯一索引

索引列的所有值只能出现一次,必须唯一,可以为空

普通索引

基本的索引类型,可以为空

覆盖索引:如select * from table where name = mcj

则先去name索引树中查找,然后再去主键索引树中查找.

select id from table where name = mcj

则先去name索引树中查找,索引树中底层保存着主键,或者唯一键,直接返回(覆盖索引)

全文索引

全文索引的类型为fulltext,群文索引可以在varchar上创建

组合索引

name与age建立组合索引,多列值组成一个索引

name与age一起查询,age单独查询或者name单独查询.优化器会优化查询字段,遵从最左匹配原则,将name或者age放在条件的最左边

但是如果

select * from table where age = 1;

select * from table where age = 1 and name = 'mcj';

组合索引的匹配规则为最左匹配原则,比如建立了name,age的组合索引.那么上面的sql语句则是全表扫描,反之建立age,name的索引后,则会进行扫描索引效率提升.

组合索引的顺序是非常重要的.但是mysql的优化器会进行sql语句的顺序优化,使之符合组合索引

上面的sql建立组合索引有两种选择

age,name

name用户使用age查询,与使用name查询都可以走索引,但是如果不为name建立索引则单查询name是无法走索引的(遵循最左匹配,因为索引的最左为age,如果查询条件没有age则不走索引)

name,age

age

两种方式关键在于单列索引,name的长度比age大每一页存放的数据小,代表数据页会很多,索引树庞大,而age长度小,代表一个数据页内可以维护很多age索引,索引树会很小,io次数会变少

name与age的位置,mysql优化器在优化我们的sql时会尽量吧查询列按照顺序调换位置匹配上

索引下推

给内部列创建索引时会进行回表操作.

索引下推与索引最左匹配原则是为联合索引服务的,只有多个索引的时候才会使用索引下推,

同时联合索引的b+树也与单列不通,联合索引的b+树每个节点都有索引值存在,所以name、age等属性可以直接匹配上

1,张三,10

2,张三,20

3,张三,10

4,张三,40

select * from table where name = '张三' and age = 10

如上会先去匹配name为张三的数据,id为1,2,3,4.然后再去id索引树中查找4条记录再判断age是否匹配10.

现在会先去先去匹配name为张三的数据,id为1,2,3,4 再匹配age的数据

mysql5.6以前,查询到name为张三的有4条数据,然后进行4次回表操作

Mysql5.7查询到name为张三时,还会匹配age=10的数据,再进行回表

建立name,age,sex的联合索引后会生成一个索引树.key为name,age,sex

根据sql,匹配第一个列为张三的,都匹配上,再匹配第二个列age=10的匹配到张三,10,男 返回1,3.

索引下推是在非主键索引下的优化,可以有效的减少回表的次数

索引维护(插入新值)

索引在插入新值的时候,为了维护索引的有效性,必须对索引的位置进行维护

为什么设置成自增,自增数据添加时主键索引的b+树是往叶子节点追加数据.效率较高

不设置成自增则可能会从中间插入数据,可能会导致一下情况(为了保证有序)

插入较大值,直接插入无成本插入中间值,需要移动后续元素空出位置插入页如果满了则需要把一页分解为两页(等值,比如某4k页被分裂,新开辟了一个磁盘页占2k,原本页占2k,总共8k,占用4k,部分空间被浪费)成为页分裂,性能会收到影响,空间使用率也会浪费,比如51、52、54、55中间插入一个53,第一页为51、52,第二页变为53,54,55, 空出了一些空间,申请数据以4k为一个单位,同时有分裂就有合并.可能会合并空间为1页,比如删除了53页合并

mysql执行流程

客户端-连接器-分析器-优化器-执行器-存储引擎-数据

日志

bin-log(mysql)

innodb日志缓冲区

redo-log-buffer

undo-log-buffer

redo-log(innodb)

当数据修改时,会先把记录写到redo-log中,并且更新内存,此时更新就算完成了,同时innodb会在合适的时机将redo-log中的数据写入磁盘.因为直接写磁盘是效率低下的,写一次就是一次io.

一个例子:饭店需要记账,有一本厚厚的账本,如果每次用户来都需要往账本里查用户并且扣钱则非常耗时.所以在门口挂了一块黑板,把每次用户花的钱都记在黑板上,等黑板满了,就把黑板上的记录放到账本里 ,再擦除黑板.所以理论上不存在数据丢失,因为黑板是持久化的

mysql支持用户自定义使用何种方式刷到磁盘(3种)

每次提交写入osbuffer,同时写入磁盘(性能较差,但是非常安全)每次提交写入osbuffer,内核每秒写入磁盘(性能尚可,可能会丢失一秒钟的数据)每秒写入osbuffer,并写入磁盘

建议为每秒调用fsync的方式,默认为第一个

undo-log(innodb)

undo-log用于事物的原子性,在操作任何数据前,会将数据备份到一个地方(undo-log),然后进行数据修改,如果出错了或者用户执行rollback,则利用undo-log中的备份将数据恢复

表锁与行锁

数据更新的流程

获取老数据

执行器从存储引擎中找到数据,如果在内存中直接返回.如果不在内存中则从磁盘查询后返回

更改数据

将数据写入内存中(innodb)

记录redo-log(innodb),通知执行器,数据处于prepare阶段

写入bin-log

提交事物,redo-log数据处于commit阶段

redo-log有两个状态是为了保证bin-log与redo-log中的数据一致性

mysql索引优化

mysql执行计划

explain select * from table

type至少是range级别

key

extra

jyperlogler算法计算基数

基数计算当前唯一列的数量

索引优化

索引分类索引数据结构索引结构的选择索引覆盖索引下推索引失效

主键索引

创建表时，创建的唯一索引

唯一建

非空唯一索引

全文索引

组合索引

组合索引，如name，age，pos共同组合形成的索引

最左匹配

哈希索引

自适应哈希