精华内容
下载资源
问答
  • 数据库索引存储结构

    千次阅读 2018-11-08 17:16:08
    数据库索引存储结构 主键索引(PRIMAY KEY):  一个表中只能有一个主键,创建主键自动创建主键索引,该索引是唯一索引,其主键列的数据值不重复。建议使用INT型的自动增长主键,这样索引效率最高。 ...
    数据库索引存储结构

    主键索引(PRIMAY KEY):

           一个表中只能有一个主键,创建主键自动创建主键索引,该索引是唯一索引,其主键列的数据值不重复。建议使用INT型的自动增长主键,这样索引效率最高。

    唯一索引(UNIQUE):

           不允许索引列的值相同。系统在创建该索引时检查是否有重复的键值,并在每次使用INSERT或UPDATE语句添加数据时进行检查。索引结果再根据主键索引查询数据。

    复合索引(INDEX):

           多个列建立索引,复合索引在数据库操作期间所需的开销更小,可以代替多个单一索引;第一个字段作为条件时才能保证系统使用该索引。建议不超过2个字段的复合索引。

     

    B-Tree的存储结构(例如100条数据):

           在B-Tree中按key检索数据的算法非常直观:首先从根节点进行二分查找,如果找到则返回对应节点的data,否则对相应区间的指针指向的节点递归进行查找,直到找到节点或找到null指针,前者查找成功,后者查找失败。每个域的大小都相同。

     

     

    B+Tree的存储结构(例如100条数据):

           

           在B+Tree中key检索数据算法如同目录:首先从根节点进行二分查找,从左(小)边到右(大)匹配,找到key匹配成功的索引值,根据索引值对应的节点地址,进入节点地址key继续进行匹配,直到进入叶节点,再根据key获取数据。

           在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针,就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能。

           注意:每个索引的最小值,也就是子索引的最小值,依次类推,最后的子索引的最小值,也就是该数据页的key索引的最小值。最小值都是靠最左边排列。都是节点开始的第一个值。

           注意:叶节点的数据页存有邻近的数据页节点地址,可以通过数据页的节点直接进入邻近数据页,查询需要的主键索引值。 

    索引性能影响:

    • 插入:按顺序插入在尾部,对索引影响微乎其微,索引不用重新排列顺序,只需要添加顺序的记录即可。如果是中间插入,则影响索引顺序,索引需要重写排列顺序,导致性能下降。
    • 删除:尾部删除,对索引影响甚微,索引不用重新排列顺序,只要去除顺序尾部的记录即可。如果中间删除,则影响索引顺序,索引需要重写排列顺序。导致性能下降。
    • 更新:更新字段不包含索引列,性能不会降低。如果更新索引列,索引目录表将重写排列索引顺序,导致性能下降。

     

    索引规则:

    建立索引规则:

    • 数据量超过300的,经常与其他表进行连接的表,在连接字段上应该建立索引;表的主键、外键必须有索引;经常出现在Where子句中的字段,特别是大表的字段,应该建立索引;
    • 索引应该建在选择性高的字段类型的小字段上,比如INT类型字段;
    • 尽量不要对数据库中某个含有大量重复的值的字段建立索引。

    复合索引规则:

    • 复合索引的建立需要进行仔细分析,尽量考虑用单字段索引代替;如果需要复合索引,正确选择复合索引中的主列字段,一般是选择性高的字段类型的小字段上;
    • 复合索引的几个字段是否经常同时以AND方式出现在Where子句中?单字段查询是否极少甚至没有?如果是,则可以建立复合索引;否则考虑单字段索引;
    • 如果复合索引中包含的字段经常单独出现在Where子句中,则分解为多个单字段索引;
    • 如果复合索引所包含的字段超过3个,那么仔细考虑其必要性,考虑减少复合的字段;复合索引最好只使用2个字段,索引本身也有I/O、内存、存储开销。复合索引字段越少越好。
    • 如果既有单字段索引,又有这几个字段上的复合索引,一般可以删除复合索引;

    索引操作影响:

    • 频繁进行数据操作的表,不要建立太多的索引;删除无用的索引,避免对执行计划造成负面影响;
    • 表上建立的每个索引都会增加存储开销,索引对于插入、删除、更新操作也会增加处理上的开销。另外,过多的复合索引,在有单字段索引的情况下,一般都是没有存在价值的;相反,还会降低数据增加删除时的性能,特别是对频繁更新的表来说,负面影响更大。
    展开全文
  • 数据库索引

    千次阅读 多人点赞 2019-08-20 22:49:54
    数据库索引 文章目录数据库索引定义优缺点索引类型建立普通索引或组合索引适合建立索引的情况索引失效的sql 定义 索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息...

    数据库索引

    定义

    索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。数据库索引好比是一本书前面的目录,能加快数据库的查询速度。索引分为聚簇索引和非聚簇索引两种,聚簇索引是按照数据存放的物理位置为顺序的,而非聚簇索引就不一样了;聚簇索引能提高多行检索的速度,而非聚簇索引对于单行的检索很快。

    本人对于索引理解较浅,如有错误或不足的地方,欢迎指正,共同学习。

    优缺点

    优点

    • 在设计数据库时,通过创建一个惟一的索引,能够在索引和信息之间形成一对一的映射式的对应关系,增加数据的惟一性特点。

    • 能提高数据的搜索及检索速度,符合数据库建立的初衷。

    • 能够加快表与表之间的连接速度,这对于提高数据的参考完整性方面具有重要作用。

    • 在信息检索过程中,若使用分组及排序子句进行时,通过建立索引能有效的减少检索过程中所需的分组及排序时间,提高检索效率。

    • 建立索引之后,在信息查询过程中可以使用优化隐藏器,这对于提高整个信息检索系统的性能具有重要意义。

      缺点

    • 在数据库建立过程中,需花费较多的时间去建立并维护索引,特别是随着数据总量的增加,所花费的时间将不断递增。

    • 在数据库中创建的索引需要占用一定的物理存储空间,这其中就包括数据表所占的数据空间以及所创建的每一个索引所占用的物理空间,如果有必要建立起聚簇索引,所占用的空间还将进一步的增加

    • 在对表中的数据进行修改时,例如对其进行增加、删除或者是修改操作时,索引还需要进行动态的维护,这给数据库的维护速度带来了一定的麻烦。

    索引类型

    根据数据库的功能,可以在数据库设计器中创建三种索引唯一索引、主键索引和聚集索引。有关数据库所支持的索引功能的详细信息,请参见数据库文档。

    提示:尽管唯一索引有助于定位信息,但为获得最佳性能结果,建议改用主键唯一约束

    唯一索引

    UNIQUE
    create unique index 索引名 on 表名(表中的列[(length)])
    alter table 表名 add UNIQUE 索引名 (表中的列[(length)])
    

    唯一索引是不允许其中任何两行具有相同索引值的索引。当现有数据中存在重复的键值时,大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如,如果在employee表中职员的姓(lname)上创建了唯一索引,则任何两个员工都不能同姓。

    主键索引

    PRIMARY KEY  -- 建表时自接指定
    alert table 表名 add primary key (表中的列[(length)])
    

    数据库表经常有一列或多列组合,其值唯一标识表中的每一行。该列称为表的主键。在数据库关系图中为表定义主键将自动创建主键索引,主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时,它还允许对数据的快速访问。

    聚集索引

    在聚集索引中,表中行的物理顺序与键值的逻辑(索引)顺序相同。一个表只能包含一个聚集索引。如果某索引不是聚集索引,则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比,聚集索引通常提供更快的数据访问速度。聚集索引和非聚集索引的区别,如字典默认按字母顺序排序(物理顺序),读者如知道某个字的读音可根据字母顺序快速定位(索引顺序与物理顺序相同)。因此聚集索引和表的内容是在一起的。如读者需查询某个生僻字,则需按字典前面的索引,举例按偏旁进行定位,找到该字对应的页数,再打开对应页数找到该字。这种通过两个地方而查询到某个字的方式就如非聚集索引。

    建立普通索引或组合索引

    create index 索引名 on 表名(列名);
    create index 索引名 on 表名(列名1,列名2,..); -- 组合索引
    

    组合索引 最左前缀 原则

    个人理解 例如 : create index index_abc on TEST_TAB(a,b,c); 相当于建立了 a, ab, abc 三组索引。

    只要某查询条件中包含复合索引中的第一个列,该查询就会走索引,如果不包含,就不会走索引

    有博主的实验 https://blog.csdn.net/tw7752/article/details/44595281

    适合建立索引的情况

    • 经常作为查询条件的列 (where)
    • 经常用于表连接的列,如外键 (join)
    • 经常用于排序的列 (order)

    索引失效的sql

    • 组合索引使用 or 索引失效 如 a=1 or b=2 or c=3

    • 索引条件为 is null / is not null 索引失效(看清况,测试过 null 比较多时, not null 会走索引)

    • 索引条件 like ‘%xxx’, 索引失效; like ‘xxxx%’ 索引生效

    • 索引列参加计算 如 t.score/10 > 10 失效, 应改成 t.score > 10*10

    • 索引列不要使用NOT ( != 、 <> )如 t.score! = 10 失效,改成:t.score > 10 or t.score < 10

    • 索引列上发生类型转换, 例如 VARCHAR2 类型的索引列 ,写成 where id = 1 ,应该 改成 where id = ‘1’ ( oracle实验)

    最好是 看一下sql的执行计划,看看是否走了索引。

    Oracle查看sql执行计划

    explain plan for  
    select xxx from tablename where xxx ;
    
    select * from table(dbms_xplan.display); 
    

    索引的原理 请看大神博客 https://blog.csdn.net/sinat_30186009/article/details/52169057

    组合索引设计 https://blog.csdn.net/bless2015/article/details/84035845

    展开全文
  • 数据库索引和分类

    2020-12-14 16:19:04
    什么是数据库索引? 我们再平时的开发中免不了用到数据库的索引,接下来就简单说一下数据库索引数据库索引用来干什么? 数据库索引就是为了提高数据的查询速率。 数据库索引有哪些? 聚集索引:数据库中,所有...
  • 数据库索引存储过程,视图,事务 数据库索引存储过程,视图,事务数据库索引存储过程,视图,事务
  • 1.什么是索引?为什么要用索引? 1.1索引的含义 1.2为什么用? 2.索引的作用与缺点 2.1作用 2.2缺点 3.索引的使用场景 3.1应创建索引的场景 3.2不应创建索引的场景 4.索引的分类与说明 4.1主键索引 ...

    热门系列:


    目录

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

       1.1索引的含义

       1.2为什么用?

    2.索引的作用与缺点

       2.1作用

       2.2缺点

    3.索引的使用场景

      3.1应创建索引的场景

      3.2不应创建索引的场景

    4.索引的分类与说明

      4.1主键索引

      4.2单列索引

      4.3唯一索引

      4.4复合索引

      4.5聚集索引与非聚集索引

         4.5.1聚集索引

         4.5.2非聚集索引

         4.5.3使用及语法

         4.5.4使用场景对比

      4.6聚簇索引与非聚簇索引

         4.6.1聚簇索引

         4.6.2非聚簇索引

         4.6.3Mysql的MYISAM和INNODB引擎

         4.6.4对比总结

     4.7稠密索引与稀疏索引

         4.7.1稠密索引

         4.7.2稀疏索引

    5.索引的底层原理

         5.1 B-Tree

         5.2 B+Tree

         5.3 B-树和B+树的区别

    6.总结


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

      1.1索引的含义

    数据库索引,是数据库管理系统中一个排序的数据结构,以协助快速查询,更新数据库中表的数据.索引的实现通常使用B树和变种的B+树(mysql常用的索引就是B+树)。除了数据之外,数据库系统还维护为满足特定查找算法的数据结构,这些数据结构以某种方式引用数据.这种数据结构就是索引!

    简言之,索引就类似于书本,字典的目录!

      1.2为什么用?

    打个比方,如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。对于没有索引的表,单表查询可能几十万数据就是瓶颈,而通常大型网站单日就可能会产生几十万甚至几百万的数据,没有索引查询会变的非常缓慢。

    一言以蔽之,合理使用索引,可以加快数据库的查询效率和提升程序性能!


    2.索引的作用与缺点

      2.1作用

       ①通过创建索引,可以在查询的过程中,提高系统的性能

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

       ③在使用分组和排序子句进行数据检索时,可以减少查询中分组和排序的时间

      2.2缺点

       ①创建索引和维护索引要耗费时间,而且时间随着数据量的增加而增大

       ②索引需要占用物理空间,如果要建立聚簇索引,所需要的空间会更大

       ③在对表中的数据进行增加删除和修改时需要耗费较多的时间,因为索引也要动态地维护


    3.索引的使用场景

      3.1应创建索引的场景

       ①经常需要搜索的列上

       ②作为主键的列上

       ③经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度

       ④经常需要根据范围进行搜索的列上

       ⑤经常需要排序的列上

       ⑥经常使用在where子句上面的列上

     

      3.2不应创建索引的场景

       ①查询中很少用到的列

       ②对于那些具有很少数据值的列.比如数据表中的性别列,bit数据类型的列

       ③对于那些定义为text,image的列.因为这些列的数据量相当大

       ④当对修改性能的要求远远大于搜索性能时.因为当增加索引时,会提高搜索性能,但是会降低修改性能


    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聚集索引

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

    聚集索引的使用场合为: 

    • a.查询命令的回传结果是以该字段为排序依据的; 
    • b.查询的结果返回一个区间的值; 
    • c.查询的结果返回某值相同的大量结果集。 

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

     

         4.5.2非聚集索引

    非聚集索引:与聚集索引相反, 索引顺序与物理存储顺序不一致。

    非聚集索引的使用场合为: 

    • a.查询所获数据量较少时; 
    • b.某字段中的数据的唯一性比较高时;

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

     

        4.5.3使用及语法

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

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

     

        4.5.4使用场景对比

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

      4.6聚簇索引与非聚簇索引

        4.6.1聚簇索引

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

    聚簇索引的特点:

    聚簇索引具有唯一性,由于聚簇索引是将数据跟索引结构放到一块,因此一个表仅有一个聚簇索引。

    表中行的物理顺序和索引中行的物理顺序是相同的在创建任何非聚簇索引之前创建聚簇索引,这是因为聚簇索引改变了表中行的物理顺序,数据行 按照一定的顺序排列,并且自动维护这个顺序;

    聚簇索引默认是主键,如果表中没有定义主键,InnoDB 会选择一个唯一且非空的索引代替。如果没有这样的索引,InnoDB 会隐式定义一个主键(类似oracle中的RowId)来作为聚簇索引。如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引,必须先删除主键,然后添加我们想要的聚簇索引,最后恢复设置主键即可。

     

        4.6.2非聚簇索引

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

     

        4.6.3Mysql的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稀疏索引

    在稀疏索引中,只为搜索码的某些值建立索引项。也就是说,稀疏索引为数据记录文件的每个存储块设一个键-指针对,存储块意味着块内存储单元连续。如下图所示。


    5.索引的底层原理

    此节我们抛开其他的数据库索引实现,主讲Mysql的索引底层实现。说到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 域,每个节点能索引的范围更大更精确。
       

    6.总结

        本章内容其实是我个人对索引这块的知识点的巩固与梳理。之前有很多总结,比较零散,趁当下有点时间,所以整理了出来。其中还有很多不足,或是有瑕疵的地方,若有不对之处,尽情拍砖指正。最后,也希望能够帮助到,正在学习的你,加油!

     

    本博客皆为学习、分享、探讨为本,欢迎各位朋友评论、点赞、收藏、关注,一起加油!

     

    展开全文
  • MySQL数据库索引

    2021-05-29 21:34:42
    MySQL数据库索引 前言 前面的文章讲解数据库的基本操作,本文开始讲解数据库更进一步的知识,本文讲解数据库中的索引,包括索引的概念、索引的优劣性、索引结构、索引分类、及Mysql索引的语法。 一、索引的概念 ...

    MySQL数据库索引

    前言

    前面的文章讲解数据库的基本操作,本文开始讲解数据库更进一步的知识,本文讲解数据库中的索引,包括索引的概念、索引的优劣性、索引结构、索引分类、及Mysql索引的语法。

    一、索引的概念

    • 索引(index)是帮助MySQL高效获取数据的数据结构(有序)
      在这里插入图片描述
    • 一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上。索引是数据库中用来提高性能的最常用的工具。

    二、索引的优劣性

    • 优势:
      1) 类似于书籍的目录索引,提高数据检索的效率,降低数据库的IO成本。
      2) 通过索引列对数据进行排序,降低数据排序的成本,降低CPU的消耗。
    • 劣势:
      1)实际上索引也是一张表,该表中保存了主键与索引字段,并指向实体类的记录,所以索引列也是要占用空间
      的。
      2)虽然索引大大提高了查询效率,同时却也降低更新表的速度,如对表进行INSERT、UPDATE、DELETE。因为更新表时,MySQL 不仅要保存数据,还要更新索引文件。

    三、索引结构

    索引是在MySQL的存储引擎层中实现的,而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同,也不是所有的存储引擎都支持所有的索引类型的。
    MySQL目前提供了以下4种索引:
    BTREE 索引 : 最常见的索引类型,大部分索引都支持 B 树索引。
    HASH 索引:只有Memory引擎支持 , 使用场景简单 。
    R-tree 索引(空间索引):空间索引是MyISAM引擎的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少,不做特别介绍。
    Full-text (全文索引) :全文索引也是MyISAM的一个特殊索引类型,主要用于全文索引,InnoDB从Mysql5.6版本开始支持全文索引。
    在这里插入图片描述
    我们平常所说的索引,如果没有特别指明,都是指B+树结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+tree 索引,统称为索引。

    补充知识:
    MySql索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上,增加一个指向相邻叶子节点的链表指针,就形成了带有顺序指针的B+Tree,提高区间访问的性能。
    在这里插入图片描述

    四、索引分类

    1) 单值索引 :即一个索引只包含单个列,一个表可以有多个单列索引
    2) 唯一索引 :索引列的值必须唯一,但允许有空值
    3) 复合索引 :即一个索引包含多个列

    五、MySql索引语法

    1. 创建索引

    方法一:为某张表的某些字段创建索引

    create index 索引名 on 表名(字段列表); 
    

    方法二:通过修改表创建索引

    1. 为某张表的某些字段添加主键索引(主键本身就是一个索引):该字段同时具有主键约束的性质
    alter table 表名 add primary key(字段列表);
    
    1. 为某张表的某些字段添加唯一索引:该字段同时具有唯一值约束的性质
    alter table 表名 add unique 索引名(字段列表);
    
    1. 为某张表的某个字段添加普通索引:该字段仅是添加了索引,并没有其它性质
    alter table 表名 add index 索引名(字段列表);
    
    1. 为某张表的某个字段添加全文索引:
    alter table 表名 add fulltext 索引名(字段列表)
    

    2. 查看索引

    查看某张表中包含的索引

    show index from 表名;
    

    3. 删除索引

    删除某张表中的某个索引

    drop index 索引名 on 表名;
    

    六、索引设计原则

    1. 对查询频次较高,且数据量比较大的表建立索引。
    2. 索引字段的选择,最佳候选列应当从where子句的条件中提取,如果where子句中的组合比较多,那么应当挑选最常用、过滤效果最好的列的组合。
    3. 使用唯一索引,区分度越高,使用索引的效率越高。
    4. 索引可以有效的提升查询数据的效率,但索引数量不是多多益善,索引越多,维护索引的代价自然也就水涨船高。另外索引过多的话,MySQL也会犯选择困难病,虽然最终仍然会找到一个可用的索引,但无疑提高了选择的代价。
    5. 使用短索引,索引创建之后也是使用硬盘来存储的,因此提升索引访问的I/O效率,也可以提升总体的访问效率。假如构成索引的字段总长度比较短,那么在给定大小的存储块内可以存储更多的索引值,相应的可以有效的提升MySQL访问索引的I/O效率。
    6. 利用最左前缀,N个列组合而成的组合索引,那么相当于是创建了N个索引,如果查询时where子句中使用了组成该索引的前几个字段,那么这条查询SQL可以利用组合索引来提升查询效率。
    展开全文
  • 数据库索引 - 数据存储 数据存储 数据存放在哪里 当然是存放硬盘中了; 但是, 我们怎么找到具体数据库文件的物理地址呢? 首先, 我们要先查找到表的oid, 然后再去数据库存储数据的目录; select oid,relfilenode from...
  • 前面两篇文章 《解析B+树比B树更加适合做数据库索引的原因 》 和《从底层解析B+索引提高查询速度的原因》是从数据结构的角度分析了B+索引,并分别介绍了B+索引两个主流存储引擎InnoDB和MyISAM中的实现。...
  • 数据库索引与数据库作业

    千次阅读 2018-05-08 10:21:52
    什么是数据库索引数据库索引是对数据库表中一列或多列进行排序的一种结构,使用索引可以快速访问数据库表中的特定信息。数据库索引就像书的目录,能加快数据库的查询速度。索引分为聚簇索引(聚集索引)和非聚簇...
  • 数据库索引漫谈

    2021-01-17 09:43:41
    数据库索引漫谈 前言 ​ 大家平时工作学习中,使用到索引的场景有非常多,比如查询缓慢等等场景,但是索引是什么东西?怎么使用索引?怎么才能命中索引?等等一系列问题提出时,是不是会懵一下,这篇文章就带...
  • 数据库索引 - 索引简介 本节很多内容来自 pg数据库的官方文档 索引简介 索引是什么 索引是一种常用提高数据库性能的方式. 索引能够帮助数据库快速地找到特定的数据行; 但是索引也会增加整个数据库系统的 开销 ...
  • 数据库索引详解

    2018-07-27 10:25:49
    数据库索引详解 [By GavinHacker] ... 关于数据库索引,相信大家用到最多的一定是数据库设计和数据库查询,本篇深度解析一下数据库索引的原理,...(一)了解数据库索引之前,首先了解一下数据库索引的数据结构...
  • mysql进阶(二十七)数据库索引原理

    万次阅读 2016-10-13 20:20:38
    mysql进阶(二十七)数据库索引原理前言  本文主要是阐述MySQL索引机制,主要是说明存储引擎Innodb。   第一部分主要从数据结构及算法理论层面讨论MySQL数据库索引的数理基础。   第二部分结合MySQL数据库中...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 545,158
精华内容 218,063
关键字:

数据库的索引存储在哪里