mysql order by 查询慢,mysql – ORDER BY中查询速度非常慢,LIMIT范围更大

MySQL 5.6,64位,RHEL 5.8

使用ORDER BY和LIMIT’row_count'(或LIMIT 0,’row_count’)查询大型表.如果’row_count’较大,那么结果集的实际计数将非常慢.

案例1：下面的查询非常快(没有’限制’)：

mysql> SELECT * FROM syslog WHERE

(ReportedTime BETWEEN '2013-11-04' AND '2013-11-05') AND

Priority<3 AND Facility=1 ORDER BY id DESC;

+---

| ...

6 rows in set (0.01 sec)

情况2：下面的查询也很快(‘LIMIT 5’)：

mysql> SELECT * FROM syslog WHERE

(ReportedTime BETWEEN '2013-11-04' AND '2013-11-05') AND

Priority<3 AND Facility=1 ORDER BY id DESC LIMIT 5;

+---

| ...

5 rows in set (0.42 sec)

情况3：下面的查询非常慢(‘LIMIT 7’,可以使用任何’row_count’值> 6)：

mysql> SELECT * FROM syslog WHERE

(ReportedTime BETWEEN '2013-11-04' AND '2013-11-05') AND

Priority<3 AND Facility=1 ORDER BY id DESC LIMIT 7;

+---

| ...

6 rows in set (28 min 7.24 sec)

差异只是个人(无限制),“限制5”和“限制7”.

案例3为何如此缓慢？

案例3中的一些调查：

>运行命令’SHOW PROCESS’,查询状态保存在’发送数据’中

>检查了服务器内存,它仍然可用.

>在运行查询之前,扩展的SESSION缓冲区’read_buffer_size’,’read_rnd_buffer_size’,’sort_buffer_size’到非常大的数量(到16MB),但没有帮助.

>还只查询列’id'(SELECT id FROM syslog ….),但结果相同.

>在查询运行期间,在另一个mysql连接中引发相同的查询但是row_count< 5(例如'LIMIT 5'),后者的返回仍然很快.

>例如,在不同条件下,将“2013-10-03”之间的时间范围扩展到“2013-11-05”以获得结果行计数149.使用LIMIT 140时,速度很快.使用LIMIT 150,它非常慢.这么奇怪.

目前在我们的网站上,程序首先获得实际结果行计数(SELECT COUNT(*)FROM …,No ORDER BY,No LIMIT),然后使用LIMIT’row_count’值进行查询,但不超过真正的行数刚刚到了.丑陋.

案例3的EXPLAIN：

-+-----..-+----..+-------+-----..+--------+---------+-----+-----+------------+

| sele.. | table| type | poss..| key | key_len | ref | rows| Extra |

-+-----..-+----..+-------+-----..+--------+---------+-----+-----+------------+

| SIMPLE | syslo| index | ... | PRIMARY| 8 | NULL| 132 | Using where|

-+-----..-+----..+-------+-----..+--------+---------+-----+-----+------------+

1 row in set (0.00 sec)

表定义：

CREATE TABLE syslog (

id BIGINT NOT NULL AUTO_INCREMENT,

ReceivedAt TIMESTAMP NOT NULL DEFAULT 0,

ReportedTime TIMESTAMP NOT NULL DEFAULT 0,

Priority SMALLINT,

Facility SMALLINT,

FromHost VARCHAR(60),

Message TEXT,

InfoUnitID INT NOT NULL DEFAULT 0,

SysLogTag VARCHAR(60) NOT NULL DEFAULT '',

PRIMARY KEY (id),

KEY idx_ReportedTime_Priority_id (ReportedTime,Priority,id),

KEY idx_Facility (Facility),

KEY idx_SysLogTag (SysLogTag(16)),

KEY idx_FromHost (FromHost(16))

);

解决方法:

Mysql因其围绕ORDER BY DESC LIMIT子句的行为而闻名.

请试试：

SELECT *

FROM syslog FORCE INDEX (Facility)

WHERE

ReportedTime BETWEEN '2013-11-04' AND '2013-11-05'

AND Priority<3

AND Facility=1

ORDER BY id DESC

LIMIT 7;

您需要强制使用第一个查询中使用的索引. (从他们的解释计划中得到它,KEY专栏)

标签：performance,sql,mysql,optimization

来源： https://codeday.me/bug/20190831/1775183.html