mysql optimize innodb,为什么MySQL(InnoDB)表在OPTIMIZE TABLE之后变得更快,但是后来不起作用？...

我有一个Django Web应用程序,它将数据存储在MySQL InnoDB数据库中.在django admin上有一个特定页面可以访问很多,查询需要很长时间(~20秒).由于它是Django内部,因此无法更改查询.

有3个表,A,B和C.查询看起来像：

SELECT *

FROM A

INNER JOIN B ON (A.b_id = B.foo)

INNER JOIN C ON (B.foo = C.id)

ORDER BY A.id DESC

LIMIT 100

一个简单的join-3-tables一起.

id字段是主键并具有索引. A.b_id,B.foo都有自己的索引.

但是查询计划看起来不对,并说它没有使用B上的任何键(但是它使用其他连接的键).从阅读大量MySQL性能的东西来看,它理论上应该使用索引,因为它是各种常量连接,可以“通过”.它说它必须扫描所有~1,200行B.

奇怪的是,我在我的本地机器上优化了每一个并重新运行查询(使用SQL_NO_CACHE),它比原来的20秒快得多,0.02秒.对同一个查询的EXPLAIN给出了一个不同的,更明智的结果,表明它可以在每个上使用索引,并且它不必扫描整个批次.一个同事在测试机器上为每个人运行OPTIMIZE,它们具有大致相同的数据(最近从加载的转储文件重新创建),并且还显示了速度增加和合理解释.

所以我们在实时系统上运行它……它没有改变任何东西(速度也没有解释).我重新创建了我的MySQL数据库(DROPed数据库并从转储重新加载),现在OPTIMIZE不会改变任何东西(即~20秒运行时,错误的查询计划).

为什么会这样？如何让MySQL使用正确的索引并恢复我的0.02s查询时间？这篇博文(http://www.xaprb.com/blog/2010/02/07/how-often-should-you-use-optimize-table/)暗示OPTIMIZE只是真正优化了主键(而不是b_id,foo不是主索引).我怎样才能“重建二级指数”？我尝试过ALTER TABLE A ENGINE = InnoDB(和B& C相同)并且没有变化.

实际上,我越是看这个,MySQL查询计划似乎就越失败.它正在执行错误的查询计划,而不使用它可以使用的索引.在执行OPTIMIZE TABLE等之后,有时可以使用正确的索引.当这样的查询计划不好时,它可以随机选择,这就是为什么(我认为)不同的机器有不同的结果.

解决方法:

OPTIMIZE重建表.这(对于InnoDB)挤出了一些碎片和浪费的空间.这不太可能在任何查询中产生明显的差异.

此外,OPTIMIZE进行分析.这有可能改变统计数据,从而导致EXPLAIN计划不同(更好或更差).

由于ANALYZE(在InnoDB上)比OPTIMIZE快得多,所以只需进行ANALYZE即可.

各种非ANALYZE操作会导致ANALYZE完成.

标签：performance,mysql,innodb,index

来源： https://codeday.me/bug/20190806/1599688.html