Skip to main content

Data Manipulation

info

Data manipulation in SQL includes DML (Data Manipulation Language) statements and DQL (Data Query Language) statements, which mainly involve operations such as data insertion (INSERT), querying (SELECT), updating (UPDATE), and deletion (DELETE). Improper writing of these data manipulation statements may lead to various issues in correctness, security, maintainability, and performance of SQL. PawSQL has designed a total of 85 audit rules for data manipulation statements from these four perspectives.

Correctness Rules​

1. INSERT...VALUES Columns and Values Count Consistency​

Rule Description​

The INSERT...VALUES statement establishes a correspondence between columns and values based on their order. If the counts do not match, the statement is incorrect.

Default Alert Level​

  • Prohibited

Conditions​

  • INSERT...VALUES columns are explicitly specified
  • The number of columns and values in INSERT...VALUES is consistent

2. Avoid Constant Strings with Leading or Trailing Spaces​

Rule Description​

Spaces at the beginning and end of a string are usually meaningless and are often the result of developer typos. However, they can affect the results of SQL queries and thus require special attention.

Default Alert Level​

  • Warning

Conditions​

  • Constant strings contain leading or trailing spaces
  • Constant strings appear in conditional statements

3. Avoid COUNT DISTINCT on Multiple Nullable Columns​

Rule Description​

When using COUNT(DISTINCT) for multiple columns, the result may differ from your expectation. COUNT(DISTINCT col) counts the number of distinct non-NULL rows in that column, while COUNT(DISTINCT col, col2) will exclude any rows where either column is NULL.

For example, in the following query, counting distinct values for column a and the combination of columns (a, b):

select count(distinct t.a) a_cnt, count(distinct t.a,t.b) a_b_cnt 
from (values 
      row(1,2),
      row(3,null)) as t(a,b);

The result for column a is 2, while for the combination of columns (a, b) it is 1, which may not meet your expectations and requires special attention.

Default Alert Level​

-提示

Conditions​

  • SQL contains the COUNT DISTINCT function
  • COUNT parameters include two or more expressions
  • At least one parameter can be NULL

4. Prohibit Common SQL Injection Functions​

Rule Description​

SQL injection is a common web attack technique that exploits insecure input validation and constructs SQL queries to gain unauthorized information or inject malicious code into databases. Common SQL injection functions include database(); user(); version(); sleep(); etc.

Default Alert Level​

  • Prohibited

Conditions​

  • User-configured function names exist in SQL

5. Prohibit GROUP BY on Non-Integer Constants​

Rule Description​

Grouping by non-integer constants is meaningless and will cause a syntax error in PostgreSQL, but not in MySQL, requiring special attention.

SELECT l_orderkey, sum(l_quantity) 
FROM lineitem
GROUP BY '1';

Default Alert Level​

  • Prohibited

Conditions​

  • Grouping field is a non-integer constant

6. Avoid LIMIT Clause in UPDATE/DELETE​

Using LIMIT in UPDATE statements can lead to unpredictable data updates and should be used with caution. PawSQL will check for such constructions and provide reminders.

Default Alert Level​

  • Warning

Conditions​

  • Statement is UPDATE or DELETE
  • Statement uses LIMIT clause

7. Avoid LIMIT Without ORDER BY in UPDATE/DELETE​

Using LIMIT without ORDER BY in UPDATE or DELETE statements can lead to inconsistent results each time the statement is executed. PawSQL will check for such constructions and provide reminders.

PostgreSQL/Opengauss does not support the use of ORDER BY clause in UPDATE or DELETE statements.

Default Alert Level​

  • Warning

Conditions​

  • Statement is UPDATE or DELETE
  • Statement uses LIMIT clause without using ORDER clause

8. Avoid Unconditional UPDATE/DELETE Statements​

UPDATE or DELETE statements without query conditions or with always-true conditions can update or delete all data records, which is a very dangerous operation. PawSQL will check for such constructions and provide reminders.

