数据清除后MySQL数据库性能下降
我有一个MySQL数据库,我决定通过删除过时数据来清除数据,以节省空间并通过删除不必要的数据来提高性能。令我惊讶的是,删除了许多记录并优化了表格后,性能下降了。我保留了旧数据库的副本,以便可以进行性能比较。我使用不同的过滤器运行了三次查询,我发现旧的巨大数据库在1.2秒内完成了查询(我的php事件探查器处于活动状态),而新的清除的数据库则需要5.7秒才能运行完全相同的查询。查询是:数据清除后MySQL数据库性能下降
select org_members.id
from member_calendar, days, org_members
where org_members.org_id=1
and days.date="2017-07-02"
and days.removed=0
and org_members.removed=0
and member_calendar.day_id=days.id
and member_calendar.org_member_id=org_members.id;
或
select org_members.id
from org_members
INNER JOIN member_calendar ON member_calendar.org_member_id=org_members.id
INNER JOIN days ON member_calendar.day_id=days.id
where org_members.org_id=1
and days.date="2017-07-02"
and days.removed=0
and org_members.removed=0
我用phpMyAdmin的个人资料在这两个分贝S中的查询和重大变化是,将数据发送步从617微秒就在大dB至2.2在清除的一秒钟!其他步骤也增加了,但这是巨大的差异。
使用关于查询的解释,大dB显示与较小的一个不同的序列。这两个结果,结果表明:
越大表Orders表member_calendar造就35943行,而不使用键,然后使用主键,产生1行org_members然后天使用主键,产生1行。
清除表查询是通过首先获取org_members而不使用主键并生成288行,然后使用member_calendar而不使用键生成32695行,然后使用主键并产生1行的日期来运行的。
因此,似乎dB已选择了一种不同的方式来运行效率低得多的查询。执行查询时,数据库在org_members表中使用主键,该查询使用较小的数据库对主键进行筛选,但将其与较大的数据库一起使用。关于为什么会这样做以及如何纠正的任何想法?这是一个普通类型的问题,应该在构造查询时解决?我对这些结果感到非常惊讶。
我不知道下一步该怎么做才能改善新数据库的状况。任何建议将非常感激。
我建议这是“超标准化”的情况。一般来说,“连续”的东西,比如日期,不应该移到另一个表中。在需要它的表格中,通常可以具有更好的性能,例如DATE
或DATETIME
。
此外,如果您使用的是ENGINE=MyISAM
,则降级的性能可能会提示您转移到InnoDB。
以下是几条建议。
- 解释这两个查询并检查是否使用了正确的索引。
- 重建索引。
- 如果在这张表中,您正在同时读取/插入数据,然后将表引擎类型更改为innodb而不是myism,因为myism会执行表锁,这会降低选择和读取的速度。
'explain [query]'的结果是什么?清除之前和之后的解释是否有所不同?我假设所有的指标都是一样的。另外,清除了多少数据?从所有三张桌子? –
'SHOW CREATE TABLE',please。请重写select以使用'JOIN ... ON'语法。 –
我用phpMyAdmin来分析这两个dB的查询,主要的变化是发送数据的步骤从大分贝的617微秒变成了被清除的分贝的2.2秒!其他步骤也增加了,但这是巨大的差异。 – Frank