MySql optimization steps (recommended), mysql optimization steps recommended

MySql optimization steps (recommended), mysql optimization steps recommended

General steps for MySql optimization:

1. Run the show status command to check the execution efficiency of various SQL statements.

Show status provides the STATUS information of the msyql Server

Generally, we only need to know the commands starting with "Com ".

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

Show global status like 'com % ': displays the statistical results since the last database was started.

Note: The default value is session-level.

Com_XXX indicates the number of times the XXX statement is executed.

NOTE: With these parameters Com_select, Com_insert, Com_update, and Com_delete, you can easily understand that the application of the current database is

Whether the data is mainly updated or queried, and the approximate execution ratio of various types of SQL statements.

Note the following parameters:

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

Show status like 'uptime' // server working time (unit: seconds)

Show status like 'slow _ queries '// The number of Slow queries (the default value is 10 seconds, as shown in)

A) How to query the slow query time of mysql

Show variables like 'long _ query_time ';

B) modify the mysql slow query time

Set long_query_time = 2 // If the query time exceeds 2 seconds, it is a slow query.

2. Locate SQL statements with lower execution efficiency (the probability of dql problems is higher than that of dml)

Q: How can I find the select statement for slow queries in a project?

Answer: mysql supports recording slow query statements into log files. Programmers need to modify the configuration file of php. ini. By default, slow query records are not enabled.

To enable slow query records, follow these steps:

Open my. ini, find [mysqld], and add

Long_query_time = 2

Log-slow-queries = D:/mysql/logs/slow. log # Set to write the log there. It can be blank. The system will give a default file

Example: A data table contains 10 million data records.

DQL statements: SELECT * FROM order_copy WHERE id = 12345;

Query time: 19 s> 2 s, so mysql records the select statement to the slow query log.

Execution time of SELECT * FROM order_copy WHERE id = 12345:

Before adding an index: 19 s

After the index is added: 0.08 s

3. Analyze the execution of low-efficiency SQL statements through explain

Use explain to analyze the dql statement:

Explain select * FROM order_copy WHERE id = 12345

The following information is generated:

Select_type: indicates the query type.

Table: The table of the output result set.

Type: indicates the table connection type (system and const are preferred)

Possible_keys: indicates the index that may be used during query.

Key: indicates the index actually used

Key_len: Index Field Length

Rows: Number of scanned rows

Extra: Description and description of execution

Note: Do not set the type result to all. The extra result is: using filesort.

4. Identify the problem and take corresponding optimization measures

A common optimization measure is to add an index. To add indexes, we do not need to add memory, change programs, or call SQL. If we execute a correct 'create Index', the query speed may be improved by times. However, there is no free lunch in the world, and the query speed is improved at the cost of the insert, update, and delete speeds. These write operations increase a lot of I/O.

For example, add an index to the field id:

Alter table order_copy add primary key (id)

It takes 428 seconds to add a primary key to 10 million data records (7 minutes)

Explain select * FROM order_copy WHERE id = 12345

It is precisely because the index is added to the id that the result of rows is 1.

However, indexes cannot be added at will. The following situations must be kept in mind:

Index should be created frequently as a query condition Field

Select * from order_copy where id = $ id

Fields with poor uniqueness are not suitable for independent index creation, even if they are frequently used as query conditions.

Select * from order_copy where sex = 'femal'

Fields with frequent updates are not suitable for index creation.

Select * from order_copy where order_state = 'unpaid'

It does not appear in the WHERE clause. The field should not create an index.

Index type:

Primary index => automatically create INDEX indexes on the PRIMARY key => is the normal index unique index => equivalent to INDEX + UniqueFULLTEXT => only supported by the MYISAM storage engine, with the aim of full-text INDEX, many are used in the content system, and many are used in the whole English website (English words are independent ). chinese data is not commonly used. It is of little significance that domestic full-text indexes are usually completed using sphinx.

Use of Indexes

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,...); join PRIMARY KEY

Delete INDEX DROP INDEXIndex_nameONTbl_name;
Alter table table_name drop index index_name; Delete primary key (index) is special: alter table t_ B drop primary key; query index (CAN) show index from table_name;
Show keys from table_name;
Desc table_Name;

The above is a summary of all the MySql optimization steps (recommended) provided by Alibaba Cloud. I hope you can provide more support ~