How to optimize SQL statements in MySQL

1. Overview

During the application system development process, due to the small initial amount of data, developers pay more attention to functional implementation when writing SQL statements. However, when the application system is officially launched, as the production data With the rapid growth in volume, many SQL statements have gradually begun to show performance problems, and their impact on the production environment has become greater and greater. At this time, these problematic SQL statements have become the bottleneck of the entire system performance, so we must optimize them. .

2. Use the show status command to understand the execution frequency of various SQLs

After the MySQL client is successfully connected, 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 the parameter "session" or "global" as needed to display the statistical results at the session level (current connection) and the statistical results at the global level (since the last time the database was started). If not written, the default parameter used is "session".

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

-- 查看会话所有统计的值
SHOW STATUS LIKE 'Com_%';
Or
SHOW SESSION STATUS LIKE 'Com_%';

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 indicates the number of times each xxx statement is executed. We usually care about the following statistics Parameters:

• Com_select: The number of times to perform SELECT operations. Only 1 will be accumulated for one query.

• Com_insert: The number of times the INSERT operation is performed. For batch insert INSERT operations, only one is accumulated.

• Com_update: The number of times UPDATE operations are performed.

• Com_delete: Number of times to perform DELETE operations.

The above parameters will be accumulated for all storage engine table operations. These parameters only apply to the InnoDB storage engine, and its accumulation algorithm is slightly different.

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

• Innodb_rows_inserted: The number of rows inserted during the INSERT operation.

• Innodb_rows_updated: The number of rows updated by UPDATE operations.

• Innodb_rows_deleted: The number of rows deleted by DELETE operation.

Through the above parameters, you can easily understand whether the current database application system is mainly based on insert and update operations or query operations, as well as the general execution of various types of SQL. What is the ratio. Regardless of commit or rollback, the count of update operations will be accumulated, and the count object is the number of executions.

For transactional applications, Com_commit and Com_rollback can be used to understand transaction submission and rollback. For databases with very frequent rollback operations, it may mean that there are problems in application writing. In addition, the following parameters help users 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 SQL statements with low execution efficiency in the following two ways.

• Locate those SQL statements with low execution efficiency through slow query logs. When starting with the --log-slow-queries[=file_name] option, mysqld writes a file containing all execution times exceeding Log file of SQL statements for long_query_time seconds.

• The slow query log is recorded after the query is completed, so when the application system reflects execution efficiency problems, querying the slow query log cannot locate the problem. You can use the show processlist command to view the current MySQL The ongoing thread, including the status of the thread, whether to lock the table, etc., can check the execution status of SQL in real time, and at the same time optimize some table lock operations.

4. Analyze the execution plan of inefficient SQL through EXPLAIN

After locating the SQL statement with low execution efficiency, you can use the EXPLAIN or DESC command to obtain how MySQL is executed. The information of the SELECT statement, including how the tables are connected and the order of the connections during the execution of the SELECT statement. For example, if you want to count the number of all inventory ladders, you need to associate the goods_stock table and the goods_stock_price table, and perform a sum operation on the goods_stock_price.Qty field. The execution plan of the corresponding SQL is as follows:

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

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

• select_type: represents SELECT type, common values ​​are:

• SIMPLE (simple table, that is, no table connection or subquery is used).

• PRIMARY (main query, that is, the outer query), UNION (the second or subsequent query statement in UNION), ◎SUBQUERY (the first SELECT in the subquery) )wait.

• table: The table that outputs the result set.

• type：表示表的连接类型，性能由好到差的连接类型为：

• system（表中仅有一行，即常量表）。

• const（单表中最多有一个匹配行，例如primary key或者unique index）。

• eq_ref（对于前面的每一行，在此表中只查询一条记录，简单来说，就是多表连接中使用primary key或者unique index）。

• ref（与eq_ref类似，区别在于不是使用primary key或者unique index，而是使用普通的索引）。

• ref_or_null（与ref类似，区别在于条件中包含对NULL的查询）。

• index_merge（索引合并优化）。

• unique_subquery（in的后面是一个查询主键字段的子查询）。

• index_subquery（与unique_subquery类似，区别在于in的后面是查询非唯一索引字段的子查询）。

• range（单表中的范围查询）。

• index（对于前面的每一行，都通过查询索引来得到数据）。

• all（对于前面的每一行，都通过全表扫描来得到数据）。

• possible_keys：表示查询时，可能使用的索引。

• key：表示实际使用的索引。

• key_len：索引字段的长度。

• rows：扫描行的数量。

• filtered：返回结果的行占需要读到的行(rows列的值)的百分比。

• Extra：执行情况的说明和描述。

• Using index（此值表示mysql将使用覆盖索引，以避免访问表）。

• Using where（mysql 将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当(并且如果)它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。“Using where”有时提示了一种可能性：查询可以从不同的索引中受益。

• Using temporary（mysql 对查询结果排序时会使用临时表）。

• MySQL will apply an external index sorting on the results instead of reading rows from the table in index order.。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成）。

• Range checked for each record(index map: N) （没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的）。

5.确定问题并采取相应的优化措施

经过以上定位步骤，我们基本就可以分析到问题出现的原因。此时我们可以根据情况采取相应的改进措施，进行优化提高语句执行效率。
在上面的例子中，已经可以确认是goods_stock是走主键索引的，但是对goods_stock_price子表的进行了全表扫描导致效率的不理想，那么应该对goods_stock_price表的GoodsStockID字段创建索引，具体命令如下：

-- 创建索引
CREATE INDEX idx_stock_price_1 ON goods_stock_price (GoodsStockID);
-- 附加删除跟查询索引语句
ALTER TABLE goods_stock_price DROP INDEX idx_stock_price_1;
SHOW INDEX FROM goods_stock_price;

创建索引后，我们再看一下这条语句的执行计划，具体如下：

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

可以发现建立索引后对goods_stock_price子表需要扫描的行数明显减少（从 3 行减少到1行），可见索引的使用可以大大提高数据库的访问速度，尤其在表很庞大的时候这种优势更为明显。

The above is the detailed content of How to optimize SQL statements in MySQL. For more information, please follow other related articles on the PHP Chinese website!