sql如何用索引查询（查询索引sql）

sql如何用索引查询（查询索引sql）？如果你对这个不了解，来看看！

postgresql 建立索引，下面是前端全栈工程师给大家的分享，一起来看看。

sql如何用索引查询

一、索引的类型：

PostgreSQL提供了多　　种索引类型：B-Tree、Hash、GiST和GIN，由于它们使用了不同的算法，因此每种索引类型都有其适合的查询类型，缺省时，CREATE INDEX命令将创建B-Tree索引。

1. B-Tree:

CREATE TABLE test1 (

id integer,

content varchar

);

CREATE INDEX test1_id_index ON test1 (id);

B-Tree索引主要用于等于和范围查询，特别是当索引列包含操作符" <、<=、=、>=和>"作为查询条件时，PostgreSQL的查询规划器都会考虑使用B-Tree索引。在使用BETWEEN、IN、IS NULL和IS NOT NULL的查询中，PostgreSQL也可以使用B-Tree索引。然而对于基于模式匹配操作符的查询，如LIKE、ILIKE、~和 ~*，仅当模式存在一个常量，且该常量位于模式字符串的开头时，如col LIKE 'foo%'或col ~ '^foo'，索引才会生效，否则将会执行全表扫描，如：col LIKE '%bar'。

2. Hash：

CREATE INDEX name ON table USING hash (column);

散列(Hash)索引只能处理简单的等于比较。当索引列使用等于操作符进行比较时，查询规划器会考虑使用散列索引。

这里需要额外说明的是，PostgreSQL散列索引的性能不比B-Tree索引强，但是散列索引的尺寸和构造时间则更差。另外，由于散列索引操作目前没有记录WAL日志，因此一旦发生了数据库崩溃，我们将不得不用REINDEX重建散列索引。

3. GiST：

GiST索引不是一种单独的索引类型，而是一种架构，可以在该架构上实现很多不同的索引策略。从而可以使GiST索引根据不同的索引策略，而使用特定的操作符类型。

4. GIN：

GIN索引是反转索引，它可以处理包含多个键的值(比如数组)。与GiST类似，GIN同样支持用户定义的索引策略，从而可以使GIN索引根据不同的索引策略，而使用特定的操作符类型。作为示例，PostgreSQL的标准发布中包含了用于一维数组的GIN操作符类型，如：<@、@>、=、&&等。

二、复合索引：

PostgreSQL中的索引可以定义在数据表的多个字段上，如：

CREATE TABLE test2 (

major int,

minor int,

name varchar

}

CREATE INDEX test2_mm_idx ON test2 (major, minor);

在当前的版本中，只有B-tree、GiST和GIN支持复合索引，其中最多可以声明32个字段。

1. B-Tree类型的复合索引：

在B-Tree类型的复合索引中，该索引字段的任意子集均可用于查询条件，不过，只有当复合索引中的第一个索引字段(最左边)被包含其中时，才可以获得最高效率。

2. GiST类型的复合索引：

在GiST类型的复合索引中，只有当第一个索引字段被包含在查询条件中时，才能决定该查询会扫描多少索引数据，而其他索引字段上的条件只是会限制索引返回的条目。假如第一个索引字段上的大多数数据都有相同的键值，那么此时应用GiST索引就会比较低效。

3. GIN类型的复合索引：

与B-Tree和GiST索引不同的是，GIN复合索引不会受到查询条件中使用了哪些索引字段子集的影响，无论是哪种组合，都会得到相同的效率。

使用复合索引应该谨慎。在大多数情况下，单一字段上的索引就已经足够了，并且还节约时间和空间。除非表的使用模式非常固定，否则超过三个字段的索引几乎没什么用处。

三、组合多个索引：

PostgreSQL可以在查询时组合多个索引(包括同一索引的多次使用)，来处理单个索引扫描不能实现的场合。与此同时，系统还可以在多个索引扫描之间组成AND和OR的条件。比如，一个类似WHERE x = 42 OR x = 47 OR x = 53 OR x = 99的查询，可以被分解成四个独立的基于x字段索引的扫描，每个扫描使用一个查询子句，之后再将这些扫描结果OR在一起并生成最终的结果。另外一个例子是，如果我们在x和y上分别存在独立的索引，那么一个类似WHERE x = 5 AND y = 6的查询，就会分别基于这两个字段的索引进行扫描，之后再将各自扫描的结果进行AND操作并生成最终的结果行。

