SQL--加快查询速度
我目前使用以下查询,由于数据量(大约14个月),大约需要8分钟才能返回结果。请问有什么方法可以加快速度?SQL--加快查询速度
数据库中的问题是与MySQL的InnoDB引擎
select
CUSTOMER as CUST,
SUM(IF(PAGE_TYPE = 'C',PAGE_TYPE_COUNT,0)) AS TOTAL_C,
SUM(IF(PAGE_TYPE = 'D',PAGE_TYPE_COUNT,0)) AS TOTAL_D
from
PAGE_HITS
where
EVE_DATE >= '2016-01-01' and EVE_DATE <= '2016-01-05'
and SITE = 'P'
and SITE_SERV like 'serv1X%'
group by
CUST
数据6个月划分。进入where子句的每一列都被编入索引。有相当一些索引&将是一个大列表在这里列出。因此,只需以文字总结。对于这个疑问,EVE_DATE + PAGE_TYPE_COUNT
是综合指数&等都是CUST + SITE_SERV + EVE_DATE
,EVE_DATE + SITE_SERV
,EVE_DATE + SITE
之一,
主键实际上是一个虚拟的自动递增数。这不是老实说。我无法获得解释计划。我会看看我能为此做些什么。
我很感激任何帮助,以改善这一个请。
好吧,作为表范围分区是EVE_DATE,数据库管理系统应该很容易看到读哪个分区。所以这都是关于使用什么索引。
有一列检查是否相等(SITE = 'P'
)。这应该首先在您的索引中。然后,您可以按照我猜想的任何顺序添加EVE_DATE
和SITE_SERV
。因此,您的索引应该能够尽快找到有问题的表记录。
但是,如果您添加在您的查询中使用你的索引等领域,表将甚至没有被读取,因为所有的数据将是指数本身可供选择:
create index on page_hits(site, eve_date, site_serv, customer, page_type, page_type_count);
如果我没有弄错,这应该是您查询的最佳索引。
谢谢你堆Thorsten。通过一些措施来提高性能。 – usert4jju7
主要优化因素将是索引。例如:
EVE_DATE, SITE, CUST, SITE_SERV
该命令是重要的,至少对于SITE_SERV是最后一个值;因为您使用LIKE
就不会使用完整值,这会降低下一列的索引效率。
您也可以通过删除IF
并返回类型和计数来获得一点点;也许你可以在前台应用程序中处理/格式化这个值?
无论如何,您应该首先使用EXPLAIN
来分析当前查询,以查看出了什么问题。如果你不能,你可以尝试在本地数据库上复制结构,索引和一些虚拟数据,而卷在这里是不相关的。
谢谢普鲁克。我很高兴删除'IF',我怎么能有效地计算条件'SUM'?你能帮忙吗? – usert4jju7
我会说只是选择'PAGE_TYPE,SUM(PAGE_TYPE_COUNT)AS TOTAL'并管理你的前端应用程序中的'C'或'D'情况;但正如我所说,它可能不值得。纠正了一些错别字,我的句子没有任何意义 – Preuk
谢谢Preuk。我会在我的开发的其他地方使用这个建议。关于这个问题,我需要在数据库层中处理数据:( – usert4jju7
我没有数据,所以我不能测试这个速度,但我认为它会更快。
select
CUSTOMER as CUST,
SUM(PAGE_TYPE_COUNT * (PAGE_TYPE = 'C')) AS TOTAL_C,
SUM(PAGE_TYPE_COUNT * (PAGE_TYPE = 'D')) AS TOTAL_D
from
PAGE_HITS
where
EVE_DATE >= '2016-01-01' and EVE_DATE <= '2016-01-05'
and SITE = 'P'
and SITE_SERV like 'serv1X%'
group by
CUST
它的工作就好了我的小提琴上的MySQL 5.6
不错的诀窍,我一定会尝试这个来简化我的一些查询;性能方面,你碰巧有任何指标? – Preuk
Thankyou Xpy。这看起来很棒。我一定会在别处使用它。在我的情况下,没有性能改进。这是一个真正的好,虽然 – usert4jju7
添加这两个指标:
INDEX(site, date)
INDEX(site, site_serv)
优化器将着眼于统计和他们之间挑选。粗略地说,如果在该范围内有'P'& DATE的行数少于'P'&'serv1X%',则第一个更好。
是的,Thorsten可能更好的“覆盖”索引,但它比我想要放在索引中的字段更多。
PARTITIONing
可能帮助。但是有太多的信息可以肯定地说。分区可能会有所帮助的原因是您有一个“二维”查找 - 日期范围和“serv1X%”。您需要在日期或site_serv上进行分区,然后将PRIMARY KEY(site, ..., ...)
与(date或site_serv)中的另一个作为第二列。其余的列需要包含分区键和一些列以使其唯一。这太乱了,我不想考虑它。
谢谢瑞克。这确实有助于提高性能。 – usert4jju7
您可以指定使用哪些索引(如果有)以及结构是什么样子?主键被使用等? – CR41G14
你能提供更多的细节:号码行,索引,存储引擎等 –
非常感谢。抱歉,我错过了更新这些细节。现在让我来做这个。 – usert4jju7