mysql5.6 Performance Optimization

1. Target

L Understand what is optimized

L MASTER the method of optimizing query

L MASTER the method of optimizing database structure

L MASTER the method of optimizing MySQL server

2. What is optimization?

L Arrange resources rationally and adjust system parameters to make MySQL run faster and save more resources.

L optimization is multi-faceted, including query, update, server and so on.

L Principle: Reduce the system bottleneck, reduce the resource consumption, increase the system response speed.

3. Database Performance Parameters

l Use the Show status statement to view the performance parameters of the MySQL database

• SHOW STATUS like ' value '

L Common parameters:

• Slow_queries Slow query times
• Number of Com_ (CRUD) operations
• Uptime on-line time

4. Query Optimization 4.1. EXPLAIN

In MySQL, you can use EXPLAIN to view the SQL execution plan, usage: EXPLAIN SELECT * from Tb_item

4.2. The results show 4.2.1. Id

Select identifier. This is the Select query serial number. It doesn't matter.

4.2.2. Select_type

Represents the type of the SELECT statement.

There are several values:

1, simple
Represents a simple query that does not contain connection queries and subqueries.

2, PRIMARY
Represents the primary query, or the outermost query statement.

3. UNION
Represents the 2nd or subsequent query statement for a connection query.

4. DEPENDENT UNION
The second or subsequent SELECT statement in the Union depends on the outside query.

5. UNION RESULT
The result of the connection query.

6, subquery
The 1th SELECT statement in a subquery.

7, DEPENDENT subquery
The 1th SELECT statement in a subquery depends on the outside query.

8, DERIVED
SELECT (subquery FROM clause).

4.2.3. Table

The table that represents the query.

4.2.4. Type (important)

Represents the connection type of the table.

The order of the following connection types is from best type to worst type:

1. System
Table has only one row, this is a const type of special column, usually do not appear, this can also be ignored.

2. Const
The data table has a maximum of one matching row, because it matches only a row of data, so quickly, queries that are commonly used with primary keys or unique indexes can be understood as const optimizations.

3, Eq_ref
The MySQL manual says this: "For each row combination from the previous table, read one row from the table. This may be the best type of join, except for the const type. It is used in all parts of an index to be joined and the index is unique or primary KEY ". Eq_ref can be used to compare indexed columns with =.

4. Ref
The query criteria index is neither a unique nor a primary key case. Ref can be used with indexed columns of = or < or > operators.

5, Ref_or_null
The join type is like ref, but adding MySQL can specifically search for rows that contain null values. The optimization of the join type is often used in the resolution subquery.

The above five scenarios are ideal for indexing use cases.

6, Index_merge
The join type represents the use of the index merge optimization method. In this case, the key column contains the list of indexes used, and Key_len contains the longest key element for the index used.

7, Unique_subquery
The type replaces the ref:value in of the in subquery in the following form (SELECT Primary_key from single_table WHERE some_expr)
Unique_subquery is an index lookup function that can completely replace a subquery and be more efficient.

8, Index_subquery
The join type is similar to Unique_subquery. You can replace in subqueries, but only for non-unique indexes in subqueries of the following form: value in (SELECT key_column from single_table WHERE some_expr)

9. Range
Retrieves only the rows for a given range, using an index to select rows.

10. Index
The join type is the same as all except that only the index tree is scanned. This is usually faster than all, because the index file is usually smaller than the data file.

11. All
For each row combination from the previous table, complete the table scan. (Worst performance)

4.2.5. Possible_keys

Indicates which index MySQL can use to find rows in the table.

If the column is null, the index is not used, and the index can be created to provide performance.

4.2.6. Key

Displays the keys (indexes) that MySQL actually decides to use. If no index is selected, the key is null.

You can force the index to be used or ignore the index:

4.2.7. Key_len

Displays the key lengths that MySQL decides to use. If the key is null, the length is null.

Note: Key_len is determining the length of the index that MySQL will actually use.

4.2.8. Ref

Shows which column or constant is used together with key to select rows from the table.

4.2.9. Rows

Shows the number of rows that MySQL must check when it executes a query.

4.2.10. Extra

This column contains the details of the MySQL resolution query

