需要帮助提高查询性能吗

tag5nh1u 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(469)

最初的问题是基于在哪里最好将tx隔离设置为read uncommitted，但经过一些建议之后，似乎我最初的想法是不正确的。
ddl公司

CREATE TABLE `tblgpslog` (
    `GPSLogID` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `DTSaved` DATETIME NULL DEFAULT NULL,
    `PrimaryAssetID` BIGINT(20) NULL DEFAULT NULL,
    `SecondaryAssetID` BIGINT(20) NULL DEFAULT NULL,
    `ThirdAssetID` BIGINT(20) NULL DEFAULT NULL,
    `JourneyType` CHAR(1) NOT NULL DEFAULT 'B',
    `DateStamp` DATETIME NULL DEFAULT NULL,
    `Status` VARCHAR(50) NULL DEFAULT NULL,
    `Location` VARCHAR(255) NULL DEFAULT '',
    `Latitude` DECIMAL(11,8) NULL DEFAULT NULL,
    `Longitude` DECIMAL(11,8) NULL DEFAULT NULL,
    `GPSFix` CHAR(2) NULL DEFAULT NULL,
    `Speed` BIGINT(20) NULL DEFAULT NULL,
    `Heading` INT(11) NULL DEFAULT NULL,
    `LifeOdometer` BIGINT(20) NULL DEFAULT NULL,
    `Extra` VARCHAR(20) NULL DEFAULT NULL,
    `BatteryLevel` VARCHAR(5) NULL DEFAULT '--',
    `Ignition` TINYINT(4) NOT NULL DEFAULT '1',
    `Radius` INT(11) NOT NULL DEFAULT '0',
    `GSMLatitude` DECIMAL(11,8) NOT NULL DEFAULT '0.00000000',
    `GSMLongitude` DECIMAL(11,8) NOT NULL DEFAULT '0.00000000',
    PRIMARY KEY (`GPSLogID`),
    UNIQUE INDEX `GPSLogID` (`GPSLogID`),
    INDEX `SecondaryUnitID` (`SecondaryAssetID`),
    INDEX `ThirdUnitID` (`ThirdAssetID`),
    INDEX `DateStamp` (`DateStamp`),
    INDEX `PrimaryUnitIDDateStamp` (`PrimaryAssetID`, `DateStamp`, `Status`),
    INDEX `Location` (`Location`),
    INDEX `DTSaved` (`DTSaved`),
    INDEX `PrimaryAssetID` (`PrimaryAssetID`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=153076364
;

原始查询如下

SELECT L.GPSLogID, L.DateStamp, L.Status, Location, Latitude, Longitude, GPSFix, Speed, Heading, LifeOdometer, BatteryLevel, Ignition, L.Extra 
FROM tblGPSLog L 
WHERE PrimaryAssetID = 183 AND L.GPSLogID > 147694199
ORDER BY DateStamp ASC 
LIMIT 100;

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
"1","SIMPLE","L","index_merge","PRIMARY,GPSLogID,PrimaryUnitIDDateStamp,PrimaryAssetID","PrimaryAssetID,PRIMARY","9,8",\N,"96","Using intersect(PrimaryAssetID,PRIMARY); Using where; Using filesort"

几个月前，这个问题出现了，经过一番调查后，我将查询改为下面的，但现在的情况非常相似。

EXPLAIN SELECT GPSLogID, DateStamp, tmpA.Status, Location, Latitude, Longitude, GPSFix, Speed, Heading, LifeOdometer, BatteryLevel, Ignition, tmpA.Extra, 
PrimaryAssetID FROM (SELECT L.GPSLogID, L.DateStamp, L.Status, Location, Latitude, Longitude, GPSFix, Speed, Heading, LifeOdometer, 
BatteryLevel, Ignition, L.Extra, PrimaryAssetID 
FROM tblGPSLog L 
WHERE L.GPSLogID > 147694199) AS tmpA 
WHERE PrimaryAssetID = 183 
ORDER BY DateStamp ASC;

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
"1","PRIMARY","<derived2>","ALL",\N,\N,\N,\N,"5380842","Using where; Using filesort"
"2","DERIVED","L","range","PRIMARY,GPSLogID","PRIMARY","8",\N,"8579290","Using where"

谢谢你的建议。
吉姆

mysql performance transaction-isolation

来源：https://stackoverflow.com/questions/51370111/mysql-need-help-in-improving-query-performance

2条答案

按热度按时间

jvidinwx1#

你的陈述
很少有一个select会在insert发生时命中表，即使命中了，也不会引起任何大的问题。delete语句仅在非高峰时间安排一周一次，
等同于“改变隔离模式不会有多大帮助”
我建议设置 long_query_time=1 打开慢日志。稍后，请用 pt-query-digest 找到几个“最差”的查询。然后我们来讨论如何改进它们。
更多

INDEX `PrimaryUnitIDDateStamp` (`PrimaryAssetID`, `DateStamp`,
INDEX `PrimaryAssetID`         (`PrimaryAssetID`)

第一个照顾第二个，所以第二个是不必要的。

PRIMARY KEY             (`GPSLogID`),
UNIQUE INDEX `GPSLogID` (`GPSLogID`),

pk是一个唯一的键，所以查克第二个。这个额外的唯一索引会减慢插入速度并浪费磁盘空间。
在这种情况下，我认为没有理由使用查询和子查询：

SELECT  GPSLogID, DateStamp, tmpA.Status, Location, Latitude,
        Longitude, GPSFix, Speed, Heading, LifeOdometer, BatteryLevel,
        Ignition, tmpA.Extra, PrimaryAssetID
    FROM  
        ( SELECT  L.GPSLogID, L.DateStamp, L.Status, Location, Latitude,
                  Longitude, GPSFix, Speed, Heading, LifeOdometer, BatteryLevel,
                  Ignition, L.Extra, PrimaryAssetID
            FROM  tblGPSLog L
            WHERE  L.GPSLogID > 147694199
        ) AS tmpA
    WHERE  PrimaryAssetID = 183
    ORDER BY  DateStamp ASC;

一双 DECIMAL(11,8) 总计12个字节，对于lat&lng来说是多余的。看看这个小的替代品。
table越来越大了，对吧？而且，在它变得如此之大之后，表演就一落千丈了？缩小数据类型以缩小表是一种方法，尽管这是一种临时修复。 Using intersect(PrimaryAssetID,PRIMARY) --几乎总是，构建一个复合索引比使用“索引合并-交集”要好。
虽然

INDEX `PrimaryAssetID`         (`PrimaryAssetID`)

应该相当于

INDEX `PrimaryAssetID`         (`PrimaryAssetID`, GPSLogID)

有什么东西在阻止它。建议你加上这个2列的综合索引。也许有很大一部分行 PrimaryAssetID = 183 ?? 如果方便的话，请做 SELECT COUNT(*) FROM tblgpslog WHERE PrimaryAssetID = 183 是否要从此日志中清除“旧”数据？如果是的话，最佳的方法是 PARTITIONing ; 看看这个。

赞(0）回复(0）举报 2021-06-20

xwbd5t1u2#

我相信将tx isolation设置为read uncommitted将阻止select锁定表。
为什么你会相信readuncommitted可以做到这一点？
默认情况下，select在除serializable之外的所有隔离级别中都已处于非锁定状态。
也就是说，除非使用 FOR UPDATE 或者 FOR SHARE / LOCK IN SHARE MODE . 使用可序列化隔离级别时，select隐式转换为锁定select FOR SHARE . 看到了吗https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html
我强烈建议不要使用readuncommitted。这不是一个好主意，因为您的事务可以读取其他事务未提交的工作，这意味着您可以读取不一致的数据（部分完成的事务）和虚拟数据（最终回滚的事务的更改）。这样做没有好处，而且查询可能返回错误的结果。
什么使您认为锁定是性能问题的原因？您是否注意到慢速查询日志中的锁定时间增加了？
性能问题更常见的原因是查询优化不好或系统资源不足。
如果您的数据库在8年多之后变得更慢，我猜数据库已经增长，直到活动数据集不再适合ram。
请回复您的意见：
有没有一个工具或方法来进一步调查这个问题？我知道问题的起因，只是无法确定原因
调查有很多工具和方法。有一些关于这个主题的书籍，比如高性能mysql，还有一些致力于创建性能监控工具的公司，比如percona和Cortex。
如果不知道更具体的细节，我就猜不出有什么建议。如果您需要更多帮助，请编辑上面的原始问题并添加：
遇到问题的sql查询。
输出 EXPLAIN <query> 对于有问题的查询。
输出 SHOW CREATE TABLE <tablename> 对于查询引用的每个表。您可以在mysql客户机中运行此语句。
这是开始。

赞(0）回复(0）举报 2021-06-20

我来回答

需要帮助提高查询性能吗

2条答案

相关问题

热门标签

最新问答