为了组合多个索引，系统扫描每个需要的索引，然后在内存里组织一个BITMAP，它将给出索引扫描出的数据在数据表中的物理位置。然后，再根据查询的需要，把这些位图进行AND或者OR的操作并得出最终的BITMAP。最后，检索数据表并返回数据行。表的数据行是按照物理顺序进行访问的，因为这是位图的布局，这就意味着任何原来的索引的排序都将消失。如果查询中有ORDER BY子句，那么还将会有一个额外的排序步骤。因为这个原因，以及每个额外的索引扫描都会增加额外的时间，这样规划器有时候就会选择使用简单的索引扫描，即使有多个索引可用也会如此。

四、唯一索引：

目前，只有B-Tree索引可以被声明为唯一索引。

CREATE UNIQUE INDEX name ON table (column [, ...]);

如果索引声明为唯一索引，那么就不允许出现多个索引值相同的行。我们认为NULL值相互间不相等。

五、表达式索引：

表达式索引主要用于在查询条件中存在基于某个字段的函数或表达式的结果与其他值进行比较的情况，如：

SELECT * FROM test1 WHERE lower(col1) = 'value';

此时，如果我们仅仅是在col1字段上建立索引，那么该查询在执行时一定不会使用该索引，而是直接进行全表扫描。如果该表的数据量较大，那么执行该查询也将会需要很长时间。解决该问题的办法非常简单，在test1表上建立基于col1字段的表达式索引，如：

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

如果我们把该索引声明为UNIQUE，那么它会禁止创建那种col1数值只是大小写有区别的数据行，以及col1数值完全相同的数据行。因此，在表达式上的索引可以用于强制那些无法定义为简单唯一约束的约束。现在让我们再看一个应用表达式索引的例子。

SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';

和上面的例子一样，尽管我们可能会为first_name和last_name分别创建独立索引，或者是基于这两个字段的复合索引，在执行该查询语句时，这些索引均不会被使用，该查询能够使用的索引只有我们下面创建的表达式索引。

CREATE INDEX people_names ON people ((first_name || ' ' || last_name));

CREATE INDEX命令的语法通常要求在索引表达式周围书写圆括弧，就像我们在第二个例子里显示的那样。如果表达式只是一个函数调用，那么可以省略，就像我们在第一个例子里显示的那样。

从索引维护的角度来看，索引表达式要相对低效一些，因为在插入数据或者更新数据的时候，都必须为该行计算表达式的结果，并将该结果直接存储到索引里。然而在查询时，PostgreSQL就会把它们看做WHERE idxcol = 'constant'，因此搜索的速度等效于基于简单索引的查询。通常而言，我们只是应该在检索速度比插入和更新速度更重要的场景下使用表达式索引。

六、部分索引：

部分索引(partial index)是建立在一个表的子集上的索引，而该子集是由一个条件表达式定义的(叫做部分索引的谓词)。该索引只包含表中那些满足这个谓词的行。

由于不是在所有的情况下都需要更新索引，因此部分索引会提高数据插入和数据更新的效率。然而又因为部分索引比普通索引要小，因此可以更好的提高确实需要索引部分的查询效率。见以下三个示例：

1. 索引字段和谓词条件字段一致：

CREATE INDEX access_log_client_ip_ix ON access_log(client_ip)

WHERE NOT (client_ip > inet '192.168.100.0' AND client_ip < inet '192.168.100.255');

下面的查询将会用到该部分索引：

SELECT * FROM access_log WHERE url = '/index.html' AND client_ip = inet '212.78.10.32';

下面的查询将不会用该部分索引：

一个不能使用这个索引的查询可以是∶

SELECT * FROM access_log WHERE client_ip = inet '192.168.100.23';

2. 索引字段和谓词条件字段不一致：

PostgreSQL支持带任意谓词的部分索引，唯一的约束是谓词的字段也要来自于同样的数据表。注意，如果你希望你的查询语句能够用到部分索引，那么就要求该查询语句的条件部分必须和部分索引的谓词完全匹配。 准确说，只有在PostgreSQL能够识别出该查询的WHERE条件在数学上涵盖了该索引的谓词时，这个部分索引才能被用于该查询。

CREATE INDEX orders_unbilled_index ON orders(order_nr) WHERE billed is not true;

下面的查询一定会用到该部分索引：

