Mysql优化建议大表 [英] Mysql Optimization suggestion for large table

问题描述

我想优化此查询，

select  location_id, dept_id,
        round(sum(sales),0), sum(qty),
        count(distinct tran_id),
        now()
    from  tran_sales
    where  tran_date <= '2016-12-24'
    group by  location_id, dept_id;

当前，此查询平均运行98秒(查询耗时97.4096秒). 在Windows 10、64位操作系统，16 GB RAM中.

currently this query runs for around average of 98 seconds (Query took 97.4096 seconds.) in windows 10, 64 Bit OS, 16 GB RAM.

这是表格详细信息，供您参考.

this is the table detail for your reference.

    CREATE TABLE tran_sales (
    tran_date date NOT NULL,
    location_id int(11) NOT NULL,
    dept_id int(11) NOT NULL,
    item_id varchar(25) NOT NULL,
    tran_id int(11) NOT NULL,
    sales float DEFAULT NULL,
    qty int(11) DEFAULT NULL,
    update_datetime datetime NOT NULL,
    PRIMARY KEY (tran_date,location_id,dept_id,item_id,tran_id),
    KEY tran_date (tran_date)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表tran_sales中的记录数: 1350万.

the record count in table tran_sales: 13.5 Million.

注意:即使我尝试不使用该索引并使用KEY tran_date (tran_date)，也是如此.使用和不使用KEY tran_date (tran_date)

Note: Even i tried without and with this index KEY tran_date (tran_date) . and average time it takes is 98 seconds with and without KEY tran_date (tran_date)

请建议如何通过更改查询或更改my.ini的某些默认设置来加快结果的速度(如果有帮助的话).谢谢.

please suggest how to speedup the results by either changing the query or by changing some default settings of my.ini if that helps. Thanks.

更新 表格中的最短日期为:2016-07-01，表格中的最短日期为:2017-07-25

Update the min date in table is : 2016-07-01, and the max date in table is : 2017-07-25

推荐答案

到目前为止，所有建议都无济于事，因为...

None of the suggestions so far will help much, because...

• 覆盖索引:它仅比表格小一点，因此速度稍快些.
• KEY(tran_date)-浪费;最好使用以tran_date开头的PK.
• PARTITIONing-不.那可能会慢一些.
• 卸下tran_date(或以其他方式重新布置PK)-这会很痛.过滤(WHERE)在tran_date上；通常最好是先 .
• 那么，为什么COUNT(*)这么快?好吧，首先查看EXPLAIN.它将显示它使用KEY(tran_date) 来扫描表.要扫描的数据更少，因此速度更快.

• Covering index: That is only slightly smaller than the table, so it is slightly faster.
• KEY(tran_date) -- a waste; it is better to use the PK, which starts with tran_date.
• PARTITIONing -- No. That is likely to be slower.
• Removing tran_date (or otherwise rearranging the PK) -- This will hurt. The filtering (WHERE) is on tran_date; it is usually best to have that first.
• So, why was COUNT(*) fast? Well, start by looking at the EXPLAIN. It will show that it used KEY(tran_date) instead of scanning the table. Less data to scan, hence faster.

真正的问题是您要扫描数百万行，触摸数百万行需要时间.

The real issue is that you have millions of rows to scan, it takes time to touch millions of rows.

如何加快速度?创建并维护 摘要表 .然后查询该表(具有数千行)，而不是原始表(数百万行).总计数为SUM(counts)；总和为SUM(sums);平均值是SUM(sums)/SUM(counts)，等等.

How to speed it up? Create and maintain a Summary table . Then query that table (with thousands of rows) instead of the original table (millions of rows). Total count is SUM(counts); total sum is SUM(sums); average is SUM(sums)/SUM(counts), etc.

这篇关于Mysql优化建议大表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！