Shulou(Shulou.com)06/01 Report--

target

 knows what optimization is.

 has mastered the method of optimizing query

 grasps the method of optimizing database structure

 knows how to optimize the MySQL server. What is optimization?

 arranges resources reasonably and adjusts system parameters to make MySQL run faster and save resources.

 optimization is multifaceted, including queries, updates, servers, and so on.

 principle: reduce the bottleneck of the system, reduce the occupation of resources, and increase the response speed of the system. Database performance parameters

 uses SHOW STATUS statements to view the performance parameters of the MySQL database

SHOW STATUS LIKE 'value'

Parameters commonly used in :

Number of Slowqueries slow queries

Number of Com (CRUD) operations

Query optimization of Uptime launch time

4.1. EXPLAIN

You can use EXPLAIN to view the SQL execution plan in MySQL, usage: EXPLAIN SELECT * FROM tb_item

4.2. The results show that

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 SELECT statement.

There are several values:

1 、 SIMPLE

Represents a simple query without join queries and subqueries.

2 、 PRIMARY

Represents the main query, or the outermost query statement.

3 、 UNION

Represents the second or subsequent query statement of the join query.

4 、 DEPENDENT UNION

The second or subsequent SELECT statement in UNION depends on the external query.

5 、 UNION RESULT

Join the results of the query.

6 、 SUBQUERY

The first SELECT statement in the subquery.

7 、 DEPENDENT SUBQUERY

The first SELECT statement in the subquery depends on the external query.

8 、 DERIVED

SELECT (a subquery of the FROM clause).

4.2.3. Table

Represents the table of the query.

4.2.4. Type (important)

Represents the connection type of the table.

The following connection types are in order from the best type to the worst type:

1 、 system

The table has only one row, which is a special column of type const, which usually does not appear, and this can be ignored.

2 、 const

The data table has at most one matching row, because it matches only one row of data, so it is very fast. Queries commonly used in PRIMARY KEY or UNIQUE indexes can be understood as const is optimized.

3 、 eq_ref

The mysql manual says: "for each row combination from the previous table, read a row from the table. This is probably the best join type, except for the const type. It is used in all parts of an index that are used by joins and the index is UNIQUE or PRIMARY KEY." Eq_ref can be used to compare indexed columns using =.

4 、 ref

The query conditional index is neither UNIQUE nor PRIMARY KEY. Ref can be used for indexed columns of the = or operator.

5 、 ref_or_null

The join type is like ref, but MySQL is added to specifically search for rows that contain null values. The optimization of this join type is often used in solving subqueries.

The above five cases are ideal for index usage.

6 、 index_merge

The join type indicates that the index merge optimization method is used. In this case, the key column contains a list of the indexes used, and the key_len contains the longest key elements of the index used.

7 、 unique_subquery

This type replaces the ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) of the in subquery in the following form

Unique_subquery is an index lookup function that can completely replace subqueries and is more efficient.

8 、 index_subquery

The join type is similar to unique_subquery. In subqueries can be replaced, but only for non-unique indexes in the following form of subqueries: value IN (SELECT key_column FROM single_table WHERE some_expr)

9 、 range

Only a given range of rows are retrieved, using an index to select rows.

10 、 index

This join type is the same as ALL, except that only the index tree is scanned. This is usually faster than ALL because index files are usually smaller than data files.

11 、 ALL

A complete table scan is performed for each row combination from the previous table. (worst performance)

4.2.5. Possible_keys

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

If the column NULL is not used, you can create an index on the column to improve performance.

4.2.6. Key

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

You can force the use of indexes or ignore them:

4.2.7. Key_len

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

Note: key_len determines the index length that MySQL will actually use.

4.2.8. Ref

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

4.2.9. Rows

Displays the number of rows that MySQL thinks it must check when executing the query.

4.2.10. Extra

This column contains the details of the MySQL resolution query

When Distinct:MySQL finds the first matching row, it stops searching for more rows for the current row combination.

Not exists:MySQL can LEFT JOIN optimize the query, and after finding a row that matches the LEFT JOIN standard, no more rows are checked in the table for the previous combination of rows.

