gpt4 book ai didi

MySQL count(*) , Group BY 和 INNER JOIN

转载 作者:行者123 更新时间:2023-11-29 01:45:57 25 4
gpt4 key购买 nike

我在 MySQL 5.1 上查询时遇到了非常糟糕的情况。我简化了我在其上进行 JOIN 的 2 个表:

CREATE TABLE  `jobs` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`title` VARCHAR( 255 ) NOT NULL
) ENGINE = MYISAM ;

CREATE TABLE `jobsCategories` (
`jobID` int(11) NOT NULL,
`industryID` int(11) NOT NULL,
KEY `jobID` (`jobID`),
KEY `industryID` (`industryID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

查询很简单:

SELECT count(*) as nb,industryID 
FROM jobs J
INNER JOIN jobsCategories C ON C.jobID=J.id
GROUP BY industryID
ORDER BY nb DESC;

我在 jobs 表中有大约 150000 条记录,在 jobsCategories 表中有 350000 条记录,我有 30 个行业;

执行查询大约需要 50 秒!!!

你知道为什么要花这么长时间吗?我怎样才能优化这个数据库的结构?分析查询显示 99% 的执行时间花在了 tmp 表上的复制上。

EXPLAIN <query> gives me : 


*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: J
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 178950
Extra: Using index; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: C
type: ref
possible_keys: jobID
key: jobID
key_len: 8
ref: J.id
rows: 1
Extra: Using where
2 rows in set (0.00 sec)

关于内存:

free -m  : 

total used free shared buffers cached
Mem: 2011 1516 494 0 8 1075
-/+ buffers/cache: 433 1578
Swap: 5898 126 5772

使用下面建议的 FORCE INDEX

select count(*) as nb, industryID 
from
jobs J
inner join jobsCategories C force index (industryID) on (C.jobID = J.id )
group by industryID
order by nb DESC;

SHOW PROFILE;

给我:

+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000095 |
| Opening tables | 0.000014 |
| System lock | 0.000008 |
| Table lock | 0.000007 |
| init | 0.000032 |
| optimizing | 0.000011 |
| statistics | 0.000032 |
| preparing | 0.000016 |
| Creating tmp table | 0.000031 |
| executing | 0.000003 |
| Copying to tmp table | 3.301305 |
| Sorting result | 0.000028 |
| Sending data | 0.000024 |
| end | 0.000003 |
| removing tmp table | 0.000009 |
| end | 0.000004 |
| query end | 0.000003 |
| freeing items | 0.000029 |
| logging slow query | 0.000003 |
| cleaning up | 0.000003 |
+----------------------+----------+

我想我的 RAM (2Gb) 不够大。我如何确定是这种情况?

最佳答案

首先,我认为您不需要加入表 jobs 来获得相同的结果(除非您在表 jobsCategories 中有一些垃圾数据):

select count(*) as nb, industryID 
from jobsCategories
group by industryID
order by nb DESC;

否则你可以尝试在 industryID 上强制建立索引:

select count(*) as nb, industryID 
from
jobs J
inner join jobsCategories C force index (industryID) on (C.jobID = J.id )
group by industryID
order by nb DESC;

关于MySQL count(*) , Group BY 和 INNER JOIN,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/6678541/

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