Shulou(Shulou.com)06/01 Report--

As a financial technology company, a large number of businesses of Yixin rely on databases. How to improve the overall database application level of the company is a major challenge to DBA, but also of great practical significance. During the many years of work of Yixin, the author summarized and sorted out the rules for the use of traditional relational databases with the help of the self-developed database audit platform, so as to help the R & D team evaluate the quality of database development. to achieve the goal of finding and solving problems as soon as possible. The following figure is a simple schematic diagram of this system.

As shown in the above figure, the rule section can be subdivided into the following categories (parts). A brief description is given below, which will be explained in detail one by one later.

I. Oracle rules (objects) 1.1Table, Partition

[rule 1]

Rule description: tables that exceed the specified size and do not have partitions.

Rule threshold: 2GB (the physical size exceeds the specified threshold).

Rule description: the scale of the table is too large, which will affect the access efficiency of the table, increase the maintenance cost and so on. A common solution is to use partitioned tables to convert large tables into partitioned tables.

[rule 2]

The rule states that the number of records in a single table or single partition is too large.

Rule threshold: 1000000 (the number of records in a single table or single partition exceeds the specified threshold).

Rule description: control the data size of a single table or a single partition to improve the access efficiency of a single object. If the number of records is too large, strategies such as sub-database, sub-table, partition and so on should be considered.

[rule 3]

The rule states that there are too many big watches.

Rule threshold: custom (too many tables over 2G).

Rule description: large tables account for more than 20% (OLTP) or 95% (OLAP) of all tables.

[rule 4]

The rule states that there are too many partitions in a single table.

Rule threshold: 500 (the number of partitions in a single table exceeds the specified threshold).

Rule description: if there are too many partitions in the partition table, the overall maintenance cost will be too high and the partition granularity can be adjusted.

[rule 5]

The rule states that there are too many partition tables.

Rule threshold: 2000 (the number of partition tables exceeds the specified threshold).

Rule description: there are too many partition tables, and the common reason is that there are too many large tables. According to the demand, vertical split is considered to reduce the size of the single database.

[rule 6]

The rule states that there are too many compound partitions.

Rule threshold: 5 (the number of compound partitions exceeds the specified threshold).

Rule description: similar to the reason why there are too many partition tables above (the number of compound partition tables).

[rule 7]

The rule states that there is a table with parallelism enabled.

Rule threshold: 1 (table degree attribute is not 1).

Rule description: in general, setting parallel properties on a table is not recommended.

1.2 Index

[rule 8]

Rule description: a table whose foreign key does not have an index.

Rule description: the lack of an index by a foreign key will lead to low association efficiency when the master child table is associated with a query.

[rule 9]

Rule description: there are too many combined indexes or no indexes.

Rule description: too many combined indexes will lead to large space consumption and high cost of index maintenance. You should consider building a strategic index structure, not by creating an index for every requirement.

[rule 10]

The rule states that there are too many indexes in a single table.

Rule threshold: 3 (the number of indexes in a single table exceeds the specified threshold).

Rule description: indexes can improve access speed, but too many indexes will lead to excessive space consumption, high cost of index maintenance, and affect the efficiency of DML. The number of indexes should be controlled.

[rule 11]

Rule description: there are indexes that have not been used for 7 days.

Rule description: the index has not been used by any SQL statements in the database for a period of time. Please evaluate the validity of this index.

[rule 12]

Rule description: field is indexed repeatedly.

Rule description: a field is referenced by multiple indexes, please consider the build strategy to delete unnecessary indexes.

[rule 13]

Rule description: there is a global partition index.

Rule description: the global partition index has the problem of high maintenance cost. When the partition changes, it is necessary to maintain the validity of the global index.

[rule 14]

Rule description: invalid index.

Rule description: index status is INVALID, UNUSABLE.

[rule 15]

Rule description: the index height exceeds the specified height.

Rule threshold: 4.

Rule description: too high index height results in increased IO cost.

[rule 16]

Rule description: there is a bitmap index.

Rule description: bitmap indexing is not recommended in the OLTP environment. If the table object is checked for DML operation, it will block the related operation to a certain extent.

[rule 17]

Rule description: there is a function index.

[rule 18]

The rule states that there is an index with parallelism enabled.

Rule threshold: 1 (the index degree property is not 1).

Rule description: in general, setting parallel properties on indexes is not recommended.

[rule 19]

The rule states that there is an index with too large clustering factor.

