V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
shrugginG
V2EX  ›  MySQL

mysql 小白请教大佬一个问题

  •  
  •   shrugginG · 75 天前 · 2199 次点击
    这是一个创建于 75 天前的主题,其中的信息可能已经有所发展或是发生改变。

    首先 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. 大佬有没有其他解决方案可以赐教
    第 1 条附言  ·  73 天前

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

    在我将原始的触发器:

    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
        1
    Gilgamesh7  
       75 天前
    1. 看能不能增加一个 redis ,将所有的 url redis 里面缓存,每次入库前,在 redis 里面检查数据是否重复,去重后直接入库,修改 url 字段为唯一约束。去掉 url_sha256 。
    2. url 字段 增加索引,假设不存在并发写入的情况下,每次入库前,进行 in 查询,过滤掉重复数据。
    3. 减少批量插入的数量,单个 insert into 需要 2s 出头 这个包含获取 url 的时间吗,正常 1 条记录插入应该在 ms 级
    shrugginG
        2
    shrugginG  
    OP
       75 天前 via iPhone
    @Gilgamesh7 感谢大佬,2s 是不包含获取 url 的时间的,就是单纯的 sql 执行时间
    julyclyde
        4
    julyclyde  
       75 天前
    url 唯一

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

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

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

    或者试试把 varchar 换 text ?
    chaoschick
        10
    chaoschick  
       73 天前
    开启事务
    START TRANSACTION;
    INSERT INTO ... VALUES (...), (...), ...;
    COMMIT;
    redog
        11
    redog  
       73 天前
    会不会是 TDSQL 在你建表用了 unique 索引时,把这个当聚簇了?
    你要不重建一张表,什么索引也不用,就把 ID 设置成主键,然后把原表的数据插入再试试?
    MoYi123
        12
    MoYi123  
       73 天前
    看看索引的大小, 可能是 1G 内存加载不到内存里, 就转成不用额外内存, 直接扫表的方案了.
    wxf666
        13
    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
        14
    HolderRoberts  
       73 天前
    我和一个朋友很好奇为什么这个触发器前后会有明显的性能差异,请问你方便展示一下 phishy.phishy_urls 的定义吗?
    shrugginG
        15
    shrugginG  
    OP
       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
        16
    redog  
       64 天前
    如果我没弄错的话,原因是你的 exists 语句,这里的逻辑是用 phishy_urls 表(外表)里的"每一条"url_sha256 去比对插入后的值(内表),所以每一次插入都要用 phishy_urls 表里已有的 10W 多条记录去逐一对比,这个时候我记得是外表索引无意义所以变成了全表扫描。
    话说,你用 in 来判断就没这问题,或是你反过来用 NEW 来做外表。
    你修改后,用了专属的 ON DUPLICATE KEY UPDATE 本来就是优化后的语句自然不存在这个问题。
    shrugginG
        17
    shrugginG  
    OP
       63 天前
    @redog 感谢解答,这个内外表的概念我确实没有了解,看来确实该加强学习了哈哈,还有就是 phishy_url 是很多表的汇总表,里面应该是有 300w+的数据量的,所以之前非常慢。
    redog
        18
    redog  
       63 天前
    @shrugginG 客气,这个 exists 使用时得考虑相关表里记录的多少,而且有时你以为它会走索引,结果语句你没写好,它就不按你想要的走索引,我一般无脑用 in ,不差那点性能,至少不会出现死活不走索引这种现象。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   1618 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 27ms · UTC 16:54 · PVG 00:54 · LAX 08:54 · JFK 11:54
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.