巧用函数索引性能调优-tutorial mysql-php.cn

Rumah

pangkalan data

tutorial mysql

巧用函数索引性能调优

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 03:28 PM

sql fungsi prestasi melaksanakan indeks Penalaan

今天调了一条SQL，执行计划中出现BITMAP CONVERSION，检查了下索引，没有位图索引

今天调了一条SQL，执行计划中出现BITMAP CONVERSION，检查了下索引，没有位图索引啊。原来是Oracle有时候会选择将B-Tree索引进行BITMAP转换来进行SQL执行，从而导致极其恶劣的执行计划。在下列计划中BITMAP CONVERSION FROM / TO ROWIDS 就是进行了位图转换后的执行计划：

SQL> SELECT *

2 FROM (SELECT A.ASSET_ID,
3 C.CLASSIFY_CODE,
4 C.CLASSIFY_NAME,
5 V.NOMINAL_VOLTAGE BASE_NOMINAL_VOLTAGE,
6 TO_CHAR(SDO_UTIL.TO_GMLGEOMETRY(A.G3E_GEOMETRY)) AS GML
7 FROM DM_ASSET A, DM_CLASSIFY C, DM_BASE_VOLTAGE V
8 WHERE A.CLASSIFY_ID = C.CLASSIFY_ID
9 AND A.BASE_VOLTAGE_ID = V.BASE_VOLTAGE_ID(+)
10 AND C.CLASSIFY_ID = '7002321'
11 AND SDO_RELATE(A.G3E_GEOMETRY,
12 SDO_GEOMETRY(3003,
13 4326,
14 NULL,
15 SDO_ELEM_INFO_ARRAY(1, 1003, 1),
16 SDO_ORDINATE_ARRAY(113.93897922622,
17 22.806658666304,
18 0,
19 114.38475977774,
20 22.806658666304,
21 0,
22 114.38475977774,
23 22.42623522295,
24 0,
25 113.93897922622,
26 22.42623522295,
27 0,
28 113.93897922622,
29 22.806658666304,
30 0)),
31 'MASK=ANYINTERACT') = 'TRUE')
32 WHERE ROWNUM 已选择500行。
已用时间: 00: 00: 06.71
执行计划
----------------------------------------------------------
Plan hash value: 1161815771
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 130K| 263 (1)| 00:00:04 |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | HASH JOIN RIGHT OUTER | | 501 | 130K| 263 (1)| 00:00:04 |
| 3 | TABLE ACCESS FULL | DM_BASE_VOLTAGE | 81 | 729 | 3 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 501 | 64629 | 259 (1)| 00:00:04 |
| 5 | TABLE ACCESS BY INDEX ROWID | DM_CLASSIFY | 1 | 23 | 2 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | IDX_DM_CLASSIFY_ID | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | DM_ASSET | 501 | 53106 | 259 (1)| 00:00:04 |
| 8 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 9 | BITMAP AND | | | | | |
| 10 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 11 | SORT ORDER BY | | | | | |
|* 12 | DOMAIN INDEX | IDX_DM_ASSET_SPL | 50100 | | 0 (0)| 00:00:01 |
| 13 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 14 | INDEX RANGE SCAN | IDX_ASSET_CLASSIFY_ID | 50100 | | 135 (1)| 00:00:02 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM 2 - access("A"."BASE_VOLTAGE_ID"="V"."BASE_VOLTAGE_ID"(+))
6 - access("C"."CLASSIFY_ID"='7002321')
12 - access("MDSYS"."SDO_RELATE"("A"."G3E_GEOMETRY","MDSYS"."SDO_GEOMETRY"(3003,4326,NULL,"SDO_ELEM
_INFO_ARRAY"(1,1003,1),"SDO_ORDINATE_ARRAY"(113.93897922622,22.806658666304,0,114.38475977774,22.8066
58666304,0,114.38475977774,22.42623522295,0,113.93897922622,22.42623522295,0,113.93897922622,22.80665
8666304,0)),'MASK=ANYINTERACT')='TRUE')
14 - access("A"."CLASSIFY_ID"='7002321')
统计信息
----------------------------------------------------------
76096 recursive calls
6004 db block gets
78944 consistent gets
0 physical reads
0 redo size
100625 bytes sent via SQL*Net to client
748 bytes received via SQL*Net from client
35 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
500 rows processed

