精华内容
下载资源
问答
  • 数据库索引的作用
    千次阅读
    2019-01-03 22:36:35

    为什么要创建索引呢?这是因为,创建索引可以大大提高系统的性能。

    第一,通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。

    第二,可以大大加快 数据的检索速度,这也是创建索引的最主要的原因。

    第三,可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。

    第四,在使用分组和排序 子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。

    第五,通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。

    更多相关内容
  • 当我们面对大量的数据查询时,为了提高查询效率,我们在数据库中总要使用到索引。那么索引究竟是怎么样的东西呢? 索引本质 索引其实就是一种数据结构,他将数据库中的数据以一定的数据结构算法进行存储,能够帮助...

    当我们面对大量的数据查询时,为了提高查询效率,我们在数据库中总要使用到索引。那么索引究竟是怎么样的东西呢?

    索引本质

    索引其实就是一种数据结构,他将数据库中的数据以一定的数据结构算法进行存储,能够帮助我们快速的检索数据库中的数据。

    何时使用索引

    1.当某些列的查询概率比较高或经常作为where条件的列。

    2.在作为主键的列上。

    3.在经常用在表连接的列上。

    在这些时候,我们就要考虑使用此列作为一个索引

    索引类型

    1.在Mysql数据库的索引中,主要包括Hash索引和B+ Tree索引,我们常用的InnoDB引擎,默认的是B+树。

    2.Hash索引和B+ Tree索引的区别:

        1)Hash索引底层是哈希表,哈希表是一种以key-value存储数据的结构,所以多个数据在存储关系上是完全没有任何顺序关系的,所以,对于区间查询(大于、小于的范围查询)是无法直接通过索引查询的,就需要全表扫描。所以,哈希索引只适用于等值查询的场景。而B+ Tree是一种多路平衡查询树,所以他的节点是天然有序的(左子节点小于父节点、父节点小于右子节点),所以对于范围查询的时候不需要做全表扫描。

        2)哈希索引没办法利用索引完成排序(例如order by);

        3)哈希索引不支持多列联合索引的最左匹配规则。(多列索引(联合索引)有最左前缀的原则:即最左优先。
    如果我们建立了一个2列的联合索引(col1,col2),实际上已经建立了两个联合索引(col1)、(col1,col2);
    如果有一个3列索引(col1,col2,col3),实际上已经建立了三个联合索引(col1)、(col1,col2)、(col1,col2,col3),查询时,会先查最左的第一个索引col1。)

        4)如果有大量重复键值得情况下,哈希索引的效率会很低,因为存在哈希碰撞问题。

    3.在B+ Tree索引下还分为了聚簇索引和非聚簇索引。在 InnoDB 里,索引B+ Tree的叶子节点存储了整行数据的是主键索引,也被称之为聚簇索引。而索引B+ Tree的叶子节点存储了主键的值的是非主键索引,也被称之为非聚簇索引。聚簇索引查询会更快,因为主键索引树的叶子节点直接就是我们要查询的整行数据了。而非主键索引的叶子节点是主键的值,查到主键的值以后,还需要再通过主键的值再回表进行一次查询。

    4.深入探索问题——什么情况下会发生明明创建了索引,但是执行的时候并没有通过索引呢?

    答:当查询优化器未选择到该索引的执行计划查询优化器: 一条SQL语句的查询,可以有不同的执行方案,至于最终选择哪种方案,需要通过优化器进行选择,选择执行成本最低的方案。 在一条单表查询语句真正执行之前,MySQL的查询优化器会找出执行该语句所有可能使用的方案,对比之后找出成本最低的方案。这个成本最低的方案就是所谓的执行计划。 优化过程大致如下: 1、根据搜索条件,找出所有可能使用的索引 2、计算全表扫描的代价 3、计算使用不同索引执行查询的代价 4、对比各种执行方案的代价,找出成本最低的那一个)

    索引的缺点

    1.创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。
    2.索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。
    3.当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。

    索引是否生效

    1.在数据库新建一个查询,使用 explain 关键字进行查询。

    例如 explain select * from user WHERE company_id=410 

    此时在查询结果中就会发现

    possible_keys列下面的值就是你创建的索引名称,如果为空,则说明你的索引未被使用到。

    展开全文
  • 数据库索引是为了提高查询速度而对表字段附加的一种标识。简单来说,索引其实是一种数据结构。数据库的索引类似于书籍的索引。在书籍中,索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中,索引...

    目录

    一、概述

    二、数据库为什么要设计索引?

    三、哈希(hash)比树(tree)更快,索引结构为什么要设计成树型?

    四、数据库索引为什么使用B+树?

    五、为什么m叉的B+树比二叉搜索树的高度大大降低?

    六、总结


    一、概述

    数据库索引是为了提高查询速度而对表字段附加的一种标识。简单来说,索引其实是一种数据结构。数据库的索引类似于书籍的索引。在书籍中,索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中,索引也允许数据库程序迅速地找到表中的数据,而不必扫描整个数据库。

    首先我们需要明白为什么索引会提高查询速度,数据库在执行一条SQL语句的时候,默认扫描方式是根据搜索条件进行全表扫描,遇到匹配条件的就加入搜索结果集合。如果我们对某一字段增加索引,查询时就会先去索引列表中一次定位到特定值的行数,大大减少遍历匹配的行数,所以数据库索引能明显提高查询的速度。

    下面列举几种适合建立索引的情况:

    1. 经常在where条件中作为查询条件的字段可以建立索引;
    2. 外键关联列可以建立索引;
    3. order by排序后面的字段可以建立索引;
    4. group by分组后的字段可以建立索引;
    5. .......

    当然,并不是所有情况下都适合建立索引,如下几种情况就不太适合建立索引:

    1. 经常增、删、改的字段不适合建立索引,每次执行,索引需重新建立;
    2. 数据过滤性很差的字段不适合建立索引,如性别字段;
    3. 当表数据量过少的时候不太适合建立索引,因为索引占用存储空间;
    4. ..........

    二、数据库为什么要设计索引?

    举个最简单的例子,小时候我们遇到不会读的字,是不是拿出《新华字典》,大家都知道一本《新华字典》里面有成千上万个字,如何快速的找到我们不会读的那个字呢?

    《新华字段》最前面几页列出了我们汉字的笔画索引,如下图:

    通过笔画索引表查询,我们是不是可以大大缩小我们查找的范围了,数据库索引的作用大体相似。假设数据库中存在1000万条数据,如何能够在最短的时间内搜索到满足条件的记录呢,显然可以引入索引来解决。

    三、哈希(hash)比树(tree)更快,索引结构为什么要设计成树型?

    加速查找速度的数据结构,常见的有两类:

    (1)、哈希,例如HashMap,查询/插入/修改/删除的平均时间复杂度都是O(1);

    (2)、树,例如平衡二叉搜索树,查询/插入/修改/删除的平均时间复杂度都是O(lg(n));

    可以看到,不管是读请求,还是写请求,哈希类型的索引,都要比树型的索引更快一些。

    那为什么索引结构要设计成树型呢?

    索引设计成树形,和SQL的需求相关。

    对于单行查询的SQL,如:

    select * from user t where t.user_id = "1356894556";

    因为上述每次查询肯定都只会返回一条记录,所以索引结构使用hash的话,确实会比用树结构快。

    但是,数据库中的查询并不仅仅只有单行查询,还有分组group by、排序oder by 等等。

    遇上如上除了单行查询的情况,如果使用hash索引的话,时间复杂度会退化为O(n),而如果使用树型索引的话,由于树的“有序”特性,依然能够保持O(log(n)) 的高效率,时间复杂度不会退化。

    另外,hash索引可能会存在hash冲突情况。

    四、数据库索引为什么使用B+树?

    数据结构中,树主要有以下几种:

    1. 二叉搜索树;
    2. B树;
    3. B+树;
    • 第一种:二叉搜索树

    二叉搜索树,也是最简单的树结构。主要特征:

    1. 若它的左子树不空,则左子树上所有结点的值均小于它的根结点的值;
    2. 若它的右子树不空,则右子树上所有结点的值均大于它的根结点的值;
    3. 左、右子树也分别为二叉排序树

    那么,二叉搜索树为什么不适合用作数据库索引?

    (1)、当数据量大的时候,树的高度会比较高,数据量大的时候,查询会比较慢;

    (2)、每个节点只存储一个记录,可能导致一次查询有很多次磁盘IO;

    • 第二种:B树

    B树特征:

    1. 树中每个结点最多含有m个孩子(m>=2);
    2. 除根结点和叶子结点外,其它每个结点至少有[ceil(m / 2)]个孩子(其中ceil(x)是一个取上限的函数);
    3. 叶子节点,非叶子节点,都存储数据;
    4. 中序遍历,可以获得所有节点;
    5. 若根结点不是叶子结点,则至少有2个孩子(特殊情况:没有孩子的根结点,即根结点为叶子结点,整棵树只有一个根节点);

    B树为什么适合做索引?

    (1)、B树结构是m分叉的,高度能够大大降低;

    (2)、每个节点可以存储j个记录,如果将节点大小设置为页大小,例如4K,能够充分的利用预读的特性,极大减少磁盘IO;

    • 第三种:B+树

    B+树叶是m叉搜索树,在B树的基础上,做了一些改进:

    1. 非叶子节点不再存储数据,数据只存储在同一层的叶子节点上;
    2. 叶子之间,增加了链表,获取所有节点,不再需要中序遍历;
    3. 叶子节点存储实际记录行,记录行相对比较紧密的存储,适合大数据量磁盘存储;非叶子节点存储记录的PK,用于查询加速,适合内存存储;
    4. 非叶子节点,不存储实际记录,而只存储记录的KEY的话,那么在相同内存的情况下,B+树能够存储更多索引;

    五、为什么m叉的B+树比二叉搜索树的高度大大降低?

    大概计算一下:

    (1)、局部性原理,将一个节点的大小设为一页,一页4K,假设一个KEY有8字节,一个节点大约可以存储500个KEY,即j = 500;(1KB = 1024字节 ,4KB = 4096字节, 4096 / 8 = 512个)

    (2)、m叉B+树,大概m / 2 <= j <= m,即可以差不多是1000叉树;

    (3)、那么:

    一层树:1个节点,1 * 500个KEY,大小4K;

    二层树:1000个节点,1000 * 500 = 50W个KEY,大小1000 * 4K = 4M

    三层树:1000 * 1000个节点,1000 * 1000 * 500 = 5亿个KEY,大小1000 * 1000 * 4K = 4G

    可以看到,存储大量的数据(5亿),并不需要太高树的深度(高度3),索引也不是太占内存(4G)。

    六、总结

    • 数据库索引用于加速查询;
    • 虽然哈希索引是O(1),树索引是O(log(n)),但SQL有很多“有序”需求,故数据库使用树型索引;
    • 数据预读的思路是:磁盘读写并不是按需读取,而是按页预读,一次会读一页的数据,每次加载更多的数据,以便未来减少磁盘IO;
    • 局部性原理:软件设计要尽量遵循“数据读取集中”与“使用到一个数据,大概率会使用其附近的数据”,这样磁盘预读能充分提高磁盘IO;
    • 数据库的索引最常用B+树:

           (1)、很适合磁盘存储,能够充分利用局部性原理,磁盘预读;

      (2)、很低的树高度,能够存储大量数据;

      (3)、索引本身占用的内存很小;

      (4)、能够很好的支持单点查询,范围查询,有序性查询;

    参考文档:https://zhuanlan.zhihu.com/p/118444365

    展开全文
  • 数据库索引设计与优化》提供了一种简单、高效、通用的关系型数据库索引设计方法。作者通过系统的讲解及大量的案例清晰地阐释了关系型数据库的访问路径选择原理,以及表和索引的扫描方式,详尽地讲解了如何快速地...
  • 数据库索引详解

    万次阅读 多人点赞 2021-11-17 19:16:13
    数据库索引,是数据库管理系统中一个排序的数据结构,以协助快速查询,更新数据库中表的数据。索引的实现通常使用B树和变种的B+树(MySQL常用的索引就是B+树)。除了数据之外,数据库系统还维护为满足特定查找算法的...

    1、什么是索引?为什么要用索引?

    1.1、索引的含义
    数据库索引,是数据库管理系统中一个排序的数据结构,以协助快速查询,更新数据库中表的数据。索引的实现通常使用B树和变种的B+树(MySQL常用的索引就是B+树)。除了数据之外,数据库系统还维护为满足特定查找算法的数据结构,这些数据结构以某种方式引用数据,这种数据结构就是索引。简言之,索引就类似于书本,字典的目录。
    1.2、为什么用索引?
    打个比方,如果正确合理设计使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。对于没有索引的表,单表查询可能几十万数据就是瓶颈,二通常大型网站单日就可能产生几十万甚至几百万的数据,没有索引查询会变得非常缓慢。一言以蔽之,合理使用索引,可以加快数据库的查询效率和提升程序性能

    索引的作用与缺点

    2.1、作用

    1. 通过创建索引,可以再查询的过程中,提高系统的性能
    2. 通过创建唯一性索引,可以保持数据库表中每一行数据的唯一性
    3. 在使用分组和排序子句进行数据检索时,可以减少查询中分组和排序的时间
      2.2、缺点
    4. 创建索引和维护索引要耗费时间,而且时间随着数据量的增加而增大
    5. 索引需要占用物理空间,如果要建立聚簇索引,所需要的空间会更大
    6. 在对表中的数据进行增删改时需要耗费较多的时间,因为索引也要动态地维护

    3、索引的使用场景

    3.1、应创建索引的场景

    1. 经常需要搜索的列上
    2. 作为主键的列上
    3. 经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度
    4. 经常需要根据范围进行搜索的列上
    5. 经常需要排序的列上
    6. 经常使用在where子句上面的列上

    3.2、不应该创建索引的场景

    1. 查询中很少用到的列
    2. 对于那些具有很少数据值的列,比如数据表中的性别列,bit数据类型的列
    3. 对于那些定义为text,image的列,因为这些列的数据量相当大
    4. 当对修改性能的要求远远大于搜索性能时,因为当增加索引时,会提高搜索性能,但是会降低修改性能

    4、索引的分类与说明

    4.1、主键索引
    设定为主键后数据库会自动建立索引,innodb为聚簇索引

    #随表一起建索引:
    CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
      PRIMARY KEY(id) 
    );
    #使用AUTO_INCREMENT关键字的列必须有索引(只要有索引就行)。
    CREATE TABLE customer2 (id INT(10) UNSIGNED,customer_no VARCHAR(200),customer_name VARCHAR(200),
      PRIMARY KEY(id) 
    );
    #单独建主键索引:
    ALTER TABLE customer add PRIMARY KEY customer(customer_no);  
    #删除建主键索引:
    ALTER TABLE customer drop PRIMARY KEY ;  
    #修改建主键索引:
    #必须先删除掉(drop)原索引,再新建(add)索引
    

    4.2、单列索引
    一个索引只包含单个列,一个表可以有多个单列索引

    #随表一起建索引:
    CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
      PRIMARY KEY(id),
      KEY (customer_name)  
    );
    #随表一起建立的索引 索引名同 列名(customer_name)
    #单独建单值索引:
    CREATE INDEX idx_customer_name ON customer(customer_name); 
    #删除索引:
    DROP INDEX idx_customer_name ;
    

    4.3、唯一索引
    索引列的值必须唯一,但允许有空值

    #随表一起建索引:
    CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
      PRIMARY KEY(id),
      KEY (customer_name),
      UNIQUE (customer_no)
    );
    #建立 唯一索引时必须保证所有的值是唯一的(除了null),若有重复数据,会报错。   
    #单独建唯一索引:
    CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no); 
    #删除索引:
    DROP INDEX idx_customer_no on customer ;
    

    4.4、复合索引
    一个索引包含多个列,在数据库操作期间,复合索引比单值索引所需要的开销更小(对于相同的多个列建索引)
    如果一个表中的数据在查询时有多个字段总是同时出现则这些字段就可以作为复合索引,形成索引覆盖可以提高查询的效率!

    #随表一起建索引:
    CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
      PRIMARY KEY(id),
      KEY (customer_name),
      UNIQUE (customer_name),
      KEY (customer_no,customer_name)
    );
    #单独建索引:
    CREATE INDEX idx_no_name ON customer(customer_no,customer_name); 
    #删除索引:
    DROP INDEX idx_no_name  on customer ;
    

    4.5、聚集索引与非聚集索引
    4.5.1、聚集索引
    聚集索引:指索引项的排序方式和表中数据记录排序方式一致的索引。它会根据聚集索引键的顺序来存储表中的数据,即对表的数据按索引键的顺序进行排序,然后重新存储到磁盘上。因为数据在物理存放只能有一种排列方式,所以一个表只能有一个聚集索引。比如字典中,用拼音查汉字,就是聚集索引。因为正文中字都是按照拼音排序的。而用偏旁部首查汉字,就是非聚集索引,因为正文中的字并不是按照偏旁部首排序的,我们通过检字表得到正文中的字在索引中的映射,然后通过映射找到所需要的字。

    聚集索引的使用场合为:
    查询命令的回传结果是以该字段为排序依据的;
    查询的结果返回一个区间的值;
    查询的结果返回某值相同的大量结果集

    聚集索引会降低insert,和update操作的性能,所以,是否使用聚集索引要全面衡量。

    4.5.2、非聚集索引
    非聚集索引:与聚集索引相反,索引顺序与物理存储顺序不一致
    非聚集索引的使用场合为:
    查询所获数据量较少时;
    某字段中的数据的唯一性比较高时;

    非聚集索引必须是稠密索引

    4.5.3、使用及语法

    create [unique] [clustered] [nonclustered] index index_name  on {tabel/view} (column[dese/asc][....n])
    

    住:[ unique ] [clu stered] [nonclustered]表示要创建索引的类型,以此为唯一索引,当省略unique选项时,建立非唯一索引,当省略clustered,nonclustered选项时。建立聚集索引,省略nonclusterrd选项时,建立唯一聚集索引。

    4.5.4、使用场景对比

    动作描述使用聚集索引使用非聚集索引
    列经常被分组排序使用使用
    返回某范围内的数据使用不使用
    一个或极少不同值不使用不使用
    小数目的不同值使用不使用
    大数目的不同值不使用使用
    频繁更新的列不使用使用
    外键列使用使用
    主键列使用使用
    频繁修改索引列不使用使用

    4.6聚簇索引与非聚簇索引
    4.6.1、聚簇索引
    聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。将数据存储于索引放到了一块,索引结构的叶子节点保存了行数据。

    聚簇索引的特点:
    1、聚簇索引具有唯一性,由于聚簇索引是将数据跟索引结构放到一块,因此一个表仅有一个聚簇索引。
    2、表中行的物理顺序和索引中行的物理顺序是相同的,在创建任何非聚簇索引之前创建聚簇索引,这是因为聚簇索引改变了行的物理顺序,数据行,按照一定的顺序排列,并且自动维护这个顺序;
    3、聚簇索引默认是主键,如果表中没有定义主键,InnoDB会选择一个唯一且非空的索引代替。如果没有这样的索引,InnoDB会隐式定义一个主键(类似oracle中的Rowld)来作为聚簇索引。如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引,必须先删除主键,然后再添加我们想要的聚簇索引,随后恢复设置主键即可。

    4.6.2、非聚簇索引
    不是聚簇索引的二级索引,也叫作辅助索引,都称为非聚簇索引。将数据与索引分开存储,索引结构的叶子节点指向了数据对应的位置。

    4.6.3、MySQL的MyIsam和InnoDB存储引擎
    因为这两种引擎对非聚簇索引和聚簇索引的使用,就是他们之间很大的一个区别。所以结合这两个引擎,再对这两种索引展开些描述就更明了了。

    在InnoDB中,在聚簇索引之上创建的索引称之为 索引,非聚簇索引都是辅助索引,像复合索引,前缀索引,唯一索引。辅助索引叶子节点存储的不再是行的物理位置,而是主键值,辅助索引访问数据总是需要二次查找。
    在这里插入图片描述
    1、InnoDB使用的是聚簇索引,将主键组织到一棵B+树中,而行数据就储存在叶子节点上,若使用“where id = 14”这样的条件查找主键,则按照B+树的检索算法即可查找到对应的叶节点,之后获得行数据。
    2、若对Name列进行条件搜索,则需要两个步骤:第一步在辅助索引B+树中检索Name,到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树中再执行一次B+树检索操作,最终到达叶子节点即可获取整行数据。(重点在于通过其他键需要建立辅助索引)

    MyIsam使用的是非聚簇索引,非聚簇索引的两颗B+树看上去没什么不同,节点的结构完全一致只是存储的内容不同而已,主键索引B+树的节点存储了主键,辅助索引B+树存储了辅助键。表数据存储在独立的地方,这两棵B+树的叶子节点都使用一个地址指向真正的表数据,对于表数据来说,这两个键没有任何差别。由于索引树是独立的,通过辅助键索引无需访问主键的索引树。
    在这里插入图片描述
    4.6.4、对比总结
    每次使用辅助索引检索都要经过两次B+熟查找,看上去聚簇索引的效率明显低于非聚簇索引,这不是多此一举吗?聚簇索引的优势在哪里?
    1、由于行数据和聚簇索引的叶子节点存储在一起,同一页中会有多条行数据,访问同一数据页不同行记录时,已经把页加载到了Buffer中(缓存器),再次访问时,会在内存中完成访问,不必访问磁盘,这样主键和行数据是一起被载入内存的,找到叶子节点就可以立即将行数据返回了,如果按照主键ID来组织数据,获得数据更快。
    2、辅助索引的叶子节点,存储主键值,而不是数据的存放地址。好处是当行数据发生改变时,索引树的节点也需要分裂变化;或者是我们需要查找的数据,在上一次IO读写的缓存中没有,需要发生一次新的IO操作时,可以避免对辅助索引的维护工作,只需要维护聚簇索引树就好了,另一个好处是,因为辅助索引存放的是主键值,减少了辅助索引占用的存储空间大小。
    注:我们知道一次IO读写,可以获取到16K大小的资源,我们称之为读取到的数据区域为Page。而我们的B树,B+树的索引结构,叶子节点上存放好多个关键字(索引值)和对应的数据,都会在一次IO操作中被读取到缓存中,所以在访问同一个页中的不同记录时,会在内存里操作,而不用再次进行IO操作了。除非发生了页的分裂,即要查询的行数据不在上次IO操作的换衬里才会触发新的IO操作。
    3、因为MyIsam的主索引并非聚簇索引,那么他的数据的物理地址必定是凌乱的,拿到这些物理地址,按照合适的算法进行I/O读取,于是开始不停的寻道不停的旋转。聚簇索引则只需一次I/O。(强烈的对比)
    4、不过,如果涉及到大数据的排序,全表扫描,count之类的操作的话,还是MyIsam占优势些,因为索引所占空间小,这些操作是需要在内存中完成的。
    5、当使用主键为聚簇索引时,主键最好不要使用uuid,因为uuid的值太过离散,不适合排序且可能出现新增加记录的uuid,会插入在索引树中间的位置,导致索引树调整复杂度变大,消耗更多的时间和资源。建议使用int类型的自增,方便排序并且默认会在索引树的末尾增加主键值,对索引树的结构影响最小。而且,主键值占用的存储空间越大,辅助索引中保存的主键值也会跟着变大,占用存储空间,也会影响到IO操作读取到的数据量。

    4.7、稠密索引与稀疏索引
    在了解稠密索引和稀疏索引之前我们先来了解一下什么是聚焦索引。在一个文件中,可以有多个索引,分别基于不同的索引码。如果包含数据记录的文件按照某个指定的顺序排列,那么该搜索码对应的索引就是聚焦索引。

    4.7.1、稠密索引
    在稠密索引中,文件中的每个搜索码值都对应一个索引值,也就是说,稠密索引为数据记录文件的每一条记录都设一个键-指针对。如下图所示,索引项包括索引值以及指向搜索码的第一条数据记录的指针,即我们所说的键-指针对。
    在这里插入图片描述
    4.7.2、稀疏索引
    在稀疏索引中,只为搜索码的某些值建立索引项,也就是说,系数索引为数据记录文件的每个存储块设一个键-指针对,存储块意味着块内存存储单元连续,如下图所示:
    在这里插入图片描述

    索引的底层原理

    抛开其他的数据库索引实现,主讲MySQL的索引底层实现,其底层是通过B+树来实现的数据结构存储。
    数据结构存储,决定了数据查找和操作时的效率,包括时间复杂度和空间复杂度,而在取舍的时候,也无非就是时间换空间,空间换时间的权衡罢了,所以,这就很好的解释了,为什么MySQL在索引的底层设计上,选用了B+树,而没有选用B-树,或是红黑树,AVL树等等其他数据结构。总之,就是使用B+树作为索引的结构存储,能在I/O性能上得到一个较大的优势。

    那么具体优势在哪里呢?以B-树与B+树的对比,来阐述具体差异和B+树的优势。

    5.1、B-Tree
    B-树是一种多路自平衡的搜索树,它类似普通的平衡二叉树,不同的一点是B-树允许每个节点有更多的子节点。B-Tree相对于AVLTree缩减了节点个数,使每次磁盘I/O取到内存的数据都发挥了作用,从而提高了查询效率。
    注:B-Tree就是我们常说的B树
    那么m阶B-Tree是满足下列条件的数据结构:
    所有键值分布在整棵树中
    搜索有可能在非叶子节点结束,在关键字全集内做一次查找,性能逼近二分查找
    每个节点最多拥有m个子树
    根节点至少有2个子树
    分支节点至少拥有m/2颗子树(除根节点和叶子节点外都是分支节点)
    所有叶子节点都在同一层,每个节点最多可以有m-1个key,并且以升序排列
    在这里插入图片描述
    每个节点占用一个磁盘块,一个节点上有两个升序排序的关键字和三个指向子树根节点的指针,指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例,关键字为17和35,P1指针指向的子树的数据范围小于17,P2指针指向的子树的数据范围为17~35,P3指针指向的子树的数据范围大于35.

    模拟查找关键字29的过程:
    1、根据根节点找到磁盘块1,读入内存。【磁盘I/O操作第1次】
    2、比较关键字29在区间(17,35),找到磁盘块1的指针P2。
    3、根据P2指针找到磁盘块3,读入内存。【磁盘I/O操作第2次】
    4、比较关键字29在区间(26,30),找到磁盘块3的指针P2。
    5、根据P2指针找到磁盘块8,读入内存。【磁盘I/O操作第3次】
    6、在磁盘块8中的关键字列表中找到关键字29。
    7、分析上面过程,发现需要3次磁盘I/O操作,和3次内存查找操作。由于内存中的关键字是一个有序表结构,可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。

    但同时B-Tree也存在问题:
    每个节点中有key,也有data,而每一个页的存储空间是有限的,如果data数据较大时将会导致每个节点(即一个页)能存储的key的数量很小。
    当存储的数据量很大时同样会导致B-Tree的深度较大,增大查询时的磁盘I/O次数,进而影响查询效率

    5.2、B+Tree
    B+Tree是在B-Tree基础上的一种优化,InnoDB存储引擎就是用B+Tree实现其索引结构。它带来的变化点:
    B+树每个节点可以包含更多的节点,这样做有两个原因,一个是降低树的高度。另外一个是将数据范围变为多个区间,区间越多,数据检索越快
    非叶子节点存储key,叶子节点存储key和数据
    叶子节点两两指针相互链接(符合磁盘的预读特性),顺序查询性能更高

    注:MySQL的InnoDB存储引擎在设计时是将根节点常驻内存,因此力求达到树的深度不超过3,也就是说I/O不需要超过3次。
    在这里插入图片描述
    通常在B+Tree上有两个头指针,一个指向根节点,另一个指向关键字最小的叶子节点,而且所有叶子节点(即数据节点)之间是一种链式环结构,因此可以对B+Tree进行两种查找运算:一种是对于主键的范围查找的分页查找,另一种是从根节点开始,进行随机查找。

    5.3、B-树和B+树的区别
    B+树内节点不存储数据,所有数据存储在叶节点导致查询时间复杂度固定为log n
    B-树查询时间复杂度不固定,与Key在树中的位置有关,最好为O(1)
    B+树叶节点两两相连可大大增加区间访问性,可使用在范围查询等
    B+树更适合外部存储(存储磁盘数据)。由于内节点无data域,每个节点能索引的范围更大更精确。

    展开全文
  • 数据库索引设计与优化数据库索引设计与优化数据库索引设计与优化数据库索引设计与优化数据库索引设计与优化数据库索引设计与优化数据库索引设计与优化数据库索引设计与优化数据库索引设计与优化数据库索引设计与优化...
  • 什么是数据库索引

    千次阅读 2021-05-18 20:06:46
    BTREE 索引 ; HASH 索引 ; R-tree 索引(空间索引);Full-text (全文索引);BTREE树的特点 ; B+TREE树 ; 索引分类 ; 索引的用法 ; 如何去很高效的去使用索引
  • Android数据库高手秘籍(十二),LitePal的索引功能

    万次阅读 多人点赞 2020-08-11 08:13:46
    我发现今年我的技术产出真的是很不错,自从《第一行代码 第3版》出版之后,我空余出来了大量的时间,不仅频繁地更新和...回到今天的主题,LitePal自上次3.1版本支持了事务之后,基本数据库该有的功能差不多都具备了,
  • 数据库索引 索引是对数据库表中的一列或多列值进行排序的一种结构,使用索引可以快速访问数据库表中的特定信息。 索引类型 (1)普通索引 ◆创建表的时候直接指定 CREATE TABLE mytable( ID INT NOT NULL, ...
  • 数据库索引实现原理

    万次阅读 多人点赞 2019-04-15 16:28:54
    MySQL索引实现 在MySQL中,索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的,本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。 MyISAM索引实现 MyISAM引擎使用B+Tree作为索引结构,叶...
  • 数据库索引: 索引(index)是帮助MySQL高效获取数据的数据结构(有效),在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据, 这样就可以在这些数据结构上实现...
  • 数据库索引原理实例

    千次阅读 2021-01-17 10:52:00
    --未使用索引,不存在to_char(d1,'yyyymmdd’)的函数索引 解决办法:改为d1 between to_date('20200102 00:00:00','yyyymmdd hh24:mi:ss') and to_date('20200102 23:59:59','yyyymmdd hh24:mi:ss') 也可以执行create...
  • 主流数据库索引及其例子

    千次阅读 多人点赞 2022-04-10 22:36:25
    数据库中使用索引的优缺点: 索引分类 普通索引 唯─性索引 主键索引 全文索引 空间索引 其他分类 索引设置的基本原则 创建索引 使用CREATE INDEX语句建立索引 创建表时创建索引 修改表时创建索引 ...
  • 数据库索引

    万次阅读 多人点赞 2018-11-11 09:27:25
    数据库索引,是数据库管理系统中一个排序的数据结构,以协助快速查询,更新数据库中表的数据.索引的实现通常使用B树和变种的B+树(mysql常用的索引就是B+树) 除了数据之外,数据库系统还维护为满足特定查找算法的数据...
  • 数据库索引设计(基础篇)

    千次阅读 2020-10-08 09:05:06
    点击蓝色“有关SQL”关注我哟加个“星标”,天天与10000人一起快乐成长索引数据库中,毋庸置疑扮演了极其重要的角色。在这篇文章中,我们即将要讨论这些和索引相关的事情:优化器是如何选择...
  • 数据库索引作用优点和缺点

    万次阅读 多人点赞 2016-09-13 17:10:04
    第一,通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。 第二,可以大大加快 数据的检索速度,这也是创建索引的最主要的原因。 第三,可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别...
  • SQL Server 数据库索引

    千次阅读 2022-03-20 18:00:13
    MySQL 数据库索引1. 索引介绍2. 索引的概述2.1 索引2.2 索引的特点1. 使用索引能提升数据库的性能,主要体现在以下几个方面:2. 在提升数据性能的同时,索引有一些负面影响2.3 设计索引的注意事项3. 索引的类型3.1...
  • MySQL数据库索引

    万次阅读 多人点赞 2018-09-23 09:31:41
    数据库有哪些索引 唯一索引 聚簇索引与非聚簇索引 全文索引 使用索引一定能提高查询性能吗? 哪些情况下设置了索引但是无法使用 哪些情况下需要设置索引、哪些情况下不需要 什么情况下应该使用组合索引而非...
  • 数据库索引原理及优化

    万次阅读 多人点赞 2018-08-07 11:03:02
    本文以MySQL数据库为研究对象,讨论与数据库索引相关的一些话题。特别需要说明的是,MySQL支持诸多存储引擎,而各种存储引擎对索引的支持也各不相同,因此MySQL数据库支持多种索引类型,如BTree索引,哈希索引,全文...
  • 数据库索引的创建和使用

    千次阅读 2020-05-20 17:17:26
    文章目录数据库索引创建索引使用场景 数据库索引 数据库的索引可以加快查询速度,原因是索引使用特定的数据结构(B-Tree)对特定的列额外组织存放,加快存储引擎(索引是存储引擎实现)查找记录的速度。 索引优化是数据库...
  • MySQL数据库索引查询

    千次阅读 2022-05-03 13:30:53
    文章目录MySQL数据库索引查询1. 创建数据库和表2. 使用alter table 语句在w_name字段上建立名为nameidx的普通索引.3. 使用create index语句在w_address和w_age字段上建立名为multiidx的组合索引.4. 使用CREATE index...
  • 创建数据库索引的几种方法

    千次阅读 2020-06-20 10:34:31
    1、普通索引 CREATE INDEX indexName ON mytable(username(length)); 创建表的时候直接指定: CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) ); ...
  • 使用索引查询某行数据,但数据库扫描全表进行查询时 叫索引失效; 索引失效的几种方式: 1、where中存在 or 2、类型为char,查询条件时用int 3、模糊查询时,%开头的查询 4、not in 5、where索引列有运算 6、...
  • 数据库专题】如何理解数据库索引

    千次阅读 多人点赞 2022-06-06 01:04:20
    索引是帮助数据库来**高效获取数据**的一种**排好顺序**的数据结构;
  • 数据库索引及其数据结构

    千次阅读 2018-04-08 20:26:04
    数据库索引,是数据库管理系统中一个排序的数据结构,以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种...
  • mysql数据库索引实现原理

    千次阅读 2019-06-01 21:27:11
    在介绍索引实现之前,我们先来了解下几种树的数据结构。 二叉搜索树 二叉搜索树有以下性质 1.每个节点有一个关键字 2.左右孩子至多有一个。 3.关键字大于左孩子,小于右孩子。 正因为二叉搜索树的特性,所以...
  • 索引分为主键索引、唯一索引、普通索引、聚集索引、全文索引几种,而索引其实就是在无序的数据中建立索引,每次查询可以根据索引迅速查到我们想要的数据(就像字典的目录a-z一样) 优点 提高数据查找速度 提高...
  • MySQL 如何查看表和数据库索引

    千次阅读 2020-10-13 14:25:52
    2.2、查询某个数据库(table_schema)的全部表索引,可以从INFORMATION_SCHEMA架构中的STATISTICS表中获取索引信息 3、查看一台主机所有数据库的所有索引信息,则可以通过INFORMATION_SCHEMA查看​ 4、获取指定...
  • 使用索引也很简单,然而, 会使用索引是一回事, 而深入理解索引原理又能恰到好处使用索引又是另一回事。 这已经是两个相差甚远的技术层级了。 二、千万级数据表索引和无索引查询效率对比 现在有一个学生表student,...
  • 数据库索引总结.xmind

    2019-10-23 22:23:50
    数据库索引总结,索引的作用?索引的注意事项?数据库索引的结构?

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 943,036
精华内容 377,214
关键字:

数据库索引的作用