当前位置：文档之家 › Unique索引优化实践

Unique索引优化实践

  • 格式：docx
  • 大小：348.34 KB
  • 文档页数：7

Unique索引优化实践 胡月军(一浪) Unique索引，有时也称Primary Key索引，顾名思义就是对于这个索引字段每个doc的值都是唯一的，如各种id字段：product id，customer id, campaign id和bidword id等。这种类型的索引一般用来进行高效的查询，最典型的应用场景就是进行附表join查询，即对主表中查到的每一个doc，都在附表中查询其对应的附表doc信息。所以，对这种类型的索引进行优化会对整体查询性能有很好的提升，特别是在主表查询的结果很多的情况下。本文主要总结一下对于这种类型索引的优化实践，包括全量和实时增量的情况。

我们知道，在全量建索引时，在内存中一般用开链的哈希表来存储Token的Hash值及其倒排链的信息。假设有N个不同的tokens，那么这个hash数组的大小一般是取第一个大于N*(5/3)的质数P。结构如下图所示：

图1: 全量索引在内存中的开链哈希表结构图 当一个段的索引建完以后，这个内存中的Hash表里面的tokens的哈希值及包含其倒排链和occ链等元信息的keyword terms一般被转成如下的三种数据结构之一存在文件中： 1. Closed Hash Table 2. Skip List 3. TieredDictionary

这几种数据结构的目的都是为了在查询时先mmap了这些文件以后，能对于一个给定的query keyword，快速根据其哈希值找到其对应的keyword term，进而定位到相应的倒排链和occ链等信息。不同的数据结构在不同的场景（数据特点）下对于内存空间的使用以及查询性能的影响也是不同的。下面先简要分析一下以上这几种常用数据结构的特点，然后再谈谈对于Unique类型的索引所采用的优化数据结构。

为了便于分析，假设我们有100万个不同的Tokens，每个Token的Hash值需用8个bytes表示(uint64_t)。Tokens对应的keyword terms100万个，同时在一般情况下，每个keyword term的第一个元素就是其对应的token的hash值。在内存中建索引的时候,这个开链hash表数组的大小P取大于N*(5/3)的第一个质数，即3145739。

Closed Hash Table(闭链哈希表) 提到哈希表，不少人想到就是快，时间复杂度为O(1), 其实未必如此，这个在后面的优化讨论中再深入。对于闭链hash,其大小一般也是取第一个大于N*(5/3)的质数P来申请空间，所以空间占用一般会比较大。对于以上例子，即N=100万，那么这个Hash数组大小为P，为原始keyword terms大小的3.15倍。闭链Hash表事实上就是环形数组，如下图所示：

图2: 闭链Hash表结构图 当查询一个token倒排链等信息的时候，首先计算其hash值，比如H，然后用H模P得到一个值作为下标，然后看这个闭链hash数组在这个下标下的元素是否是空值，如果为空（对于上图来说，就是元素的hash值为0），则直接返回表示没有查到；若不为空，则看看这个元素的hash值是否和查询值相等，若相等则找到返回，若不等则继续跟这个元素的后面元素依次进行比较，最后要么找到，要么碰到一个空元素说明没有查找到。

Skip List(跳表) 跳表，顾名思义，是能在查找的时候能快速跳过很多元素，然后在一个相对小的范围内搜索给定的一个query keyword的hash值对应的keyword term信息。跳表的实现原理是： 1. 首先确定用一个小的数组, 就叫做跳表数组吧，来存储跳表信息，这个数组的size一般取为keyword terms个数N的1/64 (假设此值为M)，或者稍微大点，数组中每个元素的大小为4个字节(uint32_t)。 2. 然后，将keyword terms按token的hash值从小到大排好序存储在一个数组中，假设这个数组叫K，同时根据最大和最小的两个token的hash值将所有的hash值值域均分成M个区间。 3. 让跳表数组的第i个元素存储hash值的第i个区间里面的最小的一个hash值对应的keyword term在数组K中的下标值（哈，这句话有点绕），若hash值第i个区间里面没有值，则存一个无效的下标值-1.