到网上找了下，有两种解决方法，一种是将_b_tree_bitmap_plans设置为false，另一种是将选择性差的索引去掉。

尝试用第一种方法：

SQL> alter session set "_b_tree_bitmap_plans"=false;
SQL> SELECT *
2 FROM (SELECT A.ASSET_ID,
3 C.CLASSIFY_CODE,
4 C.CLASSIFY_NAME,
5 V.NOMINAL_VOLTAGE BASE_NOMINAL_VOLTAGE,
6 TO_CHAR(SDO_UTIL.TO_GMLGEOMETRY(A.G3E_GEOMETRY)) AS GML
7 FROM DM_ASSET A, DM_CLASSIFY C, DM_BASE_VOLTAGE V
8 WHERE A.CLASSIFY_ID = C.CLASSIFY_ID
9 AND A.BASE_VOLTAGE_ID = V.BASE_VOLTAGE_ID(+)
10 AND C.CLASSIFY_ID = '7002321'
11 AND SDO_RELATE(A.G3E_GEOMETRY,
12 SDO_GEOMETRY(3003,
13 4326,
14 NULL,
15 SDO_ELEM_INFO_ARRAY(1, 1003, 1),
16 SDO_ORDINATE_ARRAY(113.93897922622,
17 22.806658666304,
18 0,
19 114.38475977774,
20 22.806658666304,
21 0,
22 114.38475977774,
23 22.42623522295,
24 0,
25 113.93897922622,
26 22.42623522295,
27 0,
28 113.93897922622,
29 22.806658666304,
30 0)),
31 'MASK=ANYINTERACT') = 'TRUE')
32 WHERE ROWNUM 已选择500行。
已用时间: 00: 00: 06.51
执行计划
----------------------------------------------------------
Plan hash value: 1161815771
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 130K| 263 (1)| 00:00:04 |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | HASH JOIN RIGHT OUTER | | 501 | 130K| 263 (1)| 00:00:04 |
| 3 | TABLE ACCESS FULL | DM_BASE_VOLTAGE | 81 | 729 | 3 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 501 | 64629 | 259 (1)| 00:00:04 |
| 5 | TABLE ACCESS BY INDEX ROWID | DM_CLASSIFY | 1 | 23 | 2 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | IDX_DM_CLASSIFY_ID | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | DM_ASSET | 501 | 53106 | 259 (1)| 00:00:04 |
| 8 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 9 | BITMAP AND | | | | | |
| 10 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 11 | SORT ORDER BY | | | | | |
|* 12 | DOMAIN INDEX | IDX_DM_ASSET_SPL | 50100 | | 0 (0)| 00:00:01 |
| 13 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 14 | INDEX RANGE SCAN | IDX_ASSET_CLASSIFY_ID | 50100 | | 135 (1)| 00:00:02 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM 2 - access("A"."BASE_VOLTAGE_ID"="V"."BASE_VOLTAGE_ID"(+))
6 - access("C"."CLASSIFY_ID"='7002321')
12 - access("MDSYS"."SDO_RELATE"("A"."G3E_GEOMETRY","MDSYS"."SDO_GEOMETRY"(3003,4326,NULL,"SDO_ELEM
_INFO_ARRAY"(1,1003,1),"SDO_ORDINATE_ARRAY"(113.93897922622,22.806658666304,0,114.38475977774,22.8066
58666304,0,114.38475977774,22.42623522295,0,113.93897922622,22.42623522295,0,113.93897922622,22.80665
8666304,0)),'MASK=ANYINTERACT')='TRUE')
14 - access("A"."CLASSIFY_ID"='7002321')
统计信息
----------------------------------------------------------
76096 recursive calls
6002 db block gets
78944 consistent gets
0 physical reads
0 redo size
100625 bytes sent via SQL*Net to client
748 bytes received via SQL*Net from client
35 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
500 rows processed

