MySQL里存储图片的是什么数据类型

背景

MySQL 一直以来都有 TEXT、BLOB 等类型用来存储图片、视频等大对象信息。比如一张图片，随便一张都 5M 以上。视频也是，随便一部视频就是 2G 以上。

假设用 MySQL 来存放电影视频等信息，一部是 2G，那么存储 1000 部就是 2TB，2TB 也就是 1000 条记录而已，但是对数据库性能来说，不仅仅是看记录数量，更主要的还得看占用磁盘空间大小。空间大了，所有以前的经验啥的都失效了。

所以一般来说存放这类信息，也就是存储他们的存放路径，至于文件本身存放在哪里，那这就不是数据库考虑的范畴了。数据库只关心怎么来的快，怎么来的小。

举例

虽然不推荐 MySQL 这样做，但是也得知道 MySQL 该怎么做才行，做到心里有数。比如下面一张微信图片，大概 5M 的样子。

root@ytt:/var/lib/mysql-files# ls -sihl 微信图片_20190711095019.jpg274501 5.4M -rw-r--r-- 1 root root 5.4M Jul 11 07:17 微信图片_20190711095019.jpg

拷贝 100 份这样的图片来测试

root@ytt:/var/lib/mysql-files# for i in `seq 1 100`do cp 微信图片_20190711095019.jpg "$i".jpgdone

root@ytt:/var/lib/mysql-files# ls

100.jpg   17.jpg  25.jpg  33.jpg  41.jpg  4.jpg   58.jpg  66.jpg  74.jpg  82.jpg  90.jpg  99.jpg  f8.tsv

10.jpg    18.jpg  26.jpg  34.jpg  42.jpg  50.jpg  59.jpg  67.jpg  75.jpg  83.jpg  91.jpg  9.jpg   微信图片_20190711095019.jpg

1111.jpg  19.jpg  27.jpg  35.jpg  43.jpg  51.jpg  5.jpg   68.jpg  76.jpg  84.jpg  92.jpg  f1.tsv

11.jpg    1.jpg   28.jpg  36.jpg  44.jpg  52.jpg  60.jpg  69.jpg  77.jpg  85.jpg  93.jpg  f2.tsv

12.jpg    20.jpg  29.jpg  37.jpg  45.jpg  53.jpg  61.jpg  6.jpg   78.jpg  86.jpg  94.jpg  f3.tsv

13.jpg    21.jpg  2.jpg   38.jpg  46.jpg  54.jpg  62.jpg  70.jpg  79.jpg  87.jpg  95.jpg  f4.tsv

14.jpg    22.jpg  30.jpg  39.jpg  47.jpg  55.jpg  63.jpg  71.jpg  7.jpg   88.jpg  96.jpg  f5.tsv

15.jpg    23.jpg  31.jpg  3.jpg   48.jpg  56.jpg  64.jpg  72.jpg  80.jpg  89.jpg  97.jpg  f6.tsv

16.jpg    24.jpg  32.jpg  40.jpg  49.jpg  57.jpg  65.jpg  73.jpg  81.jpg  8.jpg   98.jpg  f7.tsv

我们建三张表，分别用 LONGBLOB、LONGTEXT 和 VARCHAR 来存储这些图片信息

mysql>show create table tt_image1G

*************************** 1. row ***************************

Table: tt_image1

Create Table: CREATE TABLE `tt_image1` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`image_file` longblob,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

1 row in set (0.00 sec)

mysql>show create table tt_image2G

*************************** 1. row ***************************

Table: tt_image2

Create Table: CREATE TABLE `tt_image2` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`image_file` longtext,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

1 row in set (0.00 sec)

mysql>show create table tt_image3G

*************************** 1. row ***************************

Table: tt_image3

Create Table: CREATE TABLE `tt_image3` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`image_file` varchar(100) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

1 row in set (0.00 sec)

我们来给三张表插入 100 张图片（插入前，建议把 max_allowed_packet 设置到最大）

tt_image1

root@ytt:/var/lib/mysql-files# for i in `seq 1 100`

do mysql -S /var/run/mysqld/mysqld.sock -e "insert into ytt.tt_image1(image_file)

values (load_file('/var/lib/mysql-files/$i.jpg'))"done

tt_image2

root@ytt:/var/lib/mysql-files# for i in `seq 1 100`