Default Alert Level​

  • Warning

Conditions​

  • Statement is UPDATE or DELETE
  • Statement has no conditions

9. Prohibit ORDER BY on Non-Integer Constants​

Ordering by non-integer constants is meaningless and will cause a syntax error in PostgreSQL, but not in MySQL, requiring special attention.

SELECT l_orderkey 
FROM lineitem
ORDER BY '1';

Default Alert Level​

  • Warning

Conditions​

  • Sorting field is a non-integer constant

10. INSERT Statements Must Include Primary Key Fields​

For tables without auto-incrementing primary keys, even if they have default values, the primary key value should be specified when inserting data.

Default Alert Level​

  • Warning

Conditions​

  • Table has a primary key
  • Primary key is a non-auto-incrementing column

11. Avoid USING Clause in UPDATE/DELETE​

Using ORDER in UPDATE/DELETE statements is meaningless. Therefore, PawSQL will check for such constructions and provide reminders.

Default Alert Level​

  • Warning

Conditions​

  • Statement is UPDATE or DELETE
  • Statement uses ORDER clause

12. INSERT Statements Prohibit Use of SYSDATE Function​

When the SYSDATE() function is used in a master-slave environment based on the STATEMENT mode, it may cause data inconsistency because there is a time difference from the execution of the statement on the master to the transmission of the log to the slave, resulting in different time values when executed on the slave.

Default Alert Level​

  • Warning

Conditions​

  • SYSDATE function is used as a value in the INSERT statement
  • MySQL database

13. Avoid LIMIT Without ORDER BY in SELECT​

Using LIMIT without ORDER BY in SELECT statements can lead to inconsistent results each time the statement is executed. PawSQL will check for such constructions and provide reminders.

Default Alert Level​

  • Warning

Conditions​

  • Using LIMIT in SELECT statement without ORDER BY

14. Avoid LIKE Queries Without Wildcards​

LIKE queries without wildcards are logically equivalent to equality queries, and it is recommended to use equality queries instead. Moreover, such queries without wildcards are usually the result of developer errors and may not meet the expected business logic implementation. Developers need to pay special attention.

Default Alert Level​

  • Warning

Conditions​

  • SQL contains LIKE query conditions
  • The right operand of LIKE does not contain '%' or '_' wildcards

Maintainability Rules​

1. Aliases Should Be Used for Multiple Tables in a Query Block​

Rule Description​

If multiple tables are referenced in a query block, it is recommended to give each table a simple and recognizable alias and prefix all fields with the alias for easier code reading and subsequent maintenance.

Default Alert Level​

  • Notice

Conditions​

  • Multiple table references exist in a query block

2. INSERT...VALUES Should Specify Column Names​

Rule Description​

INSERT...VALUES statements should specify column names to reduce the possibility of misalignment between the inserted values and the target table columns.

insert into customer value(1, 'Dan', 'Mike');

The following writing style can reduce the possibility of misalignment between the inserted values and the target table columns, making the code easier to maintain.

insert into customer(c_custkey, lastname, firstName) value(1, 'Dan', 'Mike');

Default Alert Level​

  • Notice

Conditions​

  • INSERT...VALUES statement does not specify column names

3. Avoid Duplicate Aliases for Table References​

Rule Description​

The existence of table references/subqueries with the same alias or table aliases that are the same as other table names can greatly deteriorate code readability and hinder subsequent maintenance.

Default Alert Level​

  • Warning

Conditions​

  • Duplicate alias table references/subqueries exist
  • Or table aliases are the same as other table names

4. Avoid GROUP BY by Column Sequence Number​

Rule Description​

In SQL development, selecting column sequence numbers instead of column names in GROUP BY can provide some convenience for developers, but it also affects code readability and maintainability.

Default Alert Level​

  • Notice

Conditions​

  • Using column sequence numbers for GROUP BY

5. Avoid Using NATURAL JOIN​

