MySQL InnoDB比MyISAM慢得多

我有两个表(缩小到重要字段):

syskeywordobjects:
pksyskeywordobjects BIGINT
fksyskeywords BIGINT
fkcontents BIGINT

fkcontents INDEX (fkcontents)
fksyskeywords INDEX (fksyskeywords)
fkcontents_fksyskeywords INDEX (fkcontents, fksyskeywords)


syskeywords:
pksyskeywords BIGINT
keyword VARCHAR

keyword INDEX (keyword)

在这两个表上,我运行以下查询:

SELECT k.pksyskeywords, k.keyword, COUNT( k.pksyskeywords ) AS counter
FROM syskeywordobjects ko INNER JOIN syskeywords k ON ko.fksyskeywords = k.pksyskeywords
WHERE (
    k.pksyskeywords <> 1218713201167374664
    AND EXISTS (
        SELECT innerko.pksyskeywordobjects
        FROM syskeywordobjects innerko
        WHERE ko.fkcontents = innerko.fkcontents
        AND innerko.fksyskeywords = 1218713201167374664
    )
)
GROUP BY k.pksyskeywords, k.keyword
ORDER BY counter DESC 
LIMIT 20

如果表使用MyISAM,则查询大约需要1-2秒,但如果我使用InnoDB(我必须这样做)则需要25-30秒 . 为什么InnoDB慢了大约20倍?

有关此处的进一步帮助,请说明结果

InnoDB:
id    select_type         table    type    possible_keys                                        key                        key_len   ref                   rows    Extra
1     PRIMARY             k        range   PRIMARY                                              PRIMARY                    8         NULL                  52051   Using where; Using temporary; Using filesort
1     PRIMARY             ko       ref     fksyskeywords                                        fksyskeywords              8         k.pksyskeywords       1       Using where
2     DEPENDENT SUBQUERY  innerko  ref     fkcontents,fksyskeywords,fkcontents_fksyskeywords    fkcontents_fksyskeywords   16        ko.fkcontents,const   1       Using index

MyISAM:
id    select_type           table    type    possible_keys                                       key                         key_len    ref                   rows      Extra
1     PRIMARY               ko       index   fksyskeywords                                       fkcontents_fksyskeywords    16         NULL                  277823    Using where; Using index; Using temporary; Using f...
1     PRIMARY               k        eq_ref  PRIMARY                                             PRIMARY                     8          ko.fksyskeywords      1    
2     DEPENDENT SUBQUERY    innerko  ref     fkcontents,fksyskeywords,fkcontents_fksyskeywords   fkcontents_fksyskeywords    16         ko.fkcontents,const   1

回答(1)

2 years ago

为了让InnoDB更快地运行,您应该始终将 innodb_buffer_pool 设置为较大的值 . 它允许InnoDB将工作数据集存储在内存中(表数据,索引数据结构),从而更快地工作(RAM的IO> HDD的IO) . 如果我没有弄错的话,默认值是8MB,我个人拥有80%的可用内存 .