MySQL performance optimization

Performance optimization is done by some effective means to improve the speed of MySQL, reduce the amount of disk space occupied. Performance optimizations include many aspects, such as optimizing query speed, optimizing update speed, and optimizing MySQL servers. This article describes the main methods:

• Optimizing queries
• Optimizing the database structure
• Optimizing MySQL Server

Database administrators can use the show status statement to query the performance of the MySQL database. Syntax: SHOW statue like ' value ', where the value parameter is a few statistical parameters commonly used.

Connections: Number of connections to MySQL server

Uptime:mysql Server on-line time;

Slow_queries: number of slow queries;

Com_select: The number of queries to be done;

Com_insert: Number of insert operations;

Com_delete: Number of delete operations;

Com_update: Number of update operations;

1 Optimizing queries

Query operation is the most frequent operation, improve the query speed can effectively improve the performance of MySQL database.

The first is to analyze the query statement, the command to parse the query statement is the explain statement and the describe statement. For example EXPLAIN SELECT * from student \g;

An index can quickly locate a record in a table. Using indexes can also increase the speed of database queries, thereby improving database performance. If you do not use an index, the query statement will all the fields in the table. This makes the query slow. If an index is used, the query statement queries only the indexed fields. This reduces the number of records in the query, so as to improve query efficiency.

Now look at the usage of a query statement that does not have an index:

SELECT * FROM student WHERE name = ' Zhang San '; This will query all the data in the student table, comparing whether the field of name is Zhang San.

We then set up an index named Index_name on the Name field:

CREATE INDEX index_name on student (name);

Now that the Name field is indexed, the SELECT statement query is very fast and does not need to traverse the entire table.

However, there are times when the index is used even when querying, but the index does not work. For example, when querying with the LIKE keyword, the index will not be used if the first character of the matching string is '% '. If '% ' is not in the first position, the index will be used.

Another scenario is to create an index on multiple fields of a table, such as

CREATE Index index on student (birth,department) so that the index is used only if the field name is used in the query statement condition. Because the Name field is the first field in a multicolumn index, only the name field is used in the query condition to make index indexes work.

2 Optimizing subqueries

Subqueries are required in many queries. Subqueries can make query statements very flexible, but the execution of subqueries is not efficient. MySQL needs to establish a temporary table for the query results of the inner query statement. The outer query statement then queries the records in the staging table. After the query is complete, MySQL needs to pin these temporary tables. So you can use connection queries in MySQL instead of subqueries. Connection queries do not need to establish temporary tables, which are faster than subqueries.

3 Optimizing the database structure

1 decomposing a table with many fields into multiple tables

Some tables have a lot of fields set up at design time. However, some of the fields in this table are used in a very low frequency. When the table has a large amount of data, the query data is very slow. For a table with a particularly high number of fields and with a low frequency of use of the field, it can be decomposed into multiple tables.

2 Adding an intermediate table

There are times when you need to query several fields in a two table frequently. If you frequently make a query, you will reduce the query speed of your MySQL database. For this scenario, an intermediate table can be established to increase the query speed.

Parsing often requires querying those fields in those tables at the same time. These fields are then built into an intermediate table and the data from the original tables are inserted into the intermediate table, which can then be queried and counted using an intermediate table.

3 Adding redundant fields

When designing a database table, try to get the table up to three paradigms. However, sometimes to increase query speed, you can consciously add redundant fields to the table. The higher the specification of the table, the more the relationship between the table and the table may often require a connection query between multiple tables, and a connection query slows the query. For example, the student's information is stored in the student table, the information of the department is stored in the department table, and the Department table is established by the dept_id field in the student table. If you want to query the name of a student's department, you must get the dept_id field from the student table, and then go to the Department table to find the name of the system based on that number. Connecting queries can be a waste of time if you need to do this frequently. Therefore, you can add a redundant field dept_name in the student table so that you do not have to make a connection query every time. In fact, everything is business-oriented.

4 Optimizing the speed of inserting records

When you insert a record, both index and uniqueness checks affect the speed at which the record is inserted. Also, the time it takes to insert multiple records at once and insert a record multiple times is different. According to these conditions, different optimizations are carried out separately.

Disable index: When inserting records, MySQL sorts the inserted records based on the index of the table. If you insert large amounts of data, these sorts can slow down the insertion. To resolve this situation, disable the index before inserting the record. And then enable indexing after inserting. For newly created tables, you can create an index without creating an index, and so on before the records are imported. This can increase the speed at which data is imported.

ALTER table name DISABLE KEYS;

ALTER table name ENABLE KEYS;

Optimizing INSERT statements: When inserting data in large numbers, it is recommended that you insert multiple records with an INSERT statement instead of using multiple INSERT statements. This reduces the connection to the database, among other things.

5 Analysis tables, checklists, and optimization tables

The main function of analytic tables is to analyze the distribution of keywords. The purpose of the checklist is to check the table for errors. The main function of the optimization table is to eliminate the wasted space caused by the deletion or update.

The table ANALYZE table name is parsed, and the database system adds a read-only lock to the table while using ANALYZE table to parse the tables. During table parsing, only the contents of the table can be read, and the contents of the table cannot be inserted and updated. The ANALYZE table statement can parse tables of type InnoDB and MyISAM.

The check table uses a check table statement. A read-only lock is also added to the table during execution.

The refinement table uses the Optimize table statement. Only fields of type varchar,blob,text in the table can be optimized. The OPTIMIZE table statement eliminates disk fragmentation caused by deletions and updates, thereby reducing wasted space. Because if a table uses a data type such as text or a blob, updating, deleting, and so on will result in wasted disk space. Because, after the update and delete operations, previously allocated disk space is not automatically reclaimed. Use the Optimize Table statement to defragment these disks for reuse.

4 Optimizing MySQL Server

Hardware optimization: Increase memory and improve disk read and write speed, can improve the MySQL database query, update speed. Another way to improve MySQL performance is to use multiple disks to store data. Because data can be read in parallel from multiple disks, this can increase the speed at which data is read.

MySQL parameter optimization: Memory will be reserved for MySQL part of the buffer. These buffers can increase the speed of MySQL. The size of the buffer is set in the MySQL configuration file.

Several important parameters are described in detail below:

• Key_buffer_size: Represents the size of the index cache. The higher the value, the faster the query is made using an index
• Table_cache: Indicates the number of tables that are open at the same time. The larger the value, the more tables can be opened at the same time. This value is not as large as possible, because too many tables open at the same time can affect the performance of the operating system.
• Query_cache_size: Indicates the size of the query buffer. Query buffers can be used to increase the speed of queries. This method only uses a situation that is less than the modification operation and frequently performs the same query operation; The default value is 0.
• Query_cache_type: Indicates the open state of the query buffer. 0 indicates off, 1 means open.
• Max_connections: Represents the maximum number of connections for the database. The greater the number of connections, the better, because connections can waste memory resources.
• Sort_buffer_size: The size of the sort buffer, the larger the value, the faster the sort.
• Innodb_buffer_pool_size: Represents the maximum cache for tables and indexes of type Innodb. The higher the value, the faster the query will be. This value is too large to affect the performance of the operating system.

Reasonable configuration of these parameters can improve the performance of MySQL database. After configuring the parameters, the MySQL service needs to be restarted before it takes effect.

MySQL performance optimization