Rule Description​

NATURAL JOIN is a special type of equijoin that can be used with inner joins, outer joins, and full joins. It automatically searches for all columns with the same name and type in two tables and performs an equijoin based on these columns. NATURAL JOIN can simplify statements, but implicit join conditions reduce code readability and understanding of table relationships, and misconnections can easily occur. PawSQL provides risk warnings for statements using NATURAL JOIN to avoid correctness issues.

Default Alert Level​

  • Warning

Alert Conditions​

  • NATURAL JOIN syntax appears in SQL statements

6. Avoid ORDER BY by Column Sequence Number​

Rule Description​

In SQL development, selecting column sequence numbers instead of column names in ORDER BY can provide some convenience for developers, but it also affects code readability and maintainability.

Default Alert Level​

  • Notice

Conditions​

  • Using column sequence numbers for ORDER BY

7. Owner Should Be Added Before Objects​

Rule Description​

In SQL development, adding the table or subquery identifier to which each referenced object belongs can improve the readability of SQL statements and their maintainability in the later stage .

Default Alert Level​

  • Notice

Conditions​

  • There are field or expression references without owner identifiers

8. Avoid Using STRAIGHT JOIN​

Rule Description​

STRAIGHT JOIN is a table join method in MySQL that forces table joins to be done in the order defined by the table definitions, which is equivalent to an inner join. It gives developers some control over the execution of SQL by the database. However, it also loses the optimization brought by the optimizer for the order of table joins and should be used cautiously according to the scenario. PawSQL provides risk warnings for statements using STRAIGHT_JOIN to avoid performance issues.

Default Alert Level​

  • Warning

Conditions​

  • STRAIGHT_JOIN syntax appears in SQL statements

9. Suggest Using <> Instead of '!='​

Rule Description​

'!=' is a non-standard operator, while <> is the standard not equal operator in SQL. To improve the portability and standardization of SQL, it is recommended to use <> instead of '!='.

Default Alert Level​

  • Warning

Alert Conditions​

  • The '!=' operator appears in SQL statements

Rule Description​

Through parameter binding, the execution plan of SQL can be reused, reducing the parsing time of SQL; it can also avoid SQL injection and improve the security of the application.

Default Alert Level​

  • Warning

Alert Conditions​

  • When the user sets the 'strict' mode, any constants appear in the fields of conditions and sorting
  • When the user sets the 'loose' mode, there are no parameter bindings in the fields of conditions and sorting

11. SQL Length Exceeds Threshold​

Rule Description​

Overly long SQL statements have poor readability, are difficult to maintain, and can easily cause performance issues; if the length of the SQL exceeds the threshold set by the user, this rule is triggered. The specific rule threshold can be adjusted according to business needs, default value: 1024.

Default Alert Level​

  • Enhancement

Alert Conditions​

  • SQL text length exceeds the threshold

Performance Rules​

1. GROUP BY Fields with Expressions Leading to Index Ineffectiveness​

Rule Description​

Databases can utilize the orderliness of indexes to avoid sorting of columns in the GROUP BY clause, thereby enhancing SQL performance. However, if the GROUP BY field is an expression or function, the index may not be used for sorting.

Default Alert Level​

  • Notice

Conditions​

  • There are grouping fields, and the field is an expression or function

2. ORDER BY Fields with Expressions Leading to Index Ineffectiveness​

Rule Description​

Databases can utilize the orderliness of indexes to avoid sorting of columns in the ORDER BY clause, thereby enhancing SQL performance. However, if the ORDER BY field is an expression or function, the index may not be used for sorting.

Default Alert Level​

  • Notice

Conditions​

  • There are sorting fields, and the field is an expression or function

3. Avoid Using CROSS JOIN​

Rule Description​

