从MySQL 5.6迁移至MariaDB 10.1后导出数据执行时长优化请求

Hey there, let’s dig into why your export script’s runtime doubled after moving from MySQL 5.6 to MariaDB 10.1. I’ve troubleshooted similar performance gaps between these two databases before, so here are practical, step-by-step optimizations to get your speed back on track:

The first thing to check is whether MariaDB’s query optimizer is handling your join differently than MySQL 5.6. Run EXPLAIN EXTENDED on your关联 query in both environments and compare the outputs side by side:

• Verify the join order: MariaDB’s optimizer might pick a less efficient table order for your 5M+ record dataset. If you see a difference, try forcing the original order with STRAIGHT_JOIN (e.g., SELECT ... FROM table1 STRAIGHT_JOIN table2 ON ...) to test if it speeds things up.
• Check index usage: Look for type: ALL (full table scan) in the type column—this is a red flag. Ensure all join columns and filter conditions have proper indexes. If an index is being ignored, use FORCE INDEX(index_name) temporarily to confirm if it fixes the issue (only use this long-term if you’re certain the optimizer’s choice is wrong).
• Compare the rows column: MariaDB might be estimating a much higher number of rows to process than MySQL, leading to suboptimal execution plans.

MariaDB 10.1 has different default settings than MySQL 5.6, especially around memory allocation. Adjust these key settings in your my.cnf/my.ini file:

• innodb_buffer_pool_size: Set this to ~70-80% of your server’s available RAM (if it’s dedicated to the database). MySQL 5.6 might have had a higher tuned value, while MariaDB’s default could be too small for your large dataset, forcing more disk I/O.
• innodb_log_file_size: Larger log files reduce checkpointing overhead. Try setting this to 1-2GB (follow the proper resize steps: stop MariaDB, delete old ib_logfile* files, restart the service).
• query_cache_size/query_cache_type: If your export query is dynamic (not identical every run), the query cache might be causing overhead instead of helping. Disable it with query_cache_type=0 and query_cache_size=0.
• join_buffer_size/sort_buffer_size: If your query does sorting or joining without indexes, increase these values (start with 256k-1M each) to reduce disk-based operations. Avoid oversetting them, though—too large can cause memory contention.

Even if the query is faster, PHP might be the bottleneck. Try these tweaks:

• Avoid fetching all rows at once: Instead of fetchAll(), use cursor-based fetching (e.g., PDO::FETCH_ASSOC with PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY or mysqli_use_result() for mysqli). This streams rows one at a time, reducing memory usage and keeping the database from holding the result set longer than needed.
• Batch disk writes: If exporting to a file, write to disk in batches (e.g., every 1000 rows) instead of building a huge string in memory. This cuts down on PHP’s memory overhead and reduces I/O wait times.
• Shift logic to the database: If you’re doing data formatting (like date conversions, string concatenation) in PHP, move that work to your SQL query—databases are optimized for these operations and will handle them faster than PHP.
• Check PHP memory limits: Ensure memory_limit in php.ini is set high enough to avoid garbage collection thrashing, but don’t set it unnecessarily high (e.g., 256M or 512M should suffice for most cases).

If PHP is still too slow, consider exporting directly from MariaDB—this skips the PHP layer entirely and is drastically faster:

• Use SELECT ... INTO OUTFILE: This writes results directly to a file on the database server. Example:

  SELECT t1.column1, t2.column2, t3.column3
  FROM table1 t1
  JOIN table2 t2 ON t1.id = t2.table1_id
  JOIN table3 t3 ON t2.id = t3.table2_id
  INTO OUTFILE '/path/to/your_export.csv'
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\n';
  

  Ensure the database server has write permissions to the target path, then you can transfer the file to your application server if needed.
• Use mysqldump with custom queries: If you need more flexibility, export the joined result using mysqldump with a --where clause or subquery. Example:

  mysqldump -u your_username -p your_database table1 table2 table3 --where="table1.id = table2.table1_id AND table2.id = table3.table2_id" > export.sql
  

MariaDB 10.1 has had several optimizer fixes in later patch versions (the final release is 10.1.48). If you’re running an older build, upgrading to the latest patch might resolve performance bugs that affect join queries on large datasets.

Remember to test changes incrementally—adjust one setting or optimization at a time so you can pinpoint exactly what’s improving the runtime.

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