mysql - mysql 5.6 中的性能问题

Question

我在向 mysql 中的表中插入、选择和更新行时遇到了严重的性能问题。

我使用的表结构是

CREATE TABLE `sessions` (
     `sessionid` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
     `expiry` datetime NOT NULL,
     `value` text NOT NULL,
     `data` text,
     PRIMARY KEY (`sessionid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Sessions';

我面临问题的查询是:

INSERT INTO sessions (SESSIONID, EXPIRY, DATA, VALUE) VALUES ('b8c10810c505ba170dd9403072b310ed', '2019-05-01 17:25:50', 'PFJlc3BvbnNlIHhtbG5zPSJ1cm46b2FzaXM6bmFtZXM', '7bKDofc/pyFSQhm7QE5jb6951Ahg6Sk8OCVZI7AcbUPb4jZpHdrCAKuCPupJO14DNY3jULxKppLadGlpsKBifiJavZ/');

UPDATE sessions SET EXPIRY = '2019-05-01 17:26:07' WHERE (SESSIONID = 'e99a0889437448091a06a43a44d0f170');

SELECT SESSIONID, EXPIRY, DATA, VALUE FROM sessions WHERE (SESSIONID = '507a752c48fc9cc3043a3dbe889c52eb');

我尝试解释查询，但无法推断出很多关于优化表/查询的信息。

从慢查询报告所用时间

for select in average is 23.45, for update it is 15.93 and for insert it is 22.31.

非常感谢任何有助于确定问题的帮助。

Answer 1

每秒查询多少次？

table 有多大？

多少内存？

innodb_buffer_pool_size 的值是多少？

UUID 的性能很糟糕。 (那是 SHA1 吗？)这是因为它们是如此随机以至于“下一个”查询(您提到的任何查询)可能不在缓存中，因此需要磁盘命中。

因此，对于比 buffer_pool 大得多的表，您将无法通过旋转驱动器每秒维持超过 100 个查询。 SSD 会更快。

更多关于 UUID 的弊端(SHA1 具有相同的不幸属性，但没有像 uuid 那样的解决方案):http://mysql.rjweb.org/doc.php/uuid

您可以做的一件小事是缩小表格:

session_id BINARY(20)

插入/更新/删除时使用UNHEX()，选择时使用HEX()。

更多

51KB avg row len --> TEXT 列很大，并且“未记录”，因此需要多个 block 来处理一行。

0.8GB buffer_pool，但是 20GB 的数据，和“随机”PRIMARY KEY --> 缓存几乎没用。

这意味着每个 查询将有多个磁盘 命中，但可能少于 10 个。

300 毫秒(快速时间)--> HDD 上大约有 30 个磁盘命中(SSD 上更多；你有哪个？)。

因此，我必须猜测，当出现大量事件时查询会发生 20 秒，查询会相互绊倒，从而导致大量 I/O 争用。

要做什么？大多数数据看起来像十六进制。如果这是真的，您可以通过打包和使用 BINARY(..) 或 将磁盘占用量减半(并减少一些所需的磁盘命中) BLOB.

INSERT INTO sessions (SESSIONID, EXPIRY, DATA, VALUE)
    VALUES (UNHEX('b8c10810c505ba170dd9403072b310ed'),
            '2019-05-01 17:25:50',
            UNHEX('PFJlc3BvbnNlIHhtbG5zPSJ1cm46b2FzaXM6bmFtZXM'),
            UNHEX('7bKDofc/pyFSQhm7QE5jb6951Ahg6Sk8OCVZI7AcbUPb4jZpHdrCAKuCPupJO14DNY3jULxKppLadGlpsKBifiJavZ/'));

UPDATE sessions SET EXPIRY = '2019-05-01 17:26:07'
 WHERE SESSIONID = UNHEX('e99a0889437448091a06a43a44d0f170');

SELECT SESSIONID, EXPIRY, DATA, VALUE FROM sessions
 WHERE SESSIONID = UNHEX('507a752c48fc9cc3043a3dbe889c52eb');

和

 `sessionid` VARBINARY(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
 `expiry` datetime NOT NULL,
 `value` BLOB NOT NULL,
 `data` BLOB,

ROW_FORMAT=DYNAMIC 可能是最优的(但这并不重要)。