Summary of mybatis usage and SQL statement optimization

Time:2021-3-5

[Abstract] as an excellent persistence layer framework, mybatis supports custom SQL, stored procedures and advanced mapping.

1、 Mybatis summary

1.mybatisIf the query / UPDATE statement fails to find a qualified record, what will be returned?

If the return type is string, null is actually returned;

The return type is object: null is actually returned;

The return type is set such as list: the actual return is empty set [];

The return type is Boolean: the actual return is false

When the database statement insertion conditions are not met, false will be returned, such as using dual SQL statements;

2.mybatisWhat common exceptions are thrown?

Not all exceptions think that an exception should be reported,

For example, if a user repeatedly collects goods, he can return success directly. At this time, the exception of “violation of unique key” should be handled specially.

(1) Insert statement

Dataintegrity violationexception: violation of non NULL constraint, data size exceeding constraint

Duplicatekeyexception: single key constraint violation

Cannot acquire lockexception: for update nowait timeout

(2) UPDATE statement

If the condition is not satisfied, false will be returned

Database operation should judge the return value, such as the following bug:

(3) Bug class, table field not found, etc

MyBatisSystemException

BadSqlGrammarException

3. Applying for update in the transaction seems to bypass the transaction

After for update gets the lock, the latest data is selected

 4.for updateIs a row level lock, also known as exclusive lock

Once a user applies a row level lock to a row, the user can query and update the locked data row. Other users can only query but not update the locked data row;

If the query condition has a primary key, the row data will be locked; if not, the table will be locked.

If you must use for update, it is recommended to add nowait or for update wait 3

2、 SQL optimization

  1. Constraints: add constraints to the data table to prevent dirty data.

2. Limit 1: if we know that there is only one returned result, we can use limit 1 to tell the select statement that it only needs to return one record. The advantage is that select does not need to scan the complete table, it only needs to retrieve a qualified record to return.

3. Spelling style: reserved words in SQL use English upper case, others use English lower case; improve readability.

4. Like: the use of like and wildcards may make the index invalid and trigger a full table scan. If you want the index to take effect, you can’t start with (%) after “like”. For example, if you use “like too%” or “like too%”, you will scan the whole table. If like ‘too%’ is used and the retrieved fields are indexed at the same time, the full table scan will not be performed.

5. Adding indexes to the fields commonly used in search can greatly increase the query efficiency

6. Using self join is better than subquery.

7. Use view:

A view can be understood as giving an alias to a query SQL.

It’s just that the view can’t pass in variables and don’t save data after being compiled in advance

The advantage of a view is that it isolates data table operations

8. Using temporary tables:

9. In / exist uses similar two-tier for loop and follows the principle of small table driving large table.

If one of the two tables is small and the other is large, use exists for the large subquery table and in for the small subquery table, for example, table a (small table) and table B (large table)

Select * from a where CC in (select CC from b); / / the efficiency is low, and the index of CC column in a table is used;

Select * from a where exists (select CC from b where CC = a.cc); / / high efficiency, using the index of CC column in B table.

10. The use of index is not recommended:

(1) The total amount of data is very small.

(2) The data is highly repetitive, and different values are evenly distributed, for example, the ratio of male to female is close to 50%.

11. When there are three fields in the unique index / key, is it more efficient to query by one of them than without index?

—-No, in this case, creating a unique key is more to ensure the correctness of the data.

(1) What’s the difference between a unique index and a unique key?

(2) When creating a union index, we need to pay attention to the order of creation, because the efficiency of union index (x, y, z) and (Z, y, x) may be different. For example, (x, y, z), if the query condition is “where x = 1 and y = 2 and z = 3, it can match the union index; if the query condition is” where y = 2, it cannot match the union index.

12. Connection table:

(1) As far as possible, the number of join tables should not be more than 3, because every additional table is equivalent to adding a nested loop, and the order of magnitude growth will be very fast, seriously affecting the efficiency of the query.

(2) Create an index on the field used for the join, and the field must be of the same type in multiple tables. Like “user”_ ID “in” product “_ Both the comment table and the user table are of type “int (11), but not one of type” int “and the other of type” varchar “.

14. Index failure

(1) In the ﹣ where ﹣ clause, if the condition column before ﹣ or ﹣ is indexed and the condition column after ﹣ or ﹣ is not indexed, the index will be invalid.

(2) Try to set the index column to “not null” constraint.

15. Don’t use bool type for data table field, use INT2 instead of bool type to increase expansion ability

This article is shared from the Huawei cloud community “summary of mybatis usage and SQL statement optimization”, the original author: move bricks to play the game.

Click follow to learn about Huawei’s new cloud technology for the first time~