您现在的位置: 365建站网 > 365学习 > mysql数据库索引的作用 原理 优缺点和用法

mysql数据库索引的作用 原理 优缺点和用法

文章来源:365jz.com     点击数:92    更新时间:2019-06-23 01:03   参与评论

 什么是索引:

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

1、为什么要创建索引呢?

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

2、也许会有人要问:增加索引有如此多的优点,为什么不对表中的每一个列创建一个索引呢?

这种想法固然有其合理性,然而也有其片面性。虽然,索引有许多优点, 但是,为表中的每一个列都增加索引,是非常不明智的。这是因为,增加索引也有许多不利的一个方面。

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

为什么要建立索引,即索引的优点:

①  建立索引的列可以保证行的唯一性,生成唯一的rowId

②  建立索引可以有效缩短数据的检索时间

③  建立索引可以加快表与表之间的连接

④  为用来排序或者是分组的字段添加索引可以加快分组和排序顺序

 索引的缺点:

①  创建索引和维护索引需要时间成本,这个成本随着数据量的增加而加大

②  创建索引和维护索引需要空间成本,每一条索引都要占据数据库的物理存储空间,数据量越大,占用空间也越大(数据表占据的是数据库的数据空间)

③  会降低表的增删改的效率,因为每次增删改索引需要进行动态维护,导致时间变长


3、索引是建立在数据库表中的某些列的上面

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

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

什么样的表跟列要建立索引:

①  总的来说就是数据量大的,经常进行查询操作的表要建立索引

②  表中字段建立索引应该遵循几个原则:

1)        越小的数据类型通常更好:越小的数据类型通常在磁盘、内存中都需要更少的空间,处理起来更快。

2)          简单的数据类型更好:整型数据比起字符,处理开销更小,因为字符串的比较更复杂,处理起来也更耗时。

3)        尽量避免NULL:应该指定列为NOT NULL。含有空值的列很难进行查询优化,因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。

4)        对非唯一的字段,例如“性别”这种大量重复值的字段,增加索引也没有什么意义,所以索引的建立应当更多的选取唯一性更高的字段。

③  表与表连接用于多表联合查询的约束条件的字段应当建立索引

④  用于排序的字段可以添加索引,用于分组的字段应当视情况看是否需要添加索引。

⑤  添加多列索引的时候,对应的多条件查询可以触发该索引的同时,索引最左侧的列的单条件查询也可以触发。

⑥  如果有些表注定只会进行查询所有,也就没必要添加索引,因为查询全部只能进行全量搜索即扫描全表。


4、同样,对于有些列不应该创建索

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

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

索引的原理:

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

索引对增删改的影响实际数据修改测试:

一个表有字段A、B、C,同时进行插入10000行记录测试

在没有建索引时平均完成时间是2.9秒

在对A字段建索引后平均完成时间是6.7秒

在对A字段和B字段建索引后平均完成时间是10.3秒

在对A字段、B字段和C字段都建索引后平均完成时间是11.7秒

从以上测试结果可以明显看出索引对数据修改产生的影响


5、创建索引的方法

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

使用CREATE INDEX语句或者使用创建索引向导来创建索引,这是最基本的索引创建方式,并且这种方法最具有柔性,可以定制创建出符合自己需要的索引。在使用这种方式 创建索引时,可以使用许多选项,例如指定数据页的充满度、进行排序、整理统计信息等,这样可以优化索引。使用这种方法,可以指定索引的类型、唯一性和复合 性,也就是说,既可以创建聚簇索引,也可以创建非聚簇索引,既可以在一个列上创建索引,也可以在两个或者两个以上的列上创建索引。

通过定义主键约束或者唯一性键约束,也可以间接创建索引。主键约束是一种保持数据完整性的逻辑,它限制表中的记录有相同的主键记录。在创建主键约束时,系 统自动创建了一个唯一性的聚簇索引。虽然,在逻辑上,主键约束是一种重要的结构,但是,在物理结构上,与主键约束相对应的结构是唯一性的聚簇索引。换句话 说,在物理实现上,不存在主键约束,而只存在唯一性的聚簇索引。同样,在创建唯一性键约束时,也同时创建了索引,这种索引则是唯一性的非聚簇索引。因此, 当使用约束创建索引时,索引的类型和特征基本上都已经确定了,由用户定制的余地比较小。

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

6、索引的特征

索引有两个特征,即唯一性索引复合索引

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

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


索引是个既稳定又开放的信息结构,它有十一种功能。

1、分解功能

把文献中的资料单元(如篇名、机构、短语、概念、物名、地名、书名、人名、字词、符号等)一一分解,这就是索引的分解功能。它是索引工作的起跑线和索引编纂的基础,没有对文献内容的这种分解功能,就没有索引。