CROSS JOIN will perform a Cartesian product of every row from the first table with every row from the second table. It generates a number of records equal to the product of the number of rows in table 1 and table 2. Theoretically, it is equivalent to an inner join with a condition of 1=1. CROSS JOIN can quickly splice multiple tables, but it generates a large number of records, leading to low efficiency; and without specifying join conditions, the results may not make practical sense.

PawSQL provides risk warnings for statements using STRAIGHT_JOIN to avoid performance issues.

Example SQL is as follows

SELECT *
FROM lineitem
CROSS JOIN orders;

This will combine every record in the lineitem table with all records in the orders table.

Default Alert Level​

  • Warning

Conditions​

  • CROSS JOIN syntax appears in SQL statements

4. Implicit Type Conversion Leading to Index Ineffectiveness​

Rule Description​

When the data types of conditional expressions are different, some implicit data type conversions will be made during the query execution process. Type conversions may be applied to constants in the conditions, or sometimes to columns. When type conversion is applied to columns, the index cannot be used during the query execution period, which may lead to severe performance issues. For example, in the following SQL,

select count(*) from ORDERS where O_ORDERDATE = current_date();

If the data type of the O_ORDERDATE column is CHAR(16), then the index on O_ORDERDATE will not be used, leading to a full table scan. The solutions are usually twofold: one is to ALTER TABLE to change the data type of O_ORDERDATE, and the other is to force current_date to be converted to CHAR type (PawSQL provides this rewrite suggestion).

 select count(*) ORDERS where ORDERS.O_ORDERDATE = cast(current_date() as CHAR(16));

Default Alert Level​

  • Warning

Conditions​

  • Conditional expression is a filtering condition and is an indexable filtering condition
  • The data types on both sides of the filtering condition are different
  • According to the database type conversion priority, the database will prioritize converting columns over constants

5. Join Field Type Mismatch Leading to Index Ineffectiveness​

Rule Description​

When the data types of conditional expressions are different, some implicit data type conversions will be made during the query execution process. When type conversion is applied to columns, the index cannot be used during the query execution period, which may lead to severe performance issues. PawSQL will check for type mismatched join conditions and provide reminders.

Default Alert Level​

  • Warning

Conditions​

  • Conditional expression is an association condition and is an indexable association condition
  • The data types on both sides of the filtering condition are different

6. Different Sorting Directions in ORDER BY Leading to Index Ineffectiveness​

Rule Description​

All expressions in the ORDER BY clause need to be sorted in a unified ASC or DESC direction to utilize the index to avoid sorting; if the ORDER BY statement uses different directions for multiple different conditions, the index cannot be used.

Default Alert Level​

  • Notice

Conditions​

  • There are multiple sorting fields
  • There are two sorting directions

7. Avoid GROUP BY Fields from Different Tables​

Rule Description​

If the grouping fields come from different tables, the database optimizer will not be able to utilize the orderliness of indexes to avoid sorting. If there are equiconditions in the WHERE or HAVING clauses, PawSQL can replace the sorting or grouping fields to make them come from the same table, thus being able to use the index to avoid sorting. For example, in the following query

select o_custkey, c_name, sum(o.O_TOTALPRICE) from customer c, orders o where o_custkey = c_custkey group by o_custkey, c_name

The grouping fields o_custkey, c_name come from two tables, and there is a filtering condition o_custkey = c_custkey, which can be rewritten as

select c_custkey, c_name, sum(o.O_TOTALPRICE) from customer c, orders o where o_custkey = c_custkey  group by c_custkey, c_name

Default Alert Level​

  • Notice

Conditions​

  1. GROUP BY fields come from different tables

  2. Filtering conditions are indexable conditions

  3. No calculations or functions on index columns

8. Avoid Grouping on Long Fields​

Rule Description​

In databases, grouping is usually done through sorting or hashing. If the number of rows to be grouped is relatively large, the length of a single field will significantly affect the grouping efficiency. This rule can compare whether the length of the grouping field exceeds the threshold input by the user. If it exceeds the threshold, an alert will be triggered.

Default Alert Level​

  • Alert

