search
HomeDatabaseMysql Tutorial数据库设计--数据的垂直拆分

数据库设计--数据的垂直拆分

Jun 07, 2016 pm 03:56 PM
verticalFieldSplitdatadatabasein the tabledesign

如果表字段太多,如果表中有些字段比较大,即便是你只查有限的几个字段,在做表关联和全表扫的时候,由于扫描的数据块多,性能方面还是会不理想。因为oracle扫描的时候是按照块为单位扫描,读取的时候也是按块为单位读取,所以这种功能无法在SQL层面上优化的

如果表字段太多,如果表中有些字段比较大,即便是你只查有限的几个字段,在做表关联和全表扫的时候,由于扫描的数据块多,性能方面还是会不理想。因为oracle扫描的时候是按照块为单位扫描,读取的时候也是按块为单位读取,所以这种功能无法在SQL层面上优化的时候,可以考虑做数据的垂直切分,下面来做个试验:

--制造数据不做垂直切分
create table test(
a number,
b varchar2(4000),
c varchar2(4000),
d varchar2(4000),
e varchar2(4000),
f varchar2(4000),
g varchar2(4000),
h varchar2(4000)
);
INSERT INTO test
SELECT ROWNUM,
rpad('*', 4000, 1),
rpad('*', 4000, 1),
rpad('*', 4000, 1),
rpad('*', 4000, 1),
rpad('*', 4000, 1),
rpad('*', 4000, 1),
rpad('*', 4000, 1)
FROM DUAL
CONNECT BY ROWNUM commit;
create table test1 as select * from test;

--制造数据做垂直切分
create table test_cuizhi(
a number
);
INSERT INTO test_cuizhi
SELECT ROWNUM
FROM DUAL
CONNECT BY ROWNUM commit;
create table test_cuizhi1 as select * from test_cuizhi;

--开始测试,只是取两个最小的字段
SQL> set timing on
SQL> set autotrace traceonly
SQL> select t.a,t1.a from test t, test1 t1 where t.a=t1.a;
已选择100000行。
已用时间: 00: 00: 53.17
执行计划
----------------------------------------------------------
Plan hash value: 2400077556
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 44504 | 1129K| 173K (1)| 00:34:38 |
|* 1 | HASH JOIN | | 44504 | 1129K| 173K (1)| 00:34:38 |
| 2 | TABLE ACCESS FULL| TEST | 44504 | 564K| 87801 (1)| 00:17:34 |
| 3 | TABLE ACCESS FULL| TEST1 | 117K| 1490K| 85344 (1)| 00:17:05 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."A"="T1"."A")
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
52 recursive calls
0 db block gets
795627 consistent gets
534917 physical reads
0 redo size
1664840 bytes sent via SQL*Net to client
73664 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
100000 rows processed
SQL> /
已选择100000行。
已用时间: 00: 00: 33.36
执行计划
----------------------------------------------------------
Plan hash value: 2400077556
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 44504 | 1129K| 173K (1)| 00:34:38 |
|* 1 | HASH JOIN | | 44504 | 1129K| 173K (1)| 00:34:38 |
| 2 | TABLE ACCESS FULL| TEST | 44504 | 564K| 87801 (1)| 00:17:34 |
| 3 | TABLE ACCESS FULL| TEST1 | 117K| 1490K| 85344 (1)| 00:17:05 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."A"="T1"."A")
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
795446 consistent gets
552087 physical reads
0 redo size
1664840 bytes sent via SQL*Net to client
73664 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100000 rows processed


SQL> select t.a,t1.a from test_cuizhi t, test_cuizhi1 t1 where t.a=t1.a;
已选择100000行。
已用时间: 00: 00: 06.17
执行计划
----------------------------------------------------------
Plan hash value: 2501302817
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 88629 | 2250K| | 310 (2)| 00:00:04 |
|* 1 | HASH JOIN | | 88629 | 2250K| 2168K| 310 (2)| 00:00:04 |
| 2 | TABLE ACCESS FULL| TEST_CUIZHI | 88629 | 1125K| | 42 (3)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TEST_CUIZHI1 | 101K| 1288K| | 39 (3)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."A"="T1"."A")
Note
-----
- dynamic sampling used for this statement


统计信息
----------------------------------------------------------
52 recursive calls
0 db block gets
7139 consistent gets
153 physical reads
0 redo size
1664840 bytes sent via SQL*Net to client
73664 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
100000 rows processed