DM_ASSET有7百多万的数据，而CLASSIFY_ID的值只有39个，其中38个值可以走索引，一个值的数量非常多，典型的数据不均匀。
select CLASSIFY_ID,count(1) from DM_ASSET group by CLASSIFY_ID order by 2;
7002386 1
7002369 3
7002381 13
7002513 16
7002349 18
7002333 36
7002474 166
7002502 276
7002345 1131
7002344 1423
7002382 1709
7002359 1791
7002510 6340
7002374 6684
7002358 7286
7002361 8750
7002379 11189
7002340 12622
7002473 12717
7002348 14901
7002360 15722
7002335 17939
7002336 21492
7002500 28304
7002363 42883
7002343 49865
7002472 51327
7002321 76098
7002373 89485
7002515 110765
7002378 128512
7002380 147776
7002499 235166
7002370 271190
7002501 370439
7002398 456259
7002496 830986
7002401 4361079

尝试用第二种方法：删除CLASSIFY_ID上的索引。
SQL> SELECT *
2 FROM (SELECT A.ASSET_ID,
3 C.CLASSIFY_CODE,
4 C.CLASSIFY_NAME,
5 V.NOMINAL_VOLTAGE BASE_NOMINAL_VOLTAGE,
6 TO_CHAR(SDO_UTIL.TO_GMLGEOMETRY(A.G3E_GEOMETRY)) AS GML
7 FROM DM_ASSET A, DM_CLASSIFY C, DM_BASE_VOLTAGE V
8 WHERE A.CLASSIFY_ID = C.CLASSIFY_ID
9 AND A.BASE_VOLTAGE_ID = V.BASE_VOLTAGE_ID(+)
10 AND C.CLASSIFY_ID = '7002321'
11 AND SDO_RELATE(A.G3E_GEOMETRY,
12 SDO_GEOMETRY(3003,
13 4326,
14 NULL,
15 SDO_ELEM_INFO_ARRAY(1, 1003, 1),
16 SDO_ORDINATE_ARRAY(113.93897922622,
17 22.806658666304,
18 0,
19 114.38475977774,
20 22.806658666304,
21 0,
22 114.38475977774,
23 22.42623522295,
24 0,
25 113.93897922622,
26 22.42623522295,
27 0,
28 113.93897922622,
29 22.806658666304,
30 0)),
31 'MASK=ANYINTERACT') = 'TRUE')
32 WHERE ROWNUM 已选择500行。
已用时间: 00: 00: 00.50
执行计划
----------------------------------------------------------
Plan hash value: 4025821404
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 130K| 10563 (1)| 00:02:07 |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | HASH JOIN RIGHT OUTER | | 501 | 130K| 10563 (1)| 00:02:07 |
| 3 | TABLE ACCESS FULL | DM_BASE_VOLTAGE | 81 | 729 | 3 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 501 | 64629 | 10560 (1)| 00:02:07 |
| 5 | TABLE ACCESS BY INDEX ROWID| DM_CLASSIFY | 1 | 23 | 2 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | IDX_DM_CLASSIFY_ID | 1 | | 1 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID| DM_ASSET | 501 | 53106 | 10560 (1)| 00:02:07 |
|* 8 | DOMAIN INDEX | IDX_DM_ASSET_SPL | | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM 2 - access("A"."BASE_VOLTAGE_ID"="V"."BASE_VOLTAGE_ID"(+))
6 - access("C"."CLASSIFY_ID"='7002321')
7 - filter("A"."CLASSIFY_ID"='7002321')
8 - access("MDSYS"."SDO_RELATE"("A"."G3E_GEOMETRY","MDSYS"."SDO_GEOMETRY"(3003,4326,NULL,"
SDO_ELEM_INFO_ARRAY"(1,1003,1),"SDO_ORDINATE_ARRAY"(113.93897922622,22.806658666304,0,114.384
75977774,22.806658666304,0,114.38475977774,22.42623522295,0,113.93897922622,22.42623522295,0,
113.93897922622,22.806658666304,0)),'MASK=ANYINTERACT')='TRUE')
统计信息
----------------------------------------------------------
3965 recursive calls
6006 db block gets
30388 consistent gets
3 physical reads
340 redo size
94355 bytes sent via SQL*Net to client
748 bytes received via SQL*Net from client
35 SQL*Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
500 rows processed

