In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-18 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
This article will explain in detail how to optimize the SQL sentence in MySQL, the content of the article is of high quality, so share it with you for reference. I hope you can get something after reading this article.
Structured query language (Structured Query Language) is referred to as SQL. Structured query language is a database query and programming language, which is used to access data and query, update and manage relational database systems. Sql statement is a language to operate the database.
Determine the problem SQL
The novice optimizes the sql statement and can debug it directly. The most direct and obvious way is to look at the execution time and optimize which section is the slowest.
When judging whether there is a problem with SQL, you can judge by two appearances:
At the system level, it appears that CPU consumes heavily, IO waits for serious page response time, and the log of the application times out.
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.
The SQL statement shows that the execution time is too long to get data from a full table scan. The rows and cost in the execution 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 log test tool loadrunnerPercona's ptquery and other tools OracleAWR report test tool loadrunner and other related internal views such as v$sql, v$session_wait, GRID CONTROL monitoring tools, AWR reporting test tools, loadrunner and other performance monitoring tools (dem) related internal views such as v$sql, 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 more space, so it is necessary to dynamically maintain the index when executing the add, delete and modify statements, which affects the performance.
B-tree index is needed for high selection rate (few repeated values) and frequently referenced by where; general join columns need to be indexed; full-text index is more efficient for complex document type queries; 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_ name` 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 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.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-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
Optimization summary
View the execution plan explain if there is alarm information, view the alarm information show warnings; view the table structure and index information involved in SQL according to the execution plan, think about the possible optimization points according to the possible optimization points to implement table structure change, add index, SQL rewrite and other operations to view the optimized execution time and execution plan
If the optimization effect is not obvious, repeat the fourth step.
These are the optimization methods of SQL statements in MySQL. Do you have anything to gain after reading them? If you want to know more about it, you are welcome to follow the industry information. Thank you for reading.
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.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.