Rule threshold: custom.

Rule description: if the clustering factor is too large, optimization should be considered.

1.3 constraints

[rule 20]

Rule category: constraints.

Rule description: a table without a primary key.

Rule description: the primary key is the only basis for determining a record in a relational database, and there is no reason not to define the primary key.

[rule 21]

Rule category: constraints.

Rule description: a table that uses foreign keys.

Rule description: foreign key constraints are not recommended. Data consistency is resolved through the application side.

1.4 Field

[rule 22]

Rule description: too many table fields.

Rule threshold: 100 (the number of fields exceeds the specified threshold).

Rule description: too many fields will cause the record length to be too large. A single data storage unit will save too few records, affecting the efficiency of access.

[rule 23]

Rule description: contains tables of large field types.

Rule description: large object fields should be avoided as much as possible in relational databases. If necessary, consider storing it externally.

[rule 24]

Rule description: the record length definition is too long.

Rule description: the record definition length is too different from the actual storage length. Please consider whether the field type definition is reasonable and whether individual fields can be stored in separate tables if they are too long.

[rule 25]

Rule description: a table that does not contain a timestamp field.

Rule description: timestamp is a way to obtain incremental data. It is recommended to add the timestamp fields of creation time and update time to the table. The naming methods are CREATE_TIME and UPDATE_TIME.

[rule 26]

Rule description: table field types do not match.

Rule description: this rule will sample part of the data and analyze whether its definition type matches the storage type. Frequently asked questions such as saving dates with numbers, text, etc.

1.5 other objects

[rule 27]

Rule description: caches too small sequences.

Rule threshold: 100 (the sequence cache value is less than the specified threshold).

Rule description: the system will cache 20 by default, if too small will lead to frequent query of data dictionaries, affecting the concurrency ability.

[rule 28]

The rule states that there are stored procedures and functions.

Rule threshold: 20 (the number of stored procedures and functions exceeds the specified threshold).

Rule description: stored procedures and functions will affect the heterogeneous migration ability of the database, and there are some reasons such as poor code maintainability.

[rule 29]

Rule description: there is a trigger.

Rule threshold: 20 (the number of triggers exceeds the specified threshold).

Rule description: trigger, which will affect the heterogeneous migration ability of the database. If there is a need for data consistency maintenance, please consider it from the application side.

[rule 30]

Rule description: DBLINK exists.

Rule description: it is not recommended to access other databases in one database, please consider solving it on the application side.

II. Oracle rules (execution plan) 2.1 bind variables

[rule 31]

Rule description: binding variables are not used.

Rule threshold: custom (number of execution).

Rule description: if the number of execution times exceeds a certain threshold, there is a constant value on the right side of the predicate.

[rule 32]

The rule states that there are too many bound variables.

Rule threshold: custom (number of bound variables).

Rule description: too many bound variables will increase the variable replacement time and, to some extent, increase the sql execution time.

2.2 Association between tables

[rule 33]

Rule description: Cartesian product.

Rule description: the lack of join conditions results in the use of Cartesian product connections between tables, and the execution plan contains the word "CARTESIAN |".

[rule 34]

Rule description: the level of nested loops is too deep.

Rule threshold: custom (number of layers).

Rule description: the nested loop is too deep, exceeding the specified threshold. The words "NESTED LOOP" or "FILTER" are embedded in the execution plan.

[rule 35]

Rule description: the inner table of the nested loop is accessed by full table scan.

Rule description: the inner table access mode of the nested loop is full table scan, which is very inefficient.

[rule 36]

The rule states that there is a large result set sort in the sort merge connection.

Rule description: both result sets in a sort merge should be sorted and should be adjusted to other connections.

[rule 37]

Rule description: multi-table association.

Rule threshold: custom (number of tables).

Rule description: too many table associations affect performance.

2.3 access path

[rule 38]

Rule description: large table full table scan.

Rule threshold: custom (table size, unit GB).

Rule description: a full table scan is performed on a large table, and the word "TABLE ACCESS FULL" is included in the execution plan.

[rule 39]

Rule description: large index is fully scanned.

Rule threshold: custom (index size, unit GB).

Rule description: a full index scan is performed on a large index, and the word "INDEX FULL SCAN" is included in the execution plan.

[rule 40]

Rule description: large index fast full scan.

Rule threshold: custom (index size, unit GB).