Conditions​

  1. GROUP field type is CHAR/VARCHAR and the field length exceeds the threshold
  2. Or GROUP field type is CLOB/TEXT/TINYTEXT/MEDIUMTEXT/LONGTEXT

9. OFFSET Value Exceeds Threshold​

Rule Description​

In SQL queries, the LIMIT clause is used to limit the number of query results, and the OFFSET clause is used to specify from which row in the query result set to start returning data. When the OFFSET value is large, the query engine must scan more and more data to find the data rows after the offset. In the case of large datasets, this can cause the query to become very slow and may occupy a lot of system resources.

Default Alert Level​

  • Notice

Conditions​

  • The OFFSET value exceeds the threshold

10. Avoid Negative Queries on Conditional Fields​

Rule Description​

Negative queries refer to negative queries, i.e., <>, NOT IN and other negative conditions. Such queries cannot utilize indexes for fast positioning.

Default Alert Level​

  • Warning

Conditions​

  • SQL conditions are negative conditions.

11. Avoid SELECT Statements Without Conditions and Grouping​

Rule Description​

Query statements without query conditions or with always-true conditions, and without grouping syntax, will lead to full table scans and huge result sets. PawSQL will check for such constructions and provide reminders.

Default Alert Level​

  • Notice

Conditions​

  • SELECT statement is unconditional and not grouped

12. Table Joins Lack Join Conditions​

Rule Description​

Lacking join conditions in table joins can lead to result sets becoming the Cartesian product of the two tables, with a huge amount of data, and there is a high probability that it does not meet the developer's expectations. PawSQL will check for such constructions and provide reminders.

Default Alert Level​

  • Notice

Conditions​

  • Table joins lack join conditions

13. Partitioned Tables Do Not Use Partition Field Filtering​

Rule Description​

When accessing partitioned tables, not using partition fields for filtering can lead to the need to access all partitions.

Default Alert Level​

  • Notice

Conditions​

  • The table is a partitioned table
  • There are no filtering conditions on the partition key in SQL

14. Filtering Conditions Must Use Primary Keys or Indexed Columns​

Rule Description​

If a table's filtering conditions do not have primary keys or indexes, it will lead to full table scans.

Default Alert Level​

  • Notice

Conditions​

  • SQL contains filtering or association conditions
  • Filtering or association conditions are indexable conditions
  • No usable index exists for indexable conditions (including primary key indexes)

15. SELECT Statements Must Have LIMIT​

Rule Description​

SELECT statements return the number of records needed by the user, and the server usually has certain limitations on the processing capacity of query records. By adding a LIMIT clause to SELECT statements, the return of excessive result sets caused by abnormal input parameters can be limited, preventing the server from crashing completely. It is recommended that all SQL in production enable this rule.

Default Alert Level​

  • Notice

Conditions​

  • SQL is a SELECT query statement
  • This SELECT query statement does not have a LIMIT clause

16. The Number of Values in INSERT Statements Exceeds the Threshold​

Rule Description​

Bulk inserting values can effectively improve the efficiency of data insertion, as shown in the following example,

insert into customer(c_custkey, lastname, firstName)
values(1, 'Dan', 'Mike'),(2, 'Chaw', 'Tomas'),(3, 'Wang', 'Nancy');

However, if the amount of data inserted is too large, it may cause the database optimizer to choose inefficient execution plans, leading to performance issues. It may even exceed the database's limits (MySQL: max_allowed_packet), causing database-side errors. In PawSQL, this construction will be checked, and SQL exceeding the threshold (default is 500) will be alerted.

Default Alert Level​

  • Notice

Conditions​

  • SQL is a SELECT...VALUES... statement
  • The number of values in VALUES exceeds the threshold set by the user (default is 500)

17. The Number of Table Joins in a Query Exceeds the Threshold​

Rule Description​

