Elasticsearch and MySQL

preface

ElasticSearch, the open source search and data analysis engine, is also one of the top noSQL.

It is often compared to relational databases, which are not strictly comparable.

But putting some things together will give us a quick understanding of ElasticSearch.

Select * from ElasticSearch (CURD); select * from ElasticSearch (CURD);

The example uses mysql and ElasticSearch 7.12.0.

Table operation

For simplicity, make a simple order table order-2021 to demonstrate.

Delete table

drop table `order- 2021.`
Copy the code

DELETE http://localhost:9200/order-2021
Copy the code

Create a table

create table `order- 2021.` ( 
`id` bigint(20) NOT NULL AUTO_INCREMENT, 
`order_id` varchar(32) NOT NULL, 
`cus_name` varchar(20) NOT NULL, 
`item_name` varchar(64) NOT NULL, 
`number` int NOT NULL, 
`create_time` bigint(20) NOT NULL, 
`update_time` bigint(20) NOT NULL.PRIMARY KEY (`id`),
KEY `idx_order_id` (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
Copy the code

PUT http://localhost:9200/order-2021
Content-Type: application/json

{
    "settings":{
        "number_of_shards": 1
    },
    "mappings":{
        "properties":{
            "order_id" :{
                "type":"keyword"
            },
            "cus_name" :{
                "type":"keyword"
            },
            "item_name" :{
                "type":"text",
                "fields": {
                    "keyword": { 
                        "type": "keyword",
                        "ignore_above": 256
                    }
                }
            },
            "number":{
                "type":"integer"
            },
            "create_time" :{
                "type":"long"
            },
            "update_time" :{
                "type":"long"
            }
        }
    }
}
Copy the code

NOTE:

1. Mysql indexes need to be incremented one by one, not ES.
2. Mysql has the ability to auto-generate ids, ES has the ability to auto-generate ids, and you can also customize ids on both sides.
3. Keyword and text are adjusted as needed.

To change the name of the table

rename table `order- 2021.` to `order`;
Copy the code

POST http://localhost:9200/_aliases
Content-Type: application/json

{
  "actions" : [
    { "add" : { "index" : "order-2021", "alias" : "order" } }
  ]
}
Copy the code

NOTE: Use an alias for ES, and then use both names.

Insert data

A single insert

insert into `order- 2021.` 
(order_id, cus_name, item_name, number, create_time, update_time) 
values 
('11'.'catcherwong'.'phone'.1.1619877257000.0)
Copy the code

POST http://localhost:9200/order-2021/_doc/
Content-Type: application/json

{ 
    "order_id" : "11", 
    "cus_name":"catcherwong", 
    "item_name":"phone",
    "number": 1,
    "create_time": 1619877257000, 
    "update_time": 0 
}
Copy the code

Bulk insert

insert into `order- 2021.` (order_id, cus_name, item_name, number, create_time, update_time) 
values 
('12'.'catcherwong'.'phone'.1.1619877257000.0),
('13'.'catcherwong'.'item-1'.2.1619977257000.0),
('14'.'catcherwong'.'item-2'.3.1614877257000.0);
Copy the code

POST http://localhost:9200/_bulk
Content-Type: application/x-ndjson

{ "index" : { "_index" : "order-2021" } }
{ "order_id" : "12", "cus_name":"catcherwong", "item_name":"phone", "create_time": 1619877257000, "update_time": 0 }
{ "index" : { "_index" : "order-2021" } }
{ "order_id" : "13", "cus_name":"catcherwong", "item_name":"item-1", "create_time": 1619977257000, "update_time": 0 }
{ "index" : { "_index" : "order-2021" } }
{ "order_id" : "14", "cus_name":"catcherwong", "item_name":"item-2", "create_time": 1614877257000, "update_time": 0 }

Copy the code

NOTE:

1. The ES batch operation needs to note that each line should have a newline character at the end\nAt the end
2. Content-type is specified as application/x-ndjson

Update the data

Update by ID

update `order- 2021.` 
set update_time = '1619877307000', cus_name = 'catcherwong-1' 
where id = '6wvox3kB4OeD0spWtstW'
Copy the code

POST http://localhost:9200/order-2021/_update/6wvox3kB4OeD0spWtstW
Content-Type: application/json

{
    "doc":{
        "update_time" : 1619877307000,
        "cus_name": "catcherwong-1"
    }    
}
Copy the code

Update according to query conditions

update `order- 2021.` 
set update_time = '1619877307000', cus_name = 'catcherwong-1' 
where order_id = '11'
Copy the code

POST http://localhost:9200/order-2021/_update_by_query
Content-Type: application/json { "script":{ "source":"ctx._source['cus_name']=params.cus_name; ctx._source['update_time']=params.update_time;" , "params":{ "cus_name":"catcherwong-1", "update_time": 1619877307000 } }, "query":{ "term":{ "order_id":"11" } } }Copy the code

NOTE: The ES conditional update is divided into two parts, one is query, which is the WHERE part, and the other is script, which is the set part.

Delete the data

Delete by Id

delete from `order- 2021.` 
where id = 'c8cb33kBoze4GtqD9rTs'
Copy the code

DELETE http://localhost:9200/order-2021/_doc/c8cb33kBoze4GtqD9rTs
Copy the code

Delete the vm based on the query conditions

delete from `order- 2021.` 
where order_id = '11'
Copy the code

POST http://localhost:9200/order-2021/_delete_by_query
Content-Type: application/json

{
  "query": {
    "term": {
      "order_id": "11"
    }
  }
}
Copy the code

Query data

All the query

select * from `order- 2021.`
Copy the code

GET http://localhost:9200/order-2021/_search
Content-Type: application/json

{
  "query": {    
    "match_all": {}
  }
}
Copy the code

Conditions of the query

select * from `order- 2021.` 
where cus_name in ("catcher-61333", "catcher-89631") 
and create_time > = 0 
and create_time < = 1622555657322
Copy the code

GET http://localhost:9200/order/_search
Content-Type: application/json

{
    "query":{
        "bool":{
            "filter":[
                { "terms":{ "cus_name" : [ "catcher-61333", "catcher-89631" ] }},
                { "range":{ "create_time" : { "gte": 0, "lte": 1622555657322 } } }
            ]
        }        
    }
}
Copy the code

NOTE:

1. There are a lot of conditional queries in ES, and only some of them are listed here.
2. The ES query, by default, will have scoring operation, which will lose performance, and the conventional SQL query does not need these, so use bool + filter to ignore.

Query a specified field

select cus_name, order_id 
from `order- 2021.` 
where cus_name in ("catcher-61333", "catcher-89631") 
and create_time > = 0 
and create_time < = 1622555657322
Copy the code

GET http://localhost:9200/order-2021/_search
Content-Type: application/json

{
    "_source":[ "cus_name", "order_id"],
    "query":{
        "bool":{
            "filter":[
                { "terms":{ "cus_name" :  [ "catcher-61333", "catcher-89631" ] }},
                { "range":{ "create_time" : { "gte": 0, "lte": 1622555657322 } } }
            ]
        }        
    }
}
Copy the code

NOTE: If you need only a few fields, you can specify them by _source.

The number of queries

select count(*) 
from `order- 2021.` 
where cus_name in ("catcher-61333", "catcher-89631") 
and create_time > = 0 
and create_time < = 1622555657322
Copy the code

GET http://localhost:9200/order-2021/_count
Content-Type: application/json

{
    "query":{
        "bool":{
            "filter":[
                { "terms":{ "cus_name" :  [ "catcher-61333", "catcher-89631" ] }},
                { "range":{ "create_time" : { "gte": 0, "lte": 1622555657322 } } }
            ]
        }        
    }
}
Copy the code

NOTE: According to the specified conditions to obtain the number of items, it is recommended to use _count, _search search results may not be accurate.

Shallow paging

select cus_name, order_id 
from `order- 2021.` 
where cus_name in ("catcher-61333", "catcher-89631") 
and create_time > = 0 
and create_time < = 1622555657322
order by create_time desc 
limit 0.10
Copy the code

GET http://localhost:9200/order-2021/_search
Content-Type: application/json

{
    "_source":[ "cus_name", "order_id"],
    "query":{
        "bool":{
            "filter":[
                { "terms":{ "cus_name" :  [ "catcher-61333", "catcher-89631" ] }},
                { "range":{ "create_time" : { "gte": 0, "lte": 1622555657322 } } }
            ]
        }        
    },
    "size":10,
    "from":0,
    "sort":[
        { "create_time":{ "order":"desc"} }
    ]
}
Copy the code

NOTE: Shallow pagination, the page number should not be too deep, suitable for scrolling loading scenarios, deep pagination can consider SearchAfter

Group By

select number, count(*) as number_count 
from `order- 2021.` 
where create_time > = 0 
and create_time < = 1622555657322
group by number 
order by number asc
Copy the code

GET http://localhost:9200/order-2021/_search
Content-Type: application/json

{
    "size":0,
    "aggs": {
        "number_count": {
            "terms": {
                "field": "number",
                "order" : { "_key" : "asc" }
            }
        }
    },
    "query":{
        "bool":{
            "filter":[
                { "range":{ "create_time" : { "gte": 0, "lte": 1622555657322 } } }
            ]
        }        
    }
}
Copy the code

NOTE: Group by is a kind of aggregation operation, agGS is used, aggregation is not used, so the size is set to 0.

Avg/Min/Max/Sum

select avg(number) as number_avg, 
min(number) as number_min, 
max(number) as number_max, 
sum(number) as number_sum 
from order 
where create_time > = 0 
and create_time < = 1622555657322 
Copy the code

GET http://localhost:9200/order/_search
Content-Type: application/json

{
    "size":0,
    "query":{
        "bool":{
            "filter":[
                { "range":{ "create_time" : { "gte": 0, "lte": 1622555657322 } } }
            ]
        }        
    },
    "aggs": {
        "number_avg": {
            "avg": {
                "field": "number"
            }
        },
        "number_min": {
            "min": {
                "field": "number"
            }
        },
        "number_max": {
            "max": {
                "field": "number"
            }
        },
        "number_sum": {
            "sum": {
                "field": "number"
            }
        }
    }
}
Copy the code

Write in the last

SQL > select * from Elasticsearch; select * from Elasticsearch;

For some Elasticsearch specific features, it is also possible to query the official documentation.

Follow my public account “Bucai Old Huang” and share with you what huang sees and hears in the first time.