如何加速数据爬取与MySQL数据插入操作？

Great question—dealing with bulk table scraping and database inserts can get slow fast, especially when you’re handling 100–1000 tables each with 20 rows. Let’s break down the optimizations step by step, tailored to your exact setup:

1. Speed Up Scraping & Data Extraction #

Most people overlook this part, but slow scraping can bottleneck the entire pipeline before you even get to inserts:

• Batch Extract Rows: Instead of using XPath to fetch one row at a time, grab all rows in a table in a single query. For example, use //table/tr to get every <tr> element in the table, then iterate over that result set. Avoid re-parsing the DOM for each row—this cuts down on unnecessary processing overhead.
• Simplify XPath Queries: Ditch complex axes or contains() calls unless you absolutely need them. Use direct paths like tr/td (within the context of your table) instead of global //td searches. Faster XPath resolution = faster data extraction.
• Parallelize Requests: Scraping is IO-bound, so use async (like aiohttp) or multi-threading (with concurrent.futures) to fetch multiple pages at once. Just be respectful—add random delays between requests, limit concurrency (start with 5–10 threads), and check the site’s robots.txt to avoid getting blocked.
• Cache Pages: Save fetched HTML to a temporary file or in-memory cache (like lru_cache) if you might need to reprocess data later. This avoids re-requesting pages if you hit an error during insertion.

2. Optimize Data Processing Before Insert #

Don’t waste time processing rows one at a time—batch your work:

• Collect All Data First: Gather all rows from a table into a list of tuples (e.g., [(col1_val, col2_val), ...]) before doing any formatting or insertion. This reduces context switching between extraction and DB operations.
• Minimize Transformations: Skip unnecessary string manipulations or type conversions unless your MySQL schema requires it. If the scraped text matches your column types (e.g., integers, dates), use it directly.
• Use Efficient Data Structures: Stick to lists/tuples for bulk data instead of dictionaries—they’re lighter and faster to iterate over when preparing insert queries.

3. MySQL Insert Optimization (The Big Win) #

Insertions are almost always the bottleneck here. These tweaks will give you the biggest speed boost:

• Bulk Insert Instead of Single Rows: Replace repeated INSERT INTO ... VALUES (...) calls with a single bulk insert. Most MySQL drivers (like pymysql or mysql-connector-python) have an executemany() method that handles this efficiently—use it instead of looping over execute():

  # Example with pymysql
  bulk_data = [(val1, val2, ..., val10), (val11, val12, ..., val20), ...]
  cursor.executemany("INSERT INTO your_table (col1, col2, ..., col10) VALUES (%s, %s, ..., %s)", bulk_data)
  

• Turn Off Auto-Commit: By default, MySQL commits every single insert, which triggers expensive disk IO. Run SET AUTOCOMMIT = 0 at the start of your insertion process, then COMMIT once you’ve inserted all data for a table (or a batch of tables).
• Wrap Inserts in Transactions: Group all inserts for a single table (or a batch of tables) into a single transaction. This reduces the number of disk writes—InnoDB only flushes the log once per transaction instead of per row.
• Tweak MySQL Configuration: If you have access to the MySQL server, adjust these settings (backup your config first!):
  • Increase innodb_buffer_pool_size to 50–70% of your server’s physical memory (this lets MySQL cache more data in RAM).
  • Set innodb_log_file_size to 1–2GB (don’t exceed 1/4 of the buffer pool size) to reduce log rotation frequency.
  • For non-critical data, set innodb_flush_log_at_trx_commit = 2—this flushes logs to disk every second instead of every transaction, drastically speeding up inserts (note: you might lose 1 second of data if the server crashes).
• Reuse Database Connections/Cursors: Don’t create a new connection or cursor for every table. Keep a single connection open for the entire pipeline, and reuse the same cursor for all inserts—this cuts down on connection setup overhead.

4. Final Checks #

• Profile Your Code: Use cProfile or simple time.time() checks to see which part of your pipeline is slow. Is it scraping? Extraction? Inserts? Focus your optimizations there.
• Avoid SQL Injection: Always use parameterized queries (like executemany() with placeholders) instead of string concatenation. It’s safer and faster.
• Respect Rate Limits: If the site blocks you, all optimizations are useless. Add delays, rotate user agents, or use a proxy pool if needed.

内容的提问来源于stack exchange，提问作者puppet