MySQL usage and optimization summary _ MySQL

MySQL Optimization This article summarizes some common MySQL problems and solutions used in the work; suitable use cases and optimization solutions. Storage Engine selection: MyISAMvsInnoDBMyISAM: full-text indexing is supported, table-level locks are used, and read concurrency performance is good. InnoDB: supports transactions and foreign keys, row-level locks, and good write concurrency. In practical application scenarios, we generally use InnoDB as the default storage engine, except for MySQL optimization.

This article summarizes some of the common problems and solutions that MySQL is used in its work; suitable use cases and optimization solutions.

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 optimize 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.
• Use union to replace or and in operations. the next two do not go through the index.
• 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.

Simple troubleshooting skills, slow query, and troubleshooting

• Log View: slow. log, which is set in the mysql configuration file and must be enabled.

• View in real time: select * from information_schema.processlist where time> 2;

  Processing method:

  • Slow query log analysis tools: mysqlsla, mysqldumpslow, and so on, to optimize SQL;
  • If real-time slow query affects the application response, you can kill the query thread directly. Run kill [thread_id.

Lock troubleshooting

Check whether the database is locked. in processlist, you can see that the state column has a lock-related status. Only one status can be seen here, you can use the following command to view the show innodb engine status. the detailed lock and transaction information are displayed. How to solve the problem depends on how the application side controls it.

Troubleshoot Slave latency

Run show slave status on the slave instance to check the status of the slave, mainly focusing on the following three:

Slave_IO_Running: Yes // whether the thread responsible for reading the binlog runs properly. Slave_ SQL _Running: Yes // whether the thread responsible for executing the SQL statement on slave runs properly Seconds_Behind_Master: 0 // How long is the slave latency longer than the master, in seconds

If the status of the IO and SQL Threads is No, the slave synchronization has stopped. you can see the latest error through Last_Error. To restore a server load balancer instance, you can perform the following two operations: redo server load balancer to ensure data accuracy; skip the SQL statements with errors and stop server load balancer; set global SQL _slave_skip_counter = 1; start slave;, this is to skip an SQL statement, you can also skip too many lines, this method may cause slave data inconsistency.

Built-in Monitoring commands

statusshow global statusshow variables

External monitoring

Third-party monitoring tools provide graphical interfaces. Cacti, ganglia, and other open-source software provide plug-ins for monitoring mysql.

Brief introduction to mysql High Availability

Two methods:

Method 1: use MySQL Cluster: the read scalability is good, and the write performance will decrease. Not very mature, use it online with caution.

Method 2: Master + Slave combined with virtual IP + LVS + keepalived for simple high availability. the hidden danger of this solution is that the gap between virtual ip addresses will be unavailable for a short time; upgrading slave to master may fail;

Last

Databases generally store key application data, which can be said to be the life of a company's products. Therefore, data security is also very important. permission control (strict permission control, avoid data loss due to misoperations as much as possible, and back up data in a timely manner (remote, multi-Data Center). keep the core sensitive data confidential.