所以一个跳表的结构如下图所示： 图3: 跳表结构图 在查询的时候，执行如下步骤： 1. 先计算出query keyword的Hash值H，然后用(H-Hmin)/Step得到skip list数组中的下标i。 2. 查看下标i里面的元素值是否为-1，若为-1，则说明没有查到直接返回，若不为-1，就记录此元素值，假设为j；然后继续在skip list数组中查找i下标以后的元素中第一个不为-1的元素值，若找到则记录此元素值为k，如找不到则将k值设为N，即keyword terms数组的最后一个元素下标位置+1。 3. 最后在keyword terms数组K的[j,k]位置中二分查询hash值为H的keyword item。

注意由于按Hash值的值域进行分段跳跃，所以每个哈希值区间里面的元素个数是很可能是分布不均的，故每次二分查找的区间大小是不固定的。

Tiered Dictionary(分层词典) Tiered dictionary的思路是分层查询定位。即，先二分查找一个小词典定位到一个大致的小范围，然后再在小范围内再继续搜索keyword term。实现的原理是： 1. 先将所有的keyword terms按它们的hash值从小打大排序后存储在一个数组中。 2. 将上面的数组分成若干个blocks，每个block包含相同个数的keyword terms，记做B个(比如说B就取128个)，当然最后一个block的元素个数可能少于B个。 3. 将上面每个block的最后一个keyword item元素的hash值抽出来依次保存在另外一个小的字典数组中。 所以，序列化好的tiered dictionary结构图如下：

图4: Tiered dictionary结构图 那么对于任一个查询词，假设hash值为H，查找其对应的keyword term就比较简单了： 1. 先在字典数组中找到第一个大于或等于H的元素下标，若无此下标，即字典数组中的都有元素的hash值都小于H，那么说明没有查到结果，直接返回；否则，可以根据此下标定位到这个元素在keyword terms数组中所属于的block。 2. 在1确定的block中二分查找H对应的keyword term。

相对于skip list，tiered dictionary的查询比较稳定，因为它可以保证第二次搜索总是在一个元素较少的block中查询，而skip list无法做到这一点，这个前面提到过；但是skip list有时候可以在第一步查skip list小数组的时候就可以确定这个元素不存在，而tiered dictionary一般情况下做不到这点。

全量Unique索引优化 像很多数据结构的算法一样，在内存空间使用和查找时间之间往往需要一个权衡，Unique索引的优化也是这样，当然我们的目标还是在尽可能的在占用较少内存的情况下，使得查询速度更快。

不同于一般的字段索引，一次查询只查询一次，用在附表Join时候的Unique索引在一次查询中可能会被查询上万次(每个主表的doc结果都需要进行一次附表Unique索引查询)，这就决定了查询速度是Unique索引实现的第一目标。我们看到，不管是skip list还是tiered dictionary，大部分时候都需要二分查找，特别是有时候对于不在里面的元素，二分查找比较的次数反而更多，这就决定了对于Unique索引如用这两种数据结构，在线查询的性能是很不高的，虽然它们俩是比较省内存的。

当然，我们最想达到的目标就是只比较一次，或者很有限几次就能确定一个hash值是否在一个段的某个unique索引中。我们很显然会想到哈希表，比如实现简单的闭链哈希表。的确，有些搜索引擎的索引也是这么做的。但是，对于闭链哈希表的实现，这里面有一个大坑！

对于hash table的实现，我们知道关键是hash function，记做H。好的H(x)的计算结果要分布均匀(uniform distributed)、冲突少(less collisions)。但对于闭链hash table的实现，除了冲突少，H还有一个非常重要的要求，那就是H(x)的结果集要避免簇拥在一起(avoid clustering)，即要避免H(x)计算得到的数组下标是连在一起的，否则会发生非常悲惨的后果。这个其实不难理解，因为对于线性hash函数来说，闭链hash表在查找的时候若发生冲突，是依次向后比较查找，要么找到相应元素，要么碰到空元素没有找到返回。所以如果有大片的结果连在一起，如果查找的元素不在里面，同时又发生了冲突，查找到第一个空元素有时候需要比较很多次。这种情况很容易发生，比如在bidword id很多是相连在一起的情况，同时我们又采用简单取模的方式来计算hash数组存储下标。

当然，我们可以修改哈希函数来避免簇拥，这个我们增量索引优化的时候会采用。对于全量，为了在内存使用和查询效率上取得平衡，我们可以采用开链哈希表的方式来解决，其实实现也不复杂。

最简单的实现，就是将内存中的hash table里面的conflicting hash nodes list一条一条的序列化，内存中的主索引数组的元素分布情况不变，同时将conflicting nodes直接链在原hash主数组的后面。不过，为了链式存储，序列化好的每个keyword item里面会增加一个next指针和是否是每条链的最后一个节点的标记。存储好的结果如下图所示：

