Location>code7788 >text

A thorough understanding of MySQL optimization in one article

Popularity：998 ℃/2024-10-24 14:39:58

In enterprise Web development, theMySQL OptimizationIt is crucial, which directly affects the responsiveness, scalability and overall performance of the system. Below is a list of detailed MySQL optimization tips from different perspectives, covering query optimization, index design, table structure design, configuration tuning, and so on.

I. Query optimization

1. Rationalizing the use of indexes

  • single-column index: for frequently queried fields (e.g.WHERE、ORDER BY、GROUP BY fields) in the
  • combinatorial index: For queries involving multi-column conditions, it is recommended to use combined indexes. Note the order of the combined index (leftmost prefix matching principle).
  • Coverage Index: Ensure that the query's fields are all covered by the index so that MySQL can get the data directly from the index without accessing the table data.
  • Avoid over-indexing: Too many indexes can increase the overhead of write operations, such asINSERT、UPDATE cap (a poem)DELETE operation, as the index is maintained each time.

2. Optimizing Query Statements

  • Avoid usingSELECT \*: Explicitly select the required fields to avoid redundant field lookups and reduce the amount of data transfer.
  • Avoidance ofWHERE Functional manipulation of fields in conditionsAsWHERE YEAR(date_column) = 2023, this operation will invalidate the index, change toWHERE date_column >= '2023-01-01' AND date_column < '2024-01-01'。
  • Avoidance ofWHERE Use in conditionsOR：OR will result in a full table scan, try to use theIN Or break down the query.
  • Minimize subqueries: UseJOIN Alternative to subqueries. Subqueries will be executed frequently when nested, and may result in rescanning the table each time.
  • rational useJOIN: If there is a multi-table correlation query, make sure that the correlated fields are indexed and that the table join order is optimized (small tables driving large tables).

3. Paged Query Optimization

  • Big Data Paging: For paging queries with very large amounts of data, it is possible to avoidLIMIT offset way, but instead locate the starting position by index, e.g.WHERE id > last_seen_id LIMIT 10。
  • Reduce the amount of data scanning: When paging don'tSELECT *If you select only the primary key field to return the result, then you can query the details based on the primary key.

4. Proper use of temporary tables and caching

  • complex query: For complex queries, they can be queried and stored in a temporary table before further query operations to reduce repeated calculations.
  • caching mechanism: Cache frequently accessed data at the application or database tier (e.g., using Redis, Memcached) to avoid querying the database every time.

5. Avoid deadlocks and lock waits

  • Reduced lock range: Try to keep locks as small as possible (e.g., lock only necessary rows) to avoid table locks.
  • Reduction of transaction execution time: The longer the transaction, the longer it takes to lock up resources, which can easily lead to lock waiting or even deadlocks. Minimize the time of query or update operations in a transaction.

II. Index Optimization

1. Proper Use of Primary Keys and Unique Indexes

  • primary key index: Choose unique and invariant fields as primary keys, try to use self-incrementing integer primary keys, and avoid using long string primary keys.
  • unique index: Create unique indexes on fields that do not allow duplicate values (e.g., username, email, etc.) to avoid duplicate data insertion.

2. Coverage Index

  • Reducing table return operations: When the query involves fields that are all in the index, MySQL can return the results directly from the index, avoiding the need to go back to the table.

3. prefix index

  • Indexing of long string fields: When indexing fields of long string types such as VARCHAR, you can use prefix indexes (e.g.CREATE INDEX idx_name ON users(name(10))) to save index space by truncating the first few characters.

4. Avoid redundant indexes

  • Avoid Duplicate Indexes: For example, there are already(a, b) When combining indexes, there is no need to give a separatea Indexing.
  • Index Maintenance: Periodically check for useless indexes (usingSHOW INDEX FROM table_name) and deleted, reducing the overhead of index maintenance.

III. Table structure design optimization

1. Proper table field design

  • Data type selection: Select the smallest and sufficient field type. For exampleINT(11) Occupies 4 bytes, if the range of values is small, you can use theTINYINT(1 byte),SMALLINT(2 bytes) to save space.
  • utilizationVARCHAR speciousCHAR：CHAR is fixed-length, and storing fixed-length characters would be a waste of space, while theVARCHAR is variable length, suitable for storing strings of indeterminate length.
  • Avoiding BLOB and TEXT types: Large fields can cause performance problems, try to keep large files or data in the file system and store only the file paths in the database.

