mysql - 如何优化mysql上的顺序插入

Question

我正在尝试使用 MySQL 实现 HTTP 事件流服务器，用户可以将事件附加到流(MySQL 表)，并定义事件的预期序列号。

逻辑有点简单:

1. 开放交易
2. 获取表中要插入的下一个序列号
3. 验证下一个序列号是否与预期匹配(如果提供)
4. 插入数据库

这是我的代码:

    public async append(
        data: any = {},
        expectedSeq?: number
    ): Promise<void> {
        let published_at = $date.create();

        try {
            await $mysql.transaction(async trx => {
                let max = await trx(this.table)
                    .max({
                        seq: "seq",
                    })
                    .first();

                if (!max) {
                    throw $error.InternalError(`unexpected mysql response`);
                }

                let next = (max.seq || 0) + 1;

                if (expectedSeq && expectedSeq !== next) {
                    throw $error.ExpectationFailed(
                        `expected seq does not match current seq`
                    );
                }

                await trx(this.table).insert({
                    published_at,
                    seq: next,
                    data: $json.stringify(data),
                });
            });
        } catch (err) {
            if (err.code === "ER_DUP_ENTRY") {
                return this.append(data, expectedSeq);
            }

            throw err;
        }
    }

我的问题是这非常慢，因为附加到同一流的并行请求之间存在竞争条件。我的笔记本电脑在一个流上的每秒插入次数从 ~1k 变为 ~75。

关于如何优化此逻辑的任何指示/建议？

结论

经过评论考虑后，我决定使用自动增量，并仅在出现错误时重置 auto_increment。它的每秒写入次数与 ExpectedSeq 大致相同，但如果不需要排序，则速率要高得多。

解决方案如下:

    public async append(data: any = {}, expectedSeq?: number): Promise<Event> {
        if (!$validator.validate(data, this.schema)) {
            throw $error.ValidationFailed("validation failed for event data");
        }

        let published_at = $date.create();

        try {
            let seq = await $mysql.transaction(async _trx => {
                let result = (await _trx(this.table).insert({
                    published_at,
                    data: $json.stringify(data),
                })).shift();

                if (!result) {
                    throw $error.InternalError(`unexpected mysql response`);
                }

                if (expectedSeq && expectedSeq !== result) {
                    throw $error.ExpectationFailed(
                        `expected seq ${expectedSeq} but got ${result}`
                    );
                }

                return result;
            });

            return eventFactory(this.topic, seq, published_at, data);
        } catch (err) {
            await $mysql.raw(`ALTER TABLE ${this.table} auto_increment = ${this.seqStart}`);

            throw err;
        }
    }

Answer 1

为什么网页需要提供序列号？这只是困惑代码的一个秘诀，甚至可能比你所勾画的还要困惑。只需将 auto_increment 值返回给用户即可。

1. 插入...;
2. SELECT LAST_INSERT_ID(); -- session 特定，因此不需要事务。
3. 将该值返回给用户。