Collin Dauphinee's questions

我有一个在 Amazon 的 RDS 上运行的 MySQL 实例。这意味着我无法更改任何二进制日志设置。

我有一个source像这样创建的表，其中包含大约 20 亿行：

CREATE TABLE `source` (
   id INT PRIMARY KEY AUTO_INCREMENT,
   value1 VARCHAR(256),
   value2 VARCHAR(256)
);

我有另一个表destination，具有相同的列，但它id是BIGINT：

CREATE TABLE `destination` (
   id BIGINT PRIMARY KEY AUTO_INCREMENT,
   value1 VARCHAR(256),
   value2 VARCHAR(256)
);

该source表在我要压缩的 ID 中有间隙。我想将所有行复制source到destination，不带id列，如下所示：

INSERT INTO `destination` (value1, value2)
SELECT value1, value2 FROM `source`; 

我怎样才能做到这一点，而不锁定source表？由于表的大小，副本本身将花费非常长的时间，而且我不能将它锁定那么久。

我基本上想READ UNCOMMITTED隔离地运行上面的语句，但由于我无法更改任何二进制日志设置，这是不可能的。

我有一个 InnoDB 表，其主键列定义如下：

id INT NOT NULL AUTO_INCREMENT

此列的 auto_increment 值已达到 INT_MAX，因此所有插入都因重复键错误而失败。但是，此表有从 0 到 180 万的未使用 ID 值，因此我想将 auto_increment 值重置为 0，以便为实施长期修复争取时间。

我找不到任何方法来完成此操作而不会导致大量停机时间。如果我的应用程序服务器无法从该表中读取（但不能写入，就像现在一样，没关系），它们的核心功能就会被破坏。

我考虑过以下两种解决方案，但没有成功：

使用 设置 auto_increment 值ALTER TABLE。这是不可能的，因为它会重建表，这需要很长时间。

创建一个具有相同模式的新表，并使用RENAME TABLE从 0 开始的列交换新表，然后用旧表中的数据回填它。这也是不可能的，因为回填过程会破坏 auto_increment 值（而且我相信它会在回填时阻止来自应用程序的插入）。

我可以在这里采取任何其他方法，还是我完全不走运？

我在以下过程中遇到间歇性死锁：

DELIMITER $$
CREATE PROCEDURE pr_set_user_password (IN user_id INT, IN algorithm INT, IN hash VARBINARY(256))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;
        UPDATE `UserPassword` SET `Active` = 0 WHERE `UserId` = user_id;
        INSERT INTO `UserPassword` (`UserId`, `Active`, `Algorithm`, `Hash`) VALUES (user_id, 1, algorithm, hash);
COMMIT;
END $$
DELIMITER ;

UserId该表在和Active( IX_UserPassword_UserId_Active)上有一个复合索引。

有人试图运行一个并行创建许多用户的负载测试，并且他们非常一致地遇到了死锁。查看日志，我发现当此 proc 使用相邻user_id值运行时会发生死锁。

我真的在寻找一个真正的解释为什么这会导致死锁。我有各种混合和相互冲突的答案，没有一个是有意义的，从因为一个事务有锁而另一个锁无法获取它（即使只有一个锁有问题）到这就是 MySQL 的工作方式，处理它。

请参阅下面的死锁示例：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2016-08-26 00:21:52 2b30caecc700
*** (1) TRANSACTION:
TRANSACTION 71925393848, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 4901, OS thread handle 0x2b30cc1d7700, query id 37117819 <server> update
INSERT INTO `UserPassword` (`UserId`, `Active`, `Algorithm`, `Hash`) VALUES ( NAME_CONST('user_id',72332427), 1,  NAME_CONST('algorithm',2),  NAME_CONST('hash',_binary'<data>' COLLATE 'binary'))
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 388 page no 117300 n bits 784 index `IX_UserPassword_UserId_Active` of table `users`.`UserPassword` trx id 71925393848 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 714 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 844fb48c; asc  O  ;;
 1: len 1; hex 81; asc  ;;
 2: len 4; hex 818106f9; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 71925393846, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1184, 4 row lock(s), undo log entries 1