问题又来了，删除索引后，这个SQL是快了，但原本根据CLASSIFY_ID走索引的语句性能出了问题。曾经在itpub上看到newid这么用，建一个函数索引：
CREATE INDEX IDX_ASSET_CLASSIFY_ID ON DM_ASSET(CASE WHEN CLASSIFY_ID !='7002401' THEN CLASSIFY_ID ELSE NULL END) nologging;
1. 上面的SQL执行也是非常快。
2. 根据CLASSIFY_ID的代码需要改一下
if(CLASSIFY_ID = '7002401') {--如果是这个值，则走全部扫描
select * from DM_ASSET where CLASSIFY_ID = '7002321';
} else {--如果是这个值，则走索引
select * from DM_ASSET where (CASE WHEN CLASSIFY_ID !='7002401'
THEN CLASSIFY_ID ELSE NULL END)='7002401'
}

Kenyataan Laman Web ini

Kandungan artikel ini disumbangkan secara sukarela oleh netizen, dan hak cipta adalah milik pengarang asal. Laman web ini tidak memikul tanggungjawab undang-undang yang sepadan. Jika anda menemui sebarang kandungan yang disyaki plagiarisme atau pelanggaran, sila hubungi admin@php.cn

Alat AI Hot

Undresser.AI Undress

Apl berkuasa AI untuk mencipta foto bogel yang realistik

AI Clothes Remover

Alat AI dalam talian untuk mengeluarkan pakaian daripada foto.

Undress AI Tool

Gambar buka pakaian secara percuma

Clothoff.io

Penyingkiran pakaian AI

AI Hentai Generator

Menjana ai hentai secara percuma.

Tunjukkan Lagi

Artikel Panas

R.E.P.O. Kristal tenaga dijelaskan dan apa yang mereka lakukan (kristal kuning)

2 minggu yang lalu By 尊渡假赌尊渡假赌尊渡假赌

Repo: Cara menghidupkan semula rakan sepasukan

4 minggu yang lalu By 尊渡假赌尊渡假赌尊渡假赌

Hello Kitty Island Adventure: Cara mendapatkan biji gergasi

3 minggu yang lalu By 尊渡假赌尊渡假赌尊渡假赌

Berapa lama masa yang diperlukan untuk mengalahkan fiksyen berpecah?

3 minggu yang lalu By DDD

R.E.P.O. Simpan Fail Lokasi: Di manakah & bagaimana untuk melindunginya?

3 minggu yang lalu By DDD

Tunjukkan Lagi

Alat panas

Notepad++7.3.1

Editor kod yang mudah digunakan dan percuma

SublimeText3 versi Cina

Versi Cina, sangat mudah digunakan

Hantar Studio 13.0.1

Persekitaran pembangunan bersepadu PHP yang berkuasa

Dreamweaver CS6

Alat pembangunan web visual

SublimeText3 versi Mac

Perisian penyuntingan kod peringkat Tuhan (SublimeText3)

Tunjukkan Lagi

Topik panas

Di manakah pintu masuk log masuk untuk e-mel gmail?

7318

Tutorial Java

1625

Tutorial CakePHP

1349

Tutorial Laravel

1261

Tutorial PHP

1209

Tunjukkan Lagi

Related knowledge

Perbandingan prestasi rangka kerja Java yang berbeza Jun 05, 2024 pm 07:14 PM

Perbandingan prestasi rangka kerja Java yang berbeza: Pemprosesan permintaan REST API: Vert.x adalah yang terbaik, dengan kadar permintaan 2 kali SpringBoot dan 3 kali Dropwizard. Pertanyaan pangkalan data: HibernateORM SpringBoot adalah lebih baik daripada Vert.x dan ORM Dropwizard. Operasi caching: Pelanggan Hazelcast Vert.x lebih unggul daripada mekanisme caching SpringBoot dan Dropwizard. Rangka kerja yang sesuai: Pilih mengikut keperluan aplikasi Vert.x sesuai untuk perkhidmatan web berprestasi tinggi, SpringBoot sesuai untuk aplikasi intensif data, dan Dropwizard sesuai untuk seni bina perkhidmatan mikro.

Koleksi lengkap formula fungsi excel May 07, 2024 pm 12:04 PM

