MySQL Performance Optimization

What is performance optimization

• Optimizing the profile

The so-called MySQL performance optimization, on the one hand, means to make MySQL run faster and save more resources by adjusting system parameters and rationally arranging resources. On the other hand, it also means to optimize the SQL statements we commonly use, especially the query statements, to improve the performance of MySQL.

• The basic principle of

The basic principles of MySQL performance optimization are as follows:

1. Reduce system bottlenecks;
2. Reduce resource occupation;
3. Improve system response speed.

• Commonly used method

MySQL performance optimization usually starts from the following aspects:

1. Find out the system bottleneck, improve the overall performance of MySQL database;
2. Reasonable structure design and parameter adjustment, improve the corresponding speed of database operation;
3. Minimize system resources so that the system can provide a greater load of services.

Such as:

1. Optimize the file system to improve the disk I/O write speed.
2. By optimizing the scheduling strategy of the operating system, the load capacity of MySQL in the case of high load can be improved.
3. By optimizing table structure, indexes, query statements and so on, the query response is faster.

View MySQL performance parameters

In MySQL, you can use the SHOW STATUS statement to check the performance parameters of the MySQL database. Based on these performance parameters, you can learn about the STATUS of the MySQL database and develop reasonable optimization strategies.

Perform show status; To view all performance parameters, run show status like ‘Parameter name’. You can view performance parameters of a specified parameter name. Generally, a certain type of parameter has the same prefix.

Here is show status; The statement returns 356 results, most of which are omitted here:

mysql> show status; +-----------------------------------------------+--------------------------------------------------+ | Variable_name | Value | +-----------------------------------------------+--------------------------------------------------+ | Aborted_clients | 0 | | Aborted_connects | 2 | Bytes_received | 256 | | Bytes_sent | 185 | | * * part omitted here... ** | | Threads_running | 1 | | Uptime | 1038207 | | Uptime_since_flush_status | 1038207 | +-----------------------------------------------+--------------------------------------------------+ 356 rows in set (0.00 SEC)Copy the code

The following is a description of some of the performance parameters. For details, please visit Github open source Blog: github.com/ijiangtao/b…

The state name	scope	Explain in detail
Aborted_clients	Global	The number of connections that were terminated because the client did not close the connection properly
Aborted_connects	Global	The number of failed attempts to connect to the MySQL server
Handler_read_prev	Both	Number of requests to read the previous row in key order. ORDER BY… ORDER BY… DESC.
Handler_read_rnd	Both	The number of requests to read a line in a fixed position. This value is higher if you are executing a large number of queries and need to sort the results. You may be using a lot of queries that require MySQL to scan the entire table or your join is not using keys correctly.
Handler_read_rnd_next	Both	Number of requests to read the next line in the data file. This value is higher if you are doing a lot of table scans. This usually means that your table index is incorrect or that a written query does not utilize the index.
Handler_rollback	Both	Number of internal ROLLBACK statements.
Handler_savepoint	Both	The number of requests to place a savepoint in a storage engine.
Handler_savepoint_rollback	Both	A storage engine requires a rollback to a number of savepoints.
Handler_update	Both	The number of requests to update a row in the table.
Handler_write	Both	The number of requests to insert a row into the table.
Last_query_cost	Session	The total cost of the last compiled query calculated with the query optimizer. Used to compare the cost of different query schemes for the same query. The default value of 0 indicates that the query has not been compiled. The default value is 0. Last_query_cost has the range of sessions.
Max_used_connections	Global	The maximum number of concurrent connections that have been in use since the server started.
Open_tables	Both	The number of currently open tables.
Qcache_free_memory	Global	The amount of free memory used to query the cache.
Qcache_hits	Global	Query the number of times the cache is accessed.
Qcache_inserts	Global	The number of queries added to the cache.
Qcache_total_blocks	Global	Query the total number of blocks in the cache.
Queries	Both	The number of requests executed by the server, including requests in stored procedures.
Questions	Both	Number of queries that have been sent to the server.
Select_full_join	Both	The number of joins that do not use indexes. If the value is not 0, you should check the index of the table carefully
Select_full_range_join	Both	The number of joins that use range searching in the referenced tables.
Select_range	Both	The number of joins of ranges used in the first table. The general case is not the key issue, even if the value is quite large.
Select_range_check	Both	The number of keyless joins checked for key values after each row of data. If not, you should check the index of the table carefully.
Select_scan	Both	The number of joins that were fully scanned on the first table.
Slave_running	Global	If the server is a secondary server connected to the primary server, the value is ON.
Slow_queries	Both	Number of queries whose query time exceeds long_query_time seconds.
Sort_range	Both	The number of sorts performed within the scope.
Sort_rows	Both	The number of rows that have been sorted.
Sort_scan	Both	The number of sorts done by scanning tables.
Table_locks_immediate	Global	The number of times the table lock was immediately acquired.
Table_locks_waited	Global	The number of times a table lock was not immediately available. If the value is high and you have performance issues, you should first tune the query and then split the table or use replication.
Threads_cached	Global	The number of threads in the thread cache.
Threads_connected	Global	The number of connections currently open.
Threads_created	Global	The number of threads created to process connections. If Threads_created is large, you may want to increase the thread_cache_size value. The cache hit rate is calculated using Threads_created/Connections.
Threads_running	Global	Number of active (non-sleeping) threads.
Uptime	Global	The time, in seconds, that the server has been running.

