首先 mysql 环境是我购买的腾讯云学生 TDSQL,1c1g60G 。 最开始我创建了一张表:
-- auto-generated definition
create table phishtank_database
(
id int auto_increment
primary key,
phish_id int not null,
url varchar(2048) not null,
url_sha256 char(64) not null,
phish_detail_url varchar(2048) not null,
submission_time datetime not null,
verified varchar(255) not null,
verification_time datetime not null,
online varchar(255) not null,
target varchar(255) not null,
created_at timestamp default CURRENT_TIMESTAMP not null,
etag varchar(255) not null,
constraint url_sha256_unique
unique (url_sha256)
)
charset = utf8mb4;
因为为了保证 url 是唯一的,给 url_sha256 加了唯一约束,我是有一个 github action 定时抓取最新的 url 到数据库中的,已经执行了好几个月没有问题。但是突然最近疯狂报错,我一看是现在批量 insert 的速度太慢。我的批量 insert 模板就是:
insert ignore into phishing_intelligence.phishtank_database (phish_id, url, url_sha256, phish_detail_url, submission_time , verified, verification_time, online, target, etag) values (?, ?, ?, ?, ? , ?, ?, ?, ?, ?),(?, ?, ?, ?, ? , ?, ?, ?, ?, ?),......
我测试了单个 insert into 需要 2s 出头,有一次批量插入 300 余条记录花费了 18min 。 然后我看腾讯云控制台中有一次慢 sql 执行竟然扫描了 7 亿多行。
我很自然会想到由于是需要检查唯一索引 url_sha256 每次插入都需要全表扫描+重建索引。随着数据量增加(但其实现在这个表中也只有 10w 出头的行)执行时间逐渐变长。而且我现在手动执行一次上述 instert 语句然后在实时监控中看到每秒的 innodb_rows_read 在 10w+。
但是我转头一想,既然 url_sha256 是唯一索引,那我这条语句岂不是逻辑上可以分两步:
原谅我数据库知识太欠缺了,我知道上面很多推测只是我片面认识的结果,希望能有大佬帮忙解答一下:
感谢各位大佬解答,我现在已经找到原因了,原因其实不是出在索引上,而是触发器。触发器的目的是在该张表插入新内容时立即插入到另外一张表,其实我之前也想过触发器的问题,但我当时想当然的认为了触发器是相对独立的,其执行时间不会影响当前插入语句:
在我将原始的触发器:
DELIMITER $$
CREATE TRIGGER after_phishtank_database_insert
AFTER INSERT
ON phishing_intelligence.phishtank_database
FOR EACH ROW
BEGIN
IF EXISTS(SELECT 1 FROM phishy.phishy_urls WHERE phishy_urls.url_sha256 = NEW.url_sha256) THEN
-- If new url has existed in phishy_urls, update the phishtank column to TRUE
UPDATE
phishy.phishy_urls
SET phishtank = TRUE,
phishtank_brand = NEW.target
WHERE phishy_urls.url_sha256 = NEW.url_sha256;
ELSE
-- If new url does not exist in phishy_urls, insert it
INSERT INTO phishy.phishy_urls (url_sha256, url, phishtank, phishtank_brand)
VALUES (NEW.url_sha256, NEW.url, TRUE, NEW.target);
END if;
END $$
DELIMITER ;
更换为:
DELIMITER $$
CREATE TRIGGER after_phishtank_database_insert
AFTER INSERT
ON phishing_intelligence.phishtank_database
FOR EACH ROW
BEGIN
INSERT INTO phishy.phishy_urls (url_sha256, url, phishtank, phishtank_brand)
VALUES (NEW.url_sha256, NEW.url, TRUE, NEW.target)
ON DUPLICATE KEY UPDATE phishtank = TRUE,
phishtank_brand = VALUES(phishtank_brand);
END $$
DELIMITER ;
后问题得到了最终解决,现在慢插入问题得到了解决。
但是解决背后的原理还不是很清楚,我问GPT大概的意思会涉及到锁?肯能是批量插入时大量的SELECT,UPDATE以及UPDATE操作造成了锁的征用?
还请兄弟们赐教
1
Gilgamesh7 58 天前
1. 看能不能增加一个 redis ,将所有的 url redis 里面缓存,每次入库前,在 redis 里面检查数据是否重复,去重后直接入库,修改 url 字段为唯一约束。去掉 url_sha256 。
2. url 字段 增加索引,假设不存在并发写入的情况下,每次入库前,进行 in 查询,过滤掉重复数据。 3. 减少批量插入的数量,单个 insert into 需要 2s 出头 这个包含获取 url 的时间吗,正常 1 条记录插入应该在 ms 级 |
2
shrugginG OP @Gilgamesh7 感谢大佬,2s 是不包含获取 url 的时间的,就是单纯的 sql 执行时间
|
3
kongkx 58 天前 via iPhone
|
4
julyclyde 58 天前
url 唯一
和 url_sha256 唯一 根本两码事啊 |
5
ttoh 58 天前
正常情况下,插入时会通过搜索唯一索引树搜索判断是否存在,不需要全表扫描。如果有足够的空闲时间,可以考虑 drop 这个唯一索引后验证一下,注意不要插入重复的 url_sha256 ,不然重新加唯一索引会报错。
|
6
sagaxu 58 天前
每次插入都需要全表扫描+重建索引?没听说过哪个关系库这么干的。
先用 explain analyze insert ignore into ...分析下插入性能,看看哪一步耗时多。 不太可能是插入性能问题,但是并发插入可能会有死锁。 |
8
shrugginG OP @sagaxu 我用 explain 测试过了
``` EXPLAIN INSERT INTO phishing_intelligence.phishtank_database (phish_id, url, url_sha256, phish_detail_url, submission_time, verified, verification_time, online, target, etag) VALUES ('8746113', 'https://free-5477419.webadorsite.com/', '66c5960b2546bb5d7807213f8d0b6c574ac9b329aca4cc9db6ded49dbc7c4662', 'http://www.phishtank.com/phish_detail.php?phish_id=8746113', '2024-09-06T11:43:49', 'yes', '2024-09-06T11:53:23', 'yes', 'Other', '7c90038b7ae65365a87c0ef8615e98e4'); ``` 结果是 ``` [ { "id": 1, "select_type": "INSERT", "table": "phishtank_database", "partitions": null, "type": "ALL", "possible_keys": null, "key": null, "key_len": null, "ref": null, "rows": null, "filtered": null, "Extra": null } ] ``` 我看了这应该就是全表扫描了吧,根本没有用到唯一索引 |
9
ntedshen 57 天前
我对 unique 这玩意的印象是纯纯的拖插入性能(是约束,不是优化),我反正全是业务层做的去重。。。
不过这个也太慢了。。。 或者试试把 varchar 换 text ? |
10
chaoschick 57 天前
开启事务
START TRANSACTION; INSERT INTO ... VALUES (...), (...), ...; COMMIT; |
11
redog 57 天前
会不会是 TDSQL 在你建表用了 unique 索引时,把这个当聚簇了?
你要不重建一张表,什么索引也不用,就把 ID 设置成主键,然后把原表的数据插入再试试? |
12
MoYi123 57 天前
看看索引的大小, 可能是 1G 内存加载不到内存里, 就转成不用额外内存, 直接扫表的方案了.
|
13
wxf666 56 天前
感觉你的场景,用 SQLite 也挺好呀。。
## 测试结果 *(同样表结构,每次开事务插入一行再提交)* - 100W 行,104 秒,平均约 10000 TPS ,共 360MB - 1000W 行,1450 秒,平均约 7000 TPS ,共 3.6GB ## 环境 - CPU:i5-8250U (六七年前的低压轻薄本) - 内存:测试时占用 14 MB - 系统:Deepin V20 (基于 Debian 10 ) - 软件:Python 3.9 - 固态:顺序:500 MB/s 读,300 MB/s 写;随机:20 MB/s 读,64 MB/s 写 ## 代码 ```python # V 站吞空格,缩进改为全角空格了 import time import random import hashlib import sqlite3 ROWS_PER_INSERT = 1 ROWS_TOTAL = 1000_0000 DB_PATH = '/数据库存放路径/名称.db' db = sqlite3.connect(DB_PATH) db.execute('PRAGMA journal_mode = WAL') db.execute('PRAGMA synchronous = NORMAL') db.execute('PRAGMA wal_autocheckpoint = 10000') db.execute(''' CREATE TABLE IF NOT EXISTS phishtank_database ( id INTEGER PRIMARY KEY, phish_id INT, url TEXT, url_sha256 TEXT UNIQUE, phish_detail_url TEXT, submission_time DATETIME, verified TEXT, verification_time DATETIME, online TEXT, target TEXT, created_at DATETIME DEFAULT (datetime('now', 'localtime')), etag TEXT ) ''') insert_sql = ''' INSERT OR IGNORE INTO phishtank_database (phish_id, url, url_sha256, phish_detail_url, submission_time, verified, verification_time, online, target, etag) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ''' first_id, = db.execute('SELECT IFNULL(MAX(id), 0) + 1 FROM phishtank_database').fetchone() for next_id in range(first_id, first_id + ROWS_TOTAL, ROWS_PER_INSERT): rows = [] now = time.strftime('%Y-%m-%d %H:%M:%S') for phish_id in range(next_id, min(next_id + ROWS_PER_INSERT, first_id + ROWS_TOTAL)): url = f'https://free-{phish_id}.webadorsite.com/' detail_url = f'http://www.phishtank.com/phish_detail.php?phish_id={phish_id}' rows.append(( phish_id, url, hashlib.sha256(url.encode('utf-8')).hexdigest(), detail_url, now, 'yes', now, 'yes', 'Other', random.randbytes(16).hex(), )) with db: db.executemany(insert_sql, rows) ``` |
14
HolderRoberts 56 天前
我和一个朋友很好奇为什么这个触发器前后会有明显的性能差异,请问你方便展示一下 phishy.phishy_urls 的定义吗?
|
15
shrugginG OP @HolderRoberts
-- auto-generated definition create table phishy_urls ( id int auto_increment primary key, url varchar(2048) not null, url_sha256 char(64) not null, ecrimex tinyint(1) default 0 null, ecrimex_brand varchar(255) null, phishtank tinyint(1) default 0 null, phishtank_brand varchar(255) null, openphish tinyint(1) default 0 null, openphish_brand varchar(255) null, created_at timestamp default CURRENT_TIMESTAMP not null, is_crawled tinyint(1) default 0 null, page_url varchar(2048) null, is_accessible tinyint(1) null, is_completed tinyint(1) null, status_code smallint unsigned null, title varchar(255) collate utf8mb4_unicode_ci null, ip varchar(39) null, port int null, updated_at timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP, traffic_captured tinyint(1) default 0 null, last_crawled_time datetime null, constraint url_sha256_unique unique (url_sha256) ); |
16
redog 47 天前
如果我没弄错的话,原因是你的 exists 语句,这里的逻辑是用 phishy_urls 表(外表)里的"每一条"url_sha256 去比对插入后的值(内表),所以每一次插入都要用 phishy_urls 表里已有的 10W 多条记录去逐一对比,这个时候我记得是外表索引无意义所以变成了全表扫描。
话说,你用 in 来判断就没这问题,或是你反过来用 NEW 来做外表。 你修改后,用了专属的 ON DUPLICATE KEY UPDATE 本来就是优化后的语句自然不存在这个问题。 |