慢查询日志分析工具-mysqldumpslow

mysql官方提供的慢查询日志分析工具.主要功能是,

统计不同慢sql的出现次数(Count),执行最长时间(Time),累计总耗费时间(Time),等待锁的时间(Lock),发送给客户端的行总数(Rows),扫描的行总数(Rows),用户以及sql语句本身(抽象了一下格式,比如limit 1, 20用limit N,N表示).

常用的参数：

-s排序选项：c:访问计数l:锁定时间r:返回记录t:查询时间al:平均锁定时间ar:平均返回记录数at:平均查询时间 -t只显示top n条查询

[root@test-db01 ~]# mysqldumpslow -s l -t 3/application/mysql/data/mysql-slow.log

Reading mysql slow query log from/application/mysql/data/mysql-slow.log

Count: 4466Time=0.01s (26s)Lock=0.00s(1s)Rows=0.0 (2),lwl-ht[lwl-ht]@[192.168.10.33]

select transactionId,GroupId, transactionNo, contractId, contractNo, contractName, orderNo,

contractType,transferType, carryContractMethod, consignerID, consignerName, consignerUserId,

bussinessMode,biddingMode, businessStatus, platformId, supplierId, supplierName,

platformName,supplierUserId, promise, dealAmount, biddingQty, planOffLineDate,actOffLineDate,

contractLineId,contractLineNo, Etd, Eta, sourceLocationCode, sourceProvinceCode,

sourceCityCode,sourceCountyCode, sourceProvinceName, sourceCityName, sourceCountyName,

sourceLatitude,sourceAddress, sourceConnector, sourceConnectorMobile, targetLocationCode,

targetProvinceCode, targetCityCode, targetCountyCode,targetProvinceName, targetCityName,

targetCountyName,targetLatitude, targetAddress, targetConnector, targetConnectorMobile,

totalDistance,pickupQty, unloadQty, goodsCategory, goodsType, goodsName, goodsNorms,

goodsPack,goodsNumber, goodsTotalWeight, goodsMaxSingleVolumn, goodsMaxSingleWeight,

goodsTotalVolume,volumnUnit, weightUnit, lengthUnit, goodsValue, goodsRemark, vichleLimitCode,

boxCategory,boxType, boxLength, equipmmentQty, actDealDate, timelimitTypeValue,

timelimitType,remark, offerServices, addUser, addDate, modifyUser, modifyDate, unitPrice,

quotationUnit

fromcontract_contractbizheader

WHERE (bussinessMode = N

andbusinessStatus = N

andplanOffLineDate <= 'S' )

or(bussinessMode = N

andbusinessStatus = N

andplanOffLineDate <= 'S' )

or(bussinessMode = N

andbusinessStatus = N

andplanOffLineDate <= 'S' )

Count: 2233Time=0.01s (17s)Lock=0.00s(0s)Rows=0.0 (21),lwl-order[lwl-order]@[192.168.10.33]

select

orderId, orderNo,GroupId, supplierGroupId, consignerGroupId, orderType, orderStatus,

contractType,contractNos, transferMethod, refInfoNo, orderSource, consignerCompanyCode,

consignerCompanyName, platformOpenId, platformName, supplierCode,supplierName, supplierUserId,

requireFeedbakTime, actualFeedbackTime, etd, eta, atd, ata,sourceLocationCode, sourceProvinceName,

sourceProvinceCode, sourceCityName, sourceCityCode, sourceCountyName,sourceCountyCode,

sourceAddress,sender, senderMobile, senderTel, sourceLocationQty, sourceLatitude,

targetLocationCode, targetProvinceName, targetProvinceCode, targetCityName,targetCityCode,

targetCountyName,targetCountyCode, targetAddress, receiver, receiverMobile, receiverTel,

targetLocationQty, targetLatitude, vichleLimitCode, boxCategory,boxType, boxLength,

equipmmentQty,goodsCategory, goodsType, goodsName, goodsNorms, goodsPack, goodsNumber,

goodsTotalWeight,goodsMaxSingleVolumn, goodsMaxSingleWeight, goodsTotalVolume, volumnUnit,

weightUnit,lengthUnit, goodsValue, goodsRemark, serviceConstraints, podReturnMethod,

billPeriodCode,payMethodCode, donePayCost, billTermsCode, totalCost, prePayCost,

prePayMethodCode,prePaySatus, isNeedInvoice, invoiceType, taxRate, planConfirmDate,

confirmDate,remark, addUser, addDate, modifyUser, modifyTime

fromorder_orderinfo

WHERE (orderStatus = N )

or(orderStatus = N )

or(orderStatus = N )

Count: 10636Time=0.00s (41s)Lock=0.00s(0s)Rows=0.2 (2209),lwl-com[lwl-com]@[192.168.10.33]

select

DictionaryId,ParentId, Code, Name, Description, Url, Sort, AddOpenId, AddDate, UpdateOpenId,

UpdateDate,DictionaryType

from dictionary

WHERE (Code = 'S' )

[root@test-db01 ~]#