数据库索引

#索引

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

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

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

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

磁盘io为数据库瓶颈

索引是什么

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

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

索引的数据结构

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

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

存储引擎

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

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

• innodb
• myisam

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

• 聚簇索引:数据文件和索引文件放一起,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共同组合形成的索引

最左匹配

哈希索引

自适应哈希