网站预算表怎么做,wordpress文字可以动的插件,seo软件排行榜前十名,网站做半透明度的优势参考《PostgreSQL11.2-中文手册》下面这个链接#xff0c;讲的通俗易懂#xff0c;可以看看。数据分析师不得不知道的SQL优化 - 鑫获 - 博客园www.cnblogs.com索引是提高数据库性能的常用途径。比起没有索引#xff0c;使用索引可以让数据库服务器更快找到并获取特定行。但…参考《PostgreSQL11.2-中文手册》下面这个链接讲的通俗易懂可以看看。数据分析师不得不知道的SQL优化 - 鑫获 - 博客园www.cnblogs.com索引是提高数据库性能的常用途径。比起没有索引使用索引可以让数据库服务器更快找到并获取特定行。但是索引同时也会增加数据库系统的日常管理负担因此我们应该聪明地使用索引。简介CREATE TABLE test1 (
id integer,
content varchar)假设对上表做出如下查询SELECT content FROM test1 WHERE id constant在没有事前准备的情况下系统不得不扫描整个test1表一行一行地去找到所有匹配的项。如果test1中有很多行但是只有一小部分行可能是0或者1需要被该查询返回这显然是一种低效的方式。但是如果系统被指示维护一个在id列上的索引它就能使用一种更有效的方式来定位匹配行。例如它可能仅仅需要遍历一棵搜索树的几层而已。类似的方法也被用于大部分非小说书籍中经常被读者查找的术语和概念被收集在一个字母序索引中放在书籍的末尾。感兴趣的读者可以相对快地扫描索引并跳到合适的页而不需要阅读整本书来寻找感兴趣的材料。正如作者的任务是准备好读者可能会查找的术语一样数据库程序员也需要预见哪些索引会有用。正如前面讨论的下列命令可以用来在id列上创建一个索引CREATE INDEX test1_id_index ON test1 (id);索引的名字test1_id_index可以自由选择但我们最好选择一个能让我们想起该索引用途的名字。为了移除一个索引可以使用DROP INDEX命令。索引可以随时被创建或删除一旦一个索引被创建就不再需要进一步的干预系统会在表更新时更新索引而且会在它觉得使用索引比顺序扫描表效率更高时使用索引。但我们可能需要定期地运行ANALYZE命令来更新统计信息以便查询规划器能做出正确的决定。通过Chapter 14 的信息可以了解如何找出一个索引是否被使用以及规划器在何时以及为什么会选择不使用索引。索引也会使带有搜索条件的UPDATE和DELETE命令受益。此外索引还可以在连接搜索中使用。因此一个定义在连接条件列上的索引可以显著地提高连接查询的速度。在一个大表上创建一个索引会耗费很长的时间。默认情况下PostgreSQL允许在索引创建时并行地进行读SELECT命令但写INSERT、UPDATE和DELETE则会被阻塞直到索引创建完成。在生产环境中这通常是不可接受的。在创建索引时允许并行的写是可能的但是有些警告需要注意更多信息可以参考并发构建索引。一个索引被创建后系统必须保持它与表同步。这增加了数据操作的负担。因此哪些很少或从不在查询中使用的索引应该被移除。索引类型PostgreSQL提供了多种索引类型 B-tree、Hash、GiST、SP-GiST 、GIN 和 BRIN。每一种索引类型使用了 一种不同的算法来适应不同类型的查询。B-tree 索引默认情况下 CREATE INDEX命令创建适合于大部分情况的B-tree 索引。B-tree可以在可排序数据上的处理等值和范围查询。特别地PostgreSQL的查询规划器会在任何一种涉及到以下操作符的已索引列上考虑使用B-tree索引将这些操作符组合起来例如BETWEEN和IN也可以用B-tree索引搜索实现。同样在索引列上的IS NULL或IS NOT NULL条件也可以在B-tree索引中使用。优化器也会将B-tree索引用于涉及到模式匹配操作符LIKE和~ 的查询前提是如果模式是一个常量且被固定在字符串的开头—例如col LIKE foo%或者col ~ ^foo, 但在colLIKE %bar上则不会。但是如果我们的数据库没有使用C区域设置我们需要创建一个具有特殊操作符类的索引来支持模式匹配查询参见下面的Section 11.10 。同样可以将Btree索引用于ILIKE和~*但仅当模式以非字母字符开始即不受大小写转换影响的字符。B-tree索引也可以用于检索排序数据。这并不会总是比简单扫描和排序更快但是总是有用的。Hash索引Hash索引只能处理简单等值比较。不论何时当一个索引列涉及到一个使用了操作符的比较时查询规划器将考虑使用一个Hash索引。CREATE INDEX name ON table USING HASH (column)GiST索引GiST索引并不是一种单独的索引而是可以用于实现很多不同索引策略的基础设施。相应地可以使用一个GiST索引的特定操作符根据索引策略操作符类而变化。作为一个例子PostgreSQL的标准捐献包中包括了用于多种二维几何数据类型的GiST操作符类它用来支持使用下列操作符的索引化查询|
|
|
|~
这些操作符的含义见Section 9.11 Table 64.1 中给出了标准发布中所包括的 GiST 操作符类。contrib集合中还包括了很多其他GiST操作符类可见Chapter 64 。GiST索引也有能力优化“最近邻”搜索例如SELECT * FROM places ORDER BY location - point (101,456) LIMIT 10;它将找到离给定目标点最近的10个位置。能够支持这种查询的能力同样取决于被使用的特定操作符类。 在Table 64.1中“Ordering Operators”列中列出了可以在这种方法中使用的操作符。SP-GiST索引GiST相似SP-GiST索引为支持多种搜索提供了一种基础结构。SPGiST 允许实现众多不同的非平衡的基于磁盘的数据结构例如四叉树、k-d树和radix树。作为一个例 子PostgreSQL的标准捐献包中包含了一个用于二维点的SP-GiST操作符类它用于支持使用下列操作符的索引化查询~^
^GIN 索引GIN 索引是“倒排索引”它适合于包含多个组成值的数据值例如数组。倒排索引中为每一个组成值都包含一个单独的项它可以高效地处理测试指定组成值是否存在的查询。与 GiST 和 SP-GiST相似 GIN 可以支持多种不同的用户定义的索引策略并且可以与一个 GIN 索引配合使用的特定操作符取决于索引策略。作为一个例子PostgreSQL的标准贡献包中包含了用于数组的GIN操作符类它用于支持使用下列操作符的索引化查询BRIN 索引BRIN 索引块范围索引的缩写存储有关存放在一个表的连续物理块范围上的值摘要信息。与 GiST、SP-GiST 和 GIN 相似BRIN 可以支持很多种不同的索引策略并且可以与一个 BRIN 索引配合使用的特定操作符取决于索引策略。对于具有线性排序顺序的数据类型被索引的数据对应于每个块范围的列中值的最小值和最大值使用这些操作符来支持用到索引的查询多列索引一个索引可以定义在表的多个列上CREATE INDEX test2_mm_idx ON test2 (major, minor);目前只有 B-tree、GiST、GIN 和 BRIN 索引类型支持多列索引最多可以指定32个列该限制可以在源代码文件pg_config_manual.h中修改但是修改后需要重新编PostgreSQL。B-tree索引一个B-tree索引可以用于条件中涉及到任意索引列子集的查询但是当先导列即最左边的那些列上有约束条件时索引最为有效。确切的规则是在先导列上的等值约束加上第一个无等值约束的列上的不等值约束将被用于限制索引被扫描的部分。在这些列右边的列上的约束将在索引中被检查这样它们适当节约了对表的访问但它们并未减小索引被扫描的部分。例如在(a, b, c)上有一个索引并且给定一个查询条件WHERE a 5 AND b 42 AND c 77对索引的扫描将从第一个具有a 5和b 42的项开始向上进行直到最后一个具有a 5的项。在扫描过程中具有c 77的索引项将被跳过但是它们还是会被扫描到。这个索引在原则上可以被用于在b和/或c上有约束而在a上没有约束的查询但是整个索引都不得不被扫描因此在大部分情况下规划器宁可使用一个顺序的表扫描来替代索引。多列GiST索引一个多列GiST索引可以用于条件中涉及到任意索引列子集的查询。在其余列上的条件将限制由索引返回的项但是第一列上的条件是决定索引上扫描量的最重要因素。当第一列中具有很少的可区分值时一个GiST索引将会相对比较低效即便在其他列上有很多可区分值。其他多列索引一个GIN索引可以用于条件中涉及到任意索引列子集的查询。与B-tree和GiST不同GIN的搜索效率与查询条件中使用哪些索引列无关。多列 BRIN 索引可以被用于涉及该索引被索引列的任意子集的查询条件。和 GIN 相似且不同于 B-树 或者 GiST索引搜索效率与查询条件使用哪个索引列无关。在单个表上使用多个BRIN 索引来取代一个多列 BRIN 索引的唯一原因是为了使用不同的pages_per_range存储参数。当然要使索引起作用查询条件中的列必须要使用适合于索引类型的操作符使用其他操作符的子句将不会被考虑使用索引。多列索引应该较少地使用。在绝大多数情况下单列索引就足够了且能节约时间和空间。具有超过三个列的索引不太有用除非该表的使用是极端程式化的。Section 11.5 以及Section 11.9 中有对不同索引配置优点的讨论。索引和ORDER BY除了简单地查找查询要返回的行外一个索引可能还需要将它们以指定的顺序传递。这使得查询中的ORDER BY不需要独立的排序步骤。在PostgreSQL当前支持的索引类型中只有Btree可以产生排序后的输出其他索引类型会把行以一种没有指定的且与实现相关的顺序返回。规划器会考虑以两种方式来满足一个ORDER BY说明扫描一个符合说明的可用索引或者先以物理顺序扫描表然后再显式排序。对于一个需要扫描表的大部分的查询一个显式的排序很可能比使用一个索引更快因为其顺序访问模式使得它所需要的磁盘I/O更少。只有在少数行需要被取出时索引才会更有用。一种重要的特殊情况是ORDER BY与LIMIT n联合使用一个显式的排序将会处理所有的数据来确定最前面的n行但如果有一个符合ORDER BY的索引前n行将会被直接获取且根本不需要扫描剩下的数据。默认情况下B-tree索引将它的项以升序方式存储并将空值放在最后。这意味着对列x上索引的一次前向扫描将产生满足ORDER BY x或者更长的形式ORDER BY x ASC NULLSLAST的结果。索引也可以被后向扫描产生满足ORDER BY x DESCORDER BY x DESC NULLSFIRST NULLS FIRST是ORDER BY DESC的默认情况。我们可以在创建B-tree索引时通过ASC、DESC、NULLS FIRST和NULLS LAST选项来改变索引的排序例如CREATE INDEX test2_info_nulls_low ON test2 (info NULLS FIRST);CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST);一个以升序存储且将空值前置的索引可以根据扫描方向来支持ORDER BY x ASC NULLS FIRST或ORDER BY x DESC NULLS LAST。读者可能会疑惑为什么要麻烦地提供所有四个选项因为两个选项连同可能的后向扫描可以覆盖所有ORDER BY的变体。在单列索引中这些选项确实有冗余但是在多列索引中它们却很有用。考虑(x, y)上的一个两列索引它可以通过前向扫描满足ORDER BY x, y或者通过后向扫描满足ORDER BY x DESC, y DESC。但是应用可能需要频繁地使用ORDER BY x ASC,y DESC。这样就没有办法从通常的索引中得到这种顺序但是如果将索引定义为(x ASC, yDESC)或者(x DESC, y ASC)就可以产生这种排序。显然具有非默认排序的索引是相当专门的特性但是有时它们会为特定查询提供巨大的速度提升。是否值得维护这样一个索引取决于我们会多频繁地使用需要特殊排序的查询。组合多个索引只有查询子句中在索引列上使用了索引操作符类中的操作符并且通过AND连接时才能使用单一索引。例如给定一个(a, b) 上的索引查询条件WHERE a 5 AND b 6可以使用该索引而查询WHERE a 5 OR b 6不能直接使用该索引。幸运的是PostgreSQL具有组合多个索引包括多次使用同一个索引的能力来处理那些不能用单个索引扫描实现的情况。系统能在多个索引扫描之间安排AND和OR条件。例如WHERE x 42 OR x 47 OR x 53 OR x 99这样一个查询可以被分解成为四个独立的在x上索引扫描每一个扫描使用其中一个条件。这些查询的结果将被“或”起来形成最后的结果。另一个例子是如果我们在x和y上都有独立的索引WHERE x 5 AND y 6这样的查询的一种可能的实现方式就是分别使用两个索引配合相应的条件然后将结果“与”起来得到最后的结果行。为了组合多个索引系统扫描每一个所需的索引并在内存中准备一个位图用于指示表中符合索引条件的行的位置。然后这些位图会被根据查询的需要“与”和“或”起来。最后实际的表行将被访问并返回。表行将被以物理顺序访问因为位图就是以这种顺序布局的。这意味着原始索引中的任何排序都会被丢失并且如果存在一个ORDER BY子句就需要一个单独的排序步骤。由于这个原因以及每一个附加的索引都需要额外的时间即使有额外的索引可用规划器有时也会选择使用单一索引扫描。在所有的应用除了最简单的应用中可能会有多种有用的索引组合数据库开发人员必须做出权衡以决定提供哪些索引。有时候多列索引最好但是有时更好的选择是创建单独的索引并依赖于索引组合特性。例如如果我们的查询中有时只涉及到列x有时候只涉及到列y还有时候会同时涉及到两列我们可以选择在x和y上创建两个独立索引然后依赖索引组合来处理同时涉及到两列的查询。我们当然也可以创建一个(x, y)上的多列索引。当查询同时涉及到两列时该索引会比组合索引效率更高但是正如Section 11.3 中讨论的它在只涉及到y的查询中几乎完全无用因此它不能是唯一的一个索引。一个多列索引和一个y上的独立索引的组合将会工作得很好。多列索引可以用于那些只涉及到x的查询尽管它比x上的独立索引更大且更慢。最后一种选择是创建所有三个索引但是这种选择最适合表经常被执行所有三种查询但是很少被更新的情况。如果其中一种查询要明显少于其他类型的查询我们可能需要只为常见类型的查询创建两个索引。唯一索引索引也可以被用来强制列值的唯一性或者是多个列组合值的唯一性CREATE UNIQUE INDEX name ON table (column [, ...]);当前只有B-tree能够被声明为唯一。当一个索引被声明为唯一时索引中不允许多个表行具有相同的索引值。空值被视为不相同。一个多列唯一索引将会拒绝在所有索引列上具有相同组合值的表行。PostgreSQL会自动为定义了一个唯一约束或主键的表创建一个唯一索引。该索引包含组成主键或唯一约束的所有列可能是一个多列索引它也是用于强制这些约束的机制。不需要手工在唯一列上创建索引如果那样做也只是重复了自动创建的索引而已。表达式索引一个索引列并不一定是底层表的一个列也可以是从表的一列或多列计算而来的一个函数或者标量表达式。这种特性对于根据计算结果快速获取表中内容是有用的。例如一种进行大小写不敏感比较的常用方法是使用lower函数SELECT * FROM test1 WHERE lower(col1) value这种查询可以利用一个建立在lower(col1)函数结果之上的索引CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1))如果我们将该索引声明为UNIQUE它将阻止创建在col1值上只有大小写不同的行另一个例子如果我们经常进行如下的查询SELECT * FROM people WHERE (first_name || || last_name) John Smith;那么值得创建一个这样的索引CREATE INDEX people_names ON people ((first_name || || last_name))正如第二个例子所示CREATE INDEX命令的语法通常要求在被索引的表达式周围书写圆括号。而如第一个例子所示当表达式只是一个函数调用时可以省略掉圆括号索引表达式的维护代价较为昂贵因为在每一个行被插入或更新时都得为它重新计算相应的表达式。然而索引表达式在进行索引搜索时却不需要重新计算因为它们的结果已经被存储在索引中了。在上面两个例子中系统将会发现查询的条件是WHERE indexedcolumn constant因此查询的速度将等同于其他简单索引查询。因此表达式索引对于检索速度远比插入和更新速度重要的情况非常有用。部分索引一个部分索引是建立在表的一个子集上而该子集则由一个条件表达式被称为部分索引的谓词定义。而索引中只包含那些符合该谓词的表行的项。部分索引是一种专门的特性但在很多种情况下它们也很有用。使用部分索引的一个主要原因是避免索引公值。由于搜索一个公值的查询一个在所有表行中占比超过一定百分比的值不会使用索引所以完全没有理由将这些行保留在索引中。这可以减小索引的尺寸同时也将加速使用索引的查询。它也将加速很多表更新操作因为这种索引并不需要在所有情况下都被更新。Example 11.1 展示了一种可能的应用Example 11.1. 建立一个部分索引来排除公值假设我们要在一个数据库中保存网页服务器访问日志。大部分访问都来自于我们组织内的IP地址但是有些来自于其他地方如使用拨号连接的员工。如果我们主要通过IP搜索来自于外部的访问我们就没有必要索引对应于我们组织内网的IP范围。假设有这样一个表CREATE TABLE access_log (
url varchar,
client_ip inet,
...
)用以下命令可以创建适用于我们的部分索引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;可以看到部分索引查询要求公值能被预知因此部分索引最适合于数据分布不会改变的情况。当然索引也可以偶尔被重建来适应新的数据分布但是这会增加维护负担。Example 11.2 展示了部分索引的另一个可能的用途从索引中排除那些查询不感兴趣的值。这导致了上述相同的好处但它防止了通过索引来访问“不感兴趣的”值即便在这种情况下一个索引扫描是有益的。显然为这种场景建立部分索引需要很多考虑和实验。Example 11.2. 建立一个部分索引来排除不感兴趣的值如果我们有一个表包含已上账和未上账的订单其中未上账的订单在整个表中占据一小部分且它们是最经常被访问的行。我们可以通过只在未上账的行上创建一个索引来提高性能。创建索引的命令如下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;然而索引也可以用于完全不涉及order_nr的查询例如SELECT * FROM orders WHERE billed is not true AND amount 5000.00;这并不如在amount列上部分索引有效因为系统必须扫描整个索引。然而如果有相对较少的未上账订单使用这个部分索引来查找未上账订单将会更好。注意这个查询将不会使用该索引SELECT * FROM orders WHERE order_nr 3501;订单3501可能在已上账订单或未上账订单中。Example 11.2 也显示索引列和谓词中使用的列并不需要匹配。PostgreSQL支持使用任意谓词的部分索引只要其中涉及的只有被索引表的列。然而记住谓词必须匹配在将要受益于索引的查询中使用的条件。更准确地只有当系统能识别查询的WHERE条件从数学上索引的谓词时一个部分索引才能被用于一个查询。PostgreSQL并不能给出一个精致的定理证明器来识别写成不同形式在数学上等价的表达式一方面创建这种证明器极端困难另一方面即便能创建出来对于实用也过慢。系统可以识别简单的不等蕴含例如“x 1”蕴含“x 2”否则谓词条件必须准确匹配查询的WHERE条件中的部分或者索引将不会被识别为可用。匹配发生在查询规划期间而不是运行期间。因此参数化查询子句无法配合一个部分索引工作。例如对于参数的所有可能值来说一个具有参数“x ?”的预备查询绝不会蕴含“x 2”。部分索引的第三种可能的用途并不要求索引被用于查询。其思想是在一个表的子集上创建一个唯一索引如Example 11.3 所示。这对那些满足索引谓词的行强制了唯一性而对那些不满足的行则没有影响。Example 11.3. 建立一个部分唯一索引假设我们有一个描述测试结果的表。我们希望保证其中对于一个给定的主题和目标组合只有一个“成功”项但其中可能会有任意多个“不成功”项。实现它的方式是CREATE TABLE tests (
subject text,
target text,
success boolean,
...
)CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
WHERE success;当有少数成功测试和很多不成功测试时这是一种特别有效的方法。最后一个部分索引也可以被用来重载系统的查询规划选择。同样具有特殊分布的数据集可能导致系统在它并不需要索引的时候选择使用索引。在此种情况下可以被建立这样它将不会被那些无关的查询所用。通常PostgreSQL会对索引使用做出合理的选择例如它会在检索公值时避开索引这样前面的例子只能节约索引尺寸它并非是避免索引使用所必需的非常不正确的规划选择则需要作为故障报告。记住建立一个部分索引意味着我们知道的至少和查询规划器所知的一样多尤其是我们知道什么时候一个索引会是有益的。构建这些知识需要经验和对于PostgreSQL中索引工作方式的理解。在大部分情况下一个部分索引相对于一个普通索引的优势很小。关于部分索引的更多信息可以在[ston89b]、[olson93]和[seshadri95]中找到只用索引的扫描和覆盖索引操作符类和操作符族索引和排序规则检查索引使用