2. table partitioning

  • horizontal scale: When the amount of table data is too large (e.g. hundreds of millions of records), the table can be split horizontally, e.g. by time, user ID, etc., to reduce the size of a single table.
  • partition table: MySQL provides a table partitioning feature that can optimize large table query performance by dividing data into different physical partitions based on the data range.

3. Table normalization and counter-normalization

  • table normalization: Separate data into multiple tables to avoid data redundancy. Paradigm design is easier to maintain when the amount of data is small.
  • anti-normalization: When query performance becomes a bottleneck, consider anti-normalization and add redundant fields to reduce the number of related queries to the table.

IV. Transactions and locking mechanism optimization

1. Reduced lock competition

  • Line Lock Priority: Try to avoid table locks with larger lock ranges. MySQL's InnoDB engine supports row locks to ensure concurrency.
  • Submitted in batches: When operating on data in bulk, you can split the operation into multiple small batch commits to minimize holding locks for long periods of time.

2. Rationalizing the use of transactions

  • Minimizing transaction time: Transactions should be as short as possible to avoid holding locks for long periods of time and causing resources to be waited on by other transactions.
  • Transaction Isolation Level Selection: Select the appropriate isolation level based on business requirements, higher isolation levels such asSERIALIZABLE There will be more locking overhead, and the commonly usedREPEATABLE READ。

3. Using Optimistic Locks

  • application layer optimistic locking: For concurrent update business scenarios, version number control (optimistic locking) can be used at the application layer to avoid lock conflicts.

V. Configuration optimization

1. Tuning the InnoDB Buffer Pool

  • Buffer Pool Size: InnoDB's Buffer Pool is used to cache data and indexes. Configuring a reasonable buffer size is one of the keys to optimizing MySQL performance. It is recommended to set the Buffer Pool to 70-80% of the physical memory.

    innodb_buffer_pool_size = 4G # Scaled to memory size

2. Query Cache

  • Turning off query caching: In MySQL 5.7 and later, the query caching feature is gradually being deprecated because it tends to become a bottleneck in high concurrency scenarios. Therefore, it is recommended to turn it off.

    query_cache_type = 0

3. Thread Pool Optimization

  • Adjustment of connection threads: For highly concurrent business scenarios, you can adjust the maximum number of MySQL connections (max_connections) and the maximum number of threads per connection.

    max_connections = 500

4. Disk I/O Optimization

  • Adjust innodb_flush_log_at_trx_commit：innodb_flush_log_at_trx_commit Controls when logs are written to disk. Setting the2 When you do, you can reduce disk I/O and improve performance, but you slightly increase the risk of data loss.

    innodb_flush_log_at_trx_commit = 2

5. Adjusting log file size

  • Setting the appropriate redo log size：innodb_log_file_size Configure the redo log file size. It is recommended that you set the appropriate size according to the frequency of write operations and disk conditions. A redo log that is too small will trigger frequent checkpoints and affect performance.

    innodb_log_file_size = 512M

6. Adjusting the connection timeout

  • Avoid invalid connections for long periods of time: You can set the MySQL connection timeout parameter to prevent connections from sitting idle for long periods of time, resulting in wasted resources.

    wait_timeout = 600
interactive_timeout = 600

VI. Monitoring and Tuning

1. utilizationEXPLAIN Analyze the query

  • EXPLAIN Analysis of the implementation plan: ByEXPLAIN The command analyzes the execution plan of the query, checks whether indexes are used, the number of rows scanned, and so on, and optimizes the SQL query.

    EXPLAIN SELECT * FROM users WHERE name = 'Alice';

2. Slow Query Log

  • Turn on slow query logging: The slow query log allows you to monitor which queries are taking too long to execute and helps locate performance bottlenecks.

    slow_query_log = 1
long_query_time = 2 # set to log queries longer than 2 seconds to the log

3. Database Performance Monitoring

  • MySQL Enterprise Monitor or other monitoring tools: Use monitoring tools to track overall database performance metrics, such as CPU, I/O, memory usage, query response times, lock waits, etc., to make it easier to identify problems in a timely manner.

VII. Summary

Performance optimization of MySQL needs to be considered on multiple levels:Query Optimization、Indexing、Table Structure Design、transaction control、Configuration tuningetc. In enterprise web development, optimization needs vary in different business scenarios, and it is often necessary to make appropriate trade-offs with the actual needs of the business. Through continuous monitoring and tuning, MySQL database can maintain efficient and stable performance in high concurrency and large data volume scenarios.

It was too late to embrace the morning and already had dusk in hand. Once I struggled to find this answer, now I have been working for 8 years and am a 30 year old programmer. Time passes and white horses pass, leaving the answer to my own self eight years ago.

<Previous Next>