gpt4 book ai didi

mysql - 按日期顺序排序时,“使用临时”会减慢查询速度

转载 作者:行者123 更新时间:2023-12-04 20:51:27 25 4
gpt4 key购买 nike

我有一个用于日志条目的表,以及一个约100种可能的日志代码的描述表:

CREATE TABLE `log_entries` (
`logentry_id` int(11) NOT NULL AUTO_INCREMENT,
`date` datetime NOT NULL,
`partner_id` smallint(4) NOT NULL,
`log_code` smallint(4) NOT NULL,
PRIMARY KEY (`logentry_id`),
KEY `IX_code` (`log_code`),
KEY `IX_partner_code` (`partner_id`,`log_code`)
) ENGINE=MyISAM ;

CREATE TABLE IF NOT EXISTS `log_codes` (
`log_code` smallint(4) NOT NULL DEFAULT '0',
`log_desc` varchar(255) DEFAULT NULL,
`category_overview` tinyint(1) NOT NULL DEFAULT '0',
`category_error` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`log_code`),
KEY `IX_overview_code` (`category_overview`,`log_code`),
KEY `IX_error_code` (`category_error`,`log_code`)
) ENGINE=MyISAM ;


以下查询(匹配2万行中的10k)在0.0034秒内执行(使用 LIMIT 0,20):

SELECT log_entries.date, log_codes.log_desc FROM log_entries 
INNER JOIN log_codes ON log_codes.log_code = log_entries.log_code
WHERE log_entries.partner_id = 1 AND log_codes.category_overview = 1;


但是,当添加 ORDER BY log_entries.logentry_id DESC时(这当然是必需的),它会减慢到0.6秒。可能是因为在log_codes表上使用了“使用临时”?删除索引实际上使查询执行得更快,但仍然很慢(0.3秒)。

不带ORDER BY的查询的EXPLAIN输出:

+ ---- + -------------- + ------------- + ------ + --------- ------------------- + ------------------ + --------- +- ------------------------- + ------ + ------------- +
| id | select_type |桌子|类型可能的钥匙|关键key_len |参考|行|额外|
+ ---- + -------------- + ------------- + ------ + --------- ------------------- + ------------------ + --------- +- ------------------------- + ------ + ------------- +
| 1 |简单log_codes |参考| PRIMARY,IX_overview_code | IX_overview_code | 1 | const | 56 | |
| 1 |简单log_entries |参考| IX_code,IX_partner_code | IX_partner_code | 7 | const,log_codes.log_code | 25 |在哪里使用
+ ---- + -------------- + ------------- + ------ + --------- ------------------- + ------------------ + --------- +- ------------------------- + ------ + ------------- +


并包括ORDER BY:

+ ---- + -------------- + ------------- + ------ + --------- ------------------- + ------------------ + --------- +- ------------------------- + ------ + ----------------- ---------------- +
| id | select_type |桌子|类型可能的钥匙|关键key_len |参考|行|额外|
+ ---- + -------------- + ------------- + ------ + --------- ------------------- + ------------------ + --------- +- ------------------------- + ------ + ----------------- ---------------- +
| 1 |简单log_codes |参考| PRIMARY,IX_overview_code | IX_overview_code | 1 | const | 56 |使用临时的;使用文件排序|
| 1 |简单log_entries |参考| IX_code,IX_partner_code | IX_partner_code | 7 | const,log_codes.log_code | 25 |在哪里使用
+ ---- + -------------- + ------------- + ------ + --------- ------------------- + ------------------ + --------- +- ------------------------- + ------ + ----------------- ---------------- +


关于如何使此查询更快执行的任何提示?我看不到为什么需要“使用临时”,因为在获取和排序适当的日志条目之前应该选择日志代码?

更新@Eugen Rieck:

