Optimization steps for MySQL

General steps for MySQL optimization:

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

SHOW Status provides state information for the MSYQL server

In general, we just need to know the instructions that start with "Com"

Show session status like ' com% ': shows the statistics of the current connection

Show global status Like ' com% ': Displays statistics from the date the database was last started

Note: The default is session level

Where com_xxx represents the number of times that the XXX statement was executed.
Note: Com_select,com_insert,com_update,com_delete with these parameters, it is easy to see whether the current database application is primarily for insert updates or query operations, and what is the approximate scale of execution for all types of SQL.

In addition, there are several parameters to note:

Show status like ' Connections '//number of attempts to connect to MySQL server

Show status like ' Uptime '//server working time (in seconds)

Show status like ' Slow_queries '//Slow query (default is 10 seconds as a slow query, as shown)

　　

A) How to query the slow query time of MySQL

Show variables like ' long_query_time ';

b) Modify MySQL slow query time

Set long_query_time=2//If the query takes more than 2 seconds to count as a slow query

2. Locating a SQL statement that performs less efficiently (DQL the probability of a problem is greater than DML)

The question is: How do I find the SELECT statement for a slow query in a project?

Answer: MySQL supports logging slow query statements to the log file. The programmer needs to modify the php.ini configuration file, and by default, the slow query record is not turned on.

To turn on slow query logging:

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

Long_query_time = 2

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

Example: We have 10 million data volumes in our data sheet.

DQL statement: SELECT * from Order_copy WHERE id=12345;

　　

Query time: 19s>2s, so MySQL logs the SELECT statement to the slow query log

SELECT * from order_copy WHERE id=12345 execution Time:

　　Before you add an index: 19s

After adding an index: 0.08s

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. Identify the problem and take appropriate optimization measures

• A common optimization measure is to add an index. Add the index, we do not add memory, do not change the program, do not have to tune SQL, as long as the correct ' create index ', query speed may be raised hundreds of thousands of times. But there is no free lunch, the increase in query speed is at the expense of insertions, updates, and deletions, and these write operations add a lot of I/O.

For example: Add an index to the field ID:

ALTER TABLE order_copy ADD PRIMARY KEY (ID)

Adding primary key to 10 million data takes time: 428 seconds (7 minutes)

EXPLAIN SELECT * from Order_copy WHERE id=12345

The result of rows is 1 because an index is added to the ID.

But the index is not to be added casually, the following are some things to keep in mind:

• 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

Type of index:

• PRIMARY index = = automatically created on primary key
• Index indexes = = Normal Index
• Unique index = = equals Index + Unique
• Fulltext = only in the MyISAM storage engine support, the purpose is full-text indexing, in the content system used more, in all English sites with more (English word independent). Chinese data is not commonly used, meaning domestic full-text indexing is usually done using Sphinx.

Use of indexes

• Create an index create [unique| FULLTEXT] Index index_name on tbl_name (col_name [(length)] [ASC | DESC], ...);
  ALTER TABLE table_name ADD INDEX [index_name] (index_col_name,...)

Add primary key (index) ALTER table name add PRIMARY key (column name,..); Federated primary Key

• Drop INDEX index_name on tbl_name;
  ALTER TABLE table_name DROP INDEX index_name;
• Delete primary key (index) compare Special: ALTER TABLE t_b drop PRIMARY key;
• Query index (all available) show index from TABLE_NAME;
  Show keys from table_name;
  DESC table_name;

Related information:

Use of explain: http://www.cnblogs.com/you-yang/archive/2012/01/18/2325841.html

Optimization steps for MySQL