本文探讨在MySQL中处理大量图片数据的技术，包括如何通过`file_content`字段存储二进制文件，选择合适的存储类型（如文本或Blob），以及优化SQL操作如`INSERT`和`SELECT`以应对阿里云服务器带宽限制。
我们使用爬取的世界数据,最终选择数个变量进行绘图。
由于国家、地区繁多,同时我们选取的变量也很多,因此最终得到的Html形式图片,占用非常大。
最终得到大约20GB的图片(这还是精简过许多不太重要的变量之后的)
因此我们这次看一下如何把图片放到数据库里。当然,我们用的阿里云服务器,带宽并不大。因此存的非常慢,不过前端网页是按需请求,所以展示的时候是没问题的。
需要新增一个数据库使用的Mysql过程。
平台数据库：Mysql
数据库字段：file_content为存储文件的字段
<resultMap id="BaseResultMap" type="com.scorpio.bean.FileContent">
<id column="file_tid" jdbcType="VARCHAR" property="fileTid" />
<result column="file_path" jdbcType="VARCHAR" property="filePath" />
<result column="file_path_md" jdbcType="VARCHAR" property="filePathMd" />
<result column="file_content" jdbcType="BINARY" property="fileContent" />
</resultMap>
<select id="selectByFileId" parameterType="java.lang.String"
resultMap="BaseResultMap">
select
file_tid, file_path, file_path_md, file_content
from t_file_content
where file_tid = #{fileTid,jdbcType=VARCHAR}
</select>
<insert id="insertFileContent" parameterType="com.scorpio.bean.FileContent">
insert into t_file_content (file_tid, file_path, file_path_md, file_content)
values (#{fileTid,jdbcType=VARCHAR},#{filePath,jdbcType=VARCHAR},
#{filePathMd,jdbcType=VARCHAR},#{fileContent,jdbcType=BINARY})
</insert>
MySQL使用如下四种类型来处理文本大数据：
类型 长度
tinytext 28–1B（256B）
text 216-1B（64K）
mediumtext 224-1B（16M）
longtext 232-1B（4G）
con = JdbcUtils.getConnection();
String sql = "insert into tab_bin(filename,data) values(?, ?)";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, "a.jpg");
InputStream in = new FileInputStream("f:\\a.jpg");//得到一个输入流对象
pstmt.setBinaryStream(2, in);//为第二个参数赋值为流对象
pstmt.executeUpdate();
con = JdbcUtils.getConnection();
String sql = "select filename,data from tab_bin where id=?";
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, 1);
rs = pstmt.executeQuery();
rs.next();
String filename = rs.getString("filename");
OutputStream out = new FileOutputStream("F:\\" + filename)//使用文件名来创建输出流对象;
InputStream in = rs.getBinaryStream("data")//读取输入流对象;
IOUtils.copy(in, out)//把in中的数据写入到out中;
out.close();
//存数据
con = JdbcUtils.getConnection();
String sql = "insert into tab_bin(filename,data) values(?, ?)";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, "a.jpg");
File file = new File("f:\\a.jpg");
byte[] datas = IOUtils.getBytes(file);//获取文件中的数据
Blob blob = new SerialBlob(datas);//创建Blob对象
pstmt.setBlob(2, blob);//设置Blob类型的参数
pstmt.executeUpdate();
//取数据
con = JdbcUtils.getConnection();
String sql = "select filename,data from tab_bin where id=?";
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, 1);
rs = pstmt.executeQuery();
rs.next();
String filename = rs.getString("filename");
File file = new File("F:\\" + filename) ;
Blob blob = rs.getBlob("data");
byte[] datas = blob.getBytes(0, (int)file.length());
IOUtils.writeByteArrayToFile(file, datas);
-q: 直接转存
-t: 不写表创建信息(这里需要注意，因为导出的时候没有导出表创建信息，所以在导入的时候，必须先导入表结构，再导入数据，不然会直接报错)
--single-transaction: 参数的作用，设置事务的隔离级别为可重复读，即REPEATABLE READ，这样能保证在一个事务中所有相同的查询读取到同样的数据，也就大概保证了在dump期间，如果其他innodb引擎的线程修改了表的数据并提交，对该dump线程的数据并无影响，在这期间不会锁表。此参数需要InnoDB引擎支持，目前MySQL 5.7默认采用InnoDB引擎。
--set-gtid-purged: 因为木子是从阿里云RDS只读库导出(MySQL集群)，所以需要添加这个参数，不然会出现警告信息，主要原因在于：在MySQL5.6以后，加入了全局事务ID(GTID)来强化数据库的主备一致性、故障恢复、以及容错能力。
nohup mysqldump -uxxx -pxxx -q -e -t --single-transaction db_name > /db_name.sql &
# 开启GTID的MySQL导出，如果不添加--set-gtid-purged=OFF警告
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events
# 导入时错误
ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
# 如果你已经导出来GTID的SQL，又不想重新再去导出，可以使用以下方法导入：
mysql> reset slave all;
mysql> reset master;
mysql> source /db_name.sql
innodb_buffer_pool_size: 默认大小为128M，用于缓存索引和数据的内存大小，这个当然是越大越好，数据读写在内存中非常快，减少了对磁盘的读写。当数据提交或满足检查点(checkpoint)条件后才一次性将内存数据刷新到磁盘中。然而内存还有操作系统或数据库其他进程使用，根据经验推荐设置innodb-buffer-pool-size为服务器总可用内存的75%。若设置不当，内存使用可能浪费或者使用过多。对于繁忙的服务器，buffer pool将划分为多个实例以提高系统并发性，减少线程间读写缓存的争用。buffer pool的大小首先受innodb_buffer_pool_instances影响，当然影响较小。
sync_binlog: 这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。对于“sync_binlog”参数的各种设置的说明如下：
sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。
sync_binlog=1，每一次事务都提交，保证高一致性、安全性，但性能损耗也是最大的。(阿里云RDS默认值)
sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
在MySQL中系统默认的设置是sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系统故障，在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为1的时候，即使系统故障，也最多丢失binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响，就是对写入性能影响太大，binlog虽然是顺序IO，多个事务同时提交，同样很大的影响MySQL和IO性能。虽然可以通过group commit的补丁缓解，但是刷新的频率过高对IO的影响也非常大。对于高并发事务的系统来说，sync_binlog设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。阿里云RDS默认sync_binlog为1，很多MySQL DBA设置的sync_binlog并不是最安全的1，而是100、1000 或者是0。这样牺牲一定的一致性，可以获得更高的并发和吞吐量。
innodb_log_file_size: 日志文件大小，即：ib_logfile0
与ib_logfile1
大小，如果你的innodb_log_files_in_group
值为默认，就只会存在两个log file文件。如果参数innodb_log_file_size设置太小，就会导致MySQL的日志文件(redo log)频繁切换，频繁的触发数据库的检查点(Checkpoint)，导致刷新脏页(dirty page)到磁盘的次数增加。从而影响IO性能。另外，如果有一个大的事务，把所有的日志文件写满了，还没有写完，这样就会导致日志不能切换(因为实例恢复还需要，不能被循环复写，好比Oracle中的redo log无法循环覆盖)这样MySQL就Hang住了。如果参数innodb_log_file_size设置太大的话，虽然大大提升了IO性能，但是当MySQL由于意外(断电，OOM-Kill等)宕机时，二进制日志很大，那么恢复的时间必然很长。而且这个恢复时间往往不可控，受多方面因素影响，所以必须权衡二者进行综合考虑。
innodb_log_buffer_size: 控制日志缓冲区的大小，通常不需要把日志缓冲区设置得非常大。推荐的范围是1MB~8MB，一般来说是足够了，MySQL默认是8MB。
innodb_write_io_threads、innodb_read_io_threads: InnoDB使用后台线程处理数据页上读写IO请求的数量，这个值与服务器的CPU相关，因为木子这里是16C，而且我的大部份是写入，所以将innodb_write_io_threads设置成12,innodb_read_io_threads设置成4，以提高对应写入能力。
innodb_flush_log_at_trx_commit: MySQL支持用户自定义在commit时如何将log buffer中的日志刷到log file中。这种控制通过变量：innodb_flush_log_at_trx_commit 来决定，该变量有：0、1、2三种值，默认值为1。注意，这个变量只是控制commit动作是否刷新log buffer到磁盘中。
设置为0，将日志缓冲写入到日志文件，并且每秒钟写盘一次，但是事务提交时不做任何事。在这种情况下，MySQL性能最好，但如果mysqld进程崩溃，通常会导致最后1s的日志丢失。[性能最好，最不安全]
设置为1，将日志缓冲写入到日志文件，并且每次事务提交都进行写盘操作。这是默认的设置，该设置能保证不会丢失任何已经提交的事务。(阿里云RDS默认值) [性能最差，最安全]
设置为2，每次事务提交时把日志缓冲写到日志文件，但不写盘，由存储引擎的main_thread每秒将日志写入磁盘。这时如果mysqld进程崩溃，由于日志已经写入到系统缓存，所以并不会丢失数据；在操作系统崩溃的情况下，通常会导致最后1s的日志丢失。
innodb_thread_concurrency: 默认是0，则表示没有并发线程数限制，所有请求都会直接请求线程执行。注意：当innodb_thread_concurrency设置为0时，则innodb_thread_sleep_delay的设置将会被忽略不起作用。如果数据库没出现性能问题时，使用默认值即可。
innodb_log_files_in_group: 控制日志文件数量，默认为2个，mysql事务日志文件是循环覆写的，当一个日志文件写满后，innodb会自动切换到另一个日志文件，而且会触发数据库的checkpoint，这回导致innodb缓存脏页的小批量刷新，会明显降低innodb的性能。如果innodb_log_file_size设置太小，就会导致innodb频繁地checkpoint，导致性能降低。而如果设置较大，由于事务日志是顺序IO，大大提高了IO性能，但是在崩溃恢复InnoDB时，会导致恢复时间变长。如果InnoDB数据表有频繁的写操作，那么选择合适的innodb_log_file_size值对提升MySQL性能很重要。
max_allowed_packet: MySQL根据配置文件会限制Server接受的数据包大小，大的插入和更新会受max_allowed_packet参数限制，导致大数据写入或者更新失败。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
