巧用函数索引性能调优-mysql教程-PHP中文網

首頁

資料庫

mysql教程

巧用函数索引性能调优

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 03:28 PM

sql函數效能執行索引調優

今天调了一条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'
}

陳述

本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn

MySQL：初學者的基本技能Apr 18, 2025 am 12:24 AM

MySQL適合初學者學習數據庫技能。 1.安裝MySQL服務器和客戶端工具。 2.理解基本SQL查詢，如SELECT。 3.掌握數據操作：創建表、插入、更新、刪除數據。 4.學習高級技巧：子查詢和窗口函數。 5.調試和優化：檢查語法、使用索引、避免SELECT*，並使用LIMIT。

MySQL：結構化數據和關係數據庫Apr 18, 2025 am 12:22 AM

MySQL通過表結構和SQL查詢高效管理結構化數據，並通過外鍵實現表間關係。 1.創建表時定義數據格式和類型。 2.使用外鍵建立表間關係。 3.通過索引和查詢優化提高性能。 4.定期備份和監控數據庫確保數據安全和性能優化。

MySQL：解釋的關鍵功能和功能Apr 18, 2025 am 12:17 AM

MySQL是一個開源的關係型數據庫管理系統，廣泛應用於Web開發。它的關鍵特性包括：1.支持多種存儲引擎，如InnoDB和MyISAM，適用於不同場景；2.提供主從復制功能，利於負載均衡和數據備份；3.通過查詢優化和索引使用提高查詢效率。

SQL的目的：與MySQL數據庫進行交互Apr 18, 2025 am 12:12 AM

SQL用於與MySQL數據庫交互，實現數據的增、刪、改、查及數據庫設計。 1）SQL通過SELECT、INSERT、UPDATE、DELETE語句進行數據操作；2）使用CREATE、ALTER、DROP語句進行數據庫設計和管理；3）複雜查詢和數據分析通過SQL實現，提升業務決策效率。

初學者的MySQL：開始數據庫管理Apr 18, 2025 am 12:10 AM

MySQL的基本操作包括創建數據庫、表格，及使用SQL進行數據的CRUD操作。 1.創建數據庫：CREATEDATABASEmy_first_db;2.創建表格：CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY,titleVARCHAR(100)NOTNULL,authorVARCHAR(100)NOTNULL,published_yearINT);3.插入數據：INSERTINTObooks(title,author,published_year)VA

MySQL的角色：Web應用程序中的數據庫Apr 17, 2025 am 12:23 AM

MySQL在Web應用中的主要作用是存儲和管理數據。 1.MySQL高效處理用戶信息、產品目錄和交易記錄等數據。 2.通過SQL查詢，開發者能從數據庫提取信息生成動態內容。 3.MySQL基於客戶端-服務器模型工作，確保查詢速度可接受。

mysql：構建您的第一個數據庫Apr 17, 2025 am 12:22 AM

構建MySQL數據庫的步驟包括：1.創建數據庫和表，2.插入數據，3.進行查詢。首先，使用CREATEDATABASE和CREATETABLE語句創建數據庫和表，然後用INSERTINTO語句插入數據，最後用SELECT語句查詢數據。

MySQL：一種對數據存儲的初學者友好方法Apr 17, 2025 am 12:21 AM

MySQL適合初學者，因為它易用且功能強大。 1.MySQL是關係型數據庫，使用SQL進行CRUD操作。 2.安裝簡單，需配置root用戶密碼。 3.使用INSERT、UPDATE、DELETE、SELECT進行數據操作。 4.複雜查詢可使用ORDERBY、WHERE和JOIN。 5.調試需檢查語法，使用EXPLAIN分析查詢。 6.優化建議包括使用索引、選擇合適數據類型和良好編程習慣。

See all articles