过去有些反对索引的人说,索引是把古人的著书“凌迟碎割”。他们对索引法的反对,实出于对流传已久的那种落后的皓首穷经的陋习的偏爱和对新的治学方法的无知,洪业曾鄙视他们为卧于涸辙的鲋鱼,以升斗之水济命,而不知西江水之可羡。虽然如此,但他们所谓的索引是把古人著书“凌迟碎割”的形象说法,却从反面十分正确地道破了索引的分解功能。

分解功能是索引作用于文献的特殊功能,是它和其他检索工作不同之处。

2、梳理功能

每种文献都包容着许多不同性质的资料单元,它们在文献中基本呈无序的状态。把这些无序状态的资料单元按外表特征或内容性质进行各归其类的整理,这就是索引的梳理功能。章学诚早就发现了这种功能,他在给《族孙守一论史表》信中要求其在治二十四史年表时一并把廿二史列传中的人名编成索引,两者互为经纬,这样便可使考古之士,于纷如乱丝之资料中,忽得梳通栉理。

梳理功能是索引分解的后继。如果只有分解功能而没有梳理的功能,那么分解功能就没有价值。

梳理是对资料单元的初分。如是字序,只要按笔划或音序归类即可;如是类序只要按大类归纳即可。就像小姑娘梳头,先把长发梳顺,而编什么辫子或梳什么发型则是下一步的要求了。

3、组合功能

把梳理后的资料单元按照分类的要求,严密地组织它们的类别层次以及类目下的专题和同类目下款目的序列关系;或按字序的要求,严密地把标目的结构正装或倒装、考虑限定词对标目的限定和修饰的级数、或考虑字序和类序相结合的可能。此外,不论是类序或字序都要考虑参照系统的建立方案,使相关款目形成网络,使用户检索的眼界得以拓宽。这些,都是索引的组合功能。

过去,国外的同行曾把圣经的页边索引以“串珠”命名;我国有人曾把本草的方剂编成索引,以“针线”命名,“串珠”和“针线”是索引组合功能很形象的描绘。它使文献资料单元成为一串串的明珠,成为被针线贯穿起来的资料单元的珍品。

4、结网功能

对某个领域的文献进行有计划的索引编纂,利用类型的结构从各种不同的角度和层次对这些文献的内容进行纵横交错和多维的揭示和组合,使之形成一个检索这些文献中的各种不同性质的资料单元的网络。这就是索引的结网功能。

由“主表”和“词族索引”、“范畴索引”、“英汉对照索引”等所组成的《汉语主题词表》是由几种不同性质的索引构建的一个主题词间的联系、辨析主题词词义和被标引的文献主题概念是否精确的一个隐含的语义网络,它对文献中的资料单元产生族性检索和扩大检索途径的作用。这个网络的结构和作用就是运用索引结网功能的一个范例。

《古今图书集成》囊括了清初以前绝大部分的文献,是我国现存最大的一部类书,广西大学林仲湘等根据它的特点,分别编了经线索引、纬线索引、图表索引、人物传记索引、职方典汇考引和禽虫草木三典释名索引,从该书的各级类目和内容等不同角度交叉编结了一个严密的揭示网络,这又是索引结网功能的具体运用的例证。

5、揭示功能

分解功能只是客观地对文献进行解剖,而揭示功能有较强的目的性,它最常出现的是按专业的需要挑选某些资料单元。

在主题索引的编制中,主题概念的分析和主题词的选用,包括对隐性主题的析出是这种功能的体现。

各种专题索引或各种专业的文摘索引也是索引揭示功能的运用。清朝徐克祺称赞蔡烈先的《本草万方针线》为镜考、为烛照,这是对索引揭示功能很生动的形容。

6、鉴别功能

文献是作者思维的物化,包含了作者用以施展其聪明才智的特有的言语轨迹及其思想气质。在文献的出版史中,出于各种不同的动机和原因,真伪之作叠出,为杜伪辨真,不少人引经据典,注疏考证,一字之微,动千万言,甚至引发了校勘学的出现。近年来又产生了利用索引进行鉴别的方法,通过逐字索引分解被鉴别的文献,即同时从两种版本中分析作者用字(词)的规律和频率以甄别其真伪。

英国的女研究学家凯洛莱因·斯彼琴曾对莎士比亚的剧作进行过有趣的用词统计和分析,她发现,莎氏使用的许多诗意的语言都和大自然以及乡村生活有关。又有人在研究司马迁的《史记·货殖列传》时,发现这篇不到5000字的文章竟使用49次“富”字,由此得出,致“富”是这篇论文的主题。这些事例证明通过对文献中用词的解剖可以发现作者的特有气质和语言习惯,索引可利用这种潜在的规律辨析文献的真伪。

7、追踪功能

索引像只嗅觉灵敏的警犬一样能追踪各种领域的事物在文献中留下来的痕迹。

一部旧时代报纸的索引,能追踪那个时代的重大历史事件,并清晰地再现这些重大的历史事件脉络;一部类书的索引,能追踪许多名人的各方面的成就和言行;一种专业期刊的刊后索引,能追踪当年该专业学科研究的动态;一部跨多年度的报刊回溯索引,就是一部多功能的追踪记录。

