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

This article is to share with you about how to optimize SQL query, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

We are committed to building a query that works well and lasts for a long time. The editor will give some suggestions on optimizing SQL statements.

1. Instead of using select * to query SQL, try to select a dedicated field.

Counterexample:

Select * from employee

Positive example:

Select id,name fromemployee

Reason:

By querying with only the necessary fields, you can save resources and reduce network overhead.

Doing so may not use an overlay index and will cause a query to be returned to the table.

two。 If only one query result is known, limit 1 is recommended

Suppose you have an employee form in which you want to find an employee named jay.

CREATE TABLE employee (id int (11) NOT NULL, name varchar (255) DEFAULT NULL, age int (11) DEFAULT NULL, date datetime DEFAULT NULL, sex int (1) DEFAULT NULL, PRIMARY KEY (`id`))

Counterexample:

Select id,name from employeewhere name='jay'

Positive example:

Select id,name from employeewhere name='jay' limit 1

Reason: after adding limit 1, when the corresponding records are found, the search will not continue, and the efficiency will be greatly improved.

3. Try to avoid using or to join conditions in the where clause

Create a new user table with a regular index userId, which is structured as follows:

CREATE TABLE `user` (`id` int (11) NOT NULL AUTO_INCREMENT, `userId` int (11) NOT NULL, `age` int (11) NOT NULL, `name` varchar (255) NOT NULL, PRIMARY KEY (`id`), KEY `userId` (`userId`))

Now, assuming that you need to query a user whose userid is 1 or 18 years old, it would be easy to use the following SQL.

Counterexample:

Select * from user where userid = 1 or age = 18

Positive example:

/ / se union all select * from user where userid=1 union all select * from user where age = 18 more write two separate SQL select * from user where userid=1

Reason: the use of or may invalidate the index, so a full table scan is required.

In the case where or has no index, it is assumed that the userId index has been used, but when it comes to the age (age) query condition, a full table scan must be performed, which is divided into three steps: full table scan + index scan + merge.

4. Avoid using the! = or operator in the where clause whenever possible, otherwise the engine will abandon the index and perform a full table scan.

Counterexample:

Select age,name from user where age18

Positive example:

/ / You can consider separate two sql writeselect age,name from user where age 18

Reason: use! = and may invalidate the index.

5. Optimize limit paging

Limits is usually used to implement daily paging, but when the offset is very large, the query efficiency will be reduced. Because Mysql does not skip the offset, it gets the data directly.

Counterexample:

Select id,name,age from employeelimit 10000,10

Positive example:

/ / Solution 1: Return the largest record (offset) of the last query select id,name from employeewhere id > 10000 limit 10 * * limit *

Reason:

If optimization scenario 1 is used, the last query record (offset) is returned, so the offset can be skipped and the efficiency will naturally be greatly improved.

Option 2: use + index sorting, which can also improve query efficiency.

6. Optimize like statement

In daily development, if we use fuzzy keyword queries, it is easy to think of like, but like may invalidate the index.

Counterexample:

Select userId,name from user where userId like'3'

Positive example:

Select userId,name from user where userId like '123%'

Reason: https://medium.com/@pawanjain.432/hey-thanks-dovid-for-pointing-out-a-typo-in-13-1000a4103fe6

7. Use where conditions to restrict the data to be queried to avoid returning additional rows

Suppose you want to query whether a user is a member, as the old-fashioned execution code would do.

Counterexample:

List userIds = sqlMap.queryList ("select userId from userwhere isVip=1"); boolean isVip= userIds.contains (userId)

Positive example:

Long userId= sqlMap.queryObject ("select userId from user whereuserId='userId' and isVip='1'") boolean isVip= userId! = null

Reason: be able to check the data you need, avoid returning unnecessary data, and save money and computer overhead.

8. Consider using default values instead of null in the where clause

Counterexample:

Select * from user where age is not null

Positive example:

Select * from user where age > 0; / / Set 0 as default

Reason: if you replace null values with default values, you can usually build an index, and at the same time, the expression will be relatively clear.

9. If you insert too much data, you can consider bulk insert

Counterexample:

For (User u: list) {INSERT into user (name,age) values (# name#,#age#)}

Positive example:

/ / One batch of 500 inserts, carried out in batchesinsert intouser (name,age) values (# {item.name}, # {item.age})

Reason: batch insertion has good performance and saves time.

For example, with an elevator, you need to move 10,000 bricks to the top of the building. The elevator can place the appropriate number of bricks at a time (up to 500). You can choose to transport one brick at a time, or 500 at a time. Which plan is better?

10. Use the distinct keyword carefully

The Distinct keyword is often used to filter duplicate records to return unique records. When it is used to query one or more fields, the Distinct keyword will optimize the query. However, in the case of too many fields, Distinct keywords will greatly reduce the query efficiency.

Counterexample:

SELECT DISTINCT * from user

Positive example:

Select DISTINCT name from user

Reason: CPU time and occupancy time with "distinct" statements are higher than those without "distinct" statements.

If you use distinct when querying multiple fields, the database engine compares the data and filters out duplicate data. However, this comparison and filtering process will consume system resources and CPU time.

11. Delete redundant and duplicate indexes

Counterexample:

KEY `idx_ UserId` (`userId`) KEY `Userage` (`userId`, `age`)

Positive example:

/ / Delete the userId index, because the combined index (A, B) is equivalentto creating the (A) and (A, B) indexesKEY `idx_userId_ age` (`userId`, `age`)

Reason: if duplicate indexes are retained, the optimizer also needs to consider them one by one when optimizing the query, which can affect performance.

twelve。 If the amount of data is large, optimize the modify or delete statement

Avoid modifying or deleting too much data at the same time, as it will cause CPU utilization to be too high, thus affecting others' access to the database.

Counterexample:

/ / Delete 100000 or 1 million+ at a time? Delete from user where id

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.