MySQL query optimization

Query is the most frequent operation in the database. Optimization of query can effectively improve the performance of MySQL database.

Parse query statement

We can use the explain or describe keyword to analyze the query, with the following syntax:

1. explain [extended] SELECT SelectOptions
2. describe [extended] SELECT SelectOptions
3. desc [extended] SELECT SelectOptions

mysql> explain extended SELECT id, name, ip_address, `action`, create_time FROM t_user_action_log where id=11; +----+-------------+-------------------+------------+-------+---------------+---------+---------+-------+------+-------- --+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------------+------------+-------+---------------+---------+---------+-------+------+-------- - + -- -- -- -- -- -- -- + | | 1 SIMPLE | t_user_action_log | NULL | const | PRIMARY | PRIMARY 8 | | const | 1 | | NULL | 100.00 +----+-------------+-------------------+------------+-------+---------------+---------+---------+-------+------+-------- --+-------+ 1 row in set, 2 warnings (0.00 SEC) mysql> describe extended SELECT ID, name, ip_address, 'action' create_time FROM t_user_action_log where id=11; +----+-------------+-------------------+------------+-------+---------------+---------+---------+-------+------+-------- --+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------------+------------+-------+---------------+---------+---------+-------+------+-------- - + -- -- -- -- -- -- -- + | | 1 SIMPLE | t_user_action_log | NULL | const | PRIMARY | PRIMARY 8 | | const | 1 | | NULL | 100.00 +----+-------------+-------------------+------------+-------+---------------+---------+---------+-------+------+-------- --+-------+ 1 row in set, 2 warnings (0.00 SEC)Copy the code

1. Id: ID is the serial number of the select identifier.
2. Select_type: select_type specifies the type of the query statement. For example,SIMPLERepresents a simple query that excludes join queries and subqueries.
3. Table: table indicates the queried table.
4. Type: indicates the connection type
5. Possible_keys: possible index.
6. Rows: indicates the number of rows to be checked for query.
7. “Extra” : detailed query information.

Use of indexes

The advantages and considerations of using indexes were explained in detail in MySQL Index and query optimization. A few more caveats here.

1. Like queries use indexes

If the first character of the matching string is % in a like statement, the index does not move until % is not in the first position.

Therefore, it is recommended to use a uniform prefix rather than suffix for strings in MySQL.

mysql> explain SELECT id, ip_address FROM t_user_action_log where ip_address like '7%'; +----+-------------+-------------------+------------+-------+----------------+----------------+---------+------+------+- ---------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------------+------------+-------+----------------+----------------+---------+------+------+- ---------+-------------+ | 1 | SIMPLE | t_user_action_log | NULL | range | ip_address_idx | ip_address_idx | 51 | NULL | 2 | | 100.00 Using the where | +----+-------------+-------------------+------------+-------+----------------+----------------+---------+------+------+- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set, 1 warning (0.00 SEC) mysql > explain the SELECT id, ip_address FROM t_user_action_log where ip_address like '%0'; +----+-------------+-------------------+------------+------+---------------+------+---------+------+------+----------+-- -----------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------------+------------+------+---------------+------+---------+------+------+----------+-- -- -- -- -- -- -- -- -- -- -- -- + | | 1 SIMPLE | t_user_action_log | NULL | | NULL | NULL | NULL ALL 41 | | NULL | | 11.11 Using the where | +----+-------------+-------------------+------------+------+---------------+------+---------+------+------+----------+-- -----------+ 1 row in set, 1 Warning (0.00 SEC)Copy the code

2. Or keyword query statement

If the OR keyword is used in the query condition, the index takes effect only when indexes are added to the conditions before and after the keyword is used.

3. Optimized subquery

MySQL implements nested queries through subqueries, that is, the results of one query can be used as criteria for another query. Subqueries enable a SINGLE SQL statement to implement SQL conditions that logically require multiple steps to complete.

