Use examples to tell you how to optimize SQL

Although hardware costs have dropped nowadays, improving system performance by upgrading hardware is also a common optimization method. Systems with high real-time requirements still need to be optimized from the SQL aspect. Today we will introduce how to optimize SQL based on examples.

Judge the problem SQL

When you judge whether there is a problem with SQL, you can judge it through two phenomena:

• System level phenomenon

  • CPU consumption is serious

  • IO waiting is serious

  • The page response time is too long

  • Timeout and other errors appear in the application log

You can use the sar command and the top command to view the current system status. You can also observe the system status through monitoring tools such as Prometheus and Grafana.

• ##SQL statement appearance
  • Lengthy

  • Execution The time is too long

  • Getting data from full table scan

  • The rows and cost in the execution plan are very large

Long SQL is easy to understand. If a SQL is too long, the readability will be poor, and the frequency of problems will definitely be higher. To further determine the SQL problem, we have to start with the execution plan, as shown below:

The execution plan tells us that this query uses a full table scan Type=ALL, and the rows are very large (9950400) It can basically be judged that this is a "flavorful" SQL.

Get problem SQL

Different databases have different acquisition methods. The following is the slow query SQL acquisition tool for the current mainstream databases

• MySQL
  • Slow query log

  • Test tool loadrunner

  • Percona's ptquery and other tools

• Oracle
  • AWR report

  • Test tool loadrunner etc.

  • Related internal views such as v$, $session_wait, etc.

  • GRID CONTROL monitoring tool

• Dameng database
  • AWR report

  • Test tool loadrunner etc.

  • Dameng performance monitoring tool (dem)

  • Related internal views such as v$, $session_wait, etc.

SQL writing skills

SQL writing has the following general skills:

• Reasonable use of indexes

If there are few indexes, the query will be slow; if there are too many indexes, it will take up a lot of space, and when executing add, delete, or modify statements, The index needs to be maintained dynamically, which affects performance. If the selection rate is high (fewer duplicate values) and is frequently referenced by where, a B-tree index needs to be established;

Generally, join columns need to be indexed; it is more efficient to use full-text index for complex document type queries; The establishment of indexes should strike a balance between query and DML performance; when creating composite indexes, attention should be paid to queries based on non-leading columns

• Use UNION ALL instead of UNION

UNION ALL has higher execution efficiency than UNION. UNION needs to be deduplicated when executed; UNION needs to sort data

• Avoid select * writing

Optimize when executing SQL The processor needs to convert * into specific columns; each query must return the table, and covering indexes cannot be used.

• It is recommended to create an index for JOIN fields

Generally, JOIN fields are indexed in advance

• Avoid complex SQL statements

Improve readability; avoid the probability of slow queries; can be converted into multiple short queries and processed by the business end

• Avoid where 1=1 writing

• Avoid order by rand() similar writing style

RAND() causing the data column to be scanned multiple times

SQL optimization

Execution plan

Be sure to read the execution plan before completing SQL optimization. The execution plan will tell you where the efficiency is low and where optimization is needed. Let's take MYSQL as an example to see what the execution plan is. (The execution plan of each database is different and you need to understand it yourself)

##idEach is executed independently The operation identifier identifies the order in which the object is operated. The larger the id value, the first to be executed. If they are the same, the execution order is from top to bottomselect_typeIn query The type of each select clausetableThe name of the object being operated on, usually the table name, but there are other formatspartitionsMatching partition information (value is NULL for non-partitioned tables)typeType of join operationpossible_keysPossibly used indexeskeyThe index actually used by the optimizer (the most important column) The join types from best to worst are const, eq_reg, ref, range, index and ALL. When ALL appears, it means that the current SQL has a "bad smell"key_lenThe length of the index key selected by the optimizer, in bytesref indicates the reference object of the operated object in this row. No reference object is NULLrowsQuery The number of tuples scanned by the execution (for innodb, this value is an estimate)filteredPercentage of the number of tuples on the conditional table that is filteredextraImportant supplementary information of the execution plan, be careful when the words Using filesort, Using temporary appear in this column, it is likely that the SQL statement needs to be optimized

Field	Explanation

Next, we use a practical optimization case to illustrate the SQL optimization process and optimization techniques.

Optimization case

Table structure

CREATE TABLE `a`
(
    `id`          int(11) NOT NULLAUTO_INCREMENT,
    `seller_id`   bigint(20)                                       DEFAULT NULL,
    `seller_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
    `gmt_create`  varchar(30)                                      DEFAULT NULL,
    PRIMARY KEY (`id`)
);
CREATE TABLE `b`
(
    `id`          int(11) NOT NULLAUTO_INCREMENT,
    `seller_name` varchar(100) DEFAULT NULL,
    `user_id`     varchar(50)  DEFAULT NULL,
    `user_name`   varchar(100) DEFAULT NULL,
    `sales`       bigint(20)   DEFAULT NULL,
    `gmt_create`  varchar(30)  DEFAULT NULL,
    PRIMARY KEY (`id`)
);
CREATE TABLE `c`
(
    `id`         int(11) NOT NULLAUTO_INCREMENT,
    `user_id`    varchar(50)  DEFAULT NULL,
    `order_id`   varchar(100) DEFAULT NULL,
    `state`      bigint(20)   DEFAULT NULL,
    `gmt_create` varchar(30)  DEFAULT NULL,
    PRIMARY KEY (`id`)
);

Three tables are related to query the order status of the current user 10 hours before and after the current time, and sort them in ascending order according to the order creation time. The specific SQL is as follows

select a.seller_id,
       a.seller_name,
       b.user_name,
       c.state
from a,
     b,
     c
where a.seller_name = b.seller_name
  and b.user_id = c.user_id
  and c.user_id = 17
  and a.gmt_create
    BETWEEN DATE_ADD(NOW(), INTERVAL – 600 MINUTE)
    AND DATE_ADD(NOW(), INTERVAL 600 MINUTE)
order by a.gmt_create；

View data volume

Original execution time

Original execution plan

Initial optimization ideas

1. The where condition field type in SQL must be consistent with the table structure. The user_id in the table is varchar( 50) type, the actual int type used in SQL, there is implicit conversion, and no index is added. Change the user_id field in tables b and c to int type.

2. Because there is an association between table b and table c, create an index on the user_id of table b and table c

3. Because there is an association between table a and table b, Create an index on the seller_name field of tables a and b

4. Use composite index to eliminate temporary tables and sort

Initial optimization of SQL

alter table b modify `user_id` int(10) DEFAULT NULL;
alter table c modify `user_id` int(10) DEFAULT NULL;
alter table c add index `idx_user_id`(`user_id`);
alter table b add index `idx_user_id_sell_name`(`user_id`,`seller_name`);
alter table a add index `idx_sellname_gmt_sellid`(`gmt_create`,`seller_name`,`seller_id`);

View the execution time after optimization

View the execution plan after optimization

View warnings information

Continue to optimize alter table a modify "gmt_create" datetime DEFAULT NULL;

View execution time

View execution plan

Summary

1. View execution plan explain

2. If there is an alarm message, check the alarm message show warnings;

3. View the table structure and index information involved in SQL

4. Think about possible optimization points according to the execution plan

5. Perform table structure changes, add indexes, SQL rewrite and other operations according to possible optimization points

6. View the optimized execution time and execution plan

7. If the optimization effect is not obvious, repeat the fourth step

Related Recommended: "

mysql tutorial"

The above is the detailed content of Use examples to tell you how to optimize SQL. For more information, please follow other related articles on the PHP Chinese website!