In the execution plan planning of standalone database systems, the order and method of table joins are the most important planning content for database optimizers. The increase in the number of table joins will exponentially increase the search space for the database optimizer to find the optimal execution plan, leading to longer execution plan generation times and a propensity for generating poorly performing execution plans. Therefore, PawSQL detects whether the number of table joins in a query exceeds a certain threshold and reminds users of potential risks. In PawSQL, the default value of the threshold is 5, which users can modify when creating optimization tasks.

Default Alert Level​

  • Warning

Conditions​

  • The number of table joins in a single query block exceeds the threshold (default is 5)

18. The Nesting Level of Subqueries Exceeds the Threshold​

Rule Description​

The nesting of subqueries makes SQL more complex, and overly complex SQL can make the database optimizer take a long time to generate execution plans and easily generate poorly performing execution plans. Therefore, PawSQL detects whether the nesting level of subqueries exceeds a certain threshold and reminds users of potential risks. In PawSQL, the default value of the threshold is 2, which users can modify when creating optimization tasks.

Default Alert Level​

  • Warning

Conditions​

  • The nesting level of subqueries exceeds the threshold (default is 2)

19. Avoid OR Conditions on Different Fields​

Rule Description​

OR conditions on the same field allow the database optimizer to consider them together as a candidate for an index range scan. If OR conditions come from different fields, the database optimizer may not be able to use indexes for fast data positioning.

Default Alert Level​

  • Warning

Conditions​

  • SQL contains OR connected conditions
  • The columns in the OR connected conditions belong to different columns

20. Avoid Using Random Functions for Sorting​

Rule Description​

MySQL's rand function or PostgreSQL's random function returns a random floating-point number between 0 and 1.0. We sometimes use the following query statement to obtain a random sample of the dataset.

select * from orders order by rand() limit 1;

If the customer table has fewer than 10,000 rows, this method works well. However, when you have 1,000,000 rows, the sorting overhead becomes unacceptable. The reason is obvious: we sort all rows but keep only one of them. In fact, there are more efficient ways to achieve this requirement, click to get more detailed information.

Default Alert Level​

  • Warning

Conditions​

  • Sorting with random functions

21. Avoid Specifying COLLATION in Query Sorting​

Rule Description​

In SQL, you can specify the COLLATION used by the sorting field, such as the following SQL

select * from customer c order by c_name COLLATE utf8mb4_0900_bin

In this case, the SQL will not be able to utilize the orderliness of indexes to avoid sorting.

Default Alert Level​

  • Warning

Conditions​

  • Explicitly specify the COLLATION for sorting

22. Avoid ORDER BY Fields from Different Tables​

Rule Description​

If the sorting fields come from different tables, the database optimizer will not be able to utilize the orderliness of indexes to avoid sorting. If there are equiconditions in the WHERE or HAVING clauses, PawSQL can replace the sorting or grouping fields to make them come from the same table, thus being able to use the index to avoid sorting. For example, in the following query

select * from customer c, orders o where o_custkey = c_custkey order by o_custkey, c_name

The sorting fields o_custkey, c_name come from two tables, and there is a filtering condition o_custkey = c_custkey, which can be rewritten as

select * from customer c, orders o where o_custkey = c_custkey  order by c_custkey, c_name

Default Alert Level​

  • Notice

Conditions​

  1. ORDER fields come from different tables

  2. Filtering conditions are indexable conditions

  3. No calculations or functions on index columns

23. Avoid Sorting on Long Fields​

Rule Description​

In computers, sorting is an OlnN time complexity operation. If the number of rows to be sorted is relatively large, the length of a single field will significantly affect the sorting efficiency. This rule can compare whether the length of the sorting field exceeds the threshold input by the user. If it exceeds the threshold, an alert will be triggered.

Default Alert Level​

  • Alert

Conditions​

  1. ORDER field type is CHAR/VARCHAR and the field length exceeds the threshold
  2. Or ORDER field type is CLOB/TEXT/TINYTEXT/MEDIUMTEXT/LONGTEXT

