精华内容
下载资源
问答
  • ES的无论什么搜索,对于text类型字段其实都是基于倒排索引去进行搜索的,也就是进行分词后的,因此如果想像传统数据库一样的模糊匹配,一般可以使用它的keyword进行搜索。(keyword不会被分词) 以下的搜索在大型...

    前言

    ES的无论什么搜索,对于text类型字段其实都是基于倒排索引去进行搜索的,也就是进行分词后的,因此如果想像传统数据库一样的模糊匹配,一般可以使用它的keyword进行搜索。(keyword不会被分词)
    以下的搜索在大型生产环境都不推荐使用。

    前缀索引查询

    以xx开头的搜索,不计算相关度评分,和filter比,没有bitcache。前缀搜索,尽量把前缀长度设置的更长,性能差,一般大规模产品不使用。(是去倒排索引中去匹配前缀,需要遍历每一个倒排索引才能找到所有匹配的)

    语法

    GET index/_search
    {
      "query": {
        "prefix": {
          "title": {
            "value": "text"
          }
        }
      }
    }
    

    为了加快前缀搜索速度,可以设置默认的 前缀索引 (空间换时间)

    PUT my_index
    {
      "mappings": {
        "properties": {
          "text": {
            "type": "text",
            "index_prefixes": {
              "min_chars":2,  
              "max_chars":4
            }    
          }
        }
      }
    }
    

    上面这个设置的意思是,把分词后的每个词项的2-4个字符额外进行建立前缀倒排索引,从而提高后续前缀匹配的速度,但是占用空间也是相对变大。
    index_prefixes: 默认 “min_chars” : 2, “max_chars” : 5 。

    通配符查询

    通配符查询类似于正则,但没正则强大,允许对匹配表达式进行通配符占位。

    • 表示匹配任意长度的任意字符
      ? 表示匹配一个任意字符
      […]则表示匹配括号中列出的字符中的任意一个
      [!..]表示不匹配括号中列出的字符中的任意一个

    语法

    {
      "query": {
        "wildcard": {
          "text": {
            "value": "eng?ish"
          }
        }
      }
    }
    

    正则查询

    regexp查询的性能可以根据提供的正则表达式而有所不同。为了提高性能,应避免使用通配符模式,如.或 .?+未经前缀或后缀

    语法

    {
      "query": {
        "regexp": {
          "name": {
            "value": "[\\s\\S]*nfc[\\s\\S]*",
            "flags": "ALL",
            "max_determinized_states": 10000, #防止正则内存过大的保护措施
            "rewrite": "constant_score"
          }
        }
      }
    }
    

    关于参数flags,有几个配置可选:

    ALL (Default)

    启用所有可选操作符。

    COMPLEMENT

    启用操作符。可以使用对下面最短的模式进行否定。例如
    a~bc # matches ‘adc’ and ‘aec’ but not ‘abc’
    INTERVAL
    启用<>操作符。可以使用<>匹配数值范围。例如
    foo<1-100> # matches ‘foo1’, ‘foo2’ … ‘foo99’, ‘foo100’
    foo<01-100> # matches ‘foo01’, ‘foo02’ … ‘foo99’, ‘foo100’

    INTERSECTION

    启用&操作符,它充当AND操作符。如果左边和右边的模式都匹配,则匹配成功。例如:
    aaa.+&.+bbb # matches ‘aaabbb’

    ANYSTRING

    启用@操作符。您可以使用@来匹配任何整个字符串。
    您可以将@操作符与&和~操作符组合起来,创建一个“everything except”逻辑。例如:
    @&~(abc.+) # matches everything except terms beginning with ‘abc’

    Fuzzy模糊(容错)匹配

    场景

    1、混淆字符 (box → fox)
    2、缺少字符 (black → lack)
    3、多出字符 (sic → sick)
    4、颠倒次序 (act → cat)

    在出现上面情况的时候,我们也希望用户可以搜索到想要的内容,那么这个时候可以使用fuzzy。

    语法

    以下两种都可以:

    1、第一种-手动档
    可以手动多指定一些参数,但一般也不建议改动

    {
      "query": {
        "fuzzy": {
          "desc": {
            "value": "quangemneng",
            "fuzziness": 5
          }
        }
      }
    }
    

    ① value:(必需,字符串)
    ② fuzziness:(可选,字符串)最大误差 并非越大越好, 因为大了虽然召回率高 但是结果不准确
    1) 两段文本之间的Damerau-Levenshtein距离是使一个字符串与另一个字符串匹配所需的插入、删除、替换和调换的数量
    2) 距离公式:Levenshtein是lucene的,es改进版:Damerau-Levenshtein
    3) axe=>aex Levenshtein=2 Damerau-Levenshtein=1

    ③ max_expansions:可选,整数)匹配的最大词项数量。默认为50。
    ④ prefix_length:创建扩展时保留不变的开始字符数。默认为0
    1)避免在max_expansions参数中使用较高的值,尤其是当prefix_length参数值为时0。max_expansions由于检查的变量数量过多,参数中的高值 可能导致性能不佳。

    ⑤ transpositions:(可选,布尔值)指示编辑是否包括两个相邻字符的变位(ab→ba)。默认为true。
    ⑥ rewrite:(可选,字符串)用于重写查询的方法
    https://www.elastic.co/cn/blog/found-fuzzy-search#performance-considerations

    2、第二种,"自动挡"时代

    {
      "query": {
        "match": {
          "desc": {
            "query": "quangengneng nfc",
            "fuzziness": "AUTO"
          }
        }
      }
    }
    

    match_phrase_prefix(最简陋的Suggest)

    match_phrase_prefix与match_phrase相同,但是它多了一个特性,就是它允许在文本的最后一个词项(term)上的前缀匹配,如果 是一个单词,比如a,它会匹配文档字段所有以a开头的文档,如果是一个短语,比如 “this is ma” ,他会先在倒排索引中做以ma做前缀搜索,然后在匹配到的doc中做match_phrase查询,(网上有的说是先match_phrase,然后再进行前缀搜索, 是不对的)

    语法

    {
      "query": {
        "match_phrase_prefix": {
          "desc": {
            "query": "zhichi quangongneng nf",
            "analyzer": "whitespace",
            "max_expansions": 1,
            "slop": 2,
            "boost": 1
          }
        }
      }
    }
    

    参数

    analyzer

    指定何种分析器来对该短语进行分词处理

    max_expansions

    限制匹配的最大term数。

    1. 一般来讲,前缀匹配是会全索引进行扫描匹配的,为了提高效率,可以进行限制它可以进行扫描的索引的个数,但即使设置为1,也不意味着返回的doc结果只有一个,主要有2点:
      1、一个索引可能有多个doc
      2、这个限制扫描个数是针对每个分片来说的(每个分片都可以扫描1个),因此也就是说该索引的每个分片扫描的第一个term都可能被匹配上。

    boost

    用于设置该查询的权重

    slop

    允许短语间的词项(term)间隔

    slop 参数告诉 match_phrase 查询词条相隔多远时仍然能将文档视为匹配, 什么是相隔多远? 意思是说为了让查询和文档匹配你需要移动词条多少次?举个例子:

    如果我们输入的是:de zhong shouji hongzhaji
    而期望匹配的句子是:shouji zhong de hongzhaji
    那么要怎么移动呢?
    1、首先要把“shouji”词条向左移动2个词条:
    shouji/de zhong shouji hongzhaji
    2、接下来在把de向右移动2个词条:
    shouji zhong de shouji hongzhaji

    这样下来,共需要4次移动,因此slop需要设置为4的时候,输入的才能匹配上。

    N-gram-tokenFilter

    上面的前缀匹配还是存在性能问题,那有没有相对好一点的方法呢? 我们可以从分词角度出发。
    在设置索引的时候,可以进行指定分词器的相关属性,其中有一项是指定fliter,可以通过指定ngram:

    {
      "settings": {
        "analysis": {
          "filter": {
            "2_3_grams": {
              "type": "ngram",
              "min_gram": 1,
              "max_gram": 2
            }
          },
          "analyzer": {
            "my_ngram": {
              "type":"custom",
              "tokenizer": "standard",
              "filter": [ "2_3_grams" ]
            }
          }
        }
      },
      "mappings": {
        "properties": {
          "text": {
            "type": "text",
            "analyzer":"my_ngram",
            "search_analyzer": "standard"
          }
        }
      }
    }
    

    经过ngram设置的min_gram:1和"max_gram": 3,分析以下语句:

    GET _analyze
    {
      "tokenizer": "ik_max_word",
      "filter": [ "edge_ngram" ],
      "text": "reba always"
    }
    

    会先按最小粒度1进行拆分,也就是拆分出“r”,“e”,“b”,“a”,“a”,“l”,“w”,“a”,“y”,“s”
    然后按粒度2拆分,拆分成:“re”,“eb”,“ba”,“al”,“lw”,“wa”,“ay”,“ys”
    然后按最大粒度3进行拆分,拆分成:“reb”,“eba”,“alw”,“lwa”,“way”,“ays”

    Edge-N-gram-tokenFilter

    另外一个filter,根据min_gram和max_gram对分词的开头部分进行拆分。
    这个可能更常用一些,因为更多的我们的搜索是从一个词的开头进行部分搜索,而不是中间进行搜索。

    GET _analyze
    {
      "tokenizer": "ik_max_word",
      "filter": [ "edge_ngram" ],
      "text": "reba always loves me"
    }
    

    min_gram =1 “max_gram”: 1
    拆分情况:r a l m

    min_gram =1 “max_gram”: 2
    拆分情况:
    r a l m
    re al lo me

    min_gram =2 “max_gram”: 3
    拆分情况:
    re al lo me
    reb alw lov me

    展开全文
  • 最左前缀匹配原则

    2021-03-27 19:09:30
    最左前缀匹配原则:在MySQL建立联合索引时会遵守最左前缀匹配原则,即最左优先,在检索数据时从联合索引的最左边开始匹配。 要想理解联合索引的最左匹配原则,先来理解下索引的底层原理。索引的底层是一颗B+树,...

    原文地址

    最左前缀匹配原则:在MySQL建立联合索引时会遵守最左前缀匹配原则,即最左优先,在检索数据时从联合索引的最左边开始匹配。

     要想理解联合索引的最左匹配原则,先来理解下索引的底层原理。索引的底层是一颗B+树,那么联合索引的底层也就是一颗B+树,只不过联合索引的B+树节点中存储的是键值。由于构建一棵B+树只能根据一个值来确定索引关系,所以数据库依赖联合索引最左的字段来构建。

    举例:创建一个(a,b)的联合索引,那么它的索引树就是下图的样子。



    可以看到a的值是有顺序的,1,1,2,2,3,3,而b的值是没有顺序的1,2,1,4,1,2。但是我们又可发现a在等值的情况下,b值又是按顺序排列的,但是这种顺序是相对的。这是因为MySQL创建联合索引的规则是首先会对联合索引的最左边第一个字段排序,在第一个字段的排序基础上,然后在对第二个字段进行排序。所以b=2这种查询条件没有办法利用索引。

      由于整个过程是基于explain结果分析的,那接下来在了解下explain中的type字段和key_lef字段。

    1.type联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:(重点看ref,rang,index)

     system:表只有一行记录(等于系统表),这是const类型的特例,平时不会出现,可以忽略不计
        const:表示通过索引一次就找到了,const用于比较primary key 或者 unique索引。因为只需匹配一行数据,所有很快。如果将主键置于where列表中,mysql就能将该查询转换为一个const
        eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描。
        注意:ALL全表扫描的表记录最少的表如t1表
    ref:非唯一性索引扫描,返回匹配某个单独值的所有行。本质是也是一种索引访问,它返回所有匹配某个单独值的行,然而他可能会找到多个符合条件的行,所以它应该属于查找和扫描的混合体。
    range:只检索给定范围的行,使用一个索引来选择行。key列显示使用了那个索引。一般就是在where语句中出现了bettween、<、>、in等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点,结束于另一个点,不用扫描全部索引。
    index:Full Index Scan,index与ALL区别为index类型只遍历索引树。这通常为ALL块,应为索引文件通常比数据文件小。(Index与ALL虽然都是读全表,但index是从索引中读取,而ALL是从硬盘读取)
        ALL:Full Table Scan,遍历全表以找到匹配的行

    2.key_len显示MySQL实际决定使用的索引的长度。如果索引是NULL,则长度为NULL。如果不是NULL,则为使用的索引的长度。所以通过此字段就可推断出使用了那个索引。

    计算规则:

        1.定长字段,int占用4个字节,date占用3个字节,char(n)占用n个字符。

        2.变长字段varchar(n),则占用n个字符+两个字节。

        3.不同的字符集,一个字符占用的字节数是不同的。Latin1编码的,一个字符占用一个字节,gdk编码的,一个字符占用两个字节,utf-8编码的,一个字符占用三个字节。

        (由于我数据库使用的是Latin1编码的格式,所以在后面的计算中,一个字符按一个字节算)

        4.对于所有的索引字段,如果设置为NULL,则还需要1个字节。

    接下来进入正题!!!

    示例:

    首先创建一个表


    该表中对id列.name列.age列建立了一个联合索引 id_name_age_index,实际上相当于建立了三个索引(id)(id_name)(id_name_age)。

    下面介绍下可能会使用到该索引的几种情况:

    1.全值匹配查询时






    通过观察上面的结果图可知,where后面的查询条件,不论是使用(id,age,name)(name,id,age)还是(age,name,id)顺序,在查询时都使用到了联合索引,可能有同学会疑惑,为什么底下两个的搜索条件明明没有按照联合索引从左到右进行匹配,却也使用到了联合索引? 这是因为MySQL中有查询优化器explain,所以sql语句中字段的顺序不需要和联合索引定义的字段顺序相同,查询优化器会判断纠正这条SQL语句以什么样的顺序执行效率高,最后才能生成真正的执行计划,所以不论以何种顺序都可使用到联合索引。另外通过观察上面三个图中的key_len字段,也可说明在搜索时使用的联合索引中的(id_name_age)索引,因为id为int型,允许null,所以占5个字节,name为char(10),允许null,又使用的是latin1编码,所以占11个字节,age为int型允许null,所以也占用5个字节,所以该索引长度为21(5+11+5),而上面key_len的值也正好为21,可证明使用的(id_name_age)索引。

    2.匹配最左边的列时


    该搜索是遵循最左匹配原则的,通过key字段也可知,在搜索过程中使用到了联合索引,且使用的是联合索引中的(id)索引,因为key_len字段值为5,而id索引的长度正好为5(因为id为int型,允许null,所以占5个字节)。


    由于id到name是从左边依次往右边匹配,这两个字段中的值都是有序的,所以也遵循最左匹配原则,通过key字段可知,在搜索过程中也使用到了联合索引,但使用的是联合索引中的(id_name)索引,因为key_len字段值为16,而(id_name)索引的长度正好为16(因为id为int型,允许null,所以占5个字节,name为char(10),允许null,又使用的是latin1编码,所以占11个字节)。

    由于上面三个搜索都是从最左边id依次向右开始匹配的,所以都用到了id_name_age_index联合索引。

      那如果不是依次匹配呢?



    通过key字段可知,在搜索过程中也使用到了联合索引,但使用的是联合索引中的(id)索引,从key_len字段也可知。因为联合索引树是按照id字段创建的,但age相对于id来说是无序的,只有id只有序的,所以他只能使用联合索引中的id索引。



    通过观察发现上面key字段发现在搜索中也使用了id_name_age_index索引,可能许多同学就会疑惑它并没有遵守最左匹配原则,按道理会索引失效,为什么也使用到了联合索引?因为没有从id开始匹配,且name单独来说是无序的,所以它确实不遵循最左匹配原则,然而从type字段可知,它虽然使用了联合索引,但是它是对整个索引树进行了扫描,正好匹配到该索引,与最左匹配原则无关,一般只要是某联合索引的一部分,但又不遵循最左匹配原则时,都可能会采用index类型的方式扫描,但它的效率远不如最做匹配原则的查询效率高,index类型类型的扫描方式是从索引第一个字段一个一个的查找,直到找到符合的某个索引,与all不同的是,index是对所有索引树进行扫描,而all是对整个磁盘的数据进行全表扫描。




    这两个结果跟上面的是同样的道理,由于它们都没有从最左边开始匹配,所以没有用到联合索引,使用的都是index全索引扫描。

    3.匹配列前缀

     如果id是字符型,那么前缀匹配用的是索引,中坠和后缀用的是全表扫描。

    select * from staffs where id like 'A%';//前缀都是排好序的,使用的都是联合索引

    select * from staffs where id like '%A%';//全表查询

    select * from staffs where id like '%A';//全表查询

    4.匹配范围值



      在匹配的过程中遇到<>=号,就会停止匹配,但id本身就是有序的,所以通过possible_keys字段和key_len 字段可知,在该搜索过程中使用了联合索引的id索引(因为id为int型,允许null,所以占5个字节),且进行的是rang范围查询。


    由于不遵循最左匹配原则,且在id<4的范围中,age是无序的,所以使用的是index全索引扫描。



      不遵循最左匹配原则,但在数据库中id<2的只有一条(id),所以在id<2的范围中,age是有序的,所以使用的是rang范围查询。



      不遵循最左匹配原则,而age又是无序的,所以进行的全索引扫描。

    5.准确匹配第一列并范围匹配其他某一列



      由于搜索中有id=1,所以在id范围内age是无序的,所以只使用了联合索引中的id索引。

    展开全文
  • 数据库Mysql-索引的最左前缀匹配原则 最左前缀匹配原则: 最左优先,以最左边的为起点任何连续的索引都能匹配上。同时如果范围查询(>、<、between、like)就会停止匹配。 一、例子来理解最左前缀匹配原则 前一...

    数据库Mysql-索引的最左前缀匹配原则

    最左前缀匹配原则: 最左优先,以最左边的为起点任何连续的索引都能匹配上。同时如果范围查询(>、<、between、like)就会停止匹配。

    一、例子来理解最左前缀匹配原则

    前一篇文中,我们已经了解到Mysql数据库的索引的底层存储是一棵B+树,那么联合索引的底层也还是一棵B+树。只不过联合索引的键值对数量不是一个,而是多个。

    假如:构建一个(a,b)的联合索引,那么它在数据库底层的索引树是下列这样的:

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vLbG66aT-1615526690562)(E:\笔记\JAVA\Java复习框架-数据库\Mysql\temp\1-1.png)]

    可以看到a的值是有顺序的,1,1,2,2,3,3,而b的值是没有顺序的1,2,1,4,1,2。所以b = 2这种查询条件没有办法利用索引,因为联合索引首先是按a排序的,b是无序的。

    同时我们还可以发现在a值相等的情况下,b值又是按顺序排列的,但是这种顺序是相对的。所以最左匹配原则遇上范围查询就会停止,剩下的字段都无法使用索引。例如a = 1 and b = 2 a,b字段都可以使用索引,因为在a值确定的情况下b是相对有序的,而a>1and b=2,a字段可以匹配上索引,但b值不可以,因为a的值是一个范围,在这个范围中b是无序的。

    二、最左前缀匹配原则适用场景

    假如建立联合索引(a,b,c)

    2.1 全值匹配查询时

    select * from table_name where a = '1' and b = '2' and c = '3' 
    select * from table_name where b = '2' and a = '1' and c = '3' 
    select * from table_name where c = '3' and b = '2' and a = '1' 
    

    用到了索引

    where子句几个搜索条件顺序调换不影响查询结果,因为Mysql中有查询优化器,会自动优化查询顺序

    2.2 匹配左边的列时

    select * from table_name where a = '1' 
    select * from table_name where a = '1' and b = '2'  
    select * from table_name where a = '1' and b = '2' and c = '3'
    

    都从最左边开始连续匹配,用到了索引

    select * from table_name where  b = '2' 
    select * from table_name where  c = '3'
    select * from table_name where  b = '1' and c = '3' 
    

    这些没有从最左边开始,最后查询没有用到索引,用的是全表扫描

    select * from table_name where a = '1' and c = '3' 
    

    如果不连续时,只用到了a列的索引,b列和c列都没有用到

    2.3 匹配列前缀

    如果列是字符型的话它的比较规则是先比较字符串的第一个字符,第一个字符小的哪个字符串就比较小,如果两个字符串第一个字符相通,那就再比较第二个字符,第二个字符比较小的那个字符串就比较小,依次类推,比较字符串。

    如果a是字符类型,那么前缀匹配用的是索引,后缀和中缀只能全表扫描了

    select * from table_name where a like 'As%'; //前缀都是排好序的,走索引查询
    select * from table_name where  a like '%As'//全表查询
    select * from table_name where  a like '%As%'//全表查询
    

    4.匹配范围值

    select * from table_name where  a > 1 and a < 3
    

    可以对最左边的列进行范围查询

    select * from table_name where  a > 1 and a < 3 and b > 1;
    

    多个列同时进行范围查找时,只有对索引最左边的那个列进行范围查找才用到B+树索引,也就是只有a用到索引,在1<a<3的范围内b是无序的,不能用索引,找到1<a<3的记录后,只能根据条件 b > 1继续逐条过滤

    5. 精准匹配某一列并范围匹配另外一列

    如果左边的列是精确查找的,右边的列可以进行范围查找

    select * from table_name where  a = 1 and b > 3;
    

    a=1的情况下b是有序的,进行范围查找走的是联合索引

    6.排序

    一般情况下,我们只能把记录加载到内存中,再用一些排序算法,比如快速排序,归并排序等在内存中对这些记录进行排序,有时候查询的结果集太大不能在内存中进行排序的话,还可能暂时借助磁盘空间存放中间结果,排序操作完成后再把排好序的结果返回客户端。Mysql中把这种再内存中或磁盘上进行排序的方式统称为文件排序。文件排序非常慢,但如果order子句用到了索引列,就有可能省去文件排序的步骤

    select * from table_name order by a,b,c limit 10;
    

    因为b+树索引本身就是按照上述规则排序的,所以可以直接从索引中提取数据,然后进行回表操作取出该索引中不包含的列就好了

    order by的子句后面的顺序也必须按照索引列的顺序给出,比如

    select * from table_name order by b,c,a limit 10;
    

    这种颠倒顺序的没有用到索引

    select * from table_name order by a limit 10;
    select * from table_name order by a,b limit 10;
    

    这种用到部分索引

    select * from table_name where a =1 order by b,c limit 10;
    

    联合索引左边列为常量,后边的列排序可以用到索引

    展开全文
  • 最左前缀匹配原则:在MySQL建立联合索引时会遵守最左前缀匹配原则,即最左优先,在检索数据时从联合索引的最左边开始匹配。  要想理解联合索引的最左匹配原则,先来理解下索引的底层原理。索引的底层是一颗B+树,...
        

    最左前缀匹配原则:在MySQL建立联合索引时会遵守最左前缀匹配原则,即最左优先,在检索数据时从联合索引的最左边开始匹配。

      要想理解联合索引的最左匹配原则,先来理解下索引的底层原理。索引的底层是一颗B+树,那么联合索引的底层也就是一颗B+树,只不过联合索引的B+树节点中存储的是键值。由于构建一棵B+树只能根据一个值来确定索引关系,所以数据库依赖联合索引最左的字段来构建。

    举例:创建一个(a,b)的联合索引,那么它的索引树就是下图的样子。

       可以看到a的值是有顺序的,1,1,2,2,3,3,而b的值是没有顺序的1,2,1,4,1,2。但是我们又可发现a在等值的情况下,b值又是按顺序排列的,但是这种顺序是相对的。这是因为MySQL创建联合索引的规则是首先会对联合索引的最左边第一个字段排序,在第一个字段的排序基础上,然后在对第二个字段进行排序。所以b=2这种查询条件没有办法利用索引。

      由于整个过程是基于explain结果分析的,那接下来在了解下explain中的type字段和key_lef字段。

      1.type联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:(重点看ref,rang,index)

        system:表只有一行记录(等于系统表),这是const类型的特例,平时不会出现,可以忽略不计
        const:表示通过索引一次就找到了,const用于比较primary key 或者 unique索引。因为只需匹配一行数据,所有很快。如果将主键置于where列表中,mysql就能将该查询转换为一个const
        eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描。
        注意:ALL全表扫描的表记录最少的表如t1表
        ref:非唯一性索引扫描,返回匹配某个单独值的所有行。本质是也是一种索引访问,它返回所有匹配某个单独值的行,然而他可能会找到多个符合条件的行,所以它应该属于查找和扫描的混合体。
        range:只检索给定范围的行,使用一个索引来选择行。key列显示使用了那个索引。一般就是在where语句中出现了bettween、<、>、in等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点,结束于另一个点,不用扫描全部索引。
        index:Full Index Scan,index与ALL区别为index类型只遍历索引树。这通常为ALL块,应为索引文件通常比数据文件小。(Index与ALL虽然都是读全表,但index是从索引中读取,而ALL是从硬盘读取)
        ALL:Full Table Scan,遍历全表以找到匹配的行

      2.key_len显示MySQL实际决定使用的索引的长度。如果索引是NULL,则长度为NULL。如果不是NULL,则为使用的索引的长度。所以通过此字段就可推断出使用了那个索引。

        计算规则:

        1.定长字段,int占用4个字节,date占用3个字节,char(n)占用n个字符。

        2.变长字段varchar(n),则占用n个字符+两个字节。

        3.不同的字符集,一个字符占用的字节数是不同的。Latin1编码的,一个字符占用一个字节,gdk编码的,一个字符占用两个字节,utf-8编码的,一个字符占用三个字节。

        (由于我数据库使用的是Latin1编码的格式,所以在后面的计算中,一个字符按一个字节算)

        4.对于所有的索引字段,如果设置为NULL,则还需要1个字节。

    接下来进入正题!!!

    示例:

    首先创建一个表

     该表中对id列.name列.age列建立了一个联合索引 id_name_age_index,实际上相当于建立了三个索引(id)(id_name)(id_name_age)。

    下面介绍下可能会使用到该索引的几种情况:

    1.全值匹配查询时

      通过观察上面的结果图可知,where后面的查询条件,不论是使用(id,age,name)(name,id,age)还是(age,name,id)顺序,在查询时都使用到了联合索引,可能有同学会疑惑,为什么底下两个的搜索条件明明没有按照联合索引从左到右进行匹配,却也使用到了联合索引? 这是因为MySQL中有查询优化器explain,所以sql语句中字段的顺序不需要和联合索引定义的字段顺序相同,查询优化器会判断纠正这条SQL语句以什么样的顺序执行效率高,最后才能生成真正的执行计划,所以不论以何种顺序都可使用到联合索引。另外通过观察上面三个图中的key_len字段,也可说明在搜索时使用的联合索引中的(id_name_age)索引,因为id为int型,允许null,所以占5个字节,name为char(10),允许null,又使用的是latin1编码,所以占11个字节,age为int型允许null,所以也占用5个字节,所以该索引长度为21(5+11+5),而上面key_len的值也正好为21,可证明使用的(id_name_age)索引。

    2.匹配最左边的列时

     

      该搜索是遵循最左匹配原则的,通过key字段也可知,在搜索过程中使用到了联合索引,且使用的是联合索引中的(id)索引,因为key_len字段值为5,而id索引的长度正好为5(因为id为int型,允许null,所以占5个字节)。

      由于id到name是从左边依次往右边匹配,这两个字段中的值都是有序的,所以也遵循最左匹配原则,通过key字段可知,在搜索过程中也使用到了联合索引,但使用的是联合索引中的(id_name)索引,因为key_len字段值为16,而(id_name)索引的长度正好为16(因为id为int型,允许null,所以占5个字节,name为char(10),允许null,又使用的是latin1编码,所以占11个字节)。

      由于上面三个搜索都是从最左边id依次向右开始匹配的,所以都用到了id_name_age_index联合索引。

      那如果不是依次匹配呢?

      通过key字段可知,在搜索过程中也使用到了联合索引,但使用的是联合索引中的(id)索引,从key_len字段也可知。因为联合索引树是按照id字段创建的,但age相对于id来说是无序的,只有id只有序的,所以他只能使用联合索引中的id索引。

      通过观察发现上面key字段发现在搜索中也使用了id_name_age_index索引,可能许多同学就会疑惑它并没有遵守最左匹配原则,按道理会索引失效,为什么也使用到了联合索引?因为没有从id开始匹配,且name单独来说是无序的,所以它确实不遵循最左匹配原则,然而从type字段可知,它虽然使用了联合索引,但是它是对整个索引树进行了扫描,正好匹配到该索引,与最左匹配原则无关,一般只要是某联合索引的一部分,但又不遵循最左匹配原则时,都可能会采用index类型的方式扫描,但它的效率远不如最做匹配原则的查询效率高,index类型类型的扫描方式是从索引第一个字段一个一个的查找,直到找到符合的某个索引,与all不同的是,index是对所有索引树进行扫描,而all是对整个磁盘的数据进行全表扫描。

       这两个结果跟上面的是同样的道理,由于它们都没有从最左边开始匹配,所以没有用到联合索引,使用的都是index全索引扫描。

    3.匹配列前缀

      如果id是字符型,那么前缀匹配用的是索引,中坠和后缀用的是全表扫描。

    select * from staffs where id like 'A%';//前缀都是排好序的,使用的都是联合索引
    select * from staffs where id like '%A%';//全表查询
    select * from staffs where id like '%A';//全表查询

    4.匹配范围值

       在匹配的过程中遇到<>=号,就会停止匹配,但id本身就是有序的,所以通过possible_keys字段和key_len 字段可知,在该搜索过程中使用了联合索引的id索引(因为id为int型,允许null,所以占5个字节),且进行的是rang范围查询。

      由于不遵循最左匹配原则,且在id<4的范围中,age是无序的,所以使用的是index全索引扫描。

       不遵循最左匹配原则,但在数据库中id<2的只有一条(id),所以在id<2的范围中,age是有序的,所以使用的是rang范围查询。

       不遵循最左匹配原则,而age又是无序的,所以进行的全索引扫描。

    5.准确匹配第一列并范围匹配其他某一列

      由于搜索中有id=1,所以在id范围内age是无序的,所以只使用了联合索引中的id索引。

     

    展开全文
  • 索引的最左前缀原理: 通常我们在建立联合索引的时候,也就是对多个字段建立索引,相信建立过索引的同学们会发现,无论是oralce还是mysql都会让我们选择索引的顺序,比如我们想...这里就引出了数据库索引的最左前缀...
  • 咳咳,当我们在浏览器、在手机的电话联系人界面等等地方,输入一段字符串之后,就可以匹配出相应前缀的结果出来(如使用 AutoCompleteTextView 输入字段就会有相应的结果匹配),在存储本地数据的时候,由于数据后期...
  • 1.去除表字段前缀 使用table中的属性配置 searchString="^[^_]+",这个查找字符串就是使用正则表达式来匹配表的字段名 ...2.匹配数据库中所有的表(适合表太多) <table schema="" tableNa
  • sql语句实现多列前缀匹配求比值

    千次阅读 2013-11-05 15:10:35
    最近有一个需求,前缀匹配求比值,用sql语句实现起来比其他方法简单,所以就写了一个sql语句。如下: 数据库设计如下: |fs_page_turn_multi_num_data | CREATE TABLE `fs_page_turn_multi_num_data` (  `day` ...
  • 推荐阅读1、前言咳咳,当我们在浏览器、在手机的电话联系人界面等等地方,输入一段字符串之后,就可以匹配出相应前缀的结果出来(如使用 AutoCompleteTextView 输入字段就会有相应的结果匹配),在存储本地数据的...
  • 实验目的:了解索引对于全列匹配,最左前缀匹配、范围查询的影响。实验所用数据库见文章最底部连接。 实验软件版本:5.7.19-0ubuntu0.16.04.1-log (Ubuntu)实验存储引擎:InnoDB show index from `employees`.`...
  • 实验目的:了解索引对于全列匹配,最左前缀匹配、范围查询的影响。实验所用数据库见文章最底部连接。 show index from `employees`.`titles` 实验一、全列匹配 explain select * from `employees`.`titles` ...
  • 数据库最左前缀原则

    2019-10-05 22:08:53
    最左前缀原则:顾名思义是最左优先,以最左边的为起点任何连续的索引都能匹配上, 注:如果第一个字段是范围查询需要单独建一个索引 注:在创建多列索引时,要根据业务需求,where子句中使用最频繁的一列放在最...
  • 数据库索引最左前缀原则

    千次阅读 2018-11-30 12:53:53
    数据库最左前缀原则 最左前缀原则:顾名思义是最左优先,以最左边的为起点任何连续的索引都能匹配上, 注:如果第一个字段是范围查询需要单独建一个索引 注:在创建多列索引时,要根据业务需求,where子句中使用...
  • 数据库中,表、数据、索引之间的关系就类似于书籍、书籍内容、书籍目录。 倘若不使用索引,则MySQL必须遍历整个表,直到找到数据,而表越大,查询的时间则越长,则数据库的效率也就越低。而索引就类似于书籍的目录...
  • 上面的地址跟下面的地址是不一样的,上面的地址通常是在前端中找到对应的路由,下面的地址通常是被后端截取,下面的地址是由前端编写,作为一个跟后端连接的方式,前端会写好一个基础路径,还有前缀,包括一个微服务...
  • A:(这道题肯定难不住我啊)索引其实是一种数据结构,能够帮助我们快速的检索数据库中的数据 Q:那么索引具体采用的哪种数据结构呢? A:常见的MySQL主要有两种结构:Hash索引和B+ Tree索引,我们使用的是I
  • 假设数据库表中,记录如下: prefix | name | shortname | status --------+------+-----------+-------- abcdef | test | test | S abc | test | test | S ab | test | test | S ...
  • 数据库索引

    2020-07-31 11:43:36
    数据库索引 数据库索引就是为了使查询数据效率快。但是会占用一定的内存。 索引分类: 聚集索引(主键索引) ...(1) 遵从最左匹配原则,age是在最左边,所以A走索引; select * from student where age = 16
  • 如何优化数据库

    2017-06-06 16:59:49
    优化数据库查询 随着业务开发模式的变化,敏捷式开发被越来越多的团队采用,周期越来越短,很多数据库查询语句都是按照业务...最左前缀匹配原则,非常重要的原则,MySQL会一直向右匹配直到遇到范围查询(>、 3 and d
  • 数据库知识

    2020-05-14 14:29:07
    数据库设计三范式 1.表中的列只能含有原子性(不可再分)的值 数据库表中的字段都是单一属性的,不可再分。...2、最左前缀法则:如果索引了多列,查询从索引的最左前列开始,且不能跳过索引中的列.
  • 优化数据库查询

    2017-02-16 14:36:00
    下文前8条摘自Web服务端性能提升实践。 随着业务开发模式的变化,敏捷式开发被越来越多的团队采用,周期越来越短,很多数据库查询语句都是按照...最左前缀匹配原则,非常重要的原则,MySQL会一直向右匹配直到遇到范...
  • 数据库建索引原则

    2019-12-11 14:13:24
    1.最左前缀匹配原则,非常重要的原则 create index ix_name_email on s1(name,email,) - 最左前缀匹配:必须按照从左到右的顺序匹配 select * from s1 where name='egon'; #可以 select * from s1 where name='egon'...
  • 一、选择唯一性索引 1、唯一性索引的值是唯一的,可以更快速的通过该索引来确定某条记录。 2、为经常需要排序、分组和联合操作的字段建立...9、最左前缀匹配原则,非常重要的原则 10 、尽量选择区分度高的列作为.
  • 数据库中数据如下: ![图片说明](https://img-ask.csdn.net/upload/201711/07/1510038487_286903.png) ![图片说明](https://img-ask.csdn.net/upload/201711/07/1510038502_600586.png) java代码如下:::: !...
  • 数据库索引原则

    2018-03-19 11:13:18
    1.最左前缀匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查询(&gt;、&lt;、between、like)就停止匹配,比如a = 1 and b = 2 and c &gt; 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用...
  • mysql 数据库优化

    2021-02-20 10:58:51
    全值匹配我最爱,最左前缀要遵守。 带头大哥不能死,中间兄弟不能断。 索引列上少计算,范围之后全失效。 Like百分写最右,覆盖索引不写星。 不等空值还有or,索引失效要少用。 VAR引号不可丢,SQL高级也不难! 1、...
  • # 显示包含db_前缀数据库名称 show databases like 'db_*';12 1.2 使用数据库 use database名称1 1.3 创建数据库 create database dbname;1 通过location指定数据库路径 create database dbna...

空空如也

空空如也

1 2 3 4 5 ... 17
收藏数 333
精华内容 133
关键字:

数据库前缀匹配