SQL> /
已选择100000行。
已用时间: 00: 00: 06.06
执行计划
----------------------------------------------------------
Plan hash value: 2501302817
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 88629 | 2250K| | 310 (2)| 00:00:04 |
|* 1 | HASH JOIN | | 88629 | 2250K| 2168K| 310 (2)| 00:00:04 |
| 2 | TABLE ACCESS FULL| TEST_CUIZHI | 88629 | 1125K| | 42 (3)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TEST_CUIZHI1 | 101K| 1288K| | 39 (3)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."A"="T1"."A")
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
7008 consistent gets
0 physical reads
0 redo size
1664840 bytes sent via SQL*Net to client
73664 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100000 rows processed
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
How does MySQL handle data replication?How does MySQL handle data replication?Apr 28, 2025 am 12:25 AM

MySQL processes data replication through three modes: asynchronous, semi-synchronous and group replication. 1) Asynchronous replication performance is high but data may be lost. 2) Semi-synchronous replication improves data security but increases latency. 3) Group replication supports multi-master replication and failover, suitable for high availability requirements.

How can you use the EXPLAIN statement to analyze query performance?How can you use the EXPLAIN statement to analyze query performance?Apr 28, 2025 am 12:24 AM

The EXPLAIN statement can be used to analyze and improve SQL query performance. 1. Execute the EXPLAIN statement to view the query plan. 2. Analyze the output results, pay attention to access type, index usage and JOIN order. 3. Create or adjust indexes based on the analysis results, optimize JOIN operations, and avoid full table scanning to improve query efficiency.

How do you back up and restore a MySQL database?How do you back up and restore a MySQL database?Apr 28, 2025 am 12:23 AM

Using mysqldump for logical backup and MySQLEnterpriseBackup for hot backup are effective ways to back up MySQL databases. 1. Use mysqldump to back up the database: mysqldump-uroot-pmydatabase>mydatabase_backup.sql. 2. Use MySQLEnterpriseBackup for hot backup: mysqlbackup--user=root-password=password--backup-dir=/path/to/backupbackup. When recovering, use the corresponding life

What are some common causes of slow queries in MySQL?What are some common causes of slow queries in MySQL?Apr 28, 2025 am 12:18 AM

The main reasons for slow MySQL query include missing or improper use of indexes, query complexity, excessive data volume and insufficient hardware resources. Optimization suggestions include: 1. Create appropriate indexes; 2. Optimize query statements; 3. Use table partitioning technology; 4. Appropriately upgrade hardware.

What are views in MySQL?What are views in MySQL?Apr 28, 2025 am 12:04 AM

MySQL view is a virtual table based on SQL query results and does not store data. 1) Views simplify complex queries, 2) Enhance data security, and 3) Maintain data consistency. Views are stored queries in databases that can be used like tables, but data is generated dynamically.

What are the differences in syntax between MySQL and other SQL dialects?What are the differences in syntax between MySQL and other SQL dialects?Apr 27, 2025 am 12:26 AM

MySQLdiffersfromotherSQLdialectsinsyntaxforLIMIT,auto-increment,stringcomparison,subqueries,andperformanceanalysis.1)MySQLusesLIMIT,whileSQLServerusesTOPandOracleusesROWNUM.2)MySQL'sAUTO_INCREMENTcontrastswithPostgreSQL'sSERIALandOracle'ssequenceandt

What is MySQL partitioning?What is MySQL partitioning?Apr 27, 2025 am 12:23 AM

MySQL partitioning improves performance and simplifies maintenance. 1) Divide large tables into small pieces by specific criteria (such as date ranges), 2) physically divide data into independent files, 3) MySQL can focus on related partitions when querying, 4) Query optimizer can skip unrelated partitions, 5) Choosing the right partition strategy and maintaining it regularly is key.

How do you grant and revoke privileges in MySQL?How do you grant and revoke privileges in MySQL?Apr 27, 2025 am 12:21 AM

How to grant and revoke permissions in MySQL? 1. Use the GRANT statement to grant permissions, such as GRANTALLPRIVILEGESONdatabase_name.TO'username'@'host'; 2. Use the REVOKE statement to revoke permissions, such as REVOKEALLPRIVILEGESONdatabase_name.FROM'username'@'host' to ensure timely communication of permission changes.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

PhpStorm Mac version

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool