mysql 5.7.11查詢分區表的一個難題

2024-07-24 12:31:30

字體：大中小

來源：轉載

供稿：網友

mysql 查詢一個分區表，當查詢條件存在數據時執行效率OK,當不存在數據時執行不完，一直在sending data,當去掉desc就沒問題。換個版本貌似也沒問題。

mysql> select version();

+------------+

| version() |

+------------+

| 5.7.11-log |

+------------+

1 row in set (0.00 sec)

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> 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> 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)

（編輯：武林網）

上一篇：MySQL 5.7.17 安置報錯CMake Error at cmake boost.cmake 81

下一篇：mysql show processlist命令細說