SQL 데이터베이스 문장 최적화 분석 및 최적화 기법 요약(SQL 최적화 도구)

일반적으로 SQL 데이터베이스는 최적화 및 분석이 필요하며 특정 기술이 있습니다. 여기에서는 SQL 최적화의 여러 방법을 자세히 소개하지 않으며, Oracle용 최적화 도구 SQL Tuning Expert도 있습니다. 그리고 이를 사용하는 방법은 우선 데이터베이스 최적화의 몇 가지 원칙을 따라야 합니다.

1. 인덱스 오류를 유발할 수 있는 열에 대한 작업을 피하십시오.

2. 복잡한 조인 쿼리를 여러 쿼리로 분할하는 동시에 큰 결과 집합을 구동하는 작은 결과 집합입니다. 그렇지 않으면 조인하는 테이블이 많아질수록 잠금 및 정체가 더 많이 발생합니다.

3 유사 퍼지 쿼리 사용에 주의하고 %% 사용을 피하세요. 예를 들어 '%de%'와 같은 where 이름에서 *를 선택하세요.

대체 문: select * from a where name >= ' de' 및 name < ; 'df';

4. 쿼리해야 하는 필드만 나열하고 메모리를 절약하려면 select * from...을 사용하지 마세요.

5.

insert into a (id ,name)
values(2,'a'),
(3,'s');

6.limit 베이스는 상대적으로 큽니다.

7. 레코드를 무작위로 얻기 위해 rand 함수를 사용하지 마세요.

8. 쿼리 성능을 향상하려면 테이블을 만들 때 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 (&#39;locked&#39;, &#39;release&#39;) 
                               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 = &#39;OK&#39;
                    left join ws_product_container pc on ii.container_id = pc.container_id
//总起来说关联太多表，设计表时可以多一些冗余字段，减少表之间的关联查询；
                where 
                    ii.inventory_type = &#39;raw_material&#39; and 
                    ii.inventory_status = &#39;in_stock&#39; and 
                    ii.facility_id = &#39;25&#39; and 
                    datediff(now(),ii.last_updated_time) < 3  //违反了第一个原则
                     and p.product_type = &#39;goods&#39;
                     and p.product_name like &#39;%果%&#39;   // 违反原则3

                group by 
                    ii.product_id
                having 
                    qoh < 0.005
                order by 
                    qoh desc

위의 SQL은 쿼리에 매우 해로운 하위 쿼리를 사용했습니다.

더 나은 접근 방식은 다음과 같습니다.

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

참고:

1.

2. 검색 범위를 제한하고 좁히려면

4. SQL 튜닝 전문가 도구를 사용하세요. for Oracle은 SQL 문을 최적화합니다

SQL 개발자와 DBA의 경우 비즈니스 요구 사항에 따라 올바른 SQL을 쉽게 작성할 수 있습니다. 그런데 SQL의 실행 성능은 어떨까? 더 빠르게 실행되도록 최적화할 수 있나요? DBA 선배가 아니시면 자신감이 없으신 분들이 많을 것 같아요.

다행히 자동화된 최적화 도구가 이 문제를 해결하는 데 도움이 될 수 있습니다. 오늘 소개해드릴 Tosska SQL Tuning Expert for Oracle Tool 입니다.

다운로드 https://tosska.com/tosska-sql-tuning-expert-tse-oracle-free-download/ 이 도구의 발명자는 Dell의 전 수석 엔지니어인 Richard To입니다. 20년 이상의 SQL 경험 최적화 경험

1. 나중에 생성할 수도 있는 데이터베이스 연결을 생성합니다. 연결 정보를 입력하고 '연결' 버튼을 클릭하세요.

Oracle 클라이언트를 설치하고 Oracle 클라이언트에 TNS를 구성한 경우 이 창에서 "연결 모드"로 "TNS"를 선택한 다음 "데이터베이스 별칭"에서 구성된 TNS를 데이터베이스 별칭으로 선택할 수 있습니다.

Oracle 클라이언트를 설치하지 않았거나 Oracle 클라이언트를 설치하지 않으려는 경우 "연결 모드"를 "기본 유형"으로 선택하고 데이터베이스 서버 IP, 포트 및 서비스 이름만 필요합니다.

2. 성능 문제가 있는 SQL을 입력합니다.

3. Tune 버튼을 클릭하면 해당 SQL이 자동으로 대량 생성되고 실행이 시작됩니다. 아직 테스트가 완료되지 않았지만 이미 SQL 20의 성능이 100% 향상되었음을 확인할 수 있습니다.

두 개의 힌트를 사용하며 실행 속도가 가장 빠른 SQL 20에 대해 자세히 살펴보겠습니다. 원본 SQL은 0.99초가 소요되며, 최적화된 SQL 실행 시간은 0초에 가깝습니다.

이 SQL은 데이터베이스에서 매일 수만 번 실행되기 때문에 최적화 후 약 165초의 데이터베이스 실행 시간을 절약할 수 있습니다.

마지막으로 애플리케이션 소스 코드에서 성능 문제가 있는 SQL을 동등한 SQL 20으로 교체합니다. 애플리케이션을 다시 컴파일하고 성능이 향상되었습니다.

튜닝 작업이 성공적으로 완료되었습니다!

관련 기사:

Sql 성능 최적화 요약 및 SQL 문 최적화

SQL 문 최적화 원칙, SQL 문 최적화

관련 동영상:

MySQL 최적화 동영상 튜토리얼 - 부울 교육

위 내용은 SQL 데이터베이스 문장 최적화 분석 및 최적화 기법 요약(SQL 최적화 도구)의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!