巧用函数索引性能调优-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

巧用函数索引性能调优

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 03:28 PM

sqlfunctionperformanceimplementindexTuning

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

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Explain the InnoDB Buffer Pool and its importance for performance.Apr 19, 2025 am 12:24 AM

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

MySQL vs. Other Programming Languages: A ComparisonApr 19, 2025 am 12:22 AM

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

Learning MySQL: A Step-by-Step Guide for New UsersApr 19, 2025 am 12:19 AM

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL: Essential Skills for Beginners to MasterApr 18, 2025 am 12:24 AM

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL: Structured Data and Relational DatabasesApr 18, 2025 am 12:22 AM

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL: Key Features and Capabilities ExplainedApr 18, 2025 am 12:17 AM

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

The Purpose of SQL: Interacting with MySQL DatabasesApr 18, 2025 am 12:12 AM

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

MySQL for Beginners: Getting Started with Database ManagementApr 18, 2025 am 12:10 AM

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

See all articles