MySQL usage and optimization Summary

Abstract: This article summarizes some common MySQL problems, solutions, and suitable use cases and optimization solutions.

Directory:

Storage engine Selection: MyISAM vs InnoDB
Use and Optimization
Database Optimization
SQL Optimization
Application Optimization
Simple troubleshooting skills
Slow query troubleshooting
Lock troubleshooting
Troubleshoot Slave latency
Monitoring
Built-in commands
External monitoring
Brief Introduction to mysql High Availability
Last

Storage engine Selection: MyISAM vs InnoDB

MyISAM: supports full-text indexing, table-level locks, and good read concurrency.

InnoDB: supports transactions and Foreign keys, row-level locks, and good write concurrency.

In actual application scenarios, we generally use InnoDB as the default storage engine. Apart from the two reasons that support transactions and row locks, in fact, MyISAM is of little significance in actual application scenarios. Let's take a look at the following reasons:

• Full-text indexing can (or should) be replaced by third-party software, such as sphindexing;

• The features of high read performance can be completely replaced by the front-end cache, which is already the standard for Internet applications;

• When many concurrent write operations are performed on table-level locks, the read operation is seriously affected (write first );

 

Use and Optimization

 

Database Optimization

• Create an appropriate index:

  Try to index all the queries. This effect is obvious.

• Tablespace optimization:

  For tables that are frequently deleted or updated, if fields such as varchar and text are included, You need to regularly optimize the tablespace, optimaize table xxx, and sort disk fragments, reclaim the free space occupied by table data and index data;

• Configuration Parameter Optimization:

Innodb_buffer_pool_size the memory buffer size of innodb table data and index data, which is critical and can effectively reduce disk IO.
Innodb_flush_log_at_trx_commit determines how transaction logs are recorded. This is also critical to performance improvement. You can set it to 0 when writing data in batches online. you can also consider situations where write operations are frequent but a very small amount of data is lost when a fault is allowed.
The query_cache parameter is a bit subtle, because the query cache will expire when there is any data modification in the data table. For tables with frequent write operations, it may also reduce performance. For read-only tables, the effect is quite obvious, but we usually rely on the front-end cache in general scenarios. Therefore, the setting of this parameter depends on specific business scenarios.
Max_connections controls the number of concurrent connections, which cannot be too large. Otherwise, the consequences are very serious.

Splitting and resizing:

Database sharding: Generally, databases on the same instance are allocated to multiple instances to share the pressure (this is relatively simple. Just copy the database and change the ip address of the application ), or, you can place some tables in one database separately in another instance database (this is troublesome and requires the application end to modify the program together ).
Table sharding: there are two types. One is to split some fields into a new table, for example, by business, or by large fields such as text. The other is that the number of table records is too large, which exceeds the capacity of a single table and needs to be horizontally expanded to multiple tables. Table splitting is troublesome and requires the application to modify the program.

SQL Optimization

• Try to use the index. It is best to use the primary key for query.
• Minimize the scanning scope. The typical scenario is the optimization of the limit paging offset. In fact, there are many similar scenarios in actual business scenarios. We can significantly narrow the scanning scope by ID or time limit.
• Minimize the number of table connection queries, preferably single table queries (temporary tables may be used for table connections, which consumes a lot of DB; while single table queries can be quickly returned, put computing operations on front-end applications to reduce DB pressure ). If the frontend concurrency is not well controlled, tables with poor performance may be dragged to the database.
• Perform equivalent queries as much as possible. unequal condition queries and reverse queries do not go through the index.
• Replace or and in operations with union
• No pre-fuzzy query, no index
• Sort and group operations on the application end as much as possible to reduce the CPU pressure on the DB
• No function operation is performed on the query column: select concat ('foo', 'bar') as str from xxx;

 

Application Optimization

• Do not perform operations on the database as far as possible, and do not rely on the database for what can be done on the application.
• Data such as text/blob should not be stored in DB as much as possible. Other key/value types can be used for storage.
• Split large SQL statements into small SQL queries without table join
• Make good use of the connection pool to reduce connection overhead (pay attention to the idle time of the connection pool and the configuration of the idle time of the database)
• Use hot and cold data to relieve pressure as much as possible
• Read/write splitting (pay attention to the slave latency. When the master node writes frequently, the slave latency is also a headache, and there is a hidden risk in application scenarios that are sensitive to data consistency)
• Finally, preparedstatement is used to prevent SQL injection. The pre-compilation function can also be used, but it is better to enable the front-end SQL cache. It depends on the specific application scenarios. Most internet applications have not yet relied on this function to improve performance.

For more details, please continue to read the highlights on the next page:

MySQL optimization Case Analysis

MySQL optimization: configurable WAIT_FOR_READ

MySQL Optimization in CentOS

1 2 Next Page