ADADADADAD

mysql 5.7.11查询分区表的一个问题[ mysql数据库 ]

mysql数据库时间：2024-12-03 12:13:30

作者：文/会员上传

如何提高mysql查询效率

简介：

mysql 查询一个分区表，当查询条件存在数据时执行效率OK,当不存在数据时执行不完，一直在sending data,当去掉desc就没问题。换个版本貌似也没问题。mysql> select version();+-

以下为本文的正文内容，内容仅供参考！本站为公益性网站，复制本文以及下载DOC文档全部免费。

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;

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

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

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

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

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

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;

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;

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

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

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

| 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.11查询分区表的一个问题.docx

将本文的Word文档下载到电脑

下载

热门标签: 5.7.11一个分区表

精品

申请https需要什么条件

mysql 5.7.11查询分区表的一个问题[ mysql数据库 ]

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

精品

热门推荐

大家都在看