1. Fungsi SUM digunakan untuk menjumlahkan nombor dalam lajur atau sekumpulan sel, contohnya: =SUM(A1:J10). 2. Fungsi AVERAGE digunakan untuk mengira purata nombor dalam lajur atau sekumpulan sel, contohnya: =AVERAGE(A1:A10). 3. Fungsi COUNT, digunakan untuk mengira bilangan nombor atau teks dalam lajur atau sekumpulan sel, contohnya: =COUNT(A1:A10) 4. Fungsi IF, digunakan untuk membuat pertimbangan logik berdasarkan syarat yang ditentukan dan mengembalikan hasil yang sepadan.

Pembalikan nilai kunci tatasusunan PHP: analisis perbandingan prestasi kaedah yang berbeza May 03, 2024 pm 09:03 PM

Perbandingan prestasi kaedah membalik nilai kunci tatasusunan PHP menunjukkan bahawa fungsi array_flip() berprestasi lebih baik daripada gelung for dalam tatasusunan besar (lebih daripada 1 juta elemen) dan mengambil masa yang lebih singkat. Kaedah gelung untuk membalikkan nilai kunci secara manual mengambil masa yang agak lama.

Bagaimana untuk mengoptimumkan prestasi program berbilang benang dalam C++? Jun 05, 2024 pm 02:04 PM

Teknik berkesan untuk mengoptimumkan prestasi berbilang benang C++ termasuk mengehadkan bilangan utas untuk mengelakkan perbalahan sumber. Gunakan kunci mutex ringan untuk mengurangkan perbalahan. Optimumkan skop kunci dan minimumkan masa menunggu. Gunakan struktur data tanpa kunci untuk menambah baik keselarasan. Elakkan sibuk menunggu dan maklumkan urutan ketersediaan sumber melalui acara.

Pengecualian Fungsi C++ Lanjutan: Pengendalian Ralat Tersuai May 01, 2024 pm 06:39 PM

Pengendalian pengecualian dalam C++ boleh dipertingkatkan melalui kelas pengecualian tersuai yang menyediakan mesej ralat khusus, maklumat kontekstual dan melaksanakan tindakan tersuai berdasarkan jenis ralat. Tentukan kelas pengecualian yang diwarisi daripada std::exception untuk memberikan maklumat ralat tertentu. Gunakan kata kunci lontaran untuk membuang pengecualian tersuai. Gunakan dynamic_cast dalam blok try-catch untuk menukar pengecualian yang ditangkap kepada jenis pengecualian tersuai. Dalam kes sebenar, fungsi open_file membuang pengecualian FileNotFoundException Menangkap dan mengendalikan pengecualian boleh memberikan mesej ralat yang lebih spesifik.

Perbandingan prestasi C++ dengan bahasa lain Jun 01, 2024 pm 10:04 PM

Apabila membangunkan aplikasi berprestasi tinggi, C++ mengatasi bahasa lain, terutamanya dalam penanda aras mikro. Dalam penanda aras makro, kemudahan dan mekanisme pengoptimuman bahasa lain seperti Java dan C# mungkin berprestasi lebih baik. Dalam kes praktikal, C++ berprestasi baik dalam pemprosesan imej, pengiraan berangka dan pembangunan permainan, dan kawalan langsungnya terhadap pengurusan memori dan akses perkakasan membawa kelebihan prestasi yang jelas.

Apakah kesan prestasi menukar tatasusunan PHP kepada objek? Apr 30, 2024 am 08:39 AM

Dalam PHP, penukaran tatasusunan kepada objek akan memberi kesan pada prestasi, yang dipengaruhi terutamanya oleh faktor seperti saiz tatasusunan, kerumitan dan kelas objek. Untuk mengoptimumkan prestasi, pertimbangkan untuk menggunakan iterator tersuai, mengelakkan penukaran yang tidak perlu, tatasusunan penukaran kelompok dan teknik lain.

Perkara yang perlu diambil perhatian apabila fungsi Golang menerima parameter peta Jun 04, 2024 am 10:31 AM

Apabila menghantar peta ke fungsi dalam Go, salinan akan dibuat secara lalai dan pengubahsuaian pada salinan tidak akan menjejaskan peta asal. Jika anda perlu mengubah suai peta asal, anda boleh menghantarnya melalui penuding. Peta kosong perlu dikendalikan dengan berhati-hati, kerana ia secara teknikal adalah penunjuk sifar, dan menghantar peta kosong kepada fungsi yang menjangkakan peta tidak kosong akan menyebabkan ralat.

See all articles