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

Relational database optimization:

1. Optimize the data structure, simplify the structure, data structure and redundant fields through business logic

two。 Optimize through T-sql statements, including optimization of sql statements, indexing, and standardizing the writing of sql statements (according to the execution principle of sql code)

3. Money bonus, add servers, distributed clusters to solve problems

Note: the optimization of business logic requires a large amount of work, which requires close cooperation between the business and developers to ensure that the logic of every small detail can be explained clearly, and the risk is relatively large. Many enterprises will not do so, but this is also the most fundamental solution to the performance problem; T-sql sentence optimization requires high technology and a good understanding of data principles and the basis of T-sql programs. Add server method, which requires funds and relevant operation and maintenance personnel

Today, let's talk about the optimization of T-sql code:

Index classification:

Storage structure: clustered index, non-clustered index

Uniqueness distinction: unique index, non-unique index

Number of columns: single-column index, multi-column index

Considerations for using the index:

1) the more indexes, the better. To create targeted indexes according to the query, consider indexing the columns involved in the WHERE and ORDER BY commands. You can check whether indexes or full table scans are used according to EXPLAIN.

2) try to avoid judging the NULL value of the field in the WHERE clause, otherwise it will cause the engine to give up using the index and do a full table scan.

3) fields with sparse value distribution are not suitable for indexing, such as "gender", which has only two or three values.

4) character fields are indexed with prefixes only.

5) character fields are best not to be primary keys.

6) without foreign keys, constraints are guaranteed by the program

7) avoid using UNIQUE as much as possible, and be constrained by the program.

8) when using multi-column indexes, be careful to keep the order consistent with the query conditions, and delete unnecessary single-column indexes.

Clustered index: there can be only one clustered index per table; a balanced binary tree is used to reorganize the actual data on disk to sort by specified one or more column values.

Nonclustered indexes: each table can have multiple nonclustered indexes; the index created by default is a nonclustered index, which does not reorganize the data in the table, but stores the index column value for each row and uses a pointer to the page where the data is located. Each nonclustered index provides a different sort order according to the index column.

Index design principles:

1) the system generally automatically establishes a clustered index for gradual fields.

2) columns that have a large number of duplicate values and often have range queries and sorting, grouping, or frequently accessed columns, consider establishing a clustered index.

3) to build an index in a table that often does insert operations, fillfactor (fill factor) should be used to reduce page splitting and increase concurrency to reduce the occurrence of deadlocks. If the table is read-only, the fill factor can be set to 100

4) when selecting index keys, use columns of small data types as keys as far as possible so that each index page can hold as many index keys and pointers as possible. In this way, the index pages that a query must traverse can be reduced to a minimum. In addition, use integers as key values as much as possible, because integers have the fastest access speed.

Logical execution order of Sql queries:

① From: generate a virtual table vt1 by performing Cartesian product on the first two tables in the FROM clause

② On: apply an ON filter to the vt1 table. Only rows that satisfy true are inserted into the vt2.

③ Join: if you specify rows that are not found in the OUTER JOIN retention table (preserved table), add rows to vt2 generation T3 as external rows. If from contains more than two tables, repeat the steps and steps for the result table generated by the previous join and the next table.

④ Where: apply a WHERE filter to vt3 only rows that are true are inserted into the vt4

⑤ Group by: generate vt5 by grouping rows in vt4 by column list in the GROUP BY clause

⑥ With:

⑦ Having: apply a HAVING filter to vt6 only groups that are true insert vt7

⑧ Select: processing select lists to generate vt8

⑨ Distinct: removes duplicate lines from vt8 to produce vt9

⑩ Order by: sorts the rows of vt9 by the column list in the order by clause to generate a cursor vc10

⑪ Top: select a specified number or proportion of rows from the beginning of the vc10 to generate the vt11 and return the caller

Index lookup (seek) is generally optimal (but the lookup also depends on the filter of the lookup). Try to build a composite index of the fields in the where condition and include the fields in the select to be queried.

If you need your conditions, you can use the index! For example, your statement in the index column can not take functions, can not participate in the calculation such as where productID/2 = @ a, can not have implicit conversion and so on!

The most classic example is the difference between where and having. You should already understand the order in which statements are executed. Can be written in where, not in having

Horizontally:

Instead of writing SELECT *, select the fields you want.

When joining multiple tables in a SQL statement, use the alias of the table and prefix it on each Column. In this way, you can reduce parsing time and reduce syntax errors caused by Column ambiguity.

Vertically:

Where conditions should be as many as possible and ensure high screening.

It is common in business to return large amounts of data to the front end, but is this data really necessary? Can some default conditions be added to the front end?

Optimization at the level of Sql code:

Sub-library: by adding servers, hardware is used to share the pressure caused by data.

Sub-table: reasonable use of server performance, server performance optimization, to ensure that the single table data will not be too large

Idea: horizontal and vertical

Horizontal: divide the modules into different libraries through business logic to ensure that the data of each module runs independently on different servers.

Vertical: the data table of a module is divided to ensure that there is not too much data in each table, and the balance of sub-table data is ensured by algorithm.

Disadvantages: 1. Transaction execution

two。 Data association and statistics

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.