mysql 5.7.11查询分区表的一个疑惑
发布时间：2022-03-31 11:35:53 所属栏目：MySql教程 来源：互联网
导读：mysql 查询一个分区表，当查询条件存在数据时执行效率OK,当不存在数据时执行不完，一直在sending data,当去掉desc就没问题。换个版本貌似也没问题。 mysql select version(); +------------+ | version() | +------------+ | 5.7.11-log | +------------+
|
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; 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 | +----+-------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------+-------+-----------------------------+-----------+---------+------+------+----------+-----------------------+ 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 | （编辑：应用网_丽江站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
浙公网安备 33038102330468号