MySQL -在提高查询性能方面需要帮助
最初的问题是在哪里最好将tx隔离设置为读取未提交，但在一些建议之后，似乎我最初认为这是一种可能的解决方案是不正确的。
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"谢谢你的建议。
吉姆
回答 2
Stack Overflow用户
发布于 2018-07-17 11:16:06
您的语句
在INSERT发生时，SELECT命中表的情况很少见，即使这样做，也不会造成任何大问题。DELETE语句每周只在非高峰时间调度一次，
等同于“改变隔离模式不会有多大帮助”。
我建议设置long_query_time=1并打开slowlog。稍后，使用pt-query-digest查看慢日志，找出几个“最差”的查询。然后让我们讨论如何改进它们。
More
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来说是过度杀伤力。有关更小的替代方案，请参见。
表的大小一直在增长，对吗？而且，在它变得如此庞大之后，性能却一落千丈？收缩数据类型以缩小表是一种方法，尽管这是一个临时的修复方法。
Using intersect(PrimaryAssetID,PRIMARY) --几乎所有情况下，构建复合索引都比使用“索引合并交集”更好。
尽管
INDEX `PrimaryAssetID` (`PrimaryAssetID`)应该等同于
INDEX `PrimaryAssetID` (`PrimaryAssetID`, GPSLogID)有什么东西在阻止它。建议您添加此2列综合索引。也许大部分行都有PrimaryAssetID = 183?？如果方便的话，请使用SELECT COUNT(*) FROM tblgpslog WHERE PrimaryAssetID = 183
您要清除此日志中的“旧”数据吗？如果是这样的话，最好的方法就是使用PARTITIONing；请参阅。
Stack Overflow用户
发布于 2018-07-17 05:45:42
我相信将tx isolation设置为READ UNCOMMITTED会阻止SELECT锁定表。
为什么你会相信未提交的READ能做到这一点呢？
默认情况下，在除SERIALIZABLE之外的所有隔离级别中，SELECT都已处于非锁定状态。
也就是说，除非您使用FOR UPDATE或FOR SHARE / LOCK IN SHARE MODE，否则SELECT始终是非锁定的。使用SERIALIZABLE隔离级别时，SELECT将隐式转换为锁定SELECT FOR SHARE。请参阅https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html
我强烈建议千万不要使用未提交的。这不是一个好主意，因为您的事务可以读取其他事务未提交的工作，这意味着您可以读取不一致的数据(部分完成的事务)和幻影数据(来自最终回滚的事务的更改)。这样做没有好处，查询可能会返回错误的结果。
是什么让您认为锁定是导致性能问题的原因？您是否注意到慢查询日志中的锁定时间增加了？
性能问题更常见的原因是查询优化不佳或系统资源不足。
如果您的数据库在8+年后变得更慢，我会猜测数据库已经增长，直到活动数据集不再适合内存。
请回复您的评论：
有没有工具或方法可以进一步研究这一点？我知道导致问题的查询，只是不能确定原因
调查的工具和方法有很多。有像High Performance MySQL这样的关于这个主题的书籍，也有整个公司致力于创建性能监控工具，比如Percona和VividCortex。
在不知道更多具体细节的情况下，我无法猜测出一个建议。如果您需要更多帮助，请编辑上面的原始问题并添加：
- 有问题的SQL查询。对于有问题的查询，
-
EXPLAIN <query>的输出。对于查询引用的每个表， -
SHOW CREATE TABLE <tablename>的输出。您可以在MySQL客户端中运行此语句。
这只是个开始。
https://stackoverflow.com/questions/51370111
复制相似问题