精华内容
下载资源
问答
  • 2021-08-04 17:12:33

    索引的定义

            索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。索引是针对表而建立的,它是由数据页面以外的索引页面组成的,每个索引页面中的行都会含有逻辑指针,以便加速检索物理数据。

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

      在数据库关系图中,可以在选定表的“索引/键”属性页中创建、编辑或删除每个索引类型。当保存索引所附加到的表,或保存该表所在的关系图时,索引将保存在数据库中。
      在关系数据库中,索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构,它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。简单来讲:索引的作用相当于图书的目录,可以根据目录中的页码快速找到所需的内容。
      索引提供指向存储在表的指定列中的数据值的指针,然后根据指定的排序顺序对这些指针排序。数据库使用索引以找到特定值,然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快,可快速访问数据库表中的特定信息。

      当表中有大量记录时,若要对表进行查询,第一种搜索信息方式是全表搜索,是将所有记录一一取出,和查询条件进行一一对比,然后返回满足条件的记录,这样做会消耗大量数据库系统时间,并造成大量磁盘I/O操作;第二种就是在表中建立索引,然后在索引中找到符合查询条件的索引值,最后通过保存在索引中的ROWID(相当于页码)快速找到表中对应的记录。

    索引的种类

            数据库索引好比是一本书前面的目录,能加快数据库的查询速度。索引分为聚簇索引非聚簇索引两种,聚簇索引是按照数据存放的物理位置为顺序的,而非聚簇索引就不一样了;聚簇索引能提高多行检索的速度,而非聚簇索引对于单行的检索很快。

    根据数据库的功能,可以在数据库设计器中创建三种索引:唯一索引、主键索引和聚集索引。

    唯一索引

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

    主键索引

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

    聚集索引

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

    索引的特点


    优点

      1.加快了数据的检索速度;
      2.创建唯一性索引,保证数据库表中每一行数据的唯一性;
      3.加速表和表之间的连接;
      4.在使用分组和排序子句进行数据检索时,可以显著减少查询中分组和排序的时间;
      5.建立索引之后,在信息查询过程中可以使用优化隐藏器,提高整个信息检索系统的性能。

    缺点
      1.索引需要占物理空间;
      2.当对表中的数据进行增、删、改的时候,索引也要动态的维护,降低了数据的维护速度;
      3.建立数据库过程中,对于索引的建立和维护也需要花费大量的时间。

    创建索引的条件

    可以基于数据库表中的单列或多列创建索引。多列索引可以区分其中一列可能有相同值的行。如果经常同时搜索两列或多列或按两列或多列排序时,索引也很有帮助。例如,如果经常在同一查询中为姓和名两列设置判据,那么在这两列上创建多列索引将很有意义。

    检查查询的WHERE和JOIN子句。在任一子句中包括的每一列都是索引可以选择的对象。对新索引进行试验以检查它对运行查询性能的影响。考虑已在表上创建的索引数量。最好避免在单个表上有很多索引。检查已在表上创建的索引的定义,最好避免包含共享列的重叠索引。

    检查某列中唯一数据值的数量,并将该数量与表中的行数进行比较。比较的结果就是该列的可选择性,这有助于确定该列是否适合建立索引,如果适合,确定索引的类型。

    在创建索引的时候,应该考虑在哪些列上可以创建索引,在哪些列上不能创建索引。一般来说,应该在这些列上创建索引:

    ①在经常需要搜索的列上,可以加快搜索的速度;

    ②在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构;

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

    ④在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的;

    ⑤在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间。

    当然,对于有些列不应该创建索引。这些列具有下列特点:

    ①对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。

    ②对于那些只有很少数据值的列也不应该增加索引。这是因为,由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。

    ③对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少,不利于使用索引。

    ④当修改性能远远大于检索性能时,不应该创建索引。这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因此,当修改操作远远多于检索操作时,不应该创建索引。

    总结一下就是如下情况下需要创建索引列:

    • 经常需要搜索的列上
    • 作为主键的列上
    • 经常用在连接的列上,这些列主要是一些外键
    • 经常需要根据范围进行搜索的列上
    • 经常需要排序的列上
    • 经常使用在where子句上面的列上

    在如下情况下不需要创建索引列:

    • 查询中很少用到的列
    • 对于那些具有很少数据值的列,比如人事表的性别列等
    • 对于那些定义为text,image的列
    • 当对修改性能的要求远远大于搜索性能时

    建立索引案例

    最普通的情况,是为出现在where子句的字段建一个索引。

    CREATE TABLE mytable(
    
    idserial int primary key,
    
    category_id int default 0 not null ,
    
    user_id int default 0 not null ,
    
    adddate int default 0 not null
    
    );
    SELECT * FROM mytable WHERE category_id=1;

    如果在查询时常用类似上面这个语句,最直接的应对之道,是为category_id建立一个简单的索引:

    CREATE INDEX mytable_category_id ON mytable (category_id);

    那如果有不止一个选择条件呢?例如:

    SELECT * FROM mytable WHERE category_id=1 AND user_id=2;

    第一反应可能是,再给user_id建立一个索引。但这不是一个最佳的方法,可以建立多重的索引:

    CREATE INDEX mytable_categoryid_userid ON mytable(category_id,user_id);

    我们可以注意到命名的习惯,使用"表名_字段1名_字段2名"的方式。后面就会知道为什么这样做了。

    现在已经为适当的字段建立了索引,不过,还是有点不放心吧,可能会问,数据库会真正用到这些索引吗?测试一下,对于大多数的数据库来说,这是很容易的,只要使用EXPLAIN命令:

    EXPLAIN
    
    SELECT * FROM mytable
    
    WHERE category_id=1 AND user_id=2;
    
    This is what Postgres 7.1 returns (exactlyasI expected)
    
    NOTICE:QUERY PLAN:
    
    Index Scan using mytable_categoryid_userid on
    
    mytable(cost=0.00..2.02 rows=1 width=16)
    
    EXPLAIN

    以上是postgres的数据,可以看到该数据库在查询的时候使用了一个索引,而且它使用的是创建的第二个索引。我们可以通过索引命名很容易知道它使用适当的索引了。

    紧接着,如果有个ORDERBY 子句呢?

    SELECT * FROM mytable
    
    WHERE category_id=1 AND user_id=2
    
    ORDER BY adddate DESC;

    很简单,就像为where子句中的字段建立一个索引一样,也为ORDER BY的子句中的字段建立一个索引:

    CREATE INDEX mytable_categoryid_userid_adddate ON mytable (category_id,user_id,adddate);

    看看EXPLAIN的输出,数据库多做了一个没有要求的排序,这下知道性能如何受损了吧。

    EXPLAIN 
    
    SELECT * FROM mytable
    
    WHERE category_id=1 AND user_id=2
    
    ORDER BY adddate DESC;
    
    NOTICE:QUERY PLAN:
    
    Sort(cost=2.03..2.03 rows=1 width=16)
    
    Index Scanusing mytable_categoryid_userid_addda
    
    on mytable(cost=0.00..2.02 rows=1 width=16)
    
    EXPLAIN

    为了跳过排序这一步,并不需要其它另外的索引,只要将查询语句稍微改一下。这里用的是postgres,将给该数据库一个额外的提示--在ORDER BY语句中,加入where语句中的字段。这只是一个技术上的处理,并不是必须的,因为实际上在另外两个字段上,并不会有任何的排序操作,不过如果加入,postgres将会知道哪些是它应该做的。

    EXPLAIN SELECT * FROM mytable
    
    WHERE category_id=1 AND user_id=2
    
    ORDER BY category_id DESC,user_id DESC,adddate DESC;
    
    NOTICE:QUERY PLAN:
    
    Index Scan Backward using
    
    mytable_categoryid_userid_addda on mytable(cost=0.00..2.02 rows=1 width=16)
    
    EXPLAIN

    不过如果数据库非常巨大,并且每日的页面请求达上百万算,想会获益良多的。不过,如果要做更为复杂的查询呢,例如将多张表结合起来查询,特别是where限制字句中的字段是来自不止一个表格时,应该怎样处理呢?

    ①通常都尽量避免这种做法,因为这样数据库要将各个表中的东西都结合起来,然后再排除那些不合适的行,搞不好开销会很大。

    ②如果不能避免,应该查看每张要结合起来的表,并且使用前面讲到的策略来建立索引,然后再用EXPLAIN命令验证一下是否使用了预料中的索引。如果是的话,就可以了,不是的话,可能要建立临时的表来将他们结合在一起,并且使用适当的索引。

    要注意的是,建立太多的索引将会影响更新和插入的速度,因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格,就没有必要为一个很少使用的where字句单独建立索引了,对于比较小的表,排序的开销不会很大,也没有必要建立另外的索引。

    在刚开始的时候,如果表不大,没有必要作索引,一般来讲在需要的时候才作索引,也可用一些命令来优化表,例如MySQL可用"OPTIMIZETABLE"。

    每个数据库都有自己的一些优化器,虽然可能还不太完善,但是它们都会在查询时对比过哪种方式较快,在某些情况下,建立索引的话也未必会快,例如索引放在一个不连续的存储空间时,这会增加读磁盘的负担,因此,哪个是最优,应该通过实际的使用环境来检验。

    更多相关内容
  • 数据库索引和分类

    2020-12-14 16:19:04
    什么是数据库索引? 我们再平时的开发中免不了用到数据库的索引,接下来就简单说一下数据库索引数据库索引用来干什么? 数据库索引就是为了提高数据的查询速率。 数据库索引有哪些? 聚集索引:在数据库中,所有...
  • 数据库索引详解

    千次阅读 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域,每个节点能索引的范围更大更精确。

    展开全文
  • 数据库索引

    2022-02-09 15:04:28
    数据库索引,索引类型,索引优化策略

    索引

    索引是数据库为了提高查询效率所创造的一个数据结构。

    索引在存储方式上分为两种类型:

    1.聚簇索引

    InnoDB使用的索引类型,它的思想是通过特定的格式将索引信息和数据存储在一起,并且一张表上的所有数据都存在一个文件中。

    2.非聚簇索引

    MyISam使用的索引类型,它的思想是将索引和数据分开存储。

    索引在类别上分为五种类型:

    主键索引

    数据库默认会对主键施加索引。这个过程是自动的。所以数据库的主键是十分重要的。数据库通过主键索引来获得一行的所有数据。

    唯一索引

    该索引被施加后,需要保证值不能为空。空值在数据库的结构中是十分打乱布局的。

    普通索引

    对于常用的数据列施加的索引就是普通索引,普通索引会存储该列信息对应的id通过回表的方式来拿到一行数据

    组合索引

    2列以上的数据也可以一起作为索引,也就是索引从存储一个数据类型结构变为了一个元组。组合索引遵循最左匹配原则。

    全文索引

    不是很常用的索引,与大文章的分段业务的功能很像。

    索引的结构

    原先数据库是通过随机查找数据的,有了索引之后,就可以根据范围查找了。索引和数据的关系一般如下图所示。

    主键索引会对应数据行,但是普通索引不会。

    回表

    假设我们现在对name施加了索引,那么会出现一个以name为查找范围的索引树,然后在子结点处绑定的是该name对应的id值。拿到id之后,再去id对应的主键索引树通过该id查找行数据。这个过程也被称为“回表”。

    索引覆盖

    1. select * from user where name=”Bob”;
    2. Select id from user where name=”bob”;

    Id为主键,name上建有索引。

    在执行第一条语句时,数据库会先通过name索引找到对应的id,再返回id索引上查找整行数据。这个过程称为回表。

    但是第二条语句,我们要找到的是id信息,那么通过name索引可以直接拿到对应的id信息,就不需要再去查找整行数据了,直接可以通过单name索引拿到数据,这个过程就叫索引覆盖。

    索引失效

    索引在添加之后,也并不是在任何情况下都会生效,有部分情况会导致索引失效的问题。这个就涉及到索引的匹配原则。

    索引下推

    索引下推是针对组合索引来说的

    通常来讲组合索引遵循最左匹配原则,但是当我们运行如下语句时:

    表id,name,age。组合索引(name,age)

    Select * from user where name=’Bob’ and age = 15;

    它的搜寻过程是怎么样的呢?

    MySQL在5.7版本之后加入了索引下推原则。在5.7版本前,在查找以上语句时,会直接把name=’Bob’的所有行一次性全部拿到Server层,之后通过age来筛选,去掉不要的。

    但是有了索引下推之后,这个又浪费空间又浪费时间的操作被优化。现在,MySQL会直接在存储引擎中拿取符合两条条件的数据行,不会先拿name符合的再筛选。比如原先没有索引下推一次拿到server层100条数据,之后通过age筛选只剩下20行。现在会直接拿符合要求的20行数据直接到Server层。

    关于组合索引的最左匹配原则

    对于一张表,id,name,age。假设我们对name和age设置了组合索引,那么以下四句话哪个会触发组合索引呢?

    1. select * from user where name=’Bob’ and age=15;
    2. Select * from user where name = ‘Bob’;
    3. Select * from user where age = 15;
    4. Select * from user where age=15 and name=’Bob’

    对于以上四句话,1,2是肯定会触发索引的,但是3因为最左匹配原则,不会触发索引,也就是说单独出现非组合索引最左侧的列并不会触发组合索引。4表面上看起来不会触发,实则会,Server层对于语句结构有优化,会帮我们把这句话调整至select * from user where name=’Bob’ and age=15;使得该语句可以触发组合索引。所以1,2,4都会触发组合索引,只有3不会。

    对于组合索引的使用规则

    1. select * from user where name=’Bob’ and age=15;
    2. Select * from user where name = ‘Bob’;
    3. Select * from user where age = 15;
    4. Select * from user where age=15 and name=’Bob’

    组合索引的创建方式因为最左匹配原则的问题,想要以上四句话全部有索引用,可以使用一个组合索引+一个单个索引来实现。

    比如:(name,age)+age索引。

    很显然,组合索引的创建方式会因为最左匹配原则改变。(age,name)+name索引也是可以起到同样的效果的。那么实际当中选择哪个会更好呢?

    这个选择需要根据实际数据大小来定,因为索引也是需要存储和维护的,那么索引所占空间更小的就是最优解。假设说name只占2字节,age占4字节,那么当然是选择后者(age,name)+name。因为组合索引的大小是二者之和,没有优化空间。但是单个索引当然要选择实际占空间更小的。当name占16字节过大的时候,age就会是更好的选择了。

    索引优化策略

    1.尽可能减少表连接,多表联查一次最多不要超过三张表。join操作十分耗时。

    2.不要在数据库中加入计算,将计算操作放到业务层去

    诸如 select * from user where age+1=5;

    这种查询是可以成立的但是效率很慢,而且不会走索引。

    3.范围列可以用索引,但是范围查询之后的语句无法使用索引,所以范围查询尽量写在精确查询之后,并且每条sql语句尽可能只是用一条范围查询

    Select * from user where name=’Bob’ and id=16 and age > 15;

    4.尽可能用主键索引,减少其他索引的使用

    因为回表的存在,导致IO查询会翻倍。所以能用主键索引就用主键索引,效率极快。

    5.使用前缀索引

    对于字符串的索引,字符串匹配需要很长的时间,前缀索引是使用字符串的前几个字符,然后这个范围要取到让所有数据的差别尽可能大。这样可以优化对于字符串查询的效率。

    非字符串时不需要使用这样的方式的。

    6.使用索引扫描进行排序

    7.union,on,in,or都可以触发索引,其中on是最快的

    8.语句中出现强制类型转换会导致索引失效,所以sql语句一定注意数据类型的输入格式正确。

    使用explain命令可以查看查询语句的执行情况,是否使用了索引等等。结果中,possible keys表示可能用到的索引,key表示实际用到的索引。

    9.在更新十分频繁的表中尽量不建立索引,因为索引存储和维护也是需要开销的。

    10.创建索引时,列中元素避免出现null值和重复值

    11.组合索引字段总数控制在5个以内

    12.单表索引数量控制在5个以内

    13.优化前,要把结构搞清楚,不要过早优化,并且一张表的索引并不是越多越好。

    展开全文
  • 当被问到数据库索引时,首先可以回答数据库索引是添加数据库表中的字段上的,也就是给这个字段进行排序,是为了提高查询效率而存在的一种机制,一张表的一个字段可以添加一个索引,多个字段也可以添加一个索引,它...

    首先我们要理清逻辑:

    分点来论述
    1.先说说什么是数据库的索引(定义)
    2. 再说说数据库索引的作用
    3. 最后谈谈数据库索引底层的存储方式,以及查询方式

    那么下面我们就来一一说说:

    当被问到数据库索引时,首先可以回答数据库索引是添加数据库表中的字段上的,也就是给这个字段进行排序,是为了提高查询效率而存在的一种机制,一张表的一个字段可以添加一个索引,多个字段也可以添加一个索引,它相当于字典中的目录,可以让我们更快的查询到数据(表中的主键,unique会自动添加索引)
    举一个例子,有一个student学生表,里面有id ,name , score等信息,我们要查询名字为张三的学生信息:

    select  *  from student  where  name ='张三';
    

    对于上面这条语句,如果没有对学生姓名那一列字段加索引,MySQL会从第一行数据开始,每一行进行扫描并对于是否等于张三(这种方式称为全表扫描),如果数据少的话,是可行的,如果数据上千条甚至几十万条的时候,这样挨个对比就显得太笨拙了,效率太低了
    那么就出现了索引,通过索引来检索,索引是需要排序的,底层使用B+树来实现
    大致就是下面这个样子,右边是b+树,从下往上看,最底层是一串有序链表,包括了id(索引)(1 2 3 5 等 )数据data 和下一个数据的地址,其中12称为1页,它包含在同一个区域中,一页最多可存放16kb的数据,上一层是存储的下一层的每页首/尾id,下图中存放的是尾id也就是每一页最大的id,最顶层也是如此,存放中间那层每一页的最大id,这样查找的话,就很方便,例如我们要查找10这个数据,从最顶端10肯定是大于8小于15的,所以直接就进入了右分支,10又是小于11的,所以就进入了9 11 这一页,最后结果肯定也在这其中,这样查找起来就快很多
    在这里插入图片描述
    注意:在数据库的聚集索引(Clustered Index)中,叶子节点直接包含数据。在非聚集索引(NonClustered Index)中,叶子节点带有指向数据的指针。上述图中就只是带有指向数据的指针

    另一方面,B+树对于范围查询也是很快的,比如我们要查找6-13之间的数据,他会先和上面方法一样查询到6位置,然后直接根据有序链表向前查找到13,此处就比b树要显得方便很多(b树是先查询到6,然后根据中序遍历查询到8,再到8-15这一页,再到右边的11-15这一页,最后才找到9-11)


    最后来个总结:

    B+树的特征:

    1.有k个子树的中间节点包含有k个元素,每个元素不保存数据,只用来索引,所有数据都存在叶子节点中
    2.叶子节点本身又是一个有序的链表,自小到大连接
    3.所有的中间节点元素同时存在于子节点中,在子节点元素是最大/最小值

    B+树的优势:
    1.单一节点存储更多的数据,减少查询的IO次数
    2.所有的数据放在叶子节点中,意味着每次都要查询到叶子节点中,查询性能稳定
    3.所有的叶子节点又是一个有序链表,便于范围查询


    创建索引和查询索引

    查询索引

    explain   select * from 表名  where 列名 = 'King';
    查询这个列名是否有索引 
    
    

    创建索引

    create  index  索引名  on 表名(列名) 
    

    那么接下来可能还会进一步问 你知道什么情况下索引会失效吗

    1.以%开头的模糊查询
    2.使用or 也很可能会失效,因为or的两边都有索引的时候才会利用索引查询
    3.使用复合索引,没有使用左列查找,索引也会失效 这个可能不太能明白,举个例子
    现在给 student表中的 name id 创建复合索引 create index stu_index on student(name,id)
    那么如果使用

    select  * from  student  where  name = '张三'  此时会用到索引,
     而select  * from  student  where  id = 10;不会用到索引
    

    此时就成会索引失效,因为name在左侧 ,创建索引必须加上左侧的字段才能使用索引
    4.where中索引列加入了位运算,索引失效

    例如  select  * from  compane where  sal+1 = 800;
    

    还有 什么情况下可以添加索引? 给每个字段都能加索引吗

    并不需要给每个字段都加上索引
    1.当数据量庞大的表中需要添加索引
    2.加索引的字段经常出现在where关键字后面,也就是说经常作为条件来查询
    3.加索引的字段很少进行DML操作 也就是 insert delete update等操作

    这里补充一下数据库常见语言
    1.DQL :数据库查询语言 select
    2.DML:数据库操作语言 用来操作字段中的数据 例如 insert delete update
    3.DDL:数据库定义语言:用来操作数据库中表结构 例如:create alter drop
    4.TCL: 事物控制语言:用来操作事务相关 例如 提交commit 回滚rollback
    5.DCL :数据库控制语言:授予权限 grant 撤销权限 revoke

    展开全文
  • 索引: 是用来帮助MySQ高效的获取数据的数据结构。就相当于一本字典的目录,方便查找; 索引本身也不小,一般存在磁盘上的文件中; 一般所说的索引都是B+树结构。 索引的优点: 高效率进行数据的检索,降低数据库...
  • 数据库索引原理

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

    2022-03-16 12:09:49
    索引的设计原则 一 适合创建索引的情况 准备工作 CREATE TABLE `student_info` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `student_id` INT NOT NULL , `name` VARCHAR(20) DEFAULT NULL, `course_id` INT NOT NULL ...
  • 什么是数据库索引

    2022-05-31 09:50:12
    通俗来说,索引就像一本书的目录,根据目录可以快速的定位要找的内容的所在的页码。 MySQL官方对于索引的定义:索引(Index)是帮助MySQL高效获取数据的一种数据结构,而且是排好序的数据结构,索引存储在磁盘...
  • 1. 什么是索引索引就像是书的目录,是与表或视图关联的磁盘上结构,可以加快从表或视图中检索行的速度。索引中包含由表或视图中的一列或多列生成的键。这些键存储在一个结构(BTree)中,使SQL可以快速有效地...
  • 数据库索引,是数据库管理系统中一个排序的数据结构,以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种...
  • java复习 数据库 索引

    2021-07-22 16:43:29
    数据库索引数据库管理系统中一个排序的数据结构,以协助快速查询,更新数据库表中的数据。 索引的通常实现使用B树或者B+树 通俗的说索引就是一个目录,表相当于书,数据相当于书里的内容,通过内容建立索引形成...
  • MySQL数据库索引

    万次阅读 多人点赞 2018-09-23 09:31:41
    目录 索引是什么 索引有哪些结构 ...什么情况下应该使用组合索引而非单独索引 MySQL中索引是如何组织数据的存储的 在MySQL 5.6中,对索引做了哪些优化? 索引是什么 索引是对数据库表中一个或...
  • 数据库索引高频面试题梳理

    千次阅读 2021-11-20 22:12:02
    数据库中的索引是用来加快查询速度的 B Tree B+ Tree 聚簇索引和非聚簇索引 索引可分为两个大类: 主键索引:主键本身就是一个索引 辅助索引(也称为非主键索引、二级索引):设置主键之外的其他字段为索引 聚簇...
  • 索引是一种数据结构 ,能够帮助我们快速的检索数据库中的数据。 索引是对数据库表中一个或多个列的值进行排序的结构。关键点是索引包含一个表中列的值,并且这些值存储在一个数据结构中。 例如对employee 表的姓名 ...
  • 索引分类 单值索引 一个索引只包含一个字段/一个列 一个表可以有多个列 唯一索引索引列的值必须唯一 但允许有空值 主键会自动创建唯一索引 符合索引:一个索引同时包括多列 特殊的单值索引 使用索引 验证索引...
  • 单列索引(主键索引,唯一索引,普通索引)和多列索引(组合索引),全文索引 主键索引 唯一索引 普通索引 多列索引 全文索引 2、按数据结构分类 1) B+tree索引 b+tree基于平衡二叉树的一种多路平衡查找树,所有记录...
  • 这段时间一直在面试,问了很多候选人数据库索引相关的知识,能答好的不是很多。先引入一个简单的示例,通过示例操作解释一下为什么需要数据库索引。假设我们有一个名为 t_employee 的数据库...
  • Mysql创建索引相关知识
  • MySQL数据库索引及失效场景

    千次阅读 2022-01-06 14:13:11
    MySQL数据库索引及失效场景1. 索引失效概述1.1 索引的概念1.2 索引的特点1.3 索引的分类1.4 索引的使用场景2. 索引失效场景2.1 常见索引失效的9种情况2.2 索引失效场景总结3. 索引失效验证3.1 全值匹配3.2 最佳左...
  • 数据库索引: 索引(index)是帮助MySQL高效获取数据的数据结构(有效),在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据, 这样就可以在这些数据结构上实现...
  • 数据库索引(详细)

    2021-04-29 16:44:18
    二叉树的索引结构:key存放索引字段, value放索引字段所在行的磁盘地址的文件指针 当执行sql语句时,会去找当前的字段是否建立了索引文件,如果拿取字段的值根据搜索方法(遍历)去索引结构找到对应的key,如果相等...
  • 数据库索引

    千次阅读 2022-03-20 11:57:39
    一、索引介绍 1.什么是索引? 一般的应用系统,读写比例在10:1左右,而且插入操作和一般的更新操作很少出现性能问题,在生产环境中,我们遇到...索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查...
  • 数据库索引怎么实现的

    千次阅读 2019-12-15 20:44:49
    数据库索引怎么实现的 (招银网络科技java面经) 目录 数据库索引怎么实现的 1 简介 2索引是如何工作的 简介 索引数据结构类型 哈希表 有序数组 搜索树 索引是怎么提升性能的? 3 优缺点 4 如何合理...
  • 数据库索引原理及优化

    万次阅读 多人点赞 2018-08-07 11:03:02
    本文以MySQL数据库为研究对象,讨论与数据库索引相关的一些话题。特别需要说明的是,MySQL支持诸多存储引擎,而各种存储引擎对索引的支持也各不相同,因此MySQL数据库支持多种索引类型,如BTree索引,哈希索引,全文...
  • 如何正确建立MYSQL数据库索引 索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是很重要的。下面介绍几种常见的MySQL索引类型。 在数据库表中,对字段建立索引可以大大提高查询速度。假如我们创建了一个 ...
  • 数据库索引的创建和使用

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

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 318,740
精华内容 127,496
关键字:

数据库的索引应该放在哪里