SELECT log_entries.date,lc.log_desc FROM log_entries INNER JOIN(SELECT log_desc,log_code FROM log_codes WHERE category_overview = 1)AS lc ON lc.log_code = log_entries.log_code WHERE log_entries.partner_id = 1 ORDER BY log_entries.logentry_id;
+ ---- + -------------- + ------------- + ------ + --------- ---------------- + ------------------ + --------- + ---- --------------- + ------ + --------------------------- ------ +
| id | select_type |桌子|类型可能的钥匙|关键key_len |参考|行|额外|
+ ---- + -------------- + ------------- + ------ + --------- ---------------- + ------------------ + --------- + ---- --------------- + ------ + --------------------------- ------ +
| 1 |主要| |全部| NULL | NULL | NULL | NULL | 57 |使用临时的;使用文件排序|
| 1 |主要| log_entries |参考| IX_code,IX_partner_code | IX_partner_code | 7 | const,lc.log_code | 25 |在哪里使用
| 2 |派生| log_codes |参考| IX_overview_code | IX_overview_code | 1 | | 56 | |
+ ---- + -------------- + ------------- + ------ + --------- ---------------- + ------------------ + --------- + ---- --------------- + ------ + --------------------------- ------ +


更新@RolandoMySQLDBA:

使用我的原始索引,ORDER BY日期DESC:

SELECT log_entries.date,log_codes.log_desc FROM(SELECT log_code,date FROM log_entries WHERE partner_id = 1)log_entries INNER JOIN(SELECT log_code,log_desc FROM log_codes WHERE category_overview = 1)log_codes使用(log_code)ORDER BY log_entries.date DESC;
+ ---- + -------------- + ------------- + ------ + --------- --------- + ------------------ + --------- + ------ + ---- --- + --------------------------------- +
| id | select_type |桌子|类型可能的钥匙|关键key_len |参考|行|额外|
+ ---- + -------------- + ------------- + ------ + --------- --------- + ------------------ + --------- + ------ + ---- --- + --------------------------------- +
| 1 |主要| |全部| NULL | NULL | NULL | NULL | 57 |使用临时的;使用文件排序|
| 1 |主要| |全部| NULL | NULL | NULL | NULL | 21937 |在哪里使用使用连接缓冲区
| 3 |派生| log_codes |参考| IX_overview_code | IX_overview_code | 1 | | 56 | |
| 2 |派生| log_entries |全部| IX_partner_code | NULL | NULL | NULL | 22787 |在哪里使用
+ ---- + -------------- + ------------- + ------ + --------- --------- + ------------------ + --------- + ------ + ---- --- + --------------------------------- +


使用索引,无需排序:

SELECT log_entries.date,log_codes.log_desc FROM(SELECT log_code,date FROM log_entries WHERE partner_id = 1)log_entries INNER JOIN(SELECT log_code,log_desc FROM log_codes WHERE category_overview = 1)log_codes使用(log_code);
+ ---- + -------------- + ------------- + ------- + -------- --------------- + ----------------------- + --------- + ------ + ------- + -------------------------------- +
| id | select_type |桌子|类型可能的钥匙|关键key_len |参考|行|额外|
+ ---- + -------------- + ------------- + ------- + -------- --------------- + ----------------------- + --------- + ------ + ------- + -------------------------------- +
| 1 |主要| |全部| NULL | NULL | NULL | NULL | 57 | |
| 1 |主要| |全部| NULL | NULL | NULL | NULL | 21937 |在哪里使用使用连接缓冲区
| 3 |派生| log_codes |索引| IX_overview_code_desc | IX_overview_code_desc | 771 | NULL | 80 |在哪里使用使用索引
| 2 |派生| log_entries |索引| IX_partner_code_date | IX_partner_code_date | 15 | NULL | 22787 |在哪里使用使用索引
+ ---- + -------------- + ------------- + ------- + -------- --------------- + ----------------------- + --------- + ------ + ------- + -------------------------------- +


使用索引,ORDER BY日期DESC:

SELECT log_entries.date,log_codes.log_desc FROM(SELECT log_code,date FROM log_entries WHERE partner_id = 1)log_entries INNER JOIN(SELECT log_code,log_desc FROM log_codes WHERE category_overview = 1)log_codes使用(log_code)ORDER BY log_entries.date DESC;
+ ---- + -------------- + ------------- + ------- + -------- --------------- + ----------------------- + --------- + ------ + ------- + --------------------------------- +
| id | select_type |桌子|类型可能的钥匙|关键key_len |参考|行|额外|
+ ---- + -------------- + ------------- + ------- + -------- --------------- + ----------------------- + --------- + ------ + ------- + --------------------------------- +
| 1 |主要| |全部| NULL | NULL | NULL | NULL | 57 |使用临时的;使用文件排序|
| 1 |主要| |全部| NULL | NULL | NULL | NULL | 21937 |在哪里使用使用连接缓冲区
| 3 |派生| log_codes |索引| IX_overview_code_desc | IX_overview_code_desc | 771 | NULL | 80 |在哪里使用使用索引
| 2 |派生| log_entries |索引| IX_partner_code_date | IX_partner_code_date | 15 | NULL | 22787 |在哪里使用使用索引
+ ---- + -------------- + ------------- + ------- + -------- --------------- + ----------------------- + --------- + ------ + ------- + --------------------------------- +