• Distinct:mysql finds a 1th matching row, stops searching for more rows for the current row combination.
• Not Exists:mysql is able to leftjoin the query, and after discovering 1 rows that match the left join standard, no more rows are checked within the table for the preceding row.
• Range checked for each record (Index map: #): MySQL did not find a good index to use, but found that if the column values from the previous table were known, some indexes might be available.
• The Using Filesort:mysql requires an additional pass to find out how rows are retrieved in sorted order.
• Using index: Retrieves column information from a table by using only the information in the index tree without requiring further search to read the actual rows.
• Using Temporary: In order to resolve the query, MySQL needs to create a temporary table to accommodate the results.
• The Using where:where clause is used to restrict which row matches the next table or send to the customer.
• Using Sort_union (...), usingunion (...), using intersect (...): These functions explain how to merge index scans for Index_merge join types.
• Using index for group-by: similar to the Usingindex way to access a table, using index for group-by means that MySQL has found an index that can be used to query all the columns of a group by or distinct query. Instead of additional search hard disk access the actual table.

4.3. Using index queries requires attention

The index can provide the speed of the query, but not the query with indexed fields will take effect, in some cases it will not take effect, need attention!

4.3.1. Queries using the LIKE keyword

In a query statement that uses the LIKE keyword to query, the index does not work if the first character of the matching string is "%". The index will not take effect until "%" is not in the first position.

4.3.2. Queries that use federated indexes

MySQL can create indexes for multiple fields, and an index can include 16 fields. For federated indexes, the index does not take effect until the first field in these fields is used in the query criteria.

4.3.3. Queries that use the OR keyword

The index does not take effect unless the OR keyword is in the query condition of the query statement, and the columns in the two conditions before or before the or are indexed.

4.4. Sub-query optimization

MySQL supports subqueries from version 4.1, uses subqueries for SELECT statement nesting queries, and can accomplish many SQL operations that logically require multiple steps to complete.

Although the subquery is flexible, execution is not efficient.

When you execute a subquery, MySQL needs to create a temporary table, and then delete the temporary tables after the query is complete, so the speed of the subquery will be affected somewhat.

Optimization:

You can use a connection query (join) instead of a subquery, and you do not need to create a temporary table when connecting to a query faster than a subquery.

5. Database structure Optimization

A good database design will often play a multiplier effect on the performance of the database.

You need to consider the data redundancy, the speed of query and update, the data type of the field is reasonable, and many other contents.

5.1. Splitting a table of many fields into multiple tables

For tables with more fields, if some fields are used very often, you can separate the fields to form a new table.

Because when the amount of data in a table is large, it slows down due to the presence of a field with low frequency.

5.2. Increase the intermediate table

For tables that require frequent federated queries, an intermediate table can be established to improve query efficiency.

By creating an intermediate table, you insert data from the Federated query into the intermediate table and then change the original union query to the query for the intermediate table.

5.3. Add redundant fields

The design of the data table should follow the norms of the paradigm, as far as possible to reduce redundant fields, so that the database design looks exquisite, elegant. However, a reasonable addition of redundant fields can improve query speed.

The higher the degree of normalization of a table, the more relationships between tables and tables, and the more you need to connect to queries, the worse the performance will be.

Attention:

The value of a redundant field is modified in one table, and it is necessary to update it in another table, otherwise it will result in inconsistent data.

6. Optimization of INSERT Data

When inserting data, the main factors that affect the insertion speed are index, uniqueness check, number of data bars inserted at one time, and so on.

Insert data optimization, different storage engine optimization means not the same, in MySQL common storage engine has, MyISAM and InnoDB, the difference between the two:

Http://www.cnblogs.com/panfeng412/archive/2011/08/16/2140364.html

6.1. MyISAM6.1.1. disabling indexes

For non-empty tables, when you insert a record, MySQL indexes the inserted records based on the index of the table. If you insert large amounts of data, indexing reduces the speed of inserting data.

To solve this problem, you can disable the index before you bulk insert the data, and then open the index when the data is inserted.

To disable an indexed statement:

Altertable table_name DISABLE KEYS

To open an index statement:

Altertable table_name ENABLE KEYS

For bulk insert data for empty tables, no action is required because the MyISAM engine's table is indexed after the data is imported.

6.1.2. Disabling uniqueness Checks

Uniqueness checks reduce the speed at which records are inserted, you can disable the uniqueness check before inserting the record, and then turn it on when the data is inserted.

Statement to disable uniqueness checking:SET unique_checks = 0;

The statement that opens the uniqueness check: SET unique_checks = 1;

6.1.3. BULK INSERT Data

When inserting data, you can insert a single piece of data using an INSERT statement, or you can insert more than one piece of data.

The second method inserts faster than the first.

6.1.4. Using the load DATA INFILE

Using the Load Data infile statement is much faster than INSERT statements when you need to bulk import data.

6.2. InnoDB6.2.1. Disabling uniqueness Checks

Usage is the same as MyISAM.

6.2.2. Disabling foreign key checking

Do not check the foreign key before inserting the data, and then restore after the data is inserted, can provide the insertion speed.

Disabled: SET foreign_key_checks = 0;

Open: SET foreign_key_checks = 1;

6.2.3. Prohibit auto-commit

Do not automatically commit the transaction before inserting the data, and then restore after the data is inserted, which can provide the insertion speed.

Disabled: SET autocommit = 0;

Open: SET autocommit = 1;

7. Server Optimization 7.1. Optimizing Server Hardware

The hardware performance of the server directly determines the performance of MySQL database, the performance bottleneck of hardware, and decides the running speed and efficiency of MySQL database directly.

There are several aspects to consider:

1, configuration of large memory. Large enough memory is one of the ways to improve the performance of MySQL database. The memory Io is much faster than the hard disk, can increase the buffer capacity of the system, make the data stay in memory longer, to reduce the disk IO.

2, configure high-speed disk, such as SSD.

3, the reasonable allocation of disk IO, the disk IO dispersed to multiple devices, in order to reduce the competition of resources, improve the parallel operation ability.

4, multi-core processor configuration, MySQL is a multi-threaded database, multi-processor can improve the ability to execute multiple threads at the same time.

7.2. Optimize MySQL Parameters

By optimizing MySQL parameters, you can improve the utilization of resources to achieve the purpose of improving MySQL server performance.

The configuration parameters for MySQL are in the [mysqld] group of the my.conf or My.ini files, which are commonly used as follows:

Requirement: Must remember at least 3.

mysql5.6 Performance Optimization