24. Avoid LIKE Queries Starting with %​

Rule Description​

In SQL queries, the LIKE operator is used to match strings. If the pattern string starts with % (e.g., LIKE '%ABC'), the database optimizer cannot use indexes to filter data, which can easily lead to full table scans. In the absence of other filtering conditions, it may have a significant impact on query performance and efficiency. Therefore, it is best to avoid % queries starting with the condition. If you have to use % matching starting with, consider creating a full-text index to improve query performance.

Default Alert Level​

  • Warning

Conditions​

  • LIKE conditions start with '%' or '_'

25. Avoid Using Scalar Subqueries​

Rule Description​

Scalar subqueries return a single value from a single row and single column, and they can appear anywhere in SQL where a single value is needed. Scalar subqueries usually need to be determined at runtime whether they return a single row value, and they are usually correlated subqueries. They are prone to runtime errors and performance issues.

Default Alert Level​

  • Notice

Conditions​

  • SQL contains scalar subqueries

26. Avoid Adding FOR UPDATE in SELECT Statements​

Rule Description​

Adding FOR UPDATE to SELECT statements will lock tables or data rows, affecting query concurrency, leading to blocks and overall performance degradation, and should be used with caution. Therefore, PawSQL will check for such constructions and provide reminders.

Default Alert Level​

  • Warning

Conditions​

  • The query statement contains the FOR UPDATE keyword

27. Avoid Using SELECT *​

Rule Description​

The disadvantages of using SELECT * in queries are as follows:

  • SELECT * if it includes useless large fields, especially text/CLOB types of fields, can easily cause unnecessary disk IO and network overhead,
  • Using SELECT * can increase the cost of code maintenance, such as inconsistencies with resultMap configuration when adding or reducing fields, field mapping errors when insert into select *,
  • Database optimizers cannot plan for covering indexes,
  • PawSQL index recommendations cannot recommend covering indexes

Get more detailed information.

Default Alert Level​

  • Notice

Conditions​

  • SQL contains SELECT *
  • SELECT * is not in EXITS subqueries

28. Use UNION ALL Instead of UNION​

Rule Description​

When using UNION to get the union of two result sets, the union result set will be deduplicated. The deduplication operation is implemented internally by sorting or hashing, both of which require a lot of computational resources. If logically guaranteed that there are no duplicate data in the two result sets, UNION ALL can be used instead of UNION to achieve significant performance improvements.

Default Alert Level​

  • Notice

Conditions​

  • SQL contains UNION structure

29. Avoid Updating Primary Key Values​

Rule Description​

In MySQL InnoDB engine or SQL Server databases, data storage is organized by primary keys. In this case, updating the primary key involves adjusting the physical organization of data on the disk and also involves checking the uniqueness of the primary key value. When the table data volume is very large, the cost of updating may be very high.

Default Alert Level​

  • Warning

Conditions​

  • Updating the value of the primary key
  • MySQL InnoDB engine or SQL Server databases

30. Avoid Updating Values of Unique Constraints​

Rule Description​

Updating the values of columns with unique constraints requires a uniqueness check, and when the table data volume is very large, the cost of updating may be very high.

Default Alert Level​

  • Warning

Conditions​

  • Updating the value of a unique column

31. DELETE/UPDATE Prohibits Table Joins​

Rule Description​

Misoperation of table joins may lead to a large result set. DELETE/UPDATE on a large result set may take a long time, lock the table for a long time, and it is difficult to roll back the operation.

Default Alert Level​

  • Warning

Conditions​

  • DELETE/UPDATE involves multiple tables

32. Avoid Using Unnecessary Built-in Functions​

Rule Description​

Some built-in functions may not meet certain business or computational specifications. By configuring this rule, you can specify the built-in functions that need to be prohibited in the business.

Default Alert Level​

  • Notice

Trigger Rules​

  • Function names in the list appear in SQL.