mysql 5.7.11查询分区表的一个问题

mysql 查询一个分区表，当查询条件存在数据时执行效率OK,当不存在数据时执行不完，一直在sending data,当去掉desc就没问题。换个版本貌似也没问题。 mysql> select version(); +------------+ | version()  | +------------+ | 5.7.11-log | +------------+ 1 row in set (0.00 sec) mysql> use zabbix Database changed mysql> SELECT * FROM history h WHERE h.itemid='106107' AND h.clock>1533723653 ORDER BY h.clock DESC LIMIT 2 OFFSET 0; +--------+------------+-----------+-----------+ | itemid | clock      | value     | ns        | +--------+------------+-----------+-----------+ | 106107 | 1533828123 | 1792.0000 | 151803000 | | 106107 | 1533828003 | 1792.0000 |  44536142 | +--------+------------+-----------+-----------+ 2 rows in set (0.00 sec) mysql> explain SELECT * FROM history h WHERE h.itemid='106107' AND h.clock>1533723653 ORDER BY h.clock DESC LIMIT 2 OFFSET 0; +----+-------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------+-------+-----------------------------+-----------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions                                                                                                                              | type  | possible_keys               | key       | key_len | ref  | rows | filtered | Extra                 | +----+-------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------+-------+-----------------------------+-----------+---------+------+------+----------+-----------------------+ |  1 | SIMPLE      | h     | p201808,p201809,p201810,p201811,p201812,p201901,p201902,p201903,p201904,p201905,p201906,p201907,p201908,p201909,p201910,p201911,p201912 | range | history_1,idx_history_clock | history_1 | 12      | NULL |  172 |   100.00 | Using index condition | +----+-------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------+-------+-----------------------------+-----------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.05 sec) ysql> SELECT * FROM history h WHERE h.itemid='1061055' AND h.clock>1533723653 ORDER BY h.clock DESC LIMIT 2 OFFSET 0; ^C^C -- query aborted ERROR 1317 (70100): Query execution was interrupted mysql> explain SELECT * FROM history h WHERE h.itemid='1061055' AND h.clock>1533723653 ORDER BY h.clock DESC LIMIT 2 OFFSET 0; +----+-------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------+-------+-----------------------------+-----------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions                                                                                                                              | type  | possible_keys               | key       | key_len | ref  | rows | filtered | Extra                 | +----+-------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------+-------+-----------------------------+-----------+---------+------+------+----------+-----------------------+ |  1 | SIMPLE      | h     | p201808,p201809,p201810,p201811,p201812,p201901,p201902,p201903,p201904,p201905,p201906,p201907,p201908,p201909,p201910,p201911,p201912 | range | history_1,idx_history_clock | history_1 | 12      | NULL |    1 |   100.00 | Using index condition | +----+-------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------+-------+-----------------------------+-----------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT * FROM history h WHERE h.itemid='1061055' AND h.clock>1533723653 ORDER BY h.clock  LIMIT 2 OFFSET 0; Empty set (0.00 sec) mysql> explain SELECT * FROM history h WHERE h.itemid='1061055' AND h.clock>1533723653 ORDER BY h.clock  LIMIT 2 OFFSET 0; +----+-------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------+-------+-----------------------------+-----------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions                                                                                                                              | type  | possible_keys               | key       | key_len | ref  | rows | filtered | Extra                 | +----+-------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------+-------+-----------------------------+-----------+---------+------+------+----------+-----------------------+ |  1 | SIMPLE      | h     | p201808,p201809,p201810,p201811,p201812,p201901,p201902,p201903,p201904,p201905,p201906,p201907,p201908,p201909,p201910,p201911,p201912 | range | history_1,idx_history_clock | history_1 | 12      | NULL |    1 |   100.00 | Using index condition | +----+-------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------+-------+-----------------------------+-----------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) 创建了降序索引，效率提升 mysql> create index idx_history_2 on  history (itemid desc); Query OK, 0 rows affected (36 min 50.11 sec) Records: 0  Duplicates: 0  Warnings: 0 mysql>  mysql>  SELECT * FROM history h WHERE h.itemid='1060001055' AND h.clock>1533723653 ORDER BY h.clock DESC LIMIT 2 OFFSET 0; Empty set (0.00 sec) mysql> explain SELECT * FROM history h WHERE h.itemid='1060001055' AND h.clock>1533723653 ORDER BY h.clock DESC LIMIT 2 OFFSET 0; +----+-------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------+------+-------------------------------------------+-----------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions                                                                                                                              | type | possible_keys                             | key       | key_len | ref   | rows | filtered | Extra       | +----+-------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------+------+-------------------------------------------+-----------+---------+-------+------+----------+-------------+ |  1 | SIMPLE      | h     | p201808,p201809,p201810,p201811,p201812,p201901,p201902,p201903,p201904,p201905,p201906,p201907,p201908,p201909,p201910,p201911,p201912 | ref  | history_1,idx_history_clock,idx_history_2 | history_1 | 8       | const |    1 |    31.59 | Using where | +----+-------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------+------+-------------------------------------------+-----------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) 升级了版本，效率提升 mysql>  mysql> select version(); +---------------+ | version()     | +---------------+ | 5.7.22-22-log | +---------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM history h WHERE h.itemid='1060001055' AND h.clock>1533723653 ORDER BY h.clock DESC LIMIT 2 OFFSET 0; ERROR 1046 (3D000): No database selected mysql> show databses; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'databses' at line 1 mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | performance_schema | | sys                | | zabbix             | +--------------------+ 5 rows in set (0.00 sec) mysql> use zabbix Database changed mysql> SELECT * FROM history h WHERE h.itemid='1060001055' AND h.clock>1533723653 ORDER BY h.clock DESC LIMIT 2 OFFSET 0; Empty set (0.01 sec) mysql> explain  SELECT * FROM history h WHERE h.itemid='1060001055' AND h.clock>1533723653 ORDER BY h.clock DESC LIMIT 2 OFFSET 0; +----+-------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------+-------+-----------------------------+-----------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions                                                                                                                              | type  | possible_keys               | key       | key_len | ref  | rows | filtered | Extra                 | +----+-------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------+-------+-----------------------------+-----------+---------+------+------+----------+-----------------------+ |  1 | SIMPLE      | h     | p201808,p201809,p201810,p201811,p201812,p201901,p201902,p201903,p201904,p201905,p201906,p201907,p201908,p201909,p201910,p201911,p201912 | range | history_1,idx_history_clock | history_1 | 12      | NULL |    1 |   100.00 | Using index condition | +----+-------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------+-------+-----------------------------+-----------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)