精华内容
下载资源
问答
  • 数据库索引实现原理

    万次阅读 多人点赞 2019-04-15 16:28:54
    MySQL索引实现 在MySQL中,索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的,本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。 MyISAM索引实现 MyISAM引擎使用B+Tree作为索引结构,叶...

    MySQL索引实现

    在MySQL中,索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的,本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。

    MyISAM索引实现

    MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图:

    图8

     

    这里设表一共有三列,假设我们以Col1为主键,则图8是一个MyISAM表的主索引(Primary key)示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引,则此索引的结构如下图所示:

    图9

     

    同样也是一颗B+Tree,data域保存数据记录的地址。因此,MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址,读取相应数据记录。

    MyISAM的索引方式也叫做“非聚集”的,之所以这么称呼是为了与InnoDB的聚集索引区分。

    InnoDB索引实现

    虽然InnoDB也使用B+Tree作为索引结构,但具体实现方式却与MyISAM截然不同。

    第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道,MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。而在InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。

    图10

     

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

    第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说,InnoDB的所有辅助索引都引用主键作为data域。例如,图11为定义在Col3上的一个辅助索引:

    图11

     

    这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。

    了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助,例如知道了InnoDB的索引实现后,就很容易明白为什么不建议使用过长的字段作为主键,因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大。再例如,用非单调的字段作为主键在InnoDB中不是个好主意,因为InnoDB数据文件本身是一颗B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。

    下一章将具体讨论这些与索引有关的优化策略。

    索引使用策略及优化

    MySQL的优化主要分为结构优化(Scheme optimization)和查询优化(Query optimization)。本章讨论的高性能索引策略主要属于结构优化范畴。本章的内容完全基于上文的理论基础,实际上一旦理解了索引背后的机制,那么选择高性能的策略就变成了纯粹的推理,并且可以理解这些策略背后的逻辑。

    示例数据库

    为了讨论索引策略,需要一个数据量不算小的数据库作为示例。本文选用MySQL官方文档中提供的示例数据库之一:employees。这个数据库关系复杂度适中,且数据量较大。下图是这个数据库的E-R关系图(引用自MySQL官方手册):

    图12
    图12

     

    最左前缀原理与相关优化

    高效使用索引的首要条件是知道什么样的查询会使用到索引,这个问题和B+Tree中的“最左前缀原理”有关,下面通过例子说明最左前缀原理。

    这里先说一下联合索引的概念。在上文中,我们都是假设索引只引用了单个的列,实际上,MySQL中的索引可以以一定顺序引用多个列,这种索引叫做联合索引,一般的,一个联合索引是一个有序元组<a1, a2, …, an>,其中各个元素均为数据表的一列,实际上要严格定义索引需要用到关系代数,但是这里我不想讨论太多关系代数的话题,因为那样会显得很枯燥,所以这里就不再做严格定义。另外,单列索引可以看成联合索引元素数为1的特例。

    以employees.titles表为例,下面先查看其上都有哪些索引:

     

    从结果中可以到titles表的主索引为<emp_no, title, from_date>,还有一个辅助索引<emp_no>。为了避免多个索引使事情变复杂(MySQL的SQL优化器在多索引时行为比较复杂),这里我们将辅助索引drop掉:

    这样就可以专心分析索引PRIMARY的行为了。

    情况一:全列匹配。

    很明显,当按照索引中所有列进行精确匹配(这里精确匹配指“=”或“IN”匹配)时,索引可以被用到。这里有一点需要注意,理论上索引对顺序是敏感的,但是由于MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的索引,例如我们将where中的条件顺序颠倒:

    效果是一样的。

    情况二:最左前缀匹配。

    当查询条件精确匹配索引的左边连续一个或几个列时,如<emp_no>或<emp_no, title>,所以可以被用到,但是只能用到一部分,即条件所组成的最左前缀。上面的查询从分析结果看用到了PRIMARY索引,但是key_len为4,说明只用到了索引的第一列前缀。

    情况三:查询条件用到了索引中列的精确匹配,但是中间某个条件未提供。

    此时索引使用情况和情况二相同,因为title未提供,所以查询只用到了索引的第一列,而后面的from_date虽然也在索引中,但是由于title不存在而无法和左前缀连接,因此需要对结果进行扫描过滤from_date(这里由于emp_no唯一,所以不存在扫描)。如果想让from_date也使用索引而不是where过滤,可以增加一个辅助索引<emp_no, from_date>,此时上面的查询会使用这个索引。除此之外,还可以使用一种称之为“隔离列”的优化方法,将emp_no与from_date之间的“坑”填上。

    首先我们看下title一共有几种不同的值:

    只有7种。在这种成为“坑”的列值比较少的情况下,可以考虑用“IN”来填补这个“坑”从而形成最左前缀:

     这次key_len为59,说明索引被用全了,但是从type和rows看出IN实际上执行了一个range查询,这里检查了7个key。看下两种查询的性能比较:

    “填坑”后性能提升了一点。如果经过emp_no筛选后余下很多数据,则后者性能优势会更加明显。当然,如果title的值很多,用填坑就不合适了,必须建立辅助索引。

    情况四:查询条件没有指定索引第一列。

    由于不是最左前缀,索引这样的查询显然用不到索引。

    情况五:匹配某列的前缀字符串。

    此时可以用到索引,但是如果通配符不是只出现在末尾,则无法使用索引。(原文表述有误,如果通配符%不出现在开头,则可以用到索引,但根据具体情况不同可能只会用其中一个前缀)

    情况六:范围查询。

    范围列可以用到索引(必须是最左前缀),但是范围列后面的列无法用到索引。同时,索引最多用于一个范围列,因此如果查询条件中有两个范围列则无法全用到索引。

    可以看到索引对第二个范围索引无能为力。这里特别要说明MySQL一个有意思的地方,那就是仅用explain可能无法区分范围索引和多值匹配,因为在type中这两者都显示为range。同时,用了“between”并不意味着就是范围查询,例如下面的查询:

    看起来是用了两个范围查询,但作用于emp_no上的“BETWEEN”实际上相当于“IN”,也就是说emp_no实际是多值精确匹配。可以看到这个查询用到了索引全部三个列。因此在MySQL中要谨慎地区分多值匹配和范围匹配,否则会对MySQL的行为产生困惑。

    情况七:查询条件中含有函数或表达式。

    很不幸,如果查询条件中含有函数或表达式,则MySQL不会为这列使用索引(虽然某些在数学意义上可以使用)。例如:

    虽然这个查询和情况五中功能相同,但是由于使用了函数left,则无法为title列应用索引,而情况五中用LIKE则可以。再如:

    显然这个查询等价于查询emp_no为10001的函数,但是由于查询条件是一个表达式,MySQL无法为其使用索引。看来MySQL还没有智能到自动优化常量表达式的程度,因此在写查询语句时尽量避免表达式出现在查询中,而是先手工私下代数运算,转换为无表达式的查询语句。

    索引选择性与前缀索引

    既然索引可以加快查询速度,那么是不是只要是查询语句需要,就建上索引?答案是否定的。因为索引虽然加快了查询速度,但索引也是有代价的:索引文件本身要消耗存储空间,同时索引会加重插入、删除和修改记录时的负担,另外,MySQL在运行时也要消耗资源维护索引,因此索引并不是越多越好。一般两种情况下不建议建索引。

    第一种情况是表记录比较少,例如一两千条甚至只有几百条记录的表,没必要建索引,让查询做全表扫描就好了。至于多少条记录才算多,这个个人有个人的看法,我个人的经验是以2000作为分界线,记录数不超过 2000可以考虑不建索引,超过2000条可以酌情考虑索引。

    另一种不建议建索引的情况是索引的选择性较低。所谓索引的选择性(Selectivity),是指不重复的索引值(也叫基数,Cardinality)与表记录数(#T)的比值:

    Index Selectivity = Cardinality / #T

    显然选择性的取值范围为(0, 1],选择性越高的索引价值越大,这是由B+Tree的性质决定的。例如,上文用到的employees.titles表,如果title字段经常被单独查询,是否需要建索引,我们看一下它的选择性:

    title的选择性不足0.0001(精确值为0.00001579),所以实在没有什么必要为其单独建索引。

    有一种与索引选择性有关的索引优化策略叫做前缀索引,就是用列的前缀代替整个列作为索引key,当前缀长度合适时,可以做到既使得前缀索引的选择性接近全列索引,同时因为索引key变短而减少了索引文件的大小和维护开销。下面以employees.employees表为例介绍前缀索引的选择和使用。

    从图12可以看到employees表只有一个索引<emp_no>,那么如果我们想按名字搜索一个人,就只能全表扫描了:

    如果频繁按名字搜索员工,这样显然效率很低,因此我们可以考虑建索引。有两种选择,建<first_name>或<first_name, last_name>,看下两个索引的选择性:

    <first_name>显然选择性太低,<first_name, last_name>选择性很好,但是first_name和last_name加起来长度为30,有没有兼顾长度和选择性的办法?可以考虑用first_name和last_name的前几个字符建立索引,例如<first_name, left(last_name, 3)>,看看其选择性:

    选择性还不错,但离0.9313还是有点距离,那么把last_name前缀加到4:

    这时选择性已经很理想了,而这个索引的长度只有18,比<first_name, last_name>短了接近一半,我们把这个前缀索引 建上:

    此时再执行一遍按名字查询,比较分析一下与建索引前的结果:

    性能的提升是显著的,查询速度提高了120多倍。

    前缀索引兼顾索引大小和查询速度,但是其缺点是不能用于ORDER BY和GROUP BY操作,也不能用于Covering index(即当索引本身包含查询所需全部数据时,不再访问数据文件本身)。

    InnoDB的主键选择与插入优化

    在使用InnoDB存储引擎时,如果没有特别的需要,请永远使用一个与业务无关的自增字段作为主键。

    经常看到有帖子或博客讨论主键选择问题,有人建议使用业务无关的自增主键,有人觉得没有必要,完全可以使用如学号或身份证号这种唯一字段作为主键。不论支持哪种论点,大多数论据都是业务层面的。如果从数据库索引优化角度看,使用InnoDB引擎而不使用自增主键绝对是一个糟糕的主意。

    上文讨论过InnoDB的索引实现,InnoDB使用聚集索引,数据记录本身被存于主索引(一颗B+Tree)的叶子节点上。这就要求同一个叶子节点内(大小为一个内存页或磁盘页)的各条数据记录按主键顺序存放,因此每当有一条新的记录插入时,MySQL会根据其主键将其插入适当的节点和位置,如果页面达到装载因子(InnoDB默认为15/16),则开辟一个新的页(节点)。

    如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。如下图所示:

    图13

     

    这样就会形成一个紧凑的索引结构,近似顺序填满。由于每次插入时也不需要移动已有数据,因此效率很高,也不会增加很多开销在维护索引上。

    如果使用非自增主键(如果身份证号或学号等),由于每次插入主键的值近似于随机,因此每次新纪录都要被插到现有索引页得中间某个位置:

    图14

     

    此时MySQL不得不为了将新记录插到合适位置而移动数据,甚至目标页面可能已经被回写到磁盘上而从缓存中清掉,此时又要从磁盘上读回来,这增加了很多开销,同时频繁的移动、分页操作造成了大量的碎片,得到了不够紧凑的索引结构,后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

    因此,只要可以,请尽量在InnoDB上采用自增字段做主键。

    本文参考:http://blog.codinglabs.org/articles/theory-of-mysql-index.html

    展开全文
  • 1. 什么是索引: ...索引中包含由表或视图中的一列或列生成的键。这些键存储在一个结构(BTree)中,使SQL可以快速有效地查找与键值关联的行。 2. 为什么要建立索引,即索引的优点: ① ...

    Mysql优化汇总系列:https://blog.csdn.net/qq_32534441/article/details/86523894
    Mysql优化之三:数据库索引原理及优化:https://blog.csdn.net/qq_32534441/article/details/86493753
    Mysql优化系列:https://blog.csdn.net/qq_32534441/article/category/8610043

    1. 什么是索引:

    索引就像是书的目录,是与表或视图关联的磁盘上结构,可以加快从表或视图中检索行的速度。索引中包含由表或视图中的一列或多列生成的键。这些键存储在一个结构(BTree)中,使SQL可以快速有效地查找与键值关联的行。

    2. 索引的原理:

    索引的原理大致概括为以空间换时间,数据库在未添加索引的时候进行查询默认的是进行全量搜索,也就是进行全局扫描,有多少条数据就要进行多少次查询,然后找到相匹配的数据就把他放到结果集中,直到全表扫描完。而建立索引之后,会将建立索引的KEY值放在一个n叉树上(BTree)。因为B树的特点就是适合在磁盘等直接存储设备上组织动态查找表,每次以索引进行条件查询时,会去树上根据key值直接进行搜索,次数约为log总条数,底数为页面存储数,例如一个100万数据的表,页面存储数为100,那么有索引的查询次数为3次log1000000100,但是全量搜索为100万次搜索,这种方式类似于二分法,但是这个是n分法。



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

    第一,通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。 
    第二,可以大大加快 数据的检索速度,这也是创建索引的最主要的原因。 
    第三,可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。 
    第四,在使用分组和排序 子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。 
    第五,通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。 


    也许会有人要问:增加索引有如此多的优点,为什么不对表中的每一个列创建一个索引呢?这种想法固然有其合理性,然而也有其片面性。虽然,索引有许多优点,但是,为表中的每一个列都增加索引,是非常不明智的。这是因为,增加索引也有许多不利的一个方面。 

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


    索引是建立在数据库表中的某些列的上面。因此,在创建索引的时候,应该仔细考虑在哪些列上可以创建索引,在哪些列上不能创建索引。一般来说,应该在这些列上创建索引,例如: 

    在经常需要搜索的列上可以加快搜索的速度; 
    在作为主键的列上强制该列的唯一性和组织表中数据的排列结构; 
    在经常用在连接的列上 些列主要是一些外键,可以加快连接的速度; 
    在经常需要根据范围进行搜索的列上创建索引因为索引已经排序,其指定的范围是连续的; 
    在经常需要排序的列上创 建索引因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间; 
    在经常使用在WHERE子句中的列上面创建索引加快条件的判断速度。 


    同样,对于有些列不应该创建索引。一般来说,不应该创建索引的的这些列具有下列特点: 

    第一,对于那些在查询中很少使用或者参考的列不应该创建索引这是因 为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。 
    第二,对于那 些只有很少数据值的列也不应该增加索引。这是因为,由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。 
    第三,对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少。 
    第四,当修改性能远远大于检索性能时,不应该创建索 引。这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因 此,当修改性能远远大于检索性能时,不应该创建索引。 

    4.创建索引的方法和索引的特征 
    创建索引的方法 
    创 建索引有多种方法,这些方法包括直接创建索引的方法和间接创建索引的方法。直接创建索引,例如使用CREATE INDEX语句或者使用创建索引向导,间接创建索引,例如在表中定义主键约束或者唯一性键约束时,同时也创建了索引。虽然,这两种方法都可以创建索引,但 是,它们创建索引的具体内容是有区别的。 

    直接创建索引:
    使用CREATE INDEX语句或者使用创建索引向导来创建索引,这是最基本的索引创建方式,并且这种方法最具有柔性,可以定制创建出符合自己需要的索引。在使用这种方式 创建索引时,可以使用许多选项,例如指定数据页的充满度、进行排序、整理统计信息等,这样可以优化索引。使用这种方法,可以指定索引的类型、唯一性和复合 性,也就是说,既可以创建聚簇索引,也可以创建非聚簇索引,既可以在一个列上创建索引,也可以在两个或者两个以上的列上创建索引。 
    间接创建索引:
    过定义主键约束或者唯一性键约束,也可以间接创建索引。主键约束是一种保持数据完整性的逻辑,它限制表中的记录有相同的主键记录。在创建主键约束时,系统自动创建了一个唯一性的聚簇索引。虽然,在逻辑上,主键约束是一种重要的结构,但是,在物理结构上,与主键约束相对应的结构是唯一性的聚簇索引。换句话说,在物理实现上,不存在主键约束,而只存在唯一性的聚簇索引。同样,在创建唯一性键约束时,也同时创建了索引,这种索引则是唯一性的非聚簇索引。因此,当使用约束创建索引时,索引的类型和特征基本上都已经确定了,由用户定制的余地比较小。 

    当在表上定义主键或者唯一性键约束时,如果表 中已经有了使用CREATE INDEX语句创建的标准索引时,那么主键约束或者唯一性键约束创建的索引覆盖以前创建的标准索引。也就是说,主键约束或者唯一性键约束创建的索引的优先 级高于使用CREATE INDEX语句创建的索引。 

    索引的特征 
    索引有两个特征,即唯一性索引和复合索引。
     
    唯一 性索引保证在索引列中的全部数据是唯一的,不会包含冗余数据。如果表中已经有一个主键约束或者唯一性键约束,那么当创建表或者修改表时,SQL Server自动创建一个唯一性索引。然而,如果必须保证唯一性,那么应该创建主键约束或者唯一性键约束,而不是创建一个唯一性索引。当创建唯一性索引 时,应该认真考虑这些规则:当在表中创建主键约束或者唯一性键约束时,SQL Server自动创建一个唯一性索引;如果表中已经包含有数据,那么当创建索引时,SQL Server检查表中已有数据的冗余性;每当使用插入语句插入数据或者使用修改语句修改数据时,SQL Server检查数据的冗余性:如果有冗余值,那么SQL Server取消该语句的执行,并且返回一个错误消息;确保表中的每一行数据都有一个唯一值,这样可以确保每一个实体都可以唯一确认;只能在可以保证实体 完整性的列上创建唯一性索引,例如,不能在人事表中的姓名列上创建唯一性索引,因为人们可以有相同的姓名。 

    复合索引就是一个索引创建
    在两个列或者多个列上。在搜索时,当两个或者多个列作为一个关键值时,最好在这些列上创建复合索引。当创建复合索引时,应该考虑这些规则:最多可以把16个列合并成一个单独的复合索引,构成复合索引的列的总长度不能超过900字节,也就是说复合列的长度不能太长;在复合索引中,所 有的列必须来自同一个表中,不能跨表建立复合列;在复合索引中,列的排列顺序是非常重要的,因此要认真排列列的顺序,原则上,应该首先定义最唯一的列,例 如在(COL1,COL2)上的索引与在(COL2,COL1)上的索引是不相同的,因为两个索引的列的顺序不同;为了使查询优化器使用复合索引,查询语 句中的WHERE子句必须参考复合索引中第一个列;当表中有多个关键列时,复合索引是非常有用的;使用复合索引可以提高查询性能,减少在一个表中所创建的 索引数量。

     

    5.索引的类型 
    根据索引的顺序与数据表的物理顺序是否相同,可以把索引分成两种类型。一种是数据表的物理顺序与索引顺序相同的聚簇索引,另一种是数据表的物理顺序与索引顺序不相同的非聚簇索引。 

    聚簇索引

    所谓聚簇索引,就是指主索引文件和数据文件为同一份文件,聚簇索引主要用在Innodb存储引擎中。在该索引实现方式中B+Tree的叶子节点上的data就是数据本身,key为主键,如果是一般索引的话,data便会指向对应的主索引,如下图所示:

    在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针,就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能,例如图4中如果要查询key为从18到49的所有数据记录,当找到18后,只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点,极大提到了区间查询效率。

    非聚簇索

     

    非聚簇索引就是指B+Tree的叶子节点上的data,并不是数据本身,而是数据存放的地址。主索引和辅助索引没啥区别,只是主索引中的key一定得是唯一的。主要用在MyISAM存储引擎中,如下图:

    非聚簇索引比聚簇索引多了一次读取数据的IO操作,所以查找性能上会差。

    MyisAM索引与InnoDB索引相比较

    • MyisAM支持全文索引(FULLTEXT)、压缩索引,InnoDB不支持;
    • InnoDB支持事务,MyisAM不支持;
    • MyisAM顺序储存数据,索引叶子节点保存对应数据行地址,辅助索引很主键索引相差无几;InnoDB主键节点同时保存数据行,其他辅助索引保存的是主键索引的值;
    • MyisAM键值分离,索引载入内存(key_buffer_size),数据缓存依赖操作系统;InnoDB键值一起保存,索引与数据一起载入InnoDB缓冲池;MyisAM主键(唯一)索引按升序来存储存储,InnoDB则不一定
    • MyisAM索引的基数值(Cardinality,show index 命令可以看见)是精确的,InnoDB则是估计值。这里涉及到信息统计的知识,MyisAM统计信息是保存磁盘中,在alter表或Analyze table操作更新此信息,而InnoDB则是在表第一次打开的时候估计值保存在缓存区内;
    • MyisAM处理字符串索引时用增量保存的方式,如第一个索引是‘preform’,第二个是‘preformence’,则第二个保存是‘7,ance’,这个明显的好处是缩短索引,但是缺陷就是不支持倒序提取索引,必须顺序遍历获取索引

    为什么选用B+/-Tree

    一般来说,索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上。这样的话,索引查找过程中就要产生磁盘I/O消耗,相对于内存存取,I/O存取的消耗要高几个数量级,所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说,索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

    简单点说说内存读取,内存是由一系列的存储单元组成的,每个存储单元存储固定大小的数据,且有一个唯一地址。当需要读内存时,将地址信号放到地址总线上传给内存,内存解析信号并定位到存储单元,然后把该存储单元上的数据放到数据总线上,回传。

    写内存时,系统将要写入的数据和单元地址分别放到数据总线和地址总线上,内存读取两个总线的内容,做相应的写操作。

    内存存取效率,跟次数有关,先读取A数据还是后读取A数据不会影响存取效率。而磁盘存取就不一样了,磁盘I/O涉及机械操作。磁盘是由大小相同且同轴的圆形盘片组成,磁盘可以转动(各个磁盘须同时转动)。磁盘的一侧有磁头支架,磁头支架固定了一组磁头,每个磁头负责存取一个磁盘的内容。磁头不动,磁盘转动,但磁臂可以前后动,用于读取不同磁道上的数据。磁道就是以盘片为中心划分出来的一系列同心环(如图标红那圈)。磁道又划分为一个个小段,叫扇区,是磁盘的最小存储单元。

    磁盘读取时,系统将数据逻辑地址传给磁盘,磁盘的控制电路会解析出物理地址,即哪个磁道哪个扇区。于是磁头需要前后移动到对应的磁道,消耗的时间叫寻道时间,然后磁盘旋转将对应的扇区转到磁头下,消耗的时间叫旋转时间。所以,适当的操作顺序和数据存放可以减少寻道时间和旋转时间。
    为了尽量减少I/O操作,磁盘读取每次都会预读,大小通常为页的整数倍。即使只需要读取一个字节,磁盘也会读取一页的数据(通常为4K)放入内存,内存与磁盘以页为单位交换数据。因为局部性原理认为,通常一个数据被用到,其附近的数据也会立马被用到。

    B-Tree:如果一次检索需要访问4个节点,数据库系统设计者利用磁盘预读原理,把节点的大小设计为一个页,那读取一个节点只需要一次I/O操作,完成这次检索操作,最多需要3次I/O(根节点常驻内存)。数据记录越小,每个节点存放的数据就越多,树的高度也就越小,I/O操作就少了,检索效率也就上去了。

    B+Tree:非叶子节点只存key,大大滴减少了非叶子节点的大小,那么每个节点就可以存放更多的记录,树更矮了,I/O操作更少了。所以B+Tree拥有更好的性能。

     

    • 索引分类
    ○ 直接创建索引和间接创建索引
    ○ 普通索引和唯一性索引
    ○ 单个索引和复合索引
    ○ 聚簇索引和非聚簇索引

    • 索引失效

    1. 如果条件中有or,即使其中有条件带索引也不会使用(这就是为什么尽量少使用or的原因)(注意:要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引)
    2. 对于多列索引,不是使用的第一部分(不符合最左前缀原则),则不会使用索引,例子如下:
      如果select * from key1=1 and key2= 2;则建立组合索引(key1,key2);
      select * from key1 = 1;组合索引有效;
      select * from key1 = 1 and key2= 2;组合索引有效;
      select * from key2 = 2;组合索引失效;不符合最左前缀原则
    3. like查询是以%开头
    4. 如果列类型是字符串,那一定要在条件中使用引号引起来,否则不会使用索引
    5. 如果mysql估计使用全表扫描比使用索引快,则不使用索引
    展开全文
  • MySQL数据库索引

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

    目录

    索引是什么

    索引有哪些结构/索引常见的模型

    B+树索引

    数据库有哪些索引

    唯一索引

    聚簇索引与非聚簇索引

    全文索引

    索引的最左前缀原则

    索引下推

    使用索引一定能提高查询性能吗?

    哪些情况下设置了索引但是无法使用

    哪些情况下需要设置索引、哪些情况下不需要

    什么情况下应该使用组合/联合索引而非单独索引

    MySQL中索引是如何组织数据的存储的

    Mysql索引原理

    Mysql是如何根据索引查询数据的

    普通索引和唯一索引,应该怎么选择?


    索引是什么

    索引是对数据库表中一个或多个列的值进行排序的结构,是帮助MySQL高效获取数据的数据结构

    你也可以这样理解:索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中,索引允许用户不必翻阅完整本书就能迅速地找到所需要的信息。在数据库中,索引也允许数据库程序迅速地找到表中的数据,而不必扫描整个数据库。

    MySQL数据库几个基本的索引类型:普通索引、唯一索引、主键索引、全文索引

    1.索引加快数据库的检索速度

    2.索引降低了插入、删除、修改等维护任务的速度

    3.唯一索引可以确保每一行数据的唯一性

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

    5.索引需要占物理和数据空间

    索引有哪些结构/索引常见的模型

    1. 哈希表:一种以键-值(key-value)存储数据的结构,我们只要输入待查找的值即key,就可以找到其对应的值即Value。哈希的思路很简单,把值放在数组里,用一个哈希函数把key换算成一个确定的位置,然后把value放在数组的这个位置。如果出现hash冲突,则在冲突的value位置使用链表进行连接。

        适用场景:等值查询,Memcached及其他一些NoSQL引擎

        

    2. 有序数组

        适用场景:只适用于静态存储引擎。用于等值查询和范围查询(ID值必须是递增的)

        

    3. 搜索树:左子节点小于父节点、父节点小于右子节点。Innodb使用B+树,为什么数据库使用B+树作为索引?

    问:为什么采用B+树?这和Hash索引比较起来有什么优缺点吗?

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

    1、哈希索引适合等值查询,但是无法进行范围查询 

    2、哈希索引没办法利用索引完成排序 

    3、哈希索引不支持多列联合索引的最左匹配规则 

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

    B+树索引

    我们举个例子,假设我们有一个主键列为ID的表,表中有字段k,并且在k上有索引。

    表中R1~R5的(ID,k)值分别为(100,1)、(200,2)、(300,3)、(500,5)和(600,6),两棵树的示例示意图如下。

    主键索引的叶子节点存的是整行数据。在InnoDB里,主键索引也被称为聚簇索引(clustered index)。

    非主键索引的叶子节点内容是主键的值。在InnoDB里,非主键索引也被称为二级索引(secondary index)。

        PS:主键长度越小,普通索引的叶子节点就越小,普通索引占用的空间也就越小。

    基于主键索引和普通索引的查询有什么区别?

    • 如果语句是select * from T where ID=500,即主键查询方式,则只需要搜索ID这棵B+树;
    • 如果语句是select * from T where k=5,即普通索引查询方式,则需要先搜索k索引树,得到ID的值为500,再到ID索引树搜索一次。这个过程称为回表。

    也就是说,基于非主键索引的查询需要多扫描一棵索引树。因此,我们在应用中应该尽量使用主键查询。

    索引维护

        对于主键不是递增的表,在插入数据时,如新插入数据ID值为400,则需要逻辑上挪动后面的数据,空出位置。

        而更糟的情况是,如果R5所在的数据页已经满了,根据B+树的算法,这时候需要申请一个新的数据页,然后挪动部分数据过去。这个过程称为页分裂

        除了性能外,页分裂操作还影响数据页的利用率。原本放在一个页的数据,现在分到两个页中,整体空间利用率降低大约50%。当

        然有分裂就有合并。当相邻两个页由于删除了数据,利用率很低之后,会将数据页做合并。合并的过程,可以认为是分裂过程的逆过程。

    数据库有哪些索引

    在MySql数据库中,有四种索引:聚集索引(主键索引)(聚簇索引)、普通索引唯一索引以及全文索引(FUNLLTEXT INDEX)

    索引又可分为聚簇索引非聚簇索引两种

    唯一索引

    一种索引,不允许具有索引值相同的行,从而禁止重复的索引或键值。系统在创建该索引时检查是否有重复的键值,并在每次使用 INSERT 或 UPDATE 语句添加数据时进行检查。

    CREATE UNIQUE CLUSTERED INDEX myclumn_cindex ON mytable(mycolumn)

    聚簇索引与非聚簇索引

    可以理解为主键索引与普通索引

    聚簇索引:是对磁盘上实际数据重新组织以按指定的一个或多个列的值排序的算法。特点是存储数据的顺序和索引顺序一致,且一个表只能有一个聚簇索引,因为物理存储只能有一个顺序。主键索引一般都是聚簇索引

    非聚簇索引:表数据存储顺序与索引顺序无关。对于非聚簇索引,叶结点包含索引字段值及指向数据页数据行的逻辑指针,其行数量与数据表行数据量一致。非聚簇索引记录的物理顺序与逻辑顺序没有必然的联系,与数据的存储物理结构没有关系;一个表对应的非聚簇索引可以有多条,根据不同列的约束可以建立不同要求的非聚簇索引;

    一般情况下主键会默认创建聚簇索引,且一张表只允许存在一个聚簇索引。因为物理存储只能有一个顺序。

    聚簇索引的叶子节点就是数据节点(Innodb的B+树的主键对应的数据节点),而非聚簇索引的叶子节点仍然是索引节点,只不过有指向对应数据块的指针。

    聚簇索引主键的插入速度要比非聚簇索引主键的插入速度慢很多。

    相比之下,聚簇索引适合排序,非聚簇索引不适合用在排序的场合。因为聚簇索引本身已经是按照物理顺序放置的,排序很快。非聚簇索引则没有按序存放,需要额外消耗资源来排序。

    建立聚簇索引的语句:

    CREATE CLUSTER INDEX index_name ON table_name(column_name1,...);

    问:主键索引查询只会查一次,而非主键索引一定需要回表查询多次吗?

    答:通过覆盖索引也可以只查询一次

    覆盖索引(covering index)指一个查询语句的执行只用从索引中就能够取得,不必从数据表中读取。也可以称之为实现了索引覆盖。

    当一条查询语句符合覆盖索引条件时,MySQL只需要通过索引就可以返回查询所需要的数据,这样避免了查到索引后再返回表操作,减少I/O提高效率。

    如,表covering_index_sample中有一个普通索引 idx_key1_key2(key1,key2)。

    当我们通过SQL语句:select key2 from covering_index_sample where key1 = 'keytest';的时候,就可以通过覆盖索引查询,无需回表。

    注:如果这个覆盖索引是一个前缀索引,那么它依然需要回表,因为系统并不确定前缀索引的定义是否截断了完整信息。

    问:以下重建索引的步骤是否存在问题?

    重建普通索引 k

    alter table T drop index k;
    alter table T add index(k);
    

    重建主键索引

    alter table T drop primary key;
    alter table T add primary key(id);

    答:重建索引k的做法是合理的,可以达到省空间的目的。但是,重建主键的过程不合理。不论是删除主键还是创建主键,都会将整个表重建。所以连着执行这两个语句的话,第一个语句就白做了。这两个语句,可以用这个语句代替 : alter table T engine=InnoDB。

    全文索引

    全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。它能够利用【分词技术】等多种算法智能分析出文本文字中关键词的频率和重要性,然后按照一定的算法规则智能地筛选出我们想要的搜索结果。

    select * from 表名 where 标题 like '%xxx%' or 内容 like '%xxx%' or 作者 like '%xxx%';

    这种搜索效率无比底下

    全文索引是为了使得“关键词搜索”功能更加的高效能。

    我们有这么一张数据表: 

    文章id 文章标题 文章内容

    1 超级塞亚人  我是超级塞亚人我喜欢吃苹果,我不是233大国的人,也不是地球人

    2 我233大国威武,我233大国13亿人,我233大国

    3 我喜欢游泳 游泳有很多好方法

    4 动画片 我儿子喜欢看动画片,尤其是七龙珠,因为里面有塞亚人,而且塞亚人喜欢吃苹果,他们不是地球人

    5 运动 我喜欢运动,喜欢跑步,喜欢游泳,喜欢健身,喜欢xxoo

    6 打炮 我是一个二战的老兵,这是我的回忆录,我最幸福的时光就是在233大国吃着苹果打炮

    7 。。。  

    8 。。。  

    9 。。。  

    然后,根据以上的文章内容,如果建立了一个索引文件(这里忽略索引文件的数据结构,仅仅以一种易于理解的方式呈现): 

    关键词   文章id

    塞亚人    1,4

    苹果      1,4,6

    233大国      1,2,6

    地球        1,4

    游泳        3,5

    七龙珠      4

    喜欢     1,4,5,6   

    那么当我想搜索  “塞亚人”的时候,这个索引文件直接告诉我在文章id为1和4的文章里有这个词。 

    这个索引文件就是“全文索引”。

    如何使用全文索引和分词的方式来帮助优化你的搜索呢?

    需要工作的程序:索引程序,分词程序,数据库。 

    工作原理: 

    1、索引程序从数据库读取数据,比如上面例子中的数据表,索引程序通过sql语句:select 文章id,文章标题,文章内容 from 文章表.获得文章的相关数据 

    2、索引程序对需要索引的内容进行“分词”,而这里的分词就是调用分词程序啦! 

    3、索引程序对分好词的一个个词条加入索引文件。

    在你写的代码里,原来到数据库----like %xxx%-----的语句就变成了到索引文件里去查找,从而找到相应的数据(这点相信你已经理解啦!)

    创建全文索引的两种方法:

    1.在建表语句中

    2.在已知表中

    ALTER TABLE article ADD FULLTEXT INDEX fulltext_article(title,content);

    具体如何使用全文索引呢?

    不用全文索引时的写法:SELECT * FROM article WHERE content LIKE ‘%查询字符串%’;

    使用全文索引:SELECT * FROM article WHERE MATCH(title,content) AGAINST (‘查询字符串’);

    注意:

    1、MySql自带的全文索引只能对英文进行全文检索,目前无法对中文进行全文检索。如果需要对包含中文在内的文本数据进行全文检索,我们需要采用Sphinx(斯芬克斯)/Coreseek技术来处理中文。

    2、使用MySql自带的全文索引时,如果查询字符串的长度过短将无法得到期望的搜索结果。MySql全文索引所能找到的词默认最小长度为4个字符。另外,如果查询的字符串包含停止词,那么该停止词将会被忽略。

    3、如果可能,请尽量先创建表并插入所有数据后再创建全文索引,而不要在创建表时就直接创建全文索引,因为前者比后者的全文索引效率要高。

    索引的最左前缀原则

    在MySQL建立联合索引时会遵守最左前缀匹配原则,即最左优先,在检索数据时从联合索引的最左边开始匹配。

    索引下推

    Index Condition Pushdown (ICP) ,Mysql 5.6添加,用于优化数据查询。

    索引条件下推优化可以减少存储引擎查询基础表的次数,也可以减少MySQL服务器从存储引擎接收数据的次数。 

    用下面这种场景进行介绍

    假设有如下查询语句:select * from tuser where name like '张%' and age=10 and ismale=1;  (有联合索引 name,age)

    我们知道了前缀索引规则,所以这个语句在搜索索引树的时候,只能用 “张”

    以下是Mysql 5.6 之前的查询流程:

    以下是Mysql 5.6 时的查询流程:(使用了索引下推

    InnoDB在(name,age)索引内部就判断了age是否等于10,对于不等于10的记录,直接判断并跳过。在我们的这个例子中,只需要对ID4、ID5这两条记录回表取数据判断,就只需要回表2次。

    使用索引一定能提高查询性能吗?

    通常,通过索引查询数据比全表扫描要快,但是我们也必须注意到它的代价.

    索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改。这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5次的磁盘I/O.

    索引不但会使得插入和修改的效率降低,而且在查询的时候,有一个查询优化器,太多的索引会让优化器困惑,可能没有办法找到正确的查询路径,从而选择了慢的索引。

    索引范围查询(INDEX RANGE SCAN)适用于两种情况:

        1.基于一个范围的检索,一般查询返回结果集小于表中记录数的30%

        2.基于非唯一性索引的检索

        3.直接晋升为覆盖索引,避免多次查表

    哪些情况下设置了索引但是无法使用

    根本原因是查询优化器决定不使用索引:

    一条SQL语句的查询,可以有不同的执行方案,至于最终选择哪种方案,需要通过优化器进行选择,选择执行成本最低的方案。在一条单表查询语句真正执行之前,MySQL的查询优化器会找出执行该语句所有可能使用的方案,对比之后找出成本最低的方案。这个成本最低的方案就是所谓的执行计划。优化过程大致如下:

    1、根据搜索条件,找出所有可能使用的索引 

    2、计算全表扫描的代价 

    3、计算使用不同索引执行查询的代价 

    4、对比各种执行方案的代价,找出成本最低的那一个

    有时候查询语句没有按照索引的要求来也会导致无法使用索引,如下:

    1. 建立组合索引,where条件单字段。INDEX(a,b,c),当条件为a或a,b或a,b,c或a,c时都可以使用索引,但是当条件为b,c时将不会使用索引。也就是说不是使用的第一部分,则不会使用索引。如果是INDEX(a,b),即使查询的where是b,a,由于sql优化器的优化作用,会把b,a换成a,b,这样就可以走索引了。如果是index(a,b,c),查询是(a,b,c,d)不会走索引
    2. 条件中用or,即使其中有条件带索引,也不会使用索引查询(这就是查询尽量不要用or的原因,用in)(注意:使用or,又想索引生效,只能将or条件中的每个列都加上索引,这样查询时每个列都会单独使用它们自己的索引
    3. like的模糊查询的模糊词在字符串前面,比如以%或_开头,索引失效。
    4. 在使用不等于(is null、is not null、!= 、<>)的时候无法使用索引会导致全表扫描。
    5. 类型错误,如字段类型为varchar,where条件用number。
    6. 对索引应用内部函数,这种情况下应该建立基于函数的索引。
    7. 索引列不能是表达式(id+1=5)的一部分,也不能是函数的参数
    8. 如果MySQL预计使用全表扫描要比使用索引快,则不使用索引

    哪些情况下需要设置索引、哪些情况下不需要

    需要:

    1).主键自动建立唯一索引
    2).频繁作为查询条件的字段应该创建索引
    3).查询中与其它表关联的字段,外键关系建立索引
    4).单键/组合索引的选择问题(在高并发下倾向创建组合索引)
    5).查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
    6).查询中统计或者分组字段

    不需要:

    1).表记录太少
    2).经常增删改的表(因为不仅要保存数据,还要保存一下索引文件) 索引本来是一种事先在写的阶段形成一定的数据结构,从而使得在读的阶段效率较高的方式,但是如果一个字段是写多读少,则会降低写的速度。
    3).数据重复且分布平均的表字段(比如性别),因此应该只为最经常查询和最经常排序的数据列建立索引。

    4).where条件里用不到的字段不创建索引

    什么情况下应该使用组合/联合索引而非单独索引

    假设有条件语句A=a AND B=b,如果A和B是两个单独的索引,在AND条件下只有一个索引起作用,对于B则要逐个判断,而如果使用组合索引(A, B),只要遍历一棵树就可以了,大大增加了效率。但是对于A=a OR B=b,由于是 或 的关系,因而组合索引是不起作用的,此时可以使用单独索引,这个时候,两个索引可以同时起作用。

    在建立联合索引的时候,如何安排索引内的字段顺序?

        评估标准是:索引的复用能力。因为可以支持最左前缀,所以当已经有了(a,b)这个联合索引后,一般就不需要单独在a上建立索引了。

        因此,第一原则是,如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的。

        那么,如果既有联合查询,又有基于a、b各自的查询呢?查询条件里面只有b的语句,是无法使用(a,b)这个联合索引的,这时候你不得不维护另外一个索引,也就是说你需要同时维护(a,b)、(b) 这两个索引。

        这时候,我们要考虑的原则就是空间了。也就是说,如果b的大小是比较小的,如boolean、int类型, 那么可以再多建立一个b索引

    下面通过一个例子来加深理解

    假设有这么一个表:

    CREATE TABLE `geek` (
      `a` int(11) NOT NULL,
      `b` int(11) NOT NULL,
      `c` int(11) NOT NULL,
      `d` int(11) NOT NULL,
      PRIMARY KEY (`a`,`b`),
      KEY `c` (`c`),
      KEY `ca` (`c`,`a`),
      KEY `cb` (`c`,`b`)
    ) ENGINE=InnoDB;

    有以下经常使用的查询语句
    select * from geek where c=N order by a limit 1;
    select * from geek where c=N order by b limit 1;

    这里我们需要思考,ca与cb索引是否都是必要的?

        索引 ca 的组织是先按c排序,再按a排序,同时记录主键(b),根据最左前缀原则,实际上,ca索引的功能同c索引的功能是差不多的,因此可以得出ca索引不是必要的

        索引 cb 的组织是先按c排序,再按b排序,同时记录主键(a),因此该索引需要保留


    MySQL中索引是如何组织数据的存储的

    假如有如下数据表:

    对于表中每一行数据,索引中包含了last_name、first_name、dob列的值,下图展示了索引是如何组织数据存储的。

    https://upload-images.jianshu.io/upload_images/175724-3ba760afbae4a52d.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/700

    可以看到,索引首先根据第一个字段来排列顺序,当名字相同时,则根据第三个字段,即出生日期来排序,正是因为这个原因,才有了索引的“最左原则”。

    普通索引和唯一索引,应该怎么选择?

    在介绍这两者的区别之前,我们先来介绍change buffer:

    什么是change buffer?
        当需要更新一个记录,就是要更新一个数据页:

        1. 如果数据页在内存中(buffer pool中时)就直接更新
        2. 如果这个数据页还没有在内存中(没有在buffer pool中)。InooDB 会将这些更新操作缓存在 change buffer 中。在下次查询需要访问这个数据页时,将数据页读入内存,然后执行 change buffer 中与这个页有关的操作
        将 change buffer 中的操作应用到原数据页,得到最新结果的过程称为 merge,它可以避免大量的磁盘随机访问I/O,merge的流程如下(并不会直接把数据写会磁盘):

               1、从磁盘读入数据页到内存(老版本的数据页);

               2、从change buffer里找出这个数据页的change buffer 记录(可能有多个),依次应用,得到新版数据页;

               3、写redo log。这个redo log包含了数据的变更和change buffer的变更。

        而唯一索引的更新就不能使用 change buffer:对于唯一索引,所有的更新操作都要先判断这个操作是否违反唯一性约束。那么必须将数据页读入内存才能判断。比如,要插入(4,400) 这个记录,就要先判断现在表中是否已经存在 k=4 的记录

        都已经读入内存中了,那直接更新内存会更快,没有必要使用change buffer了。

        所以,只有普通索引才能使用change buffer,考虑使用普通索引还是唯一索引,如果能保证不会数据重复,那么最好使用普通索引(可以使用change buffer,且两类索引查询能力没有区别)

    注意:不是所有的场景用change buffer都能加速:

        1. 设想一个对于写多读少的业务来说,change buffer 记录的变更越多越划算,例如账单类日志类

        2. 反过来,一个业务的更新模式是写入之后马上会做查询,change buffer里的内容不多,由于马上做查询要访问数据页,这样的io次数不会减少

    如果某次写入使用了change buffer机制,之后主机异常重启,是否会丢失change buffer和数据?

        虽然写入时只更新了内存,但是在事务提交的时候,change buffer的操作也会记录到redo log,所以崩溃恢复的时候,change buffer也能找回来,即数据可以找回来。

    所以普通索引和唯一索引,应该怎么选择?

        查询时:两种索引查询性能几乎没差别

        更新时:大部分场景下,因为有change buffer的存在,普通索引的更新速度会比唯一索引的快(特别适用于写多读少的场景)(如果所有的更新后面,都马上伴随着对这个记录的查询,那么应该关闭change buffer)

    扩展阅读:

    Mysql索引原理icon-default.png?t=LA92https://mp.weixin.qq.com/s/9yeModGuGvDu5S0bW9sU6w

    Mysql是如何根据索引查询数据的icon-default.png?t=LA92https://mp.weixin.qq.com/s/ymWeGlaBYWYmfogVDFHo5w

    展开全文
  • 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.总结

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

     

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

     

    展开全文
  • 数据库索引原理及优化

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

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

    千次阅读 多人点赞 2019-03-07 18:01:23
    最近在网上看到了一篇关于mysql数据库索引的好文章,认真看完之后肯定受益匪浅,(虽说有的地方我不理解)转来供大家学习交流。 另外:Ctrl C+V 好累啊,有没有快捷转载方法? 摘要 本文以MySQL数据库为研究...
  • MySql数据库索引介绍

    千次阅读 多人点赞 2019-05-27 17:08:27
    数据库索引对我们来说是透明的,因为数据库表创建索引前后,SQL语句都可以正常运行,索引的运用只是数据库引擎工作时候的优化手段。但是,这并不是说数据库索引仅仅是数据库设计开发人员和运维人员的事情,对于一个...
  • 数据库索引的利弊

    千次阅读 2019-02-13 17:49:18
    8.4.5 索引的利弊与如何判定,是否需要索引   相信读者都知道索引能够极大地提高数据检索的效率,让Query ...索引带来的益处可能很读者会认为只是"能够提高数据检索的效率,降低数据库的IO成本"。 ...
  • Java面试之数据库——数据库索引

    万次阅读 2018-05-31 11:01:02
    原文:https://blog.csdn.net/sundacheng1989/article/details/53117172最近使用到Oracle数据库索引比较,所以就想好好研究一下索引到底是什么。毕竟作为一个Application Developer,而不是DBA,所以这篇文字也...
  • 【数据库】数据库索引原理

    千次阅读 2019-06-04 08:45:40
    正确的创建合适的索引 是提升数据库查询性能的基础 文章目录1.索引是什么?2.为什么?3.索引原理B+ tree4.B+ tree 在两大引擎中的体现5.索引的原则 1.索引是什么? 索引是为了加速对表中数据行的检索而创建的一种...
  • 54、数据库索引 索引的优缺点   优点:   1、大大加快数据的检索速度;   2、创建唯一性索引,保证数据库表中每一行数据的唯一性;   3、加速表和表之间的连接;   4、在使用分组和排序子句进行数据检索时...
  • 数据库索引相关面试题

    万次阅读 多人点赞 2019-01-05 10:50:18
    1、索引的底层实现原理和优化 B+树,经过优化的B+树,主要是在所有的叶子结点中增加了指向下一个叶子节点的指针,... 数据类型出现隐式转化(如varchar不单引号的话可能会自动转换为int型)   4、简单描述mys...
  • 数据库索引到底是什么,是怎样工作的?

    万次阅读 多人点赞 2016-05-19 16:37:46
    我们通过一个简单的例子来开始教程,解释为什么我们需要数据库索引。假设我们有一个数据库表 Employee, 这个表有三个字段(列)分别是 Employee_Name、Employee_Age 和Employee_Address。假设表Employee 有上千行...
  • 数据库索引存储结构

    千次阅读 2018-11-08 17:16:08
    数据库索引存储结构 主键索引(PRIMAY KEY):  一个表中只能有一个主键,创建主键自动创建主键索引,该索引是唯一索引,其主键列的数据值不重复。建议使用INT型的自动增长主键,这样索引效率最高。 ...
  • 然而很大一部份程序员对索引的了解仅限于到“加索引能使查询变快”这个概念为止。 使用索引也很简单,然而, 会使用索引是一回事, 而深入理解索引原理又能恰到好处使用索引又是另一回事。 这已经是两个相差甚远的...
  • 数据库索引和分区

    千次阅读 2018-09-16 15:44:40
    Mysql索引/分区 创建索引 在执行CREATE TABLE语句时可以创建索引,也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。 ALTER TABLE ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。 ALTER ...
  • NoSQL 数据库索引 总结

    千次阅读 2017-06-03 16:49:14
    SQL数据库索引深度解析地址: http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html - 索引分为聚簇索引和非聚簇索引两种,聚簇索引是按照数据存放的物理位置为顺序的,而非聚簇索引就不一样了;聚簇...
  • MySQL创建数据库和创建数据表

    万次阅读 多人点赞 2019-10-20 23:44:40
    MySQL 创建数据库和创建数据表 MySQL 是最常用的数据库,在数据库操作中,基本都是增删改查操作,简称CRUD。 在这之前,需要先安装好 MySQL ,然后创建好数据库、数据表、操作用户。 一、数据库操作语言 数据库...
  • 索引也分为很种(聚集、非聚集、联合索引等),数据结构主要有哈希索引和B+树等,哈希索引在单个查询性能上很强大,但不适合做范围查询。以下讨论主要是建立在B+树索引上面的。 索引一般采用B+树的数据结构,B+树...
  • 数据库的五种索引类型

    万次阅读 多人点赞 2019-03-15 21:24:13
    本文从如何建立mysql索引以及介绍mysql的索引类型,再讲mysql索引的利与弊,以及建立索引时需要注意的地方 首先:先假设有一张表,表的数据有10W条数据,其中有一条数据是nickname='css',如果要拿这条数据的话需要些的...
  • 数据库索引类型及实现方式

    万次阅读 2017-07-12 18:43:23
    数据库索引类型及实现方式 1、索引定义  数据库索引好比是一本书前面的目录,能加快数据库的查询速度。索引是对数据库表中一个或个列(例如,employee 表的姓氏 (lname) 列)的值进行排序的结构。如果想...
  • 数据库索引的理解及适合建立索引的字段

    万次阅读 多人点赞 2018-03-06 13:17:43
    转载深入浅出数据库索引原理,哪些字段适合建立索引 问题 为什么要给表加上主键? 为什么索引后会使查询变快? 为什么索引后会使写入、修改、删除变慢? 什么情况下要同时在两个字段上建索引? 这些问题...
  • 深入理解数据库索引

    千次阅读 2019-02-14 07:19:17
    前言:数据库和数据库索引这两个东西是在服务器端开发领域应用最为广泛的两个概念,熟练使用数据库和数据库索引是后端开发人员在行业内生存的必备技能。数据库索引是用来提高数据库表的数据查询速度的。 一、索引...
  • 数据库索引及基础优化入门

    千次阅读 2018-01-04 23:17:38
    数据库索引及基本优化入门 了解华登区块狗Holy_song_myth 一前言 经常在面试中发现很多人工作了好多年了,项目经验也不少,用过各种数据库,但大都不知道这些SQL语句背后的基本原理,更别说数据库优化了。平时...
  • 数据库常见索引

    千次阅读 2019-05-31 22:16:05
    数据库索引好比是一本书前面的目录,能加快数据库的查询速度。 2.索引的优缺点 优点: 1.大大加快数据的检索速度 2.创建唯一性索引,保证数据库表中每一行数据的唯一性 3.加速表和表之间的连接 4.在使用分组和...
  • mysql数据库索引的使用及介绍

    千次阅读 2018-06-07 16:55:43
    数据库表中,对字段建立索引可以大大提高查询速度。假如我们创建了一个 mytable表:CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL ); 我们随机向里面插入了10000条记录...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 222,216
精华内容 88,886
关键字:

数据库索引加太多