当 MySQL (InnoDB) 将整个表缓存到内存中并且该表中的一行或多行发生更改时,它会做什么?
在这种情况下,MySQL是否会修改内存中的缓存,还是会认为缓存脏,转储缓存的表数据,然后在下次查询时重新缓存该表?
当 MySQL (InnoDB) 将整个表缓存到内存中并且该表中的一行或多行发生更改时,它会做什么?
在这种情况下,MySQL是否会修改内存中的缓存,还是会认为缓存脏,转储缓存的表数据,然后在下次查询时重新缓存该表?
AFAICT 当您从任何客户端向 MySQL 发送查询时,它总是阻塞并等待服务器响应成功或失败。
当执行缓慢的操作(例如在大型表上添加新索引)时,这可能是一个问题,因为它会使客户端处于空闲状态,等待很长时间才能响应。
就我而言,我特别想从 AWS Lambda 函数发送请求。
我在这里找到了一些关于如何分离客户端进程并将客户端进程置于后台的提示https://stackoverflow.com/a/41371255/202168mysql
,还有一些建议,如果客户端关闭连接,MySQL服务器可能会取消请求。但启动客户端的主机mysql
仍然必须保持运行。
感觉这还不是全部?
PostgreSQLCREATE INDEX CONCURRENTLY
为此目的,即查询快速返回并且索引构建在服务器上继续。
我知道MySQL不支持CREATE INDEX CONCURRENTLY
但感觉很奇怪,没有非阻塞的客户端方法?(好吧......有“异步”或“非阻塞”客户端,但它们仍然必须保持相同的连接打开)
我想做的是这样的:
ALTER TABLE ... ADD KEY
查询ADD KEY
在服务器上运行有什么办法可以实现这一点吗?
相关或替代问题:如果我在发送查询后断开客户端连接,是否会ADD KEY
继续运行直至完成/失败而不是被取消?
服务器是AWS RDS上的MySQL 8.0.35,InnoDB表。
有问题的数据库:AWS RDS、MySQL 8、InnoDB。GP3 存储。
我正在尝试将行批量插入到数据库中的多个表中。
我省略了目标表中的所有二级索引,它只有 PK。目标表未分区。
导入的源数据(不在 MySQL 中)按日期范围分区。对于每个分区,我都有一个脚本,它选择一批数据并将其插入到 MySQL。每个分区的批处理循环脚本同时并行运行。
每个批次作为每个表的 pandas 数据帧加载,完成各种转换,然后使用 pandas to_sql
“multi”插入方法将该批次的数据帧插入到 MySQL(在一个数据库事务中)。
我可以想出各种方法来改进这一点。所有 MySQL 批量插入建议中出现的两个建议是 a) 按 PK 顺序插入和 b) 使用LOAD DATA IN FILE
. 我目前两者都不做。但在我彻底重写所有代码之前,我想了解运行当前代码时看到的症状:
我们可以看到导入运行了约 1 小时。并行批处理脚本的数量始终保持一致。批量大小始终保持一致。每个批次需要约 60 秒的时间来处理和插入。在上图中,约 200 个批次(数百万行)已成功处理。但 IOPS 在 30 分钟内大致线性增加,直到达到预配置限制并受到限制。
我的问题是:如果我的插入速率恒定,为什么 IOPS 不断线性增加?
我已经得到了如图所示的复制配置。一切都会复制到任何地方。其中一张表维护会话数据,其中包含由于过期而必须删除的行。我创建了一个事件,每天检查一次过期会话并删除它们:
DELETE FROM session
WHERE expiration < (UNIX_TIMESTAMP() - 7200);
我的问题是:
请注意,站点 A 和站点 B 在物理上是分开的(美国东海岸和西海岸),因此存在网络延迟。
编辑:这是 MySQL 8.0.36
我有一个包含 isin(测试)、日期(Date)和金额(int)的表。
我正在尝试获取每个 isin 的最后 2 个日期的数量。
有人可以告诉我这个技巧吗?
我每天在 MYSQL 8 中运行一系列任务。实际上,这些任务是从 vb.net 应用程序运行的,该应用程序一次使用 8 个连接与 MYSQL 进行交互。这些任务是一系列复杂的联接、选择和表创建。这些计算机是根据其使用寿命专门指定的。我已经在我的笔记本电脑上运行它们大约 6 个月了。
笔记本电脑规格:16GB RAM DDR3、Intel i5-835OU 处理器 1.7 GHZ、4 核、SSD 硬盘 - 完成任务的时间 7981 秒 - 任务期间的平均使用率 75% CPU、43% 内存、100% 磁盘
就在最近,我购买了一台便宜的迷你电脑
Mini PC 1 规格:16GB RAM DDR4、N100 处理器 3.4 GHZ、4 核、SSD 硬盘 - 完成任务的时间 5532 秒 - 任务期间的平均使用率 100% CPU、50% 内存、81% 磁盘
我对速度的升级感到非常惊喜,所以我决定购买他们升级后的迷你电脑。
Mini PC 2 规格:32GB RAM DDR5、Ryzen_7 处理器 4.75 GHZ、8 核、SSD 硬盘 - 完成任务的时间 9178 秒 - 任务期间的平均使用率 14% CPU、19% 内存、46% 磁盘
正如您所看到的,升级后的电脑速度大幅下降。而且升级后的电脑似乎并没有真正工作得那么努力。
那么我的问题是:是否有一些设置/方法可以让我为 MySQL 本身投入更多的精力?我的计算机似乎拥有加速这些任务/查询的所有资源,但它没有利用它们。购买这台电脑的全部目的是为了更快地运行这些任务。有没有办法将所有/大部分计算机资源投入 MYSQL 以提高速度?
谢谢。
在 mysql(或 mariadb)表中:
mytable:
---
id PK Biginteger Autoincrement
name varchar(255)
我搜索这样的东西:
select * from mytable where name like "%someval%";
搜索词也可以是希腊语:
select * from mytable where name like "%οτιδήπ%";
如何根据与我搜索的值的相关性对返回的结果进行排序?我的意思是,一旦我搜索,我希望首先返回最接近的οτιδήπ
结果。name
οτιδήπ
我怎样才能做到这一点?
我创建了一个这样的表,
CREATE TABLE Leaderboard(
userId bigint not null,
matchId bigint not null,
score mediumint not null,
country CHAR(10),
tournamentId int not null
)
我正在创建 5 个人的比赛,他们都来自 5 个不同的国家(“英国”、“美国”、“西班牙”、“德国”、“法国”)
我想在该表中插入大约一百万个条目,并满足以下要求,
一场比赛将这样形成
usergermany | userUSA | userSPAIN | userUK | userFrance
因此,一场比赛将有 5 位用户,他们都来自不同的国家/地区。一场锦标赛会包含多场比赛,并且一个用户只能参加一场锦标赛中的一场比赛。
所以一个示例表将如下所示
userid matchid score country tournamentid
...
988654 3877543 random USA 177
388654 3877543 random GERMANY 177
433432 3877543 random FRANCE 177
776212 3877543 random UK 177
1632987 3877543 random SPAIN 177
2113242 3877544 random SPAIN 177
2918974 3877544 random USA 177
111738 3877544 random UK 177
1772342 3877544 random FRANCE 177
1343243 3877544 random GERMANY 177
123131 3877545 random UK 178
1231414 3877545 random FRANCE 178
2858348 3877545 random GERMANY 178
1122432 3877545 random USA 178
2923434 3877545 random SPAIN 178
...
一个 userId 不能在锦标赛中存在两次,
一个国家不能在一场比赛中出现两次(比赛组由不同的国家组成)
此外,由于一场比赛有 5 名玩家参加,所以 matchid 只会在表中出现 5 次。
我想在该表中插入一百万个条目,用户 ID 在上述约束下随机从 1 到 3 百万。每场比赛有 10,000 场比赛,因此从 1 开始有 100 场比赛。
100 场比赛,每场 10000 场比赛,总共 100 万行。
- 更新 -
create table seq_data as
with recursive tmp(x) as (
select 1
union all
select x+1 from tmp
limit 3000000
)
select * from tmp;
CREATE TABLE if not exists Leaderboard(
userId bigint not null,
matchId bigint not null,
score mediumint not null,
country CHAR(10),
tournamentId int not null
);
DELIMITER //
CREATE PROCEDURE InsertLeaderboardData()
BEGIN
DECLARE tournamentloop INT DEFAULT 0;
DECLARE matchloop INT DEFAULT 0;
DECLARE groupLoop INT DEFAULT 0;
DECLARE matchidKey INT DEFAULT 1;
WHILE tournamentloop < 1 DO
SET tournamentloop = tournamentloop + 1;
SET matchloop = 0;
WHILE matchloop < 10000 DO
SET matchidKey = matchidKey + 1;
SET matchloop = matchloop + 1;
SET groupLoop = 0;
WHILE groupLoop < 5 DO
SET groupLoop = groupLoop + 1;
INSERT INTO Leaderboard (userId, matchId, score, country, tournamentId)
VALUES (1, matchidKey, FLOOR(RAND() * 1000) + 1, ELT(groupLoop, "SPAIN", "FRANCE", "UK", "USA", "GERMANY"), tournamentloop);
SELECT matchidKey;
END WHILE;
END WHILE;
END WHILE;
END;
//
DELIMITER ;
CALL InsertLeaderboardData();
DROP PROCEDURE InsertLeaderboardData;
虽然运行速度很慢,但我还想要多一项功能。
至于 的,INSERT INTO
我userId
暂时将值设置为 1,以便稍后编辑。我想要做的是,我在表 seq_data 中创建了从 1 到 300 万的序列号。对于每个WHILE tournamentloop < 100 DO
范围,我想从seq_data
表中获取 50,000 个不同的数字。这样我就可以给用户一个id。
士气是,任何用户 ID 都不能在锦标赛中出现两次。因此,对于每个锦标赛范围,我需要检索不同的随机 ID 并将其分配给行。我已经创建了序列号表,我不知道这里最好的方法是什么。
- 陈述
INSERT INTO Leaderboard (userId, matchId, score, country, tournamentId) VALUES (useridKey, matchidKey, FLOOR(RAND() * 1000) + 1, ELT(groupLoop, "SPAIN", "FRANCE", "UK", "USA", "GERMANY"), tournamentloop);
我把这个变成
DECLARE tournamentloop INT DEFAULT 0;
DECLARE matchloop INT DEFAULT 0;
DECLARE groupLoop INT DEFAULT 0;
DECLARE matchidKey INT DEFAULT 1;
DECLARE useridKey INT DEFAULT 1;
PREPARE insertStmt FROM 'INSERT INTO Leaderboard (userId, matchId, score, country, tournamentId)VALUES (useridKey, matchidKey, FLOOR(RAND() * 1000) + 1, ELT(groupLoop, "SPAIN", "FRANCE", "UK", "USA", "GERMANY"), tournamentloop);';
EXECUTE insertStmt;
我收到错误:Unknown column 'useridKey' in 'field list'
我正在创建如下所示的 2 个表,我希望这两个表具有一对一的关系,并且每当我插入一行时,User
我都希望将相同的 PK 插入到另一个表中。这是否只INSERT INTO
适用于 1 个表,User
以便将相同的内容points
也插入到其中。
CREATE TABLE if not exists User (
id bigint AUTO_INCREMENT,
country VARCHAR(128),
PRIMARY KEY (id)
);
CREATE TABLE if not exists points (
userid bigint UNIQUE NOT NULL,
points bigint
);
ALTER TABLE ['points'] ADD CONSTRAINT FK FOREIGN KEY([userid]) REFERENCES [User]([id]);
我也使用 mysql 8.0.28,ALTER TABLE ['points'] ADD CONSTRAINT FK FOREIGN KEY([userid]) REFERENCES [User]([id]);
此语句返回语法错误[]
正确的实施方法是什么?另外出于好奇,我如何在创建表时定义外键?
我有一个非常大的 InnoDB 表,目前存储了大约 2.6 亿行,大小为 40GB。
mysql> SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'db' AND TABLE_NAME = 'objects';
+---------------+--------------+------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+------------+----------------+-------------+-----------------+--------------+-----------+---------------------+-------------+------------+----------+-------------------+-----------+-----------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | PARTITION_ORDINAL_POSITION | SUBPARTITION_ORDINAL_POSITION | PARTITION_METHOD | SUBPARTITION_METHOD | PARTITION_EXPRESSION | SUBPARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | CREATE_TIME | UPDATE_TIME | CHECK_TIME | CHECKSUM | PARTITION_COMMENT | NODEGROUP | TABLESPACE_NAME |
+---------------+--------------+------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+------------+----------------+-------------+-----------------+--------------+-----------+---------------------+-------------+------------+----------+-------------------+-----------+-----------------+
| def | db | objects | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 225970904 | 171 | 38667747328 | NULL | 8046510080 | 0 | 2024-04-02 12:08:15 | NULL | NULL | NULL | | | NULL |
+---------------+--------------+------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+------------+----------------+-------------+-----------------+--------------+-----------+---------------------+-------------+------------+----------+-------------------+-----------+-----------------+
1 row in set (0.04 sec)
为了避免表的无限增长,我计划在数据库每天不那么繁忙的时间删除超过 2 年的行。我尝试了以下查询。
DELETE FROM objects WHERE DATEDIFF(NOW(), timestamp ) >= 731;
但它给了我错误:
Error 1206: The total number of locks exceeds the lock table size
其大小innodb_buffer_pool_size
为 128 MB,我认为这非常小。不幸的是,主机的可用 RAM 不超过 300 MB。我还没有尝试增加innodb_buffer_pool_size
缓冲区的大小,但我认为几百 MB 还不够,而且没有更多的空间可以增加它。查询非常慢,主机 RAM 较低,数据库正在积极为客户提供服务,并且正在运行的应用程序将数据一致地插入到数据库中。还有另一个人正在使用该应用程序,如果想重新启动数据库,我必须先要求他停止该应用程序。因此,innodb_buffer_pool_size
通过反复试验进行设置是一项棘手的工作。您能建议我如何计算大约的最小尺寸innodb_buffer_pool_size
以避免该错误吗?
我没有尝试过的另一种方法是 - 因为表有timestamp
和objectID
列,并且它由这些列索引,所以可以逐个对象删除过期的行。首先让我们收集所有唯一的对象 ID:
SELECT DISTINCT objectID FROM objects;
大约需要 30-40 秒。然后通过objectID删除:
DELETE FROM objects WHERE objectID = ... DATEDIFF(NOW(), timestamp ) >= 731;
但如何将这两个查询合并为一个查询呢?
DELETE FROM objects WHERE objectID IN (SELECT DISTINCT objectID FROM objects) AND DATEDIFF(NOW(), timestamp ) >= 731;
给出一个错误
ERROR 1093 (HY000): You can't specify target table 'objects' for update in FROM clause
Description: Ubuntu 12.04.1 LTS
mysql> select version();
+-----------------------------------+
| version() |
+-----------------------------------+
| 5.6.14-1+debphp.org~precise+1-log |
+-----------------------------------+