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
- 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.
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 timesSQL optimizationExecution 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)
Field | Explanation |
---|---|
Each 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 bottom | |
In query The type of each select clause | |
The name of the object being operated on, usually the table name, but there are other formats | |
Matching partition information (value is NULL for non-partitioned tables) | |
Type of join operation | |
Possibly used indexes | |
The 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" | |
The length of the index key selected by the optimizer, in bytes | |
indicates the reference object of the operated object in this row. No reference object is NULL | |
Query The number of tuples scanned by the execution (for innodb, this value is an estimate) | |
Percentage of the number of tuples on the conditional table that is filtered | |
Important 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 |
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`) );
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;
- 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.
- Because there is an association between table b and table c, create an index on the user_id of table b and table c
- Because there is an association between table a and table b, Create an index on the seller_name field of tables a and b
- Use composite index to eliminate temporary tables and sort
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 execution plan explain
- If there is an alarm message, check the alarm message show warnings;
- View the table structure and index information involved in SQL
- Think about possible optimization points according to the execution plan
- Perform table structure changes, add indexes, SQL rewrite and other operations according to possible optimization points
- View the optimized execution time and execution plan
- If the optimization effect is not obvious, repeat the fourth step
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!

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics



How to optimize Discuz forum performance? Introduction: Discuz is a commonly used forum system, but it may encounter performance bottlenecks during use. In order to improve the performance of Discuz Forum, we can optimize it from many aspects, including database optimization, cache settings, code adjustment, etc. The following will introduce how to optimize the performance of the Discuz forum through specific operations and code examples. 1. Database optimization: Index optimization: Creating indexes for frequently used query fields can greatly improve query speed. For example

How to optimize the performance of SQLServer and MySQL so that they can perform at their best? Abstract: In today's database applications, SQLServer and MySQL are the two most common and popular relational database management systems (RDBMS). As the amount of data increases and business needs continue to change, optimizing database performance has become particularly important. This article will introduce some common methods and techniques for optimizing the performance of SQLServer and MySQL to help users take advantage of

The Linux operating system is an open source product, and it is also a practice and application platform for open source software. Under this platform, there are countless open source software supports, such as apache, tomcat, mysql, php, etc. The biggest concept of open source software is freedom and openness. Therefore, as an open source platform, Linux's goal is to achieve optimal application performance at the lowest cost through the support of these open source software. When it comes to performance issues, what is mainly achieved is the best combination of the Linux operating system and applications. 1. Overview of performance issues System performance refers to the effectiveness, stability and response speed of the operating system in completing tasks. Linux system administrators may often encounter problems such as system instability and slow response speed, such as

The core differences between Sybase and Oracle database management systems require specific code examples. Database management systems play a vital role in the field of modern information technology. As two well-known relational database management systems, Sybase and Oracle occupy an important position in the database field. important position. Although they are both relational database management systems, there are some core differences in practical applications. This article will compare Sybase and Oracle from multiple perspectives, including architecture, syntax, performance, etc.

The ANY keyword in SQL is used to check whether a subquery returns any rows that satisfy a given condition: Syntax: ANY (subquery) Usage: Used with comparison operators, if the subquery returns any rows that satisfy the condition, the ANY expression Evaluates to true Advantages: simplifies queries, improves efficiency, and is suitable for processing large amounts of data Limitations: does not provide specific rows that meet the condition, if the subquery returns multiple rows that meet the condition, only true is returned

SQLServer and MySQL Performance Tuning: Best Practices and Key Tips Summary: This article will introduce the performance tuning methods of two common relational database systems, SQLServer and MySQL, and provide some best practices and key tips to help developers and Database administrators improve the performance and efficiency of database systems. Introduction: In modern application development, database systems are an indispensable part. As the amount of data increases and user demands increase, the optimization of database performance becomes particularly important. SQ

With the rapid development of the Internet, data storage and processing are becoming more and more important. Therefore, relational databases are an integral part of modern software platforms. MySQL database has become one of the most popular relational databases because it is simple to use, easy to deploy and manage. However, MySQL database performance issues often become an issue when dealing with large amounts of data. In this article, we will delve into MySQL's SQL statement execution plan and introduce how to improve MySQL data by optimizing the query process.

MySQL is a widely used relational database management system commonly used for web application development and data storage. In practical applications, the underlying optimization of MySQL is particularly important, among which the advanced optimization of SQL statements is the key to improving database performance. This article will introduce some tips and best practices for implementing MySQL's underlying optimization, as well as specific code examples. Determine the query conditions When writing SQL statements, you must first clearly define the query conditions and avoid using unlimited wildcard queries, that is, avoid using "%" to open the query.
