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)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29863023/viewspace-2199600/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 一個MySQL多表查詢的問題MySql
- 【MYSQL】 分割槽表MySql
- MySQL 分割槽表探索MySql
- Oracle查詢Interval partition分割槽表內資料Oracle
- MySQL的分割槽（一）MySql
- 一次分割槽查詢異常的分析
- MySQL 拷貝一個InnoDB分割槽表到另一個例項MySql
- Mysql表分割槽實現MySql
- mysql 進行表分割槽MySql
- Mysql表分割槽實操MySql
- PostgreSQL/LightDB分割槽表之常見問題SQL
- PostgreSQL 原始碼解讀（98）- 分割槽表#4（資料查詢路由#1-“擴充套件”分割槽表）SQL原始碼路由套件
- mysql~關於mysql分割槽表的測試MySql
- MySQL調優之分割槽表MySql
- MySQL 分割槽表知識整理MySql
- Oracle有沒有MySQL的分割槽DDL遇到的問題OracleMySql
- 【Linux】MBR磁碟分割槽表只能有四個分割槽？Linux
- interval 分割槽表clob預設表空間指定問題
- oracle分割槽表和分割槽表exchangeOracle
- mysql 從一個表中查詢，插入到另一個表中MySql
- PG的非分割槽表線上轉分割槽表
- Spark SQL解析查詢parquet格式Hive表獲取分割槽欄位和查詢條件SparkSQLHive
- leetcode題解(查詢表問題)LeetCode
- (3) MySQL分割槽表使用方法MySql
- MySQL資料表分割槽手記MySql
- oracle分割槽表和非分割槽表exchangeOracle
- [oracle] expdp 匯出分割槽表的分割槽Oracle
- mysql 分割槽MySql
- MySQL 合併查詢union 查詢出的行合併到一個表中MySql
- Laravel Query Builder 複雜查詢案例：子查詢實現分割槽查詢 partition byLaravelUI
- oracle 分割槽表move和包含分割槽表的lob moveOracle
- 移動分割槽表和分割槽索引的表空間索引
- Mysql資料分片技術(一)——初識表分割槽MySql
- MySQL的分割槽（二）MySql
- MySQL 分割槽表，為什麼分割槽鍵必須是主鍵的一部分？MySql
- mysql分割槽表佔用大量容量處理（最佳化）及歸檔分割槽表MySql
- 第41期：MySQL 雜湊分割槽表MySql
- 第40期：MySQL 分割槽表案例分享MySql