Shulou(Shulou.com)05/31 Report--

This article mainly introduces the relevant knowledge of "how to optimize SQL statements in MySQL". The editor shows you the operation process through an actual case. The operation method is simple, fast and practical. I hope this article "how to optimize SQL statements in MySQL" can help you solve the problem.

1. Overview

In the process of application system development, due to the small amount of initial data, developers pay more attention to the functional implementation when writing SQL statements, but when the application system is officially launched, with the rapid growth of the amount of production data, many SQL statements begin to show performance problems and have a greater and greater impact on the production environment. At this time, these problematic SQL statements become the bottleneck of the performance of the whole system. So we have to optimize them.

two。 Know the execution frequency of various SQL through the show status command

After the MySQL client connects successfully, you can provide server status information through the show [session | global] status command, or you can use the mysqladmin extended-status command on the operating system to obtain these messages. Show [session | global] status can add parameters "session" or "global" as needed to display statistics at session level (current connection) and global level (since the database was last started). If you do not write, the default parameter is "session".

The following command displays the values of all statistical parameters in the current session:

-- View the SHOW STATUS LIKE 'Com_%';OrSHOW SESSION STATUS LIKE' Com_%' of all the statistics of the session

The following command displays the values of all statistical parameters in the current global:

-- View the values of all global statistics

SHOW GLOBAL STATUS LIKE 'Com_%'

Com_xxx represents the number of times each xxx statement is executed, and we are usually concerned about the following statistical parameters:

Com_select: the number of times a SELECT operation is performed. Only 1 is added to a query.

Com_insert: the number of times the INSERT operation is performed. For bulk inserted INSERT operations, it is added only once.

Com_update: the number of times the UPDATE operation was performed.

Com_delete: the number of times the DELETE operation was performed.

The above parameters are cumulative for all table operations of the storage engine. The following parameters are only for the InnoDB storage engine, and the accumulation algorithm is also slightly different.

The number of rows returned by the Innodb_rows_read:SELECT query.

Innodb_rows_inserted: the number of rows inserted by the INSERT operation.

Innodb_rows_updated: the number of rows updated by the UPDATE operation.

Innodb_rows_deleted: the number of rows deleted by the DELETE operation.

Through the above parameters, it is easy to know whether the current database application system is mainly insert update or query operation, as well as the approximate execution proportion of various types of SQL. For the count of update operations, it is the count of the number of times executed, whether committed or rolled back.

For transactional applications, we can learn about transaction commit and rollback through Com_commit and Com_rollback. For databases with very frequent rollback operations, it may mean that there are problems in application writing. In addition, the following parameters make it easy for users to understand the basic situation of the database.

Connections: the number of attempts to connect to the MySQL server.

Uptime: server working time.

Slow_queries: the number of slow queries.

3. Locate SQL statements with low execution efficiency

You can locate inefficient SQL statements in two ways.

Slow query logs are used to locate inefficient SQL statements, and when started with the-- log-slow-queries [= file_name] option, mysqld writes a log file containing all SQL statements that have been executed for more than long_query_time seconds.

The slow query log is not recorded until the end of the query, so querying the slow query log can not locate the problem when the application system reflects that there is a problem with the execution efficiency. You can use the show processlist command to check the current threads in MySQL, including the status of the thread, whether to lock the table, etc., you can check the execution of SQL in real time, and optimize some table locking operations.

4. Analyze the implementation plan of inefficient SQL by EXPLAIN

After locating the inefficient SQL statements, you can use EXPLAIN or DESC commands to obtain information about how MySQL executes SELECT statements, including the order in which tables are joined and joined during the execution of SELECT statements. For example, to count the number of all inventory ladders, you need to associate goods_ stocktable and goods_stock_ print table, and sum the goods_stock_price.Qty field. The corresponding SQL execution plan is as follows:

EXPLAIN SELECT SUM (sp.Qty) FROM goods_stock AS s LEFT JOIN goods_stock_price AS spON s.ID=sp.GoodsStockID

As shown in the figure above, the simple explanation for each column is as follows:

Select_type: indicates the type of SELECT. Common values are:

SIMPLE (simple tables, that is, no table joins or subqueries).

PRIMARY (the main query, that is, the outer query), UNION (the query statement after the second or third in the UNION), ◎ SUBQUERY (the first SELECT in the subquery), and so on.

Table: the table that outputs the result set.

Type: indicates the connection type of the table. The connection types with good to poor performance are:

System (there is only one row in the table, that is, the constant table).

Const (there is at most one matching row in a single table, such as primary key or unique index).

Eq_ref (for each previous row, only one record is queried in this table, which simply means using primary key or unique index in multi-table joins).

Ref (similar to eq_ref, except that instead of using primary key or unique index, you use a normal index).

Ref_or_null (similar to ref, except that the condition contains a query for NULL).

Index_merge (Index merge Optimization).

Unique_subquery (in is followed by a subquery of the query primary key field).

Index_subquery (similar to unique_subquery, except that in is followed by a subquery that queries non-unique index fields).

Range (range query in a single table).

Index (for each previous row, the data is obtained by querying the index).

All (for each previous row, the data is obtained by a full table scan).

Possible_keys: represents the index that may be used when querying.

Key: represents the index actually used.

Key_len: the length of the index field.

Rows: the number of scan lines.

Filtered: the percentage of rows that return the result as a percentage of the rows that need to be read (the value of the rows column).

Extra: description and description of the implementation.

Using index (this value indicates that mysql will use an override index to avoid accessing the table).

Using where (mysql will filter after the storage engine retrieves the rows, and many where conditions involve columns in the index, and when (and if) it reads the index, it can be verified by the storage engine, so not all queries with where clauses display "Using where". Sometimes the emergence of "Using where" is a hint that queries can benefit from different indexes).

Using temporary (temporary tables are used when mysql sorts query results).

Using filesort (mysql sorts the results using an external index instead of reading rows from the table in indexed order. Mysql has two file sorting algorithms, both of which can be done in memory or on disk. Explain will not tell you which file sorting will be used by mysql, nor will it tell you whether the sorting will be done in memory or on disk.

Range checked for each record (index map: n) (there is no useful index, the new index will be re-evaluated on each row of the join, N is the bitmap of the index displayed in the possible_keys column, and is redundant).

5. Identify the problem and take corresponding optimization measures

After the above positioning steps, we can basically analyze the cause of the problem. At this time, we can take corresponding improvement measures according to the situation to optimize and improve the efficiency of sentence execution.

In the above example, it has been confirmed that goods_stock is indexed by the primary key, but a full table scan of the goods_stock_ print sub-table leads to poor efficiency, so you should create an index on the GoodsStockID field of the goods_stock_price table, as follows:

-- create index CREATE INDEX idx_stock_price_1 ON goods_stock_price (GoodsStockID);-- add delete and query index statement ALTER TABLE goods_stock_price DROP INDEX idx_stock_price_1;SHOW INDEX FROM goods_stock_price

After creating the index, let's take a look at the execution plan for this statement, as follows:

EXPLAIN SELECT SUM (sp.Qty) FROM goods_stock AS s LEFT JOIN goods_stock_price AS spON s.ID=sp.GoodsStockID

It can be found that after indexing, the number of rows that need to be scanned on the goods_stock_ print sub-table is significantly reduced (from 3 rows to 1 row). It can be seen that the use of indexes can greatly improve the access speed of the database, especially when the table is very large.

This is the end of the introduction to "how to optimize SQL statements in MySQL". Thank you for reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

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.