更新@Joe Stefanelli:

SELECT log_entries.date,log_codes.log_desc FROM log_entries INNER JOIN log_codes ON log_codes.log_code = log_entries.log_code WHERE log_entries.partner_id = 1 AND log_codes.category_overview = 1 ORDER BY date DESC;
+ ---- + -------------- + ------------- + ------ + --------- ----------------- + ----------------- + --------- + ---- ---------------------- + ------ + -------------------- -------------------------- +
| id | select_type |桌子|类型可能的钥匙|关键key_len |参考|行|额外|
+ ---- + -------------- + ------------- + ------ + --------- ----------------- + ----------------- + --------- + ---- ---------------------- + ------ + -------------------- -------------------------- +
| 1 |简单log_codes |全部| PRIMARY,IX_code_overview | NULL | NULL | NULL | 80 |在哪里使用使用临时的;使用文件排序|
| 1 |简单log_entries |参考| IX_code,IX_code_partner | IX_code_partner | 7 | log_codes.log_code,const | 25 |在哪里使用
+ ---- + -------------- + ------------- + ------ + --------- ----------------- + ----------------- + --------- + ---- ---------------------- + ------ + -------------------- -------------------------- +

最佳答案

我认为,这里和类似问题中的大多数问题都来自对MySQL(和其他数据库)如何使用索引进行排序的误解。答案是:MySQL不使用索引进行排序,它只能按索引顺序或相反的方向读取数据。如果您碰巧希望按照当前使用的索引的顺序对数据进行排序-您很幸运,否则结果将被排序(因此EXPLAIN中的文件排序)

那就是整个结果的顺序主要取决于哪个表是联接中的第一个表。而且,如果您查看自己的EXPLAIN,您将看到联接从'log_codes'表开始(因为它要小得多)。

基本上,您需要的是'log_entries'上的复合索引(partner_id,日期),'log_codes'的覆盖复合索引(log_code,category_overview,log_desc),将'INNER JOIN'更改为'STRAIGHT_JOIN'以强制加入顺序,并按“日期” DESC排序(幸运的是,该索引也将涵盖在内)。

UPD1:很抱歉,我为第一个表输入了错误的索引:应该为(partner_id, log_code, date)


但是我仍然很难理解为什么当我尝试对另一个表中的列进行排序时,MySQL为什么选择在log_codes表中使用“临时”(以及100x查询时间)?


只要您同意数据的获取顺序,MySQL便可以直接输出数据,也可以将数据放入临时表中,然后进行排序和输出。当从联接中的任何非第一个表中按字段排序时,MySQL必须对数据进行排序(而不仅仅是按索引的顺序输出),并且对数据进行排序需要一个临时表。


但是,随着我深入数据集,速度会变慢(LIMIT 50000,25为6秒)。你知道为什么吗?


为了输出行50000,25,MySQL无论如何都需要获取第一个50000并跳过它们。因为我错过了索引中的一列,所以MySQL不仅嘲笑了索引,而且还为每个项目在磁盘上另外查找了log_code值。使用覆盖索引应该快得多,因为可以从索引中获取所有数据。

UPD2:尝试强制索引:

SELECT log_entries.date, log_codes.log_desc
FROM log_entries FORCE INDEX (IX_partner_code_date)
STRAIGHT_JOIN log_codes
ON log_codes.log_code = log_entries.log_code
WHERE log_entries.partner_id = 1
AND log_codes.category_overview = 1
ORDER BY log_entries.date DESC;

关于mysql - 按日期顺序排序时,“使用临时”会减慢查询速度,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/10218194/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com