精华内容
下载资源
问答
  • 文章目录Solr怎么建立索引查询Solr建立索引的过程索引创建Solr搜索的工作流程Solr的查询查询内容进行词法分析语言处理搜索索引得到结果,进行排序   Solr有非常多的功能,但是所有搜索引擎都没法脱离索引和...

    Solr怎么建立索引和查询


      Solr有非常多的功能,但是所有搜索引擎都没法脱离索引和查询,Solr全文检索也是类似的原理,它可以归结为两个过程: 1.索引创建(Indexing) 2. 搜索索引(Search)。因此下面将会对这两个部分展开讲解。

    Solr建立索引的过程

      前面文章提到过,Solr/Lucene采用的是一种反向索引,所谓反向索引:就是从关键字到文档的映射过程,保存这种映射这种信息的索引称为反向索引。
    在这里插入图片描述
      左边保存的是字符串序列

      右边是字符串的文档(Document)编号链表,称为倒排表(Posting List)

    索引创建

    假设有如下两个原始文档:
    文档一:

    Students should be allowed to go out with their friends, but not allowed to drink
    beer.

    文档二:

    My friend Jerry went to school to see his students but found them drunk which is not allowed.
    创建过程大概分为如下步骤:
    在这里插入图片描述

    • 1.把原始文档交给分词组件(Tokenizer)

      分词组件(Tokenizer)会做以下几件事情(这个过程称为:Tokenize),处理得到的结果是词汇单元(Token)

    ​ a.将文档分成一个一个单独的单词

    ​ b.去除标点符号

    ​ c.去除停词(stop word)

      所谓停词(Stop word)就是一种语言中没有具体含义,因而大多数情况不会作为搜索的关键词,这样一来创建索引时能减少索引的大小。经过分词(Tokenizer)后得到的结果称为词汇单元(Token)。

      上例子中,便得到以下词汇单元(Token):“Students”,“allowed”,“go”,“their”,“friends”,“allowed”…….等等

    • 2.词汇单元(Token)传给语言处理组件(Linguistic Processor)

    语言处理组件(linguistic processor)主要是对得到的词元(Token)做一些语言相关的处理。对于英语,语言处理组件(Linguistic Processor)一般做以下几点:

       a.变为小写(Lowercase)。

      b.将单词缩减为词根形式,如”cars”到”car”等。

      c.将单词转变为词根形式,如”drove”到”drive”等。

      d.语言处理组件(linguistic processor)处理得到的结果称为词(Term)

      例子中经过语言处理后得到的词(Term)如下: “student”,“allow”,“go”,“their”,“friend”,“allow”,……等等

    • 3.得到的词(Term)传递给索引组件(Indexer)

    a.利用得到的词(Term)创建一个字典
    在这里插入图片描述

    b.对字典按字母顺序排序:Term Document ID

    在这里插入图片描述
    c.合并相同的词(Term)成为文档倒排(Posting List)链表
    在这里插入图片描述

      至此索引创建完成,搜索”drive”时,”driving”,”drove”,”driven”也能够被搜到。因为在索引中,”driving”,”drove”,”driven”都会经过语言处理而变成”drive”,在搜索时,如果您输入”driving”,输入的查询语句同样经过分词组件和语言处理组件处理的步骤,变为查询”drive”,从而可以搜索到想要的文档。

    Solr搜索的工作流程

    在这里插入图片描述
    用户输入查询字符串,根据用户的请求类型qt(查询为/select)选择请求处理器RequestHandler,根据用户输入的参数defType来选择一个查询解析器解析用户的查询串(默认使用RequestHander中配置的默认查询解析器),查询解析器解析完以后根据用户输入的参数qf指定的字段进行搜索(默认是所有索引字段),查询到结果以后做一些特殊的处理(fq,sort,start,rows,wt)以后使用响应处理器ResponseWriter返回给用户。

    Solr的查询

      Solr的查询,首先会对查询条件进行词法分析、语法分析、语言处理,然后通过分析后的词到索引中查询,然后得到存在指定词的文档集合,然后再进行对结果的一些处理,例如排序或者查找指定的条目数的文档等。主要步骤:

      第一步.对查询内容进行词法分析、语法分析、语言处理,最主要的处理

      第二步.搜索索引,得到符合语法树的文档集合

      第三步.根据查询语句与文档的相关性,对结果进行排序

    对查询内容进行词法分析

    区分查询内容中单词和关键字,比如:Apple and Phone, ” and”就是关键字,” Apple ”和”Phone”是普通单词。根据查询语法的语法规则形成一棵树
    在这里插入图片描述

    语言处理

    跟创建索引时处理方式是一样的。比如:learned–>learn,driven–>drive

    搜索索引得到结果,进行排序

    第二步,搜索索引,得到符合语法树的文档集合
    第三步,针对符合条件的文档结果,根据查询语句与文档的相关性,对结果进行排序,分数高比较越相关,排名就越靠前。

    展开全文
  • lunece 建立索引查询示例lunece 建立索引查询示例lunece 建立索引查询示例lunece 建立索引查询示例lunece 建立索引查询示例lunece 建立索引查询示例
  • 数据库建立索引怎么利用索引查询

    千次阅读 2019-03-05 11:32:04
    数据库建立索引怎么利用索引查询?...在经常进行连接,但是没有指定为外键的列上建立索引,而不经常连接的字段则由优化器自动生成索引。 在频繁进行排序或分组(即进行group by或order by操作)的列上建立...

    数据库建立索引怎么利用索引查询? 精选

    1.合理使用索引
    索引是数据库中重要的数据结构,它的根本目的就是为了提高查询效率。现在大多数的数据库产品都采用IBM最先提出的ISAM索引结构。
    索引的使用要恰到好处,其使用原则如下:
    在经常进行连接,但是没有指定为外键的列上建立索引,而不经常连接的字段则由优化器自动生成索引。
    在频繁进行排序或分组(即进行group by或order by操作)的列上建立索引。
    在条件表达式中经常用到的不同值较多的列上建立检索,在不同值少的列上不要建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值,因此就无必要建立索引。如果建立索引不但不会提高查询效率,反而会严重降低更新速度。
    如果待排序的列有多个,可以在这些列上建立复合索引(compound index)。
    使用系统工具。如Informix数据库有一个tbcheck工具,可以在可疑的索引上进行检查。在一些数据库服务器上,索引可能失效或者因为频繁操作而 使得读取效率降低,如果一个使用索引的查询不明不白地慢下来,可以试着用tbcheck工具检查索引的完整性,必要时进行修复。另外,当数据库表更新大量 数据后,删除并重建索引可以提高查询速度。
    (1)在下面两条select语句中:
    SELECT * FROM table1 WHERE field1<=10000 AND field1>=0;
    SELECT * FROM table1 WHERE field1>=0 AND field1<=10000;
    如果数据表中的数据field1都>=0,则第一条select语句要比第二条select语句效率高的多,因为第二条select语句的第一个条件耗费了大量的系统资源。
    第一个原则:在where子句中应把最具限制性的条件放在最前面。
    (2)在下面的select语句中:
    SELECT * FROM tab WHERE a=… AND b=… AND c=…;
    若有索引index(a,b,c),则where子句中字段的顺序应和索引中字段顺序一致。
    第二个原则:where子句中字段的顺序应和索引中字段顺序一致。
    ——————————————————————————
    以下假设在field1上有唯一索引I1,在field2上有非唯一索引I2。
    ——————————————————————————
    (3) SELECT field3,field4 FROM tb WHERE field1='sdf' 快
    SELECT * FROM tb WHERE field1='sdf' 慢[/cci]
    因为后者在索引扫描后要多一步ROWID表访问。
    (4) SELECT field3,field4 FROM tb WHERE field1>='sdf' 快
    SELECT field3,field4 FROM tb WHERE field1>'sdf' 慢
    因为前者可以迅速定位索引。
    (5) SELECT field3,field4 FROM tb WHERE field2 LIKE 'R%' 快
    SELECT field3,field4 FROM tb WHERE field2 LIKE '%R' 慢,
    因为后者不使用索引。
    (6) 使用函数如:
    SELECT field3,field4 FROM tb WHERE upper(field2)='RMN'不使用索引。
    如果一个表有两万条记录,建议不使用函数;如果一个表有五万条以上记录,严格禁止使用函数!两万条记录以下没有限制。
    (7) 空值不在索引中存储,所以
    SELECT field3,field4 FROM tb WHERE field2 IS[NOT] NULL不使用索引。
    (8) 不等式如
    SELECT field3,field4 FROM tb WHERE field2!='TOM'不使用索引。
    相似地,
    SELECT field3,field4 FROM tb WHERE field2 NOT IN('M','P')不使用索引。
    (9) 多列索引,只有当查询中索引首列被用于条件时,索引才能被使用。
    (10) MAX,MIN等函数,使用索引。
    SELECT max(field2) FROM tb 所以,如果需要对字段取max,min,sum等,应该加索引。
    一次只使用一个聚集函数,如:
    SELECT “min”=min(field1), “max”=max(field1) FROM tb
    不如:SELECT “min”=(SELECT min(field1) FROM tb) , “max”=(SELECT max(field1) FROM tb)
    (11) 重复值过多的索引不会被查询优化器使用。而且因为建了索引,修改该字段值时还要修改索引,所以更新该字段的操作比没有索引更慢。
    (12) 索引值过大(如在一个char(40)的字段上建索引),会造成大量的I/O开销(甚至会超过表扫描的I/O开销)。因此,尽量使用整数索引。 Sp_estspace可以计算表和索引的开销。
    (13) 对于多列索引,ORDER BY的顺序必须和索引的字段顺序一致。
    (14) 在sybase中,如果ORDER BY的字段组成一个簇索引,那么无须做ORDER BY。记录的排列顺序是与簇索引一致的。
    (15) 多表联结(具体查询方案需要通过测试得到)
    where子句中限定条件尽量使用相关联的字段,且尽量把相关联的字段放在前面。
    SELECT a.field1,b.field2 FROM a,b WHERE a.field3=b.field3
    field3上没有索引的情况下:
    对a作全表扫描,结果排序
    对b作全表扫描,结果排序
    结果合并。
    对于很小的表或巨大的表比较合适。
    field3上有索引
    按照表联结的次序,b为驱动表,a为被驱动表
    对b作全表扫描
    对a作索引范围扫描
    如果匹配,通过a的rowid访问
    (16) 避免一对多的join。如:
    SELECT tb1.field3,tb1.field4,tb2.field2 FROM tb1,tb2 WHERE tb1.field2=tb2.field2 AND tb1.field2=‘BU1032’ AND tb2.field2= ‘aaa’
    不如:
    declare @a varchar(80)
    SELECT @a=field2 FROM tb2 WHERE field2=‘aaa’
    SELECT tb1.field3,tb1.field4,@a FROM tb1 WHERE field2= ‘aaa’
    (16) 子查询
    用exists/not exists代替in/not in操作
    比较:
    SELECT a.field1 FROM a WHERE a.field2 IN(SELECT b.field1 FROM b WHERE b.field2=100)
    SELECT a.field1 FROM a WHERE EXISTS( SELECT 1 FROM b WHERE a.field2=b.field1 AND b.field2=100)
    SELECT field1 FROM a WHERE field1 NOT IN( SELECT field2 FROM b)
    SELECT field1 FROM a WHERE NOT EXISTS( SELECT 1 FROM b WHERE b.field2=a.field1)
    (17) 主、外键主要用于数据约束,sybase中创建主键时会自动创建索引,外键与索引无关,提高性能必须再建索引。
    (18) char类型的字段不建索引比int类型的字段不建索引更糟糕。建索引后性能只稍差一点。
    (19) 使用count(*)而不要使用count(column_name),避免使用count(DISTINCT column_name)。
    (20) 等号右边尽量不要使用字段名,如:
    SELECT * FROM tb WHERE field1 = field3
    (21) 避免使用or条件,因为or不使用索引。
    2.避免使用order by和group by字句。
    因为使用这两个子句会占用大量的临时空间(tempspace),如果一定要使用,可用视图、人工生成临时表的方法来代替。
    如果必须使用,先检查memory、tempdb的大小。
    测试证明,特别要避免一个查询里既使用join又使用group by,速度会非常慢!
    3.尽量少用子查询,特别是相关子查询。因为这样会导致效率下降。
    一个列的标签同时在主查询和where子句中的查询中出现,那么很可能当主查询中的列值改变之后,子查询必须重新查询一次。查询嵌套层次越多,效率越低,因此应当尽量避免子查询。如果子查询不可避免,那么要在子查询中过滤掉尽可能多的行。
    4.消除对大型表行数据的顺序存取
    在 嵌套查询中,对表的顺序存取对查询效率可能产生致命的影响。
    比如采用顺序存取策略,一个嵌套3层的查询,如果每层都查询1000行,那么这个查询就要查询 10亿行数据。
    避免这种情况的主要方法就是对连接的列进行索引。
    例如,两个表:学生表(学号、姓名、年龄……)和选课表(学号、课程号、成绩)。如果两个 表要做连接,就要在“学号”这个连接字段上建立索引。
    还可以使用并集来避免顺序存取。尽管在所有的检查列上都有索引,但某些形式的where子句强迫优化器使用顺序存取。
    下面的查询将强迫对orders表执行顺序操作:
    SELECT * FROM orders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008
    虽然在customer_num和order_num上建有索引,但是在上面的语句中优化器还是使用顺序存取路径扫描整个表。因为这个语句要检索的是分离的行的集合,所以应该改为如下语句:
    SELECT * FROM orders WHERE customer_num=104 AND order_num>1001
    UNION
    SELECT * FROM orders WHERE order_num=1008
    这样就能利用索引路径处理查询。
    5.避免困难的正规表达式
    MATCHES和LIKE关键字支持通配符匹配,技术上叫正规表达式。但这种匹配特别耗费时间。例如:SELECT * FROM customer WHERE zipcode LIKE “98_ _ _”
    即使在zipcode字段上建立了索引,在这种情况下也还是采用顺序扫描的方式。如果把语句改为SELECT * FROM customer WHERE zipcode >“98000”,在执行查询时就会利用索引来查询,显然会大大提高速度。
    另外,还要避免非开始的子串。例如语句:SELECT * FROM customer WHERE zipcode[2,3] >“80”,在where子句中采用了非开始子串,因而这个语句也不会使用索引。
    6.使用临时表加速查询
    把表的一个子集进行排序并创建临时表,有时能加速查询。它有助于避免多重排序操作,而且在其他方面还能简化优化器的工作。例如:
    SELECT cust.name,rcvbles.balance,……other COLUMNS
    FROM cust,rcvbles
    WHERE cust.customer_id = rcvlbes.customer_id
    AND rcvblls.balance>0
    AND cust.postcode>“98000”
    ORDER BY cust.name
    如果这个查询要被执行多次而不止一次,可以把所有未付款的客户找出来放在一个临时文件中,并按客户的名字进行排序:
    SELECT cust.name,rcvbles.balance,……other COLUMNS
    FROM cust,rcvbles
    WHERE cust.customer_id = rcvlbes.customer_id
    AND rcvblls.balance>;0
    ORDER BY cust.name
    INTO TEMP cust_with_balance
    然后以下面的方式在临时表中查询:
    SELECT * FROM cust_with_balance
    WHERE postcode>“98000”
    临时表中的行要比主表中的行少,而且物理顺序就是所要求的顺序,减少了磁盘I/O,所以查询工作量可以得到大幅减少。
    注意:临时表创建后不会反映主表的修改。在主表中数据频繁修改的情况下,注意不要丢失数据。
    7.用排序来取代非顺序存取
    非顺序磁盘存取是最慢的操作,表现在磁盘存取臂的来回移动。SQL语句隐藏了这一情况,使得我们在写应用程序时很容易写出要求存取大量非顺序页的查询。

    展开全文
  • 首先明白为什么索引会增加速度,DB在执行一条Sql语句的时候,默认的方式是根据搜索条件进行全表扫描,遇到匹配条件的就加入搜索结果集合。如果我们对某一字段增加索引查询时就会先去索引列表中一次定位到特定值的...

    数据库建立索引为什么会加快查询速度

    首先明白为什么索引会增加速度,DB在执行一条Sql语句的时候,默认的方式是根据搜索条件进行全表扫描,遇到匹配条件的就加入搜索结果集合。如果我们对某一字段增加索引,查询时就会先去索引列表中一次定位到特定值的行数,大大减少遍历匹配的行数,所以能明显增加查询的速度

        添加索引的话,首先去索引列表中查询,而我们的索引列表是B类树的数据结构,查询的时间复杂度为O(log2N),定位到特定值得行就会非常快,所以其查询速度就会非常快。

    为什么说B+-tree比B 树更适合实际应用中操作系统的文件索引和数据库索引?

    1) B+-tree的磁盘读写代价更低

    B+-tree的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

        举个例子,假设磁盘中的一个盘块容纳16bytes,而一个关键字2bytes,一个关键字具体信息指针2bytes。一棵9阶B-tree(一个结点最多8个关键字)的内部结点需要2个盘快。而B+ 树内部结点只需要1个盘快。当需要把内部结点读入内存中的时候,B 树就比B+ 树多一次盘块查找时间(在磁盘中就是盘片旋转的时间)。

    2) B+-tree的查询效率更加稳定

    由于非终结点并不是最终指向文件内容的结点,而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同,导致每一个数据的查询效率相当。

    读者点评
    fanyy1991(csdn用户名)道:个人觉得这两个原因都不是主要原因。数据库索引采用B+树的主要原因是 B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题,B+树应运而生。B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的,而B树不支持这样的操作(或者说效率太低)。

    上述那个问题转载自:从B树、B+树、B*树谈到R 树

        那么在任何时候都应该加索引么?这里有几个反例:1、如果每次都需要取到所有表记录,无论如何都必须进行全表扫描了,那么是否加索引也没有意义了。2、对非唯一的字段,例如“性别”这种大量重复值的字段,增加索引也没有什么意义。3、对于记录比较少的表,增加索引不会带来速度的优化反而浪费了存储空间,因为索引是需要存储空间的,而且有个致命缺点是对于update/insert/delete的每次执行,字段的索引都必须重新计算更新。

        那么在什么时候适合加上索引呢?我们看一个Mysql手册中举的例子,这里有一条sql语句:

    SELECT c.companyID, c.companyName 
    FROM Companies c, User u 
    WHERE c.companyID = u.fk_companyID 
    AND c.numEmployees >= 0 
    AND c.companyName LIKE '%i%' 
    AND u.groupID IN (SELECT g.groupID FROM Groups g WHERE g.groupLabel = 'Executive')

      

        这条语句涉及3个表的联接,并且包括了许多搜索条件比如大小比较,Like匹配等。在没有索引的情况下Mysql需要执行的扫描行数是 77721876行。而我们通过在companyID和groupLabel两个字段上加上索引之后,扫描的行数只需要134行。在Mysql中可以通过 Explain Select来查看扫描次数。可以看出来在这种联表和复杂搜索条件的情况下,索引带来的性能提升远比它所占据的磁盘空间要重要得多。

        那么索引是如何实现的呢?大多数DB厂商实现索引都是基于一种数据结构——B树。oracle实现索引的数据结构是B*树。具体关于B树、B+树、B*树的讲解可以查看另一篇博文:

        可以看到在这棵B树搜索英文字母复杂度只为o(m),在数据量比较大的情况下,这样的结构可以大大增加查询速度。然而有另外一种数据结构查询的虚度比B树更快——散列表。Hash表的定义是这样的:设所有可能出现的关键字集合为u,实际发生存储的关键字记为k,而|k|比|u|小很多。散列方法是通过散列函数h将u映射到表T[0,m-1]的下标上,这样u中的关键字为变量,以h为函数运算结果即为相应结点的存储地址。从而达到可以在o(1)的时间内完成查找。
        然而散列表有一个缺陷,那就是散列冲突,即两个关键字通过散列函数计算出了相同的结果。设m和n分别表示散列表的长度和填满的结点数,n/m为散列表的填装因子,因子越大,表示散列冲突的机会越大。
        因为有这样的缺陷,所以数据库不会使用散列表来做为索引的默认实现,Mysql宣称会根据执行查询格式尝试将基于磁盘的B树索引转变为和合适的散列索引以追求进一步提高搜索速度。我想其它数据库厂商也会有类似的策略,毕竟在数据库战场上,搜索速度和管理安全一样是非常重要的竞争点。

     

    基本概念介绍:

    索引

    使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构,例如 employee 表的姓(lname)列。如果要按姓查找特定职员,与必须搜索表中的所有行相比,索引会帮助您更快地获得该信息。

    索引提供指向存储在表的指定列中的数据值的指针,然后根据您指定的排序顺序对这些指针排序。数据库使用索引的方式与您使用书籍中的索引的方式很相似:它搜索索引以找到特定值,然后顺指针找到包含该值的行。

    在数据库关系图中,您可以在选定表的“索引/键”属性页中创建、编辑或删除每个索引类型。当保存索引所附加到的表,或保存该表所在的关系图时,索引将保存在数据库中。有关详细信息,请参见创建索引。

    注意;并非所有的数据库都以相同的方式使用索引。有关更多信息,请参见数据库服务器注意事项,或者查阅数据库文档。

    作为通用规则,只有当经常查询索引列中的数据时,才需要在表上创建索引。索引占用磁盘空间,并且降低添加、删除和更新行的速度。在多数情况下,索引用于数据检索的速度优势大大超过它的。

    索引列

    可以基于数据库表中的单列或多列创建索引。多列索引使您可以区分其中一列可能有相同值的行。

    如果经常同时搜索两列或多列或按两列或多列排序时,索引也很有帮助。例如,如果经常在同一查询中为姓和名两列设置判据,那么在这两列上创建多列索引将很有意义。

    确定索引的有效性:

    • 检查查询的 WHERE 和 JOIN 子句。在任一子句中包括的每一列都是索引可以选择的对象。
    • 对新索引进行试验以检查它对运行查询性能的影响。
    • 考虑已在表上创建的索引数量。最好避免在单个表上有很多索引。
    • 检查已在表上创建的索引的定义。最好避免包含共享列的重叠索引。
    • 检查某列中唯一数据值的数量,并将该数量与表中的行数进行比较。比较的结果就是该列的可选择性,这有助于确定该列是否适合建立索引,如果适合,确定索引的类型。

    索引类型

    根据数据库的功能,可以在数据库设计器中创建三种索引:唯一索引、主键索引和聚集索引。有关数据库所支持的索引功能的详细信息,请参见数据库文档。

    提示:尽管唯一索引有助于定位信息,但为获得最佳性能结果,建议改用主键或唯一约束。

    唯一索引

    唯一索引是不允许其中任何两行具有相同索引值的索引。

    当现有数据中存在重复的键值时,大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如,如果在 employee 表中职员的姓 (lname) 上创建了唯一索引,则任何两个员工都不能同姓。

    主键索引

    数据库表经常有一列或列组合,其值唯一标识表中的每一行。该列称为表的主键。

    在数据库关系图中为表定义主键将自动创建主键索引,主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时,它还允许对数据的快速访问。

    聚集索引

    在聚集索引中,表中行的物理顺序与键值的逻辑(索引)顺序相同。一个表只能包含一个聚集索引。

    如果某索引不是聚集索引,则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比,聚集索引通常提供更快的数据访问速度。

    建立方式和注意事项

    最普通的情况,是为出现在where子句的字段建一个索引。为方便讲述,我们先建立一个如下的表。 

    DROP TABLE IF EXISTS mytable;
    CREATE TABLE mytable ( 
    
     id serial primary key, 
     category_id int not null default 0, 
     user_id int not null default 0, 
     adddate int not null default 0 
    ); 


    如果你在查询时常用类似以下的语句:  

    SELECT * FROM mytable WHERE category_id=1; 


    最直接的应对之道,是为category_id建立一个简单的索引: 

    CREATE INDEX mytable_categoryid ON mytable (category_id); 

    OK.如果你有不止一个选择条件呢?例如: 

    SELECT * FROM mytable WHERE category_id=1 AND user_id=2; 

    你的第一反应可能是,再给user_id建立一个索引。不好,这不是一个最佳的方法。你可以建立多重的索引。 

    CREATE INDEX mytable_categoryid_userid ON mytable (category_id,user_id); 

    注意到我在命名时的习惯了吗?我使用"表名_字段1名_字段2名"的方式。你很快就会知道我为什么这样做了。 

    现在你已经为适当的字段建立了索引,不过,还是有点不放心吧,你可能会问,数据库会真正用到这些索引吗?测试一下就OK,对于大多数的数据库来说,这是很容易的,只要使用EXPLAIN命令: 

    EXPLAIN 
    SELECT * FROM mytable 
    WHERE category_id=1 AND user_id=2; 

    This is what Postgres 7.1 returns (exactly as I expected) 
     NOTICE: QUERY PLAN: 
     Index Scan using mytable_categoryid_userid on mytable (cost=0.00..2.02 rows=1 width=16) 
    EXPLAIN 
    以上是postgres的数据,可以看到该数据库在查询的时候使用了一个索引(一个好开始),而且它使用的是我创建的第二个索引。看到我上面命名的好处了吧,你马上知道它使用适当的索引了。

    接着,来个稍微复杂一点的,如果有个ORDER BY字句呢?不管你信不信,大多数的数据库在使用order by的时候,都将会从索引中受益。 
     

    SELECT * FROM 
    mytable 
    WHERE category_id=1 AND user_id=2 
    ORDER BY adddate DESC;

    很简单,就象为where字句中的字段建立一个索引一样,也为ORDER BY的字句中的字段建立一个索引: 

    CREATE INDEX mytable_categoryid_userid_adddate ON mytable(category_id,user_id,adddate); 

    注意: "mytable_categoryid_userid_adddate" 将会被截短为 "mytable_categoryid_userid_addda" 
    CREATE 

    EXPLAIN SELECT * FROM mytable WHERE category_id=1 AND user_id=2 ORDER BY adddate DESC; 

     NOTICE: QUERY PLAN: 
     Sort (cost=2.03..2.03 rows=1 width=16) 
    -> Index Scan using mytable_categoryid_userid_addda  on mytable (cost=0.00..2.02 rows=1 width=16) 
     EXPLAIN 
    看看EXPLAIN的输出,数据库多做了一个我们没有要求的排序,这下知道性能如何受损了吧,看来我们对于数据库的自身运作是有点过于乐观了,那么,给数据库多一点提示吧。 
    为了跳过排序这一步,我们并不需要其它另外的索引,只要将查询语句稍微改一下。这里用的是postgres,我们将给该数据库一个额外的提示--在 ORDER BY语句中,加入where语句中的字段。这只是一个技术上的处理,并不是必须的,因为实际上在另外两个字段上,并不会有任何的排序操作,不过如果加入,postgres将会知道哪些是它应该做的。 

    EXPLAIN SELECT * FROM mytable WHERE category_id=1 AND user_id=2 ORDER BY category_id DESC,user_id DESC,adddate DESC; 

     NOTICE: QUERY PLAN: 

    Index Scan Backward using  mytable_categoryid_userid_addda on mytable  (cost=0.00..2.02 rows=1 width=16) 
     EXPLAIN 
    现在使用我们料想的索引了,而且它还挺聪明,知道可以从索引后面开始读,从而避免了任何的排序。
    以上说得细了一点,不过如果你的数据库非常巨大,并且每日的页面请求达上百万算,我想你会获益良多的。不过,如果你要做更为复杂的查询呢,例如将多张表结合起来查询,特别是where限制字句中的字段是来自不止一个表格时,应该怎样处理呢?我通常都尽量避免这种做法,因为这样数据库要将各个表中的东西都结合起来,然后再排除那些不合适的行,搞不好开销会很大。 
    如果不能避免,你应该查看每张要结合起来的表,并且使用以上的策略来建立索引,然后再用EXPLAIN命令验证一下是否使用了你料想中的索引。如果是的话,就OK。不是的话,你可能要建立临时的表来将他们结合在一起,并且使用适当的索引。 
    要注意的是,建立太多的索引将会影响更新和插入的速度,因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格,就没有必要为一个很少使用的where字句单独建立索引了,对于比较小的表,排序的开销不会很大,也没有必要建立另外的索引。 
    以上介绍的只是一些十分基本的东西,其实里面的学问也不少,单凭EXPLAIN我们是不能判定该方法是否就是最优化的,每个数据库都有自己的一些优化器,虽然可能还不太完善,但是它们都会在查询时对比过哪种方式较快,在某些情况下,建立索引的话也未必会快,例如索引放在一个不连续的存储空间时,这会增加读磁盘的负担,因此,哪个是最优,应该通过实际的使用环境来检验。 
    在刚开始的时候,如果表不大,没有必要作索引,我的意见是在需要的时候才作索引,也可用一些命令来优化表,例如MySQL可用"OPTIMIZE TABLE"。 
    综上所述,在如何为数据库建立恰当的索引方面,你应该有一些基本的概念了。

    转载自:http://www.jb51.net/article/27315.htm

    展开全文
  • 1.主键自动建立唯一索引; 2.频繁作为查询条件的字段应该创建索引; 3.查询中与其他表有关联的字段,例如外键关系; 4.频繁更新的字段不适合创建索引,因为每次更新不单单是更新记录,还会更新索引,保存索引文件...

    索引用来快速地寻找那些具有特定值的记录,所有MySQL索引都以B-树的形式保存。如果没有索引,执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录。表里面的记录数量越多,这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引,MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录,通过索引查找记录至少要比顺序扫描记录快100倍。

    1.主键自动建立唯一索引;

    2.频繁作为查询条件的字段应该创建索引;

    3.查询中与其他表有关联的字段,例如外键关系;

    4.频繁更新的字段不适合创建索引,因为每次更新不单单是更新记录,还会更新索引,保存索引文件;

    5.where条件里用不到的字段,不创建索引;

    6.高并发的情况下一般选择复合索引;

    7.查询中排序的字段创建索引将大大提高排序的速度(索引就是排序加快速查找);

    8.查询中统计或者分组的字段;

    9.表记录太少,不需要创建索引;

    10.经常增删改的表;

    11.数据重复且分布平均的字段,因此为经常查询的和经常排序的字段建立索引。注意某些数据包含大量重复数据,因此他建立索引就没有太大的效果,例如性别字段,只有男女,不适合建立索引。

    索引的类型

    普通索引
    这是最基本的索引类型,而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建:
    创建索引,例如CREATE INDEX <索引的名字> ON tablename (列的列表);
    修改表,例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表);
    创建表的时候指定索引,例如CREATE TABLE tablename ( […], INDEX [索引的名字] (列的列表) );

    唯一性索引
    这种索引和前面的“普通索引”基本相同,但有一个区别:索引列的所有值都只能出现一次,即必须唯一。唯一性索引可以用以下几种方式创建:
    创建索引,例如CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表);
    修改表,例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表);
    创建表的时候指定索引,例如CREATE TABLE tablename ( […], UNIQUE [索引的名字] (列的列表) );

    主键
    主键是一种唯一性索引,但它必须指定为“PRIMARY KEY”。如果你曾经用过AUTO_INCREMENT类型的列,你可能已经熟悉主键之类的概念了。主键一般在创建表的时候指定,例如“CREATE TABLE tablename ( […], PRIMARY KEY (列的列表) ); ”。但是,我们也可以通过修改表的方式加入主键,例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。每个表只能有一个主键。

    全文索引
    MySQL从3.23.23版开始支持全文索引和全文检索。在MySQL中,全文索引的索引类型为FULLTEXT。全文索引可以在VARCHAR或者TEXT类型的列上创建。它可以通过CREATE TABLE命令创建,也可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集,通过ALTER TABLE(或者CREATE INDEX)命令创建全文索引要比把记录插入带有全文索引的空表更快。本文下面的讨论不再涉及全文索引,要了解更多信息,请参见MySQL documentation。

    单列索引与多列索引
    索引可以是单列索引,也可以是多列索引。下面我们通过具体的例子来说明这两种索引的区别

    1.多个单列索引:
    定义:即是在表中在需要索引的字段上为每个字段设计一个索引;
    特点:简单,索引个数多

    2.多列索引:
    定义:即是在表中根据查询需求在多个字段上设计一个索引;
    特点:稍微复杂,需要考虑索引顺序;

    3.覆盖索引
    定义:查询所需数据通过索引可以直接获取到,不需要回表查询数据行;

    Select查询返回列包含在索引列中
    where条件包含索引列或复合索引的前导列
    查询结果的总字段长度可以接受

    4.索引合并
    在索引设计不太合理的时候,MySql可能会对一些查询执行索引合并

    在多个单列索引的情况下,
    where条件的索引列使用or连接时,会触发索引合并
    where条件的索引列使用and连接时,通常会选择限制最严格的索引,这个时候联合索引(多列索引)比较合适
    合并标识
    使用 explain 可看到 type:index_merge
    触发索引合并,说明当前索引设计的比较糟糕

    选择索引列
    在性能优化过程中,选择在哪些列上创建索引是最重要的步骤之一。可以考虑使用索引的主要有两种类型的列:在WHERE子句中出现的列,在join子句中出现的列

    索引的缺点
    到目前为止,我们讨论的都是索引的优点。事实上,索引也是有缺点的。

    首先,索引要占用磁盘空间。通常情况下,这个问题不是很突出。但是,如果你创建每一种可能列组合的索引,索引文件体积的增长速度将远远超过数据文件。如果你有一个很大的表,索引文件的大小可能达到操作系统允许的最大文件限制。

    第二,对于需要写入数据的操作,比如DELETE、UPDATE以及INSERT操作,索引会降低它们的速度。这是因为MySQL不仅要把改动数据写入数据文件,而且它还要把这些改动写入索引文件。

    展开全文
  • 怎样优化数据库查询,使其速度更快。 索引建立
  • 多个条件的查询建立索引

    千次阅读 2017-01-19 09:57:26
    3、经常与其他表进行连接的表,在连接字段上应该建立索引; 4、经常出现在Where子句中的字段,特别是大表的字段,应该建立索引; 5、索引应该建在选择性高的字段上; 6、索引应该建在小字段上,对于大的文本字段...
  • 咨询方法-建立数据索引的方法、利用该索引查询数据的方法和系统.zip
  • 通过建立索引优化MySQL查询速度

    千次阅读 2017-01-19 10:59:31
    因此,提升数据库查询速度迫在眉睫,下面我们来看下如何通过建立索引提升MySQL的查询速度。一,什么是索引 索引是用来快速地寻找那些具有特定值的记录,所有MySQL索引都以B-树的形式保存。如果没有索引,执行查询时...
  • 【MySQL进阶学习】子查询索引建立

    千次阅读 多人点赞 2020-04-03 12:50:23
    查询索引建立
  • postgis建立空间索引

    2019-11-27 11:23:56
    postgis建立空间索引 查询索引删除索引建立GIST空间索引 查询索引 select * from pg_indexes where tablename='tablename'; 删除索引 DROP INDEX index_name; 建立GIST空间索引 CREATE INDEX index_name ...
  • SQL 如何建立索引来加快数据库的查询

    千次阅读 多人点赞 2020-04-02 18:35:25
    什么索引2. 索引的特点3. 索引的分类3.1. 普通索引3.2. 唯一索引(unique)3.3. 主键索引(primary key)3.4. 单列索引3.5. 组合索引3.6. 全文索引(fulltext)3.7. 空间索引(spatial)4. 索引的设计原则5. 创建索引5.1....
  • line_sid字段上已经建立索引,为什么还会用到了filesort,数据库总数据在9000条
  • 说到索引,很多人都知道“索引是一个排序的列表,在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址,在数据十分庞大的时候,索引可以大大加快查询的速度,这是因为使用索引后可以不用扫描全表来定位某...
  • 索引的优势当然是提高检索速度,但并不是说数据库建立索引就真的会提高检索速度.为什么呢?我们知道,索引本身是有序的,索引查找的时候一般是多分查找,(当然在内存用数组实现的索引则可以做到随机查找,但数据库一般很...
  • 什么能够提高查询速度? 索引就是通过事先排好序,从而在查找时可以应用二分查找等高效率的算法。 一般的顺序查找,复杂度为O(n),而二分查找复杂度为O(log2n)。当n很大时,二者的效率相差及其悬殊。 举个例子: ...
  • 什么情况下不适合建立索引

    千次阅读 2018-08-21 23:21:14
    表记录太少 经常增删改的表 数据重复且分布平均的表字段,因此应该只为最经常查询经常排序的数据列建立索引(如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果) ...
  • Django 建立索引

    千次阅读 2020-06-18 00:22:28
    数据库数据规模挺大的,建立索引以加速查询。 通过db_index和Meta index选项给数据表字段建立索引 使用索引可快速访问数据库表中的特定信息。数据库索引好比是一本书前面的目录,没有索引目录的话,你访问书中某个...
  • 经常与其他表进行连接的表,在连接字段上应该建立索引经常出现在Where子句中的字段,特别是大表的字段,应该建立索引; 索引应该建在选择性高的字段上; 索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建...
  • 自动建立唯一索引 如zl_yhjbqk(用户基本情况)中的hbs_bh(户标识编号) 表的字段唯一约束 ORACLE利用索引来保证数据的完整性 如lc_hj(流程环节)中的lc_bh+hj_sx(流程编号+环节顺序) 直接条件查询的...
  • 在我的上一篇博客&lt;&lt;Explain检测SQL语句的性能&gt;&gt;中简单的介绍了explain关键字查询结果字段....如果表中查询的列有一个索引,MySQL能快速到达一个位置去搜寻到数据文件...
  • MySQL 建立索引

    万次阅读 2018-06-16 13:17:44
    合适地建立索引后比不建立索引,提高了查询速度 创建索引的语法: 简单索引(可以有重复数据) create index [indexname] on [tablename]([column_name]);` 举例子说明如: 创建一个数据表,设定一些...
  • 什么建立索引后,查询会很快

    千次阅读 2010-01-06 13:46:00
    索引是ORACLE为了提高查询的速度引入的,它是按索引关键字的顺序存放记录也叫做数据结构。在索引记录中存有索引关键字和指向真正数据的指针。因为索引中只存关键字和指针所以索引的规模要比真正的表的规模小很多。...
  • 3.查询中与其它表关联的字段,外键关系建立索引 4.频繁更新的字段不适合创建索引or因为每次更新不单单是更新了记录还会更新索引,加重了IO负担。 5. Where条件里用不到的字段不创建索引 6.单键/组合索引的选择问题,...
  • 3、经常与其他表进行连接的表,在连接字段上应该建立索引; 4、经常出现在Where子句中的字段,特别是大表的字段,应该建立索引; 5、索引应该建在选择性高的字段上; 6、索引应该建在小字段上,对于大的文本字段甚至...
  • 什么建立索引?  索引用于快速找出在某个列中有一特定值的行,不使用索引,MySQL必须从第一条记录开始读完整个表,直到找出相关的行,表越大,查询数据所花费的时间就越多,如果表中查询的列有一个索引,MySQL...
  • 什么样的表和列需要建立索引

    千次阅读 2018-04-08 17:24:20
    数据量大的,经常进行查询操作的表要建立索引1)表与表连接用于多表联合查询的约束条件的字段应当建立索引;2)用于排序的字段可以添加索引,用于分组的字段应当视情况看是否需要添加索引。添加多列索引的时候,对应...
  • mysql建立函数索引

    万次阅读 2018-06-02 17:12:57
    mysql版本需要是5.7及以上版本才支持建立函数索引建立函数索引需要两步1.创建虚拟列alter table t_log add column create_time_index datetime GENERATED ALWAYS AS (date_format(create_time,'%Y-%m-%d'));上面这条...
  • 对于复杂的查询语句,为了让oracle查询更快一些,可以建立索引,是对where条件字段建立索引好些?还是查询结果字段建立索引好些?

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 560,344
精华内容 224,137
关键字:

经常查询建立什么索引