查看: 6688|回复: 11
blob字段存储占用空间问题？？？
论坛徽章:64
54万行的blob字段使占用了4222m空间.而别的字段和blob字段分开存储的，blob字段的大小也不大，怎么就占用了这么多空间呢？
具体请看以下信息：
SQL& select count(*) from report_305;
&&COUNT(*)
----------
& & 540100
SQL& select&&dbms_lob.getlength(actions) from report_305 where dbms_lob.getlengt
h(actions) &1000;
DBMS_LOB.GETLENGTH(ACTIONS)
---------------------------
& && && && && && && &&&1828
& && && && && && && &&&5562
& && && && && && && &&&1096
& && && && && && &&&2092279
SQL& select distinct dbms_lob.getlength(actions),count(*) from report_305 group by&&dbms_lob.getlength(actions)
&&2&&having count(*)&1000;
DBMS_LOB.GETLENGTH(ACTIONS)& &COUNT(*)
--------------------------- ----------
& && && && && && && && &314& &&&535852
& && && && && && && && &451& && & 3853
SQL& select segment_name from dba_lobs&&where owner='REPORT' and&&table_name='RE
PORT_305';
SEGMENT_NAME
------------------------------------------------------------
BLOB_SEG_305
SQL& select bytes/||'M' as size_m,extents,initial_extent,next_extent
&&2&&from dba_segments where tablespace_name='BLOB_TS' and&&segment_name='BLOB_S
& && &EXTENTS INITIAL_EXTENT NEXT_EXTENT
--------------------------------------------------------------------------------
-- ---------- -------------- -----------
& && && &4222& && &&&1048576& &&&1048576
论坛徽章:18
select * from user_lobs
论坛徽章:19
对于OUT ROW存储的LOB，哪怕内容再少，也要占一个CHUNK。
而CHUNK的大小至少为1个db block。你的db_block_size是多少？
假设是8K，显然你的LOB大部分是不到1K的，有超过90%的空间都浪费了。
54万 乘以 8k 是不是约等于4个GB?
如果都是几百字节的小LOB，建议采用IN ROW存储；或建一个db_block_size=2048的表空间来存储。
论坛徽章:64
SQL& select chunk,pctversion,retention,nvl(freepools,0),cache,logging,in_row
&&2& &from user_lobs where segment_name='BLOB_SEG_305';
& &&&CHUNK PCTVERSION&&RETENTION NVL(FREEPOOLS,0) CACHE& && && && && & LOGGING
& && &IN_ROW
---------- ---------- ---------- ---------------- -------------------- ---------
----- ------
& && &8192& && && &10& && &10800& && && && && & 0 YES& && && && && && &YES
SQL&&&select sum(dbms_lob.getlength(actions))/ all_real_s from report_305;
ALL_REAL_S
----------
164.211569
从PCTVERSION=10看只有每个extent只有10%的空间是空闲的。而4222M中只有不到200m的空间是存的真正的数据？
论坛徽章:64
最初由 rejoice999 发布
[B]对于OUT ROW存储的LOB，哪怕内容再少，也要占一个CHUNK。
而CHUNK的大小至少为1个db block。你的db_block_size是多少？
假设是8K，显然你的LOB大部分是不到1K的，有超过90%的空间都浪费了。
54万 乘以 8k 是不是约等于4个GB?
如果都是几百字节的小LOB，建议采用IN ROW存储；或建一个db_block_size=2048的表空间来存储。 [/B]
我只考虑到将来的blob一般应该在4k以上，所以我选择了out row，但现在的数据是测试数据，都比较小。而我又不清楚blob的存储原理，所以在奇怪呢。我想现在我已经理解了。谢谢。
论坛徽章:19
和PCTVERSION是两码事
我说得够清楚了，一个CHUNK只能存一个LOB。
请回去看一下书吧。
最初由 cjf107 发布
[B]SQL& select chunk,pctversion,retention,nvl(freepools,0),cache,logging,in_row
&&2& &from user_lobs where segment_name='BLOB_SEG_305';
& &&&CHUNK PCTVERSION&&RETENTION NVL(FREEPOOLS,0) CACHE& && && && && & LOGGING
& && &IN_ROW
---------- ---------- ---------- ---------------- -------------------- ---------
----- ------
& && &8192& && && &10& && &10800& && && && && & 0 YES& && && && && && &YES
SQL&&&select sum(dbms_lob.getlength(actions))/ all_real_s from report_305;
ALL_REAL_S
----------
164.211569
从PCTVERSION=10看只有每个extent只有10%的空间是空闲的。而4222M中只有不到200m的空间是存的真正的数据？ [/B]
论坛徽章:18
PCTVERSION这个不是标现有的空余空间的，而且你的retention真的需要这么大吗？lob的表空间里还有lobindex，使用空间肯定比dbms_log.getlength多，但是多也多不了哪去，你的lob的平均长度如果和chunk差太远了，用disable in row没意义，甚至用lob都没意义
论坛徽章:64
最初由 oracledba 发布
[B]PCTVERSION这个不是标现有的空余空间的，而且你的retention真的需要这么大吗？lob的表空间里还有lobindex，使用空间肯定比dbms_log.getlength多，但是多也多不了哪去，你的lob的平均长度如果和chunk差太远了，用disable in row没意义，甚至用lob都没意义 [/B]
我的理解上pctversion与pctfree类似，表示用于以后更新或别的用途，是事先留好的。
而我现在只有insert，不会有update，所以我是不是最好将pctversion设为0？
而我用out row是因为将来正式环境中的blob绝大部分都是大于4k的。
而因为blob比较大（&4k），所以我最好不要cache呢（将来查询同一个块的频率还是未知数）。
论坛徽章:19
我的理解上pctversion与pctfree类似，表示用于以后更新或别的用途，是事先留好的。
错，回去仔细看书。
而我现在只有insert，不会有update，所以我是不是最好将pctversion设为0？
最好不要设为0，除非是一次性INSERT进去没用修改，只有查询。
而我用out row是因为将来正式环境中的blob绝大部分都是大于4k的。
而因为blob比较大（&4k），所以我最好不要cache呢（将来查询同一个块的频率还是未知数）。
CACHE一般没有必要用。而且用了还容易有问题。
论坛徽章:4
itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有　　　　
　北京市公安局海淀分局网监中心备案编号：　广播电视节目制作经营许可证：编号（京）字第1149号ASP.NET下传大文件（几百兆）到oracle数据库Blob字段中，内存溢出，该如何解决 - ASP.NET当前位置:& &&&ASP.NET下传大文件（几百兆）到oracle数据库Blob字ASP.NET下传大文件（几百兆）到oracle数据库Blob字段中，内存溢出，该如何解决&&网友分享于：&&浏览：37次ASP.NET上传大文件（几百兆）到oracle数据库Blob字段中，内存溢出，该怎么解决？HttpPostedFile upFile = FileUpload1.PostedF &
int FileLength = upFile.ContentL &
Byte[] FileByteArray = new Byte[FileLength]; &
Stream StreamObject = upFile.InputS &
int pos = upFile.FileName.LastIndexOf(&\\&); &
string ImgFName = upFile.FileName.Substring(pos + 1); &
StreamObject.Read(FileByteArray, 0, FileLength);代码如上------解决方案--------------------光是用HttpPostedFile上传几百M的文件就没办法想象了
------解决方案--------------------你这么大的文件，为什么一定要保存到数据库？为什么不能以文件的形式存放呢？
------解决方案--------------------1、分段上传（不要一下子读取几百兆的数据一下就上传）2、文件方式传送
------解决方案--------------------用http的话silverlight或者flash分块上传，数据库好歹也找个key-value类型的nosql数据库啊，用oracle这是有多蛋疼
------解决方案--------------------/
------解决方案--------------------Blob可以存的下那么大的图片，但是这样写很占性能，期待有高雅的解决方案。
------解决方案--------------------探讨 可以给个例子吗？因为数据库是客户自己弄的，我们说了不算~
------解决方案--------------------图片传到服务器某个指定目录下，数据库保存文件名。。你那什么客户啊。无语。。就算勉强存进去了，读取的时候得花多长的时间啊
12345678910
12345678910
12345678910 上一篇：下一篇：文章评论相关解决方案 12345678910 Copyright & &&版权所有1. Bolb类型字段说明：&
写入Blob字段和写入其它类型字段的方式非常不同，因为Blob自身有一个cursor，你必须使用cursor对blob进行操作，因而你在写入Blob之前，必须获得cursor才能进行写入，那么如何获得Blob的cursor呢？
这需要你先插入一个empty的blob，这将创建一个blob的cursor，然后你再把这个empty的blob的cursor用select查询出来，这样通过两步操作，你就获得了blob的cursor，可以真正的写入blob数据了。
2. Bolb类型字段保存：
Hibernate的配置文件就不写了 ，需要将Blob字段了类型设为java.sql.Blob，下面直接上代码
public void save(ZyglBlxx bean, InputStream ins) throws WebServiceException {
// TODO Auto-generated method stub
Session session = this.getHibernateTemplate().getSessionFactory().openSession();
//out.write(b)
bean.setDoccontent(BLOB.getEmptyBLOB());
Transaction tr = session.beginTransaction();
bean.setVId(WebServiceEditUtils.getPk());
session.save(bean);
session.flush();
session.refresh(bean, LockMode.UPGRADE);
if(ins!=null){
SerializableBlob sb = (SerializableBlob)bean.getDoccontent();
BLOB b = (BLOB)sb.getWrappedBlob();
OutputStream out = b.getBinaryOutputStream();
int len=-1;
byte[] bt = new byte[2048];
//可以根据实际情况调整，建议使用1024，即每次读1KB
while((len=(ins.read(bt))) != -1)
out.write(bt,0,len);
//建议不要直接用os.write(bt)
out.flush();
ins.close();
out.close();
session.flush();
tr.commit();
session.close();
} catch (IOException e) {
e.printStackTrace();
throw ExceptionManager.getExcption(&18&);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw ExceptionManager.getExcption(&19&);
}为了保存文件时比较方便我这里的参数直接接收为InputStream，其他类型类似
3. Bolb类型字段读取：
public ZyglBlxx getBlxx(Map&String,Object& map){
Session session = this.getHibernateTemplate().getSessionFactory().openSession();
ZyglBlxx bean =
Criteria criteria = session.createCriteria(ZyglBlxx.class);
Set&String& set = map.keySet();
Iterator&String& it = set.iterator();
while(it.hasNext()){
String key = it.next();
Object value = map.get(key);
criteria.add(Property.forName(key).eq(value));
List&ZyglBlxx& list = criteria.list();
if(list.size()&0)bean = list.get(0);
session.close();
}从数据库的读取跟以前一样
字段的读取有点麻烦
public String blobToString(Blob inblob){
String data =
if(inblob == null)
SerializableBlob seb = (SerializableBlob)
BLOB blob = (BLOB)seb.getWrappedBlob();
BufferedInputStream stream = new BufferedInputStream(blob.getBinaryStream());
ByteArrayOutputStream out = new ByteArrayOutputStream();
byte[] buff = new byte[1024];
int i = 0;
while((i=stream.read(buff))!=-1){
out.write(buff,0,i);
data = out.toString();
stream.close();
out.close();
} catch (SQLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}直接调用inblob.getBytes();得到的是乱码，blob.getBinaryStream().available()得到的是0.对于Hibernate3版本只能用上面的方法来解析。
4. 使用JPA2.0读写Bolb类型字段：
使用JPA2.0读写Blob类型就方便的多了，只需要将Blob类型改为byte[],然后在字段上加上@Basic和@Lob注解就可以了。
本文已收录于以下专栏：
相关文章推荐
java hibernate 映射和注解oracle含有blob字段的数据表的pojo源码
1. Bolb类型字段说明： 写入Blob字段和写入其它类型字段的方式非常不同，因为Blob自身有一个cursor，你必须使用cursor对blob进行操作，因而你在写入Blob之前，必须获得curs...
程序员升职加薪指南！还缺一个“证”！
CSDN出品，立即查看！
使用Intellij IDEA创建Hibernate项目，目录结构如下：
其中 assets/app.png 为将要存储的照片，src/hibernate.cfg.xml 为Hib...
一般网站在处理用户上传图片时通常采用两种策略：一是直接把图片存入数据库中的Blob字段；二是数据库中只存储图片的在服务器上的路径信息 ，图片存放在分门别类的文件中，使用的时候从数据库读取路径信息到页面...
通过一个例子给大家介绍一下在Oracle中使用大对象的方法。在这个例子中，目的是要把一个jpeg图像装入Oracle，并且用C++Builder显示出来。如果使用bmp图像，则处理的过程更简单一些，这...
from table1
where col is null or pare(col, empty_clob()) = 0
import java.io.F
import java.io.FileInputS
import java.io.FileOutputS
import java.io...
首先：先了解一下Oracle中的clob与blob字段
Oracle将LOB分为两种：内部LOB和外部LOB。内部LOB包括CLOB,BLOB和NCLOB三种类型，它们的数据存储在数据库中，并且支持...
关于文件保存到Oracle中BLOB字段的方法及例子
-------------------------------------------
public class FileOpClass
CREATE TABLE txjblob(x NUMBER(5) NOT NULL,b BLOB DEFAULT EMPTY_BLOB() NOT NULL)插入：
stmt.executeUpd...
您举报文章：
举报原因：
原文地址：
原因补充：
(最多只允许输入30个字)提高MySQL中InnoDB表BLOB列的存储效率的教程
投稿：goldensun
字体：[ ] 类型：转载 时间：
这篇文章主要介绍了提高MySQL中InnoDB表BLOB列的存储效率的教程,InnoDB的优化在MySQL的优化研究中也是一个非常热门的课题,需要的朋友可以参考下
首先，介绍下关于InnoDB引擎存储格式的几个要点：
1、InnoDB可以选择使用共享表空间或者是独立表空间方式，建议使用独立表空间，便于管理、维护。启用 innodb_file_per_table 选项，5.5以后可以在线动态修改生效，并且执行 ALTER TABLE xx ENGINE = InnoDB 将现有表转成独立表空间，早于5.5的版本，修改完这个选项后，需要重启才能生效；
2、InnoDB的data page默认16KB，5.6版本以后，新增选项 innodb_page_size 可以修改，在5.6以前的版本，只能修改源码重新编译，但并不推荐修改这个配置，除非你非常清楚它有什么优缺点；
3、InnoDB的data page在有新数据写入时，会预留1/16的空间，预留出来的空间可用于后续的新纪录写入，减少频繁的新增data page的开销；
4、每个data page，至少需要存储2行记录。因此理论上行记录最大长度为8KB，但事实上应该更小，因为还有一些InnoDB内部数据结构要存储；
5、受限于InnoDB存储方式，如果数据是顺序写入的话，最理想的情况下，data page的填充率是15/16，但一般没办法保证完全的顺序写入，因此，data page的填充率一般是1/2到15/16。因此每个InnoDB表都最好要有一个自增列作为主键，使得新纪录写入尽可能是顺序的；
6、当data page填充率不足1/2时，InnoDB会进行收缩，释放空闲空间；
7、MySQL 5.6版本的InnoDB引擎当前支持COMPACT、REDUNDANT、DYNAMIC、COMPRESSED四种格式，默认是COMPACT格式，COMPRESSED用的很少且不推荐（见下一条），如果需要用到压缩特性的话，可以直接考虑TokuDB引擎；
8、COMPACT行格式相比REDUNDANT，大概能节省20%的存储空间，COMPRESSED相比COMPACT大概能节省50%的存储空间，但会导致TPS下降了90%。因此强烈不推荐使用COMPRESSED行格式；
9、当行格式为DYNAMIC或COMPRESSED时，TEXT/BLOB之类的长列（long column，也有可能是其他较长的列，不一定只有TEXT/BLOB类型，看具体情况）会完全存储在一个独立的data page里，聚集索引页中只使用20字节的指针指向新的page，这就是所谓的off-page，类似ORACLE的行迁移，磁盘空间浪费较严重，且I/O性能也较差。因此，强烈不建议使用BLOB、TEXT、超过255长度的VARCHAR列类型；
10、当InnoDB的文件格式（innodb_file_format）设置为Antelope，并且行格式为COMPACT 或 REDUNDANT 时，BLOB、TEXT或者长VARCHAR列只会将其前768字节存储在聚集索页中（最大768字节的作用是便于创建前缀索引/prefix index），其余更多的内容存储在额外的page里，哪怕只是多了一个字节。因此，所有列长度越短越好；
11、在off-page中存储的BLOB、TEXT或者长VARCHAR列的page是独享的，不能共享。因此强烈不建议在一个表中使用多个长列。
综上，如果在实际业务中，确实需要在InnoDB表中存储BLOB、TEXT、长VARCHAR列时，有下面几点建议：
1、尽可能将所有数据序列化、压缩之后，存储在同一个列里，避免发生多次off-page；
2、实际最大存储长度低于255的列，转成VARCHAR或者CHAR类型（如果是变长数据二者没区别，如果是定长数据，则使用CHAR类型）；
3、如果无法将所有列整合到一个列，可以退而求其次，根据每个列最大长度进行排列组合后拆分成多个子表，尽量是的每个子表的总行长度小于8KB，减少发生off-page的频率；
4、上述建议是在data page为默认的16KB前提下，如果修改成8KB或者其他大小，请自行根据上述理论进行测试，找到最合适的值；
5、字符型列长度小于255时，无论采用CHAR还是VARCHAR来存储，或者把VARCHAR列长度定义为255，都不会导致实际表空间增大；
6、一般在游戏领域会用到比较多的BLOB列类型，游戏界同行可以关注下。
下面是测试验证过程，有耐心的同学可以慢慢看：
# 测试案例：InnoDB中长列存储效率
# 测试场景描述：
# 在InnoDB表中存储64KB的数据，对比各种不同存储方式# 每个表写入5000行记录，观察最后表空间文件大小对比
#表0：所有数据存储在一个BLOB列中
CREATE TABLE `t_longcol_0` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol` blob NOT NULL COMMENT 'store all data in a blob column',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
#相应的数据写入存储过程：mysp_longcol_0_ins()
CREATE PROCEDURE `mysp_longcol_0_ins`( in cnt int )
set @i = 1;
while @i & cnt do
insert into t_longcol_0(longcol) select repeat('a',65535);
set @i = @i + 1;
#表1：将64KB字节平均存储在9个列中
CREATE TABLE `t_longcol_1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol1` blob NOT NULL COMMENT 'store all data in 9 blob columns',
`longcol2` blob NOT NULL,
`longcol3` blob NOT NULL,
`longcol4` blob NOT NULL,
`longcol5` blob NOT NULL,
`longcol6` blob NOT NULL,
`longcol7` blob NOT NULL,
`longcol8` blob NOT NULL,
`longcol9` blob NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#相应的数据写入存储过程：mysp_longcol_1_ins()
CREATE PROCEDURE `mysp_longcol_1_ins`( in cnt int )
set @i = 1;
while @i & cnt do
insert into t_longcol_1(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9) select
repeat('a',7500),
repeat('a',7500),
repeat('a',7500),
repeat('a',7500),
repeat('a',7500),
repeat('a',7500),
repeat('a',7500),
repeat('a',7500),
repeat('a',5535);
set @i = @i + 1;
#表2：将64KB数据离散存储在多个BLOB列中
CREATE TABLE `t_longcol_2` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol1` blob NOT NULL COMMENT 'store 100 bytes data',
`longcol2` blob NOT NULL COMMENT 'store 100 bytes data',
`longcol3` blob NOT NULL COMMENT 'store 100 bytes data',
`longcol4` blob NOT NULL COMMENT 'store 100 bytes data',
`longcol5` blob NOT NULL COMMENT 'store 100 bytes data',
`longcol6` blob NOT NULL COMMENT 'store 255 bytes data',
`longcol7` blob NOT NULL COMMENT 'store 368 bytes data',
`longcol8` blob NOT NULL COMMENT 'store 496 bytes data',
`longcol9` blob NOT NULL COMMENT 'store 512 bytes data',
`longcol10` blob NOT NULL COMMENT 'store 640 bytes data',
`longcol11` blob NOT NULL COMMENT 'store 768 bytes data',
`longcol12` blob NOT NULL COMMENT 'store 912 bytes data',
`longcol13` blob NOT NULL COMMENT 'store 1024 bytes data',
`longcol14` blob NOT NULL COMMENT 'store 2048 bytes data',
`longcol15` blob NOT NULL COMMENT 'store 3082 bytes data',
`longcol16` blob NOT NULL COMMENT 'store 4096 bytes data',
`longcol17` blob NOT NULL COMMENT 'store 8192 bytes data',
`longcol18` blob NOT NULL COMMENT 'store 16284 bytes data',
`longcol19` blob NOT NULL COMMENT 'store 20380 bytes data',
`longcol20` blob NOT NULL COMMENT 'store 5977 bytes data',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#相应的数据写入存储过程：mysp_longcol_1_ins()
CREATE PROCEDURE `mysp_longcol_1_ins`( in cnt int )
set @i = 1;
while @i & cnt do
insert into t_longcol_2(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10,
longcol11,longcol12,longcol13,longcol14,longcol15,longcol16,longcol17,longcol18,longcol19,longcol20) select
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',256),
repeat('a',368),
repeat('a',496),
repeat('a',512),
repeat('a',640),
repeat('a',768),
repeat('a',912),
repeat('a',1024),
repeat('a',2048),
repeat('a',3082),
repeat('a',4096),
repeat('a',8192),
repeat('a',16284),
repeat('a',20380),
repeat('a',5977);
set @i = @i + 1;
#表3：将64KB数据离散存储在多个CHAR、VARCHAR、BLOB列中
CREATE TABLE `t_longcol_3` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol1` char(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol2` char(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol3` char(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol4` char(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol5` char(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol6` varchar(256) NOT NULL DEFAULT '' COMMENT 'store 255 bytes data',
`longcol7` varchar(368) NOT NULL DEFAULT '' COMMENT 'store 368 bytes data',
`longcol8` varchar(496) NOT NULL DEFAULT '' COMMENT 'store 496 bytes data',
`longcol9` varchar(512) NOT NULL DEFAULT '' COMMENT 'store 512 bytes data',
`longcol10` varchar(640) NOT NULL DEFAULT '' COMMENT 'store 640 bytes data',
`longcol11` varchar(768) NOT NULL DEFAULT '' COMMENT 'store 768 bytes data',
`longcol12` varchar(912) NOT NULL DEFAULT '' COMMENT 'store 912 bytes data',
`longcol13` varchar(1024) NOT NULL DEFAULT '' COMMENT 'store 1024 bytes data',
`longcol14` varchar(2048) NOT NULL DEFAULT '' COMMENT 'store 2048 bytes data',
`longcol15` varchar(3082) NOT NULL DEFAULT '' COMMENT 'store 3082 bytes data',
`longcol16` varchar(4096) NOT NULL DEFAULT '' COMMENT 'store 4096 bytes data',
`longcol17` blob NOT NULL COMMENT 'store 8192 bytes data',
`longcol18` blob NOT NULL COMMENT 'store 16284 bytes data',
`longcol19` blob NOT NULL COMMENT 'store 20380 bytes data',
`longcol20` varchar(5977) NOT NULL DEFAULT '' COMMENT 'store 5977 bytes data',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#相应的数据写入存储过程：mysp_longcol_3_ins()
CREATE PROCEDURE `mysp_longcol_1_ins`( in cnt int )
set @i = 1;
while @i & cnt do
insert into t_longcol_3(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10,
longcol11,longcol12,longcol13,longcol14,longcol15,longcol16,longcol17,longcol18,longcol19,longcol20) select
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',256),
repeat('a',368),
repeat('a',496),
repeat('a',512),
repeat('a',640),
repeat('a',768),
repeat('a',912),
repeat('a',1024),
repeat('a',2048),
repeat('a',3082),
repeat('a',4096),
repeat('a',8192),
repeat('a',16284),
repeat('a',20380),
repeat('a',5977);
set @i = @i + 1;
#表4：将64KB数据离散存储在多个VARCHAR、BLOB列中，对比t_longcol_3中几个列是CHAR的情况
CREATE TABLE `t_longcol_4` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol1` varchar(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol2` varchar(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol3` varchar(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol4` varchar(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol5` varchar(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol6` varchar(256) NOT NULL DEFAULT '' COMMENT 'store 255 bytes data',
`longcol7` varchar(368) NOT NULL DEFAULT '' COMMENT 'store 368 bytes data',
`longcol8` varchar(496) NOT NULL DEFAULT '' COMMENT 'store 496 bytes data',
`longcol9` varchar(512) NOT NULL DEFAULT '' COMMENT 'store 512 bytes data',
`longcol10` varchar(640) NOT NULL DEFAULT '' COMMENT 'store 640 bytes data',
`longcol11` varchar(768) NOT NULL DEFAULT '' COMMENT 'store 768 bytes data',
`longcol12` varchar(912) NOT NULL DEFAULT '' COMMENT 'store 912 bytes data',
`longcol13` varchar(1024) NOT NULL DEFAULT '' COMMENT 'store 1024 bytes data',
`longcol14` varchar(2048) NOT NULL DEFAULT '' COMMENT 'store 2048 bytes data',
`longcol15` varchar(3082) NOT NULL DEFAULT '' COMMENT 'store 3082 bytes data',
`longcol16` varchar(4096) NOT NULL DEFAULT '' COMMENT 'store 4096 bytes data',
`longcol17` blob NOT NULL COMMENT 'store 8192 bytes data',
`longcol18` blob NOT NULL COMMENT 'store 16284 bytes data',
`longcol19` blob NOT NULL COMMENT 'store 20380 bytes data',
`longcol20` varchar(5977) NOT NULL DEFAULT '' COMMENT 'store 5977 bytes data',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#相应的数据写入存储过程：mysp_longcol_4_ins()
CREATE PROCEDURE `mysp_longcol_1_ins`( in cnt int )
set @i = 1;
while @i & cnt do
insert into t_longcol_4(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10,
longcol11,longcol12,longcol13,longcol14,longcol15,longcol16,longcol17,longcol18,longcol19,longcol20) select
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',256),
repeat('a',368),
repeat('a',496),
repeat('a',512),
repeat('a',640),
repeat('a',768),
repeat('a',912),
repeat('a',1024),
repeat('a',2048),
repeat('a',3082),
repeat('a',4096),
repeat('a',8192),
repeat('a',16284),
repeat('a',20380),
repeat('a',5977);
set @i = @i + 1;
#表5：将64KB数据离散存储在多个VARCHAR、BLOB列中，和t_longcol_4相比，变化在于前面的几个列长度改成了255，但实际存储长度还是100字节
CREATE TABLE `t_longcol_5` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol1` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol2` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol3` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol4` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol5` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol6` varchar(256) NOT NULL DEFAULT '' COMMENT 'store 255 bytes data',
`longcol7` varchar(368) NOT NULL DEFAULT '' COMMENT 'store 368 bytes data',
`longcol8` varchar(496) NOT NULL DEFAULT '' COMMENT 'store 496 bytes data',
`longcol9` varchar(512) NOT NULL DEFAULT '' COMMENT 'store 512 bytes data',
`longcol10` varchar(640) NOT NULL DEFAULT '' COMMENT 'store 640 bytes data',
`longcol11` varchar(768) NOT NULL DEFAULT '' COMMENT 'store 768 bytes data',
`longcol12` varchar(912) NOT NULL DEFAULT '' COMMENT 'store 912 bytes data',
`longcol13` varchar(1024) NOT NULL DEFAULT '' COMMENT 'store 1024 bytes data',
`longcol14` varchar(2048) NOT NULL DEFAULT '' COMMENT 'store 2048 bytes data',
`longcol15` varchar(3082) NOT NULL DEFAULT '' COMMENT 'store 3082 bytes data',
`longcol16` varchar(4096) NOT NULL DEFAULT '' COMMENT 'store 4096 bytes data',
`longcol17` blob NOT NULL COMMENT 'store 8192 bytes data',
`longcol18` blob NOT NULL COMMENT 'store 16284 bytes data',
`longcol19` blob NOT NULL COMMENT 'store 20380 bytes data',
`longcol20` varchar(5977) NOT NULL DEFAULT '' COMMENT 'store 5977 bytes data',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#相应的数据写入存储过程：mysp_longcol_5_ins()
CREATE PROCEDURE `mysp_longcol_1_ins`( in cnt int )
set @i = 1;
while @i & cnt do
insert into t_longcol_5(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10,
longcol11,longcol12,longcol13,longcol14,longcol15,longcol16,longcol17,longcol18,longcol19,longcol20) select
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',256),
repeat('a',368),
repeat('a',496),
repeat('a',512),
repeat('a',640),
repeat('a',768),
repeat('a',912),
repeat('a',1024),
repeat('a',2048),
repeat('a',3082),
repeat('a',4096),
repeat('a',8192),
repeat('a',16284),
repeat('a',20380),
repeat('a',5977);
set @i = @i + 1;
#从下面开始，参考第3条建议进行分表，每个表所有列长度总和
#分表1，行最大长度 100 + 100 + 100 + 100 + 100 + 255 + 368 + 496 + 512 + 640 + 768 + 912 + 3082 = 7533 字节
CREATE TABLE `t_longcol_51` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol1` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol2` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol3` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol4` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol5` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol6` varchar(256) NOT NULL DEFAULT '' COMMENT 'store 255 bytes data',
`longcol7` varchar(368) NOT NULL DEFAULT '' COMMENT 'store 368 bytes data',
`longcol8` varchar(496) NOT NULL DEFAULT '' COMMENT 'store 496 bytes data',
`longcol9` varchar(512) NOT NULL DEFAULT '' COMMENT 'store 512 bytes data',
`longcol10` varchar(640) NOT NULL DEFAULT '' COMMENT 'store 640 bytes data',
`longcol11` varchar(768) NOT NULL DEFAULT '' COMMENT 'store 768 bytes data',
`longcol12` varchar(912) NOT NULL DEFAULT '' COMMENT 'store 912 bytes data',
`longcol15` varchar(3082) NOT NULL DEFAULT '' COMMENT 'store 3082 bytes data',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#分表2，行最大长度 1024 + 2048 + 4096 = 7168 字节
CREATE TABLE `t_longcol_52` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol13` varchar(1024) NOT NULL DEFAULT '' COMMENT 'store 1024 bytes data',
`longcol14` varchar(2048) NOT NULL DEFAULT '' COMMENT 'store 2048 bytes data',
`longcol16` varchar(4096) NOT NULL DEFAULT '' COMMENT 'store 4096 bytes data',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#分表3，行最大长度 8192 字节
CREATE TABLE `t_longcol_53` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol17` blob NOT NULL COMMENT 'store 8192 bytes data',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#分表4，行最大长度 16284 + 20380 = 36664 字节
CREATE TABLE `t_longcol_54` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol18` blob NOT NULL COMMENT 'store 16284 bytes data',
`longcol19` blob NOT NULL COMMENT 'store 20380 bytes data',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#分表5，行最大长度 5977 + 4 = 5981 字节
CREATE TABLE `t_longcol_55` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol20` varchar(5977) NOT NULL DEFAULT '' COMMENT 'store 5977 bytes data',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#相应的数据写入存储过程：mysp_longcol_51_ins()
CREATE PROCEDURE `mysp_longcol_51_ins`( in cnt int )
set @i = 1;
while @i & cnt do
insert into t_longcol_51(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10,
longcol11,longcol12,longcol15) select
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',256),
repeat('a',368),
repeat('a',496),
repeat('a',512),
repeat('a',640),
repeat('a',768),
repeat('a',912),
repeat('a',3082);
insert into t_longcol_52(longcol13,longcol14,longcol16) select
repeat('a',1024),
repeat('a',2048),
repeat('a',4096);
insert into t_longcol_53(longcol17) select repeat('a',8192);
insert into t_longcol_54(longcol18,longcol19) select
repeat('a',16284),
repeat('a',20380);
insert into t_longcol_55(longcol20) select repeat('a',5977);
set @i = @i + 1;
上述各个测试表都写入5000行记录后，再来对比下其表空间文件大小，以及重整表空间后的大小，观察碎片率。详细对比见下：
最后一种分表方式中，5个子表的表空间文件大小总和是 40960 + 40960 + 98304 + 286720 + 40960 = 507904 字节。
可以看到，这种方式的总大小和原始表大小差距最小，其他几种存储方式都比这个来的大。
您可能感兴趣的文章:
大家感兴趣的内容
12345678910
最近更新的内容
常用在线小工具