MySQL查询需要很长时间才能完成

I'm using MySQL Database and following query:

我正在使用MySQL数据库和以下查询:

SELECT
    customer_id,
    customer_code, 
    customer_name,  
    customer_address,
    customer_home_town
FROM  
    customers
    INNER JOIN (
        SELECT 
            issue_entry_customer_id,
            SUM(issue_entry_copies
              +issue_entry_subscribe
              +issue_entry_freecopies) AS col_copies 
        FROM 
            issue_entry 
        WHERE 
            issue_entry_deleted_status = 0  AND
            DATE_FORMAT(issue_entry_date,'%Y-%m') 
                = '".dateDatabaseFormat($issue_date)."'     
        GROUP BY 
            issue_entry_customer_id
    ) col_table 
        ON issue_entry_customer_id = customer_id
            AND customer_code = 1
            AND col_table.col_copies != 0
    ORDER BY
        customer_id ASC

issue_entry table has over 500 000 rows.

issue_entry表有超过500 000行。

How can I improve the performance?

如何提高性能?

3 个解决方案

#1

table_name alias INNER JOIN table_name2 alias ON ...

that's how INNER JOIN query is. Why are you doing a select after the INNER JOIN statement?

这就是INNER JOIN查询的方式。为什么在INNER JOIN语句后执行select?

#2

This is your query:

这是您的查询:

SELECT c.customer_id, c.customer_code, c.customer_name, 
       c.customer_address, c.customer_home_town
FROM customers c INNER JOIN
      (SELECT issue_entry_customer_id, 
              SUM(issue_entry_copies + issue_entry_subcripe + issue_entry_freecopies
              ) AS col_copies 
       FROM issue_entry ie
       WHERE issue_entry_deleted_status = 0  AND
             DATE_FORMAT(issue_entry_date, '%Y-%m')  = '".dateDatabaseFormat($issue_date)."'                 
      GROUP BY issue_entry_customer_id
     ) col_table
     ON col_table.issue_entry_customer_id = c.customer_id AND
        c.customer_code = 1 AND
        col_table.col_copies <> 0  
ORDER BY c.customer_id ASC;

My recommendation is to change the whole format of the query:

我的建议是更改查询的整个格式:

SELECT c.customer_id, c.customer_code, c.customer_name, 
       c.customer_address, c.customer_home_town
FROM customers c
WHERE c.customer_code = 1 AND
      EXISTS (SELECT 1
              FROM issue_entry ie
              WHERE ie.issue_entry_customer_id = customer_id AND  
                    DATE_FORMAT(issue_entry_date, '%Y-%m')  = '".dateDatabaseFormat($issue_date)."' AND
                    ie.issue_entry_deleted_status = 0 AND
                    (issue_entry_copies > 0 OR
                     issue_entry_subcripe > 0 OR
                     issue_entry_freecopies
                    )
             )
ORDER BY customer_id ASC;

Note: this assumes that the three columns are never negative.

注意:这假设三列永远不会为负数。

For this query, you want the following indexes: customers(customer_code, customer_id) and issue_entry(issue_entry_customer_id, issue_entry_deleted_status).

对于此查询,您需要以下索引:customers(customer_code,customer_id)和issue_entry(issue_entry_customer_id,issue_entry_deleted_status)。

#3

Your query using some alias for better readablility.

您的查询使用一些别名以获得更好的可读性。

select
c.customer_id, 
c.customer_code, 
c.customer_name,  
c.customer_address,
c.customer_home_town
from customers c
inner join (
 SELECT 
 issue_entry_customer_id, 
 SUM(issue_entry_copies+issue_entry_subcripe+issue_entry_freecpoies) AS col_copies 
 FROM issue_entry 
 WHERE 
 issue_entry_deleted_status = 0  AND
 DATE_FORMAT(issue_entry_date,'%Y-%m') = '".dateDatabaseFormat($issue_date)."'                 
 GROUP BY 
 issue_entry_customer_id
)x
on x.issue_entry_customer_id = c.customer_id
and c.customer_code   = 1
and x.col_copies != 0  
order BY customer_id ASC

Now the for large data you certainly needed to add some indexes to boostup the query.

现在,对于大数据,您当然需要添加一些索引来提升查询。

alter table issue_entry add index i_del_date(issue_entry_deleted_status,issue_entry_date);
alter table issue_entry add index i_cid_idx(issue_entry_customer_id);
alter table customers add index c_code_idx(customer_code);

if customer_id in customer table is not primary key you may need to index that as well

如果customer表中的customer_id不是主键,您可能还需要对其进行索引

alter table customers add index c_customer_id_idx(customer_id);

Also note that even with the issue_entry_date being indexed it may fail to use index since you are formatting the date.

另请注意,即使将issue_entry_date编入索引,也可能无法使用索引,因为您要格式化日期。

Make sure to take a backup of the tables before applying the indexes.

确保在应用索引之前备份表。

#1

table_name alias INNER JOIN table_name2 alias ON ...