es索引对应mysql es搜索引擎结合mysql

转载

epeppanda 2023-10-19 21:35:44

文章标签 es索引对应mysql elasticsearch mysql 搜索引擎 analyzer 文章分类 MySQL 数据库

文章目录

系列文章目录
前言
一、首先通过kibana的web界面的devtool或者postman创建一个索引
二、同步数据
三、用kibana的devtool来测试查询结果
四、最后在php里如何使用elasticSearch
总结

前言

一直以来，公司的项目简单的搜索都是直接通过mysql字段加索引的方式来完成查询，随着业务的越来越复杂和db数据量达到几千万级mysql的索引有些吃力了，被逼着自已搭建了elasticsearch服务，来解决业务中用mysql的索引和全文索引无法解决的业务搜索场景，关于elasticSearch+elasticsearch-head +kibana + go-mysql-elasticsearch 环境搭建见我前面的系列实战教程

一、首先通过kibana的web界面的devtool或者postman创建一个索引

es索引对应mysql es搜索引擎结合mysql_elasticsearch

创建一个elasticsearch的索引数据库，创建索引的语句如下：

PUT /kdsysorder
{
  "settings": {
    "number_of_shards": 3,
    "number_of_replicas": 0,
    "analysis": {
      "filter": {
        "rev_ordersn_filter": {
          "type": "edge_ngram",
          "min_gram": 6,
          "max_gram": 64
        },
        "pre_ordersn_filter": {
          "type": "edge_ngram",
          "min_gram": 10,
          "max_gram": 64
        }
      },
      "analyzer": {
        "rev_ordersn_ana": {
          "type": "custom",
          "tokenizer": "standard",
          "filter": [
            "reverse",
            "rev_ordersn_filter",
            "reverse"
          ]
        },
        "pre_ordersn_ana": {
          "type": "custom",
          "tokenizer": "standard",
          "filter": [
            "pre_ordersn_filter"
          ]
        }
      }
    }
  },
  "mappings": {
    "properties": {
      "order_sn": {
        "type": "text",
        "analyzer": "rev_ordersn_ana",
        "fields": {
          "raw": {
            "type": "text",
            "analyzer": "pre_ordersn_ana"
          }
        }
      },
      "out_no": {
        "type": "text",
        "analyzer": "rev_ordersn_ana",
        "fields": {
          "raw": {
            "type": "text",
            "analyzer": "pre_ordersn_ana"
          }
        }
      },
      "tel": {
        "type": "keyword"
      },
      "mobile": {
        "type": "keyword"
      },
      "ordertel": {
        "type": "keyword"
      },
      "invoice_no": {
        "type": "keyword"
      }
    }
  }
}

创建成功后通过elasticsearch head的管理界面可以看到索引库的状态

es索引对应mysql es搜索引擎结合mysql_搜索引擎_02

二、同步数据

使用go-mysql-elasticsearch初始化和实时同步mysql数据到elastic,具体的见我原来的教程：
用go-mysql-elasticsearch同步MYSQL5.7.20数据到ElasticSearch8.1.3图文教程

三、用kibana的devtool来测试查询结果

elasticSearch的查询语句是非常强大的，基本上可以满足各种场景使用，有时间了大家最好多看看官方的手册，但是是英文版，理解起来其实也不困难，我这里的业务场景是实现对订单中的订单号，手机号，平台单号及快递单号多个字段的全文索引查询，语句如下：

es索引对应mysql es搜索引擎结合mysql_mysql_03

GET kdsysorder/_search
{
  "query": {
      "bool" : {
        "should":[
          {
            "match_phrase": {
              "order_sn": {
                "query":"2042682625",
                "analyzer": "keyword"                
              }              
            }            
          },
	  {
            "match_phrase": {
              "order_sn.raw": {
                "query":"2042682625",
                "analyzer": "keyword"                
              }              
            }            
          },
	  {
            "match_phrase": {
              "tel": {
                "query":"2042682625",
                "analyzer": "keyword"                
              }              
            }            
          },
	  {
            "match_phrase": {
              "mobile": {
                "query":"2042682625",
                "analyzer": "keyword"                
              }              
            }            
          },
	  {
            "match_phrase": {
              "ordertel": {
                "query":"2042682625",
                "analyzer": "keyword"                
              }              
            }            
          },
	  {
            "match_phrase": {
              "outno": {
                "query":"2042682625",
                "analyzer": "keyword"                
              }              
            }            
          },
	  {
            "match_phrase": {
              "outno.raw": {
                "query":"2042682625",
                "analyzer": "keyword"                
              }              
            }            
          }
        ]
      }
  }
}

这里説明的我这里订单号是支持后6位搜索的，mysql搜索后几位是不能用索引的，但用elasticsearch就可以实现订单后几位的搜索，每个项目搜索的业务场景不一样，搜索子句也不一样，大家可以根据自已的业务场景自已定制自已的搜索子句

四、最后在php里如何使用elasticSearch

在php中需要首先引入elasticsearch第三方包：

composer require elasticsearch/elasticsearch

然后在业务代码中引入elasticsearch扩展：

require ROOT_PATH . 'plugins/vendor/autoload.php';
use Elastic\Elasticsearch\ClientBuilder;

最后实现业务中的搜索逻辑:

if($filter['order_sn'])
	{
        $opensearch =0;
	    $elastic_client = \Elasticsearch\ClientBuilder::create()
            ->setHosts(['172.16.0.14:9200'])
            ->build();
        $params = [
            'index' => 'kdsysorder',
            'body'  => [
                'query' => [
                    'bool' => [
                        'should'=>[
                            ['match_phrase'=>['order_sn'=>['query'=>$filter['order_sn'],'analyzer'=>'keyword']]],
                            ['match_phrase'=>['order_sn.raw'=>['query'=>$filter['order_sn'],'analyzer'=>'keyword']]],
                            ['match_phrase'=>['tel'=>['query'=>$filter['order_sn'],'analyzer'=>'keyword']]],
                            ['match_phrase'=>['mobile'=>['query'=>$filter['order_sn'],'analyzer'=>'keyword']]],
                            ['match_phrase'=>['ordertel'=>['query'=>$filter['order_sn'],'analyzer'=>'keyword']]],
                            ['match_phrase'=>['outno'=>['query'=>$filter['order_sn'],'analyzer'=>'keyword']]],
                            ['match_phrase'=>['outno.raw'=>['query'=>$filter['order_sn'],'analyzer'=>'keyword']]]
                        ],
                    ]
                ]
            ]
        ];
        $response = $elastic_client->search($params);
        if( $response['hits']['total']['value'] >0  && $response['hits']['total']['value'] <200)
        {
            $osoids = array();
            foreach( $response['hits']['hits'] as $ositem)
            {
                $osoids[] = $ositem['_id'];
            }
            $where = " where 1 ";
            $where .= " and o.order_id " . db_create_in($osoids);
            $opensearch =1;
        }

        if($opensearch ==0){
            //$where = " where 1 and  o.order_sn like  '%".$filter['order_sn']."' ";
            $where = " where 1 and MATCH(o.`order_sn`,o.`out_no`,o.`mobile`,o.`ordertel`,o.`tel`,o.`invoice_no`) AGAINST('\"" . $filter['order_sn'] . "\"' IN BOOLEAN MODE ) ";
            //$where = " where 1 and o.order_id > '613429' and  o.order_sn = '".$filter['order_sn']."' ";
        }
	}

相关代码仅供参考，项目中需要根据自已的情况做适当调整，这里仅供参考，如有问题欢迎联系我沟通