追踪,是索引的功能之一。

8、导航功能

导航就是指引,它带有较强的评价取向,索引具有这种功能。

过去,我国有一些所谓新产品,在技术人员的积极努力下问市了,但到国际市场上却被打回票,因为这种新产品在别的国家已成老产品。技术人员的情报鼻子不灵,其中原因之一是不查阅有关的索引,不依靠这个情报导航工具,以致迷失了生产的方向。

在哲学社会科学的领域中,索引经常能提供在某个时期某个专业的理论动向和水平的第一手材料,许多科研工作者就依靠索引的导航,找到自己出发的港湾和要达到的彼岸。

洪业在《引得说》中指出:“引得者,予学者以游翔于载籍之舟车也。舟车愈善,则其所游愈广,所入愈深。”这就是索引的导航功能。

9、执简功能

您感到在信息爆炸的文献环境中很难伸展自己的科研手脚吧?那么,请您先为自己的研究对象编编索引,那就可以执简御繁了。我国的史学家顾颉刚曾说:“我常想暂不办学术研究所而先办材料整理所。”“索引,也是研究的基础的一种,它能给您一个钥匙,使您在许多头绪不清的材料中找出头绪,而得到你所需要的东西。”

国外盛行的那种快速反映科研情报的KWIC、KWOC,就是发挥索引执简功能的工具,把众多科研期刊中的关键词以最简要的格式显现,使用户能享受“索引在手,千刊掌握”的好处,而避免花去一半的科研时间在众多的资料堆中苦苦求索。因而,不少科研工作者赞誉索引有“天增岁月人增寿”的妙用。

10、检索功能

索引诞生在文献检索困难的历史背景里,它的检索功能随索引的诞生同步而生。在近二千年的索引发展史中,检索功能的变化和发展就是它的主要内容。所以,检索功能是索引最基本的功能。

任何文献记录或工作记录一旦和索引方法挂上钩,它就会从山穷水尽疑无路的困境中解脱出来,很快得到检索方便的好处,机关或企业中人事档案和工资单的人员的索引就是如此。又如,最令旅游者头疼的是,到了一个旅游点,买了地图,但要寻找一个地区、一条街道或里弄,那真像大海捞针一样的困难。当这幅地图加上了地名索引后,所要之地名便一检即得了。

11、预测功能

索引中有不少数学现象,如从引文索引中统计到被引作者的论文频率,便基本上能预测到这个专业的学术带头人的出现;从题录索引中统计的课题内容,便可预测到学术界未来研究的动向;从统计索引中出现的各种产品名称的频率,便能预测到市场将出现的热门商品。这些都是索引的预测功能。

MYSQL数据库四种索引类型的简单使用


MYSQL数据库索引类型包括普通索引,唯一索引,主键索引与组合索引,这里对这些索引的做一些简单描述:


(1)普通索引

这是最基本的MySQL数据库索引,它没有任何限制。它有以下几种创建方式:

创建索引

CREATE INDEX indexName ON mytable(username(length));

如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length,下同。

修改表结构

ALTER mytable ADD INDEX [indexName] ON (username(length)) 创建表的时候直接指定

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );

删除索引的语法:

DROP INDEX [indexName] ON mytable;

(2)唯一索引

它与前面的普通索引类似,不同的就是:MySQL数据库索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式:

创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length)) 修改表结构

ALTER mytable ADD UNIQUE [indexName] ON (username(length)) 创建表的时候直接指定

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );

(3)主键索引

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

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );

当然也可以用 ALTER 命令。记住:一个表只能有一个主键。

(4)组合索引

为了形象地对比单列索引和组合索引,为表添加多个字段:

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL );

为了进一步榨取MySQL的效率,就要考虑建立组合索引。就是将 name, city, age建到一个索引里:

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age); 建表时,usernname长度为 16,这里用 10。这是因为一般情况下名字的长度不会超过10,这样会加速索引查询速度,还会减少索引文件的大小,提高INSERT的更新速度。

如果分别在 usernname,city,age上建立单列索引,让该表有3个单列索引,查询时和上述的组合索引效率也会大不一样,远远低于我们的组合索引。虽然此时有了三个索引,但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

建立这样的组合索引,其实是相当于分别建立了下面三组组合MySQL数据库索引:

usernname,city,age usernname,city usernname 为什么没有 city,age这样的组合索引呢?这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引,下面的几个SQL就会用到这个组合MySQL数据库索引:

SELECT * FROM mytable WHREE username="admin" AND city="郑州" SELECT * FROM mytable WHREE username="admin" 而下面几个则不会用到:

SELECT * FROM mytable WHREE age=20 AND city="郑州" SELECT * FROM mytable WHREE city="郑州"



如对本文有疑问,请提交到交流论坛,广大热心网友会为你解答!! 点击进入论坛


发表评论 (92人查看0条评论)
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
用户名: 验证码: 点击我更换图片
最新评论
------分隔线----------------------------