Elasticsearch DSL与MySQL的基础语法对比

2021-12-26 4172点热度 12人点赞 0条评论

作者第一次使用 ES 进行数据查询，目前仅总结了一些本次用过的查询语法，后续用到其他的语法，会再在本文中更新

总述

Elasticsearch 使用的是 Query DSL 进行查询的，相比SQL而言……
（好吧，我也不知道二者之间孰强孰弱）总之，语法差别很大。

为了后续可以温故知新，所以我总结了一些常用的SQL语句进行对比。

查询

基础查询结构

$dsl = [
    'index' => 'index_name',         // FROM   index_name
    '_source_include' => 'orderID',     // SELECT orderId
    'body' => [
        'size' => 10,                         // Limit 10
        'query' => $query,                    // WHERE
        'sort' => [                           // ORDER BY
            'orderIDLong' => [                // 这里的key是字段名称
                'order' => 'desc',            // DESC
            ],
        ],
        'collapse' => [               // DISTINCT (orderId)
            'field' => 'orderID',
        ],
        'aggs' => [
            'custom_name' => [   // 相当于 AS `custom_name`
                'cardinality' => [    // COUNT( DISTINCT (orderId) ) （最终得到的值是近似值）
                    'field' => 'orderID',
                    'precision_threshold' => 100,
                ],
            ],
        ],
    ],
];
echo 'dsl:[' . json_encode($dsl);
$response = $esClient->search($dsl);
echo 'return ' . json_encode($response);

index：相当于 FROM，索引名
_source_include：相当于 SELECT，若需要查询多个字段，则应像SQL一样，使用逗号分隔
body：body写查询条件，在该层下一般用到以下字段size、query、sort、collapse、aggs
- size：相当于LIMIT，从第0条数据开始取n条数据
- from：从第m条数据开始取数据
- query：相当于WHERE，搜索条件（下一章介绍）
- sort：相当于ORDER BY，排序规则
- collapse：相当于 DISTINCT
- aggs：聚合操作，在本例中使用了COUNT(DISTINCT(orderID))。
  - cardinality：相当于COUNT( DISTINCT (orderID) )近似数值，precision_threshold（计数精度）

ES提供了近似的基数聚合计数
precision_threshold 代表了count(DISTINCT (xx))的精度，值越大，ES占用内存越多，相对的得到的结果精度越高，当数据较少时，在较低的 precision_threshold 值下，近似聚合也可以得到准确的值。

query（WHERE）结构

$query = [
    'bool' => [
        'must' => [
            ['term' => ['itemName' => '衣服']],
            [
                'bool' => [
                    'should' => [
                        ['term' => ['size' => 'L码']],
                        ['term' => ['size' => 'XL码']],
                    ],
                ],
            ],
        ],
        'must_not' => [
            ['term' => ['itemName' => '裙子']],
        ],
        'should' => [
            ['term' => ['brand' => '阿迪○斯']],
            ['term' => ['brand' => '海○之家']],
        ],
    ],
    'filter' => ['range' => ['upTime' => ['gt' => '2021-12-26 00:00']]]     // 时间一般是时间戳
];

query中需要先包一层bool，在bool中有四个基础结构must、must_noot、should、filter，四者之间的基础关系如下SQL语句所示

SELECT * 
FROM index_name
WHERE (  [must_1] AND [must_2] AND [must_3]  ) 
  AND NOT ( [must_not_1] OR [must_not_2] OR [must_not_3] )
  AND ( 
        [should_1] 
        OR [should_2]
        OR [should_3]
      )
  AND (  [filter_1] AND [filter_1]  )

三个基础结构中，都可以包含term、terms、range或是bool等
- term：相当于 =
- terms：相当于 IN
- range：适用于区间范围大于、小于
- gt: > 大于（greater than）
- lt: < 小于（less than）
- gte: >= 大于或等于（greater than or equal to）
- lte: <= 小于或等于（less than or equal to）
- bool：用于组合成复杂的查询语句

本章开头举例的DSL，翻译为SQL语句就是

SELECT *
FROM table_name
WHERE itemName = '衣服'
  AND (size = 'L码' OR size = 'XL码')
  AND itemName <> '裙子'
  AND brand IN ('阿迪○斯', '海○之家')
  AND upTime > '2021-12-26 00:00'    -- 时间一般是时间戳

翻译成人话（中文）就是：