MySQL thread id 4909, OS thread handle 0x2b30caecc700, query id 37117815 <server> update
INSERT INTO `UserPassword` (`UserId`, `Active`, `Algorithm`, `Hash`) VALUES ( NAME_CONST('user_id',72332426), 1,  NAME_CONST('algorithm',2),  NAME_CONST('hash',_binary'<data>' COLLATE 'binary'))
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 388 page no 117300 n bits 784 index `IX_UserPassword_UserId_Active` of table `users`.`UserPassword` trx id 71925393846 lock_mode X locks gap before rec
Record lock, heap no 714 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 844fb48c; asc  O  ;;
 1: len 1; hex 81; asc  ;;
 2: len 4; hex 818106f9; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 388 page no 117300 n bits 784 index `IX_UserPassword_UserId_Active` of table `users`.`UserPassword` trx id 71925393846 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 714 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 844fb48c; asc  O  ;;
 1: len 1; hex 81; asc  ;;
 2: len 4; hex 818106f9; asc     ;;

*** WE ROLL BACK TRANSACTION (1)

我在一个非常古老且非常大的应用程序中有一个场景，其中我有一个代表一种资源的表：

CREATE TABLE resource (resource_id INT, name NVARCHAR(4000))

该表选自数百个不同的地方，包括应用程序代码中的存储过程和动态 SQL。

一个团队最近更新了此资源的名称以进行本地化，他们的方法非常简单。有一个包含本地化名称的新表，以及resource表上的“默认”语言 ID，用于当名称未针对所请求的语言进行本地化时：

-- Foreign keys omitted
ALTER TABLE resource ADD default_language_id INT
CREATE TABLE resource_local (resource_id INT, language_id INT, name NVARCHAR(4000))

大多数 proc 都有一个@user_language_id参数，所以选择要返回的名称的逻辑很简单：如果存在则resource_local.name匹配，如果language_id = @user_language_id存在则匹配，否则就resource_local.name匹配。language_id = resource.default_language_idresource.name

不幸的是，这会将选择正确名称的逻辑变成如下所示：

SELECT ISNULL(ISNULL(exact.name, default.name), res.name)
FROM resource res
LEFT JOIN resource_local exact ON exact.resource_id = res.resource_id 
    AND exact.language_id = @user_language_id
LEFT JOIN resource_local default ON default.resource_id = res.resource_id
    AND default.language_id = res.default_language_id
WHERE res.resource_id = @resource_id

尝试选择的所有数百个位置resource.name都必须使用此逻辑进行更新，这已将此项目变成整个组织的一项巨大工作，因为每个团队都需要更新其 SQL 以使用此逻辑。这也会导致可维护性问题，因为任何处理此表的新开发人员都需要知道他们不能只使用该name列。

现在为时已晚，但我很好奇：是否有更好的方法来解决这个问题，以便name从中选择列resource只会根据@user_language_id变量（如果存在）“做正确的事”？

我正在尝试诊断间歇性花费很长时间的查询。我怀疑它可能在尝试获取锁时被阻止。我无权在遇到问题的环境中使用探查器。

有没有办法让我在不使用外部分析器的情况下获得有关此单个查询被阻止多长时间的统计信息？

我有一个非常基本的表：

CREATE TABLE [obj_local] (
    [obj_id]     INT             NOT NULL,
    [value]      NVARCHAR (1000) NOT NULL
);

这个表存储了很多数据，我需要在value列中搜索一个字符序列：

SELECT [obj_id] FROM [obj_local] WHERE [value] LIKE '%{substring}%'

这是非常缓慢的。我的理解是，由于两边都有通配符，索引对我没有帮助，全文索引也对我没有帮助，因为我不是在搜索标记（单词）。

我能做些什么来优化这个搜索吗？

我正在调查由两个并发 UPDATE 语句引起的死锁：

UPDATE [table] 
SET [column] = 0 
WHERE [unindexed_column] = @id

我的理解是，因为所WHERE预测的列是未索引的，所以执行了全表扫描。对于每一行，获取一个更新锁。如果它匹配该WHERE子句，则它会升级为独占锁并持有它直到语句完成。

当会话 A 对第 2 行具有排他锁并试图获取对第 1 行的更新锁时，就会发生死锁，而会话 B 对第 1 行具有排他锁并试图获取对第 2 行的更新锁。

死锁的原因是有道理的，但我不明白表扫描是如何执行的，从而使这种情况成为可能。如果这两个查询以相同的顺序执行扫描，那么最坏的情况似乎是其中一个查询在获取更新锁时被阻塞，直到另一个查询完成并释放它的锁。

表扫描是如何执行的？表格行的扫描顺序是否不一致？如果update语句获取更新锁失败，是否跳转到下一行，稍后再尝试上一行？究竟是什么让这种僵局成为可能？