Create efficient SQL notes in mass data-mysql tutorial

Dr. Zheng Wei gave a lecture on how to create efficient SQL notes in mass data, and gained a lot. I used to write some simple SQL statements and thought that SQL is just a simple command. In fact, SQL is very promising. SQL is an important aspect that reflects the capabilities of technicians. SQL is not a query language for applications! The following briefly summarizes the training gains of Yesterday: www.2cto. com1. SQL

Dr. Zheng Wei gave a lecture on how to create efficient SQL notes in mass data, and gained a lot. I used to write some simple SQL statements and thought that SQL is just a simple command. In fact, SQL is very promising. SQL is an important aspect that reflects the capabilities of technicians. SQL is not a query language for applications! The following briefly summarizes the training gains of Yesterday: www.2cto.com 1. SQL

Create efficient SQL notes in massive data volumes

He participated in Dr. Zheng's lecture and gained a lot. I used to write some simple SQL statements and thought that SQL is just a simple command. In fact, SQL is very promising. SQL is an important aspect that reflects the capabilities of technicians. SQL is not a query language for applications! The following briefly summarizes the training gains of yesterday:

Www.2cto.com

1. SQL statements are a collection language, while JAVA/c ++ and even PL/SQL are procedural languages. SQL statements process data by block, and procedural languages process data by line. The efficiency gap can be imagined.

2. expanded SQL statements can be used for all data processing functions. instead of using SQL statements as only one database command, "he is a giant, not an ordinary person ".

3. The index is very important. it is very difficult to establish a strategic index. it is best to establish a joint index. unless it is a primary key, it is best not to create a separate index. The more indexes you create, the better. the less indexes you create, the better the indexes you create. you should have a balance point. Because indexes also consume storage space, indexes that are too bloated during delete/update, especially insert operations may affect statement execution efficiency.

4. the execution plan is very important. there must be an execution plan and Optimizer concept. for an SQL statement, you must be able to roughly determine the execution path.

Www.2cto.com

5. the execution speed is largely related to IO because the CPU computing speed is very fast, but I/O is a bottleneck. So we are studying the concept of "memory database.

6. the Optimizer selects and optimizes execution plans based on statistical information, statistics can be collected by the database at regular intervals or updated manually after a large amount of data is modified.

7. The new optimizer is cost-based, not rule-based.

8. adjusting database parameters can solve 10% of performance problems, modifying the execution plan can solve 30%, and establishing strategic indexes and optimizing SQL statements can solve 50% of the problems, good data models and modeling can solve 80% of performance problems.

9. the execution plan is very sensitive, and minor changes may change the execution plan, such as the table connection data, updated statistics, and index changes in SQL statements.

10. whether the degree of discretization is good or bad determines the index effect. it is generally considered that if the degree of discretization of a column data is less than 10%-15%, the index is effective.

In the where query condition of an SQL statement, some conditions are used to filter query conditions. The best way is to use the condition with the minimum data range as the query driving condition.

11. the column order in the Composite Index may determine the query efficiency.

12. the use of indexes will be affected by the contact methods and order of the tables.

13. determine whether there is a replacement scheme in PL/SQL:

Original SQL: SELECT COUNT (*) INTO: CNT

FROM ITEM_TAB

Where dept = '123'

And seq> 100;

................................................

Www.2cto.com

If cnt> 0 ....

Improved: SELECT 1 INTO: CNT FROM DUAL

Where exists (SELECT 'X'

FROM ITEM_TAB

Where dept = '123'

And seq> 100 );

...................................................

If cnt> 0 ....

14. alternative solution for getting the maximum serial number

Original SQL: SELECT MAX (seq) + 1

FROM order

WHERE deptno = '20140901'

SQL replacement: SELECT/* + INDEX_DESC (order pk_order )*/

NVL (MAX (seq), 0) + 1

FROM order

WHERE deptno = '20140901'

And rownum = 1

Www.2cto.com

15. general principles and summary

1) Data processing is a set operation;

2) it is necessary to change from process developers to data developers, and use complex SQL and complex execution plans to replace procedural program execution, which can effectively reduce the number of computing times and cooperate with strategic indexes, you can streamline the code and mention performance.

3) SQL is not a command statement, but an application!