mysql 小白请教大佬一个问题

75 天前
 shrugginG

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

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

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

  1. 上面的逻辑是不是存在问题
  2. 是不是我购买的低配置的 tqsql 硬件配置制约了插入的速度？必须提升硬件配置才有可能解决
  3. 大佬有没有其他解决方案可以赐教
2198 次点击
所在节点    MySQL
18 条回复
Gilgamesh7
75 天前
1. 看能不能增加一个 redis ，将所有的 url redis 里面缓存，每次入库前，在 redis 里面检查数据是否重复，去重后直接入库，修改 url 字段为唯一约束。去掉 url_sha256 。
2. url 字段 增加索引，假设不存在并发写入的情况下，每次入库前，进行 in 查询，过滤掉重复数据。
3. 减少批量插入的数量，单个 insert into 需要 2s 出头 这个包含获取 url 的时间吗，正常 1 条记录插入应该在 ms 级
shrugginG
75 天前
@Gilgamesh7 感谢大佬，2s 是不包含获取 url 的时间的，就是单纯的 sql 执行时间
kongkx
75 天前
https://www.perplexity.ai/search/auto-generated-definition-crea-TcVC5zgmSJuyvVEA1pHgSg

试试看？
julyclyde
75 天前
url 唯一
和
url_sha256 唯一
根本两码事啊
ttoh
75 天前
正常情况下，插入时会通过搜索唯一索引树搜索判断是否存在，不需要全表扫描。如果有足够的空闲时间，可以考虑 drop 这个唯一索引后验证一下，注意不要插入重复的 url_sha256 ，不然重新加唯一索引会报错。
sagaxu
75 天前
每次插入都需要全表扫描+重建索引？没听说过哪个关系库这么干的。

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

不太可能是插入性能问题，但是并发插入可能会有死锁。
shrugginG
74 天前
@ttoh 我还真测试过 drop 掉 url_sha256 的唯一索引，但是测试发现 drop 前后插入速度基本没有区别
shrugginG
74 天前
@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
74 天前
我对 unique 这玩意的印象是纯纯的拖插入性能（是约束，不是优化），我反正全是业务层做的去重。。。
不过这个也太慢了。。。

或者试试把 varchar 换 text ？
chaoschick
73 天前
开启事务
START TRANSACTION;
INSERT INTO ... VALUES (...), (...), ...;
COMMIT;
redog
73 天前
会不会是 TDSQL 在你建表用了 unique 索引时，把这个当聚簇了？
你要不重建一张表，什么索引也不用，就把 ID 设置成主键，然后把原表的数据插入再试试？
MoYi123
73 天前
看看索引的大小, 可能是 1G 内存加载不到内存里, 就转成不用额外内存, 直接扫表的方案了.
wxf666
73 天前
感觉你的场景，用 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
73 天前
我和一个朋友很好奇为什么这个触发器前后会有明显的性能差异，请问你方便展示一下 phishy.phishy_urls 的定义吗？
shrugginG
72 天前
@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
64 天前
如果我没弄错的话，原因是你的 exists 语句，这里的逻辑是用 phishy_urls 表（外表）里的"每一条"url_sha256 去比对插入后的值（内表），所以每一次插入都要用 phishy_urls 表里已有的 10W 多条记录去逐一对比，这个时候我记得是外表索引无意义所以变成了全表扫描。
话说，你用 in 来判断就没这问题，或是你反过来用 NEW 来做外表。
你修改后，用了专属的 ON DUPLICATE KEY UPDATE 本来就是优化后的语句自然不存在这个问题。
shrugginG
63 天前
@redog 感谢解答，这个内外表的概念我确实没有了解，看来确实该加强学习了哈哈，还有就是 phishy_url 是很多表的汇总表，里面应该是有 300w+的数据量的，所以之前非常慢。
redog
63 天前
@shrugginG 客气，这个 exists 使用时得考虑相关表里记录的多少，而且有时你以为它会走索引，结果语句你没写好，它就不按你想要的走索引，我一般无脑用 in ，不差那点性能，至少不会出现死活不走索引这种现象。

这是一个专为移动设备优化的页面（即为了让你能够在 Google 搜索结果里秒开这个页面），如果你希望参与 V2EX 社区的讨论，你可以继续到 V2EX 上打开本讨论主题的完整版本。

https://www.v2ex.com/t/1070957

V2EX 是创意工作者们的社区，是一个分享自己正在做的有趣事物、交流想法，可以遇见新朋友甚至新机会的地方。

V2EX is a community of developers, designers and creative people.

© 2021 V2EX