do mysql -S /var/run/mysqld/mysqld.sock -e "insert into ytt.tt_image2(image_file)

values (hex(load_file('/var/lib/mysql-files/$i.jpg')))"done

tt_image3

root@ytt:/var/lib/mysql-files# aa='begin'for i in `seq 1 100`

do aa=$aa"insert into ytt.tt_image3(image_file) values

('/var/lib/mysql-files/$i.jpg')"

doneaa=$aa'commit'mysql -S /var/run/mysqld/mysqld.sock -e "`echo $aa`"

检查下三张表记录数

mysql>select 'tt_image1' as name ,count(*) from tt_image1 union allselect 'tt_image2',count(*) from tt_image2 union all select 'tt_image3', count(*) from tt_image3+-----------+----------+| name      | count(*) |+-----------+----------+| tt_image1 |      100 || tt_image2 |      100 || tt_image3 |      100 |+-----------+----------+3 rows in set (0.00 sec)

看下文件大小，可以看到实际大小排名，LONGTEXT 字段存储的最大，LONGBLOB 字段缩小到一半，最小的是存储图片路径的表 tt_image3。所以这里从存储空间来看，存放路径最占优势。

root@ytt:/var/lib/mysql/ytt# ls -silhS tt_image*274603 1.1G -rw-r----- 1 mysql mysql 1.1G Jul 11 07:27 tt_image2.ibd274602 545M -rw-r----- 1 mysql mysql 544M Jul 11 07:26 tt_image1.ibd274605  80K -rw-r----- 1 mysql mysql 112K Jul 11 07:27 tt_image3.ibd

那么怎么把图片取出来呢？

tt_image3 肯定是最容易的

mysql>select * from tt_image3+----+----------------------------+| id | image_file                 |+----+----------------------------+|  1 | /var/lib/mysql-files/1.jpg |+----+----------------------------+...100 rows in set (0.00 sec)

tt_image1 直接导出来二进制文件即可，下面我写了个存储过程，导出所有图片。

mysql>DELIMITER $$mysql>USE `ytt`$$mysql>DROP PROCEDURE IF EXISTS `sp_get_image`$$mysql>CREATE DEFINER=`ytt`@`localhost` PROCEDURE `sp_get_image`()mysql>BEGIN      DECLARE i,cnt INT DEFAULT 0     SELECT COUNT(*) FROM tt_image1 WHERE 1 INTO cnt     WHILE i <cnt DO        SET @stmt = CONCAT('select image_file from tt_image1  limit ',i,',1 into dumpfile ''/var/lib/mysql-files/image',i,'.jpg''')       PREPARE s1 FROM @stmt       EXECUTE s1       DROP PREPARE s1     SET i = i + 1     END WHILE     END$$mysql>DELIMITER mysql>call sp_get_image

tt_image2 类似，把 select 语句里 image_file 变为 unhex(image_file) 即可。

总结

这里我举了个用 MySQL 来存放图片的例子，总的来说有以下三点：

占用磁盘空间大（这样会带来各种各样的功能与性能问题，比如备份，写入，读取 *** 作等）

使用不易

还是推荐用文件路径来代替实际的文件内容存放

Mysql是一个数据库系统，比较小型但很实用。

MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。

应用环境

与其他的大型数据库例如 Oracle、DB2、SQL Server等相比，MySQL自有它的不足之处，但是这丝毫也没有减少它受欢迎的程度。对于一般的个人使用者和中小型企业来说，MySQL提供的功能已经绰绰有余，而且由于 MySQL是开放源码软件，因此可以大大降低总体拥有成本。

现象

Sysbench对MySQL进行压测, 并发数过大(>5k)时, Sysbench建立连接的步骤会超时.

猜想

猜想: 直觉上这很简单, Sysbench每建立一个连接, 都要消耗一个线程, 资源消耗过大导致超时.

验证: 修改Sysbench源码, 调大超时时间, 仍然会发生超时.

检查环境

猜想失败, 回到常规的环境检查:

MySQL error log 未见异常.

syslog 未见异常.

tcpdump 观察网络包未见异常, 连接能完成正常的三次握手只观察到在出问题的连接中, 有一部分的TCP握手的第一个SYN包发生了重传, 另一部分没有发生重传.

自己写一个简单的并发发生器, 替换sysbench, 可重现场景. 排除sysbench的影响

猜想2

怀疑 MySQL 在应用层因为某种原因, 没有发送握手包, 比如卡在某一个流程上:

检查MySQL堆栈未见异常, 仿佛MySQL在应用层没有看到新连接进入.

通过strace检查MySQL, 发现 accept() 调用确实没有感知到新连接.

怀疑是OS的原因, Google之, 得到参考文档: A TCP “stuck” connection mystery【http://www.evanjones.ca/tcp-stuck-connection-mystery.html】

分析

参考文档中的现象跟目前的状况很类似, 简述如下:

正常的TCP连接流程:

Client 向 Server 发起连接请求, 发送SYN.

Server 预留连接资源, 向 Client 回复SYN-ACK.

Client 向 Server 回复ACK.

Server 收到 ACK, 连接建立.

在业务层上, Client和Server间进行通讯.

当发生类似SYN-flood的现象时, TCP连接的流程会使用SYN-cookie, 变为:

Client 向 Server 发起连接请求, 发送SYN.

Server 不预留连接资源, 向 Client 回复SYN-ACK, 包中附带有签名A.

Client 向 Server 回复ACK, 附带 f(签名A) (对签名进行运算的结果).

Server 验证签名, 分配连接资源, 连接建立.

在业务层上, Client和Server间进行通讯.

当启用SYN-cookie时, 第3步的ACK包因为 某种原因 丢失, 那么:

从Client的视角, 连接已经建立.

从Server的视角, 连接并不存在, 既没有建立, 也没有”即将建立” (若不启用SYN-cookie, Server会知道某个连接”即将建立”)

发生这种情况时:

若业务层的第一个包应是从 Client 发往 Server, 则会进行重发或抛出连接错误

若业务层的第一个包应是从 Server 发往 Client的, Server不会发出第一个包. MySQL的故障就属于这种情况.

TCP握手的第三步ACK包为什么丢失

参考文档中, 对于TCP握手的第三步ACK包的丢失原因, 描述为:

Some of these packets get lost because some buffer somewhere overflows.

我们可以通过Systemtap进一步探究原因. 通过一个简单的脚本:

probe kernel.function("cookie_v4_check").return

{

source_port = @cast($skb->head + $skb->transport_header, "struct tcphdr")->source

printf("source=%d, return=%d\n",readable_port(source_port), $return)

}

function readable_port(port) {

return (port &((1<<9)-1)) <<8 | (port >>8)

}

观察结果, 可以确认cookie_v4_check (syn cookie机制进行包签名检查的函数)会返回 NULL(0). 即验证是由于syn cookie验证不通过, 导致TCP握手的第三步ACK包不被接受.

之后就是对其中不同条件进行观察, 看看是哪个条件不通过. 最终原因是accept队列满(sk_acceptq_is_full):

static inline bool sk_acceptq_is_full(const struct sock  *sk){   return sk->sk_ack_backlog >sk-   >sk_max_ack_backlog}

恢复故障与日志的正关联

在故障处理的一开始, 我们就检查了syslog, 结论是未见异常.

当整个故障分析完成, 得知了故障与syn cookie有关, 回头看syslog, 里面是有相关的信息, 只是和故障发生的时间不匹配, 没有正关联, 因此被忽略.

检查Linux源码:

if (!queue->synflood_warned &&

sysctl_tcp_syncookies != 2 &&

xchg(&queue->synflood_warned, 1) == 0)

pr_info("%s: Possible SYN flooding on port %d. %s.

Check SNMP counters.\n",

proto, ntohs(tcp_hdr(skb)->dest), msg)

可以看到日志受到了抑制, 因此日志与故障的正关联被破坏.

粗看源码, 每个listen socket只会发送一次告警日志, 要获得日志与故障的正关联, 必须每次测试重启MySQL.

解决方案

这种故障一旦形成, 难以检测系统日志中只会出现一次, 在下次重启MySQL之前就不会再出现了Client如果没有合适的超时机制, 万劫不复.

解决方案:

1. 修改MySQL的协议, 让Client先发握手包. 显然不现实.

2. 关闭syn_cookie. 有安全的人又要跳出来了.

3. 或者调高syn_cookie的触发条件 (syn backlog长度). 降低系统对syn flood的敏感度, 使之可以容忍业务的syn波动.

有多个系统参数混合影响syn backlog长度, 参看【http://blog.dubbelboer.com/2012/04/09/syn-cookies.html】

下图为精华总结

请点击输入图片描述

---