为什么MySql不会自动优化BETWEEN查询？ [英] Why doesn't MySql automatically optimises BETWEEN query?

问题描述

我对同一输出有两个查询

I have two query for same output

慢查询：

SELECT 
    *
FROM
    account_range
WHERE
    is_active = 1 AND '8033576667466317' BETWEEN range_start AND range_end;

执行时间： ~800 ms 。

解释：

+----+-------------+---------------+------------+------+-------------------------------------------+------+---------+------+--------+----------+-------------+
| id | select_type | table         | partitions | type | possible_keys                             | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------------+------------+------+-------------------------------------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | account_range | NULL       | ALL  | range_start,range_end,range_se_active_idx | NULL | NULL    | NULL | 940712 |     2.24 | Using where |
+----+-------------+---------------+------------+------+-------------------------------------------+------+---------+------+--------+----------+-------------+

非常快速查询： 从这里学习

SELECT 
    *
FROM
    account_range
WHERE
    is_active = 1 AND 
    range_start = (SELECT 
            MAX(range_start)
        FROM
            account_range
        WHERE
            range_start <= '8033576667466317') AND 
    range_end = (SELECT 
            MIN(range_end)
        FROM
            account_range
        WHERE
            range_end >= '8033576667466317')

执行时间： ~1ms

说明：

+----+-------------+---------------+------------+------+-------------------------------------------+---------------------+---------+-------------------+------+----------+------------------------------+
| id | select_type | table         | partitions | type | possible_keys                             | key                 | key_len | ref               | rows | filtered | Extra                        |
+----+-------------+---------------+------------+------+-------------------------------------------+---------------------+---------+-------------------+------+----------+------------------------------+
|  1 | PRIMARY     | account_range | NULL       | ref  | range_start,range_end,range_se_active_idx | range_se_active_idx | 125     | const,const,const |    1 |   100.00 | NULL                         |
|  3 | SUBQUERY    | NULL          | NULL       | NULL | NULL                                      | NULL                | NULL    | NULL              | NULL |     NULL | Select tables optimized away |
|  2 | SUBQUERY    | NULL          | NULL       | NULL | NULL                                      | NULL                | NULL    | NULL              | NULL |     NULL | Select tables optimized away |
+----+-------------+---------------+------------+------+-------------------------------------------+---------------------+---------+-------------------+------+----------+------------------------------+

表格结构：

CREATE TABLE account_range (
    id int(11) unsigned NOT NULL AUTO_INCREMENT,
    range_start varchar(20) NOT NULL,
    range_end varchar(20) NOT NULL,
    is_active tinyint(1) NOT NULL,
    bank_name varchar(100) DEFAULT NULL,
    addedon timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updatedon timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    description text,
    PRIMARY KEY (id),
    KEY range_start (range_start),
    KEY range_end (range_end),
    KEY range_se_active_idx (range_start , range_end , is_active)
)  ENGINE=InnoDB AUTO_INCREMENT=946132 DEFAULT CHARSET=utf8;

请解释为什么MySql不能自动优化 BETWEEN 查询？

Please do explain Why doesn't MySql automatically optimizes BETWEEN query?

更新：

从@kordirko回答实现了我的错误。我的表只包含非重叠范围，因此两个查询都返回相同的结果。

Update:
Realised my mistake from @kordirko answer. My table contains only non-overlapping ranges, so both queries are returning same results.

推荐答案

这样的比较没有意义，因为你将苹果与橙子进行比较。


这两个查询不是等价的，它们提供不同的结果，因此MySql以不同的方式优化它们，它们的计划可能不同。



见这个简单的例子： http：// sqlfiddle.com/#!9/98678/2

Such a comparision doesn't make sense, since you are comparing apples to oranges.

These two queries are not eqivalent, they give different resuts,
thus MySql optimises them in a different way and their plans can differ.

See this simple example: http://sqlfiddle.com/#!9/98678/2

create table account_range(
  is_active int,
  range_start int,
  range_end int
 );

 insert into account_range values
 (1,-20,100), (1,10,30);

第一个查询给出2行：

select * from account_range
 where is_active = 1 and 25 between range_start AND range_end;

| is_active | range_start | range_end |
|-----------|-------------|-----------|
|         1 |         -20 |       100 |
|         1 |          10 |        30 |

第二个查询仅提供1行：

Second query gives only 1 row:

SELECT * FROM account_range
WHERE
    is_active = 1 AND 
    range_start = (SELECT MAX(range_start)
                   FROM account_range
                   WHERE range_start <= 25
    ) AND 
    range_end = (SELECT MIN(range_end)
                 FROM account_range
                 WHERE range_end >= 25
    )

| is_active | range_start | range_end |
|-----------|-------------|-----------|
|         1 |          10 |        30 |

---

加快此查询速度（第一个） ，两个位图索引可以与位图和操作一起使用 - 但MySql没有这样的功能。


另一种选择是空间索引（例如PostgreSql中的GIN索引：< a href =http://www.postgresql.org/docs/current/static/textsearch-indexes.html\"rel =nofollow> http://www.postgresql.org/docs/current/static/textsearch- indices.html ）。


另一个选项是星形转换（或星型模式） - 您需要将此表划分为两个维度或度量表和一个事实表....但这是一个太广泛的主题，如果你想了解更多，你可以从这里开始： https://en.wikipedia.org/wiki/Star_schema

这篇关于为什么MySql不会自动优化BETWEEN查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！