Subqueries are flexible, but not efficient. The reason is that the subquery needs to create a temporary table for the inner query, and then the outer query queries the results from the temporary table. After the query is complete, undo the temporary table.

In MySQL, JOIN queries can be used instead of subqueries. Join queries do not require temporary tables and are more efficient if indexes are used in the query.

Optimize the database structure

An excellent database structure design, not only can let the database occupy less disk space, but also can make the query faster.

The design of database structure should consider:

• Data redundancy
• Query and update speed
• The data type of the field
• And so on…

The following provides several optimization strategies for database structure design.

Split a table with many fields into multiple tables

A table with many fields, many of which are used infrequently, because these infrequently used fields will slow down the database query and update speed, so you can consider separating these fields to form a new table.

For example, a commodity table can separate the related attributes and classification information that are not commonly used into other tables. In this way, when querying the main information of the product, only the necessary fields need to be queried. If you need detailed information about an item, you can use a join query to return the details of the item together.

Add intermediate tables

Usually we design a table based on some sort of logical and business isolation principle. For example, the user table contains the user’s information, the order table is the user’s transaction information, the commodity table is the commodity information, the promotion table is the commodity can participate in the discount and preferential activities information.

However, sometimes we need to connect the information of some tables to query frequently. At this time, in order to improve the query efficiency, we can form a new intermediate table for these fields that often need to be connected to query. Each query only needs to check one table, so as to improve the query efficiency.

However, it is important to note that when the primary table is updated, the intermediate table must be updated at the same time, i.e. both actions must be in one transaction.

Add redundant fields

When designing database tables, we should try to follow the rules of paradigm theory, reduce redundant fields as much as possible, and make the database design more simplified.

However, reasonable increase of redundant fields can improve the query speed.

For example, the higher the degree of normalization of a table is, the more cases of connecting queries between tables are required. Therefore, adding redundant fields reasonably can reduce the connection queries and improve the query speed.

As with adding intermediate tables, redundant fields are used to reduce join queries, as well as database record consistency issues.

Optimize record insertion speed

When inserting data, index, uniqueness check, number of inserts and other factors affect the inserting speed.

Based on these dimensions, several optimization strategies are provided below.

MyISAM engine table

1. Disable index

When inserting data into a non-empty table of MyISAM engine, MySQL will create an index for the inserted data according to the index of the table. If a large amount of data is inserted, the insertion efficiency will be reduced.

Therefore, you can close the index before the record is inserted and build the index after the insertion.

• Disable indexes:

alter table t_user_action_log_oneline disable keys;
Copy the code

• Open index:

alter table t_user_action_log_oneline enable keys;
Copy the code

2. Disable uniqueness checking

When inserting data, MySQL performs a uniqueness check to slow down the speed of inserting data. Therefore, you can turn off the uniqueness check before inserting data and turn on the uniqueness check after inserting data.

• Disable uniqueness checking:

set unique_checks=0;
Copy the code

• Turn on the uniqueness check:

set unique_checks=1;
Copy the code

3. Using batch inserts

Using one INSERT statement to insert multiple records is more efficient than using multiple insert statements to insert multiple records.

4. Use load Data Infile

Load Data Infile can import data more efficiently.

Raw data can be written manually or exported using the outfile statement. However, the following errors may occur when using the outfile statement:

mysql> select * from t_user_action_log into outfile "/data/mysql/t_user_action_log.sql";
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
Copy the code

Secure file-priv = secure file-priv

mysql> show global variables like '%secure%'; +--------------------------+------------------------------------------------+ | Variable_name | Value | +--------------------------+------------------------------------------------+ | require_secure_transport | OFF | | Check secure_auth | | | secure_file_priv | C: \ ProgramData/MySQL/MySQL Server 5.7 \ Uploads \ | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 3 rows in the set, 1 warning (0.01 SEC)Copy the code

Specifies that the exported data is separated by commas:

select * from t_user_action_log into outfile "C: / ProgramData/MySQL/MySQL Server 5.7 / Uploads/t_user_action_log SQL" fields terminated by ', ';
Copy the code

The contents of the t_user_action_log. SQL file are as follows:

50, LiSi, 9.8.8.2, 51, 0201-06-30 20:46:21 LiSi, 7.8.8.2, 5201-06-30 11:56:43Copy the code

Load Data Infile to insert data in batches:

load data infile "C: / ProgramData/MySQL/MySQL Server 5.7 / Uploads/t_user_action_log SQL" into table t_user_action_log fields terminated by ', ';
Copy the code

InnoDB engine tables

1. Disable uniqueness checking

This is the same as the MyISAM engine list.

2. Disable foreign key check

Turn off foreign key checking before inserting:

set foreign_key_checks=0;
Copy the code

