Introduction to Mysql's optimization steps (recommended) _mysql

General steps for MySQL optimization:

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

Show status provides status information for the MSYQL server

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

Show sessions status like ' com% ': Displays the statistics of the current connection

Show global status Like ' com% ': statistics showing since the database was last started

Note: Default is session level

Where com_xxx represents the number of times the XXX statement was executed.

Key Note: Com_select,com_insert,com_update,com_delete through these parameters, it is easy to understand that the current database application is to insert

Whether to update the main or query operations, as well as various types of SQL roughly the percentage of execution.

In addition, there are several parameters to note:

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

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

Show the status like ' slow_queries '//Slow query (default is 10 seconds as a slow query, as shown in the following figure)

A) How to query the MySQL slow query time

Show variables like ' long_query_time ';

b) Modify MySQL slow query time

Set long_query_time=2//If the query time is more than 2 seconds is counted as a slow query

2. Location execution less efficient SQL statement (DQL is more likely to have problems 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 log files. The programmer needs to modify the php.ini configuration file, by default, the slow query record is not open.

To open a slow query record:

Open My.ini and find [mysqld] added below it

Long_query_time = 2

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

Example: We have 10 million data volumes in our datasheet

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

Query time consuming: 19s>2s, so MySQL will record the SELECT statement in the slow query log

SELECT * from order_copy WHERE id=12345 execution Time:

Before adding an index: 19s

After adding an index: 0.08s

3. The implementation of inefficient SQL statements through explain analysis

Use explain to parse the DQL statement:

EXPLAIN SELECT * from Order_copy WHERE id=12345

will produce the following information:

Select_type: Represents the type of query.

Table: Tables for output result sets

Type: Indicates the connection type of the table (System and const are preferred)

Possible_keys: An index that may be used to indicate a query

Key: Represents the actual index used

Key_len: Length of index field

Rows: Number of lines scanned

Extra: Description and description of implementation

Note: Try to avoid having the result of type All,extra as: Using Filesort

4. Determine the problem and take appropriate optimization measures

A common optimization measure is to add an index. Add index, we do not need to add memory, do not change the program, do not need to adjust the SQL, as long as the implementation of the correct ' create index ', query speed may be raised hundreds of times. But the world does not have a free lunch, query speed is increased by inserting, updating, delete the speed of the cost, these write operations, adding a lot of I/O.

For example: Add an index to a 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

It is because the index is added to the ID that the result of rows is 1

However, the index is not easily added, and the following situations need to be kept in mind:

More frequent query criteria fields should create an index

SELECT * from order_copy where id = $id

Fields that are too unique are not suitable for creating indexes individually, even if they are frequently used as query criteria

SELECT * from order_copy where sex= ' female '

Fields that are updated very frequently are not appropriate to create an index

SELECT * from order_copy where order_state= ' not paid '

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

Type of index:

PRIMARY index => automatically create INDEX index on primary key => is normal index unique index => equivalent to index + uniquefulltext => only in MyISAM storage engine support, the purpose is full-text indexing, in content The system uses many, in the whole English website to use many (English word independent). Chinese data is not commonly used, it is not very meaningful domestic full-text indexing is usually done using Sphinx.

Use of indexes

Create 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,..); Union PRIMARY key

Deletes index index_name on tbl_name;
ALTER TABLE table_name DROP INDEX index_name; Delete primary key (index) is 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;

The above is small series for everyone to bring the MySQL optimization steps introduced (recommended) all content, I hope that we support cloud Habitat Community ~