办公室设计公司专业网站,浙江工程信息网,网站上的动态背景怎么做的,网站建设项目进展情况前言上一次#xff0c;我们从优化子查询的角度#xff0c;讲解了一些简单的数据库性能优化方面的知识。通过优化子查询的顺序#xff0c;包括合理使用IN和EXISTS#xff0c;可以起到部分查询的效率提升。但对于其他大多数场景#xff0c;如单表记录很大#xff0c;或多表… 前言上一次我们从优化子查询的角度讲解了一些简单的数据库性能优化方面的知识。通过优化子查询的顺序包括合理使用IN和EXISTS可以起到部分查询的效率提升。但对于其他大多数场景如单表记录很大或多表级联查询包含条件或排序时子查询的优化往往起不到决定性的效果。所以从今天开始我们要逐渐接触这一部分的主角索引。这部分内容我会分为上中下三篇进行详细的讲解上篇会着重于索引的概念以及基础的使用方式中篇会讲解索引背后的实现原理便于理解索引如何发挥作用下篇会结合一些复杂的SQL场景来描述索引在实际工作中如何使用。概述数据库中的索引就像一本书的目录它可以帮我们快速定位和查找从而加快数据查询的效率。如果我们不使用索引那么记录就必须从表中第一行开始进行逐行扫描直到找出符合条件的记录为止这样的查询效率必然是很低的。那么索引是不是建立得越多就越好呢答案是否定的因为索引也是一种存储在数据库硬盘中的数据结构会占用一定的空间。而且对于数据的新增、修改和删除时数据库也需要对其相关的索引进行更新进而降低了整体效率。索引的类型了解了索引的基本概念之后我们还需要知道不同索引的用途。索引从功能逻辑上可以分为四类普通索引、唯一索引、主键索引和全文索引。而在物理实现上可以分为两类聚集索引和非聚集索引。下面我们来逐一讲解1、普通索引最基础的索引没有任何约束直接建立即可用于提升查找的效率。2、唯一索引在普通索引的基础上加入了数据唯一性的约束用于检查数据是否唯一在同一张数据表中可以有多个唯一索引。3、主键索引在唯一索引的基础上加入了NOT NULL的特性在我们给数据库表设置主键时会自动创建主键索引而根据主键的特性一个表中只能有一个主键索引。4、全文索引一种特殊的基于标记的索引由数据库引擎维护用于快速查找某个字符出现的位置较少使用。目前此种查找一般会使用搜索引擎实现如ES(ElasticSearch)。5、聚集索引确定了数据存储的顺序在物理上是连续存储的因此聚集索引在每个表中只能有一个。在默认情况下数据库会对主键约束自动创建聚集索引这就是数据表中的行记录通常按照主键排列的原因。6、非聚集索引在数据库中有单独的空间进行存储索引项本身是按照顺序存储的但是索引指向的内容却是随机存储的。也就是说非聚集索引在工作时系统会进行两次查找第一次会找到索引本身第二次则根据索引对应的位置找出数据行。非聚集索引不会把索引指向的内容像聚集索引一样直接放到索引后面而是维护单独的索引表为数据的检索提供方便。由于实现原理的不同非聚集索引在每个表中可以有多个。索引的应用现在我们来看一下索引使用的实际效果目前我有一张表T_ORG_USER其中大约有3000条数据。这时我们需要查询一条记录如下我们可以使用主键Id和用户名分别查询同一条记录然后查看查询记录的区别首先我们使用主键Id进行查询SET STATISTICS TIME ON
SELECT * FROM T_ORG_USER WHERE F_USER_ID 8400查询结果如下图所示执行时间为0毫秒此时我们再使用用户名进行查询用户名字段没有建立索引SET STATISTICS TIME ON
SELECT * FROM T_ORG_USER WHERE F_NAME 24查询结果如下图所示执行时间为196毫秒从执行时间上我们可以看出二者有明显的效率差别这时我们再对F_NAME字段建立索引CREATE INDEX IDX_USERNAME ON T_ORG_USER(F_NAME)再次查询结果如图所示执行时间缩短到了5毫秒从上述三次的执行结果中我们可以总结出以下两点1、对WHERE条件后的字段进行索引可以大幅度提高查询的效率2、采用聚集索引的查询效率比采用非聚集索引的查询效率略高。在我们上述的例子中第一次使用主键进行查询系统会使用聚集索引进行查找而第三次我们使用非聚集索引进行查找效率会降低。因此在需要多次查询的场景下我们的SQL语句应尽量使用主键索引进行查询。除了我们之前描述的几种索引类型之外索引还可以根据字段个数分为单一索引和联合索引。单一索引是指使用单个列创建的索引如我们上述的IDX_USERNAME所示而把多个列组合在一起创建的索引叫做联合索引。创建联合索引时我们需要注意字段的顺序问题因为字段(a,b,c)和字段(b,a,c)创建出的联合索引在使用时查询效率可能会不同。导致这样的原因是由于联合索引存在最左匹配原则也就是说如果我们创建了联合索引(a,b,c)时WHERE条件如果为WHERE a 1 AND b 2 AND c 3则会匹配上联合索引如果条件为WHERE b 2那么联合索引会失效查询就会走全表扫描的方式去查找。还有一些范围查找操作也会导致联合索引失效如果某一列使用了between等那么此列后面的列就无法使用到索引。总结今天我们讲述了索引的基本概念索引的类型以及索引的基本使用方式。合理使用索引可以帮助我们提升查询效率但索引也存在一些缺点如单独占用空间降低数据库的写操作性能等所以我们在使用索引时需要权衡索引的利与弊。在实际工作中我们也要根据查询的业务逻辑来决定如何建立索引牢记最左匹配原则适当对语句进行改写以便于索引生效也可以大大提升数据查询的性能。好了今天我们要讲的内容到这里就结束了如果有什么疑问或者启发欢迎大家在评论区进行留言。下一期我们会从索引背后的原理B树和B树的算法实现进行讲解敬请期待您的点赞和在看是我创作的最大动力感谢支持公众号wacky的碎碎念知乎wacky