SELECT * FROM orders WHERE billed is not true AND order_nr < 10000;

那么对于如下查询呢？

SELECT * FROM orders WHERE billed is not true AND amount > 5000.00;

这个查询将不像上面那个查询这么高效，毕竟查询的条件语句中没有用到索引字段，然而查询条件"billed is not true"却和部分索引的谓词完全匹配，因此PostgreSQL将扫描整个索引。这样只有在索引数据相对较少的情况下，该查询才能更有效一些。

下面的查询将不会用到部分索引。

SELECT * FROM orders WHERE order_nr = 3501;

3. 数据表子集的唯一性约束：

CREATE TABLE tests (

subject text,

target text,

success boolean,

...

);

CREATE UNIQUE INDEX tests_success_constraint ON tests(subject, target) WHERE success;

该部分索引将只会对success字段值为true的数据进行唯一性约束。在实际的应用中，如果成功的数据较少，而不成功的数据较多时，该实现方法将会非常高效。

七、检查索引的使用：

见以下四条建议：

1. 总是先运行ANALYZE。

该命令将会收集表中数值分布状况的统计。在估算一个查询返回的行数时需要这个信息，而规划器则需要这个行数以便给每个可能的查询规划赋予真实的开销值。如果缺乏任何真实的统计信息，那么就会使用一些缺省数值，这样肯定是不准确的。因此，如果还没有运行ANALYZE就检查一个索引的使用状况，那将会是一次失败的检查。

2. 使用真实的数据做实验。

用测试数据填充数据表，那么该表的索引将只会基于测试数据来评估该如何使用索引，而不是对所有的数据都如此使用。比如从100000行中选1000行，规划器可能会考虑使用索引，那么如果从100行中选1行就很难说也会使用索引了。因为100行的数据很可能是存储在一个磁盘页面中，然而没有任何查询规划能比通过顺序访问一个磁盘页面更加高效了。与此同时，在模拟测试数据时也要注意，如果这些数据是非常相似的数据、完全随机的数据，或按照排序顺序插入的数据，都会令统计信息偏离实际数据应该具有的特征。

3. 如果索引没有得到使用，那么在测试中强制它的使用也许会有些价值。有一些运行时参数可以关闭各种各样的查询规划。

4. 强制使用索引用法将会导致两种可能：一是系统选择是正确的，使用索引实际上并不合适，二是查询计划的开销计算并不能反映现实情况。这样你就应该对使用和不使用索引的查询进行计时，这个时候EXPLAIN ANALYZE命令就很有用了。

查询索引sql

表的存储结构

表 -> 分区 -> 堆或B+树 -> 页(数据页、索引页、LOB页，溢出页)

表默认只有一个分区（sql server的分区表技术，可以将表进行水平拆分，这样就会产生多个分区）。 分区里面就是存储的数据，有两种存储形式：堆或B+树，具体结构下面细说。、

页

页是数据存储的最小单位。 页类型分为：数据页、索引页、Log_mixed_page、Lob_tree_page、IAM页面

一个数据页可以存储8K（8192字节，减去96字节的头）大小的数据。数据页里面就是数据行，数据行不能跨页。

疑问：那一行数据可以超过8K吗，超过8K不就跨页了吗？

sql server 2000会有这个限制。sql server 2005 突破了每行8K的限制 但是sql server列的大小，仍不能超过8K（比如你不能定义varchar(9000) 或者 nvarchar(5000)）；

如果一行数据超出了8K，那么超出8K的字段会存到溢出页上，原数据行上有个指针指向到溢出页。

有人可能会说 varchar(max)、nvarchar(max)、text、image这种类型，其实不然，这种类型是LOB类型。

LOB(large object)是一种用于存储大对象的数据类型，每个LOB可以有2GB。LOB列可以跨多页，并且页不一定是连续的。

区(又叫扩展区)

区(又叫扩展区)是页的集合，一个区包括了8个页，区大小是64K。 注意：这里的区，不是表分区。每个表默认只有一个表分区。

堆结构

堆是一个没有聚集索引的表。表中的数据不按任何字段排序。 用"索引分配映射(IAM)"页将堆的页面联系在一起。如下图所示：

堆内的数据页和行没有任何特定的顺序；页面也不链接在一起，数据页之间唯一的逻辑连接是记录在IAM页内的信息， 页面与页面之间没有什么紧密的联系；用IAM页查找数据页集合中的每一页。 从数据存储管理上来讲，用堆去管理一个超大的表格是比较吃力的，经常使用的表格上都建立聚集索引。

