Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to optimize sql database statements, the article is very detailed, has a certain reference value, interested friends must read it!

Usually, sql database needs to be optimized and analyzed, and there are some skills. Several methods of sql optimization will not be introduced in detail here. This article will summarize the optimization of sql statements, followed by the optimization tool SQL Tuning Expert for Oracle and how to use it. First of all, we should follow several principles of database optimization:

1. Try to avoid doing operations on columns, which will cause the index to fail

two。 To use join, you should use small result sets to drive large result sets, while splitting complex join queries into multiple query. Otherwise, the more tables there are in join, the more locking and blocking will result.

3. Pay attention to the use of like fuzzy queries and avoid%%, such as select * from a where name like'% de%'

Substitute statement: select * from a where name > = 'de' and name

< 'df'; 4.仅列出需要查询的字段，不要使用select * from ...，节省内存； 5.使用批量插入语句，节省交互； insert into a (id ,name)values(2,'a'),(3,'s'); 6.limit基数比较大时，使用between ... and ... 7.不要使用rand函数随机获取记录； 8.避免使用null ，这就需要在建表时，尽量设置为not null，提升查询性能； 9，不要使用count(id),而应该是count(*) 10.不要做无谓的排序，尽可能在索引中完成排序； 我们先来看一个sql： select ii.product_id, p.product_name, count(distinct pim.pallet_id) count_pallet_id, if(round(sum(itg.quantity),2) >

-1 & round (sum (itg.quantity), 2)

< 0.005, 0, round(sum(itg.quantity),2)) quantity, round(ifnull(sum(itag.locked_quantity), 0.00000),2) locked_quantity, pc.container_unit_code_name, if(round(sum(itg.qoh),2) >

-1 & round (sum (itg.qoh), 2)

< 0.005, 0, round(sum(itg.qoh),2)) qoh, round(ifnull(sum(itag.locked_qoh), 0.00000),2) locked_qoh, p.unit_code, p.unit_code_name from (select it.inventory_item_id item_id, sum(it.quantity) quantity, sum(it.real_quantity) qoh from ws_inventory_transaction it where it.enabled = 1 group by it.inventory_item_id ) itg left join (select ita.inventory_item_id item_id, sum(ita.quantity) locked_quantity, sum(ita.real_quantity) locked_qoh from ws_inventory_transaction_action ita where 1=1 and ita.type in ('locked', 'release') group by ita.inventory_item_id )itag on itg.item_id = itag.item_id inner join ws_inventory_item ii on itg.item_id = ii.inventory_item_id inner join ws_pallet_item_mapping pim on ii.inventory_item_id = pim.inventory_item_id inner join ws_product p on ii.product_id = p.product_id and p.status = 'OK' left join ws_product_container pc on ii.container_id = pc.container_id//总起来说关联太多表，设计表时可以多一些冗余字段，减少表之间的关联查询； where ii.inventory_type = 'raw_material' and ii.inventory_status = 'in_stock' and ii.facility_id = '25' and datediff(now(),ii.last_updated_time) < 3 //违反了第一个原则 and p.product_type = 'goods' and p.product_name like '%果%' // 违反原则3 group by ii.product_id having qoh < 0.005 order by qoh desc 上面的sql我们在from 中使用了子查询，这样对查询是非常不利的； 更好的一种做法是下面的语句： select t.facility_id, f.facility_name, t.inventory_status, wis.inventory_status_name, t.inventory_type, t.product_type, t.product_id, p.product_name, t.container_id, t.unit_quantity, p.unit_code, p.unit_code_name, pc.container_unit_code_name, t.secret_key, sum(t.quantity) quantity, sum(t.real_quantity) real_quantity, sum(t.locked_quantity) locked_quantity, sum(t.locked_real_quantity) locked_real_quantity from ( select ii.facility_id, ii.inventory_status, ii.inventory_type, ii.product_type, ii.product_id, ii.container_id, ii.unit_quantity, ita.secret_key, ii.quantity quantity, ii.real_quantity real_quantity, sum(ita.quantity) locked_quantity, sum(ita.real_quantity) locked_real_quantity from ws_inventory_item ii inner join ws_inventory_transaction_action ita on ii.inventory_item_id = ita.inventory_item_id where ii.facility_id = '{$facility_id}' and ii.inventory_status = '{$inventory_status}' and ii.product_type = '{$product_type}' and ii.inventory_type = '{$inventory_type}' and ii.locked_real_quantity >

0 and ita.type in ('locked',' release') group by ii.product_id, ita.secret_key, ii.container_id Ita.inventory_item_id having locked_real_quantity > 0) as t inner join ws_product p on t.product_id = p.product_id left join ws_facility f on t.facility_id = f.facility_id left join ws_inventory_status wis on wis.inventory_status = t. Inventory_status left join ws_product_container pc on pc.container_id = t.container_id group by t.product_id T.secret_key, t.container_id

Note:

1. Do not use subqueries in from statements

2. Use more where to limit and narrow the search scope.

3. Make rational use of the index

4. Check sql performance through explain

Optimize SQL statements using the tool SQL Tuning Expert for Oracle

It is easy for SQL developers and DBA to write the right SQL based on business requirements. But what about the execution performance of SQL? Can you optimize it and run faster? If you are not senior,

DBA, it is estimated that many people do not have confidence.

Fortunately, automated optimization tools can help us solve this problem. This is the Tosska SQL Tuning Expert for Oracle tool we are going to introduce today.

Download https://tosska.com/tosska-sql-tuning-expert-tse-oracle-free-download/

The inventor of this tool, Richard To, a former chief engineer of Dell, has more than 20 years of experience in SQL optimization.

Create a database connection, or you can create it later. Fill in the connection information and click the "Connect" button.

If you have installed the Oracle client and configured TNS on the Oracle client, you can select "TNS" as the "Connection Mode" in this window, and then select the configured TNS as the database alias in "Database Alias".

If you do not have the Oracle client installed or do not want to install the Oracle client, you can select "Basic Type" as the "Connection Mode", just need the database server IP, port and service name.

2. Enter SQL with performance problems

3. Click the Tune button to automatically generate a large number of equivalent SQL and start execution. Although the testing is not yet complete, we can already see a 100% improvement in the performance of SQL 20.

Let's take a closer look at SQL 20, which uses two Hints to stand out with the fastest execution speed. The original SQL takes 0.99 seconds, and the optimized SQL execution time is close to 0 seconds.

Because this SQL has to be executed in the database tens of thousands of times a day, it can save about 165s of database execution time after optimization.

Finally, replace the performance-problematic SQL in the application source code with the equivalent SQL 20. Performance has been improved by recompiling the application.

The above is all the contents of the article "how to optimize sql database statements". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.