Common optimization techniques for MySQL

1. Understanding the execution efficiency of various SQL through the show status command

2. Locating SQL statements that perform less efficiently

Turn on slow query logging:

Open the MySQL profile My.ini and find [mysqld] below it to add

Long_query_time = 2//Slow query time

Log_slow_queries = E:/mysql/logs/log.log #设置把日志写在那里, can be empty, the system will give a default file

3. Analyze the execution of inefficient SQL statements by explain

Parse the DQL statement using explain:

EXPLAIN SELECT * from Order_copy WHERE id=12345
The following information is generated:
Select_type: Represents the type of query.
Table: Tables for output result sets
Type: Represents the connection types for the table (System and const are preferred)
Possible_keys: The index that may be used when representing the query
Key: Represents the actual index used
Key_len: Length of index field
Rows: Number of rows scanned
Extra: Description and description of the performance

　　Note: To try to avoid having the result of type All,extra as the result of: using Filesort

4. The appropriate location plus the index "note the following situations"

• More frequent as a query criteria field should create an index

SELECT * from order_copy where id = $id

• Fields with poor uniqueness are not suitable for creating indexes individually, even if they are frequently used as query criteria

SELECT * from order_copy where sex= ' woman '

• Fields that are updated very frequently are not suitable for creating indexes

SELECT * from order_copy where order_state= ' not paid '

• Fields that do not appear in the WHERE clause do not create an index

5. How to build an index

Please refer to http://www.cnblogs.com/itsharehome/p/4955162.html

Common optimization techniques for MySQL