mysql 小白请教大佬一个问题

-- 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 是唯一索引，那我这条语句岂不是逻辑上可以分两步：

先检查本条 url_sha256 是否存在，存在就直接 ignore （我理解的因为有索引的存在这个过程应该很快）
如果不存在就直接在表尾插入新数据。（因为我的主键是自增 ID ，按照聚簇的话是不是新数据就追加在最后面？）如果按照上述逻辑的话是不是就不应该有大量全表扫描的操作了？难道是插入完毕后重建索引的操作需要大量的全表扫描？

原谅我数据库知识太欠缺了，我知道上面很多推测只是我片面认识的结果，希望能有大佬帮忙解答一下:

上面的逻辑是不是存在问题
是不是我购买的低配置的 tqsql 硬件配置制约了插入的速度？必须提升硬件配置才有可能解决
大佬有没有其他解决方案可以赐教

第 1 条附言 · 69 天前

感谢各位大佬解答，我现在已经找到原因了，原因其实不是出在索引上，而是触发器。触发器的目的是在该张表插入新内容时立即插入到另外一张表，其实我之前也想过触发器的问题，但我当时想当然的认为了触发器是相对独立的，其执行时间不会影响当前插入语句：

在我将原始的触发器：

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操作造成了锁的征用？

还请兄弟们赐教

18 条回复 • 2024-09-19 17:36:16 +08:00

Gilgamesh7

71 天前

1. 看能不能增加一个 redis ，将所有的 url redis 里面缓存，每次入库前，在 redis 里面检查数据是否重复，去重后直接入库，修改 url 字段为唯一约束。去掉 url_sha256 。
2. url 字段增加索引，假设不存在并发写入的情况下，每次入库前，进行 in 查询，过滤掉重复数据。
3. 减少批量插入的数量，单个 insert into 需要 2s 出头这个包含获取 url 的时间吗，正常 1 条记录插入应该在 ms 级

shrugginG

71 天前 via iPhone

@Gilgamesh7 感谢大佬，2s 是不包含获取 url 的时间的，就是单纯的 sql 执行时间

kongkx

71 天前 via iPhone

https://www.perplexity.ai/search/auto-generated-definition-crea-TcVC5zgmSJuyvVEA1pHgSg

试试看？

julyclyde

71 天前

url 唯一
和
url_sha256 唯一
根本两码事啊

ttoh

71 天前

正常情况下，插入时会通过搜索唯一索引树搜索判断是否存在，不需要全表扫描。如果有足够的空闲时间，可以考虑 drop 这个唯一索引后验证一下，注意不要插入重复的 url_sha256 ，不然重新加唯一索引会报错。

sagaxu

71 天前

每次插入都需要全表扫描+重建索引？没听说过哪个关系库这么干的。

先用 explain analyze insert ignore into ...分析下插入性能，看看哪一步耗时多。

不太可能是插入性能问题，但是并发插入可能会有死锁。

shrugginG

70 天前

@ttoh 我还真测试过 drop 掉 url_sha256 的唯一索引，但是测试发现 drop 前后插入速度基本没有区别

shrugginG

70 天前

@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
}
]
```
我看了这应该就是全表扫描了吧，根本没有用到唯一索引

ntedshen

70 天前

我对 unique 这玩意的印象是纯纯的拖插入性能（是约束，不是优化），我反正全是业务层做的去重。。。
不过这个也太慢了。。。

或者试试把 varchar 换 text ？

chaoschick

69 天前

开启事务
START TRANSACTION;
INSERT INTO ... VALUES (...), (...), ...;
COMMIT;

redog

69 天前

会不会是 TDSQL 在你建表用了 unique 索引时，把这个当聚簇了？
你要不重建一张表，什么索引也不用，就把 ID 设置成主键，然后把原表的数据插入再试试？

MoYi123

69 天前

看看索引的大小, 可能是 1G 内存加载不到内存里, 就转成不用额外内存, 直接扫表的方案了.

wxf666

69 天前

感觉你的场景，用 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)
```

HolderRoberts

69 天前

我和一个朋友很好奇为什么这个触发器前后会有明显的性能差异，请问你方便展示一下 phishy.phishy_urls 的定义吗？

shrugginG

68 天前

@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)
);

redog

60 天前

如果我没弄错的话，原因是你的 exists 语句，这里的逻辑是用 phishy_urls 表（外表）里的"每一条"url_sha256 去比对插入后的值（内表），所以每一次插入都要用 phishy_urls 表里已有的 10W 多条记录去逐一对比，这个时候我记得是外表索引无意义所以变成了全表扫描。
话说，你用 in 来判断就没这问题，或是你反过来用 NEW 来做外表。
你修改后，用了专属的 ON DUPLICATE KEY UPDATE 本来就是优化后的语句自然不存在这个问题。

shrugginG

59 天前

@redog 感谢解答，这个内外表的概念我确实没有了解，看来确实该加强学习了哈哈，还有就是 phishy_url 是很多表的汇总表，里面应该是有 300w+的数据量的，所以之前非常慢。

redog

59 天前

@shrugginG 客气，这个 exists 使用时得考虑相关表里记录的多少，而且有时你以为它会走索引，结果语句你没写好，它就不按你想要的走索引，我一般无脑用 in ，不差那点性能，至少不会出现死活不走索引这种现象。