合集下载

SQL主键冲突或者唯一索引冲突更新duplicatekeyupdate...values（）

SQL主键冲突或者唯一索引冲突更新duplicatekeyupdate...values（）

SQL主键冲突或者唯⼀索引冲突更新duplicatekeyupdate...values（）**说明：1. on duplicate key update 含义：1）如果在INSERT语句末尾指定了 on duplicate key update，并且插⼊⾏后会导致在⼀个UNIQUE索引或PRIMARY KEY中出现重复值，则在出现重复值的⾏执⾏UPDATE；2）如果不会导致唯⼀值列重复的问题，则插⼊新⾏。

2. values(col_name)函数只是取当前插⼊语句中的插⼊值，并没有累加功能。

如：count = values(count) 取前⾯ insert into 中的 count 值，并更新当有多条记录冲突，需要插⼊时，前⾯的更新值都被最后⼀条记录覆盖，所以呈现出取最后⼀条更新的现象。

如：count = count + values(count) 依然取前⾯ insert into 中的 count 值，并与原记录值相加后更新回数据库，这样，当多条记录冲突需要插⼊时，就实现了不断累加更新的现象。

注：insert into ... on duplicate key update ... values() 这个语句尽管在冲突时执⾏了更新，并没有插⼊，但是发现依然会占⽤ id 序号（⾃增），出现很多丢失的 id 值，可参看下⾯案例----《百度百科--MySQL函数》函数 VALUES(col_name)函数使⽤说明：在⼀个 INSERT … ON DUPLICATE KEY UPDATE … 语句中，你可以在 UPDATE ⼦句中使⽤ VALUES(col_name ) 函数，⽤来访问来⾃该语句的 INSERT 部分的列值。

换⾔之，UPDATE ⼦句中的 VALUES(col_name ) 访问需要被插⼊的 col_name 的值 , 并不会发⽣重复键冲突。

这个函数在多⾏插⼊中特别有⽤。

VALUES() 函数只在 INSERT ... UPDATE 语句中有意义，⽽在其它情况下只会返回 NULL。

sql server加索引语句

sql server加索引语句

sql server加索引语句在 SQL Server 中创建索引是增加查询性能的关键。

通过使用索引，查询可以快速定位数据行，而不必扫描整个数据表。

本文将为您介绍如何在 SQL Server 中创建索引，以提高查询性能。

一、什么是索引索引是一种数据结构，用于快速访问数据库中的数据。

在数据库中，索引是一个单独的对象，可以存储在表中或独立于表之外。

索引可以是唯一的，也可以是非唯一的。

索引让数据库引擎更快地找到数据。

当执行查询时，数据库引擎使用索引而不是扫描整个表格进行查找。

索引可以大大提高查询性能，因为它可以将查询的成本从 O(n) 降低到 O(log n)。

二、创建索引要创建索引，您需要使用CREATE INDEX语句。

CREATE INDEX语句的语法如下：CREATE INDEX [index_name] ON [table_name] (column1,column2,...)例如，以下代码创建了一个名为“idx_last_name”的索引，用于表“employees”中的“last_name”列：如果您要创建一个唯一的索引，请在CREATE INDEX语句中添加UNIQUE关键字。

例如，以下代码创建一个唯一索引，用于表“employees”中的“employee_id”列：CREATE UNIQUE INDEX idx_employee_id ON employees (employee_id)三、使用索引一旦您创建了索引，就可以使用它来加速查询。

例如，以下查询将使用名为“idx_last_name”的索引来查找员工名为“Smith”的记录：SELECT * FROM employees WHERE last_name = 'Smith'查询优化器将使用索引来查找所有姓氏为“Smith”的员工，并返回它们的记录。

如果没有索引，查询将扫描整个表，这可能需要更长时间。

虽然索引可以大大提高查询性能，但过多的索引可能会降低性能。

聚集索引与非聚集索引详解

聚集索引与非聚集索引详解

SQL中、聚集索引和非聚集索引有何区别?聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致，聚集索引表记录的排列顺序与索引的排列顺序一致，优点是查询速度快，因为一旦具有第一个索引值的纪录被找到，具有连续索引值的记录也一定物理的紧跟其后。