Rule description: a fast full index scan operation is performed on a large index, and the word "INDEX FAST FULL SCAN" is included in the execution plan.

[rule 41]

Rule description: index jump scan.

Rule description: perform a jump scan on the index, with the word "INDEX SKIP SCAN" included in the execution plan.

[rule 42]

Rule description: partition full scan.

Rule description: the partition table is scanned in full partition, and the execution plan contains the word "PARTITION RANGE ALL".

[rule 43]

Rule description: discontinuous partition scanning.

Rule description: non-contiguous partition scanning, the execution plan contains the words "PARTITION RANGE INLIST" or "PARTITION RANGE OR".

[rule 44]

Rule description: scan across partitions.

Rule description: continuous partition scan with the word "PARTITION RANGE ITERATOR" in the execution plan.

2.4 Type conversion

[rule 45]

The rule states that there is an implicit conversion.

Rule description: implicit data type conversion is used in conditional judgment.

2.5 other implementation plans

[rule 46]

The rule states that there is a large result set sort operation.

Rule description: consider to avoid sorting by introducing operations such as indexes.

[rule 47]

The rule states that there are parallel access characteristics.

Rule description: parallelism affects performance and generally needs to be avoided.

[rule 48]

Rule description: there is view access.

Rule description: view operations can generally be merged, unnested, etc., if not, you should check the view definition.

III. Oracle rules (execution characteristics) 3.1 implementation characteristics

[rule 49]

The rule states that the ratio of the number of scanning blocks to the number of records returned is too low.

Rule threshold: custom (percentage).

Rule description: scan a large amount of data but return a small number of records, so you need to adjust the SQL statement logically.

[rule 50]

Rule description: too many child cursors.

Rule threshold: custom (number of child cursors).

Rule description: there are too many child cursors and the execution plan may be unstable.

[rule 51]

Rule description: elapsed_time.

Rule threshold: custom.

[rule 52]

Rule description: cpu_time

Rule thresholds: customizin

[rule 53]

Rule description: buffer_gets

Rule thresholds: customizin

[rule 54]

Rule description: disk_reads

Rule thresholds: customizin

[rule 55]

Rule description: direct_writes

Rule thresholds: customizin

[rule 56]

Rule description: executions

Rule thresholds: customizin

4. MySQL rules (objects) 4.1 tables, partitions

[rule 57]

Rule description: tables that exceed the specified size and do not have partitions.

Rule threshold: custom (table size, GB).

Rule description: the scale of the table is too large, which will affect the access efficiency of the table, increase the maintenance cost and so on. A common solution is to use partitioned tables to convert large tables into partitioned tables.

[rule 58]

Rule description: too many data tables in a single database.

Rule threshold: custom (number of tables).

Rule description: too many data tables in a single database will affect the overall performance. If necessary, perform a vertical split of the business logic.

[rule 59]

The rule states that the amount of data in a single table (partition) is too large.

Rule threshold: custom (data size, number of records).

Rule description: too many single table (partitioned) data tables will affect the overall performance. If necessary, carry out sub-database, sub-table or regularly clean up and archive the data.

4.2 Index

[rule 60]

The rule states that there are too many indexes in a single table.

Rule threshold: custom (number of indexes).

Rule description: too many indexes in a single table, not only high maintenance costs, but also take up more space.

[rule 61]

Rule description: there is a duplicate index.

Rule description: the index can be completely replaced by another index containing the prefix, which is redundant. Redundant indexes waste storage space and affect data update performance.

[rule 62]

Rule description: index selection rate is not high.

Rule threshold: custom (selection rate, percentage).

Rule description: the index selection rate is not high, which will lead to index inefficiency. Please adjust the index field.

4.3 constraint

[rule 63]

Rule description: there is a foreign key in the table.

Rule description: foreign key resources will consume the computing power of the database. It is recommended to ensure data constraints through the application layer.

[rule 64]

Rule description: the table does not have a primary key defined.

Rule description: there is no primary key defined, MySQL will automatically create the primary key. This is not a good design method.

4.4 Field

[rule 65]

Rule description: there are large object fields.

Rule description: large object fields will affect access performance and consume more space, so it is recommended to store them outside the database.

[rule 66]

Rule description: there are too many fields in a single table.

Rule threshold: custom (number of fields).

Rule description: too many table fields will result in long records and a reduction in the number of records stored on a single page. You can consider dismantling the table.

[rule 67]

Rule description: the definition length of a single table field is too long.