可以通过扫描 IAM 页对堆进行表扫描或串行读操作来找到容纳该堆的页的扩展盘区。 因为 IAM 按扩展盘区在数据文件内存在的顺序表示它们，所以这意味着串行堆扫描连续沿每个文件进行。使用 IAM 页设置扫描顺序还意味着堆中的行一般不按照插入的顺序返回。

索引

MSSQL的索引存储结构是B+树，这是一种平衡多叉树。

B树和B+树的区别

B树的索引节点里面除了键值和指针之外，还有行数据。

B+树的索引节点里面，只有键值和指针。b+树的叶节点是个双向链表，范围查找非常快速。

为什么B+树更适合做索引？

1，因为页容量是固定的，所以B+树能容纳更多的索引值，那么索引深度就相对较浅，查找性能会更好。

2，b+树叶级节点之间，是个双向链表，范围查找很快。

B+树结构：

当一个表上加了聚集索引后，其结构即成了一个B+树，数据记录成了B+树的一部分。 数据的物理顺序按索引字段的顺序来排列，因为物理排列顺序肯定是只有一种的，所以表上只能添加一个聚集索引。

聚集索引

下图是一个单字段聚集索引的存储结构图(假设是在Name上加的聚集索引)

聚集索引是以B树结构存储的。根节点和中间节点都是索引页，叶子节点是数据页。当表加了聚集索引的话，数据就不是按堆存储了，而是按B树结构存储的，数据记录成了B树的一部分，是B树的叶子节点。索引页里面包含的是索引行，索引行由索引键值和指针构成，指针指向的是下一级索引的页ID。如果下一级是数据页，则指向的就是数据页ID（不是数据行的ID）。数据页里面包含的就是数据行，如果数据行大小超过8060字节，那么超出的部分会存到溢出页，此行数据会有一个指针指向溢出页。上面的图有一个缺陷（页之间的关联没有标明），相同层级的索引页之间是相互关联的，是个双向链表，每个索引页都有指针指向上下一页。数据页也是一个双向链表，都会指上一页和下一页。数据在物理上不一定是连续的，但是在逻辑上一定是连续的。所以范围查询的时候是很快的。数据是有序的，按照聚集索引字段的顺序来排列，所以一个表只能有一个聚集索引。如上图所示最右边的数据记录很明显可以看出是按照Name升序来排列的。B+树的查找方式：如上图数据所示，假设要查找Name=Greene的记录从根节点开始查找： >= ‘Bennet’ 且 < ‘Karsen’ 的数据 --> 进入索引页1007 （Greene的记录应该再查找此页） >= ‘Karsen’ 且 < ‘Smith’ 的数据 --> 进入索引页1009 >= ‘Smith’ 且 < xxxxxxx 的数据 --> 进入索引页1062 再从中间节点索引页1007查找： >= ‘Bennet’ 且 < ‘Greane’ 的数据 --> 进入数据页1132 >= ‘Greane’ 且 < ‘Hunter’ 的数据 --> 进入数据页1133 （Greene的记录应该再查找此页） >= ‘Hunter’ 且 < xxxxxxxx 的数据 --> 进入数据页1127 最后从数据页1133中取得Name=Greene的这行记录复制代码

根节点的索引键值是如何决定的？

1，根节点里面的存储索引键值是如何决定的？为什么是zhangsan，而不是lisi或者其他？

取每个数据页的第一条的索引键值，向上形成索引页。再用最底层的每个索引页的第一条向上形成索引页，这样依次向上推，直到根节点。这样根节点的索引键值就出来了

PS：即第一条记录肯定是在根节点里面的，下面的DBCC分析也佐证了这点。

索引的层数如何决定的？

假设某表里1亿行数据，并且这1亿行数据刚好构成了1000万个数据页，

聚集索引字段是个Int型字段(Int类型为4字节)，一个索引页只能存储8K（8060字节）数据的：

那么数据页上层需要 4000万字节/8060字节=4963个索引页。 （因为索引指向是索引页的ID，所以数据页上层的索引只需要4000万字节）再上一层(4963*4)/8060 = 3个索引页再上一层1个索引页即可，至此就是根节点了。

索引的层数（即索引深度）是由索引键的大小和数量决定的。

