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

Editor to share with you how to optimize SQL, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Determine the problem SQL

When judging whether there is a problem with SQL, you can judge by two appearances:

System level representation

Serious CPU consumption

IO waiting is serious

The page response time is too long.

Errors such as timeout occurred in the applied log

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

SQL statement representation

Tedious

The execution time is too long

Get data from a full table scan

The rows and cost in the implementation plan are very large.

Long SQL is easy to understand, a SQL too long reading will certainly be poor, and the frequency of problems will certainly be higher. To further determine the SQL problem, you need to start with the execution plan, as follows:

The execution plan tells us that this query has gone the full table scan Type=ALL,rows is very large (9950400) it can basically be judged that this is a "flavor" SQL.

Get question SQL

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

MySQL

Slow query log

Test tool loadrunner

Percona's ptquery and other tools

Oracle

AWR report

Testing tools loadrunner, etc.

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

GRID CONTROL monitoring tool

Dameng database

AWR report

Testing tools loadrunner, etc.

Damon performance Monitoring tool (dem)

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

Writing skills of SQL

There are several common techniques for writing SQL:

Rational use of index

The index is less and the query is slow; the index takes up a lot of space, so it is necessary to dynamically maintain the index when executing the add, delete and modify statements, which affects the performance and has a high selection rate (less repeated values) and is frequently referenced by where and needs to build a B-tree index.

General join columns need to be indexed; full-text indexing for complex document types is more efficient; index establishment 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

The execution efficiency of UNION ALL is higher than that of UNION, and the weight of UNION needs to be sorted; UNION needs to sort the data.

Avoid select * writing

When executing SQL, the optimizer needs to convert * to a specific column; each query returns to the table and cannot overwrite the index.

The JOIN field is recommended to be indexed

Generally speaking, 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 side

Avoid writing where 1. 1

Avoid similar writing of order by rand ()

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

SQL optimization execution plan

To complete SQL optimization, be sure to read the execution plan first, which will tell you where it is inefficient and where optimization is needed. Let's take MYSQL as an example to see what the execution plan is. (each database has a different execution plan, which needs to be understood by itself.)

The field explains the identity of each operation performed independently by the id, identifying the order in which the object is manipulated. The higher the id value, the first to be executed. If the same, the execution order is from top to bottom. The type of each select sentence in the select_type query table is the name of the object, usually the table name. But there are other formats of partitions matching partition information (NULL for non-partition table values) the type of type join operation possible_keys may use the index key optimizer actually uses the index (the most important column) the best to the worst join types are const, eq_reg, ref, range, index, and ALL. When ALL appears, it indicates that there is a "bad smell" in the current SQL. The length of the index key selected by the optimizer in bytes ref represents the reference object of the object being operated on this row, and the non-reference object is the number of tuples scanned by the NULLrows query execution (for innodb, this value is the estimated value) the number of tuples filtered on the filtered condition table is more important than the supplementary information of the extra execution plan. Be careful when the words Using filesort and Using temporary appear in this column. It is very likely that the SQL statement needs to be optimized.

Next, we use an actual optimization case to illustrate the process and skills of SQL optimization.

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_ name` 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 associated to query the current user's order status for 10 hours before and after the current time, and arranged 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.statefrom a, b, cwhere 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-600MINUTE) AND DATE_ADD (NOW (), INTERVAL 600MINUTE) order by a.gmt_create

View amount of data

Original execution time

Original execution plan

Preliminary optimization idea

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

Because there is an association between table b and table c, user_id the tables b and c to create an index

Because there is an association between table an and table b, the seller_name fields of tables an and b are indexed

Using composite indexes to eliminate temporary tables and sorting

Preliminary optimization of SQL

Alter table b modify `user_ id` int (10) DEFAULT NULL;alter table c modify `idx_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` (`gmt_ id`, `seller_ name`); alter table an 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 the execution plan

The above is all the contents of the article "how to optimize SQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.