Range checked for each record (index map: #): MySQL did not find a good index to use, but found that some indexes might be available if the column values from the previous table were known.

Using filesort:MySQL needs an extra pass to figure out how to retrieve rows in sort order.

Using index: retrieve the column information in the table by reading the actual rows from using only the information in the index tree without further search.

Using temporary: to solve the query, MySQL needs to create a temporary table to hold the results.

The Using where:WHERE clause is used to restrict which row matches the next table or is sent to the customer.

Using sort_union (...), Using union (...), Using intersect (...): these functions show how to merge index scans for index_merge join types.

Using index for group-by: similar to the Using index way of accessing tables, Using index for group-by indicates that MySQL has found an index that can be used to query all columns of a GROUP BY or DISTINCT query without additional searching the hard disk to access the actual table.

4.3. Attention should be paid to using index query

The index can provide the speed of the query, but not all queries with indexed fields will take effect, and in some cases it will not take effect, so you should pay attention to it!

4.3.1. Queries using the LIKE keyword

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

4.3.2. Queries using federated indexes

MySQL can create indexes for multiple fields, and an index can include 16 fields. For federated indexes, the index takes effect only if the first of these fields is used in the query criteria.

4.3.3. Queries using the OR keyword

The index will only take effect if there is only the OR keyword in the query condition of the query statement, and the columns in both conditions before and after OR are indexes, otherwise, the index will not take effect.

4.4. Subquery optimization

MySQL supports subqueries since version 4.1. Using subqueries to nest SELECT statements, you can complete many SQL operations that logically require multiple steps to complete at a time.

Although the subquery is very flexible, its execution efficiency is not high.

When executing a subquery, MYSQL needs to create temporary tables, and then delete these temporary tables after the query is completed, so the speed of the subquery will be affected to a certain extent.

Optimization:

You can use join query (JOIN) instead of sub-query, join query does not need to establish temporary tables, its speed is faster than sub-query.

Database structure optimization

A good database design scheme will often get twice the result with half the effort for the performance of the database.

Many aspects need to be considered, such as data redundancy, the speed of query and update, whether the data type of the field is reasonable, and so on.

5.1. Decompose a table with many fields into multiple tables

For tables with more fields, if some fields are not used frequently, you can separate these fields to form a new table.

Because when a table has a large amount of data, it will slow down due to the existence of fields that are used less frequently.

5.2. Add an intermediate table

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

By establishing the intermediate table, the data that needs to be queried by the federation is inserted into the intermediate table, and then the original federated query is changed to the query of the intermediate table.

5.3. Add redundant fields

When designing the data table, we should follow the specification of the paradigm theory as far as possible, reduce the redundant fields as much as possible, and make the database design look delicate and elegant. However, the reasonable addition of redundant fields can improve the query speed.

The higher the degree of normalization of the table, the more relationships between the table and the table, the more situations that need to join queries, and the worse the performance.

Note:

If the values of redundant fields are modified in one table, you must find a way to update them in other tables, otherwise it will lead to data inconsistencies.

Optimization of insert data

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

Different storage engines have different optimization methods for inserting data. The commonly used storage engines in MySQL are MyISAM and InnoDB.

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

6.1. MyISAM

6.1.1. Disable indexing

For non-empty tables, when you insert a record, MySQL indexes the inserted record based on the index of the table. If you insert a large amount of data, establishing an index slows down the insertion of data.

To solve this problem, you can disable the index before bulk inserting the data, and open the index after the data insertion is complete.

Statement that disables the index:

ALTER TABLE table_name DISABLE KEYS

Open the index statement:

ALTER TABLE table_name ENABLE KEYS

For empty tables to insert data in bulk, no action is required because the tables of the MyISAM engine are indexed after the data has been imported.

6.1.2. Disable uniqueness check

The uniqueness check slows down the insertion of the record. You can disable the uniqueness check before inserting the record and turn it on after inserting the data.

Statement that disables uniqueness checking: SET UNIQUE_CHECKS = 0

Statement that turns on uniqueness checking: SET UNIQUE_CHECKS = 1

6.1.3. Insert data in bulk

When inserting data, you can insert a single piece of data or multiple pieces of data using a single INSERT statement.

The insertion speed of the second method is faster than that of the first.

6.1.4. Use LOAD DATA INFILE

When you need to import data in bulk, using LOAD DATA INFILE statements is much faster than inserting INSERT statements.

6.2. InnoDB

6.2.1. Disable uniqueness check

The usage is the same as MyISAM.

6.2.2. Disable foreign key check

Perform a check that forbids foreign keys before inserting data, and restore it after data insertion is completed, which can provide insertion speed.

Disable: SET foreign_key_checks = 0

On: SET foreign_key_checks = 1

6.2.3. Automatic submission is prohibited

The automatic commit of the transaction is prohibited before the data is inserted, and the data is restored after the data insertion is completed, which can improve the insertion speed.

Disable: SET autocommit = 0

On: SET autocommit = 1

Server optimization

7.1. Optimize server hardware

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

The following aspects need to be considered:

1. Configure larger memory. Enough memory is one of the ways to improve the performance of MySQL databases. The IO of the memory is much faster than that of the hard disk, which can increase the buffer capacity of the system and make the data stay in memory longer, so as to reduce the IO of the disk.

2. Configure high-speed disks, such as SSD.

3. Allocate disk IO reasonably and distribute disk IO to multiple devices in order to reduce the competition of resources and improve the ability of parallel operation.

4. Configure multi-core processors. MySQL is a multi-threaded database. Multi-processors can improve the ability to execute multiple threads at the same time.

7.2. Optimize the parameters of MySQL

The resource utilization can be improved by optimizing the parameters of MySQL, so as to improve the performance of MySQL server.

The configuration parameters of MySQL are all in the [mysqld] group of my.conf or my.ini file. Common parameters are as follows:

Requirement: at least 3 must be memorized.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.