聚集索引的缺点是对表进行修改速度较慢，这是为了保持表中的记录的物理顺序与索引的顺序一致，而把记录插入到数据页的相应位置，必须在数据页中进行数据重排，降低了执行速度。

建议使用聚集索引的场合为： a.此列包含有限数目的不同值； b.查询的结果返回一个区间的值；c.查询的结果返回某值相同的大量结果集。

非聚集索引指定了表中记录的逻辑顺序，但记录的物理顺序和索引的顺序不一致，聚集索引和非聚集索引都采用了B+树的结构，但非聚集索引的叶子层并不与实际的数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针的方式。

非聚集索引比聚集索引层次多，添加记录不会引起数据顺序的重组。

建议使用非聚集索引的场合为： a.此列包含了大量数目不同的值； b.查询的结束返回的是少量的结果集； c.order by 子句中使用了该列。

聚集索引:表中存储的数据按照索引的顺序存储,检索效率比普通索引高,但对数据新增/修改/删除的影响比较大非聚集索引:不影响表中的数据存储顺序,检索效率比聚集索引低,对数据新增/修改/删除的影响很少主要是存储的顺序的调整不同。

这样对一些操作的影响不同。

有利就有弊，两个之间属于对立和弥补的关系。

主要看你需要怎样的应用了。

聚集索引将数据行的键值在表内排序并存储对应的数据记录，使得数据表物理顺序与索引顺序一致一个表只能有一个聚集索引非聚集索引是完全独立与数据行的结构，表中的数据行不按非聚集键的次序存储，存储数据过程中不会影响数据表中记录的实际存储顺序每个表可以创建249个非聚集索引1 对于经常更新的列不宜建立聚集索引2 具有高百分比的唯一值和不经常被修改的属性上创建聚集索引例如：主键3 若在一个表中既要创建聚集索引，又要创建非聚集索引，应先创建聚集索引，然后创建非聚集索引SQL Server创建索引什么是索引拿汉语字典的目录页（索引）打比方：正如汉语字典中的汉字按页存放一样，SQL Server中的数据记录也是按页存放的，每页容量一般为4K 。

goldendb sql优化实践

goldendb sql优化实践

goldendb sql优化实践金数据Golelndb 是一款高性能、高可用的SQL 引擎，常被应用于大数据、数据仓库、实时分析等领域。

在实际的使用过程中，SQL 优化是至关重要的一环。

本文将介绍金数据SQL 优化的实践，从基本概念到具体操作，让大家能够更好地利用金数据进行SQL 优化。

一、什么是SQL 优化SQL 优化是针对SQL 查询语句进行的一系列操作，目的是提高查询性能、减少资源消耗、提升数据库整体性能。

SQL 查询语句的优化包括但不限于优化查询逻辑、利用索引、减少I/O 操作、合理使用缓存等，通过这些操作可以有效地提高SQL 查询的效率。

二、为什么需要SQL 优化在实际的应用中，数据库中通常会包含大量的数据，查询操作的频繁和复杂度都会对数据库的性能产生影响。

未经优化的SQL 查询语句可能会导致数据库负载过重、响应时间过长、甚至因为耗尽资源而导致系统崩溃。

因此，进行SQL 优化可以有效地提高数据库的性能，保障系统的稳定运行。

三、金数据SQL 优化实践1. 确定优化对象在进行SQL 优化之前，首先需要确定优化的对象，即要对哪些SQL 查询进行优化。

可以通过数据库的性能监控工具或者日志分析工具，找到查询频次高、执行时间长的SQL 查询语句作为优化对象。

2. 分析查询计划对于优化对象的SQL 查询语句，需要使用金数据提供的explain 命令来分析其查询计划。

查询计划是数据库对SQL 查询语句执行过程的详细分析，包括了查询涉及到的表、索引、执行顺序等信息。

通过查询计划可以找到SQL 查询语句的性能瓶颈，从而有针对性地进行优化。

3. 优化查询逻辑在分析查询计划的基础上，需要对SQL 查询语句的逻辑进行优化。

比如可以通过合并查询、改写查询条件、拆分复杂查询等方式来优化查询逻辑，减少不必要的计算和数据传输，提高查询效率。

4. 利用索引索引是数据库中常用的性能优化手段之一，能够加快查询速度。

在金数据中，可以通过create index 命令来创建索引，然后通过explain 命令来确认索引是否被查询计划所使用。