主页 / user-126797

Blackbam's questions

Martin Hope
Blackbam
Asked: 2022-08-25 06:41:41 +0800 CST
  • 1

假设我有一个包含约 3000 万个条目和 40 列的 MySQL 表，我有一个高度活跃的查询（5 个查询/秒），它非常慢（平均约 20 秒）并且扫描的行数很高（平均 50.000行）。随着表的增长，性能越来越差。我想通过添加正确的复合甚至覆盖索引来解决问题。

教义查询由动态查询构建器构建，涉及以下属性（任何查询中仅使用 userId，所有其他列有时仅用于过滤）：

  • 总是：user_idint 有=[> 1 m 用户，但单个用户可能有 > 200K 条目]
  • 有时：带有[7 种可能性]status的 varchar(20)IN()
  • 有时：expiration_timestamp带有<[可以是任何时间戳]的日期时间
  • 有时：typevarchar(20)( 有IN()[7 种可能性]
  • 罕见：namevarchar(255) 带有LIKE[带有尾随通配符，很少重复]
  • 非常罕见：带有[前导通配符和尾随通配符] 的tagsvarchar(2000)LIKE
  • 经常：orderBy id int DESC[id为主键，orderBy是必须的]

未经测试（将需要具有维护窗口的生产部署，包括短停机时间）我会提出以下解决方案：

CREATE INDEX listing ON items(user_id,status,type,name,expiration_timestamp,id);

这是我的推理：首先，user_id总是与相等比较一起使用，所以这应该是第一个。status并且type有一个IN子句，因此它们应该是第二个。第三个是name，因为即使LIKE使用尾随通配符，它​​也是高度选择性的。索引expiration_timestamp将有助于显着减少结果的数量。id由于 MySQL 使用索引进行排序，因此将 放在复合索引的末尾是有意义的。没有理由将标签放入索引中，因为带有前导通配符的 LIKE 上的索引是无用的。

这是正确的方法还是你会建议在这里改进一些东西？

还有一个我不确定的事实：如果查询没有类型或状态，MySQL 是否足够“智能”以使用我的复合索引？对 MySQL 索引还是很陌生，感谢您的帮助！

mysql performance
Martin Hope
Blackbam
Asked: 2022-07-11 03:49:31 +0800 CST
  • 3

有一个包含 25.000.000 个条目的表，我有以下查询，每 2 秒启动一次，这会导致非常高的负载（亚马逊 AWS 中高达 40 AAS）。执行需要 20 秒到 5 分钟，这甚至会导致用户浏览器超时和高丢弃率。

SELECT COUNT ( * ) AS `chk` 
  FROM ( SELECT `item_id` 
           FROM `items` 
          WHERE `item_status` IN (...) 
            AND `item_type` = ? 
            AND `user_id` != ? 
            AND `item_name` IN (...) 
          LIMIT 3 
       ) AS OTHERS
;

索引优化已经完成 - user_id, item_name,item_type并且item_status都被索引（每列一个索引）。

更多信息：

  • 一个用户有 1 - 1.000.000 百万个条目
  • item_name 是varchar128
  • item_type 的基数为 7
  • item_status 的基数也为 7
  • 只需要知道是否有三个或更多匹配项

请注意，在大约 50% 的情况下，MySQL 必须检查完整的表，因为找到的项目少于 3 个。所以这个限制只有在超过 3 个项目的情况下才有帮助。

虽然我对某些结果进行 Redis 缓存，但对于此查询，这是不可能的，因为始终需要准确的结果。随着数据库以每秒约 1 个条目的速度增长，查询性能变得非常快。

虽然过去我可以解决大多数索引问题，但这里有一个严重的问题。想过用一些触发器或视图来解决问题，但我不确定这是否有帮助？在高度活跃的生产数据库中，在不知道是否解决问题的情况下进行此类更改是危险的。

我在这里向专业人士提出的问题是：如何用 MySQL 解决这个问题？

mysql performance
Martin Hope
Blackbam
Asked: 2022-06-22 06:58:49 +0800 CST
  • 2

在对该主题进行了一些讨论之后，我可以假设 MySQL InnoDB 有一个非常令人沮丧的事实：当涉及到 DML 时，它不支持（原子）事务。

如果您使用数据进行数据库迁移，那么有一个相当简单的解决方案可以使其完全失败或成功完成。

START TRANSACTION;

INSERT INTO orders(orderNumber,orderDate) VALUES (1,'2020-05-31');
INSERT INTO orders(orderNumber,orderDate) VALUES (1,'2020-05-31');

COMMIT;

事务是针对一个或多个数据库中的数据的数据库操作的原子单元。

不幸的是，以下情况并非如此：

START TRANSACTION;

CREATE TABLE Persons ( PersonID int, LastName varchar(255),FirstName varchar(255));
CREATE TABLE Ducks ( DuckID int, DuckName varchar(255));
CREATE INDEX duckname_index ON Ducks (DuckName varchar(255));

COMMIT;

每个语句都将创建一个隐式提交，因此如果在 MySQL 数据库损坏和迁移一半之间迁移失败。

从文档：

有些语句不能回滚。通常，这些包括数据定义语言 (DDL) 语句，例如创建或删除数据库的语句，创建、删除或更改表或存储例程的语句。您应该在设计事务时不包含此类语句。如果您在无法回滚的事务中早期发出语句，然后另一个语句稍后失败，则在这种情况下无法通过发出 ROLLBACK 语句来回滚事务的全部效果。

由于我们必须为某个软件实施自定义迁移系统，我们现在想知道如何解决这个问题？例如 Symfony ( https://symfony.com/ ) Doctrine ( https://www.doctrine-project.org/ ) 如何在内部解决这个问题？

想法：

  1. 如果出现错误，请在 CI/CD 级别解决并恢复旧数据库？缺点：听起来真的很笨拙。

  2. 仅允许仅包含一个 DML 语句的迁移，并严格分开 DML 和 DDL 迁移。缺点：每个生产部署将有 10 个或数百个迁移文件。

我仍然希望有更好的方法吗？该问题的最佳实际解决方案是什么 - 如果有的话？

mysql innodb
Martin Hope
Blackbam
Asked: 2022-05-25 06:01:46 +0800 CST
  • -1

根据 Amazon RDS 中的见解，大型 MySQL 生产数据库中的以下查询导致高负载（约 50.000.000 个条目）：

SELECT * FROM entities WHERE status='ready' AND user_id='81663729'  AND (primary_name LIKE '%mysearch%' OR additional_names LIKE '%mysearch%')  ORDER BY id DESC  LIMIT 0, 100000

负责它的列：

common_name: VARCHAR(255) Additional_names: VARCHAR(2000) （注意：不幸的是，这是一个逗号分隔的字符串，每个条目包含零到数百个名称，这是可怕的数据库设计）

这个查询表现不佳并不奇怪：前导通配符使索引或多或少不可用（primary_name被索引，additional_names不是）。一些用户在这个表中有很多条目（目前每个用户最多有 2.000.000 个条目，条目数量正在增长） - 所以查询中的 user_id 不会挽救它的性能。

我的问题是什么可能是解决这个问题的最佳方法。

方法一：MySQL FULLTEXT 索引

创建全文索引：

CREATE FULLTEXT INDEX domain ON entities (primary_name,additional_names);

新查询：

SELECT * FROM entities WHERE status='ready' AND user_id='81663729'  AND MATCH (primary_name,additional_names) AGAINST ('mysearch') IN NATURAL LANGUAGE MODE ORDER BY id DESC  LIMIT 0, 100000;

Pro：最容易迁移和实施？

反对：不确定这是否符合要求？创建该索引可能需要大量时间。

方法二：MySQL JSON

第 1 步：使用 JSON 添加附加列

ALTER TABLE entities ADD additional_names_j JSON AFTER additional_names;

第 2 步：所有新实体都作为 json 插入到新的 JSON 列以及旧列中。

第 3 步：可能会运行几天的脚本确保所有旧实体在 JSON 列中都有其条目。

第 4 步：调整应用程序以使用 JSON 列。

第 5 步：删除旧的附加名称列。

询问：SELECT * FROM entities WHERE status='ready' AND user_id='81663729' AND 'mysearch' member of ORDER BY id DESC LIMIT 0, 100000;

优点：搜索 JSON 会更好，并且可以在没有逗号爆炸之类的情况下提取名称。

反对：不确定是否可以对 JSON 列进行部分匹配？此外，努力是相当高的。

方法 3：创建关系表和名称表

这基本上意味着我们有一个带有 id 和 name 的表和 am:n 关系表。

优点：将是漂亮和干净的设计。

Contra：必须与正在运行的应用程序并行存在并填充，在这种情况下，它将导致应用程序逻辑发生巨大变化。

我向专家提出的问题是：哪些方法能最好地解决这种情况——为什么？

注意：该表以每秒约 5 个条目的速度增长，不允许停机。我愿意接受任何其他方法来使这个查询更快，我目前不知道！

mysql query-performance
Martin Hope
Blackbam
Asked: 2022-01-25 07:08:54 +0800 CST
  • 3

为了优化 SELECT 语句，我尝试在 MySQL 中使用以下 SQL 语句创建索引 UNIQUE：

ALTER TABLE credentials DROP INDEX special_credential_id, ADD UNIQUE KEY special_credential_id(special_credential_id)

我的问题是：这是一笔交易吗？这意味着如果创建唯一索引失败，旧的 special_credential_id 索引是否仍然存在？通常创建一个新索引很容易，但我们谈论的是一个包含 100 个 Mio 条目的表。

mysql query-performance