Enable foreign key check after insertion:

set foreign_key_checks=1;
Copy the code

3. Disabling automatic submission

Turn off auto commit before inserting:

set autocommit=0;
Copy the code

Enable auto commit after insert:

set autocommit=1;
Copy the code

Analyze, review and optimize tables

1. Analysis of the table

MySQL > select * from TABLE 1; ANALYZE TABLE 2; ;

When you use the ANALYZE TABLE to ANALYZE a TABLE, the database system places a read-only lock on the TABLE. During analysis, only records in the table can be read, not updated or inserted.

The ANALYZE TABLE statement analyzes tables of InnoDB and MyISAM type.

mysql> analyze table t_user_action_log; +--------------------------------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------------------------------------+---------+----------+----------+ | ijiangtao_local_db_mysql.t_user_action_log | analyze | status | OK | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.02 SEC)Copy the code

• Table: indicates the name of a Table.
• Op: indicates the operation to be performed. Analyze indicates that the analyze operation is performed. Check indicates checking and searching. Optimize means to optimize the operation;
• Msg_type: Indicates the information type. The value displayed is usually one of status, warning, error, and information.
• Msg_text: displays information.

2. Check the table

MySQL uses the CHECK TABLE statement to CHECK tables. The CHECK TABLE statement checks for errors in tables of InnoDB and MyISAM type. It also checks for errors in the view.

CHECK TABLE name 1 [, TABLE name 2…] [option] ;

The option parameter has five parameters, which are QUICK, FAST, CHANGED, MEDIUM, and EXTENDED. The execution efficiency of these five parameters decreases in turn.

The option option is only valid for MyISAM tables, not InnoDB tables. The CHECK TABLE statement also assigns a read-only lock to the TABLE during execution.

mysql> check table t_user_action_log; +--------------------------------------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------------------------------------+-------+----------+----------+ | ijiangtao_local_db_mysql.t_user_action_log | check | status | OK | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.01 SEC)Copy the code

3. Optimize the table

MySQL uses the OPTIMIZE TABLE statement to OPTIMIZE tables. This statement is valid for both InnoDB and MyISAM tables. However, the OPTILMIZE TABLE statement can only optimize VARCHAR, BLOB, or TEXT fields in the TABLE.

OPTILMIZE TABLE syntax: OPTIMIZE TABLE name 1 [, TABLE name 2…] ;

The OPTIMIZE TABLE statement eliminates disk fragmentation caused by deletions and updates, reducing space waste. The OPTIMIZE TABLE statement also locks the TABLE during execution.

If a table uses data types such as TEXT or BLOB, updates, deletes, and so on waste disk space. The disk space allocated is not automatically reclaimed after the update or delete operation. The OPTIMIZE TABLE statement allows you to shard these disks for later reuse.

TABLE optimization can be done in many ways: OPTIMIZE TABLE statement, mysqlCheck tool (server to run) or Myisamchk (server not running or TABLE interaction)

With the use of MySQL, tables containing BLOB and VARCHAR bytes become more verbose because these fields are of different lengths and take up different amounts of space when records are inserted, updated, or deleted, shredding records and leaving free space. Disk fragmentation, for example, degrades performance and requires defragmenting, so optimize.

mysql> optimize table t_user_action_log; +--------------------------------------------+----------+----------+---------------------------------------------------- ---------------+ | Table | Op | Msg_type | Msg_text | +--------------------------------------------+----------+----------+---------------------------------------------------- ---------------+ | ijiangtao_local_db_mysql.t_user_action_log | optimize | note | Table does not support optimize, doing recreate + analyze instead | | ijiangtao_local_db_mysql.t_user_action_log | optimize | status | OK | +--------------------------------------------+----------+----------+---------------------------------------------------- ---------------+ 2 rows in set (0.11 SEC)Copy the code

MySQL server optimization

To optimize MySQL server performance, consider the following aspects:

1. Configure a large enough memory
2. Configure a high disk system to reduce disk read latency
3. Allocate disk I/O on multiple devices to reduce resource competition and improve parallel operation capability.
4. MySQL is a multithreaded database. Multiple processors can handle multiple threads at the same time.
5. Optimize MySQL parameters, such as the size of the index buffer with the key_buffer_size parameter.

conclusion

This paper introduces the optimization scheme using MySQL database from many aspects, but the specific practice also needs to have an in-depth understanding of the advantages and disadvantages of each optimization scheme, and combined with the specific situation analysis, so as to design the optimal scheme suitable for their own.

Related articles

• Bit operation and SQL implementation
• MySQL index and query optimization
• Install the MySQL decompressed version in Windows
• MySQL primary key Auto Increment usage
• Introduction to MySQL Database storage engine

---