Career transition : 21 Tips for SQL Performance Optimization (Part 1）
Catalogue
1、Inquire SQL try not to use select *, but specific fields
2、 Avoid using or in the where clause to connect conditions
3、Use numeric values instead of character string as much as possible
4、Use varchar instead of char
5、Technical extension, the differences between char and varchar2?
6、Using default value instead of null in where
7、 Avoid using the where clause with ! = or <> operator
8、inner join, left join, right join, give priority to using inner join
9、 Improve the efficiency of the group by statement
10、When emptying the table, give priority to the use of truncate
11、 Operate delete or update statement, add a limit or loop to delete in batches
12、UNION operator
13、 Batch insertion performance improvement
14、 Not too many table joins, not too many indexes, generally within 5
15、Avoid using built-in functions on index columns
16、the combination of indexes
17、 composite index leftmost feature
18、 optimize like statement
19、Use explain to analyze your SQL execution plan
20、Some other optimizing ways
21、Mind Map
1、Inquire SQL try not to use select *, but specific fields
1、counter example
1｜SELECT * FROM user
2、positive example
1｜SELECT id,username,tel FROM user
3、Reason
1.Save resources, reduce network expense.
2.Possibly using covering indexes to reduce table returns and improve query efficiency.
Note: To save time, the sample fields below have been replaced with *.
2、 Avoid using or in the where clause to connect conditions
1、counter example
1｜SELECT * FROM user WHERE id=1 OR salary=5000
2、positive example
(1)use UNION ALL
1| SELECT * FROM user WHERE id=1
2| UNION ALL
3| SELECT * FROM user WHERE salary=5000
(2) Write two separate sql
1｜SELECT * FROM user WHERE id=1
2｜
3｜SELECT * FROM user WHERE salary=5000
3. Reasons
1. the use of or may invalidate the index, and thus the full table scan.
2. for or without the index of the salary this case, assume that it takes the index of id, but when it goes to the salary query conditions, it still has to full table scan.
3. that is to say, the whole process requires three steps: full table scan + index scan + merge. If it had started with a full table scan, it is done with one scan directly.
4. although mysql has an optimizer, considered in terms of efficiency and cost, encountering or conditions, the index may still be invalid;.
3、Use numeric values instead of character string as much as possible
1、positive example
1. primary key (id): primary key gives priority to the use of numeric type int, tinyint
2. gender (sex): 0 represents female, 1 represents male; database does not have boolean type, mysql is recommended to use tinyint
2、Reason
1. Because the engine compares each character in the character string one by one when processing queries and joins.
2. While for numeric types it is enough to compare only once.
3. Characters will reduce the performance of queries and joins and will increase the storage expense.
1. varchar variable-length fields are stored according to the actual length of the data content, with small storage space, which can save storage space.
2. char is stored according to the declared size, insufficient to fill in the spaces.
3. next for queries, search within a relatively small field, more efficient.
5、Technical extension, the difference between char and varchar2?
1. The length of char is fixed, while the length of varchar2 can be changed.
For example,Store String "101", for char(10), it means that the character you store will take up 10 characters (including 7 empty characters), and it is occupied by spaces in the database, while the same varchar2(10) only takes up 3 bytes of length, 10 is just the maximum, when the character you store is less than 10, it will be stored according to the actual length.
2、char is slightly more efficient than varchar2. 3、When do we use char and when do we use varchar2? char and varchar2 is more efficient than varchar2.
3、When do we use char and when do we use varchar2?
char and varchar2 is a contradictory unity, the two are complementary relationship, varchar2 is less space-saving than char, but a little less efficient than char, if you want to get efficiency, you must sacrifice a little space, this is what we often say in the database design " use the space in exchange for efficiency".
Although varchar2 is more space-saving than char, but if a varchar2 column is frequently modified, and each time the length of the modified data is different, this will cause "Row Migrating" phenomenon, and this causes extra I/O, which is the one to avoid in the database design, it is better to use char instead of varchar2 in this case. char will also automatically fill in the spaces, because you insert to a char field automatically supplemented by spaces, but select after the space is not deleted, so when querying the char type you must remember to use trim, which is the reason for writing this article.
If a developer refines the use of the rpad() trick to convert a bind variable to some type that can be compared to a char field (of course, it is better to fill the bind variable than to truncate the trim database column, since applying the function trim to a column can easily result in not being able to use the existing index on that column), it may have to take into account that changes in the column length over time. If there is a change in the size of the field, the application will be affected because it will have to modify the field width.
It is for the above reasons that fixed-width storage space can lead to tables and associated indexes that are much larger than usual, and is accompanied by problems with bind variables, so it is important to avoid using char types no matter what circumstances.
6、Using default value instead of null in where
1、counter example
1|SELECT * FROM user WHERE age IS NOT NULL
2、positive example
1|SELECT * FROM user WHERE age>0
3、reason
1. it is not that if one uses is null or is not null, one will not go to the index, this is related to mysql version and the cost of the query.
2. If the mysql optimizer finds that the cost of going to the index is higher than not going to the index, it will give up the index, these conditions ! =, <>, is null, is not null are often considered to invalidate the index.
3. in fact, it is because in general, the cost of the query is high, the optimizer automatically gives up the index
4. if the null value, replaced by the default value, many times let the go index become possible, at the same time, the expressed meaning is relatively clear a little;.
7、 Avoid using the != or <> operator in the where clause. = or <> operator
1. Counter example
1｜SELECT * FROM user WHERE salary!=5000
2｜
3｜SELECT * FROM user WHERE salary<>5000
2、Reason
1.Using ! = and <> is likely to invalidate the index
2.You should try to avoid using the ! = or <> operator, otherwise the engine will give up using the index and perform a full table scan
3.Achieve business priority, there is really no choice but to use, not unable to use
8、inner join, left join, right join, give priority to using inner join
If the results of the three joins are the same, the priority is to use inner join, if you use left join the left table should be as small as possible.
• inner join within connect, only retaining the exact matching result set in the two tables.
• left join will return all rows of the left table, even if there are no matching records in the right table.
• right join will return all rows of the right table, even if there are no matching records in the left table.
Why?
• if inner join is an equal-join, it returns fewer rows, so performance is relatively better.
• uses a left join, the left table data result is as small as possible, and the conditions are placed on the left side of the process as much as possible，meaning that the number of rows returned may be less.
• this is the mysql principle of optimality that small tables drive large tables, and small data sets drive large data sets, thus allowing better performance;
9、 Improve the efficiency of the group by statement
1、Counter example
Group first, then filter
1｜select job, avg(salary) from employee
2｜group by job
3｜having job = 'develop' or job = 'test';
2、Positive example
Filter first, group later
1｜select job, avg(salary) from employee
2｜where job = 'develop' or job = 'test'
3｜group by job;
3、Reason
You can filter out the unwanted records before executing to this statement.
10、When emptying the table, give priority to the use of truncate
truncate table is functionally the same as the delete statement without the where clause: both delete all the rows in the table. But truncate table is faster than delete and uses less system and transaction journal resources.
The delete statement deletes one row each time and records an item in the transaction log for each row deleted. A truncate table deletes data by releasing the data pages used to store the table data and records the page release only in the transaction log.
truncate table removes all rows from the table, but the table structure and its columns, constraints, indexes, etc. remain unchanged. The count value used for the new row identifier is reset to the seed of the column. If you want to keep the identification count value, use DELETE instead. to delete the table definition and its data, use the drop table statement.
For a table referenced by a foreign key constraint, you cannot use truncate table; instead, use the DELETE statement without the where clause. Since truncate table is not recorded in the log, it does not activate triggers.
A truncate table cannot be used for a table that participates in an indexed view.
Link for the original article: https://blog.csdn.net/guorui_java/article/details/126542005
直连行业大牛导师，1v1模拟面试与求职指导
实战与求职精品课程
2000+名企面试真题
