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

Here's how to explain and optimize performance in MySQL. The secret of text is to be close to the topic. So, gossip will not talk about it, we directly look at the following bar, I believe that after reading how to use MySQL explain and optimize performance this article you will certainly benefit.

1. Use the explain statement to view the analysis results

as

explain select * from test1 where id=1;

It will appear:

id selecttype table type possible_keys key_len ref rows extra Columns.

Among them,

type=const indicates that the index is found once;

key=primary, indicating that the primary key is used;

type=all, indicating full table scanning;

key=null means no index is used. type=ref, because it is considered to be multiple matching rows at this time, in the joint query, it is generally REF.

2. Composite index in MYSQL

Suppose the table has id,key1,key2,key3, and the three form a combined index.

For example:

where key1=.... where key1=1 and key2=2 where key1=3 and key3=3 and key2=2

According to the leftmost principle, these can be indexed, such as from test where key1=1 order by key3, with explain analysis, only the normal_key index is used, but only for the where clause, and the latter order by needs to be sorted.

3. Use slow query analysis

In my.ini:

long_query_time=1log-slow-queries=d:\mysql5\logs\mysqlslow.log

Log anything longer than 1 second in the slow query log

It can be analyzed using mysqlsla. It can also be found in mysqlreport, such as

DMS analyzes the percentage of select ,update,insert,delete,replace, etc.

4. MYISAM and INNODB lock

myisam, note that the table is locked, for example, after multiple UPDATE operations, SELECT again, you will find that the SELECT operation is locked, you must wait until all UPDATE operations are completed, then SELECT

Innodb is different. It uses a row lock and does not have the above problem.

5. MYSQL Transaction Configuration Items

innodb_flush_log_at_trx_commit=1

Indicates that the transaction log is written to disk immediately after the transaction is committed, and the data and index are updated.

innodb_flush_log_at_trx_commit=0

When a transaction commits, the transaction log is not written to disk immediately, but every 1 second

innodb_flush_log_at_trx_commit=2

When a transaction commits, it writes to the disk file immediately (here only to the kernel buffer, but not to disk immediately, but to disk every 1 second, updating both the data and the index

explain usage

EXPLAIN tbl_name or: EXPLAIN [EXTENDED] SELECT select_options

The former can derive the field structure of a table and so on, the latter mainly gives some relevant index information, and today's focus is on the latter.

example

mysql> explain select * from event; +--+-----+---+--+-----+--+---+--+--+---+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +--+-----+---+--+-----+--+---+--+--+---+ | 1 | SIMPLE | event | ALL | NULL | NULL | NULL | NULL | 13 | | +--+-----+---+--+-----+--+---+--+--+---+ 1 row in set (0.00 sec)

Meaning of each attribute

id

select the serial number of the query

select_type

The type of select query is mainly to distinguish ordinary queries from complex queries such as joint queries and subqueries.

table

The table referenced by the output row.

type

The type used by federated queries.

Type shows the type of access, which is a more important indicator. The result values from good to bad are:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

In general, you need to ensure that queries reach at least range level, preferably ref level.

possible_keys

Indicates which index MySQL can use to find rows in the table. If empty, there is no associated index. To improve performance, check the WHERE clause to see if certain fields are referenced, or if fields do not fit into the index.

key

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

key_len

Displays the key length MySQL decides to use. If the key is NULL, the length is NULL. Note in particular that this value tells you what part of mysql is actually used in a multiple primary key.

ref

Shows which field or constant is used with key.

rows

This number indicates how much data mysql has to traverse to find, which is inaccurate on innodb.

Extra

If index only, this means that information is retrieved only from the index tree, which is faster than scanning the entire table.

If it is where used, it is where restriction is used.

If it is impossible where, it means that there is no need for where. Generally, it means that nothing has been found out.

If this information shows that Using filesort or Using temporary will be very difficult, WHERE and ORDER BY indexes often can not be balanced, if the index is determined according to WHERE, then in ORDER BY, it will inevitably cause Using filesort, which depends on whether it is cost-effective to filter first and then sort, or to sort first and then filter.

Is there anything you don't understand about how to use MySQL explain and optimize performance? Or if you want to know more about it, you can continue to pay attention to our industry information section.

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.