主页 / dba / 问题 / 4358
Accepted
RPK
Asked: 2011-08-06 00:32:59 +0800 CST

改进缓慢的查询。附上慢查询日志

  • 772

下面是我的 .NET 应用程序运行时的慢速查询日志。请让我知道如何提高查询的性能：

TCP Port: 3306, Named Pipe: (null)
Time                 Id Command    Argument
# Time: 110805 13:25:39
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.015625  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1373
use stockist;
SET timestamp=1312530939;
SELECT SUM(GROSSAMOUNT) FROM BILLDETAILS WHERE MONTH(BILLDATE) = 8 AND YEAR(BILLDATE) = 2011;
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.000000  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1373
SET timestamp=1312530939;
SELECT COUNT(BILLNO) FROM BILLDETAILS WHERE MONTH(BILLDATE) = 8 AND YEAR(BILLDATE) = 2011;
# Time: 110805 13:30:32
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.078125  Lock_time: 0.046875 Rows_sent: 1  Rows_examined: 1374
SET timestamp=1312531232;
SELECT A.BILLNO AS BILL_NO, DATE_FORMAT(A.BILLDATE,'%d/%m/%Y') AS BILL_DATE, B.PARTYNAME AS PARTY_NAME, A.NETAMOUNT AS NET_AMOUNT FROM BILLDETAILS A, PARTYMASTER B WHERE A.PARTYID = B.PARTYID AND MONTH(A.BILLDATE) = 8 AND YEAR(A.BILLDATE) = 2011 ORDER BY A.BILLNO, A.BILLDATE;
# Time: 110805 13:30:44
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.296875  Lock_time: 0.031250 Rows_sent: 407  Rows_examined: 19552
SET timestamp=1312531244;
select a.itemcode as Item_Code, a.itemname as Item_Name, a.stockinhand as Stock_In_Hand, b.mrp As MRP, round((a.stockinhand * b.mrp),2) As Value, date_format(b.invoicedate,'%d/%m/%Y') As Stock_Date from itemmaster a, stockentry b where a.itemid = b.itemid and invoicedate = (select max(invoicedate) from stockentry where itemid = b.itemid) group by a.itemname order by a.itemname;
# Time: 110805 13:30:55
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.000000  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1312531255;
SELECT * FROM REMARKSETTINGS;
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.000000  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1312531255;
SELECT * FROM BILLINGSETTINGS;
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.015625  Lock_time: 0.015625 Rows_sent: 1  Rows_examined: 1373
SET timestamp=1312531255;
SELECT MAX(BILLNO) FROM BILLDETAILS WHERE YEAR(BILLDATE) = 2011;
# Time: 110805 13:30:58
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.031250  Lock_time: 0.000000 Rows_sent: 184  Rows_examined: 3266
SET timestamp=1312531258;
SELECT B.RECORDID, A.ITEMCODE, A.ITEMNAME, A.STOCKINHAND, B.SALEPRICE FROM ITEMMASTER A, STOCKENTRY B WHERE A.ITEMID = B.ITEMID AND RECORDID = (SELECT MAX(RECORDID) FROM STOCKENTRY WHERE ITEMID = A.ITEMID) AND A.STOCKINHAND > 0 AND B.SALEPRICE > 0 AND B.INVOICEDATE IS NOT NULL ORDER BY A.ITEMNAME, B.INVOICEDATE;
# Time: 110805 13:31:21
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.031250  Lock_time: 0.000000 Rows_sent: 184  Rows_examined: 2898
SET timestamp=1312531281;
SELECT B.RECORDID, A.ITEMCODE, A.ITEMNAME, A.STOCKINHAND, B.SALEPRICE FROM ITEMMASTER A, STOCKENTRY B WHERE A.ITEMID = B.ITEMID AND RECORDID = (SELECT MAX(RECORDID) FROM STOCKENTRY WHERE ITEMID = A.ITEMID) AND A.STOCKINHAND > 0 AND B.SALEPRICE > 0 AND B.INVOICEDATE IS NOT NULL AND A.ITEMNAME LIKE '%';
# Time: 110805 13:36:31
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.250000  Lock_time: 0.000000 Rows_sent: 407  Rows_examined: 20367
SET timestamp=1312531591;
select c.categoryname, a.itemcode as Item_Code, a.itemname as Item_Name, a.stockinhand as Stock_In_Hand, b.mrp As MRP, round((a.stockinhand * b.mrp),2) As Value, date_format(b.invoicedate,'%d/%m/%Y') As Stock_Date, D.TradeName, D.OwnerAddress, D.OwnerTinNo from itemmaster a, stockentry b, categorymaster c, OwnerDetails D where a.itemid = b.itemid and a.categoryid = c.categoryid and invoicedate = (select max(invoicedate) from stockentry where itemid = b.itemid) group by a.categoryid, a.itemname order by a.itemname;
mysql performance

3 个回答

  1. Best Answer

    一个突出的方面是您访问 BillDetails 表的方式。所有查询似乎都在 BillDate 上使用 MONTH 和 YEAR 运算符。例如：

    SELECT SUM(GROSSAMOUNT) FROM BILLDETAILS WHERE MONTH(BILLDATE) = 8 AND YEAR(BILLDATE) = 2011;

    此方法需要访问 BillDetails 中的每条记录。更好的方法是按如下方式索引 BillDate 和查询：

    SELECT SUM(GROSSAMOUNT) FROM BILLDETAILS WHERE BILLDATE BETWEEN '2011-08-01' AND '2011-08-31';

    看起来你目前没有太多数据（1373 行）所以上面的差异目前是最小的，当数量增长时更显着。

    • 5

  2. 首先，其中大部分似乎都很“慢”，因为它们没有使用索引。您需要分析您如何访问表中的数据以确定适当的索引。但是这个文件中的一些提示：

    SELECT * FROM REMARKSETTINGS;

    和

    SELECT * FROM BILLINGSETTINGS;

    这些目前只有 1 行，但如果它们增长，则您要求减速。你可以在这里做的几件事是添加一个 WHERE 子句（我不能在不了解 DDL 的情况下提出建议）或者至少只选择你需要的列而不是“*”。

    为了

    SELECT B.RECORDID, A.ITEMCODE, A.ITEMNAME, A.STOCKINHAND, B.SALEPRICE 
FROM ITEMMASTER A, STOCKENTRY B 
WHERE A.ITEMID = B.ITEMID 
  AND RECORDID = (SELECT MAX(RECORDID) FROM STOCKENTRY 
                  WHERE ITEMID = A.ITEMID) 
  AND A.STOCKINHAND > 0 
  AND B.SALEPRICE > 0 
  AND B.INVOICEDATE IS NOT NULL 
ORDER BY A.ITEMNAME, B.INVOICEDATE;

    乍一看，确保您在 recordID 上有一个索引。

    我可以提出建议并阅读索引吗？索引是一个很大的帮助，但如果你过度索引，它和索引不足一样糟糕。

    • 3

  3. @Mark Storey-Smith 解决方案的一个变体是将特定日期信息存储在日历表中。此表可能包含以下字段： * 所有受影响的日历日期的天字段，即。1990-01-01 至 2020-12-31。该字段将被加入

    • 年号字段
    • 月份数字字段
    • 一个月中的某一天
    • 星期几
    • 一个月中的一周（有几种定义）
    • 公共假期

    根据查询，您需要索引所有这些列（并且可能有一些多列）

    • 1

相关问题