MySQL性能调优的10个技巧（英文版）

MySQL Summit: Configuration, Best Practices, and Tracking

Importing Data

• Use MySQL Shell Utility: Prefer mysqlsh for importing data, especially for logical dumps. It supports parallel processing and can handle full instances, one or multiple schemas, and specific tables.
• Speed Up Imports: Utilize util.dumpInstance() and util.loadDump() for high-speed imports. These methods support parallel processing and can be optimized for speed by adjusting thread counts.
• Optimize for Speed: During an initial load, disable binary logs, redo logs, and adjust InnoDB settings to reduce durability requirements. This can significantly speed up the process.

Schema Design

• Primary Keys:

  • InnoDB Requirement: A Primary Key is mandatory for InnoDB tables. A well-structured primary key improves performance.
  • Sequential vs. Non-Sequential: Use a sequential auto-increment primary key (INT) to minimize page access and reduce I/O operations. Non-sequential keys can lead to increased random I/O.
  • Hidden Primary Key: If no primary key is defined, InnoDB creates a hidden primary key which can cause contention issues and is unsupported in HA scenarios. Use GEN_CLUST_INDEX to identify tables without primary keys.
  • GIPK Mode: Since MySQL 8.0.30, InnoDB supports generated invisible primary keys (GIPK) in GIPK mode, adding a primary key automatically with the column name my_row_id.

• Indexes:

  • Unused Indexes: Identify and drop unused indexes using sys.schemas and innodb_index_stats. This reduces unnecessary I/O operations.
  • Duplicate Indexes: Remove duplicate indexes to avoid redundant maintenance. Check for and drop redundant indexes using sys.schemas and innodb_index_stats.

Monitoring

• ALTER Statements Progress: Monitor the progress of ALTER statements using SQL commands to track work completed and estimated work.

This summary covers key aspects of MySQL configuration, best practices for importing data, schema design considerations, and monitoring techniques.