组合聚集索引的结构

在索引行里会有多个索引键的值，如下图所示。下图截取自DBCC分析的内容

非聚集索引

非聚集索引和聚集索引的区别是：叶级不再是数据页，即数据不再是索引结构的一部分。

非聚集索引的叶级存储的内容是什么呢？ 可以分为两种情况来讨论：堆表上的非聚集索引、聚集表(即有聚集索引的表)上的非聚集索引 其叶级内容是不一样的。

堆上的非聚集索引

如上图所示，堆表上的非聚集索引叶级节点里，行存储的是索引键值和RID

（行ID，即数据页面里面的数据行的ID）。 这种RID由索引指向的特定行的区段、页以及行偏移量构成。即叶级不是实际的数据，使用叶级也仅仅比使用聚集索引多一个步骤。 因为RID具有行的位置的全部信息，所以可以直接到达数据。差了一个步骤，实际上差别的系统开销是很大的。 因为叶级节点里只存了索引键值和RID，这意味着每个页能够包含的行比聚集索引单个页节点包含的行更多。 根据行ID，可以加载此行所在的数据页来读取数据。sql server 读取数据是以页为单位的，即使只读取一行，也要加载整个数据页

B+树上的非聚集索引

如上图所示，叶级节点里存储的是，非聚集索引的键值 和 聚集索引的键值。 根据非聚集索引查询时，先根据非聚集索引的键值来一步一步定位到叶级节点里的索引行，在根据此行内的聚集索引键值，去到索引键值里面查找（也是从根节点一步一步开始查）。

注意，如果表没有聚集索引，建立了非聚集索引，那么非聚集索引使用的是行号，如果此时你又添加了聚集索引，那么所有的非聚集索引引用的RID都要改为聚集索引键。这对性能的消耗是非常大的，因此最好先建立聚集索引，在建立非聚集索引。

1，聚集表上的非聚集索引，叶级节点为什么不再使用RID来定位记录，而要使用聚集索引的键值来定位？

如果使用RID定位的话，如果数据页发生了页拆分，那么新拆分出来的半页数据，索引里面原来对应这个半页数据行的RID，全部要更新为新的RID。

这个过程的效率是十分低下的，因为这半页数据行的非聚集索引，一般都不是在一起的，都是分散在个索引页里面的，查找起来效率很低。 而存储聚集索引键值的话，即使聚集索引发生了数据页拆分，对非聚集索引也没有影响。

优缺点

缺点：查找效率相对低下，因为非聚集索引查找完后，还得根据聚集索引查一轮。优点：真正的数据定位是使用的聚集索引键值，而不是RID。这样发生数据页拆分时，也不用影响非聚集索引。常见问题分析

使用索引字段查询为什么会提供效率？

因为不再是表扫描，而是使用索引查找，呈几何式提高效率

索引会使得，增、删、改的效率降低吗？具体是如何影响的？

新增

对新增操作来说，效率确实会降低，因为实实在在是多了一步更新索引的操作。新增操作带来的效率影响，更多是在页拆分操作上面。对聚集索引来说：如果聚集索引键值不是有序递增的，数据可能会在数据页的中间插入，这样会导致数据页拆分（也可能会级联向上导致各级索引页的拆分）。页拆分会导致内部碎片和外部碎片，如果外部碎片过多，范围查找时会导致顺序IO变为了随机IO(磁盘悬臂来回移动读取数据)，效率很低下。页拆分以及向上的级联反应，确实是比较低效的，所以聚集索引字段的趋势有序是十分必要的。对非聚集索引来说：只要聚集索引是有序的，那么数据页拆分是比较少的。但是新增的数据依然有可能导致索引页的拆分， 这种可能性无法避免，因为不可能把非聚集索引键值也设计为趋势有序递增的。页拆分详见后面，后面再写

修改

如果修改了索引字段的值，才会导致索引更新。如果没有修改索引字段的话，效率我理解应该是不会降低的

删除

删除了数据，需要更新索引，从而降低效率。

PS:对修改和删除操作来说，虽然更新索引对效率有一定影响，但是要UPDATE或DELETE一行的前提是必须找到一行，因此索引实际上对于有复杂WHERE条件的UPDATE或DELETE也有帮助的。在使用索引定位一行的有效性通常能弥补更新索引所带来的额外开销，除非索引设计不合理。

索引