精华内容
下载资源
问答
  • MySQL innoDB索引底层原理详解

    万次阅读 多人点赞 2016-09-02 21:25:22
    本文介绍MySQL的InnoDB索引相对底层原理相关知识,涉及到B+Tree索引和Hash索引,但本文主要介绍B+Tree索引,其中包括聚簇索引和非聚簇索引,InnoDB数据页结构详解,B+Tree索引的使用以及优化,同时还有B+Tree索引的...
    摘要
    本文介绍MySQL的InnoDB索引相对底层原理相关知识,涉及到B+Tree索引和Hash索引,但本文主要介绍B+Tree索引,其中包括聚簇索引和非聚簇索引,InnoDB数据页结构详解,B+Tree索引的使用以及优化,同时还有B+Tree索引的查询流程简介。
    此文是我对学习InnoDB索引的一个总结,内容主要参考MySQL技术内幕 InnoDB存储引擎一书,及网上一些博客(参考文献会给出)
    一、先从B+Tree入手
    B+树的特性
    因作者文笔有限,B+树的定义如果在这里重复列出的话,应该只会让大家更困惑,同时相信任何一本数据结构书中都能找到其复杂的定义。但是为了便于读者理解接下来的内容,下面只是简单的介绍一下B+树的几个本文中会用到的特性。
    B+树是为磁盘或其他直接存取辅助设备而设计的一种平衡查找树(如果不知道平衡查找树,请自行google),在B+树中,所有记录节点都是按键值的大小顺序存放在同一层的叶节点中,各叶节点指针进行连接。
    下图是在网上找的一张B+树示意图

    二、InnoDB数据页结构
    1.页介绍
    页是InnoDB存储引擎管理数据库的最小磁盘单位。 页类型为B-Tree node的页,存放的即是表中行的实际数据了。
    InnoDB中的页大小为16KB,且不可以更改
    InnoDB可以将一条记录中的某些数据存储在真正的数据页面之外,即作为行溢出数据。MySQL的varchar数据类型可以存放65535个字节,但 实际只能存储65532个 。同时InnoDB是B+树结构的,因此 每个页中至少应该有两个行记录 ,否则失去了B+树的意义,变成了链表,所以一行记录 最大长度的阈值是8098 ,如果大于这个值就会将其存到溢出行中。

    2.InnoDB数据页组成部分
    File Header(文件头)
    Page Header(页头)
    Infimun + Supremum Records
    User Records(用户记录,即行记录)
    Free Space(空闲空间)
    Page Directory(页目录)
    File Trailer(文件结尾信息)
    这也是我摘抄的书上的内容,下面我只介绍一下会帮助理解底层原理的部分。

    1.在File header中,FIL+PAGE_PREV,FIL_PAGE_NEXT两个表示当前页的上一页和下一页,由此可以看出 叶子节点是双向链表串起来的 。如下图

    2.Infimum和Supremum记录
    在InnoDB存储引擎中,每个数据页中有两个虚拟的行记录,用来限定记录的边界。Infimum记录是比该页中任何主键值都要小的值,Supremum指比任何可能大的值还要大的值。这两个值在页创建时被建立,并且在任何情况下不会被删除。

    由上图可以看出,行记录是记录在页中的,同时是在页内行记录之间也是双向链表链接的(在网上有看到说是单链表的)
    3.Page Directory
    页目录中存放了记录的相对位置,有些时候这些记录指针称为Slots(槽)或者目录槽,与其他数据库不同的是, InnoDB并不是每个记录拥有一个槽 ,InnoDB中的槽是一个稀疏目录,即一个槽中可能属于多个记录,最少属于4个目录,最多属于8个目录。槽中记录按照键顺序存放,这样可以利用二叉查找迅速找到记录的指针。 但是由于InnoDB中的Slots是稀疏目录,二叉查找的结果只是一个粗略的结果 ,所以InnoDB必须通过recorder header中的next_record来继续查找相关记录。同时slots很好的解释了recorder header中的n_owned值的含义,即还有多少记录需要查找,因为这些记录并不包括在slots中。

    三、查询B+树索引的流程
    首先通过B+树索引找到叶节点,再找到对应的数据页,然后将数据页加载到内存中,通过二分查找Page Directory中的槽,查找出一个粗略的目录,然后根据槽的指针指向链表中的行记录,之后在链表中依次查找。
    需要注意的地方是, B+树索引不能找到具体的一条记录 ,而是只能找到对应的页。 把页从磁盘装入到内存中 ,再通过 Page Directory进行二分查找 ,同时此 二分查找也可能找不到具体的行记录 (有可能会找到),只是能找到一个接近的链表中的点,再从此点开始遍历链表进行查找。

    四、聚簇索引与非聚簇索引
    B+树索引可以分为聚集索引和辅助索引,他们不同点是,聚集索引的行数据和主键B+树存储在一起,辅助索引只存储辅助键和主键。
    1.聚集索引
    聚集索引是按每张表的主键构造的一颗B+树,并且叶节点中存放着整张表的行记录数据,因此也让聚集索引的节点成为数据页,这个特性决定了索引组织表中数据也是索引的一部分。由于实际的数据页只能按照一颗B+树进行排序,所以每张表只能拥有一个聚集索引。查询优化器非常倾向于采用聚集索引,因为其直接存储行数据,所以主键的排序查询和范围查找速度非常快。
    不是物理上的连续,而是逻辑上的,不过在刚开始时数据是顺序插入的所以是物理上的连续,随着数据增删,物理上不再连续。
    2.辅助索引
    辅助索引页级别不包含行的全部数据。叶节点除了包含键值以外,每个叶级别中的索引行中还包含了一个书签,该书签用来告诉InnoDB哪里可以找到与索引相对应的行数据。其中存的就是聚集索引的键。
    辅助索引的存在并不影响数据在聚集索引的结构组织。InnoDB会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键,然后通过主键索引找到一个完整的行记录。当然如果只是需要辅助索引的值和主键索引的值,那么只需要查找辅助索引就可以查询出索要的数据,就不用再去查主键索引了。

    五、索引的管理
    索引在创建或者删除时,MySQL会先创建一个新的临时表,然后把数据导入临时表,删除原表,再把临时表更名为原表名称。
    但是在InnoDB Plugin版本开始,支持快速创建索引。其原理是先在InnoDB上加一个s锁,在创建过程中不需要建表,所以速度会很快。创建过程中由于加了s锁,所以只能进行读操作,不能写操作。
    show index form table;是查看表中索引的信息的。
    Table:索引所在的表名
    Non_unique:非唯一的索引,可以看到primary key 是0,因为必须是唯一的
    Key_name:索引名称
    Seq_in_index:索引中该列的位置
    Column_name:索引的列
    Collation:列以什么方式存储在索引中。可以是A或者NULL,B+树索引总是A,即排序的。
    Cardinality:表示索引中唯一值的数目的估计值。如果非常小,那么需要考虑是否还需要建立这个索引了。优化器也会根据这个值来判断是否使用这个索引。
    Sub_part:是否是列的部分被索引。100表示只索引列的前100个字符。
    Packed:关键字如果被压缩。
    Null:是否索引的列含有NULL值。
    Index_type:索引的类型。InnoDB只支持B+树索引,所以显示BTREE

    六、Hash索引
    InnoDB中自适应哈希索引使用的是散列表的数据结构,并且DBA无法干预。
    其实这一部分的原理,非常简单,在此就不做过多介绍了

    总结
    至此本文就结束了,本文只是从原理上进行了简单的介绍,由于笔者水平有限,且了解不深入,本文多处借鉴书本知识。外加了一些自己的见解,如有错误之处,还请不吝赐教。其中参考的博客地址: http://www.admin10000.com/document/5372.html ,同时还有eremy Cole的博客,地址: https://blog.jcole.us/2013/01/14/efficiently-traversing-innodb-btrees-with-the-page-directory/

    展开全文
  • 1、索引数据结构2、InnoDB索引的设计(1)计算机原理(2)查找过程(3)聚族索引与二级索引(4)创建索引及索引的类型(5)索引的缺点(6)外键及作用 1、索引数据结构 哈希索引(Hash indexes)采用哈希表来对键值...

    目录

    1、索引数据结构
    2、InnoDB索引的设计
    (1)计算机原理
    (2)查找过程
    (3)聚族索引与二级索引
    (4)创建索引及索引的类型
    (5)索引的缺点
    (6)外键及作用


    1、索引数据结构

    哈希索引(Hash indexes)采用哈希表来对键值进行查找,时间复杂度为O(1)。1、使用哈希索引时对于键值的等值查询是非常快的,但是其他类型的查询如范围查询、模糊查询、排序等是不能使用哈希索引的。这是哈希索引使用比较少的主要原因。2、在数据量很大的情况下,内存无法加载全部的数据索引。3、Hash 索引遇到大量Hash值相等(hash碰撞)的情况后性能并不一定就会比B-Tree索引高。

    二叉搜索树:优点:可以解决大量数据索引无法一次加载进内存中的问题,二叉搜索树可以批量加载数据进内存。缺点:1、检索时间与树的高度有关,树的高度越高,检索次数及时间相对就会越久。2、极端情况下,如果数据本身就是有序的,二叉搜索树会退化成链表,性能会急剧降低。红黑树:红黑树是一种自平衡二叉树,主要解决二叉搜索树在极端情况下退化成链表的情况。

    B树:B树是一种多路搜索树(返回整个表时需要往上回查),每个子节点可以拥有多于2个子节点,M路的B树最多可拥有M个子节点。设计成多路,其目的是为了降低树的高度,降低查询次数,提高查询效率。1、虽然多路可以降低树的高度,但是如果设计成无限多路,就会退化成有序数组,一般B树的使用场景是用于文件的索引,这些索引会存放于硬盘中,有时内存是无法一次性加载完,此时就无法进行查找。2、如果全部在内存中,红黑树的查找效率要高于B树,但是涉及到磁盘操作,B树要优于红黑树,所以在JDK1.8版本的HashMap中,如果单个桶的链表长度多于8或全部桶的链表总长度多余64,会将链表转换成红黑树。

    B+树:B+树是对于B树进行优化的多路搜索树,主要设计是将数据全部存放于叶子节点,并将叶子节点用指针进行链表链接。

    • 数据库的索引一般数据量不小,同时又存放于磁盘中,采用多路搜索树,可以降低树的高度,同时在大数据量下可以分批载入内存,提高查询效率。
    • 不同于B树的使用场景,数据库的查询中,我们一般查询的数量不会是单条数据,例如列表常用查询中的分页查询--查询第1页的10条数据,此时如果采用B树,需要进行树的中序遍历,可能需要跨层访问。
    • 而B+树的所有数据全部存放于叶子节点上,且叶子节点之间采用指针进行链表链接一次查询多条时,确定首尾位置,便可以方便的确定多条数据位置。

    • 内节点不存储data,只存储key:更大的出度;叶子节点不存储指针;一般来说,B+Tree比B-Tree更适合实现外存储索引结构

    2、InnoDB索引的设计

    (1)计算机原理

    在计算机系统中一般包含两种类型的存储,计算机主存(RAM)外部存储器(如硬盘、CD、SSD等)。在设计索引算法和存储结构时,我们必须要考虑到这两种类型的存储特点。主存的读取速度快,相对于主存,外部磁盘的数据读取速率要比主从慢好几个数量级,具体它们之间的差别后面会详细介绍。 上面讲的所有查询算法都是假设数据存储在计算机主存中的,计算机主存一般比较小,实际数据库中数据都是存储到外部存储器的。
               一般来说,索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上。这样的话,索引查找过程中就要产生磁盘I/O消耗,相对于内存存取,I/O存取的消耗要高几个数量级,所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说,索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。
               磁盘读取数据靠的是机械运动,当需要从磁盘读取数据时,系统会将数据逻辑地址传给磁盘,磁盘的控制电路按照寻址逻辑将逻辑地址翻译成物理地址,即确定要读的数据在哪个磁道,哪个扇区。为了读取这个扇区的数据,需要将磁头放到这个扇区上方,为了实现这一点,磁头需要移动对准相应磁道,这个过程叫做寻道,所耗费时间叫做寻道时间,然后磁盘旋转将目标扇区旋转到磁头下,这个过程耗费的时间叫做旋转时间,最后便是对读取数据的传输。 所以每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分。
               局部性原理与磁盘预读:为了提高效率,要尽量减少磁盘I/O。为了达到这个目的,磁盘往往不是严格按需读取,而是每次都会预读,即使只需要一个字节,磁盘也会从这个位置开始,顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理:当一个数据被用到时,其附近的数据也通常会马上被使用。程序运行期间所需要的数据通常比较集中。由于磁盘顺序读取的效率很高(不需要寻道时间,只需很少的旋转时间),因此对于具有局部性的程序来说,预读可以提高I/O效率。预读的长度一般为页(page)的整倍数。页是计算机管理存储器的逻辑块,硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块,每个存储块称为一页(在许多操作系统中,页得大小通常为4k),主存和磁盘以页为单位交换数据。

    (2)查找过程

    过程:如果要查找数据项29,那么首先会把磁盘块1由磁盘加载到内存,此时发生一次IO,在内存中用二分查找确定29在17和35之间,锁定磁盘块1的P2指针,内存时间因为非常短(相比磁盘的IO)可以忽略不计,通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存,发生第二次IO,29在26和30之间,锁定磁盘块3的P2指针,通过指针加载磁盘块8到内存,发生第三次IO,同时内存中做二分查找找到29,结束查询,总计三次IO。真实的情况是,3层的b+树可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提高将是巨大的,如果没有索引,每个数据项都要发生一次IO,那么总共需要百万次的IO,显然成本非常非常高。

    再看为啥B+树比B树适合做索引:B+内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。但这不是最主要的因素,关键是B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。一种解释很好:B+树还有一个最大的好处,方便扫库,B树必须用中序遍历的方法按序扫库,而B+树直接从叶子结点挨个扫一遍就完了,B+树支持range-query非常方便,而B树不支持。这是数据库选用B+树的最主要原因。 另外B树也好B+树也好,根或者上面几层因为被反复query,所以这几块基本都在内存中,不会出现读磁盘IO,一般已启动的时候,就会主动换入内存。  真实数据库中的B+树应该是非常扁平的,可以通过向表中顺序插入足够数据的方式来验证InnoDB中的B+树到底有多扁平。通常是单表在千万级,大小几十个G的情况下,高度是3及高度是4的情况通常实际业务达不到已经分表了。

    (3)聚族索引与二级索引

    每个InnoDB的表都拥有一个特殊索引,此索引中存储着行记录(称之为聚簇索引Clustered Index),一般来说,聚簇索引是根据主键生成的。聚簇索引按照如下规则创建:1、当定义了主键后,InnoDB会利用主键来生成其聚簇索引;2、如果没有主键,InnoDB会选择一个非空的唯一索引来创建聚簇索引;3、如果这也没有,InnoDB会隐式的创建一个自增的列(rowid)来作为聚簇索引。

    除了主键索引之外的索引,称为二级索引(Secondary Index)。二级索引可以有多个,二级索引建立在经常查询的列上。与聚簇索引的区别在于二级索引的叶子节点中存放的是除了这几个列外用来回表的主键信息(指针)。

    所谓回表:就是在使用二级索引时,因为二级索引只存储了部分数据,如果根据键值查找的数据不能包含全部目标数据,就需要根据二级索引的键值的主键信息,去聚簇索引的全部数据。然后根据完整数据取出所需要的列。这种在二级索引不能找到全部列的现象称为“非索引覆盖”,需要两次B+树查询,反之称为索引覆盖。所以索引需要平衡考虑,多建索引有利于查询,但是占用空间大还影响写入性能。即索引要精有用。

    这样的优势:1、由于行数据和叶子节点存储在一起,这样主键和行数据是一起被载入内存的,找到叶子节点就可以立刻将行数据返回了,如果按照主键Id来组织数据,获得数据更快。2 、辅助索引使用主键作为"指针" 而不是使用地址值作为指针的好处是,减少了当出现行移动或者数据页分裂时辅助索引的维护工作,使用主键值当作指针会让辅助索引占用更多的空间,换来的好处是InnoDB在移动行时无须更新辅助索引中的这个"指针"。也就是说行的位置(实现中通过16K的Page来定位,后面会涉及)会随着数据库里数据的修改而发生变化(前面的B+树节点分裂以及Page的分裂),使用聚簇索引就可以保证不管这个主键B+树的节点如何变化,辅助索引树都不受影响。

    (4)创建索引及索引的类型

    (1)普通索引:1 直接创建索引   2修改表结构的方式添加索引  3、创建表的时候创建索引

    CREATE INDEX index_name ON table(column(length))
    ALTER TABLE table_name ADD INDEX index_name ON (column(length))
    CREATE TABLE `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `username` varchar(50) DEFAULT NULL,
      `password` varchar(50) DEFAULT NULL,
      `salt` varchar(50) DEFAULT NULL,
      `email` varchar(100) DEFAULT NULL,
      `type` int(11) DEFAULT NULL COMMENT '0-普通用户; 1-超级管理员; 2-版主;',
      `status` int(11) DEFAULT NULL COMMENT '0-未激活; 1-已激活;',
      `activation_code` varchar(100) DEFAULT NULL,
      `header_url` varchar(200) DEFAULT NULL,
      `create_time` timestamp NULL DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `index_username` (`username`(20)),
      KEY `index_email` (`email`(20))
    ) ENGINE=InnoDB AUTO_INCREMENT=160 DEFAULT CHARSET=utf8

    (2)唯一索引:与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式:1 直接创建索引   2修改表结构的方式添加索引  3、创建表的时候创建索引

    (1)
    CREATE UNIQUE INDEX indexName ON table(column(length))
    (2)
    ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))
    (3)
    CREATE TABLE `table` (
        `id` int(11) NOT NULL AUTO_INCREMENT ,
        `title` char(255) CHARACTER NOT NULL ,
        `content` text CHARACTER NULL ,
        `time` int(10) NULL DEFAULT NULL ,
        UNIQUE indexName (title(length))
    );

    (3)主键索引:是一种特殊的唯一索引,一个表只能有一个主键,不允许有空值。一般是在建表的时候同时创建主键索引:

    CREATE TABLE `table` (
        `id` int(11) NOT NULL AUTO_INCREMENT ,
        `title` char(255) NOT NULL ,
        PRIMARY KEY (`id`)
    );

    (4)组合索引:指多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用组合索引时遵循最左前缀原则

    ALTER TABLE `table` ADD INDEX name_city_age (name,city,age); 

    联合索引。一般索引只有一个字段,联合索引可以为多个字段创建一个索引。它的原理也很简单,比如,我们在(a,b,c)字段上创建一个联合索引,则索引记录会首先按照A字段排序,然后再按照B字段排序然后再是C字段,因此,联合索引的特点就是:1、第一个字段一定是有序的;2、当第一个字段值相等的时候,第二个字段又是有序的,依次类推。

    联合索引的查询:(a,b,c)字段上建了一个联合索引,所以这个索引是先按a 再按b 再按c进行排列的,所以:以下的查询方式都可以用到索引

    select * from table where a=1;
    select * from table where a=1 and b=2;
    select * from table where a=1 and b=2 and c=3;
    

    上面三个查询按照 (a ), (a,b ),(a,b,c )的顺序都可以利用到索引,这就是最左前缀匹配。

    如果查询语句是:那么只会用到索引a。

    select * from table where a=1 and c=3; 
    

    如果查询语句是:因为没有用到最左前缀a,所以这个查询是用不到索引的。

    select * from table where b=2 and c=3; 
    

    如果用到了最左前缀,但是顺序颠倒会用到索引码?比如:

    select * from table where b=2 and a=1;
    select * from table where b=2 and a=1 and c=3;
    

    如果用到了最左前缀而只是颠倒了顺序,也是可以用到索引的,因为mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高,最后才生成真正的执行计划。但我们还是最好按照索引顺序来查询,这样查询优化器就不用重新编译了。

    (5)索引的缺点

    数据库索引的作用:加快查找速度,约束数据的值,如唯一索引。

    1.虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行insert、update和delete。因为更新表时,不仅要保存数据,还要保存一下索引文件。

    2.建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会增长很快。索引只是提高效率的一个因素,如果有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。

    什么时候不建议使用索引:1)数据唯一性差的字段:比如性别,意味着二叉树级别少;2)频繁更新的字段:比如登录次数,频繁变化导致索引也频繁变化,增大数据库的工作量,降低效率。3)数据不再where语句出现时:只有在where语句出现,mysql才会用索引;4)数据量少的表不要用索引。

    什么时候要使用索引?1)主键自动建立唯一索引;2)经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引;3)作为排序的列要建立索引;4)查询中与其他表关联的字段,外键关系建立索引;5)高并发条件下倾向组合索引;6)用于聚合函数的列可以建立索引,例如使用了max(column_1)或者count(column_1)时的column_1就需要建立索引

    (6)外键及作用

    外键的定义:表的外键是另一张表的主键。将两张表联系到一起。

    作用:简单的说是为了保证数据的完整性与一致性。

    主键和索引是不可少的,不仅可以优化数据检索速度,开发人员还省下其它的工作;外键考虑有两个问题:一个是如何保证数据库数据的完整性和一致性;二是第一条对性能的影响。

    外键的要求:
    1.父表(student)与子表(sc_class)必须具有相同的存储引擎,而且禁用使用临时表
    2.数据表的存储引擎必须为InnoDB
    3.外键列与参照列必须具有相似的数据类型,其中数字的长度或有无符号位必须相同,而字符的长度可以不同。
    4.外键列和参照列必须创建索引,如果外键列不存在索引,mysql会自动创建索引。

    优势:
    1、由数据库自身保证数据一致性,完整性,更可靠,因为程序很难100%保证数据的完整性,而用外键即使在数据库服务器当机或者出现其他问题的时候,也能够最大限度的保证数据的一致性和完整性。 eg:数据库和应用是一对多的关系,A应用会维护他那部分数据的完整性,系统一变大时,增加了B应用,A和B两个应用也许是不同的开发团队来做的。他们如何协调保证数据的完整性,而且一年以后如果又增加了C应用呢?
    2、有主外键的数据库设计可以增加ER图的可读性,这点在数据库设计时非常重要。 3、外键在一定程度上说明的业务逻辑,会使设计周到具体全面。

    劣势:
    1、可以用触发器或应用程序保证数据的完整性
    2、过分强调或者说使用主键/外键会平添开发难度,导致表过多等问题
    3、不用外键时数据管理简单,操作方便,性能高(导入导出等操作,在insert, update, delete 数据的时候更快)eg:在海量的数据库中想都不要去想外键,试想,一个程序每天要insert数百万条记录,当存在外键约束的时候,每次要去扫描此记录是否合格,一般还不止一个字段有外键,这样扫描的数量是成级数的增长!我的一个程序入库在3个小时做完,如果加上外键,需要28个小时!

    外键的性能问题:
    1.数据库需要维护外键的内部管理;
    2.外键等于把数据的一致性事务实现,全部交给数据库服务器完成;
    3.有了外键,当做一些涉及外键字段的增,删,更新操作之后,需要触发相关操作去检查,而不得不消耗资源;
    4.外键还会因为需要请求对其他表内部加锁而容易出现死锁情况;

     

     

    展开全文
  • 摘要本文介绍MySQL的InnoDB索引相对底层原理相关知识,涉及到B+Tree索引和Hash索引,但本文主要介绍B+Tree索引,其中包括聚簇索引和非聚簇索引,InnoDB数据页结构详解,B+Tree索引的使用以及优化,同时还有B+Tree...

    摘要本文介绍MySQL的InnoDB索引相对底层原理相关知识,涉及到B+Tree索引和Hash索引,但本文主要介绍B+Tree索引,其中包括聚簇索引和非聚簇索引,InnoDB数据页结构详解,B+Tree索引的使用以及优化,同时还有B+Tree索引的查询流程简介。此文是我对学习InnoDB索引的一个总结,内容主要参考MySQL技术内幕 InnoDB存储引擎一书,及网上一些博客(参考文献会给出)一、先从B+Tree入手 B+树的特性 因作者文笔有限,B+树的定义如果在这里重复列出的话,应该只会让大家更困惑,同时相信任何一本数据结构书中都能找到其复杂的定义。但是为了便于读者理解接下来的内容,下面只是简单的介绍一下B+树的几个本文中会用到的特性。 B+树是为磁盘或其他直接存取辅助设备而设计的一种平衡查找树(如果不知道平衡查找树,请自行google),在B+树中,所有记录节点都是按键值的大小顺序存放在同一层的叶节点中,各叶节点指针进行连接。 下图是在网上找的一张B+树示意图

    二、InnoDB数据页结构 1.页介绍 页是InnoDB存储引擎管理数据库的最小磁盘单位。页类型为B-Tree node的页,存放的即是表中行的实际数据了。 InnoDB中的页大小为16KB,且不可以更改 InnoDB可以将一条记录中的某些数据存储在真正的数据页面之外,即作为行溢出数据。MySQL的varchar数据类型可以存放65535个字节,但实际只能存储65532个。同时InnoDB是B+树结构的,因此每个页中至少应该有两个行记录,否则失去了B+树的意义,变成了链表,所以一行记录最大长度的阈值是8098,如果大于这个值就会将其存到溢出行中。

     

    2.InnoDB数据页组成部分 File Header(文件头) Page Header(页头) Infimun + Supremum Records User Records(用户记录,即行记录) Free Space(空闲空间) Page Directory(页目录) File Trailer(文件结尾信息) 这也是我摘抄的书上的内容,下面我只介绍一下会帮助理解底层原理的部分。

    1.在File header中,FIL+PAGE_PREV,FIL_PAGE_NEXT两个表示当前页的上一页和下一页,由此可以看出叶子节点是双向链表串起来的。如下图

    2.Infimum和Supremum记录 在InnoDB存储引擎中,每个数据页中有两个虚拟的行记录,用来限定记录的边界。Infimum记录是比该页中任何主键值都要小的值,Supremum指比任何可能大的值还要大的值。这两个值在页创建时被建立,并且在任何情况下不会被删除。

    由上图可以看出,行记录是记录在页中的,同时是在页内行记录之间也是双向链表链接的(在网上有看到说是单链表的) 3.Page Directory 页目录中存放了记录的相对位置,有些时候这些记录指针称为Slots(槽)或者目录槽,与其他数据库不同的是,InnoDB并不是每个记录拥有一个槽,InnoDB中的槽是一个稀疏目录,即一个槽中可能属于多个记录,最少属于4个目录,最多属于8个目录。槽中记录按照键顺序存放,这样可以利用二叉查找迅速找到记录的指针。但是由于InnoDB中的Slots是稀疏目录,二叉查找的结果只是一个粗略的结果,所以InnoDB必须通过recorder header中的next_record来继续查找相关记录。同时slots很好的解释了recorder header中的n_owned值的含义,即还有多少记录需要查找,因为这些记录并不包括在slots中。

     

    三、查询B+树索引的流程 首先通过B+树索引找到叶节点,再找到对应的数据页,然后将数据页加载到内存中,通过二分查找Page Directory中的槽,查找出一个粗略的目录,然后根据槽的指针指向链表中的行记录,之后在链表中依次查找。 需要注意的地方是,B+树索引不能找到具体的一条记录,而是只能找到对应的页。把页从磁盘装入到内存中,再通过Page Directory进行二分查找,同时此二分查找也可能找不到具体的行记录(有可能会找到),只是能找到一个接近的链表中的点,再从此点开始遍历链表进行查找。

    四、聚簇索引与非聚簇索引 B+树索引可以分为聚集索引和辅助索引,他们不同点是,聚集索引的行数据和主键B+树存储在一起,辅助索引只存储辅助键和主键。 1.聚集索引 聚集索引是按每张表的主键构造的一颗B+树,并且叶节点中存放着整张表的行记录数据,因此也让聚集索引的节点成为数据页,这个特性决定了索引组织表中数据也是索引的一部分。由于实际的数据页只能按照一颗B+树进行排序,所以每张表只能拥有一个聚集索引。查询优化器非常倾向于采用聚集索引,因为其直接存储行数据,所以主键的排序查询和范围查找速度非常快。 理上的连续,而是逻辑上的,不过在刚开始时数据是顺序插入的所以是物理上的连续,随着数据增删,物理上不再连续。 2.辅助索引 辅助索引页级别不包含行的全部数据。叶节点除了包含键值以外,每个叶级别中的索引行中还包含了一个书签,该书签用来告诉InnoDB哪里可以找到与索引相对应的行数据。其中存的就是聚集索引的键。 辅助索引的存在并不影响数据在聚集索引的结构组织。InnoDB会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键,然后通过主键索引找到一个完整的行记录。当然如果只是需要辅助索引的值和主键索引的值,那么只需要查找辅助索引就可以查询出索要的数据,就不用再去查主键索引了。

    五、索引的管理 索引在创建或者删除时,MySQL会先创建一个新的临时表,然后把数据导入临时表,删除原表,再把临时表更名为原表名称。 但是在InnoDB Plugin版本开始,支持快速创建索引。其原理是先在InnoDB上加一个s锁,在创建过程中不需要建表,所以速度会很快。创建过程中由于加了s锁,所以只能进行读操作,不能写操作。 show index form table;是查看表中索引的信息的。 Table:索引所在的表名 Non_unique:非唯一的索引,可以看到primary key 是0,因为必须是唯一的 Key_name:索引名称 Seq_in_index:索引中该列的位置 Column_name:索引的列 Collation:列以什么方式存储在索引中。可以是A或者NULL,B+树索引总是A,即排序的。 Cardinality:表示索引中唯一值的数目的估计值。如果非常小,那么需要考虑是否还需要建立这个索引了。优化器也会根据这个值来判断是否使用这个索引。 Sub_part:是否是列的部分被索引。100表示只索引列的前100个字符。 Packed:关键字如果被压缩。 Null:是否索引的列含有NULL值。 Index_type:索引的类型。InnoDB只支持B+树索引,所以显示BTREE

    六、Hash索引 InnoDB中自适应哈希索引使用的是散列表的数据结构,并且DBA无法干预。 其实这一部分的原理,非常简单,在此就不做过多介绍了

    七、InnoDB和MyISAM的区别

    1.InnoDB支持事物,外键等高级的数据库功能,MyISAM不支持。需要注意的是,InnDB行级锁也不是绝对的,例如mysql执行一个未定范围的sql时,也还是会锁表,例如sql中like的使用

    2.效率,明显MyISAM在插入数据的表现是InnoDB所远远不及的,在删改查,随着InnoDB的优化,差距渐渐变小

    3.行数查询,InnoDB不保存行数,也就是select的时候,要扫描全表,MyISAM只需读取保存的行数即可,这也是MyISAM查询速度快的一个因素。

    4.索引,InnoDB会自动创建Auto_Increment类型字段的索引,一般习惯应用于主键,即主键索引(只包含该字段),而MyISAM可以和其他字段创建联合索引。

    除此之外,MyISAM还支持全文索引(FULLTEXT_INDEX),压缩索引,InnoDB不支持。

    备注:MyISAM的索引和数据是分开的,并且索引是有压缩的,内存使用率就对应提高了不少。能加载更多索引,而Innodb是索引和数据是紧密捆绑的,没有使用压缩从而会造成Innodb比MyISAM体积庞大不小。

    InnoDB存储引擎被完全与MySQL服务器整合,InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB存储它的表&索引在一个表空间中,表空间可以包含数个文件(或原始磁盘分区)。这与MyISAM表不同,比如在MyISAM表中每个表被存在分离的文件中。InnoDB 表可以是任何尺寸,即使在文件尺寸被限制为2GB的操作系统上。

    5.服务器数据备份。InnoDB必须导出SQL来备份,LOAD TABLE FROM MASTER操作对InnoDB是不起作用的,解决方法是首先把InnoDB表改成MyISAM表,导入数据后再改成InnoDB表,但是对于使用的额外的InnoDB特性(例如外键)的表不适用。

    备注:而且MyISAM应对错误编码导致的数据恢复速度快。MyISAM的数据是以文件的形式存储,所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。

    InnoDB是拷贝数据文件、备份 binlog,或者用 mysqldump,支持灾难恢复(仅需几分钟),MyISAM不支持,遇到数据崩溃,基本上很难恢复,所以要经常进行数据备份。

    6.锁的支持。**MyISAM只支持表锁。InnoDB支持表锁、行锁 行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁,只是在WHERE的主键是有效的,非主键的WHERE都会锁全表的

    使用场景建议:

    1)可靠性高或者要求事务处理,则使用InnoDB。这个是必须的。

    2)表更新和查询都相当的频繁,并且表锁定的机会比较大的情况指定InnoDB数据引擎的创建。

    对比之下,MyISAM的使用场景:

    1)做很多count的计算的。如一些日志,调查的业务表。

    2)插入修改不频繁,查询非常频繁的。

    MySQL能够允许你在表这一层应用数据库引擎,所以你可以只对需要事务处理的表格来进行性能优化,而把不需要事务处理的表格交给更加轻便的MyISAM引擎。对于 MySQL而言,灵活性才是关键。

    引擎原理分析

    MyISAM索引结构: MyISAM索引用的B+ tree来储存数据,MyISAM索引的指针指向的是键值的地址,地址存储的是数据。B+Tree的数据域存储的内容为实际数据的地址,也就是说它的索引和实际的数据是分开的,只不过是用索引指向了实际的数据,这种索引就是所谓的非聚集索引

    主索引如下:

    辅助索引如下:

    因此,过程为: MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址,根据data域的值去读取相应数据记录。

    InnoDB引擎的索引结构:

    也是B+Treee索引结构。Innodb的索引文件本身就是数据文件,即B+Tree的数据域存储的就是实际的数据,这种索引就是聚集索引。这个索引的key就是数据表的主键,因此InnoDB表数据文件本身就是主索引。【Java高架构师、分布式架构、高可扩展、高性能、高并发、性能优化、Spring boot、Redis、ActiveMQ、Nginx、Mycat、Netty、Jvm大型分布式项目实战学习架构师视频免费获取架构群;855355016】

    InnoDB的辅助索引数据域存储的也是相应记录主键的值而不是地址,所以当以辅助索引查找时,会先根据辅助索引找到主键,再根据主键索引找到实际的数据。所以Innodb不建议使用过长的主键,否则会使辅助索引变得过大。

    建议使用自增的字段作为主键,这样B+Tree的每一个结点都会被顺序的填满,而不会频繁的分裂调整,会有效的提升插入数据的效率。

    主索引如下:

    辅助索引如下:

    上图,可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。

    而且,与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说,InnoDB的所有辅助索引都引用主键作为data域。

    因此,过程为:将主键组织到一棵B+树中,而行数据就储存在叶子节点上,若使用”where id = 13”这样的条件查找主键,则按照B+树的检索算法即可查找到对应的叶节点,之后获得行数据。若对Name列进行条件搜索,则需要两个步骤:第一步在辅助索引B+树中检索Name,到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作,最终到达叶子节点即可获取整行数据。

    两种索引数据查找过程如下:

     

     

     

     

    展开全文
  • InnoDB索引

    万次阅读 2019-08-05 16:30:09
    InnoDB存储引擎支持一下几种索引 B+ 树索引 全文索引 哈希索引 2.B+ 树索引 B+ 树索引并不能找到给定字符的具体位置,而是将字符所在的页读取到内存中,然后再内存中查找数据。B+树中的B不是代表二叉(binary), ...

    1.概述

    InnoDB存储引擎支持一下几种索引

    • B+ 树索引
    • 全文索引
    • 哈希索引

    本文参考了姜承尧先生的《MySQL技术内幕InnoDB存储引擎》一书

    2.B+ 树索引

    B+ 树索引并不能找到给定字符的具体位置,而是将字符所在的页读取到内存中,然后再内存中查找数据。B+树中的B不是代表二叉(binary), 而是代表平衡(balance)

    B+树索引可以分为聚集索引(clustered inex)和辅助索引(secondary),其内部全是B+树结构,高度平衡。聚集索引与辅助索引的区别是 : 叶子节点存放的是否是一整行的信息

    • 2.1 聚集索引

    聚集索引就是按照每张表的主键构建一颗B+树,叶子节点中存放的即为整张表的行记录数据,也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分,每个数据页都通过一个双向链表进行连接

    由于每个数据页只能按一颗B+树进行排序,因此每张表只能有一个聚集索引,查询优化器倾向于采用聚集索引,因为聚集索引能在B+树索引的叶子节点上直接找到数据。

    聚集索引另一个好处是:他对于主键的排序查找和范围查找速度非常快,叶子节点就是用户查找的数据

    • 2.2 辅助索引

    辅助索引(也称非聚集索引)的叶子节点并不包含行记录的全部数据,叶子节点除了包含键值以外每个叶子节点中的索引行还包含了一个书签(bookmark),书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据,因此书签就是相应行数据的聚集索引键。

    辅助索引并不会影响聚集索引,所以每张表可以有多个辅助索引。
    当通过辅助索引查找数据时,InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获取指向主键索引的主键,然后通过主键索引来找到一个完整的行记录。

    3.Cardinality 值

    • Cardinality 值的作用

    对于查询条件中出现的列不一定都需要添加索引,对于反复出现重复字段的,例如性别,类型、地区等重复性高的列,他们取值范围很小,属于低选择性,这时添加索引是没有必要的,相反,对于数据几乎没有重复的字段,属于高选择性的添加B+树索引最合适。

    查看索引是否属于高选择性,可以通过SHOW INDEX 中的Cardinality 值,它表示索引中不重复记录数量的预估值。他并不是一个准确值

    4.B+ 树索引的使用

    • 4.1 联合索引

    创建方法和单个索引创建方法一样,不同之处是有多个索引列。

    CREATE TABLE t{
    	a INT,
    	b INT,
    	PRIMARY KEY (a),
    	KEY idx_a_b (a,b)
    } ENGINE = INNODB
    

    索引 idx_a_b 是联合索引,联合的列为(a,b)。

    何时需要使用联合索引呢?
    先看联合索引底层的实现, 多个键值对的B+树和单个键的B+树没有什么不同,都是按顺序存放的。
    在这里插入图片描述
    这样就限制了查找方式,如果我们这样查SELECT * FROM t WHERE a = xxx and b = xxx显然这样能使用这个联合索引找到(a,b)
    但是我们使用SELECT * FROM t WHERE b = xxx查找b,就不会通过索引来查找,因为构建的B+是先根据a的大小进行排序的,索引通过b找不到位置。

    • 4.2 覆盖索引

    覆盖索引是从辅助索引中就可以得到查询记录,不需要查询聚集索引,因为辅助索引是不包含整行信息的,所以其大小远小于聚集索引。

    • 4.3 优化器选择不使用索引的情况

    这种情况多发生于有固定查询范围并且需要对数据整行数据进行查询,例如

    SELECT * FROM TABLES WHERE ID>10000 AND ID<102000
    
    • 4.4 Multi-Range Read(MRR) 优化

    优化的目的是减少磁盘的随机访问,并将随机访问转化为较为有序的数据访问,这对于IO-Bound类型的SQL查询语句带来性能的极大提升。MRR优化可用于range、ref、eq_ref类型的查询。

    • MRR使数据访问变得较为顺序. 在查询辅助索引时, 首先根据得到的查询结果, 按照主键顺序进行排序, 并按照主键排序的顺序进行书签查找
    • 减少缓冲池中页被替换的次数
    • 批量处理对键值的查询操作

    对于InnoDB 和 MyISAM 存储引擎的范围查询和JOIN 查询操作,MRR的工作方式如下:

    • 将查询得到的辅助索引键存放于一个缓存中,这时缓存中的数据时根据辅助索引键值排序的
    • 将缓存中的键值根据RowID进行排序
    • 根据RowID的排序顺序来访问实际的数据文件
    —————————————————————————————————————————————————————————————————————————————————
    
    • 4.5 Index Condition Pushdown (ICP)优化

    之前版本进行索引查询的时候,首先根据索引来查找记录,然后在根据WHERE条件来过滤记录,支持Index Condition Pushdown 后MySQL数据库会在取出索引的同时,判断是否可以进行WHERE条件的过滤,也就是将WHERE的部分过滤操作放在了存储引擎层。

    5 全文检索

    B+树索引的特点是支持前缀进行查找,例如SELECT * FROM blog WHERE content like 'xxx%'。对于SELECT * FROM blog WHERE content like '%xxx',并不支持,这种模式会导致InnoDB扫描整个表,速度会非常慢。那么现在就需要一种方案解决这个问题。那就是全文索引。InnoDB 1.2版本开始,已经全面支持了全文索引(Full-Text Search )。全文检索是将存储于数据库中的整本书或整篇文章中的任意内容单词查找出来的技术。它可以根据需要获得全文中有关章、节、段、句、词等信息,也可以进行各种统计和分析。全文索引使用倒排索引实现辅助表中存储了单词与单词自身在一个或多个文档中的映射。将整个表中单词所在的行及其位置全部索引出来。这样在需要从中检索出行中释放含有某个单词就很快了。

    • 5.1 倒排索引

    在这里插入图片描述
    InnoDB存储引擎采用 full inverted index 的方式实现全文检索。

    展开全文
  • innodb索引

    2019-11-12 14:58:50
    innodb索引分为2种: 聚簇索引: 所有记录都是通过主键大小排序的双向数据页, 底层数据页存储的是真实的数据记录, 通过主键查询的话, 只需要通过此聚簇索引就可以找到真实的数据记录; 二级索引: 通过索引列大小...
  • 3.InnoDB索引

    万次阅读 多人点赞 2021-08-19 10:54:46
    InnoDB索引
  • MySQL存储引擎MyISAM和InnoDB底层索引结构

    万次阅读 多人点赞 2018-10-10 11:29:36
    目录 一 存储引擎作用于什么对象 二 MyISAM和InnoDB对索引和数据的存储在磁盘上是如何体现的 ...五 InnoDB索引结构需要注意的点 PS:为了更好地理解本文内容,我强烈建议先阅读完我的上一篇文章...
  • B+树 再到B+树在Myisam和Innodb中的体现形式,会提到索引失效的情况,以及创建索引时的一些注意事项及其原理。采用大量的图片+部分文字更加清晰的描述。其中部分图片来源于咕泡学院公开课。 准备 提到my...
  • 三、MyIsam主键索引和辅助索引(非主键索引)的结构1、主键索引2、辅助索引(非主键)索引四、InnoDB主键索引与辅助索引的结构1、主键索引2、辅助(非主键)索引五、InnoDB索引结构需要注意的点 一、存储引擎作用于...
  • InnoDB索引总结

    2021-05-09 21:06:11
    InnoDB索引、工具、调优思路全篇总结 测试版本:MySQL-8.0.20 测试版本:MySQL-8.0.20 文章目录InnoDB索引、工具、调优思路全篇总结1)索引类型[单列索引][前缀索引][主键索引][唯一索引][组合索引][全文索引]2)B+...
  • 索引是什么及工作机制? 索引是为了加速对表中数据行的检索而创建的一种分散存储的数据结构。其工作机制如下图: 上图中,如果现在有一条sql语句 select * from user where id = 40,如果没有索引的条件下,我们要...
  • 面试:InnoDB 索引

    2021-03-08 15:41:54
    面试:InnoDB 索引 数据存储 当 InnoDB 存储数据时,它可以使用不同的行格式进行存储;MySQL 5.7 版本支持以下格式的行存储方式: Antelope是 InnoDB 最开始支持的文件格式,它包含两种行格式Compact和...
  • MySQL索引底层实现原理 MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。提取句子主干,就可以得到索引的本质:索引是数据结构。 我们知道,数据库查询是数据库的最主要功能之一。我们都...
  • Mysql InnoDB索引分析

    2020-09-08 15:17:16
    而即使多个存储引擎支持同一种类型的索引,其底层的实现也可能不同。InnoDB 存储引擎在 MySQL 数据库中使用最为广泛,下面我们介绍一下InnoDB索引模型。 在 InnoDB 中,表都是根据主键顺序以索引的形式存放的,...
  • 底层是B+树作为索引结构,叶子节点data存放的是数据记录指针的地址,这种索引结构为非聚簇索引。 下图为myisam底层索引结构: innodb: 与myisam的区别是,数据文件就是索引文件,叶子节点data存放的是数据的整条...
  • 目录一、什么是索引二、索引的分类三、索引的创建1、索引创建2、创建建议四、InnoDB存储引擎索引1、B+树2、InnoDB存储引擎索引类型3、聚集索引4、辅助索引1)单值辅助索引2)复合辅助索引5、行记录的插入五、索引...
  • 在MySQL中,索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的,本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。MyISAM索引实现MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是...
  • 【mysql】MyISAM和InnoDB索引结构分析

    千次阅读 2019-08-22 09:47:31
    MyISAM和InnoDB索引结构分析 一、存储引擎作用于什么对象 存储引擎是作用在表上的,而不是数据库。 二 MyISAM和InnoDB对索引和数据的存储在磁盘上是如何体现的 先来看下面创建的两张表信息,role表使用的存储...
  • 删除索引四、索引的执行过程五、索引底层原理 一、索引的介绍 索引是创建在数据库表中,是对数据库表中的一列或者多列的值进行排序的一种结果,索引是一种提高查询效率的数据结构(B树或者是哈希结构)。 索引...
  • 本文主要从整体上把INNODB索引涉及到的知识点进行梳理,让读者从整体把握索引的原理,具体内容还需要读者自行查看MySQL技术内幕一书,因为网上大多数文章基本都是拷贝这本书的内容,并且有些文章会误导读者,具体...
  • 2、InnoDB索引数据结构 1)B-Tree 在看B+Tree之前,我们先看看B-Tree B-Tree是一种为外查找(磁盘类外存储设备,数据量太大不能全放在内存里)而设计的平衡多叉树。那为什么用B树而不用其他的如二叉树、平衡二叉树呢...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 25,420
精华内容 10,168
关键字:

innodb索引底层