Rule threshold: custom (field length, unit bytes).

Rule description: the definition length of a single table should be controlled to avoid long records.

[rule 68]

Rule description: the definition length of single table primary key field is too long.

Rule threshold: custom (field length, unit bytes).

Rule description: the length of the primary key field should be controlled. Excessively long primary key fields will consume too much index space.

[rule 69]

Rule description: there is no timestamp field defined in the table.

Rule description: the timestamp field is the best way to get incremental data. Define a timestamp field for the table.

[rule 70]

Rule description: field data type definition error.

Rule threshold: custom (number of records).

Rule description: according to the saved content of the field, the field type definition is abnormal, and it is recommended to select the appropriate data type.

4.5 other objects

[rule 71]

Rule description: there are functions, stored procedures and triggers in a single table.

Rule description: stored procedures, functions, triggers, etc., will consume the computing power of the database. It is recommended to ensure data constraints through the application layer.

5. MySQL rules (execution Plan) 5.1 access path

[rule 72]

Rule description: large table full table scan.

Rule threshold: custom (table size, unit GB).

Rule description: a full table scan operation is performed on a large table.

5.2 SELECT_TYPE

[rule 73]

Rule description: DEPENDENT UNION

[rule 74]

Rule description: SUBQUERY

[rule 75]

Rule description: DEPENDENT SUBQUERY

[rule 76]

Rule description: MATERIALIZED

[rule 77]

Rule description: UNCACHEABLE SUBQUERY

[rule 78]

Rule description: UNCACHEABLE UNION

5.3 ACCESS_TYPE

[rule 79]

Rule description: fulltext

[rule 80]

Rule description: index_merge

[rule 81]

Rule description: unique_subquery

[rule 82]

Rule description: all

[rule 83]

Rule description: index range

5.4 other implementation plans

[rule 84]

Rule description: use temporary tables.

Rule description: temporary tables are used during execution, and "using temporary" is included in the execution plan.

[rule 85]

Rule description: use disk sorting.

Rule description: disk sorting is used in the execution plan, which contains the word "using filesort".

VI. MySQL rules (execution characteristics) 6.1 execution characteristics

[rule 86]

Rule description: index_ratio

[rule 87]

Rule description: lock_time_sum

7. Oracle+MySQL (statement level) 7.1 query class

[rule 88]

Rule description: select *

Rule description: the use of select * is prohibited and the desired column must be explicitly selected.

[rule 89]

Rule description: repeat the query clause.

Rule description: duplicate query clauses are prohibited and with as replacement clauses (Oracle only) should be used to improve the efficiency of SQL execution.

[rule 90]

Rule description: query field reference function.

Rule description: it is forbidden to refer to functions in query fields (type conversion functions, function indexes can be ignored).

[rule 91]

Rule description: nesting select clauses.

Rule description: prohibit the occurrence of nested subqueries of select clauses to avoid performance problems.

[rule 92]

Rule description: union appears.

Rule description: prevent unnecessary sorting actions.

[rule 93]

Rule description: multiple filter conditions are connected through or.

Rule description: prevent selection exceptions from the optimizer.

[rule 94]

Rule description: predicate condition uses like'% xxx'

Rule description: index cannot be used.

[rule 95]

The rule states that there is a negative operator in the predicate.

Rule description:! =,!, not exists,not.

[rule 96]

Rule description: there is a sub-query.

Rule description: this should distinguish between locations (select, from, where, having, etc.).

[rule 97]

Rule description: there are more than three table associations.

[rule 98]

The rule states that there is a full connection or an external connection.

Rule description: cross join or outer join case.

7.2 change class

[rule 99]

Rule description: the order by clause appears in update.

Rule description: prevent unnecessary sorting during the update process.

[rule 100]

The rule states that the where clause must appear in update.

Rule description: prevent unexpected all update actions.

[rule 101]

Rule description: update the primary key.

Rule description: it is prohibited to update the primary key.

[rule 102]

Rule description: the order by clause appears in delete.

Rule description: prevent unnecessary sorting in the deletion process.

[rule 103]

The rule states that the where clause must appear in delete.

Rule description: prevent unexpected delete actions.

[rule 104]

Rule description: added SQL text too long rule.

[rule 105]

Rule description: too many IN List elements have been added.

Author: Han Feng

The first release is on the author's personal official name "Han Feng Channel".

Source: Yixin Institute of Technology

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.